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.

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: 939. Of those, the number typed musical is 749, with 78 revivals, 62 specials, 46 revues, and two values at n=2. 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,459. Of those, the number belonging to a show with a page is 1,536. 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: 237. 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 show record for it. None has a page, and there are 10,618. web/app.py:titles() exists entirely because of this: a template that links every workId to /show/ hands out a 404 on the large majority of production pages, so the helper returns the title and whether a page exists behind it.

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

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 for the 939 with a page
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 237 records.

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. 11,463 of the productions reference a theatre as an IBDB numeric id while venues.json is keyed by slug, so the two id spaces never meet and 87% of productions cannot reach a venue at all. 5,758 of them carry a theatre name that matches a venue record exactly, so the build joins on that and records what the id was. Reported as broadway-data issue #95.

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

role is user | trusted | admin, status is active | suspended | closed, and visibility is private | unlisted | public. Identifiers are strings — u_ and l_ followed by 12 urlsafe characters — which is a third id scheme alongside the album mint and the broadway-data slugs.

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 are not catalogue numbers. All 237 album records have catalogueNumber: null and label: null. The only identifier held is 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.