Skip to content

Pipeline Plan 17

Jump to bottom
Seth Ford edited this page Feb 12, 2026 · 4 revisions

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

Implementation Plan: SQLite Persistence Layer for Team State

Summary

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.

Files to Modify

New Files

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

Modified Files

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

Implementation Steps

Step 1: Schema (scripts/lib/migrations/001_initial_schema.sql)

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)

Step 2: Bash library (scripts/lib/db.sh)

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.

Step 3: CLI subcommand (scripts/sw-db.sh)

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.

Step 4: TypeScript module (dashboard/db.ts)

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.

Step 5–8: Wire dual-writes in bash scripts

  • 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

Step 9: Dashboard integration

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.

Steps 10–12: CLI routing, tests, docs

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

Task Checklist

  • 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

Testing Approach

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.

Definition of Done

  • 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

Architecture Decisions

  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

Clone this wiki locally