8.5 Database care

Included from Basic tier

After this you can: read the database status, run the upkeep lanes on demand, keep the migration ledger clean (online and offline), run the one-time log backfill on an adopted estate, and know what housekeeping the server does for you.

Before you start — tick each one:

• Lesson 8.1 — Backup, restore and prove it (opschain learn 8.1)

In the GUI: the Database page in the Operations area is the store console: the per-table statistics with autovacuum and analyze recency and the on-demand ANALYZE and VACUUM runs with their per-table result; the migration ledger with its integrity verdict and Apply pending (behind a type-to-confirm arm); the System quiesce panel; the log-migration control plane (start, pause, resume, and the purge behind its warning); and the encryption-at-rest census with re-encryption. Only database import and restore execution stay with the CLI.

The database is the estate: runs, events, settings, identities — all of it. Lesson 8.1 made it recoverable; this lesson keeps it healthy and trustworthy day to day. The component reference is opschain docs database-maintenance.

1. The upkeep the server runs on its own

On PostgreSQL the server schedules statistics and bloat maintenance itself, on the elected leader only, with durable cadence state — a restart or failover never re-runs or skips a window:

• Daily statistics (ANALYZE), by default every 24 hours (database.maintenance.analyze_interval_minutes), and additionally after any retention sweep that deleted rows.
• Weekly vacuum (VACUUM (ANALYZE)), inside a configured window (vacuum_day, vacuum_window — default Sunday 02:00–04:00 server time), at most once per window.

Every completed run lands in the audit trail as api:database:maintenance (or warn:database:maintenance when any statement failed), with the task, the trigger and per-table figures. If your DBA team owns this work, hand it to them cleanly:

database:
  maintenance:
    enabled: false

The scheduled lanes stop; the status read says so; the on-demand verbs keep working. On the embedded SQLite backend the scheduled lane is a periodic PRAGMA optimize instead.

2. Status and the on-demand verbs

opschain admin database status

backend: postgres  scheduled maintenance: enabled  analyze every 30m  vacuum sunday 02:00-04:00
TABLE   SIZE    LIVE   DEAD  LAST ANALYZE          LAST VACUUM
steps   42.3MB  81204  1532  2026-10-14T02:30:00Z  2026-10-12T02:10:11Z (auto)
last analyze: finished 2026-10-14T02:30:00Z in 412ms over 9 statement(s), 0 failure(s)

status prints the backend, the effective schedule and — on PostgreSQL — a per-table block (size, live and dead tuples, last analyze and vacuum times) covering the high-churn tables. The on-demand verbs run synchronously and print a per-table summary; an invalid table name is refused by name before any SQL runs:

opschain admin database analyze --table steps --table runs
opschain admin database vacuum  --table steps

VACUUM (ANALYZE) takes table-level locks that can briefly contend with heavy writers — run the on-demand vacuum off-peak (the scheduled lane already is). ANALYZE is safe at any time. See opschain docs cli/admin-database.

3. Migration integrity: the ledger

Every applied migration is recorded in a ledger by name and content digest, and every boot verifies the binary's migration set against it. A mismatch — a renamed or altered migration, a database that took a different build's history — fails the boot loudly, naming each finding and the remedy, instead of letting the damage surface as request-time errors hours later. On a running server:

opschain admin database migrations status

migration ledger OK: the postgres database's migration history matches this server's migrations

opschain admin database migrations list

backend: postgres
VERSION  NAME                              STATE    APPLIED AT            DETAIL
…
117      add_notification_mandates         applied  2026-10-02T01:14:09Z  -
118      add_transport_grant_caps          applied  2026-10-14T01:02:51Z  -
119      add_agent_release_stage_names     pending  -                     -

migrations apply stages pending migrations ahead of a boot:

opschain admin database migrations apply

applied 119 (add_agent_release_stage_names)

A mismatched ledger exits non-zero, lists each finding with its remedy, and blocks further applies until it is repaired (restore the true row, or restore the database — lesson 8.1). Migrations are additive-only, so an older binary runs correctly against a newer schema — rollback mode, lesson 8.6.

4. The offline lane: when the server will not boot

The verbs above ride the server's API — unavailable in exactly the scenario where migration introspection matters most: the server refusing to boot on a migration problem. opschain server migrate runs the same machinery directly against the database named by the server host's own config.yaml — no API, no credential surface, no running server:

opschain server migrate status   # the boot verifier's verdict, server down
opschain server migrate list     # the per-version view
opschain server migrate up       # apply exactly what the next boot would

migration ledger OK: the postgres database's migration history matches this server's migrations

Diagnose with status, fix the cause (deploy the build matching the database's history, or restore from backup), apply with up, then start the server. The same verbs bootstrap an empty database identically to a first boot.

5. The one-time log backfill on a drop-in estate

A drop-in estate — a database adopted from an earlier OpsChain generation — can hold years of step, workflow and background-task output in its log_lines table. The server reads it from there transparently, but retention, archiving and the sealed-segment log reads work on log segments and the blob store, so an adopted estate runs the backfill once:

opschain admin migrate-logs start
opschain admin migrate-logs status

log_lines (estimate): 18422931
migrate: running — 1240 owner(s) verified, 2881402 line(s), 611MB written, 0 failed, 0 group(s) skipped
purge:   idle — 0 owner(s) purged, 0 line(s) deleted

The worker runs inside the server (one instance, on the leader, resuming from a durable cursor across restarts and failovers); each verb only flips server-side state and returns, so the terminal that started it can go away — pause holds the active phase and resume picks up from its cursor. Reads dual-serve throughout — a migrated owner from segments, an unmigrated one from log_lines — so there is no dark window. The migration copies and verifies: per owner, the line count and a content digest are compared source against segments and the verdict recorded as evidence. purge is the separate, deliberate deletion — it refuses while nothing is verified and deletes only verified owners — so run it once the blob store is confirmed accurate and captured by your backups (lesson 8.1). Every verb in full: opschain docs cli/admin-migrate-logs.

6. Housekeeping you inherit

• Retention — expired operational data is swept to a verified filesystem archive before deletion; opschain docs retention covers the data classes, intervals and the archive layout.
• Events — operational events (including every warning this lesson names) land in the audit history; opschain docs events catalogues the types. The boot log is the server's stderr and is yours to collect with your process supervisor.
• After any maintenance change: opschain admin database status shows the schedule you intended, and the audit trail shows the runs landing.

Check yourself

• What does migrations status print when the ledger is clean, and which lane answers when the server refuses to boot?
• When do you run migrate-logs, and what does purge refuse to do?

What you learned

• The leader runs daily ANALYZE and a weekly VACUUM window on its own; the on-demand verbs and status are yours.
• The migration ledger is verified on every boot; migrations status|list|apply read and stage it online, server migrate offline.
• An adopted estate backfills log_lines once, verified per owner, with purge as a separate deliberate step.
• Retention and the event catalogue are the housekeeping you inherit.

Next: lesson 8.6 — Upgrade and roll (opschain learn 8.6), or back to your track (opschain learn).