Four registries answer whether a migration is applied and no two agree - three repo-side disagreements found today by accident, none by a check: add a source-only test comparing applied/, draft/ and REPLAY-ORDER.md
FOUR REGISTRIES ANSWER "HAS THIS MIGRATION BEEN APPLIED" AND NO TWO OF THEM AGREE. supabase_migrations.schema_migrations, the db/migrations/applied/ directory, db/migrations/applied/REPLAY-ORDER.md, and each migration file's own header. Every one of them was found wrong today, each in a different direction, and none of them was found wrong by a check - all four came out of chasing an unrelated question. THE FOUR INSTANCES, so the count is the argument rather than any single miss. TERRA-156: ten applied migrations with no row in schema_migrations, three of them explained by the enamel co-tenancy lane and seven unexplained. TERRA-158: terra_29 promoted to applied/ while a stale draft twin stayed behind still reading "DRAFT ONLY. NOT APPLIED." - it misled a DB lane into reporting terra_29 as an unregistered apply within two hours of being found. TERRA-159: terra_53 applied, live on terraParticipantes since 13:10, its ONLY copy in draft/ and nothing in applied/. Also TERRA-159: REPLAY-ORDER.md skipped terra_53 entirely, so a replay would have rebuilt the database WITHOUT the membership guard that is currently in force. THE INSTRUMENT IS CHEAP AND READS ONLY SOURCE, WHICH IS WHY IT IS WORTH BUILDING NOW. Three arms, all filesystem comparisons, no database connection and no credential: Arm A. Every .sql in db/migrations/applied/ appears as an entry in REPLAY-ORDER.md's ordered list, and every entry names a file that exists. This is the arm that catches terra_53's replay omission. Arm B. No file in db/migrations/draft/ declares itself applied. Fail on a header matching APPLIED as Supabase migration or STATUS: APPLIED anywhere in draft/. This catches terra_53's misplacement and reads the file's own claim rather than a directory convention. Arm C. No name-stem in draft/ has a twin in applied/. This catches terra_29's stale copy. Compare stems, not bytes - the twins differ by an entire authorization header, so a byte comparison finds nothing and a stem comparison finds everything. WHAT IT DELIBERATELY DOES NOT DO, stated so the coverage is not overread. It does not touch schema_migrations, so TERRA-156's class is OUT OF SCOPE - that needs a live connection and belongs to a DB lane. This instrument answers "do the repo's three records agree with each other", not "do they agree with the database". A green run is not evidence that anything was applied. GUARD THE GUARD, because all three arms are filesystem reads and all three fail vacuously on a wrong path. Assert non-empty populations before comparing: if applied/ globs to zero files or draft/ globs to zero, the arms pass having compared nothing. That is the exact failure mode a filesystem-reading test has and an import-based one does not, and this repo has already shipped one instrument that read as coverage while executing nothing. AND RUN EACH RED ARM. Not reported - run. Add a probe file, watch the specific arm fail naming it, remove it, confirm green. An assertion that has never failed records nothing, and three of the four defects above sat in place for days looking exactly like correctness.
Activity
-
SHIPPED AS 42cbc47, v0.30.44, 435 tests, and ARM B WAS DEMONSTRABLY POROUS ON ARRIVAL. src/lib/migration-replay-order.test.ts carries three arms with non-empty population guards on every one, and the file states in its own comment that it proves agreement among the repository's records and does not query schema_migrations or prove any SQL was applied. Arms A and C are correct. I DID NOT RE-RUN THEIR PROBE, I WROTE A DIFFERENT ONE, and that is the only reason the hole surfaced. Re-running the reported probe confirms the reporter's own choice of input; it cannot find what that choice excluded. I wrote db/migrations/draft/terra_zz_probe_old_phrasing.sql declaring "APPLIED 2026-07-31 (db-terra-cc, session pooler, ref eprpywqwzsvrjeaforei)" and ran the suite: 3 pass, 0 fail. A draft file announcing itself applied walked through the arm built to catch exactly that. Probe deleted, suite back to 3/3. THE PATTERN WAS BUILT FROM THE ONE FILE THAT MOTIVATED THE ROW. It matches APPLIED AS SUPABASE MIGRATION or STATUS: APPLIED, which is terra_53's phrasing and the newest in the directory. Run against all 53 files in applied/, TEN match neither: terra_11, terra_12A, terra_12A2, terra_12B, terra_20, terra_21, terra_22, terra_27, terra_28, terra_30. terra_11, terra_12A and terra_30 use bare APPLIED with a date and no Supabase clause and no STATUS line. THE FIXTURE WAS SITTING IN THE DIRECTORY THE WHOLE TIME. Every file in applied/ is by definition applied, so the set of phrasings a real declaration takes is READABLE, not inferrable from the single example that prompted the work. Any matcher for a self-declaration has a knowable corpus to be validated against, and validating against it is the difference between a pattern and a guess. THE DISCRIMINATOR IS CASE AND IT IS NOT OBVIOUS. Declarations are uppercase APPLIED; incidental prose is lowercase - terra_20 line 16 "applied 2026-08-01", line 28 "never applied to Participantes". A case-insensitive bare match on the word would turn both into false positives, so widening carelessly breaks the arm in the other direction. Ordered validated across all 53 plus the 4 real drafts before shipping. terra_28 DECLARES NOTHING AT ALL, which bounds the arm permanently: an applied migration is NOT required to announce itself, so this can never be inverted into "every applied file declares". It catches a draft that LIES, never a file that is SILENT. Ordered into the test file as a comment, because the next reader will assume the stronger property. AND THE TWO FILES THAT PROVE THE CLASS SAY IT IN THEIR OWN TEXT, IN THE PHRASING THE ARM MISSED. terra_11 line 3 and terra_12A line 3 both read: "This line read as DRAFT/not-applied while the file already sat in applied/ and had run." The repo was bitten by this exact confusion, wrote the lesson down, and the instrument built to prevent its recurrence could not see the files carrying it.
-
SECOND PROBE, SECOND PASS, AND THE HOLE MOVED RATHER THAN CLOSED. Arm B was widened to treat the uppercase word APPLIED as a claim after stripping NOT APPLIED, which does catch the bare legacy phrasing and lifts corpus coverage from 43 to 48 of 53. I then wrote a draft declaring "Applied as Supabase migration 20260831999999 on 2026-08-31" - title case instead of uppercase - and the suite returned 3 pass, 0 fail. Same claim, same consequence, different shift key. Probe deleted, suite back to 3/3. THE WRONG SUGGESTION WAS MINE. I proposed case as the discriminator after observing that declarations are uppercase and incidental prose is lowercase across the corpus. That observation is true and it is an accident of how people happened to type, not a property of the claim being made. It held for 53 files and failed on the 54th, which nobody has written yet. THE GENERAL SHAPE: ONE ELEGANT DISCRIMINATOR BUYS A HOLE ON ITS INVERSE. The first pattern keyed on the newest phrasing and missed the legacy one. The second keyed on case and missed a capital letter. Each was validated against the corpus and each passed, because the corpus contains only what has already been written - it cannot contain the adversarial variant, which is exactly why the probe has to be authored rather than sampled. REPLACED WITH A UNION OF VALIDATED SHAPES, ALL CASE-INSENSITIVE: applied-as-supabase-migration, status-colon-applied, and a line-anchored legacy declaration - a comment line whose text STARTS with an optional TERRA-nn prefix then APPLIED then an ISO date. Not a cleverer single rule; three shapes, each read off the corpus. THE LINE ANCHOR IS LOAD-BEARING AND ITS PLACEMENT IS SUBTLE. terra_20's prose mentions sit mid-sentence at lines 16 and 28 - "applied 2026-08-01" and "never applied to Participantes" - so a bare case-insensitive word match makes both false positives. But terra_30's REAL declaration is at line 36, so anchoring to the top of the file would miss it. The anchor belongs at the start of a comment line, not at the start of the file. VALIDATION IS BY COUNT BECAUSE NOTHING ELSE SETTLES IT: 48 matched in applied/, exactly terra_20, terra_21, terra_22, terra_27 and terra_28 missed, ZERO of the 4 real drafts matched, and both probes caught. Both probe strings ordered into the test file as fixture comments so the next person widening this inherits the adversarial cases instead of re-deriving them - the corpus will still not contain them then either.
-
CLOSED. 736347a, v0.30.46, 436 tests. src/lib/migration-replay-order.test.ts holds four arms: REPLAY-ORDER lists exactly the applied directory, no draft declares itself applied, the detector catches the retained adversarial probes, and no draft/applied stem overlaps. Ran it here, 4 pass 0 fail. WHAT IT CATCHES, EACH DEFECT LIVE THIS MORNING. REPLAY-ORDER.md skipped terra_53 entirely, so a replay would have rebuilt the database without a membership guard currently in force on terraParticipantes. terra_53 sat in draft/ as the only copy of an applied migration, its own first line saying APPLIED. terra_29's stale draft twin stayed behind reading DRAFT ONLY NOT APPLIED and misled a DB lane into reporting terra_29 as an unregistered apply within two hours. ARM B TOOK THREE REVISIONS AND EACH FAILURE WAS FOUND BY AN AUTHORED PROBE, NOT BY REVIEW. v1 matched terra_53's phrasing, which is the newest file in the directory and the one that motivated the row; 43 of 53 applied files matched, and a draft in the bare legacy form "APPLIED 2026-07-31 (db-terra-cc, session pooler, ref ...)" ran the suite to 3 pass 0 fail. v2 keyed on uppercase APPLIED, reaching 48 of 53, and a draft saying "Applied as Supabase migration 20260831999999" - title case - passed 3 pass 0 fail again. Same claim, different shift key. The case discriminator was MY suggestion and it was wrong for the same reason v1 was: true of the corpus, not a property of the claim. v3 IS A UNION OF THREE SHAPES READ OFF THE CORPUS, all case-insensitive with NOT APPLIED stripped first: applied-as-supabase-migration, status-colon-applied, and a comment-line-anchored legacy APPLIED plus ISO date. Not a cleverer single rule. Anchoring to a comment line rather than to the top of the file is load-bearing in both directions - terra_20's "applied 2026-08-01" and "never applied to Participantes" are mid-sentence and must not match, while terra_30's real declaration is at line 36 and must. VERIFIED BY COUNT AND BY READING THE MISSES, not by accepting the census. 48 of 53 matched; the five that do not are terra_20, terra_21, terra_22, terra_27 and terra_28. I opened all five: each contains only lowercase prose - "inside the applied COMMIT", "this exact applied revision", "was applied BEFORE" - and terra_28 says nothing at all. So the misses are correct and, more importantly, terra_27's two prose mentions are NOT false positives, which was the risk the widening created. THE PROBES ARE NOW A UNIT ARM RATHER THAN A MEMORY. Arm 3 asserts the detector against both retained adversarial strings, so the next person widening this inherits the cases instead of re-deriving them. That matters because the corpus cannot contain the adversarial variant by construction - it holds only what has already been written - so a probe has to be authored, and an authored probe that lives only in a chat transcript is gone. BOUND, and it is written into the test file: this proves the repository's three records agree with EACH OTHER. It never touches supabase_migrations, so TERRA-156's class is out of scope and a green run is not evidence that anything was applied. It also catches a draft that LIES, never a file that is SILENT - terra_28 declares nothing and is legitimately applied, so this can never be inverted into a requirement that every applied file announce itself. STILL OPEN ELSEWHERE: the fourth registry. schema_migrations versus the repo needs a live connection and belongs to a DB lane.