After CI applies the whole change chain to a throwaway database, what else must the job assert?

## Why "it applied" is not a green build Applying the change chain to a throwaway database proves the chain is internally consistent. It says nothing about whether the schema that came out is the schema the code needs. Those two artefacts — the change chain and the object-to-table mapping in the code — are edited by hand, in separate files, often in separate commits, sometimes by separate people. They drift silently, and the drift is only discovered when a query is generated against a column that is spelled differently, is nullable when the code assumes it is not, or does not exist at all. So the job that applies the chain has three assertions to make, not one. ## Assertion 1 — the schema satisfies the mapping After the chain has run, compare the produced schema against what the mapping declares and **fail the build on a mismatch**. A useful comparison looks at more than table and column names: - **Presence** — every mapped table and column exists, with the spelling and case the mapping uses. - **Type compatibility** — the stored type can hold the mapped type, including precision and scale on numeric columns and length on text columns. - **Nullability** — a column the code treats as always-present is actually declared not-null; the reverse mismatch is quieter but produces surprises on write. - **Keys and uniqueness** — the primary key columns match, and any uniqueness the code relies on to avoid duplicates is enforced by a constraint rather than by hope. - **Key generation** — the mechanism the mapping expects to supply new identifiers exists on that column, whether the engine offers identity columns or separate sequence objects. - **Relationships** — the join columns and link tables the mapping expects are present, whether or not foreign keys are enforced. The two directions of mismatch are not symmetric. Something the mapping needs and the schema lacks is fatal — the code will fail on first use. Something the schema has and the mapping does not know about is usually fine, and treating it as an error makes the check unusable on any database that also carries reporting tables or columns owned by other consumers. | Mismatch | Verdict | |---|---| | Mapped column missing from the schema | Fail the build | | Type or length too small for the mapped type | Fail the build | | Mapped-as-required column is nullable in the schema | Fail, or warn if writes cannot violate it | | Column exists in the schema, unknown to the mapping | Ignore, or report only | | Index the mapping does not know about | Ignore | ## Assertion 2 — the code paths actually run against it A structural comparison is a static check, and it misses everything that only shows up when a statement is generated and executed: a reserved word used as a column name and quoted differently by the two sides, a value that will not round-trip through the chosen type, a generated key that comes back empty, a cascade the schema does not permit. Run the data-access tests **against the database the chain just built**, in the same job, so that the schema under test is never one produced by a different route. A handful of read-and-write tests per aggregate is enough to turn most of those into a failing test. ## Assertion 3 — the chain is complete for this commit The last assertion is about the commit rather than the schema: if the mapping changed and no change unit accompanies it, the build should fail rather than wait for the deploy to notice. Comparing the mapping's expected shape against a database built only by the chain gives exactly that signal, because the missing column shows up as a mismatch in assertion 1. This is why the comparison must run on a database the chain built from nothing — a database created any other way already contains the column, and the missing unit stays invisible until deploy. ## Ordering inside the job 1. Create an empty database of the production engine family. 2. Run the whole chain; fail on any error. 3. Run the schema-versus-mapping comparison; fail on any fatal mismatch. 4. Run the data-access tests against that same database. 5. Tear it down. Two habits keep this honest. First, no step may modify the schema outside the chain — no test that quietly adds a column it needs, no fixture that creates a table. Second, the comparison's severity rules belong in version control with everything else, so relaxing one is a reviewable change rather than a local flag someone set to get their build green.