Task S4T05: Create stats.user_cache_visits Table¶

Sprint Context¶

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

Source¶

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

Purpose & Scope¶

Creates stats.user_cache_visits, which records how many times each user's moves have been associated with each waypoint/cache. This enables per-user cache interaction analytics:

• "How many distinct caches has this user visited?"
• "What are the top caches visited by users?"
• "Has a user visited a specific cache recently?"

The structure is symmetric to stats.gk_cache_visits but keyed by user_id rather than gk_id.

Create stats.user_cache_visits to pre-aggregate per-user per-waypoint visit counts. Symmetric to stats.gk_cache_visits but from the user perspective.

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

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400400_create_user_cache_visits.php

Full SQL DDL¶

CREATE TABLE stats.user_cache_visits (
  user_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 (user_id, waypoint_id),
  CONSTRAINT fk_user_cache_visits_waypoint
    FOREIGN KEY (waypoint_id) REFERENCES stats.waypoints(id)
    DEFERRABLE INITIALLY DEFERRED
);

COMMENT ON TABLE stats.user_cache_visits
  IS 'Per-user per-waypoint visit counter; enables user cache analytics without gk_moves scans';
COMMENT ON COLUMN stats.user_cache_visits.user_id
  IS 'User internal ID (references geokrety.gk_users.id; no cross-schema FK)';
COMMENT ON COLUMN stats.user_cache_visits.visit_count
  IS 'Number of moves by this user at this waypoint; incremented by trigger';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateUserCacheVisits extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.user_cache_visits (
  user_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 (user_id, waypoint_id),
  CONSTRAINT fk_user_cache_visits_waypoint
    FOREIGN KEY (waypoint_id) REFERENCES stats.waypoints(id)
    DEFERRABLE INITIALLY DEFERRED
);

COMMENT ON TABLE stats.user_cache_visits
  IS 'Per-user per-waypoint visit counter; enables user cache analytics without gk_moves scans';
COMMENT ON COLUMN stats.user_cache_visits.user_id
  IS 'User internal ID (references geokrety.gk_users.id; no cross-schema FK)';
COMMENT ON COLUMN stats.user_cache_visits.visit_count
  IS 'Number of moves by this user at this waypoint; incremented by trigger';
SQL
        );
    }

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

Data Contract¶

Design note: Anonymous moves (author IS NULL on gk_moves) do NOT create rows in this table. Only moves with a non-NULL author contribute. This is enforced by the trigger (S4T08).

SQL Usage Examples¶

-- How many distinct caches has a user visited?
SELECT COUNT(*) AS distinct_caches
FROM stats.user_cache_visits
WHERE user_id = 9876;

-- Top caches by total user visits (globally)
SELECT w.waypoint_code, SUM(uv.visit_count) AS total_visits, COUNT(DISTINCT uv.user_id) AS distinct_users
FROM stats.user_cache_visits uv
JOIN stats.waypoints w ON w.id = uv.waypoint_id
GROUP BY w.waypoint_code
ORDER BY total_visits DESC
LIMIT 10;

-- Has a specific user visited a specific cache?
SELECT visit_count, first_visited_at, last_visited_at
FROM stats.user_cache_visits uv
JOIN stats.waypoints w ON w.id = uv.waypoint_id
WHERE uv.user_id = 9876 AND w.waypoint_code = 'GC1A2B3';

-- Most active users by number of distinct caches
SELECT user_id, COUNT(*) AS distinct_caches
FROM stats.user_cache_visits
GROUP BY user_id
ORDER BY distinct_caches DESC
LIMIT 20;

-- Users who visited the most caches in a given country
SELECT uv.user_id, COUNT(DISTINCT uv.waypoint_id) AS caches_in_country
FROM stats.user_cache_visits uv
JOIN stats.waypoints w ON w.id = uv.waypoint_id
WHERE w.country = 'PL'
GROUP BY uv.user_id
ORDER BY caches_in_country DESC
LIMIT 10;

Graph / Visualization Specification¶

1. Cache visit distribution by user — Histogram
2. Data: SELECT COUNT(*) AS distinct_caches, COUNT(user_id) AS users FROM stats.user_cache_visits GROUP BY user_id ORDER BY distinct_caches

3. Shows how many users visited 1, 2-5, 5-10, 10-50, 50+ caches

4. Top caches by unique users — Bar chart

5. Data: SELECT w.waypoint_code, COUNT(DISTINCT uv.user_id) FROM stats.user_cache_visits uv JOIN stats.waypoints w ... GROUP BY ... ORDER BY ... LIMIT 20
6. x-axis: cache code, y-axis: unique user count

ASCII Sample — Cache visit distribution:
Visited 1 cache:    45,231 users (30%)
Visited 2-5:        38,192 users (26%)
Visited 6-20:       31,440 users (21%)
Visited 21-100:     25,000 users (17%)
Visited 100+:        9,000 users  (6%)

TimescaleDB Assessment¶

NOT recommended. Same reasoning as stats.gk_cache_visits — this is a compressed rollup keyed by (user_id, waypoint_id). No time-series dimension on the PK. Standard PostgreSQL is optimal.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Verify S4T01 (stats.waypoints) migration applied
• 2. Create migration 20260310400400_create_user_cache_visits.php
• 3. Run phinx migrate — confirm no errors
• 4. \d stats.user_cache_visits — 5 columns with correct types
• 5. Verify PK on (user_id, waypoint_id)
• 6. Verify FK is DEFERRABLE INITIALLY DEFERRED
• 7. Table should be empty
• 8. Run pgTAP tests T-4.5.001 through T-4.5.010

• 9. Verify phinx rollback drops table cleanly

• • [DBA ] DBA file reviewed: task-S4T05.dba.md
• S4T01 applied
• Migration 20260310400400_create_user_cache_visits.php created
• Migration runs successfully
• All T-4.5.xxx tests pass
• Rollback verified