Stat-Dashboard/mockups/README.md

PostgreSQL-backed development mockups

This existing mockup is the reference product to port into production. See PRODUCTION_IMPLEMENTATION.md for the porting inventory, endpoints, Roblox payload/schedule contract, AI prompts, and production acceptance gates.

The HTML prototype now reads PostgreSQL data. The temporary Python service is a read-only development API; the planned production stack remains Lapis and React/TypeScript.

Run on Windows

Double-click start-dashboard.bat in the repository root, or run python start_dashboard.py. The launcher sets up missing dependencies on first run, starts PostgreSQL and the backend, shows backend output in its terminal, and opens the dashboard in your browser. Close that terminal or press Ctrl+C to stop the backend and local PostgreSQL together. Windows process ownership also prevents background children being left behind if the terminal is closed abruptly. An already running local sample database is restarted under the launcher's ownership. A DATABASE_URL server is external and is never stopped. Browser tabs opened in an existing browser can be closed normally.

Use start-dashboard.bat --no-browser to keep the browser from opening. Only one launcher per checkout can run at a time. If another backend already occupies port 8011, close that backend's terminal first; the launcher reports the conflict.

From the repository root:

powershell -ExecutionPolicy Bypass -File mockups/setup-database.ps1
powershell -ExecutionPolicy Bypass -File mockups/start-mockups.ps1

Setup creates .venv, installs requirements.txt, and provisions PostgreSQL 18.6 from the official EDB binary distribution. The local database listens only on 127.0.0.1:55432. Its generated password, connection settings, binaries, and data directory stay in ignored .local/ files. Setup applies migrations and seeds the sample once; running it again preserves the existing data.

An existing database can be used by setting DATABASE_URL before running setup. It must be a development database where the configured role can create the analytics schema. No local PostgreSQL download is performed when this environment variable is set.

Open http://127.0.0.1:8011/mockups/overview-mockup.html. The game selector switches between three independent game datasets. For manually running Python commands, use .venv/Scripts/python.exe so the PostgreSQL driver is available.

Sample data

The benchmark-informed generator creates 90 days of synthetic records relative to setup time, with 18,000 players across three games. Most players have one session; return rates decline sharply over time. Session lengths, progression, purchases, activity funnels, and gamepass ownership vary by player behavior. A small heavy-spending group accounts for a large share of revenue. Visits and CCU come from the same session histories.

See SAMPLE_DATA.md for public Roblox references and model assumptions. These are synthetic histories, not imported real player data. Run manage_database.py status for current record counts.

To replace only the three bundled sample datasets while keeping game IDs and unrelated projects:

.venv/Scripts/python.exe mockups/manage_database.py reset-sample
.venv/Scripts/python.exe mockups/sample_report.py

There are deliberately missing names, snapshot fields, metric reports, session end times, and one missing Roblox experience ID. PostgreSQL keeps unknowns as NULL or absent JSON properties; the UI displays N/A. Zero remains zero, incomplete sessions do not get an invented duration, and missing chart observations appear as gaps.

Game roots and data organization

games.id is the internal database identity and the root of every dataset. games.roblox_experience_id is an optional property of that root. The IDs are not interchangeable.

• players and sessions are scoped to game_id. Sessions reference an internal player ID.
• player_snapshots keeps timestamped copies of custom player data in JSONB.
• events is categorized and always belongs to a game. Its player and session references are optional, so announcements, server events, and other unbound data stay directly under the game.
• game_metrics holds named, categorized time samples such as visits, CCU, and server performance, without a player reference.
• activity_definitions and activity_runs support aggregate journey analytics and individual player history.
• products stores the Roblox product ID and catalog metadata. product_sales stores each unique receipt with time, actual unit price, quantity, product, and player/session when available. Updating catalog prices does not rewrite historical sales.

Composite foreign keys enforce game boundaries for player, session, activity, and product relationships. Source migrations are in db/; manage_database.py init applies unapplied migrations and seeds an empty sample set without deleting other records.

API

Start with GET /api/games, then use the returned internal ID:

/api/games/{game_id}
/api/games/{game_id}/overview
/api/games/{game_id}/sessions
/api/games/{game_id}/engagement
/api/games/{game_id}/retention
/api/games/{game_id}/events
/api/games/{game_id}/metrics
/api/games/{game_id}/players
/api/games/{game_id}/players/{internal_player_id}
/api/games/{game_id}/activities
/api/games/{game_id}/monetization

Analytics accepts days=7|14|30|90 (any integer from 1 to 90 is supported). Players supports page, page_size (default and maximum 20), sort, direction=asc|desc, q, ranking for the fourth Hall of Fame stat, and segment=all|whales|playtime|first. Sorting and pagination happen in the backend; unknown values sort last in either direction. Activity analytics accepts activity_id and the same segment names. Player detail accepts value and value2 for its comparison graph.

Player records use /api/games/{game_id}/players/{player_id}/{snapshots|sessions|activities|events|purchases}?page=1, always 20 per page and newest first. Repeated event parameters filter event names; repeated product parameters filter internal product IDs. Filters apply before pagination. The graph uses chronological snapshot timestamps independently of the table page. Other aggregate tables also have 20-row page controls.

/api/games/{game_id}/prompts supplies the Product prompts monetization tab. Prompt interactions are independent of activity runs. Owned gamepasses and product totals appear in separate player-overview cards. The Players Hall of Fame shows four top-five rankings with avatar placeholders; production avatar loading is not connected.

Prompt analytics accepts source=all|game|player. New interactions default to unknown unless an explicit trigger_source is reported. The prompt-source comparison always shows all sources for context, while product metrics follow the filter. Charts with more than two lines support search, per-line toggles, and All/None controls.

All API values are queried from PostgreSQL; there is no in-memory sample-data fallback. Database errors return HTTP 503, and the UI shows an error and N/A. The HTTP server serves only HTML/CSS/JS assets, not credentials, database files, Python, or SQL source.

Pages

Overview now starts with a broad Everything at a glance analysis, with daily/weekly numerical reports underneath and no Game health section header. It interprets audience/concurrency/spending relationships, engagement strengths, onboarding weaknesses, activity/progression friction, payer concentration, offer placement, and priorities. Matched saved-player values add economy/progression context. The former Summaries navigation/page is removed; old summaries-mockup.html links redirect to Overview preserving their query string. Structured topic evidence remains available internally in the report payload.

Overview includes daily Game Health sample-analysis cards for yesterday and the past seven complete UTC days. Each game has separate text and evidence. /api/games/{game_id}/reports returns the current pair; PostgreSQL caches them by game/date/window, and the backend checks for a new UTC day every minute. Reports catch up after downtime. No AI key or model call is needed for these sample narratives. The public API remains read-only for ingestion/settings; report-cache writes are internal server maintenance.

Overview, Sessions, Retention, Events, Players, Player info, Activity sessions, Monetization, and Project settings follow the pagename-mockup.html convention. Login remains a visual prototype. All pages share mockup.css; mockup.js loads the shared data-ui.js module. Activity definition editing remains a UI draft; this service does not expose an ingestion or settings-write API.

See METRICS.md for calculation definitions, denominators, and handling of unavailable data.

Monetization includes a Gamepasses tab (gamepasses-mockup.html) with ownership percentages and reporting coverage. Events shows daily lines per event and aggregate summaries. Sessions supports player-group filtering; activity journeys display stage reach and drop-off. Individual session, event, activity, and purchase records remain accessible in player info.

Page layouts are generated by build_pages.py. After editing the generator, run .venv/Scripts/python.exe mockups/build_pages.py from the repository root to refresh the HTML pages.

Checks and database control

.venv/Scripts/python.exe mockups/manage_database.py status
.venv/Scripts/python.exe -m unittest discover -s mockups/tests -v

The integration checks insert fixture records in transactions and roll them back. They verify known metric arithmetic, missing-data behavior, historical sale prices, receipt deduplication, game isolation, player lookup, and missing-last sorting.

To stop only the local sample database:

.local/pgsql/bin/pg_ctl.exe -D .local/pgdata -m fast stop

Project settings includes a Data quality log with category filters and pages of 20. Missing/unresolved records remain stored and visible there while regular prompt/activity analytics use known results. Report warning notes, unresolved columns, and unknown-source prompt comparisons are omitted from the main views. Recent open sessions remain visible; stale unended sessions appear in the diagnostic log.