113 lines
10 KiB
Markdown
113 lines
10 KiB
Markdown
# PostgreSQL-backed development mockups
|
|
|
|
This existing mockup is the reference product to port into production. See [PRODUCTION_IMPLEMENTATION.md](../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
|
|
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](https://www.enterprisedb.com/download-postgresql-binaries). 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](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:
|
|
|
|
```powershell
|
|
.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:
|
|
|
|
```text
|
|
/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](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
|
|
|
|
```powershell
|
|
.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:
|
|
|
|
```powershell
|
|
.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.
|