Task S5T04: stats.first_finder_events Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates stats.first_finder_events, which records who was the first non-owner to interact with each GeoKret, and how quickly that happened after the GK was created. One row per GeoKret maximum.

This table powers:

• UC14: First Finder Hall of Fame ("fastest finders", "most first finds per user")
• "First find within 7 days" statistics
• User achievement: first-finder badge

Key design: PK is gk_id (one first-finder per GeoKret). The finder_user_id is the first non-owner to log a qualifying move. hours_since_creation is the time delta between GK creation and the first-finder event (as SMALLINT to cap at 65535 hours ≈ 7.5 years).

Records one row per GeoKret for the first non-owner interaction, capturing who found it first and how quickly. PK is gk_id. Powers UC14 First Finder Hall of Fame and "fastest finders" analytics.

Scope: DDL + 2 secondary indexes. Trigger population in S5T07 (handles first-find detection on INSERT). Backfill in Sprint 6.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310500300_create_first_finder_events.php

Full SQL DDL¶

CREATE TABLE stats.first_finder_events (
  gk_id                INT          NOT NULL,
  finder_user_id       INT          NOT NULL,
  move_type            SMALLINT     NOT NULL,
  hours_since_creation SMALLINT     NOT NULL,
  found_at             TIMESTAMPTZ  NOT NULL,
  recorded_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  PRIMARY KEY (gk_id),
  CONSTRAINT chk_ffe_move_type CHECK (move_type IN (0, 1, 3, 5)),
  CONSTRAINT chk_ffe_hours_non_negative CHECK (hours_since_creation >= 0)
);

CREATE INDEX idx_first_finder_events_user
  ON stats.first_finder_events (finder_user_id, found_at DESC);

CREATE INDEX idx_first_finder_events_hours
  ON stats.first_finder_events (hours_since_creation)
  WHERE hours_since_creation <= 168;  -- within 7 days (168 hours)

COMMENT ON TABLE stats.first_finder_events
  IS 'First non-owner interaction per GeoKret; one row per GK; powers UC14 first-finder leaderboard';
COMMENT ON COLUMN stats.first_finder_events.gk_id
  IS 'GeoKret ID — PK; one first-find per GeoKret';
COMMENT ON COLUMN stats.first_finder_events.finder_user_id
  IS 'User ID of the first non-owner to interact with this GK';
COMMENT ON COLUMN stats.first_finder_events.move_type
  IS 'Move type of the first-find interaction: 0=DROP, 1=GRAB, 3=SEEN, 5=DIP';
COMMENT ON COLUMN stats.first_finder_events.hours_since_creation
  IS 'Hours between GK creation and this first-find event (capped at SMALLINT max ~65535h)';
COMMENT ON COLUMN stats.first_finder_events.found_at
  IS 'Timestamp of the first-find move';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateFirstFinderEvents extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.first_finder_events (
  gk_id                INT          NOT NULL,
  finder_user_id       INT          NOT NULL,
  move_type            SMALLINT     NOT NULL,
  hours_since_creation SMALLINT     NOT NULL,
  found_at             TIMESTAMPTZ  NOT NULL,
  recorded_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  PRIMARY KEY (gk_id),
  CONSTRAINT chk_ffe_move_type CHECK (move_type IN (0, 1, 3, 5)),
  CONSTRAINT chk_ffe_hours_non_negative CHECK (hours_since_creation >= 0)
);

CREATE INDEX idx_first_finder_events_user
  ON stats.first_finder_events (finder_user_id, found_at DESC);

CREATE INDEX idx_first_finder_events_hours
  ON stats.first_finder_events (hours_since_creation)
  WHERE hours_since_creation <= 168;
SQL
        );
    }

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

Data Contract¶

Graph / Visualization Specification¶

UC14: First Finder Hall of Fame

-- Top first finders (most GKs found first)
SELECT finder_user_id, COUNT(*) AS gks_found_first
FROM stats.first_finder_events
GROUP BY finder_user_id
ORDER BY gks_found_first DESC
LIMIT 20;

-- Fastest finders (only within 7-day window = 168 hours)
SELECT gk_id, finder_user_id, hours_since_creation, found_at
FROM stats.first_finder_events
WHERE hours_since_creation <= 168
ORDER BY hours_since_creation
LIMIT 20;

-- Distribution: how quickly are GKs first found?
SELECT
  CASE
    WHEN hours_since_creation <= 24   THEN '< 1 day'
    WHEN hours_since_creation <= 72   THEN '1-3 days'
    WHEN hours_since_creation <= 168  THEN '3-7 days'
    ELSE '> 7 days'
  END AS bucket,
  COUNT(*) AS gk_count
FROM stats.first_finder_events
GROUP BY 1
ORDER BY MIN(hours_since_creation);

TimescaleDB Assessment¶

NOT recommended. One row per GeoKret (natural upper bound). TimescaleDB hypertable overhead not justified.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create 20260310500300_create_first_finder_events.php
• 2. phinx migrate — no errors
• 3. \d stats.first_finder_events — 6 columns, gk_id PK, 2 CHECKs
• 4. Test move_type = 2 (COMMENT) → CHECK violation
• 5. Test hours_since_creation = -1 → CHECK violation
• 6. Test duplicate gk_id → PK violation
• 7. Verify partial index on hours_since_creation <= 168
• 8. Run pgTAP T-5.4.001 through T-5.4.011 — all pass

• 9. phinx rollback — table and indexes gone

• 1. Write 20260310500300_create_first_finder_events.php

• 2. phinx migrate — no errors
• 3. Verify canonical first-finder columns, PK, CHECK constraints, and indexes
• 4. Test CHECK and PK violations
• 5. Run pgTAP T-5.4.001 through T-5.4.011 — all pass
• 6. phinx rollback — clean removal

Master-Spec Alignment¶

This task is governed by ../00-SPEC-DRAFT-v1.obsolete.md, Section 5.7 and Test Matrix items T142-T143.

• Canonical table contract: stats.first_finder_events(gk_id, finder_user_id, move_id, move_type, found_at, gk_created_at, hours_since_creation).
• First-finder eligibility is limited to qualifying non-owner moves within 168 hours of GK creation.
• Any lower text that omits move_id or gk_created_at, or that treats the first-finder window as open-ended, is obsolete and superseded by this alignment block.

Table Created¶

stats.first_finder_events (gk_id, finder_user_id, move_id, move_type, found_at, gk_created_at, hours_since_creation)