Task S4T10: Indexes for Sprint 4 Tables¶

Sprint Context¶

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

Source¶

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

Resolved Decision¶

• Sprint 4 creates exactly four canonical secondary indexes in this task.
• The earlier ten-index draft is obsolete because it depended on non-canonical columns and unapproved performance assumptions.
• Additional indexes remain out of scope until approved as a master-spec amendment.

Purpose & Scope¶

Creates secondary indexes on the tables introduced in S4T01–S4T07 to support efficient:

• Waypoint lookup by country (UC1, UC10)
• Cache visit queries by waypoint (UC10: most-visited caches)
• Social relation queries by user (UC2, UC3)
• Social relation queries by GeoKret (UC3)

Tables already have appropriate PK indexes. Only non-PK access patterns requiring additional indexes are created here.

Adds the canonical four secondary indexes to the Sprint 4 tables. These indexes are performance optimizations, not correctness requirements. They support the approved UC2, UC3, and UC10 query shapes without reintroducing the deprecated ten-index draft.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400900_create_cache_relation_indexes.php

Full SQL DDL¶

CREATE INDEX idx_waypoints_country
  ON stats.waypoints (country)
  WHERE country IS NOT NULL;

CREATE INDEX idx_gk_cache_visits_waypoint
  ON stats.gk_cache_visits (waypoint_id, gk_id);

CREATE INDEX idx_user_cache_visits_waypoint
  ON stats.user_cache_visits (waypoint_id, user_id);
CREATE INDEX idx_gk_related_users_user
  ON stats.gk_related_users (user_id);

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateCacheRelationIndexes extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE INDEX idx_waypoints_country
  ON stats.waypoints (country) WHERE country IS NOT NULL;

CREATE INDEX idx_gk_cache_visits_waypoint
  ON stats.gk_cache_visits (waypoint_id, gk_id);

CREATE INDEX idx_user_cache_visits_waypoint
  ON stats.user_cache_visits (waypoint_id, user_id);

CREATE INDEX idx_gk_related_users_user
  ON stats.gk_related_users (user_id);
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<'SQL'
DROP INDEX IF EXISTS stats.idx_waypoints_country;
DROP INDEX IF EXISTS stats.idx_gk_cache_visits_waypoint;
DROP INDEX IF EXISTS stats.idx_user_cache_visits_waypoint;
DROP INDEX IF EXISTS stats.idx_gk_related_users_user;
SQL
        );
    }
}

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration 20260310400900_create_cache_relation_indexes.php
• 2. phinx migrate — no errors
• 3. \di+ stats.* — confirm exactly the canonical 4 indexes listed
• 4. Verify partial index on idx_waypoints_country (only non-NULL countries)
• 5. Run pgTAP T-4.10.001 through T-4.10.005 — all pass

• 6. phinx rollback — canonical 4 indexes dropped

• 1. Write 20260310400900_create_cache_relation_indexes.php

• 2. phinx migrate — no errors
• 3. \di+ stats.* — exactly the canonical 4 indexes listed
• 4. Verify no duplicate index is created for waypoint_code unique lookup
• 5. Run pgTAP T-4.10.001 through T-4.10.005 — all pass
• 6. phinx rollback — canonical 4 indexes removed cleanly

Index Reference Table¶

Notes on Missing Indexes¶

• stats.waypoints (waypoint_code) — already covered by the UNIQUE constraint (implicit index)
• stats.gk_cache_visits (gk_id, waypoint_id) — PK covers this already
• stats.user_cache_visits (user_id, waypoint_id) — PK covers this already
• stats.gk_related_users (geokrety_id, user_id) — PK covers this already
• stats.user_related_users (user_id, related_user_id) — PK covers this already

Master-Spec Alignment¶

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

• The canonical Sprint 4 secondary-index set is exactly: idx_gk_related_users_user, idx_user_cache_visits_waypoint, idx_gk_cache_visits_waypoint, and idx_waypoints_country.
• References to non-canonical or nonexistent columns such as source_type, touch_count, or alternate relation-key names are obsolete draft text.
• If additional performance indexes are desired beyond the canonical four, they must first be approved as a master-spec amendment rather than asserted here as settled requirements.

Agent Loop Log¶

• 2026-03-10T18:40:00Z — dba: removed the unapproved ten-index draft and kept only the four indexes referenced by the Sprint 4 contract.
• 2026-03-10T18:40:00Z — critical-thinking: retained only indexes tied to explicit UC2, UC3, and UC10 access paths; broader performance tuning stays out of scope until approved.
• 2026-03-10T18:40:00Z — specification: aligned SQL, rollback, tests, checklist, and reference tables with Q-029 and the canonical Sprint 4 column names.

Resolution¶

Q-029 is resolved by reducing this task to the canonical four-index set only.

Objects Created (Secondary Indexes)¶