Design: per-source metadata model for content records

[ThatXliner]

Problem

Every new source adds fields that only that source has, and there is nowhere good to put them. The current answer is "add a column," which does not scale, and the pressure is already visible in the schema.

A column per source-specific field does not generalize. Bill.congress (schema.ts:134) is federal-only. State bills leave it null — see #158 / #276 — and it will stay null for every non-federal source we ever add. A session column would have the inverse problem.

So fields get smuggled into strings. With nowhere to store a legislative session, the Open States scraper encodes it into the display identifier: billNumber is "CA SB 243 (2025-2026)". That string is now load-bearing for row identity — the unique constraint is (billNumber, sourceWebsite) — so a formatting change silently duplicates every stored row, and reading the session back out requires a regex. That is a workaround, not a design.

Shared columns quietly mean different things. Bill.chamber is varchar(50) holding "House" / "Senate" / "Assembly", with the vocabulary varying by state (and Nebraska unicameral, and "House of Delegates" in VA/WV/MD). It is a display string doing the work of an enum.

Divergent date columns break cross-type queries. Bill has introducedDate + sourceUpdatedAt (nullable), GovernmentContent has publishedDate (not null), CourtCase has filedDate. The Browse "All" tab UNIONs all three, which is why it currently sorts on created_at — our row-insertion clock — and shows a 2025 bill adjacent to a 2026 one under a "SORTED BY RECENT" label. (Separate bug, fixable today; see Non-goals.)

Data with no home gets dropped. Open States roll-call votes are not ingested at all because Bill has no column for them, even though we fetch them live for the detail screen.

Why "just use a JSONB blob" is not obviously the answer

There is precedent — Bill.actions and Bill.versions are already jsonb with $type<...> annotations. But $type is a compile-time assertion with no runtime validation, so nothing stops a scraper writing a differently-shaped object, and nothing tells a reader six months later what shape to expect. Pushing more into an unvalidated blob trades a schema problem for a data-integrity one, and makes the fields unindexable-by-default and awkward to query.

So the question is not "columns or JSON" — it is what the contract is, who enforces it, and where.

What a design should answer

• Shape. Typed JSONB with a Zod schema per source? A sidecar content_metadata table keyed by (contentId, key)? Per-source tables with a shared view? Something else?
• Validation. Where is the schema enforced — at the scraper boundary, in upsertContent, in a DB check constraint, or all three? What happens to a record whose metadata fails validation: reject, or store degraded?
• Promotion path. When a field turns out to be universal (a canonical "last activity" date), how does it graduate from metadata into a real column? Migration story?
• Queryability. Which fields need indexes/filters — jurisdiction, session, chamber are all plausible filter dimensions for the app. GIN on JSONB, generated columns, or promotion?
• The date problem specifically. Is there one canonical "when did this happen" per content record, normalized across all three tables? That single decision fixes cross-type sorting for good.
• Scope. Does this cover only Bill/GovernmentContent/CourtCase, or also measures, candidates, and the civic cache?

Non-goals

• This does not block the Browse sort bug. That is fixable today with existing columns (coalesce(sourceUpdatedAt, introducedDate, createdAt) projected under a common alias across the union) and should not wait on this design.
• Not proposing a rewrite of the content tables. The likely outcome is additive.

Context

Surfaced while implementing #158 (Open States state-bill ingestion, PR #276), where congress went null, session went into the identifier string, and votes were dropped for lack of anywhere to put them.