69 lines
2.5 KiB
SQL
69 lines
2.5 KiB
SQL
-- Individual games, one row per game actually played.
|
|
--
|
|
-- The Stern API exposes no per-game history feed. The only per-game signal is
|
|
-- "most recent game" (per title, and overall), so the collector captures each
|
|
-- distinct (title, played_at) tuple as it appears. (title_id, played_at) is the
|
|
-- natural key: the API reports the exact play timestamp, so re-observing the
|
|
-- same game is idempotent.
|
|
CREATE TABLE IF NOT EXISTS game_event (
|
|
title_id INTEGER NOT NULL,
|
|
played_at TIMESTAMPTZ NOT NULL,
|
|
score BIGINT NOT NULL,
|
|
title_name TEXT NOT NULL,
|
|
title_code TEXT,
|
|
first_seen_at TIMESTAMPTZ NOT NULL,
|
|
source TEXT NOT NULL,
|
|
PRIMARY KEY (title_id, played_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS game_event_played_at_idx
|
|
ON game_event (played_at DESC);
|
|
CREATE INDEX IF NOT EXISTS game_event_title_idx
|
|
ON game_event (title_id, played_at DESC);
|
|
|
|
-- Per-visit rollup from /api/v1/portal/user_activities/. This is aggregate data
|
|
-- (a session high score and a play count), kept because it is the only record of
|
|
-- games played before this collector started, and because num_plays is what
|
|
-- makes missed-game accounting possible.
|
|
CREATE TABLE IF NOT EXISTS play_session (
|
|
score_key TEXT PRIMARY KEY,
|
|
session_number INTEGER NOT NULL,
|
|
session_at TIMESTAMPTZ NOT NULL,
|
|
location_id INTEGER NOT NULL,
|
|
location_name TEXT NOT NULL,
|
|
title_name TEXT NOT NULL,
|
|
model_type TEXT NOT NULL,
|
|
num_plays INTEGER NOT NULL,
|
|
high_score BIGINT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS play_session_session_at_idx
|
|
ON play_session (session_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS collection_run (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
observed_at TIMESTAMPTZ NOT NULL,
|
|
events_captured INTEGER NOT NULL,
|
|
sessions_upserted INTEGER NOT NULL,
|
|
reported_total_plays INTEGER
|
|
);
|
|
|
|
-- Honest accounting: polling can only ever catch one game per title per
|
|
-- interval, so this reports how much of the real play history was captured
|
|
-- rather than implying the event table is complete.
|
|
CREATE OR REPLACE VIEW capture_coverage AS
|
|
SELECT
|
|
(SELECT count(*) FROM game_event) AS games_captured,
|
|
coalesce(
|
|
(
|
|
SELECT reported_total_plays
|
|
FROM collection_run
|
|
WHERE reported_total_plays IS NOT NULL
|
|
ORDER BY observed_at DESC
|
|
LIMIT 1
|
|
),
|
|
(SELECT sum(num_plays) FROM play_session),
|
|
0
|
|
) AS games_played_reported;
|