Task S4T07: stats.user_related_users Table¶

Sprint Context¶

• Sprint index: Sprint 4 Task Index
• Tags: database, database-refactor, sprint-4, task-index

Source¶

• DBA source: task-S4T07.dba.md
• Specification source: task-S4T07.specification.md

Purpose & Scope¶

Creates stats.user_related_users, which records social connections between pairs of users as inferred from shared GeoKrety interactions. A row (user_id, related_user_id) means these two users have both interacted with the same GeoKret. shared_geokrety_count counts how many distinct GeoKrety they share. This directly powers:

• UC2: User social-network graph (most-shared-GK pairs)
• "Most connected user" leaderboard
• Social proximity analytics

The relationship is directional in storage but semantically symmetric: if (A, B) exists, (B, A) also exists. The trigger maintains both directions.

Provides a persistent social-graph adjacency table between authenticated users. Pair (user_id, related_user_id) exists when both users have interacted with the same GeoKret. Symmetric: both (A,B) and (B,A) are stored. This table directly serves UC2 (User Network Graph) and any social proximity queries.

Scope: DDL only. Trigger population is in S4T09.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400600_create_user_related_users.php

Full SQL DDL¶

CREATE TABLE stats.user_related_users (
  user_id                INT          NOT NULL,
  related_user_id        INT          NOT NULL,
  shared_geokrety_count  BIGINT       NOT NULL DEFAULT 0,
  first_seen_at          TIMESTAMPTZ  NOT NULL,
  last_seen_at           TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (user_id, related_user_id),
  CONSTRAINT chk_user_related_users_no_self
    CHECK (user_id <> related_user_id)
);

COMMENT ON TABLE stats.user_related_users
  IS 'Directional user-user relation via shared GeoKrety; both directions stored; powers UC2 social graph';
COMMENT ON COLUMN stats.user_related_users.shared_geokrety_count
  IS 'Number of distinct GeoKrety that both users have interacted with';
COMMENT ON COLUMN stats.user_related_users.user_id
  IS 'Source user (authenticated only)';
COMMENT ON COLUMN stats.user_related_users.related_user_id
  IS 'Target user (authenticated only); never equals user_id (self-relation prevented by CHECK)';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateUserRelatedUsers extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.user_related_users (
  user_id                INT          NOT NULL,
  related_user_id        INT          NOT NULL,
  shared_geokrety_count  BIGINT       NOT NULL DEFAULT 0,
  first_seen_at          TIMESTAMPTZ  NOT NULL,
  last_seen_at           TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (user_id, related_user_id),
  CONSTRAINT chk_user_related_users_no_self
    CHECK (user_id <> related_user_id)
);

COMMENT ON TABLE stats.user_related_users
  IS 'Directional user-user relation via shared GeoKrety; both directions stored; powers UC2 social graph';
COMMENT ON COLUMN stats.user_related_users.shared_geokrety_count
  IS 'Number of distinct GeoKrety that both users have interacted with';
COMMENT ON COLUMN stats.user_related_users.user_id
  IS 'Source user (authenticated only)';
COMMENT ON COLUMN stats.user_related_users.related_user_id
  IS 'Target user (authenticated only); never equals user_id (self-relation prevented by CHECK)';
SQL
        );
    }

    public function down(): void
    {
        $this->execute('DROP TABLE IF EXISTS stats.user_related_users;');
    }
}

Data Contract¶

Constraint: chk_user_related_users_no_self — CHECK user_id <> related_user_id prevents self-links.

Trigger behaviour (S4T09): When user A interacts with a GeoKret that users B and C have also touched:

1. Upsert (A, B) — increment shared_geokrety_count
2. Upsert (B, A) — same increment for reverse direction
3. Do the same for (A, C) and (C, A)

SQL Usage Examples¶

-- UC2: Who has user 9876 connected with, ordered by strength?
SELECT related_user_id, shared_geokrety_count, last_seen_at
FROM stats.user_related_users
WHERE user_id = 9876
ORDER BY shared_geokrety_count DESC
LIMIT 20;

-- Symmetric check: if (A,B) exists, (B,A) must also exist
SELECT a.user_id, a.related_user_id, a.shared_geokrety_count,
       b.shared_geokrety_count AS reverse_count
FROM stats.user_related_users a
JOIN stats.user_related_users b ON b.user_id = a.related_user_id AND b.related_user_id = a.user_id
LIMIT 10;

-- Most socially connected users (hub detection)
SELECT user_id, COUNT(*) AS distinct_connections, SUM(shared_geokrety_count) AS total_shared
FROM stats.user_related_users
GROUP BY user_id
ORDER BY distinct_connections DESC
LIMIT 20;

-- Common connections between two users (via SQL join on gk_related_users)
-- (used in advanced graph queries, not direct from this table)
SELECT DISTINCT r1.geokrety_id
FROM stats.gk_related_users r1
JOIN stats.gk_related_users r2 ON r1.geokrety_id = r2.geokrety_id
WHERE r1.user_id = 100 AND r2.user_id = 200;

-- Self-relation guard check: returns 0 if integrity holds
SELECT COUNT(*) AS self_relations
FROM stats.user_related_users
WHERE user_id = related_user_id;

Graph / Visualization Specification¶

1. UC2: User social-network graph — Force-directed graph (D3.js)
2. Nodes: users; edges: (user_id, related_user_id) where shared_geokrety_count >= 2
3. Edge weight: shared_geokrety_count
4. Only one direction needed for undirected graph visualisation: WHERE user_id < related_user_id

5. Data: SELECT user_id, related_user_id, shared_geokrety_count FROM stats.user_related_users WHERE user_id < related_user_id AND shared_geokrety_count >= 2 LIMIT 500

6. Most connected users — Table/leaderboard

7. Data: SELECT user_id, COUNT(*) AS connections FROM stats.user_related_users GROUP BY user_id ORDER BY connections DESC LIMIT 50

ASCII Sample — Social graph fragment:
User A ──── (23 GKs) ──── User B
     |                       |
  (8 GKs)                 (5 GKs)
     |                       |
User C ──── (12 GKs) ─── User D

Top connected users:
User_912  ████████████████████ 430 connections
User_003  ███████████████████  407 connections

TimescaleDB Assessment¶

NOT recommended. Social-relation rollup table with PK (user_id, related_user_id). Writes are UPSERT patterns. No time-series partitioning appropriate.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration 20260310400600_create_user_related_users.php
• 2. Run phinx migrate — confirm no errors
• 3. \d stats.user_related_users — 5 columns with correct types
• 4. Verify PK on (user_id, related_user_id)
• 5. Test self-link: INSERT INTO stats.user_related_users VALUES (1,1,0,now(),now()) → expect CHECK violation
• 6. Table empty after creation
• 7. Run pgTAP tests T-4.7.001 through T-4.7.009

• 8. Verify phinx rollback drops table cleanly

• 1. Write 20260310400600_create_user_related_users.php

• 2. phinx migrate — no errors
• 3. Confirm 5 columns in \d stats.user_related_users
• 4. Verify composite PK and CHECK constraint present
• 5. Test self-link violation manually
• 6. Table is empty
• 7. Run pgTAP T-4.7.001 through T-4.7.009 — all pass
• 8. phinx rollback — table gone

Table Created¶

stats.user_related_users (user_id, related_user_id, shared_geokrety_count, first_seen_at, last_seen_at)

No FK to geokrety.* schema¶

user_id and related_user_id are bare INTs. No cross-schema foreign keys. Referential integrity enforced by trigger logic (S4T09).