sqlite-durable-agent-state

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]

1. 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.
2. Choose journal mode: WAL for a single writer with concurrent readers (the common daemon shape); DELETE/TRUNCATE only for a genuinely single-connection tool.
3. 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.