Catching PostgreSQL Schema Drift in CI With ForgeSchema Verify

Picture a team with three environments: development, staging and production. The schema lives in a YAML file in the repository, and everyone agrees that file is the truth. Six months in, nobody is quite sure it still is.

Staging got an extra index during a load test. Production has a column someone added from a SQL console during an incident. Development was rebuilt from scratch last week and is probably fine. Nobody wrote any of this down.

A read-only check that fails the build

forgeschema verify compares a live database with a schema file and reports what differs. It runs inside a read-only transaction, reads catalog metadata only, writes nothing and repairs nothing. That makes it safe to point at production from a CI job.

# DATABASE_URL comes from the CI secret store, never the command line
./bin/forgeschema verify schema/app.yaml

The exit code is the part a pipeline cares about. 0 means the supported structure matches. 4 means drift was found, and the report still goes to stdout as JSON:

{
  "status": "drift",
  "schema": "public",
  "differences": [
    {
      "kind": "column_unexpected",
      "table": "customers",
      "column": "legacy_flag",
      "expected": "absent",
      "actual": "present"
    }
  ]
}

The real report also carries the expected and actual fingerprints. Differences come out sorted by table, column, index, constraint and kind, so the same drift produces the same output every run. That makes diffs between two nightly reports readable.

What it won’t do

Verify describes, it doesn’t propose. A renamed column shows up as one missing and one unexpected, because verify doesn’t infer renames. It doesn’t generate SQL to fix anything. And the file has to describe the whole managed schema: an extra table in the database is drift, not something to ignore.

A match is also narrower than it sounds. It means the tables, columns, types, defaults, identity, keys, constraints and indexes ForgeSchema supports line up at that moment. It says nothing about grants, comments, row contents, or whether the app is happy.

If the database holds something ForgeSchema can’t represent, say a view or a trigger in the managed schema, verify fails with an error instead of producing a partial report. In a CI job that’s a loud failure, which is the right kind.

For the team in the story, a nightly job against each environment turns “probably fine” into a report they can read in the morning.