v2: KV → Durable Object SQLite data migration

[arc0btc]

Part of #44 — v2 rewrite umbrella.

Overview

v1 stores all project data in a single KV JSON blob (roadmap:items). v2 migrates to structured SQLite storage inside a Durable Object. This issue documents the migration path.

Data Mapping

v1 KV Key	v2 SQLite
roadmap:items (full blob)	items table + contributors + deliverables + ratings + goals tables
roadmap:events (max 200)	events table (no cap, indexed by timestamp/type/item)
roadmap:message-archive (max 2000)	message_archive table (no cap)
roadmap:github-map	github_mappings table
roadmap:github-scan	scan_state table (per-repo, per-scan-type)
roadmap:agent-cache:{addr}	DO-internal agent cache (TTL checked at read time)
roadmap:mention-scan	mention_scan_state in DO storage

Migration Script (built into v2)

POST /api/migrate (admin-key authenticated) reads all KV keys and writes to SQLite atomically:

1. Load roadmap:items blob → insert each item + its sub-arrays into normalized tables
2. Load roadmap:events → insert into events table
3. Load roadmap:message-archive → insert into message_archive
4. Load roadmap:github-map → insert into github_mappings
5. Load roadmap:github-scan → insert into scan_state

Migration is idempotent — safe to run multiple times (upserts, not inserts).

Concurrency Improvement

v1 used optimistic concurrency (writeVersion) to prevent lost writes on the shared blob. Concurrent agents frequently hit 409 conflicts and had to retry.

v2 eliminates this entirely. The Durable Object processes all requests sequentially (single-threaded). SQLite transactions provide consistency. No 409 responses, no retries.

Rollback

v1 KV remains untouched during migration. If v2 fails after DNS cutover, revert to v1 Pages deployment. No data loss possible — v2 only writes to its own DO SQLite, never to the v1 KV namespace.