Schema Ownership

The Forge schema is not generated by an ORM. It is written by hand as PostgreSQL DDL, one object per file, and a diff engine works out what to run against a live database to make it match. This page explains why it ended up that way, what "desired state" means in practice when you are editing these files, how the tree is assembled into the single artifact everything downstream consumes, and precisely where the API's own schema code starts and stops.

The product-level framing is on the hub at Architecture § Schema ownership. This page is the mechanism.

Why the schema left EF Core

forge-api once carried a long chain of EF Core migrations. They were collapsed into a single baseline and then retired outright; the pg_dump --schema-only of that proven baseline is the one-time seed of the schema/ tree, ingested by the harness's baseline verb (SqlDumpSplitter.cs routes each pg_dump object to its file). The move was driven by three things a migration generator handles badly:

Objects the model cannot express. The pgvector extension behind document search, and the plpgsql functions plus triggers that make posted accounting entries immutable, lived in a hand-written migrationBuilder.Sql() step because EF has no vocabulary for them. In a desired-state tree they are ordinary files in schema/functions/ and schema/triggers/.

A silent diff gap is catastrophic. During the squash the ledger immutability triggers were dropped and the schema check in use at the time still reported the schemas synced. It was the test suite that caught it — a single missed trigger had broken append-only accounting. That episode is why verify still runs an explicit pg_extension / pg_proc / pg_trigger comparison on top of the engine's own diff; see The Reconcile Harness.

A migration chain is history; a schema is state. A chain has to be replayed in order forever, and its files accumulate. A desired-state tree is read, not replayed: the current definition of a table is the whole of schema/tables/<table>.sql and nothing else.

The engine choice was contested once and is settled. An earlier build ran on Atlas, whose free tier gates CREATE EXTENSION, CREATE FUNCTION and CREATE TRIGGER behind an account — a non-starter for a self-hosted open-source stack. pg-schema-diff is MIT, needs no registration, and handles the extension, vector columns, identity columns, functions and triggers natively. The swap was contained to one file, PgSchemaDiffRunner.cs, which is the shape to preserve if the engine is ever changed again.

What desired state means when you are editing

Each file is the final definition of exactly one object, not a step toward it. You add a column by editing the table's CREATE TABLE. You never write an ALTER. The engine derives the ALTER by comparing your tree against the live database. This is the single discipline the whole repo rests on, and the most common way to get it wrong is to reach for premigrate/ because a change feels risky — a column added by hand there is a column the desired state does not know about, and the very next reconcile plans to drop it.

The rule of thumb: if the change is describable as a difference between two states, it belongs in schema/. If it is only describable as an action, it belongs in premigrate/.

A table file carries the table and everything owned by it — identity/sequence, primary key, unique and check constraints, and its foreign keys — so the file reads as a self-contained definition. Indexes get their own files in schema/indexes/. Ordering inside a file is for humans; the engine derives apply order.

Four conventions in the tree are not stylistic:

• Explicit snake_case names on every constraint and index. This is what lets the tree round-trip through pg_dump cleanly and lets the diff produce stable, readable output. An anonymous constraint is a rename waiting to happen.
• Names are truncated at 63 characters by PostgreSQL. Several foreign-key names are longer than that as authored and are stored truncated. Anything in the harness that compares by name must compare against the truncated form, not the authored one.
• There is no schema/enums/. Enumerations are int columns plus reference_data rows. Native PostgreSQL enums fight declarative apply (ALTER TYPE ... ADD VALUE is not expressible as a state difference) and the application already enforces the values.
• In extensions/, functions/ and triggers/, the filename must equal the object name. verify's explicit check builds its expected set from filenames alone, so a mismatched name reads as one object missing and another extra.

One wart in the tree is deliberate and worth knowing before you "clean it up": a number of tables carry vestigial column defaults — booleans defaulted to false, numerics to 0, strings to '' — inherited from legacy backfill migrations. The application always sets those values. They were kept so the cutover from EF stayed a provable no-op. They are safe to remove, but removing one is a schema change like any other and belongs in its own commit, not smuggled into an unrelated edit.

Assembly: from tree to one ordered file

pg-schema-diff takes a directory of DDL and applies it to a temporary database in statement order — it does not topologically sort. So the tree cannot be handed over as-is. DesiredStateAssembler flattens it into one ordered file:

extensions → tables (minus their FKs) → every foreign key → indexes → functions → triggers → keep-alives

Pulling the foreign keys out of the table files and emitting them after every table exists is what dissolves circular-FK ordering; it is also why a table file is free to be organised for reading. Extensions come first because the engine applies this DDL to its own scratch database, where vector-typed columns will not resolve until the extension exists.

The tail of that file holds keep-alives: exact DDL for objects that live in real databases but are deliberately absent from the committed tree, injected so the engine sees them on both sides and never plans a DROP. There are two — EF Core's __EFMigrationsHistory table, which EF owns and forge-db does not, and a sequence the application creates at runtime for job numbering. Keep-alives are injection rather than exclusion on purpose: an exclude selector hides an object from the diff in both directions, while a keep-alive states what the object is supposed to look like.

Two schemas are excluded outright at the engine level instead, because nothing in this repo authors them: hangfire, which the background-job library installs and migrates itself at application start, and forge_db, the harness's own bookkeeping schema. Without those exclusions a reconcile plans DROP TABLE across every Hangfire table on the first run.

You can look at the assembled result any time:

dotnet run --project src/Forge.Db -- assemble --out /tmp/desired.sql

What SchemaBootstrapper does, and what it does not

forge-api embeds the assembled file as forge.data/Schema/forge-schema.sql and applies it through SchemaBootstrapper at boot. Being precise about its scope matters more than anything else on this page, because misreading it is how installs end up broken.

It does: ensure the target database exists at all, creating an empty one through the maintenance database if it is missing (the wipe escape hatch drops the database with nothing else to recreate it); probe a sentinel core table; and, only when that table is absent, execute the entire embedded schema as one raw batch. Dollar-quoted function bodies survive because the batch goes over the simple-query protocol.

It does not: compare anything, reconcile anything, or apply anything at all when the sentinel is present. On every populated install it is a no-op that logs a line saying so. It has no notion of a version, so it cannot tell a database one release behind from one ten releases behind.

The consequence is the one to carry away. A fresh database is provisioned by the API; a populated database is moved forward only by the forge-db reconcile. An upgrade that swaps the API image without running the reconcile puts a new binary in front of a schema that does not have the relations it expects, and nothing in the API's boot path will notice or repair it. The operator-facing version of this warning, including the flag that gates the reconcile and the split-topology trap, is on the hub at Upgrades and Rollback; the mechanism is in The Reconcile Harness.

Keeping the embedded copy honest

The embedded SQL is a build artifact of this repo, and it goes stale the moment schema/ changes. Regenerate it explicitly:

dotnet run --project src/Forge.Db -- assemble --repo <forge-db> --out <forge-api>/forge.data/Schema/forge-schema.sql

forge-api carries a schema-drift-check workflow that checks out forge-db, re-assembles the tree and diffs it against the committed embedded file, failing on any difference. Read its triggers before relying on it: as it stands the workflow runs on manual dispatch only — its pull_request and push triggers are commented out, and its header still describes the schema tree as living on an unmerged branch. Until those triggers are restored, nothing automatically catches a schema/ change that was not accompanied by a regenerated embedded file, and the failure surfaces later as a fresh install provisioned from a stale schema. Treat the regeneration as part of the schema change, not as something CI will remind you about.

Two other places where the committed design documents run ahead of the code, both worth knowing so you do not go looking for machinery that is not there:

• docs/DESIGN.md §6 says the API's boot becomes read-only — running verify and refusing to start on drift. That is not implemented. The boot path applies-or-no-ops through SchemaBootstrapper and starts either way. Drift is caught by the reconcile step and by CI, not by the container.
• §5 describes the drift check as building two scratch databases (one from schema/, one from the EF model) and running verify between them. The workflow that exists does a textual diff of the assembled SQL against the embedded file. The complementary invariant — that the EF model still maps onto this schema — is carried by forge-api's Postgres-backed test collection, which runs real queries against it.

Where a doc and the code disagree, the code wins; both of the above are bugs in the docs worth fixing when someone touches those areas.

Related

• The Reconcile Harness — how the tree gets applied to a live database.
• Dump and Import — moving data between databases built from this tree.
• forge-api wiki — the EF mapping layer that sits on this schema.
• Data Ownership and Export — the schema is published, and what that means for an adopter.