v0.4.1 — Read-only SQL steps on defined database connections; nightly backups

Upgrading from 0.4.0: back up first — the database (pg_dump, checked with pg_restore --list) and OpenBao (make backup-openbao) — then rebuild and start: docker compose build, then docker compose up -d. If up -d stops with dependency failed to start: container alexclaw-openbao-init-1 exited (0) — 0.4.1 changes the openbao-init image while OpenBao is running — AlexClaw is left created, not started: once migrate has exited 0 (docker compose ps -a), start it with docker compose up -d --no-deps alexclaw-prod. The migrate job adds the db_connections table; nothing else changes, and nothing has to be set. The SQL demo is opt-in and never starts with a plain docker compose up -d.

What a database connection and a sql_query step guarantee, and their known limitations, are in SECURITY.md, "Database Connections (SQL Read)" and "Known Limitations and Design Decisions".

Changes

Database connections and the sql_query step

• A Connections page defines PostgreSQL servers: name, host, port, database, user and a TLS mode chosen explicitly (disable, require, verify_full). Creating, changing, testing and removing one needs the editing elevation and is audited. A connection a step uses cannot be removed. The Services page shows each connection's state.
• A connection's password is stored in OpenBao, bound to the server it was entered for: a connection whose host, port, database, user or TLS mode changes — by a save, a restore or any other write — cannot use it until it is entered again. Every new password is a secret of its own; the one it replaces is deleted once the save has committed. A restore naming another server for a connection is refused.
• A connection may not point at AlexClaw itself or its own services (its database, OpenBao, the web automator).
• sql_query runs one fixed, parameterised read (SELECT, WITH, VALUES, TABLE) on a connection and returns {columns, rows, row_count}; zero rows take the empty route. Parameters are bound, never interpolated: JSON literals or values from the step's input.
• Saving a step checks the query on the database (PREPARE and EXPLAIN): its errors, its parameter and column types, a plan that would write or lock rows, a statement with no columns — all refused at save.
• Every run is read-only in its session, and the session is reset afterwards: no advisory lock or setting outlives the query. The deadline (timeout_ms, required, at most five minutes) is enforced by the server. A result over 5 MB is an error, never a cut, and the query runs in a process whose memory is bounded. Errors carry no row data, and every run is audited without values.
• Known limitation: a single json or jsonb value holding a dense array near the cap — for example jsonb_agg over millions of small values, about 4.5 MB of JSON in one row — can be refused as result_too_large although it is under 5 MB, because its decoded form exceeds the query's memory bound. The refusal is stated, never a cut result. Aggregate in smaller pieces, or return the rows instead.
• sql_query runs only as a saved workflow step: no skill, no reasoning loop and no other caller can run it with a configuration of its own, and no step can name it as its fallback.
• SQL demo: a generated company database under the demo compose profile, a read-only role, and two workflows (a weekly sales brief, overdue invoices), started with docker-compose.demo.yml on top of docker-compose.yml — see SQL demo. Production's AlexClaw never joins the demo network otherwise.

Backups

• A nightly backup of the database and OpenBao together (scripts/backup-scheduled.sh, run by a LaunchAgent at 03:30 or at the next wake): pg_dump checked with pg_restore --list, an OpenBao snapshot with checksums, owner-only files in ~/backups, the newest 14 of each kept. Manual backups are never deleted. See Backups.
• scripts/drill-restore-openbao.sh restores a snapshot into a throwaway OpenBao, with no network, and reads back every catalogued secret as name and length only.

Builds

• Every build context has a .dockerignore: the OpenBao unseal key, .env, backups, snapshots, the test stack's credentials and crash dumps never reach an image or the build cache.
• openbao-init's image pins openssl 3.5.9-r0.