Task S4T04: Create stats.gk_cache_visits Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates stats.gk_cache_visits, which records how many times each GeoKret has visited each waypoint/cache, along with first and last visit timestamps. This enables instant answers to:

• "How many caches has this GeoKret visited?"
• "Which caches did this GeoKret visit?"
• "What is the most visited cache by a specific GeoKret?"

Without this table these answers require full scans of gk_moves. The FK to stats.waypoints(id) ensures only registered waypoints appear here.

Create stats.gk_cache_visits to pre-aggregate per-GeoKret per-waypoint visit counts. This eliminates gk_moves scans for cache popularity and GK travel analytics.

Scope: One table with FK to stats.waypoints. No triggers (added in S4T08). No seed data.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400300_create_gk_cache_visits.php

Full SQL DDL¶

CREATE TABLE stats.gk_cache_visits (
  gk_id             INT          NOT NULL,
  waypoint_id       BIGINT       NOT NULL,
  visit_count       BIGINT       NOT NULL DEFAULT 0,
  first_visited_at  TIMESTAMPTZ  NOT NULL,
  last_visited_at   TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (gk_id, waypoint_id),
  CONSTRAINT fk_gk_cache_visits_waypoint
    FOREIGN KEY (waypoint_id) REFERENCES stats.waypoints(id)
    DEFERRABLE INITIALLY DEFERRED
);

COMMENT ON TABLE stats.gk_cache_visits
  IS 'Per-GeoKret per-waypoint visit counter; enables cache analytics without gk_moves scans';
COMMENT ON COLUMN stats.gk_cache_visits.gk_id
  IS 'GeoKret internal ID (references geokrety.gk_geokrety.id, not FK to avoid cross-schema dep)';
COMMENT ON COLUMN stats.gk_cache_visits.waypoint_id
  IS 'FK to stats.waypoints(id); surrogated to allow rename/merge without cascading issues';
COMMENT ON COLUMN stats.gk_cache_visits.visit_count
  IS 'Count of moves referencing this waypoint for this GeoKret; incremented by trigger';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateGkCacheVisits extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.gk_cache_visits (
  gk_id             INT          NOT NULL,
  waypoint_id       BIGINT       NOT NULL,
  visit_count       BIGINT       NOT NULL DEFAULT 0,
  first_visited_at  TIMESTAMPTZ  NOT NULL,
  last_visited_at   TIMESTAMPTZ  NOT NULL,
  PRIMARY KEY (gk_id, waypoint_id),
  CONSTRAINT fk_gk_cache_visits_waypoint
    FOREIGN KEY (waypoint_id) REFERENCES stats.waypoints(id)
    DEFERRABLE INITIALLY DEFERRED
);

COMMENT ON TABLE stats.gk_cache_visits
  IS 'Per-GeoKret per-waypoint visit counter; enables cache analytics without gk_moves scans';
COMMENT ON COLUMN stats.gk_cache_visits.gk_id
  IS 'GeoKret internal ID (references geokrety.gk_geokrety.id, not FK to avoid cross-schema dep)';
COMMENT ON COLUMN stats.gk_cache_visits.waypoint_id
  IS 'FK to stats.waypoints(id); surrogated to allow rename/merge without cascading issues';
COMMENT ON COLUMN stats.gk_cache_visits.visit_count
  IS 'Count of moves referencing this waypoint for this GeoKret; incremented by trigger';
SQL
        );
    }

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

Data Contract¶

Constraints:

• PK: (gk_id, waypoint_id)
• FK: waypoint_id → stats.waypoints(id) — DEFERRABLE INITIALLY DEFERRED

Design note: gk_id has no FK to geokrety.gk_geokrety intentionally — cross-schema FKs have been avoided throughout to allow independent schema migrations. Referential integrity is maintained by the trigger.

SQL Usage Examples¶

-- How many distinct caches has a GeoKret visited?
SELECT COUNT(*) AS distinct_caches
FROM stats.gk_cache_visits
WHERE gk_id = 12345;

-- Which caches did a GeoKret visit, most recent first?
SELECT w.waypoint_code, cv.visit_count, cv.last_visited_at
FROM stats.gk_cache_visits cv
JOIN stats.waypoints w ON w.id = cv.waypoint_id
WHERE cv.gk_id = 12345
ORDER BY cv.last_visited_at DESC;

-- Most visited cache globally (used in v_uc10_cache_popularity)
SELECT w.waypoint_code, SUM(cv.visit_count) AS total_visits, COUNT(DISTINCT cv.gk_id) AS distinct_gks
FROM stats.gk_cache_visits cv
JOIN stats.waypoints w ON w.id = cv.waypoint_id
GROUP BY w.waypoint_code
ORDER BY total_visits DESC
LIMIT 10;

-- Caches visited multiple times by the same GK (repeat visitors)
SELECT w.waypoint_code, cv.visit_count
FROM stats.gk_cache_visits cv
JOIN stats.waypoints w ON w.id = cv.waypoint_id
WHERE cv.gk_id = 12345 AND cv.visit_count > 1
ORDER BY cv.visit_count DESC;

-- Top GeoKrety by cache diversity
SELECT gk_id, COUNT(*) AS distinct_caches_visited
FROM stats.gk_cache_visits
GROUP BY gk_id
ORDER BY distinct_caches_visited DESC
LIMIT 20;

Graph / Visualization Specification¶

1. Most visited caches — Horizontal bar chart
2. Data: SELECT w.waypoint_code, SUM(cv.visit_count) AS total FROM stats.gk_cache_visits cv JOIN stats.waypoints w ON w.id = cv.waypoint_id GROUP BY w.waypoint_code ORDER BY total DESC LIMIT 20

3. x-axis: total visit count, y-axis: waypoint code labels

4. Cache visit distribution — Histogram

5. Data: SELECT visit_count, COUNT(*) AS gk_count FROM stats.gk_cache_visits GROUP BY visit_count ORDER BY visit_count
6. Shows how many GKs visited each cache 1 vs 2 vs 3+ times (most: 1)

ASCII Sample — Top Caches:
GC1A2B3  ████████████████████  2,341 GK visits
GC00001  ██████████████████    1,992 GK visits
GC5ZZZZ  ████████████          1,234 GK visits
...

TimescaleDB Assessment¶

NOT recommended. stats.gk_cache_visits is a compressed rollup table with PK (gk_id, waypoint_id). There is no time-series dimension on the PK. first_visited_at and last_visited_at are metadata columns, not partitioning keys. All writes are INSERT ... ON CONFLICT DO UPDATE upserts on fixed PK rows. TimescaleDB hypertable partitioning is inappropriate.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Verify S4T01 (stats.waypoints) migration applied
• 2. Create migration 20260310400300_create_gk_cache_visits.php
• 3. Run phinx migrate — confirm no errors
• 4. \d stats.gk_cache_visits — verify 5 columns with correct types
• 5. Verify PK on (gk_id, waypoint_id)
• 6. Verify FK fk_gk_cache_visits_waypoint is DEFERRABLE INITIALLY DEFERRED
• 7. Table should be empty (no seed in this step)
• 8. Run pgTAP tests T-4.4.001 through T-4.4.010

• 9. Verify phinx rollback drops the table cleanly

• • [DBA ] DBA file reviewed: task-S4T04.dba.md
• S4T01 applied (waypoints table exists)
• Migration 20260310400300_create_gk_cache_visits.php created
• Migration runs successfully
• PK and FK verified
• All T-4.4.xxx tests pass
• Rollback verified