Everything the platform knows lives in one PostgreSQL 17 database with the TimescaleDB extension. The schema is written in Drizzle in packages/db/src/schema.ts and applied by the SQL migrations in packages/db/drizzle/, which the migrate job runs before every deployment. This page names the main tables and what each holds; the column-by-column reference is the vault note Data Model.
How the market tables fit together
A game is a universe (Roblox's word for an experience). Every reading of it is a row in snapshots; every other market table hangs off the universe the same way. Derived tables (universe_metrics, ccu_hourly) are computed from the history and can be rebuilt; the history itself cannot.
Roblox numbers users and groups separately, so user 5 and group 5 are different creators: a creator is always identified by its type and id together.
Market tables: the history the collector writes
- universes
One row per tracked game: its current name, creator, genres, the latest players, visits, favorites and votes, the observed peak (
peak_ccu,peak_ccu_at), its tier and pin (tier,tier_pinned_until), when it is next due (next_poll_at), how it was found (discovered_via) and how many polls in a row Roblox left it out (miss_count). A row with nolast_snapshot_atwas found but never read, and is left out of every list.- snapshots
A game's player count and counters at one moment:
universe_id,ts,ccu(players),visits,favorites,up_votes,down_votes(votes null when they could not be read that time). Append-only: never updated.tsis when the numbers were fetched, not when the job was queued. The history.- sort_appearances
A game's rank in one of Roblox's sorts at one reading:
ts,sort_id,universe_id,rank(from 1), theccuthe sort showed, andis_sponsored. One reading shares onetsacross every sort.- recommendation_observations
One "this game recommends that game" edge at a moment:
ts,src_universe_id,dst_universe_id,rank. Sponsored recommendations and a game recommending itself are not stored.- universe_changes
One row each time a tracked field differs from the previous snapshot: name, description,
genre_l1,genre_l2, icon (compared by the image's content), max players, price and Roblox's noisy "updated" time. No row for a game's first snapshot. Kept 180 days.- creators
One row per Roblox user or group that owns a tracked game.
- sorts
One row per Roblox discovery sort, with when it was first and last seen.
- rolimons_listings
Rolimon's game list: one row per root place with Rolimon's player count, when it was first and last seen, and the universe it was matched to.
Derived tables: computed from the history
- universe_metrics
One row per game, rebuilt every 5 minutes by the worker's
metrics-tick: the comparison readings and growth over 1 hour, 6 hours, 24 hours and 7 days, the 24-hour averages and daily growth, velocity, acceleration, rising hours, volatility, peak ratio, the breakout score, visits and favorites gained in a day and the like ratio. A null means unknown, never zero.- ccu_hourly
A TimescaleDB continuous aggregate over
snapshots: for each game and UTC clock hour, the mean players, the highest, and how many readings there were. TimescaleDB refreshes it every 10 minutes for the last 3 days. The breakout signals, the market's players over time and players by hour of the week read it. See hourly means.
Hypertables and compression
snapshots, sort_appearances, recommendation_observations and collector_runs are
hypertables: TimescaleDB splits each by time into chunks and compresses older chunks. Compression
barely helps snapshots, whose daily chunks hold about five readings per game, but shrinks a week
of sort_appearances from about 310 MB to a few MB. ccu_hourly has a covering index on
(universe_id, bucket DESC) that carries the mean, so the market's player series (about a
thousand games' hours at once) are read from the index alone.
How long rows are kept
Most tables keep everything. The worker's daily data-retention job deletes three kinds of row
that would otherwise only grow, a few thousand at a time so nothing waits on it:
| Table | Kept |
|---|---|
github_collection_snapshots | 90 days, and always an organization's newest |
universe_changes | 180 days |
github_collection_cache | 30 days after a run last refreshed the answer |
The market's history (snapshots, ccu_hourly, sort_appearances,
recommendation_observations) and the studio's own analytics are never deleted by it. Each
window is a worker setting (RETENTION_*_DAYS).
Which tables can be rebuilt and which cannot
| Tables | If lost |
|---|---|
snapshots, sort_appearances, recommendation_observations, universe_changes | Gone for good. Roblox serves no history |
universes, creators, sorts | Found again by the collector within a day |
universe_metrics, ccu_hourly | Recomputed from the history in minutes |
collector_runs, invariant_results | Disposable |
People, sign-in and audit tables
- users
Everyone who has signed in: GitHub id and login, display name, avatar, role (
ownerormember), when GitHub last confirmed their membership (membership_verified_at) and last tried (membership_checked_at). The email column holds a made-upgithub-<id>@users.invalidthat nothing writes to.- sessions
One row per signed-in browser. Only a hash of the token (
h1:and its SHA-256) is stored, so a copy of the table opens nothing.- auth_accounts
A person's link to GitHub, with their GitHub access token encrypted, used only to re-check membership.
- auth_verifications
A sign-in in progress: its single-use state and PKCE verifier, ten minutes at most.
- auth_events
The audit log: sign-ins, refusals, sign-outs, ended sessions, expiries, lost memberships, role changes, owners' group-key changes and game keys created, rotated or revoked. Never a token or a key. Kept 180 days.
Personal tables: watchlist, home, notifications and devices
- watchlist_items
One row per person and game they watch; watchlist notifications go to these people. Up to 500 games each.
- home_pages
A person's pages of widgets: name, icon, position, the widget layout as validated JSON. Up to 20 each.
- home_favorites
The panel pages a person starred. Up to 30 each.
- notifications
A note to one person: kind, title, body, a panel path, the game it is about, when written, when read, and
pushed_at(when the push job took it; null until then).- notification_preferences
Only the kinds a person turned off or on; a kind without a row follows its default.
- notification_cooldowns
When a person was last told one kind about one subject, so a repeat inside the kind's wait is dropped.
- notification_detector_state
What notification detection remembers between runs: how far it read and the last value it compared each game against.
- push_subscriptions
A phone or browser a person turned push on for: the push service's endpoint (never shown or logged), the browser's keys, a label, the last success, failures in a row and when it may be tried again. Up to ten each.
Studio tables: catalog, groups, owned analytics and projects
- studio_catalog
One document listing the studio's games with their release state, saved with a revision number; every save is also an immutable row in
studio_catalog_events.- studio_groups
The Roblox groups the studio registered, each with exactly one key.
- provider_credentials
Each group's Roblox Open Cloud key, sealed with AES-256-GCM under the credential root key and bound to its row, with a 16-character fingerprint and what Roblox last said about it.
- studio_game_details
A studio game's lead, repository, target release date, pitch and notes.
- game_releases
The release log: launches, updates, hotfixes and live events, added and removed but never edited.
- game_daily_metrics
Owned analytics: one value per studio game, UTC day, metric and breakdown, with Roblox's quality flag. See Studio game stats.
- game_daily_breakdowns
Owned analytics split by one dimension (platform, country, age group, source), one value per slice a day.
- game_analytics_state
Whether each studio game's analytics can be read, and when they last were;
game_resource_statedoes the same for breakdowns, products, passes and bans.- game_store_items
Developer products and game passes as last read, never deleted; price and sale changes go to
game_store_item_changes.- game_bans
Bans as last read, private reasons included;
game_daily_ban_countsholds how many were active each day.- studio_projects
One row per project, with its contract's state (
draft,submitted,approved), version, canonical terms and their hash, and its release (released_at,release_week), written once by the worker.- project_members
A project's developers and their parts, in basis points (1,000 to 10,000).
- project_signatures
Who signed which version of the terms, and how (drawn or typed); every change after submission deletes them all.
- project_documents
A contract's PDFs, the draft and the sealed final, every version kept.
- studio_points
One row per point earned (
releaseorbonus) with the studio week it counts from.- project_share_purchases
One row per share bought, spending exactly one point; one share per member per game.
Developer API and GitHub tables
- game_api_keys
A studio game's platform keys, the one in use and every retired one: the key's SHA-256 hash and last four characters, never the key. One key in use per game, enforced by a partial unique index.
- game_feedback
Player feedback a game's servers sent: category, text (1 to 2,000 characters, any key removed), player id, place, server version, status, stored once per idempotency key.
- github_collection_snapshots
One whole document of the GitHub organization per successful collection: members, repositories, pull requests with reviews, releases. Kept 90 days; an organization's newest is always kept.
- github_collection_cache
Each GitHub answer's ETag and body, so the next run can ask "has this changed?". An answer no run has refreshed in 30 days is deleted.
Operational tables: the run log and invariant checks
- collector_runs
One row per run of every worker job: when, how long, items, and on failure one readable sentence. A hypertable kept 30 days. See Jobs and queues.
- invariant_results
One row per database rule checked by the daily
invariant-checksjob: how many rows broke it, or why it could not run.
Rules the database enforces
The database refuses what cannot be true instead of trusting every writer (ADR-0037). Checks hold, for example, that a tier is one of the five names, that peak_ccu is at least ccu, that a game is dead exactly when miss_count is 3 or more, that a like ratio and peak ratio are between 0 and 1 and a breakout score between 0 and 100, and that a notification's kind is a known one. A value that breaks one is refused with 422 constraint_violated by the API. Rules that compare tables, or that are not enforced yet, are counted every day by the read-only invariant-checks job; it never repairs anything.
How big the database is
Measured on production on 2026-10-22, 23 days after collection began: 3.1 GB on a 50 GiB volume.
| Table | Size then | Grows by |
|---|---|---|
snapshots | 1.28 GB, 9.7 million rows | About 500,000 rows, 70 MB a day |
sort_appearances | 669 MB | About 44 MB a day before compress |
recommendation_observations | 161 MB | About 10 MB a day |
universes | 104 MB, 117,000 games | 500 to 1,500 games a day |
universe_changes | 95 MB, 390,000 rows | About 15,000 rows a day |
github_collection_snapshots | 65 MB, 919 documents of ~69 kB | One document per stored run |
collector_runs | 49 MB | About 3 MB a day, kept 30 days |
Nothing is deleted from the history tables; what to keep is a founder's decision.
Types every column follows
- bigint
Ids, visits, favorites and votes, which pass 32 bits; they stay below 2^53, so they are safe as JavaScript numbers. Raw SQL returns them as text and the API converts them.
- integer
Players (CCU) and ranks.
- timestamptz
Every time, stored in UTC.
- double precision
Ratios: growth, like ratio, peak ratio, volatility.
Changing the schema
Change the Drizzle schema
Add the table or column to
packages/db/src/schema.ts, with a comment saying what its rows mean and where they come from.Generate the migration
Run
pnpm db:generate --name <what>and read the SQL it writes inpackages/db/drizzle/. TimescaleDB features (hypertables, aggregates, policies) are written by hand after the generated statements.Teach the tests the table
Add it to
TABLESinpackages/testing/src/database.ts, add a fixture, and a row in the seeded market if lists should show it.Apply it locally
Run
pnpm db:migrate, then the integration tests of every app that reads the table.Document it
Describe the table in the vault's Data Model note in the same pull request.
Related: Architecture, API and caching, Jobs and queues, Metrics and data.
Updated