SQLite canonical store adapter

[devkade]

Summary

Implement the SQLite-backed canonical store adapter for TaskStream kernel records.

Parent: #236

Scope

Create crates/store package ilchul-store.

Implement storage for:

TaskStream
Run
RuntimeSession
Contract snapshot
EvidenceRecord
VerificationResult
CompletionDecision
EventEnvelope

Boundary

SQLite stores canonical records but does not define domain rules.

Acceptance criteria

• SQLite migrations exist.
• Store implements core storage ports.
• Transactions preserve stream/run/event consistency.
• Events are append-only.
• CompletionDecision records reference evidence and verification IDs.
• Store tests pass.

[devkade]

AI-understood issue summary

Issue #241 asks for the crates/store Rust package (ilchul-store) that persists TaskStream kernel canonical records in SQLite. The store should implement the core storage ports for TaskStream, Run, RuntimeSession, contract snapshots, EvidenceRecord, VerificationResult, CompletionDecision, and EventEnvelope, while preserving the parent #236 boundary: SQLite is persistence infrastructure, not the owner of domain rules.

Why this issue is needed

• Parent SSOT: Ilchul Agent OS — MCP TaskStream control plane MVP #236 makes SQLite/kernel-owned storage the canonical authority for TaskStream records, while filesystem artifacts remain raw logs/projections/artifact refs. Without SQLite canonical store adapter #241, SSOT: Ilchul Agent OS — MCP TaskStream control plane MVP #236 cannot mechanically prove stream/run/event/evidence/verification/completion consistency.
• The current repository’s durable workflow state is file-backed (state.json, events.jsonl, snapshot.json), not SQLite-backed canonical records. FileWorkflowStore.saveWorkflow writes state.json directly (src/adapters/file-store.ts:13-src/adapters/file-store.ts:18), and current event persistence appends workflow.snapshot JSONL records (src/application/workflow-service.ts:876-src/application/workflow-service.ts:883).
• SQLite canonical store adapter #241 is the right place to enforce database-level referential integrity for completion decisions referencing evidence/verification IDs, transactionally update related stream/run/session records, and make events append-only.

Why this issue may not be needed

• If Ilchul remains a TypeScript/Pi workflow harness only, the existing file store plus WorkflowStore interface already supports active workflow state, artifact creation, artifact reads/writes, metadata checks, and evidence recording (src/application/ports.ts:11-src/application/ports.ts:24, src/application/ports.ts:112-src/application/ports.ts:115).
• The existing RunContractView projection already exposes goal, lifecycle, artifacts, evidence, completion criteria, and completion summary without introducing SQLite (src/domain/run-contract.ts:49-src/domain/run-contract.ts:84, src/domain/run-contract.ts:244-src/domain/run-contract.ts:254).
• However, those existing pieces are reusable references, not replacements for SQLite canonical store adapter #241, because the issue explicitly requires a Rust ilchul-store crate, migrations, transactions, append-only events, and canonical kernel records.

Expected Outcome

After approval, the implementation agent should produce:

1. A new crates/store Rust crate named ilchul-store in the Rust workspace.
2. SQLite migrations for all canonical record tables and join/reference tables needed by completion decisions.
3. A SQLite adapter that implements the storage traits/ports defined by the core crate from Core services and explicit port contracts #240/Core domain invariants for TaskStream authority #239.
4. Transactional write APIs that preserve TaskStream/Run/RuntimeSession/Event consistency.
5. Append-only event storage for EventEnvelope records.
6. Database- and test-level enforcement that CompletionDecision references existing evidence and verification IDs and uses kernel authority semantics from core.
7. Store tests covering migrations, CRUD/query paths, transaction rollback, event append-only behavior, and invalid completion references.

Evidence-based implementation plan

Feasibility grounded in research

In-repo reusable code/patterns found:

• Existing store abstraction pattern: WorkflowStore is an explicit port with save/load/active/list/artifact operations (src/application/ports.ts:11-src/application/ports.ts:24). The implementation agent should mirror this explicit-port style in Rust traits rather than letting SQLite define domain behavior.
• Existing evidence vocabulary: ValidationEvidence already captures evidence kind, command, exit code, artifact refs, changed files, acceptance criteria, actor, authority, and snapshot hash (src/domain/types.ts:72-src/domain/types.ts:93). This can guide EvidenceRecord field choices, while core remains Rust-owned.
• Existing file-backed store implementation: FileWorkflowStore persists state to state.json (src/adapters/file-store.ts:13-src/adapters/file-store.ts:18) and provides artifact safety checks, including no path separators (src/adapters/file-store.ts:131-src/adapters/file-store.ts:151), root confinement (src/adapters/file-store.ts:180-src/adapters/file-store.ts:187), and symlink rejection (src/adapters/file-store.ts:273-src/adapters/file-store.ts:299). These are useful boundary lessons for artifact refs, but not a SQLite implementation.
• Existing generated-state guard: direct user writes to generated artifacts such as verify.md, handoff.json, snapshot.json, events.jsonl, state.json, and active.json are blocked (src/application/workflow-service.ts:911-src/application/workflow-service.ts:913). The SQLite adapter should preserve the same authority split: runtime/artifact writers must not mutate canonical records directly.
• Existing legacy event behavior: current durable events are workflow.snapshot JSONL entries (src/application/workflow-service.ts:876-src/application/workflow-service.ts:883). This is a contrast case; SQLite canonical store adapter #241 needs a new append-only EventEnvelope table, not only snapshot projection.
• Existing workflow artifact surface: Kapi modes currently track state.json, events.jsonl, and snapshot.json as generated artifacts (src/domain/workflows.ts:3-src/domain/workflows.ts:8). SQLite canonical store adapter #241 should make SQLite canonical and leave those files as projections/artifacts where needed.
• No active Rust crate source exists in the repo root today: searching first-party files found no root Cargo.toml or *.rs under this repository outside node_modules/references. That means SQLite canonical store adapter #241 is likely new Rust workspace work, not a small adapter added to existing Rust code.

External sources / suitable libraries found:

• rusqlite is a suitable SQLite binding for a synchronous canonical store; its docs describe it as an ergonomic SQLite wrapper and expose Connection, Statement, Transaction, Savepoint, and params support: rusqlite - Rust - https://docs.rs/rusqlite/.
• rusqlite::Transaction provides explicit transaction handling, which matches the acceptance criterion that stream/run/event consistency be transactionally preserved: Transaction in rusqlite - Rust - https://docs.rs/rusqlite/latest/rusqlite/struct.Transaction.html.
• refinery is a suitable embedded SQL migration option; its docs describe running migrations on a provided connection and migration naming (V{version}__{name}): refinery - Rust - https://docs.rs/refinery/latest/refinery/.
• sqlx also has migration support, but it adds async/database-pool complexity; if the store is intended to be a small embedded SQLite authority, rusqlite + refinery is the more conservative default: migrate in sqlx - Rust - https://docs.rs/sqlx/latest/sqlx/macro.migrate.html.
• SQLite foreign key enforcement must be enabled per connection with PRAGMA foreign_keys = ON, and deferred foreign keys can report violations at commit time: SQLite Foreign Key Support - https://www.sqlite.org/foreignkeys.html.
• SQLite triggers can be used to reject updates/deletes on append-only event tables if tests need database-level append-only enforcement, not just adapter-level checks: CREATE TRIGGER - https://www.sqlite.org/lang_createtrigger.html.

Concrete step-by-step plan for the implementation agent

1. Confirm the core trait surface before writing store code. Inspect/implement the core storage ports from Core services and explicit port contracts #240/Core domain invariants for TaskStream authority #239 first. The store crate should implement those traits; it should not invent domain rules. Use the existing TS WorkflowStore port style as a structural reference (src/application/ports.ts:11-src/application/ports.ts:24).
2. Create crates/store / ilchul-store. Add Cargo.toml for the store crate and wire it into the Rust workspace. Use rusqlite for SQLite access and refinery or explicit embedded SQL migrations for migrations. Keep the public API domain-oriented, e.g. SqliteStore::open(path), migrate(), and trait impls.
3. Add migration files. Define tables for task_streams, contract_snapshots, runs, runtime_sessions, evidence_records, verification_results, completion_decisions, completion_decision_evidence, completion_decision_verifications (or equivalent), and event_envelopes. Include schema_migrations if using refinery.
4. Enable SQLite integrity on every connection. Execute PRAGMA foreign_keys = ON immediately after opening each connection because SQLite documents that FK enforcement is runtime-enabled per connection (SQLite Foreign Key Support - https://www.sqlite.org/foreignkeys.html).
5. Store domain fields as typed columns plus bounded JSON where appropriate. Prefer typed IDs/status/timestamps/foreign keys for queryable invariants; use JSON text for versioned payload details that belong to the domain model. Use EvidenceRecord fields inspired by the existing ValidationEvidence shape (src/domain/types.ts:72-src/domain/types.ts:93), but do not copy TypeScript as the canonical schema.
6. Make event storage append-only. Provide only append/query methods for event_envelopes. Add tests that attempts to update/delete events fail. If enforcing in SQL, use BEFORE UPDATE/BEFORE DELETE triggers that raise an error, backed by SQLite trigger support (CREATE TRIGGER - https://www.sqlite.org/lang_createtrigger.html).
7. Make consistency writes transactional. Use rusqlite::Transaction for operations that create or update multiple related records: stream + first run + event, runtime session start + event, evidence record + event, verification result + event, completion decision + join rows + completion.decided event. Roll back the whole transaction on any failure.
8. Enforce completion references. Make completion_decisions require decided_by = 'ilchul' either in core validation or DB check, and enforce non-empty evidence/verification references via the adapter method plus foreign-key-backed join tables. Tests should cover missing evidence ID, missing verification ID, and wrong decided_by.
9. Separate SQLite authority from filesystem artifacts. Store artifact references/digests/summaries, not raw stdout/stderr/log bodies. Preserve the artifact-boundary lesson from FileWorkflowStore root/symlink checks (src/adapters/file-store.ts:180-src/adapters/file-store.ts:187, src/adapters/file-store.ts:273-src/adapters/file-store.ts:299) for the later artifact adapter, but keep SQLite canonical store adapter #241 focused on canonical records.
10. Add targeted tests in the store crate. Use in-memory SQLite for fast unit tests and temp-file DBs for migration/open/reopen tests. Required test cases: migrations run; create/load stream/run/session; append/query events; update/delete events fail; transaction rollback leaves no partial stream/run/event; completion decision requires existing evidence and verification IDs; foreign keys are active; store implements all core ports.
11. Update README/docs if crate behavior or validation commands change. The repository instruction requires README alignment when behavior, commands, validation gates, artifacts, hooks, CLI usage, tools, or workflow semantics change.

Risks / assumptions surfaced by research

• Sequencing risk: SQLite canonical store adapter #241 depends on Rust workspace scaffold + DDD core skeleton #238/Core domain invariants for TaskStream authority #239/Core services and explicit port contracts #240 for the Rust workspace, domain types, and storage ports. If those are not merged first, the implementation agent should either land the minimal store-adjacent scaffold or explicitly keep the PR scoped to the available core API.
• SQLite integrity risk: foreign keys are not enough unless enabled per connection. Tests must prove PRAGMA foreign_keys = ON is active.
• Append-only risk: adapter-only “do not update/delete” conventions are easy to bypass in tests/tools; database triggers or absent public mutators plus negative tests are recommended.
• Boundary risk: avoid moving completion rules into SQL. SQLite can enforce references and simple checks, but core should decide domain validity and completion semantics.
• Dependency assumption: rusqlite + refinery is the conservative plan. If maintainers prefer async store access or compile-time SQL checking, they can direct the implementation agent toward sqlx before /approve.

Assumptions / human decisions to resolve asynchronously before /approve

• Assumption: SQLite canonical store adapter #241 may add rusqlite and refinery as Rust dependencies.
• Assumption: the store crate may include SQL triggers to enforce append-only events.
• Assumption: SQLite canonical store adapter #241 should wait for or include the minimal Rust core port definitions required by Core services and explicit port contracts #240; it should not duplicate a second store trait surface.
• Optional decision before /approve: choose rusqlite + refinery versus sqlx migrations if maintainers want async DB access from the start.

Approval reminder

Implementation starts only after a human comment containing /approve. This comment is a planning handoff only; the implementation agent will use the issue body and this discussion as its self-contained starting context.

Agent Signature

[devkade]

/approve

[devkade]

Completed by the merged feature PR for this issue. Closing manually because the PR body did not include a GitHub closing keyword before Dani was updated to add Closes #<issue>.