DuckDB recovery

An agent’s entire state is one DuckDB file. When DuckDB hits a fatal internal error it invalidates the instance: reads and writes fail for the rest of the process’s life, and a restart replays the same failure from disk. The agent looks alive (HTTP answers) while the coordinator, auth config reads, and every workflow error out.

Symptoms

FATAL Error: Failed to create checkpoint because of error: Attempted to dereference shared_ptr that is NULL! — the checkpoint that invalidated the database.
Then, repeated until restart: database has been invalidated because of a previous fatal error. The database must be restarted prior to being used again.
A service that crash-loops on start with the same checkpoint error (the startup readopt write triggers it again).
Stale-looking API payloads: empty groups/checks and dashboard unknown, because reads fall back to defaults while the database is down.

Never trust a health read during this window. Since the auth gate also reads its config from the database, do not delete tokens or re-login while the agent is wedged — authentication is failing closed and recovers with the database.

Do not use a host `duckdb` CLI

The agent bundles a pinned DuckDB build (a specific duckdb-rs git rev, compiled in). A distro package or an artifacts.duckdb.org CLI is a different build: checkpointing with it rewrites the file into a serialization the agent refuses to open:

Serialization Error: Failed to deserialize: field id mismatch, expected: 200, got: 201

That turns a recoverable WAL into a restore-from-backup. Always repair with a binary built from the agent’s own pin — steelframe db does exactly that.

Procedure

The agent must not be running: DuckDB takes an exclusive file lock. Copying the file while the process still has it open can save a torn pair.

Stop the agent and confirm the unit is inactive before copying. Dogfood is a user unit. A host installed with steelframe agent install (including makolate) is a system unit — stopping only the user unit leaves that process running.

# user unit
systemctl --user stop steelframe.service
systemctl --user is-active steelframe.service   # inactive

# system unit
sudo systemctl stop steelframe.service
systemctl is-active steelframe.service          # inactive

Back up the database and its WAL:

cd <data-dir>            # e.g. ~/opt/steelframe/data
backup="recovery-$(date +%Y%m%d-%H%M%S)"
mkdir -p "$backup"
cp --preserve=all steelframe.duckdb steelframe.duckdb.wal "$backup"/

Run the repair with the agent binary:
Terminal window
```
steelframe db repair steelframe.duckdb
```
- checkpoint ok — the WAL merged into the file; it is usable as-is. Skip to step 5.
- rebuilt <db> -> <db>.repaired — the checkpoint failed, so every readable table was exported to <db>.dump-<timestamp> (SQL + CSV) and imported into a fresh file. Row counts are compared table by table and mismatches are printed. When the --replace flag is not set the original is left untouched. A later repair removes a previous <db>.repaired (and its .wal) before importing again.
Swap the rebuilt file in once the counts match:
Terminal window
```
steelframe db repair steelframe.duckdb --replace
```
The original and its write-ahead log move to steelframe.duckdb.invalid-<timestamp> and steelframe.duckdb.invalid-<timestamp>.wal. The rebuilt file takes the original path. The command refuses to replace while any table count differs, and it restores the original pair if the log cannot be moved.

Do not mv the rebuilt file onto the original path yourself. A leftover steelframe.duckdb.wal replays onto the rebuilt file the next time it is opened.

Start and verify with the same unit you stopped:

systemctl --user start steelframe.service
journalctl --user -u steelframe.service -n 50 --no-pager

sudo systemctl start steelframe.service
journalctl -u steelframe.service -n 50 --no-pager

A clean start logs migrations complete, quack bound, starting coordinator, then workflow ticks — with no invalidated lines.

Keep the backups (recovery-*, *.invalid-*, and any *.dump-*) until the agent has run cleanly for a few days. They are the only way to recover rows if a later problem turns out to be data corruption rather than a checkpoint bug.

Individual commands

steelframe db checkpoint <db>          # merge the WAL only
steelframe db export     <db> <dir>    # SQL + one CSV per table
steelframe db import     <dir> <new>   # import a dump into a fresh file

After recovery

If the database was down for a while, any operator session token that expired during the outage is invalid again once the gate comes back — there is no refresh during one. Log in again (the login route is always open), or use the STEELFRAME_TUI_TOKEN automation token.
Investigate the trigger before assuming it was transient. The previous known cause was an ART index inconsistency in workflow_runs, fixed by dropping the secondary indexes (migration 004_drop_workflow_run_indexes). The 2026-09-25 failure was a checkpoint bug in the pinned DuckDB build.
A wedged database is a good moment to make sure the agent’s backup story (if any) actually restores.