five-on-it / apps/api/src/postgres
apps/api/src/postgres
The store in Postgres (TECHNICAL_SPECS §9.10), what the Compose stack runs (FOI_STORE=postgres, DATABASE_URL). Drafts, builds and game versions are kept whole, as JSONB documents keyed by their ids: a field the API adds to a draft or a build needs no migration. So are the ratings, the reports, the games' standings, the audit log and OAuth's clients, codes, grants and refresh tokens. The columns beside a document are only its keys (and, for the reports and the audit log, a sequence that keeps their order; for OAuth's codes and tokens, when they expire and when they were used, a column one update sets once, so a code used twice is caught however the requests interleave), and the indexes are on what the queries look up (a draft's owner, a build's draft, the public games, the reports by state).
The job queue's jobs (queue.ts) keep beside their document the columns a claim and a lease are decided on (the kind, the owner, the state, the priority, the attempts, the lease, the times): a claim locks the next in line with FOR UPDATE SKIP LOCKED, so two processes claiming at once take two jobs, and a lease is renewed or a job settled only by the attempt that holds it. The daily quotas are a row per account, quota and day, spent in one statement (insert … on conflict do update … where used < limit); the limits admins set are a document per account, and so is each Steam account's entitlement (beside it the account it signs in to, unique, and when it was last seen and checked: the sweep's query). A creation is claimed only for an owner with none running (an index holds them to one, and a claim that loses that race tries again), and the ledger of the platform model's spend is a row per creation, its amounts in numbers of micro-dollars' precision, summed in one query; each creation's transcript is a document beside when it was kept, which the 30 days' purge deletes by.
The standings (respect.ts) are a document per account beside its Respect (the all-time leaderboard's order); the ledger a row per change, its id its key (a run's award is run:<run>:<account>, and a partial unique index on the run and the account holds it to one), beside its account, season and Respect (the season's leaderboard sums them); a line is applied in a transaction that locks its account's standing, once (a line already in the ledger changes nothing); a run's award is one transaction per account, its standing's row held (SELECT … FOR UPDATE) while the day's repeat is counted from its ledger, so two awards of one account never both count the same day's runs (the API assumes one process anyway: the lobbies live in its memory). A mission version's tally is a row of counts and its distinct players a row each, a run counted once by a row of its own; endorsements and the players' ratings of missions are rows keyed by what makes them one. An erased account's place among a mission's players is kept under an anonymous id, so the counts stay.
The schema is a short ordered list of migrations, run at start inside an advisory lock (two API processes starting together migrate once) and recorded in foi_migrations; a migration, once released, is never edited: a change is a new one at the end. JSONB keeps an object's keys in its own order, so a document read back equals the one saved but may list its keys differently; a build of it is still the same artifact every time.