Data model

The main Postgres and TimescaleDB tables, what each holds, which history can never be rebuilt, how big it grows, and how the schema is changed.

Platform / How the platform works

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.

The market tables. A creator owns many universes. Each universe has many snapshots, many sort appearances, many recommendation observations and many recorded changes. Each sort has many sort appearances. Each universe has one row of derived metrics and one hourly aggregate row per clock hour, both computed from its readings.

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 no last_snapshot_at was 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. ts is 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), the ccu the sort showed, and is_sponsored. One reading shares one ts across 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:

TableKept
github_collection_snapshots90 days, and always an organization's newest
universe_changes180 days
github_collection_cache30 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

TablesIf lost
snapshots, sort_appearances, recommendation_observations, universe_changesGone for good. Roblox serves no history
universes, creators, sortsFound again by the collector within a day
universe_metrics, ccu_hourlyRecomputed from the history in minutes
collector_runs, invariant_resultsDisposable

People, sign-in and audit tables

users

Everyone who has signed in: GitHub id and login, display name, avatar, role (owner or member), when GitHub last confirmed their membership (membership_verified_at) and last tried (membership_checked_at). The email column holds a made-up github-<id>@users.invalid that 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_state does 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_counts holds 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 (release or bonus) 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-checks job: 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.

TableSize thenGrows by
snapshots1.28 GB, 9.7 million rowsAbout 500,000 rows, 70 MB a day
sort_appearances669 MBAbout 44 MB a day before compress
recommendation_observations161 MBAbout 10 MB a day
universes104 MB, 117,000 games500 to 1,500 games a day
universe_changes95 MB, 390,000 rowsAbout 15,000 rows a day
github_collection_snapshots65 MB, 919 documents of ~69 kBOne document per stored run
collector_runs49 MBAbout 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

  1. 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.

  2. Generate the migration

    Run pnpm db:generate --name <what> and read the SQL it writes in packages/db/drizzle/. TimescaleDB features (hypertables, aggregates, policies) are written by hand after the generated statements.

  3. Teach the tests the table

    Add it to TABLES in packages/testing/src/database.ts, add a fixture, and a row in the seeded market if lists should show it.

  4. Apply it locally

    Run pnpm db:migrate, then the integration tests of every app that reads the table.

  5. 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