| name | sqlite-durable-agent-state |
| description | Design durable local SQLite state for multi-agent developer tooling that survives package upgrades, concurrent writers, and crashes: canonical env-pinned paths, WAL/busy_timeout discipline, idempotent verified migrations, and safe writer topology. Use when a daemon, CLI, or agent fleet needs a persistent local DB, when reviewing a schema/path change before it ships, or when diagnosing an already-fragmented multi-.db mess. NOT for relational schema/query design (use daemon-development for the daemon's broader lifecycle), server-based Postgres/MySQL durability, or deciding whether SQLite is the right engine at all. |
| license | Apache-2.0 |
| allowed-tools | Read,Write,Edit,Bash,Grep,Glob |
| metadata | {"category":"Data & Storage","tags":["sqlite","durable-state","wal-mode","migration-integrity","multi-writer-safety"],"provenance":{"kind":"first-party","owners":["port-daddy"]},"pairs-with":[{"skill":"port-daddy-internal-dev","reason":"Port Daddy's own 7-.db fragmentation (ADR-0090/0044) is the canonical case this skill prevents and recovers from."},{"skill":"daemon-development","reason":"The daemon process is usually the single writer that must enforce serialization and PRAGMA discipline."},{"skill":"runtime-verification-for-agents","reason":"Post-migration verification must check live schema state, not migration-history metadata."}],"io-contract":{"kind":"deliverable","consumes":["[Truncated]"],"produces":["[Truncated]","[Truncated]"]}} |
SQLite Durable Agent State
Design local SQLite state for multi-agent daemons and CLIs that survives brew upgrade, npm reinstall, concurrent writers, and crashes — and recover when it already hasn't.
Use This For
- Picking a canonical SQLite path for a daemon/CLI pair, pinned by one env var, before the first line of storage code is written.
- Choosing journal mode (WAL vs DELETE) and
busy_timeout for a daemon with a CLI, a snapshot exporter, and a web API all touching the same DB.
- Designing idempotent, atomic migrations with a real post-apply verification probe instead of trusting a migration-history "applied" row.
- Reviewing a
pd-fleet.yml, ADR, or storage design doc before it reintroduces a Cellar-path, per-worktree, or per-tool-default DB regression.
- Diagnosing an already-fragmented multi-
.db mess where CLI/snapshot/export tooling see different counts for the same entity.
Do Not Use This For
- General relational schema design, indexing strategy, or query tuning — that's data modeling, not durability engineering.
- Postgres/MySQL server-based durability, where WAL, replication, and connection pooling mean something structurally different.
- Deciding whether SQLite is the right engine versus a client-server DB — this skill assumes SQLite is already the choice for a local, mostly-single-daemon store.
Durability Design Loop
flowchart TD
A[Name one canonical DB path, env-pinned] --> B[Choose journal mode + busy_timeout]
B --> C[Design migrations: idempotent, atomic, versioned]
C --> D[Attach a post-apply verify probe per migration]
D --> E[Define writer topology: single-writer or serialized queue]
E --> F[Run scripts/db_path_audit.mjs on the plan]
F --> G{Blocker findings?}
G -->|Yes| C
G -->|No| H[Ship; add a doctor check for stray DB files]
- Name exactly one canonical path, resolved by every reader and writer through the same env var (e.g.
PORT_DADDY_DB). No per-tool fallback default — that is how CLI/snapshot/export tooling end up reading three different files.
- Choose journal mode:
WAL for a single writer with concurrent readers (the common daemon shape); DELETE/TRUNCATE only for a genuinely single-connection tool.
- If
WAL, set PRAGMA busy_timeout (2000-5000ms floor) on every connection open. WAL gives reader/writer concurrency, not writer/writer concurrency — without a timeout, contention becomes SQLITE_BUSY crash-loops, not graceful waits.
