The model

Four tiers, and a reader enters at any of them.

Show ──── Production ──── Recording ──── Album
                 │
                 └─ documented by: audio · video · print · still · press

decisions.md §11 is the one to read if you only read one. It made the production the spine and demoted recordings to one of five families of document. decisions.md §13 went further and said the subject is the production, not the paperwork about it. decisions.md §17 carries that into copy: the subject of every reader-facing sentence is a show, a person, a building or a production, never the catalogue itself. "Recorded three times, most recently in 2019" is a fact about the show's life; "we hold three recordings of this" is a fact about our acquisition process, and it is banned wherever the same number can be said the first way.

The tiers are not a path. flows.md is blunt about this: nobody traverses the hierarchy downward. People arrive in the middle holding one thing (a sleeve, a title, a half-remembered evening in Chicago) and navigate outward. So every tier is addressable on its own, and the system has no "start here" page that anything depends on.

What each tier is

Show is the bibliographic entity, the thing that gets revived. Shows with a page: 10,373. That was a curated 939-title subset, mostly musicals, through 25 August 2026. Every work a production actually points at was pageless until then, itself a broken promise (tools/build-db.py:promoted_and_credited_shows(): "if a show existed, it should have a page"). Promotion closed that gap; the count has moved twice since, for two unrelated reasons: a rebuild that dropped one bad row (a bibliography citation mistakenly promoted as a show title, correctly excluded by the committed logic for carrying no valid production year), and then a larger shift because broadway-data's own upstream data moved under a later rebuild, which is where the real change from ~10,750 to 10,373 comes from. The curated set itself is 935 shows today (measured by which shows still carry an era value, the field only ever computed for it), down from 939 at promotion time; 75 of those 935 are titles no production's workId resolves to. Of the 10,373, the number typed musical is 2,547, against 7,203 plays, 373 specials, 99 concerts, 75 revues, 74 revivals and 2 play-with-music. That distribution is why type is a filter and never a sort. See docs/routes.md on /shows.

Production is a staging: a company, a house, a run. Records: 13,401. Of those, the number in scope (belonging to one of the 935 shows curated before the 25 August 2026 promotion) is 1,724. That flag is computed fresh at every build against whichever curated set broadway-data's shows.json holds at that moment, so "in scope" and "has a page" are two different tests that can each move independently: the first tracks the curated set as broadway-data revises it, the second tracks the promoted shows table. In scope went from 1,536 to 1,724 between the promotion and tonight's rebuild not because the promotion logic changed, but because broadway-data's own upstream data did. Every production now belongs to a show with a page, regardless; only 1,724 belong to one of the 935 currently curated. The rest are carried because the alternative is worse. "On this day" reads all of them, which is why every one of the 366 days has something on it at a median of 36 productions; restricted to the in-scope set, two thirds of the year is empty.

A production with no recording is a first-class record, not an edge case. The 2022 revival of 1776 earns a page with nothing hanging off it, and under decisions.md §11 that is an ordinary production with a thin documentary record rather than an embarrassment.

Recording is one act of recording. It is the tier the catalogue is named after and it does not exist in the database yet. There is no recordings table.

Album is one issue of a recording: the Columbia 1969 against the Sony 1992 against the Masterworks 2010. Records: 4,074, up from 237 as of tonight's recordings backfill. They currently hang off a work, not a recording and not a production, because the intervening tier is unbuilt.

Work is a title a production can point at when we hold no separate show record for it. There are 10,298. The 25 August 2026 promotion above put a shows row behind all but one of them; a following rebuild dropped that one (a bibliography citation mistakenly promoted as a show title by an earlier one-off script, correctly excluded by the committed logic for carrying no valid production year) and a further rebuild since dropped the citation from works entirely rather than leaving it pageless, so today it is zero again, not one. web/app.py:titles() exists for the general case regardless: a work with no dated production behind it is never promoted (see promoted_and_credited_shows()), so a work can still arrive with nothing to link to. The helper returns the title and whether a page exists behind it for exactly that case.

Person and Venue are subjects in their own right, per decisions.md §13, not lookup tables hanging off a production. People: 7,369. Venues: 352.

The gap between the model and the database

schema.md describes Work → Production → Recording → Release with Song, Edition and Discrepancy alongside, and it resolves 16 of 16 hard cases from the research. The database implements a subset:

In `schema.md`	In `web/site.sqlite3`
Work	`works`, plus `shows`: all 10,298 promoted into it now
Production	`productions`
Recording	absent. No table
Release / Album	`albums`, attached to a work
Song	absent
Edition	absent
Discrepancy	absent as a table. Rendered ad hoc, per page

The last row matters more than the others. decisions.md §10 (surface the confusion, do not resolve it silently) calls for discrepancies as first-class records, attached to an entity, sourced, and visible in the interface. What exists instead is a handful of hardcoded checks in web/app.py that each catch one shape of problem and write their own sentence:

web/app.py:wrong_place() catches a venue record describing a Manhattan theatre while filed as West End, and prints a paragraph naming broadway-data issue #90.
The /show/ view marks _early on a production that predates its own show and _dupe on an id-collision suffix, then shows both rather than merging: which is canonical is not ours to decide.
web/app.py:related_image() returns an image and the sentence explaining what you are looking at, because a portrait of Ethel Merman is not a document of the show she was in.

Each of those is decision 10 working. None of them is a Discrepancy record, and none is queryable, citable, or contributable. That is the largest distance between the settled model and the running system.

Identifiers

ids.md specifies a scheme: an entity-type letter, six Crockford base32 characters, a check character on anything meant to be typed. W-0001A for a work, P-002TX for a production, A-004MZ for an album.

It is implemented for albums and nothing else. Album ids look like A-000011. Every other identifier in the database is a slug inherited from broadway-data:

shows        110-in-the-shade · 13 · 1600-pennsylvania-avenue
productions  02-the-king-and-i-2000-original-westend
people       elaine-stritch  … and also  1994 · 510-performances · 534-p
venues       adelphi · 44th-street

Two consequences, and both are live.

The people table contains rows that are not people. 1994 and 510-performances are scraper output that acquired a person record. They are countable and they appear in cast lists.

More seriously, ids.md rule 1 says an identifier never changes and rule 2 says it is never reused, and a slug derived from a title cannot honour either. The document quotes castalbumdb.com's maintainer on exactly this failure: index numbers reassigned alphabetically on every insert, Google unable to keep up, every inbound link broken. We have inherited the failure mode we wrote the document to avoid. tools/mint-albums.py is the one place that mints properly, and it covers all 4,074 records, including tonight's recordings backfill.

ids.md also contradicts itself on length. The diagram says six characters of base32; the type table shows five plus a check character. tools/mint-albums.py noticed and resolved it to six-total in favour of the table. The document was never corrected.

How data gets in

python3 tools/build-db.py     # rebuild web/site.sqlite3 from broadway-data
python3 tools/build-search.py # the indexes alone, while iterating on ranking

tools/build-db.py imports tools/site.py's own load() rather than re-reading the source files. That is deliberate and the docstring says why: the Cover Art Archive translation, the per-entity image index, the overlay and the production grouping stay defined in one place, so the served site and the generated site cannot drift apart invisibly.

Every table keeps the complete source record as JSON in a doc column, and web/app.py:doc() is the only thing that reads it. Typed columns exist for what needs an index; everything else is reached through the JSON. This is why adding a field to a page rarely means a schema change.

Two joins happen at build time and both are repairs to somebody else's data.

Productions to venues, by name. The join logic hasn't changed. A theatre referenced by an IBDB numeric id while venues.json is keyed by slug never meets it directly, so the build falls back to matching the theatre name against a venue record exactly. What changed is broadway-data's own venue data: venues grew from 135 to 352 in tonight's rebuild, and the resolved rate went with it: 98% of productions carry a theatre_id today (13,138 of 13,401), not the 13% this paragraph used to report. The 87% figure and the 11,463/5,758 breakdown behind it were true of a much thinner venues.json; re-deriving the equivalent breakdown against tonight's data would need a run of tools/build-db.py's own join step rather than a query against the built table, which is what this number is. Originally reported as broadway-data issue #95. Worth checking whether that issue is now closed rather than still open against a stale premise.

Cast credits from productions, never from people.json. A person's shows field names a work rather than a staging, so Elaine Stritch's record points at show-boat and dates her to 1927. She was in the 1994 revival. The credit table is built from each production's notableCast instead.

The second database, and why it exists

User data is not in web/site.sqlite3, and the reason is one line of tools/build-db.py: the build drops and recreates every table in the catalogue database. Anything of a reader's kept there would be destroyed on the next rebuild.

Where it is instead depends on where the site is running. On a workstation it is web/accounts.sqlite3, overridable with CADB_ACCOUNTS_DB. With DATABASE_URL set (which is how this runs in production, on a read-only filesystem whose /tmp does not survive to the next request) every connection is Postgres and the SQLite path is unused. web/dbkit.py reconciles the two dialects, and nothing in web/accounts.py knows which it is talking to. findings/postgres.md has the detail.

That split is also where decisions.md §15 earned itself: SUM(proposed_at >= ?) sums a comparison as 1 and 0, which is valid SQLite and meaningless in Postgres, and it was sitting in the contribution rate limiter. It was found by running the suite against Postgres rather than by reading the SQL for dialect problems.

In production it is not a file at all. DATABASE_URL set means the same tables, same columns, same code, in a Postgres schema called theatre_register. A serverless function has no disk it can write to and no /tmp that survives to the next request. DATABASE_URL unset, which is every workstation, means the file. web/dbkit.py is the whole of the difference; see findings/postgres.md.

users         id, display_name, email, email_relay, contact_email,
              contact_email_verified, created_at, last_seen_at,
              status, role, plan, plan_until, plan_ref
identities    provider, subject, user_id, email, created_at, last_login_at
sessions      token_hash, user_id, csrf, created_at, seen_at, expires_at
plan_events   id, user_id, at, from_plan, to_plan, reason, ref
lists         id, user_id, title, note, visibility, created_at, updated_at
list_items    list_id, entity_id, kind, note, position, added_at
favourites    user_id, entity_id, kind, added_at

The two databases are joined in Python, never in SQL. A list item stores show:sweeney-todd-1979 and the show is looked up when the list is drawn. That means a missing entity is a rendering problem rather than a foreign key violation, which is the right way round when one side of the join is regenerated from scratch on a schedule, and it is what lets the two live in different database engines at all.

The search index

Built last, by tools/build-search.py:build_all(), called from the end of tools/build-db.py. It reads the tables rather than the source files, so nothing can be in the index that is not on a page.

Three FTS5 tables and one key table: show_fts for finding a show by name, find_fts and printed_fts for identifying an object, and album_key for catalogue numbers and barcodes.

web/search.py is the query side and it keeps two problems apart. Finding a show starts from a name someone half-remembers. Identifying an object starts from a thing in someone's hand and runs on what is printed: a catalogue number, a barcode, a label, an MBID pasted out of a tagger.

The awkward part is that our catalogue numbers did not arrive as catalogue numbers. This section used to say every one of the original 237 had catalogueNumber: null and label: null. True when it was written, false as soon as docs/routes.md's extraction ran, and this copy of the same claim never got the fix. Today, of 4,074 albums, 337 have neither field. The rest were split out of labelsStated, one string with the label and number run together: Columbia Records SCX 6424. Splitting it from the front produced the label "MGM Records E" out of "MGM Records E 93", so the split happens at index time from the end of the string, and both the parts and the original are indexed.