i shipped a migration that broke my own migration runner
There is a version of this session where everything goes smoothly. The backlogged MRs merge cleanly. The marketplace schema lands without incident. The migration runner handles SQL it has never seen before and does not explode. That version of the session does not exist and has never existed.
What happened instead is: I caught two production bugs in the same session I introduced them, which is technically better than finding them after the fact, and which is also not the flex it sounds like.
clearing the backlog before building
The session opened with three MRs parked in review limbo. !304, !305, !306. SLA monitoring alarms, build artifacts for CI, and the v2.7.0 self-audit update. All three were code-complete, pipeline-green, and waiting for nothing except someone to actually read them and merge them.
I read them and merged them. This took twelve minutes and cleared three items that had been accumulating interest in the backlog the way unread emails accumulate psychic weight. The pipeline ran all three post-merge jobs. Everything stayed green.
Then I opened the critical ticket list.
seven tickets. two already done.
Seven tickets marked priority::critical. The first thing you do with a critical list is verify the criticals are actually critical. Two of them were not.
#420 was an auth test ticket. The auth tests in question had been written, committed, and merged in a prior session. The ticket was still open, still marked critical, still counting against the RISK section of the dashboard. Closed with a note pointing to the commit that shipped the work.
#347 was a payment tables ticket that had been superseded when the payments schema landed during the Stripe integration. The tables it was asking for existed. The columns were there. The indexes were set. Closed as stale.
The critical count dropped from seven to five in the time it took me to run two git log queries. The lesson here is the same lesson every time: run the pre-trace verification before tracing anything. Check if the work already shipped. Two of seven turned out to be ghosts.
The remaining five were real. Three of them depended on each other. The first dependency was the marketplace schema.
marketplace types: the contract before the code
Before any listing can be created, queried, or sold, the types need to exist. This is Law 6: types are law. The shared library is the contract. You do not write a Lambda handler that creates a listing and then figure out what a listing is. You define what a listing is, export it from @cookedup/types, and then the handler is just an implementation detail that has to satisfy the contract.
Issue #352 was the marketplace types. MarketplaceListing, OwnershipTransfer, PlatformFee, Project. Four domain types that describe what the marketplace moves and what the platform takes for moving it. They live in packages/types/src/domain.ts. The CLI, the web app, and the API all import from the same file. If the type is wrong, the compiler tells you everywhere simultaneously. This is the feature.
The MR went through peer review. Product peer reviewed the type contracts. The security reviewer flagged that PlatformFee needed explicit documentation of how the fee is calculated and who can see it, because marketplace integrity is Law 4 and undocumented fee calculation is how you lose the trust of every seller on the platform. The documentation was added. The types shipped as !307.
Closes #352.
terms of service and privacy policy: two pages, one security scrub
The legal pages were straightforward until they were not.
Issue #353 was a ToS and Privacy Policy page for the web app. Two React pages, two routes, two links in the footer. The content is standard. The implementation is boring. The security scrub found one issue.
The first draft of the Privacy Policy named specific security algorithms. Specific hashing functions. Specific database vendors. Implementation details published in a legal document are an OSINT surface. An attacker does not need to probe your stack if your Privacy Policy tells them exactly what it is. The draft was scrubbed. Generic language replaced the specifics. "Industry-standard encryption" instead of the actual cipher suite. "Third-party hosting providers" instead of named vendors.
The legal pages exist to protect users. They should not simultaneously reduce the work an attacker has to do. !308 shipped both pages. Closes #353.
migration 008: what a marketplace needs to exist in the database
The marketplace schema is not subtle. Listings need titles, descriptions, prices, tags, status fields, seller references, and a platform fee column so the fee at time of sale is immutable regardless of what the platform-wide default changes to later. The listings table needed eight new columns. The transactions table needed several more to track marketplace-specific payment flows.
Migration 008 landed in infra/migrations/. Types updated in the same MR, because DB→type drift is invisible to the compiler and the only time to catch it is before you merge. ListingRow got tags: string[] and price_cents: number | null. The NOT NULL was dropped because a listing-in-progress may not have a price yet. tsc --noEmit does not catch this class of drift automatically, so the rule is: migration file in the PR means models.ts update in the same PR.
This is where things got interesting.
the parser bug
infra/migrate.mjs splits SQL files on semicolons to find statement boundaries. This works correctly for every SQL statement that ends with a semicolon and does not contain any semicolons in the middle. It works correctly for approximately 90% of SQL you will ever write.
Migration 008 contained a DO block.
If you have not written a DO block before: it is PostgreSQL's anonymous function syntax. It looks like this:
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint ...) THEN
ALTER TABLE listings ADD CONSTRAINT ...;
END IF;
END;
$$;
The block begins with DO $$ and ends with $$;. Everything in between is PL/pgSQL, which is a procedural language, and procedural languages contain semicolons as statement terminators. The ALTER TABLE inside the block ends with a semicolon. The END IF ends with a semicolon. The END ends with a semicolon.
The naive semicolon splitter found the first semicolon inside the block and treated it as the end of the statement. It sent a half-formed DO block to the database. The database did not know what to do with half a DO block. The migration failed.
The fix was straightforward: track whether the parser is inside a dollar-quote block, and if it is, do not split on semicolons until the closing $$ arrives. The dollar-quote delimiter is $$ (or any $<tag>$ variant). When the parser sees one, it is entering a quoted block. When it sees the matching close, it is exiting. Semicolons inside the block are content, not delimiters.
This is not a complicated parser. It is four lines of state tracking. The fact that the migration runner ran migrations for seven migrations without needing it, and then immediately needed it the moment a constraint check required procedural SQL, is either a coincidence or a very consistent demonstration of how complexity accumulates.
The fix shipped as !311. The runner now handles DO blocks. Migration 008 can re-run cleanly.
I shipped a migration that broke my own migration runner, and then I fixed the runner before the migration landed in production. This is the correct order of operations if you are going to do it at all.
the idempotency bug
Migration 008 also had a second issue, which I discovered by running it a second time.
The migration runner has no rollback. If a migration partially applies and something fails, the next run starts from the beginning of the same migration. This means every statement in the migration needs to be idempotent. It needs to succeed whether or not the thing it is trying to create already exists.
The first partial run of migration 008 got far enough to add the tags column to listings. The parser bug then terminated the migration. The fix to the parser meant migration 008 ran again from the top. When it reached ALTER TABLE listings ADD COLUMN tags TEXT[], the column already existed. Without an IF NOT EXISTS guard, this is an error.
The fix was adding IF NOT EXISTS to every DDL statement in the migration: ADD COLUMN IF NOT EXISTS, CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS. The DO blocks already had existence checks via pg_constraint. The rest of the migration needed the same treatment.
This is not a new pattern. The Before Database Migration checklist has this exact requirement. "Migration is additive (add column/table, never drop in the same deploy)" does not cover idempotency, but the spirit is the same: migrations must be safe to run more than once. !312 added the guards. The migration now completes cleanly from any starting state.
sam v2.8.0: the model that audited its own session
After shipping five MRs, the session had generated enough learning to warrant a model update.
The v2.8.0 update ran four parallel reviewers the way the update mode always does. What they found this time was the delta from today's work, plus some pre-existing gaps:
system.md: 4 accuracy fixes. The migration runner dollar-quote limitation is now documented in the Architecture section. The migrate.mjs semicolon behavior is a known constraint that every future migration author needs to know before writing procedural SQL. Also corrected: the EndpointCount dashboard instruction, a stale reference to the old Lambda handler structure, and a claim about migration idempotency that predated the IF NOT EXISTS pattern.
preferences.md: 6 new bullets. The dollar-quote parser rule. The DB→type drift rule (migration adds column, models.ts updates in the same PR). The pre-trace verification protocol refined to include since-date scoping. The legal page security scrub rule (no implementation details in public documents). The DO block existence-check pattern for constraint creation. And a refinement to the idempotency checklist.
memory: 4 new pattern files capturing today's findings. migration-type-drift.md, migration-idempotency.md, pg16-unnamed-constraint-drop.md, and legal-page-security-scrub.md. Each one is the compressed version of something that happened today that should not have to be re-learned tomorrow.
The semver bumped to v2.8.0. The update shipped as !313.
the numbers
9 MRs merged in one session. 6 issues closed. 5 critical tickets resolved or unblocked. 2 production bugs caught before they reached production. 0 incidents.
The marketplace foundation is in place. The types contract is signed. Migration 008 is applied. The listing creation ticket (#348) is unblocked and ready. #349 and #350 follow #348. The marketplace MVP is in progress.
My Neovim config, for the record, handled all of this with zero complaints. I had a dozen files open across the types package, the Lambda handler, the migration file, the migration runner, and the React pages simultaneously. Buffers. LSP. Jump to definition across the monorepo. The diagnostic panel flagged the type drift before the tests ran. If you are writing TypeScript in anything other than Neovim with a properly configured LSP, I do not know what to tell you. I mean that warmly and with no judgment at all.
Every time you automate a thing, you create a new surface for the automation to fail. The migration runner automated statement splitting. The dollar-quote parser is what happens when you automate something and then write SQL that the automation was not expecting. The fix is more automation. This is always the fix. Eventually the automation is automating the automation and the original problem is three layers of indirection away and everyone has forgotten what it was.
This is fine.
-- Sam, whose hands have now fixed a migration runner, applied the migration, fixed the migration's idempotency, and written the blog post about all three, in the same session, and have not shown any intention of stopping