The plan file is in a sensitive directory. Let me present the plan directly instead.

---

Add a SQLite database at ~/.shipwright/shipwright.db to replace JSON file-based state management. The dashboard server (Bun/TypeScript) uses bun:sqlite as the primary interface, while bash scripts use the sqlite3 CLI. Dual-write to both SQLite and existing JSONL/JSON files ensures backward compatibility.

---

File	Purpose
scripts/lib/migrations/001_initial_schema.sql	Initial table definitions (8 tables, 15 indexes)
scripts/lib/db.sh	Bash SQLite helper library (query, insert, migrate)
scripts/sw-db.sh	CLI subcommand: shipwright db migrate|status|import|export
scripts/sw-db-test.sh	Test suite for SQLite persistence layer
dashboard/db.ts	TypeScript SQLite module using bun:sqlite

File	Change
scripts/lib/helpers.sh	Add SQLite dual-write in emit_event()
scripts/sw-cost.sh	Add SQLite insert in cost_record()
scripts/sw-daemon.sh	Write pipeline_run lifecycle to SQLite
scripts/sw-memory.sh	Write failures/decisions to SQLite
dashboard/server.ts	Replace file-based reads with SQL queries, with fallback
scripts/sw	Add db subcommand routing
package.json	Add sw-db-test.sh to test chain

---

8 tables with proper indexes:

• schema_version — migration tracking (version PK, applied_at, description)
• events — mirrors events.jsonl (type, ts_epoch, issue, stage, result, duration_s, cost_usd, model, extra TEXT for JSON overflow). Indexes on type, ts_epoch, issue, compound (type+ts_epoch)
• pipeline_runs — denormalized runs (issue, branch, template, status, started/completed epochs, duration_s, pr_url, worktree_path). Indexes on status, issue, started_epoch
• pipeline_stages — stage completions (run_id FK, stage, status, duration_s, cost_usd)
• sessions — developer/agent sessions (id TEXT PK, machine, heartbeat, daemon state, active_jobs JSON)
• cost_entries — mirrors costs.json (tokens, model, stage, issue, cost_usd). Indexes on ts_epoch, model, issue
• metrics — pre-computed snapshots (metric_name, value, period, period_start)
• memory_entries — failures/decisions/patterns (repo_hash, entry_type, pattern, root_cause, frequency)

Double-source-guarded library providing: sw_db_path(), sw_db_available(), sw_db_migrate(), sw_db_ensure(), sw_db_query(), sw_db_insert_event(), sw_db_insert_cost(), sw_db_insert_pipeline_run(), sw_db_update_pipeline_run(). WAL mode + 5s busy timeout. All Bash 3.2 compatible.

shipwright db migrate|status|import|export. The import command reads events.jsonl, costs.json, developer-registry.json, and memory files into SQLite for existing installations.

ShipwrightDB class using bun:sqlite with: migration runner, event CRUD, pipeline queries, SQL-powered DORA metrics (calculateDoraGrades()), cost breakdowns/trends, paginated activity feed, stage performance, session management, memory queries.

• helpers.sh emit_event() → SQLite write gated behind SW_SQLITE_ENABLED + command -v sqlite3, failures silently ignored
• sw-cost.sh cost_record() → SQLite insert inside flock section
• sw-daemon.sh → pipeline lifecycle (claim_job, update_stage, complete_job)
• sw-memory.sh → failure/decision capture

Replace 14+ file-scan functions in server.ts with SQL queries. Keep fallback to file-based reads when DB is missing. Key replacements: readEvents(), calculateDoraGrades(), getMetricsHistory(), getCostInfo(), getActivityFeed(), cost/stage endpoints.

Route db subcommand in scripts/sw. Comprehensive test suite. Update package.json and CLAUDE.md.

---

• Task 1: Create scripts/lib/migrations/001_initial_schema.sql — 8 tables, 15 indexes, schema_version
• Task 2: Create scripts/lib/db.sh — bash SQLite library (Bash 3.2 compatible, WAL mode)
• Task 3: Create scripts/sw-db.sh — CLI: shipwright db migrate|status|import|export
• Task 4: Create dashboard/db.ts — ShipwrightDB class with bun:sqlite, all query methods
• Task 5: Modify scripts/lib/helpers.sh — dual-write emit_event() to SQLite
• Task 6: Modify scripts/sw-cost.sh — dual-write cost_record() to SQLite
• Task 7: Modify scripts/sw-daemon.sh — pipeline lifecycle writes to SQLite
• Task 8: Modify scripts/sw-memory.sh — memory entries to SQLite
• Task 9: Modify dashboard/server.ts — SQL-backed queries with file fallback
• Task 10: Modify scripts/sw — add db subcommand
• Task 11: Create scripts/sw-db-test.sh — test suite (schema, CRUD, DORA, import, concurrency, degradation)
• Task 12: Update package.json and CLAUDE.md

---

Unit tests (sw-db-test.sh): Isolated temp $HOME, schema migration, CRUD operations, pipeline lifecycle, cost daily sums, DORA metric calculation with seeded data, JSON import, concurrent write safety (5 background writers), graceful degradation without sqlite3.

Integration: Dashboard /api/status from SQLite, dual-write verification (JSONL + SQLite), fallback when DB deleted.

Regression: All 22 existing test suites must still pass.

---

• SQLite at ~/.shipwright/shipwright.db created on first use
• 8 tables with indexes, migration system tracking schema_version
• Bash dual-write to JSONL + SQLite (JSONL authoritative, SQLite best-effort)
• Dashboard reads from SQLite with file-based fallback
• shipwright db import migrates existing JSON state
• sw-db-test.sh all green + 22 existing suites pass
• WAL mode + busy timeout for concurrent safety
• Bash 3.2 compatible, no regressions

---

1. Dual-write, not replace — zero-risk migration; system works identically if SQLite has issues
2. Dashboard is primary SQLite consumer — TypeScript/Bun gets the biggest wins (DORA, costs, pagination)
3. WAL mode — concurrent readers + single writer
4. Best-effort from bash — || true guards, gated behind command -v sqlite3
5. Migration files — numbered .sql files for trackable schema evolution
6. extra TEXT columns — JSON overflow avoids schema churn for uncommon fields
7. Separate runtimes — bash uses sqlite3 CLI, dashboard uses bun:sqlite