DB schema check (docs/db ⇔ live DB)

← ConceptsDB schema check (docs/db ⇔ live DB)Every table under `docs/db/` is a schema-only OpenAPI file describing the real database. The builtin db-schema checker in `jsonui-doc check` compares those files against the live schema — declaration vs declaration, confidence `proof` — so migrations can never drift away from the docs unnoticed. This page covers the file format, exactly what gets compared, and how to wire the check up.~6 min read

Migrations move, docs stay behind`docs/db/` is the source of truth for the data model — the generated HTML, the ERD, and every agent working on the project read it. But the real schema moves through migrations (ORM autogeneration, hand-written SQL), and nothing in that path touches `docs/db/`. `jui verify --fail-on-diff` cannot see this drift either: it guards the docs→code pipeline, not docs→database. The db-schema checker closes the loop from the other side — it reads the real database's schema and compares it against `docs/db/`, table by table, column by column.

The docs/db table JSONOne table per file, written as schema-only OpenAPI: the first non-enum object schema in the file is the table (a file that declares `paths` is treated as an API doc and skipped). The table name comes from `info.x-table-name`, falling back to the snake_case of the schema name. Columns are the schema's `properties`, and DB-specific facts ride on `x-*` extensions. One deliberate convention: a column is NOT NULL unless it declares `nullable: true` — the OpenAPI `required` array is not reused as a NOT-NULL marker. Multi-database projects use one directory per database (`docs/db/<name>/`).

docs/db/users.json

{
  "openapi": "3.0.3",
  "info": { "title": "users table", "x-table-name": "users" },
  "components": {
    "schemas": {
      "User": {
        "type": "object",
        "properties": {
          "id":         { "type": "integer", "format": "int64",
                          "x-primary-key": true, "x-auto-increment": true },
          "email":      { "type": "string", "maxLength": 255, "x-unique": true },
          "name":       { "type": "string", "maxLength": 100 },
          "bio":        { "type": "string", "nullable": true },
          "role":       { "$ref": "#/components/schemas/UserRole" },
          "plan_id":    { "type": "integer", "x-index": true,
                          "x-foreign-key": { "table": "plans", "column": "id" } },
          "balance":    { "type": "number", "x-db-type": "DECIMAL(10,2)" },
          "created_at": { "type": "string", "format": "date-time" }
        },
        "x-indexes": [
          { "columns": ["role", "created_at"], "name": "idx_users_role_created" }
        ]
      },
      "UserRole": { "type": "string", "enum": ["admin", "member", "guest"] }
    }
  }
}

Enum columns can reference a companion enum schema in the same file (like `UserRole` above). If that enum schema also declares `x-enum-values`, the column is expected to store int codes instead of strings. An `x-foreign-key` written as `{ "table": …, "column": …, "enforced": false }` marks an ERD-only logical reference — no actual DB constraint is expected.

What gets comparedEvery finding is declaration-vs-declaration, so the confidence is always `proof` — when the report says the schemas match, they match:• Tables, in both directions — a table documented in docs/db but absent from the database is `missing_in_impl`; a live table the docs don't know is `missing_in_doc`. Migration bookkeeping tables (alembic_version, schema_migrations, _prisma_migrations, …) are ignored by default; add project-specific ones via `ignore_tables`.• Columns — presence in both directions, then type. Type comparison is family-based by default (`integer` accepts INT / BIGINT / SMALLINT, …); declare `x-db-type` for an exact-match comparison, and `maxLength` to pin a varchar length.• Constraints & indexes — `x-primary-key`, `x-unique`, `x-auto-increment`, per-column `x-index`, composite `x-indexes`, and `x-foreign-key` (skipped when `enforced: false`).• Enums & nullability — `enum` value sets (int codes when `x-enum-values` is present) and the `nullable: true` convention above.

Declaring the checkThe declaration lives in `jui.config.json`: a `databases` map (`{name: {dialect}}`) plus one `checks` entry of `type: "builtin:db-schema"` per database. There are two ways to reach the real schema. The zero-dependency path is a `dump_command` — any command that prints normalized schema JSON (`{ "tables": { … } }`) to stdout. Or omit it, and the builtin SQLAlchemy dumper connects directly using the `JSONUI_CHECK_DB_URL_<DATABASE>` environment variable. Connection info never goes in the config — and only commands declared in the config are ever executed, from scripts inside the project root.

jui.config.json (excerpt)

{
  "databases": {
    "default": { "dialect": "postgresql" }
  },
  "checks": [
    {
      "name": "db-schema",
      "type": "builtin:db-schema",
      "database": "default",
      "ignore_tables": ["audit_log_archive"],
      "timeout_seconds": 60
    }
  ]
}

Schema source for the example above: either export `JSONUI_CHECK_DB_URL_DEFAULT=postgresql://…` in the environment, or add `"dump_command": ["python", "scripts/dump_schema.py"]` to the check entry.

Run it, read the reportResults land in `docs/db/.check-report.json` (named databases: `docs/db/<name>/.check-report.json`) — add them to .gitignore. `jsonui-doc generate html` (or the combined `generate html --with-checks`) then renders the Implementation contract page: every finding with its target (`table.column`), status (mismatch / missing_in_impl / missing_in_doc), expected vs actual, and a confidence badge. In CI the exit code is the merge gate: 0 = OK, 1 = mismatch, 2 = execution error.

running the check

$ jsonui-doc check --list      # preview the resolved commands, run nothing
$ jsonui-doc check db          # run only the DB checkers
$ jsonui-doc check db:default  # narrow to one database

The report records `input_hashes` of the docs it verified — if docs/db changes after the last check ran, the HTML shows a stale banner instead of pretending the verdict still holds.

What it does not checkIt verifies structure — tables, columns, types, constraints, indexes — not data. Whether a specific row's value is semantically valid ("this column is null only when the order is refunded") is full-checker territory (`type: "checker"`). And because type comparison is family-based unless you pin `x-db-type`, an exact-precision drift (DECIMAL scale, exact varchar width) is only caught where you pinned it. For the confidence model and the plugin tiers, see the Implementation contract check concept page.