The schema is the value of this skill. Install, driver setup, config and migrations come from the vendor: use the neon-drizzle skill if it is installed, otherwise https://orm.drizzle.team/docs/get-started.
Target the stable Drizzle line, drizzle-orm 0.45.x. The Drizzle docs install lines use drizzle-orm@rc and drizzle-kit@rc; pin the 0.45 versions instead (see "Drizzle 1.0" below).
Conventions
Every table and query in a game project follows these. Other skills (stripe-game-payments, gameplay-analytics, game-economy-design) build on them.
- Naming. Tables are plural snake_case (
item_definitions). Columns are snake_case in SQL and camelCase in TypeScript:playerId: text("player_id"). Indexes are named<table>_<columns>_idx. - IDs.
text("id").primaryKey().$defaultFn(() => crypto.randomUUID()). Player-facing identifiers are never auto-increment integers; sequential ids let anyone enumerate players and items. - Timestamps.
timestamp(...).notNull().defaultNow(). Entity and catalog tables (players,item_definitions,guilds) carrycreatedAtandupdatedAt; append-only and state rows carry the time that matters (events.createdAt,sessions.startedAt,guild_members.joinedAt). Give a new mutable table both columns. SetupdatedAtin every update; a column default fills it only on insert. - Soft delete.
playershasdeletedAt: timestamp("deleted_at"); a deleted player keeps their rows for history. Every read that returns players, including joins, filtersisNull(players.deletedAt). AdddeletedAtto a new entity when its history matters (templates/entity-template.tspattern 1). - JSONB. Typed columns for anything you filter, sort, join or sum.
jsonb(...).$type<...>().default({})for per-genre extras:stats,baseStats,properties,metadata. Never add genre columns (swordDamage) to core tables. Usejsonb, notjson: PostgreSQL storesjsonbdecomposed, processes it faster and can index it. - Enums.
pgEnumfor fixed sets that code switches on (rarity, guild role, leaderboard period). Plaintextfor open sets designers extend (event type, currency type). - Foreign keys.
onDelete: "cascade"from player-owned rows toplayers. Analytics rows (events) useonDelete: "set null"so history survives. Catalog references (itemDefinitionId) have no cascade. - Indexes. Index the owner key (
playerId) of every player-owned table. Add a composite index for each common filter, leading with the owner:events (playerId, type),leaderboards (category, period, score). Put a unique index on every natural key an upsert targets (currencies (playerId, currencyType)): PostgreSQL infers theON CONFLICTtarget from a unique index. - Money and currency. Integer amounts, never read-modify-write. Credit with the atomic
amount = amount + nupsert (addCurrencyintemplates/query-patterns.ts). Debit withspendCurrency: oneUPDATE ... WHERE amount >= cost, and it reports failure when no row was updated, so two parallel spends cannot overdraw. Thecurrenciestable also hasCHECK (amount >= 0), so a debit that bypasses the guard fails instead of going negative. - Idempotency.
processed_eventsholds one row per external event or job already applied (a Stripe event id, a reward job key), withidas the primary key, akindandcreated_at. Insert it in the same transaction as the change it guards; a primary-key conflict means the change already happened.stripe-game-paymentsandbullmq-game-queuesuse this table.
Characters and ownership
players is the account, keyed to the auth user. characters holds the several characters one account owns, with a name unique per shard among live characters (characters_shard_name_idx is a partial index on deleted_at IS NULL, so deleting a character frees its name; the schema comment says how to reserve names or make them global). The slot limit is a game rule; check it in code.
When the game has characters, the character owns level, xp and stats, and you delete those three columns from players, along with players_level_idx, and point getGuildWithMembers in templates/query-patterns.ts (it selects players.level) at the character. A single-character game skips characters and keeps them on players.
The game decides whether wallets and inventory belong to the account or to a character. The schema ships them on the account (playerId). To move a table to characters:
- Replace its
playerIdcolumn withcharacterIdreferencingcharacters.id(keeponDelete: "cascade"), and repoint its indexes. - Keep the
CHECK (amount >= 0)and the guardedspendCurrencyas they are. - In
templates/query-patterns.ts, changespendCurrency,addCurrency(including itsON CONFLICTtarget),getPlayerCurrenciesand the inventory queries to takecharacterIdand filter on it. They filter byplayerIdtoday, so they read and spend the wrong wallet until you do.
A game can also keep both columns and use the character one for per-character rows.
Guild membership stays one guild per player, since a guild is a permanent group and short-lived parties live outside this table. To allow several guilds, see the comment on guild_members_player_idx. Alts also need game rules (gear transfer, risk diversion) that no table enforces.
Procedure
- Copy
boilerplate/schema.tsinto the project's schema module. Delete tables the game does not need. - Add game entities from
templates/entity-template.ts, following the conventions above. - Generate and apply migrations with drizzle-kit as the Drizzle docs describe. Change the schema file first, then generate; never hand-write migration SQL.
- Write queries in the style of
templates/query-patterns.ts, with the database passed in.
Drizzle 1.0
Drizzle 1.0 is a release candidate (npm rc tag), not the latest tag. If a project moves to it, these parts change (https://orm.drizzle.team/docs/v0-v1-changes):
relations()is replaced bydefineRelations(), passed to the client asdrizzle({ client, relations }). The relational query API v1 (db.querywith aschemaoption) is removed.drizzle({ casing })moves to a table-level API (snakeCase.table(...)).- Migration folders drop
journal.json; runningdrizzle-kit upupgrades the migration table. drizzle-orm/pg-coreexportsPgAsyncDatabaseand noPgDatabase, so theGameDbtype intemplates/query-patterns.tsmust be redone.
Pitfalls
- One JSON blob for all game state. Normalize into tables; keep JSONB for the extensible fields only.
- Missing composite index. "A player's events of type X" needs
events (playerId, type), not two single-column indexes. Add the composite index with the query that needs it. - Filtering on a JSONB key in a hot path. A GIN index on a
jsonbcolumn serves containment (properties @> '{"key": "value"}'), notproperties ->> 'key' = .... For a hot filter, query with@>against a GIN index, add an expression index on that key, or promote the key to a typed column. - Forgetting soft-deleted players. Leaderboard and guild queries join
players; filterisNull(players.deletedAt)there too, astemplates/query-patterns.tsdoes. - Running migrations from two git worktrees at once. Worktrees share one Neon database, so concurrent migrations conflict. Only one worktree runs migrations at a time. Use separate Neon branches for experimental schema work.
Files
boilerplate/schema.ts: copy when starting a game database. Thirteen tables (players, characters, sessions, item definitions, inventory, events, leaderboards, achievements, currencies, social relations, guilds, guild members, processed events) and their enums.templates/entity-template.ts: copy when adding a new game entity. Three patterns: an entity a player owns, a definition catalog with player instances, and an enum-driven timed entity.templates/query-patterns.ts: read when writing queries against the schema: inventory join, leaderboard top N, event aggregation, JSONB filters, atomic currency credit, guarded spend, best-score upsert. Each function takes the database as its first argument (GameDb, any Drizzle Postgres database), so it works with whichever driver the project uses.
Related skills
redis-game-patternsfor cached leaderboards and presence.bullmq-game-queuesfor async event processing.betterauth-integrationfor auth tables.database-performance-tuningfor slow queries.
Verified citations are in references/sources.md; read it when checking where a claim comes from.