Task S5T10: Sprint 5 Analytics Indexes¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates performance indexes on all Sprint 5 analytics tables. Sprint 5 tables that survive live-data writes (hourly_activity, country_pair_flows, outbox_events) are the primary targets. The milestone and first_finder tables have low cardinality PK-based access, so additional indexes are minimal.

Adds the incremental B-tree indexes needed by canonical Sprint 5 analytics tables. No new tables are created.

Requirements¶

Migration File¶

20260310500900_create_analytics_indexes.php

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateAnalyticsIndexes extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
-- hourly_activity
CREATE INDEX IF NOT EXISTS idx_hourly_activity_date_desc
  ON stats.hourly_activity (activity_date DESC);

-- country_pair_flows
CREATE INDEX IF NOT EXISTS idx_country_pair_flows_month_desc
  ON stats.country_pair_flows (year_month DESC);

CREATE INDEX IF NOT EXISTS idx_country_pair_flows_from
  ON stats.country_pair_flows (from_country, year_month DESC);

CREATE INDEX IF NOT EXISTS idx_country_pair_flows_to
  ON stats.country_pair_flows (to_country, year_month DESC);

-- outbox_events
CREATE INDEX IF NOT EXISTS idx_outbox_events_created_at
  ON stats.outbox_events (created_at ASC);

CREATE INDEX IF NOT EXISTS idx_outbox_events_unprocessed
  ON stats.outbox_events (created_at ASC)
  WHERE processed = FALSE;

-- gk_milestone_events
CREATE INDEX IF NOT EXISTS idx_gk_milestone_events_type
  ON stats.gk_milestone_events (event_type);

-- first_finder_events
CREATE INDEX IF NOT EXISTS idx_first_finder_events_user
  ON stats.first_finder_events (finder_user_id);
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<'SQL'
DROP INDEX IF EXISTS stats.idx_hourly_activity_date_desc;
DROP INDEX IF EXISTS stats.idx_country_pair_flows_month_desc;
DROP INDEX IF EXISTS stats.idx_country_pair_flows_from;
DROP INDEX IF EXISTS stats.idx_country_pair_flows_to;
DROP INDEX IF EXISTS stats.idx_outbox_events_created_at;
DROP INDEX IF EXISTS stats.idx_outbox_events_unprocessed;
DROP INDEX IF EXISTS stats.idx_gk_milestone_events_type;
DROP INDEX IF EXISTS stats.idx_first_finder_events_user;
SQL
        );
    }
}

Data Contract¶

TimescaleDB Assessment¶

stats.hourly_activity and stats.country_pair_flows are natural candidates for TimescaleDB hypertables if the dataset grows beyond 50M rows. However, PostgreSQL B-tree indexes on (activity_date DESC) and (year_month DESC) provide equivalent performance for dashboard queries in the near term. TimescaleDB conversion is a Sprint 7+ operation if warranted.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create 20260310500900_create_analytics_indexes.php
• 2. phinx migrate — no errors
• 3. Verify all 8 index names via \di stats.*
• 4. EXPLAIN on recent-activity query — confirm index scan
• 5. EXPLAIN on country-flows from-country query — confirm index scan
• 6. Verify partial index on outbox via pg_indexes system view
• 7. Run pgTAP T-5.10.001 through T-5.10.010

• 8. phinx rollback — all 8 indexes dropped cleanly

• Create 20260310500900_create_analytics_indexes.php

• Run phinx migrate — no errors
• Verify the task-owned canonical indexes via \di stats.*
• Run EXPLAIN on key dashboard queries to confirm index scans
• Confirm any implementation-specific bridge index only if a bridge table exists
• Run pgTAP T-5.10.001 through T-5.10.010
• phinx rollback — all 8 indexes dropped

hourly_activity — Descending Date¶

CREATE INDEX IF NOT EXISTS idx_hourly_activity_date_desc
  ON stats.hourly_activity (activity_date DESC);

Used by: dashboard queries reading the last N days of activity. The primary key already covers (activity_date, hour_utc, move_type) for exact lookups but a single-column DESC index speeds up range scans from the API.

country_pair_flows — year_month Descending¶

CREATE INDEX IF NOT EXISTS idx_country_pair_flows_month_desc
  ON stats.country_pair_flows (year_month DESC);

Used by: leaderboard / map queries that ORDER BY year_month DESC LIMIT N.

country_pair_flows — from_country, year_month Descending¶

CREATE INDEX IF NOT EXISTS idx_country_pair_flows_from
  ON stats.country_pair_flows (from_country, year_month DESC);

Used by: "show recent outbound flows from PL" queries. Covers WHERE from_country='PL' ORDER BY year_month DESC.

country_pair_flows — to_country, year_month Descending¶

CREATE INDEX IF NOT EXISTS idx_country_pair_flows_to
  ON stats.country_pair_flows (to_country, year_month DESC);

Used by: "show recent inbound flows to DE" queries.

outbox_events — created_at Ascending (Relay Consumption)¶

CREATE INDEX IF NOT EXISTS idx_outbox_events_created_at
  ON stats.outbox_events (created_at ASC);

Used by: AMQP relay worker that reads unprocessed events ORDER BY created_at ASC LIMIT N. Replaces the partial index design (which required WHERE processed = FALSE) to keep it future-proof as other states could be added.

outbox_events — Partial Index on Unprocessed Events (Optional Fast-Path)¶

CREATE INDEX IF NOT EXISTS idx_outbox_events_unprocessed
  ON stats.outbox_events (created_at ASC)
  WHERE processed = FALSE;

Small partial index for relay worker hot-path. Invisible to index scanner once all rows are processed.

gk_milestone_events — event_type¶

CREATE INDEX IF NOT EXISTS idx_gk_milestone_events_type
  ON stats.gk_milestone_events (event_type);

Used by: "how many GKs have hit km_100?" aggregate queries.

first_finder_events — finder_user_id¶

CREATE INDEX IF NOT EXISTS idx_first_finder_events_user
  ON stats.first_finder_events (finder_user_id);

Used by: "list all GKs first found by user X" profile queries.

Index Summary Table¶

Master-Spec Alignment¶

This task is governed by ../00-SPEC-DRAFT-v1.obsolete.md, Section 9 and the canonical table contracts for Sprint 5.

• Do not assume a processed column on any event bridge table unless the master spec is amended to define one.
• Indexes already owned by table-creation tasks must not be re-specified here as new mandatory Sprint 5 indexes.
• Any lower text that depends on non-canonical event-bridge columns is obsolete and superseded by this alignment block.

AC-5.10.1 — Hourly Activity Index Used¶

Given hourly_activity table populated, query filters activity_date > NOW() - INTERVAL '7 days' When EXPLAIN ANALYZE run Then query plan shows Index Scan on idx_hourly_activity_date_desc

AC-5.10.2 — Country Pair Flows From-Country Filter¶

Given country_pair_flows rows exist, query does WHERE from_country = 'PL' ORDER BY year_month DESC When EXPLAIN ANALYZE run Then plan shows Index Scan on idx_country_pair_flows_from

AC-5.10.3 — Event Bridge Read Index¶

Given a concrete bridge-table implementation is approved When the bridge reader query is benchmarked Then the approved implementation-specific read index is used

AC-5.10.4 — All Indexes Present¶

Given migration ran When SELECT indexname FROM pg_indexes WHERE schemaname='stats' queried Then the canonical indexes created by this task are listed

AC-5.10.5 — Rollback Removes All Indexes¶

Given migration has been applied When phinx rollback run Then none of the indexes created by this task appear in pg_indexes WHERE schemaname='stats'