#5 - SQLite persistence — EventStore & migrations - charles/peon

claude-desktop commented

2026-04-16 11:28:34 +00:00

Collaborator

User story

As a backend developer, I want an SQLite-backed EventStore that persists domain events atomically and supports full replay at startup, plus denormalized projection tables for fast reads, so that application state survives restarts.

Acceptance criteria

Database schema (`migrations/001_initial.sql`)

events table: sequence INTEGER PRIMARY KEY AUTOINCREMENT, event_type TEXT NOT NULL, payload TEXT NOT NULL (JSON), stored_at TEXT NOT NULL (ISO 8601)
projection_projects table with columns matching spec §12.1
projection_threads table with columns matching spec §12.1
projection_turns table with columns matching spec §12.1
projection_messages table with columns matching spec §12.1
checkpoints table with columns matching spec §12.1
settings table: key TEXT PRIMARY KEY, value TEXT NOT NULL (JSON)
Indexes: idx_events_sequence, idx_turns_thread, idx_messages_turn

Migration runner

Uses sqlx embedded migrations (sqlx::migrate!)
Runs automatically at startup before any read/write operations
Creates the database file if it doesn't exist (path from AppConfig.general.data_dir)

EventStore (`persistence/event_store.rs`)

EventStore::new(pool: SqlitePool) constructor
append_all(events: Vec<OrchestrationEvent>) -> Result<Vec<StoredEvent>> — atomic batch insert in a transaction, returns stored events with assigned sequence numbers
replay_all() -> Result<Vec<StoredEvent>> — reads all events ordered by sequence for startup replay
replay_from(sequence: u64) -> Result<Vec<StoredEvent>> — partial replay from a given sequence
JSON serialization of OrchestrationEvent via serde for the payload column
event_type_name() helper that extracts the variant name as a string for the event_type column

Tests

Test: append single event, replay, verify sequence + payload
Test: append batch atomically, verify all sequences are contiguous
Test: replay_all on empty DB returns empty vec
Test: replay_from filters correctly
All tests use in-memory SQLite (:memory:)

Out of scope

Projection table read/write (the projector works in-memory; projection tables are for future optimization)
t3code migration import (post-v1, spec Annexe B)

References

Spec §12 (Persistence SQLite)
Spec §5.5 (OrchestrationEngine — append + replay flow)

Dependencies

Blocked by: #4 (OrchestrationEvent, StoredEvent types)
Blocks: #7
Branch off: issue-4-event-sourcing
Full graph: #21

## User story As a **backend developer**, I want an SQLite-backed EventStore that persists domain events atomically and supports full replay at startup, plus denormalized projection tables for fast reads, so that application state survives restarts. ## Acceptance criteria ### Database schema (`migrations/001_initial.sql`) - [ ] `events` table: `sequence INTEGER PRIMARY KEY AUTOINCREMENT`, `event_type TEXT NOT NULL`, `payload TEXT NOT NULL` (JSON), `stored_at TEXT NOT NULL` (ISO 8601) - [ ] `projection_projects` table with columns matching spec §12.1 - [ ] `projection_threads` table with columns matching spec §12.1 - [ ] `projection_turns` table with columns matching spec §12.1 - [ ] `projection_messages` table with columns matching spec §12.1 - [ ] `checkpoints` table with columns matching spec §12.1 - [ ] `settings` table: `key TEXT PRIMARY KEY`, `value TEXT NOT NULL` (JSON) - [ ] Indexes: `idx_events_sequence`, `idx_turns_thread`, `idx_messages_turn` ### Migration runner - [ ] Uses sqlx embedded migrations (`sqlx::migrate!`) - [ ] Runs automatically at startup before any read/write operations - [ ] Creates the database file if it doesn't exist (path from `AppConfig.general.data_dir`) ### EventStore (`persistence/event_store.rs`) - [ ] `EventStore::new(pool: SqlitePool)` constructor - [ ] `append_all(events: Vec<OrchestrationEvent>) -> Result<Vec<StoredEvent>>` — atomic batch insert in a transaction, returns stored events with assigned sequence numbers - [ ] `replay_all() -> Result<Vec<StoredEvent>>` — reads all events ordered by sequence for startup replay - [ ] `replay_from(sequence: u64) -> Result<Vec<StoredEvent>>` — partial replay from a given sequence - [ ] JSON serialization of `OrchestrationEvent` via serde for the `payload` column - [ ] `event_type_name()` helper that extracts the variant name as a string for the `event_type` column ### Tests - [ ] Test: append single event, replay, verify sequence + payload - [ ] Test: append batch atomically, verify all sequences are contiguous - [ ] Test: replay_all on empty DB returns empty vec - [ ] Test: replay_from filters correctly - [ ] All tests use in-memory SQLite (`:memory:`) ## Out of scope - Projection table read/write (the projector works in-memory; projection tables are for future optimization) - t3code migration import (post-v1, spec Annexe B) ## References - Spec §12 (Persistence SQLite) - Spec §5.5 (OrchestrationEngine — append + replay flow) ## Dependencies - **Blocked by:** #4 (OrchestrationEvent, StoredEvent types) - **Blocks:** #7 - **Branch off:** `issue-4-event-sourcing` - **Full graph:** #21

claude-desktop added this to the v0.1.0 milestone

2026-04-16 11:28:34 +00:00

claude-desktop added the

area:persistence

type:user-story

labels

2026-04-16 11:28:34 +00:00

claude-desktop referenced this issue

2026-04-16 20:22:35 +00:00

v0.1.0 dependency tree and implementation order #21

claude-desktop referenced this issue

2026-04-16 20:24:36 +00:00