Task S5T03: stats.gk_milestone_events Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates stats.gk_milestone_events, which records one-time milestone achievements for individual GeoKrety. Each event type can only be recorded once per GeoKret. Examples: "This GK has reached 100 km", "This GK has been held by 10 different users", "This GK has crossed to a new country for the first time."

These events enable:

• Timeline views of GK history milestones (UC13)
• Milestone-based notifications (future)
• Hall-of-fame moments ("First GK to reach 10,000 km")

Key design: This table is an append-only event log. Once an event is written, it is never updated. The UNIQUE (gk_id, event_type) constraint ensures each milestone is recorded only once per GeoKret.

Append-only event log recording when each GeoKret crosses a significant threshold. At most 8 distinct milestone types per GeoKret (one row per (gk_id, event_type)). Enables milestone timeline views (UC13) and hall-of-fame queries.

Scope: DDL + 2 secondary indexes. Trigger population in S5T07. Backfill in Sprint 6.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310500200_create_gk_milestone_events.php

Full SQL DDL¶

CREATE TABLE stats.gk_milestone_events (
  id              BIGSERIAL    NOT NULL,
  gk_id           INT          NOT NULL,
  event_type      TEXT         NOT NULL,
  event_value     NUMERIC      NULL,
  additional_data JSONB        NULL,
  occurred_at     TIMESTAMPTZ  NOT NULL,
  recorded_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE (gk_id, event_type),
  CONSTRAINT chk_gk_milestone_event_type CHECK (
    event_type IN (
      'country_first',
      'km_100',
      'km_1000',
      'km_10000',
      'users_10',
      'users_50',
      'users_100',
      'first_find'
    )
  )
);

CREATE INDEX idx_gk_milestone_events_gk
  ON stats.gk_milestone_events (gk_id, occurred_at DESC);

CREATE INDEX idx_gk_milestone_events_type
  ON stats.gk_milestone_events (event_type, occurred_at DESC);

COMMENT ON TABLE stats.gk_milestone_events
  IS 'Append-only log of per-GK milestones; each event_type recorded at most once per GK';
COMMENT ON COLUMN stats.gk_milestone_events.event_type
  IS 'Milestone type: country_first, km_100, km_1000, km_10000, users_10, users_50, users_100, first_find';
COMMENT ON COLUMN stats.gk_milestone_events.event_value
  IS 'Numeric value at threshold (e.g. total km, user count)';
COMMENT ON COLUMN stats.gk_milestone_events.additional_data
  IS 'JSONB metadata (e.g. country code for country_first, actor user_id)';
COMMENT ON COLUMN stats.gk_milestone_events.occurred_at
  IS 'When the GK actually crossed the milestone (from move timestamp)';
COMMENT ON COLUMN stats.gk_milestone_events.recorded_at
  IS 'When this row was inserted into the stats DB';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateGkMilestoneEvents extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.gk_milestone_events (
  id              BIGSERIAL    NOT NULL,
  gk_id           INT          NOT NULL,
  event_type      TEXT         NOT NULL,
  event_value     NUMERIC      NULL,
  additional_data JSONB        NULL,
  occurred_at     TIMESTAMPTZ  NOT NULL,
  recorded_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE (gk_id, event_type),
  CONSTRAINT chk_gk_milestone_event_type CHECK (
    event_type IN (
      'country_first','km_100','km_1000','km_10000',
      'users_10','users_50','users_100','first_find'
    )
  )
);

CREATE INDEX idx_gk_milestone_events_gk
  ON stats.gk_milestone_events (gk_id, occurred_at DESC);

CREATE INDEX idx_gk_milestone_events_type
  ON stats.gk_milestone_events (event_type, occurred_at DESC);
SQL
        );
    }

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

Data Contract¶

Unique constraint: (gk_id, event_type) — each milestone type fires at most once per GeoKret.

SQL Usage Examples¶

-- UC13: Milestone timeline for GK 1234
SELECT event_type, event_value, occurred_at, additional_data
FROM stats.gk_milestone_events
WHERE gk_id = 1234
ORDER BY occurred_at;

-- Hall of fame: first GKs to reach 10,000 km
SELECT gk_id, event_value, occurred_at
FROM stats.gk_milestone_events
WHERE event_type = 'km_10000'
ORDER BY occurred_at
LIMIT 10;

-- How many GKs have reached each milestone?
SELECT event_type, COUNT(*) AS gk_count
FROM stats.gk_milestone_events
GROUP BY event_type
ORDER BY event_type;

TimescaleDB Assessment¶

NOT recommended. Low-cardinality table: at most 8 events per GeoKret. Total rows: O(N_geokrety × 8). Hypertable overhead not justified.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create 20260310500200_create_gk_milestone_events.php
• 2. phinx migrate — no errors
• 3. \d stats.gk_milestone_events — 7 columns, BIGSERIAL PK, UNIQUE + CHECK constraints
• 4. Test invalid event type → exception
• 5. Test duplicate (gk_id, event_type) → UNIQUE exception
• 6. Verify 2 secondary indexes created
• 7. Run pgTAP T-5.3.001 through T-5.3.010 — all pass

• 8. phinx rollback — table and indexes dropped

• 1. Write 20260310500200_create_gk_milestone_events.php

• 2. phinx migrate — no errors
• 3. Verify canonical milestone columns, PK, event-type CHECK, and supporting indexes
• 4. Test invalid event type and canonical deduplication behavior
• 5. Run pgTAP T-5.3.001 through the canonical milestone table checks — all pass
• 6. phinx rollback — table and indexes gone

Milestone Event Types Reference¶

Master-Spec Alignment¶

This task is governed by ../00-SPEC-DRAFT-v1.obsolete.md, Section 5.7.

• Canonical table contract: stats.gk_milestone_events(id, gk_id, event_type, occurred_at, actor_user_id, metadata).
• event_type remains VARCHAR(50) constrained to the master milestone set.
• Fields such as event_value, additional_data, and recorded_at, and a blanket uniqueness rule on (gk_id, event_type), are not part of the canonical contract unless the master spec is amended.

Table Created¶

stats.gk_milestone_events (id, gk_id, event_type, occurred_at, actor_user_id, metadata)