Task S4T06: Create stats.gk_related_users Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates stats.gk_related_users, which tracks which users have interacted with each GeoKret and how many times. A row (geokrety_id, user_id) means that user has logged at least one non-comment move on that GK. This directly powers:

• "How many distinct users has this GeoKret touched?" (reach metric)
• UC3: GK circulation graph (number of interactions per GK)
• Owner +1 handover bonus detection (via reach of 10 distinct users in 6 months)

Create stats.gk_related_users to track user-GeoKret interactions. Powers UC3 (GK circulation graph) and the 10-distinct-users reach-bonus detection logic.

Scope: One table. No FK to geokrety.gk_geokrety (cross-schema avoided). No triggers (S4T09). No seed data (S4T11).

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400500_create_gk_related_users.php

Full SQL DDL¶

CREATE TABLE stats.gk_related_users (
  geokrety_id         INT          NOT NULL,
  user_id             INT          NOT NULL,
  interaction_count   BIGINT       NOT NULL DEFAULT 0,
  first_interaction   TIMESTAMPTZ  NOT NULL,
  last_interaction    TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (geokrety_id, user_id)
);

COMMENT ON TABLE stats.gk_related_users
  IS 'Per-GeoKret per-user interaction counter; powers UC3, reach bonus, and social graph';
COMMENT ON COLUMN stats.gk_related_users.geokrety_id
  IS 'GeoKret internal ID (no cross-schema FK)';
COMMENT ON COLUMN stats.gk_related_users.user_id
  IS 'User internal ID — only authenticated users (author IS NOT NULL)';
COMMENT ON COLUMN stats.gk_related_users.interaction_count
  IS 'Count of qualifying moves (DROP/GRAB/SEEN/DIP, not COMMENT) by this user on this GK';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateGkRelatedUsers extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.gk_related_users (
  geokrety_id         INT          NOT NULL,
  user_id             INT          NOT NULL,
  interaction_count   BIGINT       NOT NULL DEFAULT 0,
  first_interaction   TIMESTAMPTZ  NOT NULL,
  last_interaction    TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (geokrety_id, user_id)
);

COMMENT ON TABLE stats.gk_related_users
  IS 'Per-GeoKret per-user interaction counter; powers UC3, reach bonus, and social graph';
COMMENT ON COLUMN stats.gk_related_users.geokrety_id
  IS 'GeoKret internal ID (no cross-schema FK)';
COMMENT ON COLUMN stats.gk_related_users.user_id
  IS 'User internal ID — only authenticated users (author IS NOT NULL)';
COMMENT ON COLUMN stats.gk_related_users.interaction_count
  IS 'Count of qualifying moves (DROP/GRAB/SEEN/DIP, not COMMENT) by this user on this GK';
SQL
        );
    }

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

Data Contract¶

Qualifying moves: move_type IN (0,1,3,5) — DROP, GRAB, SEEN, DIP. Comments (type 2) and Archives (type 4) do NOT qualify.

SQL Usage Examples¶

-- How many distinct users has a GeoKret touched?
SELECT COUNT(*) AS distinct_users
FROM stats.gk_related_users
WHERE geokrety_id = 12345;

-- Total interactions on a GeoKret
SELECT SUM(interaction_count) AS total_interactions
FROM stats.gk_related_users
WHERE geokrety_id = 12345;

-- Has a specific user interacted with a specific GK?
SELECT interaction_count, first_interaction, last_interaction
FROM stats.gk_related_users
WHERE geokrety_id = 12345 AND user_id = 9876;

-- GeoKrety that have reached 10+ distinct users (for reach bonus check)
SELECT geokrety_id, COUNT(*) AS distinct_users
FROM stats.gk_related_users
GROUP BY geokrety_id
HAVING COUNT(*) >= 10
ORDER BY distinct_users DESC;

-- Most circulated GeoKrety (UC3 base query)
SELECT geokrety_id,
       COUNT(*) AS distinct_users,
       SUM(interaction_count) AS total_interactions
FROM stats.gk_related_users
GROUP BY geokrety_id
ORDER BY distinct_users DESC
LIMIT 20;

-- Users that have interacted with the most GeoKrety
SELECT user_id, COUNT(*) AS distinct_gks_touched
FROM stats.gk_related_users
GROUP BY user_id
ORDER BY distinct_gks_touched DESC
LIMIT 20;

Graph / Visualization Specification¶

1. UC3: GK circulation chart — Horizontal bar
2. Data: SELECT geokrety_id, COUNT(*) AS users FROM stats.gk_related_users GROUP BY geokrety_id ORDER BY users DESC LIMIT 20

3. x-axis: distinct users, y-axis: GK identifier

4. Reach milestone distribution — Histogram

5. Data: SELECT reached_users, COUNT(*) AS gk_count FROM (SELECT geokrety_id, COUNT(*) AS reached_users FROM stats.gk_related_users GROUP BY geokrety_id) sub GROUP BY reached_users ORDER BY reached_users

ASCII Sample — GK reach distribution:
1 user   ██████████████████████████████ 60%   (very new GKs)
2-5      ████████████████               30%
6-9      ████                            7%
10+      █                               3%

Top 3 most circulated GKs:
GK-0001  ██████████████████████  412 distinct users
GK-ABCD  █████████████████████   387 distinct users
GK-F001  ████████████████████    359 distinct users

TimescaleDB Assessment¶

NOT recommended. stats.gk_related_users is a compressed rollup with PK (geokrety_id, user_id). No time dimension on the PK. Standard PostgreSQL with B-tree index handles all workloads.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration 20260310400500_create_gk_related_users.php
• 2. Run phinx migrate — confirm no errors
• 3. \d stats.gk_related_users — 5 columns with correct types
• 4. Verify PK on (geokrety_id, user_id)
• 5. Table empty after creation
• 6. Run pgTAP tests T-4.6.001 through T-4.6.009

• 7. Verify phinx rollback drops table cleanly

• • [DBA ] DBA file reviewed: task-S4T06.dba.md
• Migration 20260310400500_create_gk_related_users.php created
• Migration runs successfully
• All T-4.6.xxx tests pass
• Rollback verified