ADR-027: The database schema lives in the repo, with a typed twin


Status	Accepted
Date	2026-08-01
Author	Łukasz Bodurka
Tags	data, schema, disaster-recovery, types

Context

The standard says where every kind of knowledge lives - behavior in specs, decisions in records, procedures in skills - and says nothing about the database schema, which in a product repo is the most consequential structure in the system and the one thing a backup restores into.

Two failure modes follow, and both are ordinary:

The schema exists only in the running database. The repo holds a chain of migrations, so the current shape is a fold nobody performs by reading. Losing the database means replaying that chain and hoping it still applies; reviewing a table means opening a client against an environment. There is no floor.
Every access path restates the row shape. Each query, DTO and handler carries its own idea of what a row is, so nothing enforces that the code's shape and the table's shape agree - they diverge silently, one column at a time.

The operating rule this repo already runs under assumes the fix without ever requiring it: an agent never writes to a remote database, it prepares a reviewed .sql file for a human to apply. The shipped guard's own denial message names database/schema/ as where that file belongs. The standard never said the directory should exist.

Options considered

Migrations are the schema. The current state is the fold of the chain, and the chain is already in the repo. Rejected as sufficient, kept as necessary: replay is not a read. Nobody reviews a table's current shape by reading twelve deltas, and a chain that breaks halfway has nothing underneath it.
The ORM model is the schema, DDL is generated from it. One source, no pairing to maintain. Rejected: it binds the repo's most durable asset to a library's lifetime and to whatever that library can express - partial indexes, check constraints, triggers, grants and extensions do not round-trip, so the generated DDL is a subset presented as the whole.
A pg_dump --schema-only artifact committed by CI. Always true by construction. Rejected as the primary copy: it is machine output, so its diff is unreviewable and it records what happened rather than what was intended. It is a fine check against the authored DDL, which is where it belongs.
Executable DDL in the repo, plus a typed definition the code goes through, kept 1:1. Chosen.

Decision

A repo that owns a database carries that schema twice, on purpose, and the two copies are one declared pair.

database/schema/ holds executable DDL, complete enough to rebuild the database from a checkout alone. That is the disaster-recovery copy, the thing a reviewer reads, and the artifact a schema change ships as.
A typed, documented definition in the stack's idiom (Zod in TypeScript, Pydantic in Python) is the shape every read and write path goes through - parsing, validation and inference come from it, not from inline restatements.
The pair is 1:1: every table, column, constraint and enum present in one is present in the other, each side names its counterpart, and a change to either lands in the same PR as the change to the other.

Either side may be generated from the other where a stack has a generator worth trusting; generation changes who types the characters, not the rule. Migrations stay how a change reaches a database - they are the delta, never the readable current state.

Consequences

Positive: disaster recovery starts with a checkout instead of an archaeology session. A reviewer reads the current shape in one file. The code has one idea of a row, and it is the table's. The pairing is a declared edge, which is the shape the coupling guard already knows how to check - so this can become mechanical rather than staying a promise.
Negative / cost accepted: two artifacts to keep in step, and until a per-stack generator or drift check exists, the 1:1 claim is held by review. A generator that silently drops constraints is worse than no generator, because it makes the DDL look derived while it rots.
Follow-ups: per-stack mechanics - where the typed definition lives, how it is generated, whether the types themselves agree - belong in the stack repos, not here.

Confirmation

scripts/schema-pair.mjs, shipped as a manifest guard, so self-verify counts a broken pair as drift. It proves the two things that are provable without knowing the twin's language: the declared edge resolves both ways, and every name the DDL defines - table, column, enum type, enum label - appears in the twin (compared case- and separator-insensitively, so a camelCase twin of a snake_case column matches). A repo with no database/schema/ reports that and passes; the rule is conditional on owning a database.

Two things stay outside it, deliberately. Whether the types agree needs the twin parsed structurally, which means knowing the language - that is a stack-repo check. And a field in the twin with no column behind it is not drift: the database is the source of what exists, and a typed module legitimately carries input shapes and derived fields.

Revisit when

A stack ships a generator plus a drift check that makes one side genuinely derivable from the other - then the rule fixes the direction instead of leaving it open. Or a repo arrives whose database is not owned by it (a shared, externally governed schema), which the rule deliberately does not cover.

R24 and R19 in standard/SPEC.md; ADR-012 (in-repo instructions are the source of truth); ADR-018 (history lives in the changelog - the schema file carries no change-log section either).