checkId: check.postgres.schemas.present plugin: stellaops.doctor.database severity: fail tags: [database, schema, migrations, postgres]

PostgreSQL Schemas Present

This Doctor check confirms that every expected per-service PostgreSQL schema is actually provisioned — not just that the database server is reachable.

What It Checks

The central doctor-web container receives ONE Postgres connection string, so check.db.connection proves the server is up but says nothing about the ~30 per-service module schemas (authority, platform, scanner, concelier, policy, evidencelocker, attestor, scheduler, notify, releaseorchestrator, doctor, …). Per repo AGENTS.md §2.7 a missing schema/table is the #1 cause of QA 500s: a fresh-volume or partial-migration state passes connectivity yet leaves half the platform broken.

The check resolves the connection string from the canonical platform key ConnectionStrings:StellaOps (compose ConnectionStrings__StellaOps, with legacy Doctor:Plugins:Database:ConnectionString / ConnectionStrings:DefaultConnection fallbacks). It derives the expected schema set from the integration manifest — every integration.database.schema.<name> entry in the declared catalog — so the expectation stays in lock-step with the manifest rather than a hard-coded literal list. For each expected schema it reads the real platform migration ledger <schema>.schema_migrations (DOC-TRUTH-005), which the platform’s StartupMigrationHost writes — never the EF __EFMigrationsHistory table the platform does not use. A schema counts as provisioned iff its schema_migrations table exists AND has at least one applied migration row.

Condition	Result
No connection string configured	`skip`
All expected schemas provisioned	`pass`
One or more expected schemas missing / without an applied migration ledger	`fail` (names them; platform-wide for authority/platform)
No migration ledger anywhere (fresh / still-converging database)	`skip` (no false alarm during startup convergence)
Connect/query failure	`fail` (visible, never a silent empty)

A partially-provisioned database (some expected schemas present, others missing) is the genuine broken/absent state and fails — only a totally-empty database gets the benefit of the doubt as “still converging”.

This check is read-only: it inspects information_schema and the ledger; it never creates, drops, or mutates a schema. For cataloged tenants, tenant connection-string overrides under Doctor:TenantOverrides:<tenant>:<key> or Tenants:<tenant>:<key> are checked before global connection strings. Transient ledger-read/connect exceptions are retried with bounded ProbeRetry; returned missing-schema results are not retried. Tenant fan-out evidence includes per-tenant severity and retry attempt counts.

Why It Matters

A service whose schema was never migrated returns 500s on every request that touches its tables, while connectivity and the server-level checks stay green. This is exactly the silent half-broken state a fresh volume or a partial migration leaves behind. Surfacing each missing schema by name turns an opaque “some endpoints 500” outage into an actionable list.

Common Causes

A service was never started against this database, so its StartupMigrationHost never provisioned its schema
The database was reset (fresh volume) and only some services have converged their schema yet
A migration failed mid-run, leaving the schema partially or not created
The connection points at the wrong database/instance

How to Fix

Docker Compose

# Each service auto-migrates its own schema on startup (repo §2.7). Restart the service(s) that own a
# missing schema so their StartupMigrationHost re-runs the embedded forward-only migrations (non-destructive).
docker compose -f devops/compose/docker-compose.stella-ops.yml restart <service>

# List the schemas that already have a migration ledger:
psql "$STELLAOPS_POSTGRES_CONNECTION" -c "SELECT table_schema FROM information_schema.tables WHERE table_name = 'schema_migrations' ORDER BY table_schema"

Bare Metal / systemd

# Restart the owning service so it re-applies its embedded migrations, then re-list provisioned schemas.
systemctl restart stellaops-<service>
psql "$STELLAOPS_POSTGRES_CONNECTION" -c "SELECT table_schema FROM information_schema.tables WHERE table_name = 'schema_migrations' ORDER BY table_schema"

# Or apply pending forward-only migrations for a specific schema directly (idempotent; never drops/truncates):
stella db migrate --schema <schema>

Kubernetes / Helm

# Rollout-restart the owning deployment so its StartupMigrationHost re-runs the migrations.
kubectl rollout restart deployment/<service> -n stellaops
kubectl get pods -n stellaops -l app=<service>

Checksum drift / “requires manual execution” conditions are surfaced by the migration checks (check.db.migrations.failed / check.db.migrations.pending) and are a manual runbook — never auto-rewrite checksums.

Verification

stella doctor run --check check.postgres.schemas.present

check.db.connection — proves the PostgreSQL server is reachable (this check adds per-schema presence on top)
check.db.migrations.pending — reports the applied migration ledger and flags invalid-checksum rows
check.db.migrations.failed — flags applied migrations that cannot be integrity-verified
check.db.schema.version — schema/table consistency across provisioned schemas
check.core.integration-coverage — the meta-check this is the owning check for the integration.database.schema.* family