Three workshops existed independently: one that queues work on documents, one that queues work on video episodes, one that queues work on memory entries. Each had grown the same tables — a queue row, a claim so two workers cannot take the same job, a lease so two results cannot land at once, a log.
Structurally identical. Textually different in exactly one respect that mattered: each named the subject of a job after its own domain. And each already had a live database, with real rows in it, and its own index names.
Why That Detail Nearly Killed the Extraction
If the shared builder had imposed one vocabulary, adoption would have meant a data migration in every workshop: rename a column, rebuild indexes, migrate live rows, and be certain nothing referenced the old name. That is expensive, it is risky, and — most importantly — it is the kind of cost that stops an extraction from ever being attempted. A refactor that requires a migration gets scheduled, and scheduled work in a personal project is work that does not happen.
So the builder takes the column name as an argument, and emits CREATE TABLE IF NOT EXISTS. The consequence is worth stating precisely: an existing database is never altered, because the statement is a no-op against a table that already exists. A fresh database — a test fixture, a new workshop — gets the canonical shape. Adoption becomes code motion with no storage change at all, which is why all three could adopt in a day and prove it with their existing suites.
Design the extraction so that adopting it changes no data. The largest hidden cost of consolidation is almost never the code — it is whatever the code has already written down. An extraction that can be adopted by editing imports will be adopted. One that requires a migration competes with feature work, and loses, and the duplication survives another year while everyone agrees it should be fixed.
Column Position Is Part of the Contract
One more detail, easy to dismiss as fussiness. The builder slots each app's extra columns at the position that app's live table already has them, rather than appending everything at the end.
This matters because a fresh test database and a production database should project identical row shapes. Code that indexes a row positionally, a test fixture that constructs rows in order, a diagnostic that prints columns — any of these can behave differently between a table built by the new builder and a table built years ago. Matching position means a test written against a fresh database is evidence about the real one, which is the entire reason the tests are being run.
Code
One builder, three live databases, zero migrations·python
def delegations_ddl(subject_col: str,
extra_cols: tuple[str, ...] = (),
review_cols: bool = True,
tail_cols: tuple[str, ...] = ()) -> str:
"""The queue row, with the app's own vocabulary injected.
`subject_col`: what a job is ABOUT. The kernel never learns it.
`extra_cols` : full column definitions slotted after `pipeline`,
at the position each live table already has them.
`tail_cols` : columns that ride after `commit_sha`.
`review_cols`: False for the workshop that has no reviewer
machinery at all - see the next lesson.
IF NOT EXISTS is load-bearing: an existing database is never
altered, so adoption is code motion, not a data migration.
"""
review = (
" review_on INTEGER NOT NULL DEFAULT 1,\n"
" review_brain TEXT,\n"
if review_cols else ""
)
extras = "".join(f" {col},\n" for col in extra_cols)
tail = "".join(f",\n {col}" for col in tail_cols)
return (
"CREATE TABLE IF NOT EXISTS delegations (\n"
" id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
f" {subject_col} TEXT NOT NULL,\n"
" pipeline TEXT NOT NULL,\n"
f"{extras}"
" template_version INTEGER NOT NULL DEFAULT 0,\n"
" brief_path TEXT NOT NULL,\n"
" main_brain TEXT NOT NULL,\n"
f"{review}"
" notes TEXT NOT NULL DEFAULT '',\n"
" status TEXT NOT NULL DEFAULT 'queued',\n"
" created_at TEXT NOT NULL,\n"
" taken_at TEXT,\n"
" taken_by TEXT,\n"
" landed_at TEXT,\n"
f" commit_sha TEXT{tail}\n"
");\n"
)
def claims_ddl(subject_col: str, index_name: str) -> str:
"""One active claim per subject. The PARTIAL UNIQUE INDEX is the
race-free guarantee - the database refuses a second open claim, so
no application-level lock is needed. `index_name` keeps each app's
existing index so adoption creates no duplicate on a live DB."""
return (
"CREATE TABLE IF NOT EXISTS claims (\n"
" id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
f" {subject_col} TEXT NOT NULL,\n"
" session_id TEXT NOT NULL,\n"
" acquired_at TEXT NOT NULL,\n"
" released_at TEXT\n"
");\n"
f"CREATE UNIQUE INDEX IF NOT EXISTS {index_name}\n"
f" ON claims({subject_col}) WHERE released_at IS NULL;\n"
)
# Each app's shim, in one line each:
# documents_schema = delegations_ddl("doc_slug")
# episodes_schema = delegations_ddl("slug",
# extra_cols=("pointer TEXT NOT NULL DEFAULT '{}'",),
# tail_cols=("render_path TEXT",))
# memory_schema = delegations_ddl("vault", review_cols=False)
Find two services in your world with near-identical tables that differ only in domain naming. Write the builder function that would produce both, taking the differing names as parameters. Then answer the real question: could each service adopt it without a migration? If not, list exactly which differences force one — those are the ones worth negotiating away before any code is written.
Hint
Compare column ORDER as well as column names. Two tables with the same columns in different orders will produce different results for anything positional, and that difference is invisible in a schema diff that sorts its output.
Progress
Progress is local-only — sign in to sync across devices.