We shipped the database migration for feed identity this week. Nine new columns, six constraints, one 407-line backfill job, and a question that sounds simple until you try to answer it: when a user has been seeing 'The Kids' as their feed name for months, was that their choice or ours?
The migration problem
Last week we shipped the feed identity domain—a 527-line composition engine that knows how to name calendar feeds. It handles new subscriptions. But production has five existing feeds, each with a display_name that's been showing for months. Those names came from somewhere: the assistant suggested them at import time. The user never typed them.
Now we need to classify them. Was 'The Kids' the user's choice (rank 4, chat—never re-infer over it) or was it a generic label the assistant picked (rank 1, fallback—re-infer when we have better data)? The name itself doesn't tell you.
The schema: additive, idempotent, non-blocking
The migration adds nine columns to calendar_feed_subscriptions: title (what the feed is called), subject (the team or person it's about), source_label (the platform), title_source (where it came from: fallback < calendar < inferred < chat < user), confidence, evidence codes, a material-change hash, and an optimistic-lock version.
Every column is nullable or has a default. Existing rows keep their display_name exactly as it is. The application reads these columns defensively until they exist. It's safe to run twice, safe to run before or after the deploy. This is what 'non-blocking' means in practice.
Six constraints enforce the domain's contracts. A title with a source carries its confidence; a fallback is confidence 0.0 and may have no title. Subject and source_label are at most 60 characters, never only whitespace. Evidence is at most five codes, each a non-blank string of at most 80 characters—codes, never feed text. A row that no FeedIdentity could be built from is refused at write time.
The backfill: comparing names as a person reads them
The migration moves no data. Classifying existing names needs the user's calendar list—the full list, not just the import target. The rule:
A name that passes the A8 guard against every calendar in the user's list is chat, so it's kept and never re-inferred over. An empty name, a generic word, hostile text, or a name equal to any calendar in the list (not only the import target) is fallback, so it's re-inferred. An unreadable calendar list (no token, an HTTP error, a missing page, or an answer with no calendar names at all) leaves the row null and is retried.
That's 407 lines of Python. It fetches the Google calendar list (up to 20 pages), sanitizes each name, strips combining marks, and compares them as a person reads them. So 'Soccer' against a calendar named 'Soc<ZWSP>cer' (with a zero-width space) is a match—fallback. A feed called 'Soccer' when you have a calendar named 'café' but your feed was imported from a page saying 'cafe'? Also fallback, because the mark-stripped forms match.
The job writes only rows whose title_source is still null, conditioned on the identity_version it read, and bumps that version. So a user edit that lands mid-run wins. A later conditional write from the identity service cannot clobber the result unseen. It's idempotent. It's sequential. It defaults to a dry run that prints each decision without writing anything—pass --apply to commit.
Testing a migration
The behavioral run was done against a real Postgres 16 instance with the subscription migrations replayed, five production-shaped rows, the migration applied twice, and each constraint exercised to prove it accepts what it should and refuses what it shouldn't. Results are recorded in the PR.
The test suite is 1,024 lines across two files. test_identity_migration.py is static checks: the migration version is above every other file (so an ordered replay applies it last), it alters only calendar_feed_subscriptions, it adds exactly nine columns with if not exists, every constraint exists and matches what the code expects, title_source is checked against the five-rank vocabulary, evidence is validated as at most five strings, a title that came from somewhere carries its confidence.
test_identity_backfill.py is 821 lines. It exercises the classification logic, the calendar list fetch, the conditional writes, the race detector (a write that matched nothing is told apart from a lost race by re-reading the row), the dry run, the column-missing detector, the response-cap detector, endless-source clips, the comparable-name logic including mark stripping and zero-width characters, and the outcome reporting.
What the review found
Four-lens review and running the migration on Postgres found things the design missed. The CHECK constraint that validates evidence arrays had to be strict and silent—lax jsonpath mode unwraps nested arrays, so [['a']] passed, and six codes inside one inner array passed the length check. The fix: strict mode, and a test that pins the regex character for character.
The calendar name comparison had to strip combining marks and sanitize zero-width characters, or 'Soccer' against 'Soc<ZWSP>cer' would classify as chat when it should be fallback. An empty or name-less calendar list is unreadable (not 'no calendars'—that would mark every row chat). A write that matches nothing is a failure, told apart from a lost race by re-reading. Blank subject or source_label text is refused. btrim in SQL ignores tabs; the regex was fixed.
One more: a chat row gets title, confidence and evidence. A fallback row gets title_source and evidence only—no title. So the consumer (T10 feed_label) must read display_name when title is null. Otherwise a fallback feed would show 'Calendar feed' instead of the name the user has been seeing.
What's next
The migration is deployed. The backfill job runs once, manually, in dry-run mode first to review decisions before writing. After that: T9 (ensure_identity service) re-infers the fallback rows with better data, T10 (feed_label consumer) surfaces the identity everywhere a feed is shown, and T11 (user retype) lets a user replace any title, at rank 5.
The feed identity domain is live. The migration moved zero rows. The backfill will tell us what the user meant when they never said.