feat(dbtool): preflight the target database's schema version and fail with an actionable message on mismatch

[ericfitz]

tmi-dbtool assumes the database it connects to already has the schema its own compiled-in models expect. When that assumption is wrong it fails deep inside an operation with a raw driver error, and on some paths it can fail after partially writing.

How it bites

Concretely, from #794 (which adds a system_settings.origin column):

Deploy order matters. The server owns migration — it runs AutoMigrate plus the internal/dbschema steps at boot. tmi-dbtool does not migrate on the read/write paths; only --schema does. So running tmi-dbtool --import-config against a database that the new server binary has not yet booted against hits a column that does not exist, and the operator gets:

• Oracle: ORA-00904: "ORIGIN": invalid identifier
• PostgreSQL: ERROR: column "origin" of relation "system_settings" does not exist (SQLSTATE 42703)

Neither says "your database is older than this binary expects, boot the server first." An operator mid-rollout has to reason backwards from an identifier name to a deploy-ordering mistake.

This is not specific to #794 — it is structural, and it recurs on every schema change. It is also easy to hit in exactly the situations where it hurts most: a production cutover, a deploy-aws.sh --config-export round trip, or a make dev-config-restore.

The check already exists in-tree

internal/dbschema/schema_version.go maintains a single-row tmi_schema_versions table stamping a SHA-256 fingerprint of the migrated model set (#480). The pieces are all public:

• dbschema.ComputeModelsFingerprint(models ...any) string — what this binary's models hash to
• dbschema.SchemaFingerprintCurrent(db, desired) bool — whether the database's stamp matches
• dbschema.RecordSchemaFingerprint(db, fp) — how the server stamps it after a successful migrate

So dbtool can answer "is this database at the schema version I was built for?" in one cheap read, before doing anything else.

What to build

A preflight in tmi-dbtool that runs after the DB connection is established and before any operation touches a table:

1. Compute the fingerprint of dbtool's compiled model set.
2. Read the stamp from tmi_schema_versions.
3. On mismatch or missing stamp, fail fast with an actionable message rather than proceeding — naming what was found, what was expected, and the remedy.

Something like:

error: database schema is not at the version this tool expects.

  database fingerprint: a3f1c9e2… (stamped 2026-08-19T04:12:03Z)
  expected fingerprint: 7b40d5aa… (tmi-dbtool 1.8.22)

The TMI server owns schema migration and applies it at startup. Deploy and start
the server against this database first, then re-run tmi-dbtool. To migrate with
this tool instead, run: tmi-dbtool --schema --config <config>

Design notes worth settling in the issue:

• --schema must be exempt — it is the remediation path, so it has to be allowed to run against an out-of-date schema. Same for --health if that is meant to diagnose a sick database.
• A missing stamp is ambiguous. A pre-Investigate GORM AutoMigrate timing against a remote database (per-object introspection × network latency) #480 database, or one migrated by an older binary, has no row. Treat that as a warning rather than a hard failure, or gate it behind a flag — refusing to run against every legacy database would be worse than the problem.
• Provide an override. --skip-schema-check (or similar) for an operator who knows better, since a fingerprint mismatch is conservative: it changes whenever any model's migratable definition changes, including changes irrelevant to the operation being run.
• Direction matters. dbtool older than the database is usually harmless (the extra column is simply unused); dbtool newer than the database is what breaks. The fingerprint is an equality check and cannot distinguish the two, so the message should say "does not match" rather than claiming the database is old. If distinguishing them is worth it, that needs an ordered schema version, not a hash — probably a separate discussion.
• The check is two round-trips at most, so it is safe to run against Oracle ADB on every invocation.

Affected commands

Everything that reads or writes application tables: --import-config / -c, --export-config, --import-test-data / -t, --import-legacy / -l, --backfill-empty-strings, and the config strip/reference paths.

Related

• Investigate GORM AutoMigrate timing against a remote database (per-object introspection × network latency) #480 — introduced the schema fingerprint and tmi_schema_versions
• fix(config): TMI_OAUTH_CALLBACK_URL duplicates a DB-authoritative setting, and the env/DB precedence is inconsistent per key #794 — the change whose rollout surfaced this; its PR body carries the deploy-ordering warning as prose, which is exactly the kind of instruction that should be enforced by the tool instead