Task S4T01: Create stats.waypoints Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates the stats.waypoints canonical lookup table. Every distinct waypoint encountered in geokrety.gk_moves.waypoint is normalised, deduplicated, and stored here with coordinates, country, and source provenance. This table enables O(1) cache lookups in gk_cache_visits and user_cache_visits without scanning raw move rows.

The source column tracks where the waypoint record originated:

• GC = seeded from geokrety.gk_waypoints_gc
• OC = seeded from geokrety.gk_waypoints_oc
• UK = first encountered in the gk_moves stream (source unknown)

Create the canonical waypoint lookup table stats.waypoints. This table is the foundation for cache-analytics: every move that references a waypoint string (e.g., GC1A2B3, OK12345) resolves against this table and obtains a stable surrogate integer id. Downstream tables stats.gk_cache_visits and stats.user_cache_visits use this FK, eliminating repeated string comparisons and enabling O(1) join lookups.

Scope: Create one table with two constraints. No triggers. No functions. No seed data in this step (seeding is S4T03).

Definitions¶

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400000_create_waypoints.php

Full SQL DDL¶

CREATE TABLE stats.waypoints (
  id             BIGSERIAL    PRIMARY KEY,
  waypoint_code  VARCHAR(20)  NOT NULL,
  source         CHAR(2)      NOT NULL DEFAULT 'UK',
  lat            DOUBLE PRECISION,
  lon            DOUBLE PRECISION,
  country        CHAR(2),
  first_seen_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  CONSTRAINT uq_waypoints_code UNIQUE (waypoint_code),
  CONSTRAINT chk_waypoints_source CHECK (source IN ('GC','OC','UK'))
);

COMMENT ON TABLE stats.waypoints
  IS 'Canonical deduplicated waypoint registry; each distinct waypoint code appears exactly once';
COMMENT ON COLUMN stats.waypoints.waypoint_code
  IS 'Uppercase normalised waypoint identifier, e.g. GC1A2B3, OKXXXX';
COMMENT ON COLUMN stats.waypoints.source
  IS 'Provenance: GC=geocaching.com seed, OC=opencaching seed, UK=first seen in move stream';
COMMENT ON COLUMN stats.waypoints.country
  IS 'ISO 3166-1 alpha-2 country code derived from waypoint seed tables; may be NULL for UK-sourced';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateWaypoints extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.waypoints (
  id             BIGSERIAL    PRIMARY KEY,
  waypoint_code  VARCHAR(20)  NOT NULL,
  source         CHAR(2)      NOT NULL DEFAULT 'UK',
  lat            DOUBLE PRECISION,
  lon            DOUBLE PRECISION,
  country        CHAR(2),
  first_seen_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  CONSTRAINT uq_waypoints_code UNIQUE (waypoint_code),
  CONSTRAINT chk_waypoints_source CHECK (source IN ('GC','OC','UK'))
);

COMMENT ON TABLE stats.waypoints
  IS 'Canonical deduplicated waypoint registry; each distinct waypoint code appears exactly once';
COMMENT ON COLUMN stats.waypoints.waypoint_code
  IS 'Uppercase normalised waypoint identifier, e.g. GC1A2B3, OKXXXX';
COMMENT ON COLUMN stats.waypoints.source
  IS 'Provenance: GC=geocaching.com seed, OC=opencaching seed, UK=first seen in move stream';
COMMENT ON COLUMN stats.waypoints.country
  IS 'ISO 3166-1 alpha-2 country code derived from waypoint seed tables; may be NULL for UK-sourced';
SQL
        );
    }

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

Data Contract¶

Constraints:

• uq_waypoints_code — UNIQUE on waypoint_code
• chk_waypoints_source — CHECK source IN ('GC','OC','UK')

Table: stats.waypoints

SQL Usage Examples¶

-- Look up a waypoint by code (case-insensitive lookup helper)
SELECT id, waypoint_code, source, lat, lon, country
FROM stats.waypoints
WHERE waypoint_code = UPPER('gc1a2b3');

-- Count waypoints by source
SELECT source, COUNT(*) AS total
FROM stats.waypoints
GROUP BY source
ORDER BY source;

-- Find waypoints with no coordinates (UK-sourced, need enrichment)
SELECT waypoint_code, first_seen_at
FROM stats.waypoints
WHERE lat IS NULL AND source = 'UK'
ORDER BY first_seen_at DESC
LIMIT 50;

-- Count total registered waypoints
SELECT COUNT(*) AS total_waypoints FROM stats.waypoints;

-- Waypoints in a specific country
SELECT waypoint_code, source
FROM stats.waypoints
WHERE country = 'PL'
ORDER BY waypoint_code;

Graph / Visualization Specification¶

Unlocked visualisations:

1. Waypoints by source — Donut chart
2. Data: SELECT source, COUNT(*) FROM stats.waypoints GROUP BY source

3. Segments: GC (green), OC (blue), UK (orange)

4. Cache discovery over time — Line chart

5. Data: SELECT DATE_TRUNC('month', first_seen_at) AS month, COUNT(*) FROM stats.waypoints GROUP BY 1 ORDER BY 1
6. x-axis: months, y-axis: new waypoints registered

ASCII Sample — Waypoints by Source:
GC  ████████████████████ 64%   (241,000)
OC  ████████             26%   (97,000)
UK  ███                  10%   (37,000)
Total: 375,000 waypoints

TimescaleDB Assessment¶

NOT recommended. stats.waypoints is a dimension table with a surrogate BIGSERIAL PK, not a time-series. first_seen_at is informational, not a partitioning key. The table grows by insertion of new unique codes (rare after initial seeding). Standard PostgreSQL with a B-tree unique index on waypoint_code is sufficient. TimescaleDB would add overhead with no benefit.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration file 20260310400000_create_waypoints.php
• 2. Verify PHP class name is CreateWaypoints (matches Phinx convention)
• 3. Run phinx migrate — confirm no errors
• 4. \d stats.waypoints — verify all 7 columns present with correct types
• 5. Verify uq_waypoints_code UNIQUE constraint exists
• 6. Verify chk_waypoints_source CHECK constraint exists
• 7. Test CHECK: INSERT INTO stats.waypoints (waypoint_code, source) VALUES ('TEST', 'ZZ') → expect error
• 8. Test UNIQUE: insert same waypoint_code twice → expect error
• 9. Run pgTAP tests T-4.1.001 through T-4.1.011

• 10. Verify phinx rollback drops the table cleanly (no orphaned objects)

• • [DBA ] DBA file reviewed and approved: task-S4T01.dba.md
• Migration file 20260310400000_create_waypoints.php created
• Migration runs successfully (phinx migrate)
• All T-4.1.xxx pgTAP tests pass
• Migration rolls back successfully (phinx rollback)
• Spec sign-off: table matches data contract above