Database maintenance status

Included from Basic tier

The payloads of the /api/admin/database surface (admin-token gated):

• GET /api/admin/database — the maintenance status snapshot (the Status payload below; opschain admin database status).
• POST /api/admin/database/analyze and POST /api/admin/database/vacuum — synchronous on-demand runs, answering the run's Summary (below). The optional body {"tables": ["..."]} narrows the target set; a name that is not a plain lower-case SQL identifier is refused 400 before any SQL is built (invalid table name).

The v2 OpenAPI document declares this resource's schema; the resource-field coverage gate holds this page to it.

Status payload (GET /api/admin/database)

Field	Type	Meaning
backend	string	postgres or sqlite.
enabled	bool	Whether the scheduled maintenance loop is on (config.yaml database.maintenance block). Gates the scheduled lanes only — this status read and the on-demand verbs stay available either way.
analyze_interval_minutes	int	The effective analyze cadence (default 1440 — 24 h).
vacuum_window	string	The weekly vacuum lane's clock window, HH:MM-HH:MM (default 02:00-04:00; an end at or before the start crosses midnight). Evaluated in the server-local clock.
vacuum_day	string	The vacuum lane's weekday name (default sunday).
tables	array	PostgreSQL only (absent on SQLite): one pg_stat_user_tables snapshot per churn table, fields below.
last_runs	array	The durable last-run row per task (database_maintenance_runs, upserted per run so the cadence decision survives restarts and failovers). Always present ([] when no run has happened). Fields below.
database_size_bytes	int	SQLite only: page_count × page_size. Absent on PostgreSQL.
free_bytes	int	SQLite only: freelist_count × page_size. Absent on PostgreSQL.

tables entries (PostgreSQL)

Field	Type	Meaning
table	string	The table name, from the effective target set: the configured override, else the built-in churn set (changes, events, go_port_shims, log_lines, log_segments, properties_versions, queue_ready, scheduler_done, settings_versions, step_transitions, steps, trigger_firings, workflow_runs, workflow_steps).
total_bytes	int	pg_total_relation_size.
live_tuples / dead_tuples	int	n_live_tup / n_dead_tup.
last_analyze / last_autoanalyze / last_vacuum / last_autovacuum	timestamp	The corresponding pg_stat_user_tables timestamps. Absent when PostgreSQL has none recorded.

last_runs entries

Field	Type	Meaning
task	string	analyze or vacuum (the state-table key — one durable row per task).
started_at / finished_at	timestamp	The run's UTC bounds.
duration_ms	int	Wall time.
tables	int	How many statements the run executed (a count here, unlike the Summary's array).
failures	int	Statements that errored.
detail	object	The run's full Summary JSON, persisted verbatim. Absent when empty.

Run summary payload (POST …/analyze, POST …/vacuum)

Field	Type	Meaning
task	string	analyze or vacuum.
trigger	string	What started the run — vocabulary below. On-demand verbs always record on_demand.
started_at / finished_at	timestamp	UTC bounds.
duration_ms	int	Wall time.
tables	array	Per-statement results, fields below.
failures	int	Results whose error is non-empty. Statement failures are recorded per table, never aborting the pass; the HTTP error path covers only refusals before any statement ran.

tables (per-statement) entries

Field	Type	Meaning
table	string	The target table — or the literal pragma optimize on SQLite, where both tasks run a single PRAGMA optimize statement.
duration_ms	int	Statement wall time.
dead_tuples_before / dead_tuples_after	int	Vacuum runs on PostgreSQL only: n_dead_tup sampled around the pass. Absent otherwise.
error	string	The statement's error. Absent on success.

Vocabularies (verbatim)

• Tasks: analyze, vacuum.
• Triggers: scheduled, on_demand, post_retention (the best-effort analyze after a retention sweep that deleted rows).
• Events: every completed run emits api:database:maintenance, or warn:database:maintenance when any statement failed — customer-visible, never log-only. PostgreSQL stores only: the SQLite (trial) store has no server-event lane, so there the run is visible as the operator notice and this status's last_runs instead (opschain docs database-maintenance).

What changes the lanes

Only the cron leader runs the scheduled lanes; followers tick idle. Dueness is read from the durable last-run rows, so a freshly booted or failed-over leader catches up on a missed cadence at its first tick. On PostgreSQL the statements are per-table ANALYZE and VACUUM (ANALYZE) (plain VACUUM is reserved for the backup lane); on SQLite both are PRAGMA optimize.

Related pages

• opschain docs database-maintenance — the policy, config keys and scheduling narrative.