| name | cellar-data-model |
| description | Schema reference, id/externalId conventions, per-entity upsert semantics, the roomDictionary, drizzle-kit migration workflow, and shared-database discipline for comedy-cellar-bot's Supabase Postgres. Use when adding or changing a table/column in packages/core/sql/*.sql.ts, writing queries or upserts in packages/core/models/, running pnpm db generate/migrate, debugging duplicate or missing rows (comic name variants, N-plicated shows in /api/shows/new, shows silently not persisting), or bootstrapping/seeding an empty database. |
cellar-data-model — schema, identity, upserts, migrations
The backend stores scraped comedycellar.com ("CC") data and user preferences in one Supabase Postgres database via Drizzle ORM (a TypeScript SQL query builder; schema lives in code, migrations are generated SQL files). Schema files: packages/core/sql/*.sql.ts. Query/mutation helpers: packages/core/models/*.ts. Migrations: migrations/ (checked in, applied with drizzle-kit).
When NOT to use this skill: how data GETS INTO the DB from the scraper (endpoints, parsing, politeness) → cellar-scraping-reference; wrong data symptoms and triage → cellar-debugging-playbook; which changes need whose approval → cellar-change-control; stage/secret plumbing (DbUrl, sst shell) → cellar-config-and-secrets; running the app/crons → cellar-run-and-operate.
0. Mental model (60 seconds)
- 9 tables. Scrape-populated:
show, room, comic, act (show↔comic join). User-populated: user, show_notification, comic_notification, comic_to_user. Outbox: new_show_queue (written by ingestion, drained by the show-notification cron — §1, added 2026-07-13 #62).
- All SST stages (prod + dev) share this one database. Only
user rows are stage-partitioned. Destructive DB work from ANY stage is prod surgery. See §8.
- Show identity = CC's own numeric show id (used as our primary key). Comic identity = the exact name string CC prints. Both have sharp edges (§3).
- Notification tables are user-editable via
/api/settings. As of 2026-07-13 (#62), show_notification IS now read and fires real user emails: getShowNotificationRecipients (models/showNotification.ts:35-43) feeds ShowNotificationCron, which drains the new_show_queue outbox and emails opted-in users. Shipped but unproven — zero tests, no production track record. comic_notification is still never read — nothing sends "a comic I follow was booked", so that half remains the flagship unshipped feature (open problem: cellar-frontier-and-method).
comic_to_user (likes) is schema-only: no model file, no code reads or writes it anywhere in packages/ (verified by grep, 2026-07-07).
1. Schema reference (as of 2026-07-13; migrations 0000–0003 — new_show_queue added by 0003, 2026-07-13 #62; the 0000–0002 core was unchanged since 2025-02-05)
Every table except comic_to_user has: id serial PRIMARY KEY, externalId varchar(128) NOT NULL UNIQUE (app-generated, §2), createdAt timestamp DEFAULT now(). Columns below are the rest.
| Table | Schema file | Other columns | Constraints beyond externalId |
|---|
show | sql/show.sql.ts:16-41 | time varchar(8), description, forwardUrl, soldout bool, max int, special bool, roomId int NOT NULL → room.id (ON DELETE NO ACTION), cover int, note, mint bool, weekday int, totalGuests int, venueMin, venueMax, available int, timestamp int (unix seconds) | NONE — no unique on timestamp, despite code treating it as a lookup key (§3) |
room | sql/room.sql.ts:15-24 | name text NOT NULL, maxReservationSize int NOT NULL DEFAULT 4 | name UNIQUE |
comic | sql/comic.sql.ts:18-38 | img text NOT NULL, name text NOT NULL, description, website, enabled bool | name UNIQUE plus a second case-insensitive unique index nameUniqueIndex on lower(name) (comic.sql.ts:33-37; migrations/0000:124) — two overlapping uniques, see §3 |
act | sql/act.sql.ts:17-40 | showId int NOT NULL → show.id CASCADE, comicId int NOT NULL → comic.id CASCADE, enabled bool DEFAULT true | unique uniqueComicShow (comicId, showId) (act.sql.ts:34-39) |
user | sql/user.sql.ts:17-36 | authId text NOT NULL (Clerk user id), email text NOT NULL, stage text NOT NULL | authId UNIQUE (global, not stage-scoped); unique emailUniqueIndex (lower(email), stage) (user.sql.ts:30-35) |
show_notification | sql/showNotification.sql.ts:18-35 | userId int NOT NULL → user.id CASCADE, enabled bool NOT NULL DEFAULT false | unique user_show_unique on userId alone (added in migration 0002) — one global on/off row per user, not per-show |
comic_notification | sql/comicNotification.sql.ts:18-41 | userId → user.id CASCADE, comicId → comic.id CASCADE, enabled bool NOT NULL DEFAULT false | unique user_comic_unique (userId, comicId) |
comic_to_user | sql/comicToUser.sql.ts:17-36 | userId → user.id CASCADE, comicId → comic.id CASCADE, isLiked bool NOT NULL DEFAULT false | unique user_comic_unique1 (userId, comicId); deliberately no externalId (comment at comicToUser.sql.ts:16) |
new_show_queue | sql/newShowQueue.sql.ts:19-36 | showId int NOT NULL → show.id CASCADE, notifiedAt timestamp (NULL = pending, set once announced) | unique index queue_show_unique on showId alone (a show is queued at most once). OUTBOX (added 2026-07-13 #62): a row is written when a show is first inserted (§4 / handleShowDetails), and notifiedAt is set once ShowNotificationCron has announced it. HAS an externalId (prefix nsq), unlike comic_to_user |
Cascade map (what deletes take with them):
| Deleting a… | Cascades to | Blocked by |
|---|
| user | show_notification, comic_notification, comic_to_user | — |
| comic | act, comic_notification, comic_to_user | — |
| show | act, new_show_queue | — |
| room | nothing | any show.roomId referencing it (FK is NO ACTION → delete fails) |
Drizzle relations() are declared in every sql.ts file but are inert: packages/core/database.ts:5 does import * as schema from "./database" — it imports itself, so the drizzle instance's schema is { db } and the relational API (db.query.*) does not work. All real code uses the query builder (db.select()...). Don't "fix" this casually (behavior change → cellar-change-control); don't write new code that relies on db.query.* until it is fixed.
2. ID conventions
Two ids per row:
id serial — internal integer PK, used for FKs. Exception that bites: show.id is NOT locally generated; it is CC's own numeric show id, inserted verbatim (§3).externalId — API-facing opaque id, "<prefix>_<cuid2>", generated app-side by createExternalId(PREFIX) (packages/core/common/createExternalId.ts:3-9; cuid2 = collision-resistant random string, lowercase alphanumeric).
Prefixes (packages/core/common/constants.ts:1-8):
| Constant | Value | Example externalId |
|---|
| USER_PREFIX | user | user_tz4a98xxat96iws9zmbrgj3a |
| COMIC_PREFIX | comic | comic_… |
| SHOW_PREFIX | show | show_… |
| ROOM_PREFIX | room | room_… |
| ACT_PREFIX | act | act_… |
| SHOW_NOTIFICATION_PREFIX | show_notif | show_notif_… |
| COMIC_NOTIFICATION_PREFIX | comic_notif | comic_notif_… |
| NEW_SHOW_QUEUE_PREFIX | nsq | nsq_… |
Trap — the guards are UNANCHORED substring regexes. Every "is this a valid X id" check is externalId.match(new RegExp(PREFIX)):
isComicExternalId models/comic.ts:32-34, isShowExternalId models/show.ts:161-163, isRoomExternalId models/room.ts:7-9, isUser sql/user.sql.ts:51-58, and models/user.ts:13-15 (which is ALSO named isRoomExternalId — copy-paste misnomer; it checks USER_PREFIX).
Consequences:
show_notif_… passes the show guard; comic_notif_… passes the comic guard (prefix is a substring).- cuid2 output can contain
user/act/show as a substring, so random ids can pass the wrong guard.
- These guards are input-shape hints, not authorization or type safety. New guards you write should anchor (
^prefix_), but changing the existing ones is a behavior change → cellar-change-control.
3. Identity semantics (where duplicate/missing rows come from)
Show identity = CC's numeric show id. handleShowDetails (packages/core/handleShowDetails.ts:13,22) inserts the RAW CC API objects, so their id field becomes our show.id serial PK. Two consequences:
- Sequence desync (latent): explicit-id inserts never advance Postgres's serial sequence. Nothing currently inserts a show without an id, but any future code relying on the serial default will start at ~1 and eventually collide with CC ids. Never insert a show row without an explicit id.
show.timestamp (unix seconds of showtime; it is also the showid= in CC reservation URLs) is used as the lookup key by the lineup pipeline — getShowByTimestamp (models/show.ts:261-263) — but has no unique index (§1). handleLineUp only creates acts when EXACTLY one show matches the timestamp (handleLineUp.ts:34: if (show.length === 1 && show[0].id)); duplicates make acts silently stop being created for that show.
Comic identity = exact name string. Dedup within a scrape batch is a Set of names (handleLineUp.ts:16-21); DB dedup is the name unique + lower(name) unique index. Accent, spelling, or punctuation variants of the same person ("Dave Attell" vs "Dave Attel") fork separate rows forever. There is no merge tooling; merging rows is prod surgery (§8).
The 56581a9 lesson (2025-09-02, "Fixed comic creation logic"): createComics used onConflictDoNothing({ target: comic.name }). Postgres could not match that conflict target to the lower(name) expression index, so a case-variant duplicate raised a unique violation instead of being ignored — comic creation crashed in prod. Fix: drop the target — onConflictDoNothing() with no target ignores conflicts on ANY unique constraint (models/comic.ts:36-38 today). Rule: if a table has an expression/partial unique index, either name it precisely in the conflict target or use targetless doNothing; a plain column target does not cover lower(col).
4. Upsert semantics per entity — what refreshes on conflict, what never does
| Entity | Function | Conflict target | On conflict |
|---|
| show | createShows models/show.ts:165-187 | show.id | DO UPDATE, refreshes ONLY: description, cover, note, roomId, max, totalGuests (via EXCLUDED.*). Never refreshed: soldout, available, timestamp, time, special, mint, weekday, venueMin/venueMax, forwardUrl — those stay at first-insert values. As of 2026-07-13 (#62) it also has .returning({ id, timestamp, inserted }) where inserted is the SQL boolean expression xmax = 0 — true for a brand-new insert, false for a row that took the ON CONFLICT update path; handleShowDetails uses this flag to enqueue only brand-new upcoming shows into new_show_queue |
| room | createRooms models/room.ts:11-13 | room.id | DO NOTHING — name and maxReservationSize never updated by scraping |
| comic | createComics models/comic.ts:36-38 | none (any unique) | DO NOTHING — img/description/website never refreshed after first sighting |
| act | createActs models/act.ts:44-51 | (showId, comicId) | DO NOTHING |
| user | createUser models/user.ts:17-22 | user.authId | DO NOTHING — email never refreshed |
| show_notification | upsertShowNotification models/showNotification.ts:9-19 | userId | DO UPDATE enabled |
| comic_notification | upsertComicNotification models/comicNotification.ts:21-55 | (userId, comicId) | DO UPDATE enabled = excluded.enabled. Trap: unknown external comicId maps to undefined → NOT NULL violation → unhandled 500 (comicNotification.ts:39-43) |
| comic_to_user | none exists | — | schema-only table |
| new_show_queue | enqueueNewShows models/newShowQueue.ts:17-24 | newShowQueue.showId | DO NOTHING — an already-queued show is not re-queued (idempotent enqueue). Drained by claimPendingNewShows (models/newShowQueue.ts:43-54): atomic UPDATE … SET notifiedAt=now WHERE id IN (…) AND notifiedAt IS NULL RETURNING, the guard that stops overlapping cron runs double-announcing |
Why "stale soldout" confuses people twice: the stored soldout column is never refreshed (above), AND the API doesn't even use it — Show.toJSON() computes soldout as totalGuests >= max because "soldout from comedy cellar api is not accurate" (models/show.ts:83-85,103-104). totalGuests/max ARE refreshed on conflict, so live-scraped availability is current even though the column is stale.
5. roomDictionary and the unknown-room silent failure
Room names are NOT scraped; they come from a hardcoded map (models/show.ts:27-32):
const roomDictionary: Record<number, string> = {
1: "MacDougal St",
2: "Village Underground",
3: "Fat Black Pussycat",
5: "Unknown",
};
(Yes, 4 is missing — CC's numbering, not ours.) If CC ever emits a new roomId, show.roomName is undefined → createRooms gets name: undefined → NOT NULL violation kills the whole rooms batch insert → createShows for that day hits an FK violation on the missing room → and both failures are swallowed by Promise.allSettled with discarded results inside a try/catch (handleShowDetails.ts:19-28). Net effect: every show that day silently fails to persist, no email, no log you'll notice. Symptom→triage for this lives in cellar-debugging-playbook. Also note the same allSettled runs room-insert and show-insert in parallel, so show inserts only succeed because rooms already exist from prior runs.
room.maxReservationSize (default 4) gates reservation party size in the booking flow; scraping never sets it, so it is 4 everywhere unless hand-edited in the DB (hand-editing = prod surgery, §8).
6. Migration workflow
Toolchain: drizzle-kit (v0.31.10 installed here, ^0.31.8 in package.json:25). Config drizzle.config.ts: schema glob ./packages/core/**/*.sql.ts, out ./migrations, dialect postgresql, url Resource.DbUrl.value. Because the config reads an SST secret at load time, every drizzle-kit command must run under sst shell (which resolves stage secrets; needs AWS credentials + root .env — see cellar-config-and-secrets). That is what the root scripts do (package.json:12-13):
pnpm db <subcommand>
pnpm db:studio
The procedure:
pnpm db generate
git status --short migrations/ && cat "$(ls -t migrations/*.sql | head -1)"
pnpm db migrate
pnpm exec sst shell --stage <stage> drizzle-kit migrate
Subcommand names verified against pnpm exec drizzle-kit --help (2026-07-07): generate, migrate, introspect, push, studio, up, check, drop, export. Steps 2/4 cannot run in a sandbox without AWS creds (Resource.DbUrl.value throws outside sst shell); that's an environment limit, not a bug (cellar-build-and-env).
Which database does migrate hit? The DbUrl secret of whichever stage sst shell runs as — your personal stage by default (stage defaults to your username), --stage prod for prod. But all stages have historically pointed at the same Supabase database (§8), so a migrate "from your dev stage" is, in practice, a prod schema change. Treat every pnpm db migrate as a prod deploy: owner approval per cellar-change-control before running it.
Never use drizzle-kit push here — it applies schema diffs directly with no migration file, no review step, and no journal entry, on a database prod is reading. If you're tempted, you want generate + migrate.
7. The Feb-2025 squash, and the append-only rule
Commit 8b3b837 (2025-02-05, "Refactor database models…") deleted all 18 accumulated migrations (0000–0017) and re-baselined the history to three files: 0000_closed_mattie_franklin.sql, 0001_pale_nighthawk.sql, 0002_light_miss_america.sql (journal timestamps 2025-02-05/06, migrations/meta/_journal.json). This only works if the live database's drizzle migration ledger is manually reconciled to match the rewritten history — a one-time, risky, owner-executed event on the shared prod DB. 0003_dizzy_lady_ursula.sql (2026-07-13, #62, adds new_show_queue) was added the right way — a plain pnpm db generate append with no re-baseline and no squash — which vindicates the append-only rule below.
Rules derived from it:
- Treat
migrations/ as append-only. Never edit, rename, renumber, or delete a migration that may have been applied anywhere; migrations/meta/ moves only via pnpm db generate.
- Never reset/re-baseline migration history without a written prod reconciliation plan approved by the owner (
cellar-change-control — this is in the owner-only class).
- If generated SQL looks wrong, fix the schema code and regenerate the not-yet-applied file (delete it AND its journal/snapshot entries together, before anything has run it) — never hand-edit applied SQL.
8. Shared-DB discipline
One Supabase Postgres serves every stage. Evidence: commit 741ca41 (2024-10-14) "Added stage field to users since db is shared across envs". DbUrl is a per-stage secret so stages COULD diverge, but assume shared unless the owner says otherwise.
- User-adjacent queries MUST filter by stage. The pattern (models/user.ts:7-11): every user query goes through
applyWhere(...), which appends eq(user.stage, Resource.App.stage). Any new query that touches user (or joins through it) without this filter leaks rows across stages — e.g. dev code acting on prod users. createUser stamps stage on insert (user.ts:17-22). Note user.authId is unique globally, NOT per stage: the same Clerk account cannot have rows in two stages (dev and prod use different Clerk instances, so this holds today).
- Scraped tables (
show, room, comic, act) are NOT partitioned. A dev-stage scrape or seed writes rows that prod immediately serves. Keep dev writes limited to what the normal pipeline would write anyway (idempotent upserts, §4), and stay polite to CC while doing it (cellar-scraping-reference).
- Any UPDATE/DELETE/TRUNCATE, hand-edit via
pnpm db:studio/Supabase console, or comic-row merge is prod surgery — owner approval + backup plan first (cellar-change-control). Remember the cascade map (§1): deleting one comic silently deletes its acts and every user's notification rows for it.
9. Bootstrap: an empty show table crashes things
Three call sites destructure getLastShow()'s first element and read .timestamp without a guard, so with zero show rows they throw TypeError: Cannot read properties of undefined:
| Crashes | Where | Visible as |
|---|
getComics | models/comic.ts:53-59 | GET /api/comics 500s (reads both getLastShow and getUpcomingShow results unguarded) |
| newShowCron | functions/cron/newShowCron.ts:17-19 | discovery cron errors every 6h |
| syncCron | functions/cron/syncCron.ts:52-55 | hourly sync errors; GET /sync-shows 500s |
Seeding procedure (fresh DB, or after the shared DB was ever emptied). These hit the deployed API, which scrapes CC live and persists as a side effect — serial, one date at a time, respecting politeness rules in cellar-scraping-reference:
API=https://comedycellar-api.mafz.al
D=$(date +%F)
curl -s "$API/api/shows?date=$D" | head -c 300
curl -s "$API/api/line-up?date=$D" | head -c 300
Order matters: line-up before shows writes comics but zero acts (no show row matches the timestamp), and acts are NOT retroactively backfilled by the hourly sync for past dates. Do NOT seed with GET /sync-shows — it calls getLastShow first and 500s on the empty table.
10. Known data-quality issues (open — do not silently "fix"; route through change control)
| Issue | Evidence | Status |
|---|
/api/shows/new returns a show once per comic on its lineup ("N-plicated"), and total counts act-rows: getShows/getShowsCount inner-join show→act→comic with no GROUP BY/DISTINCT | models/show.ts:193-253 | open |
Shows with zero acts (typically special shows) are invisible in /api/shows/new (inner join drops them) | same join | open |
| Comic duplicates from name variants; no merge tooling | §3 | open |
Stored show.soldout/available/timestamp never refreshed after first insert | §4 | open (API computes soldout instead) |
maxReservationSize stuck at default 4 for all rooms | §5 | open |
database.ts self-import leaves relations()/db.query.* inert | database.ts:5 | open trap |
comic_notification written but never read to notify anyone (show_notification IS now read, shipped 2026-07-13 #62 but unproven) | §0 | open — comic-follow notifications remain unshipped, see cellar-frontier-and-method |
Add-a-table / add-a-column checklist
- New file
packages/core/sql/<name>.sql.ts following the house pattern: id serial PK + externalId with createExternalId(PREFIX) default + createdAt — add the PREFIX to common/constants.ts. Skipping externalId is allowed only for internal-only tables; say why in a comment (precedent: comicToUser.sql.ts:16).
- Decide partitioning: user-adjacent data gets a
stage text NOT NULL column AND an applyWhere-style stage filter on every query (§8); scraped/shared data gets a comment saying it is shared.
- Don't rely on
relations()/db.query.* (§1 inert-schema trap); write models with the query builder in packages/core/models/.
- If the table has any expression/partial unique index, re-read §3 (56581a9) before writing its upsert; pick the conflict target deliberately and document what refreshes vs what doesn't (add it to the §4 table).
pnpm db generate → review SQL → get owner approval → pnpm db migrate (§6).- Behavior-affecting or destructive? Stop and run the gates in
cellar-change-control. Zero tests exist (as of 2026-07-07), so evidence standards come from cellar-validation-and-qa.
Provenance and maintenance
Verified 2026-07-07 against working tree at commit c8d9918 (branch == main) by reading: all 8 packages/core/sql/*.sql.ts; packages/core/models/{show,comic,room,act,user,showNotification,comicNotification}.ts; packages/core/{handleLineUp,handleShowDetails,database}.ts; packages/functions/cron/{newShowCron,syncCron}.ts; packages/functions/{lineUp.ts,shows/index.ts}; infra/{cron,config}.ts; drizzle.config.ts; root package.json; .env.template; migrations/* incl. meta/_journal.json. Commands run: pnpm exec drizzle-kit --help and --version (v0.31.10); grep for comic_to_user/isLiked usage. Git evidence: git show 8b3b837 --stat (18→3 migration squash), git show 56581a9 (onConflict target removal diff), git log for 741ca41 (shared-DB commit message). Not verifiable here: anything requiring a live DB connection or AWS creds (pnpm db *, actual DbUrl values, whether stages' DbUrls are literally identical today — stated as historical/assumed above).
Reconciled 2026-07-13 against commit 5ceaf98 (new main). #62 added migration 0003_dizzy_lady_ursula and table new_show_queue, taking tables to 9, packages/core/sql/*.sql.ts to 9 files, and migrations to 4 (0000–0003). Re-read: packages/core/sql/newShowQueue.sql.ts; packages/core/models/newShowQueue.ts; packages/core/models/showNotification.ts (new getShowNotificationRecipients); packages/core/handleShowDetails.ts (now enqueues via enqueueNewShows); packages/core/models/show.ts (createShows .returning add, which shifted getShows/getShowsCount/getShowByTimestamp line numbers ~+7); infra/cron.ts (third cron ShowNotificationCron); packages/core/common/constants.ts (NEW_SHOW_QUEUE_PREFIX = "nsq"). Net for this skill: show_notification notifications now ship to users but are unproven (zero tests, no track record); comic_notification is still unshipped.
| May drift | Re-verify with |
|---|
| Table/column/constraint set | cat migrations/*.sql and ls packages/core/sql/ |
No new migrations since 0003 (0003 = new_show_queue, 2026-07-13) | cat migrations/meta/_journal.json |
| drizzle-kit subcommands/version | pnpm exec drizzle-kit --help && pnpm exec drizzle-kit --version |
pnpm db script wiring | grep '"db' package.json |
| roomDictionary contents | sed -n '27,32p' packages/core/models/show.ts |
createShows refreshed-column list + .returning | sed -n '165,187p' packages/core/models/show.ts |
| Stage-filter pattern intact | sed -n '7,11p' packages/core/models/user.ts |
| comic_to_user still unused | grep -rn isLiked packages/ --include='*.ts' | grep -v sql/ |
| Prefix list | cat packages/core/common/constants.ts |
