Build: The Store — runs, jobs, insert and get

Maps to: Phase 2 (control-store). Kind: Build.

Objective

Stand up control-store, the crate that gives a run somewhere durable to live. In this chapter you write the schema — the runs and jobs tables plus their indexes — and the four methods that get data in and out without any concurrency yet: Store::connect, Store::in_memory, insert_run, get_run, and insert_jobs. One test, insert_then_get_run, proves a Run survives a round trip through SQLite unchanged. The atomic claim and transactional recording — the reason this crate exists — are the next build; here we lay the table they act on.

Everything here is the concept-sqlx toy with the domain swapped in: notes becomes runs (with jobs alongside), NoteStore becomes Store, and the connect/insert/get shape does not move. If the toy compiled and ran for you, this chapter is mostly translation — the only genuinely new thing is a second table and a couple of JSON-encoded columns.

Scaffold

Create (new crate — add crates/control-store to the workspace members):

• crates/control-store/Cargo.toml
  • [dependencies]: control-core = { path = "../control-core" } (the Run/Job types and ControlError live there), plus sqlx, serde_json, chrono, uuid — all { workspace = true }.
    • sqlx (features ["runtime-tokio", "sqlite", "macros", "migrate"]) — the async SQL toolkit. macros is for migrate!() (which embeds local .sql files, needing no build-time database); migrate pulls in the migrator that runs them. We do not use the query! macros, so there is no DATABASE_URL at build time — that decision was the whole first chapter.
    • serde_json — three columns (models, spec, outcome) hold JSON that serde encodes and decodes; the database sees a TEXT string, your Rust sees a Vec/struct.
    • chrono — Run::created_at is a DateTime<Utc>, stored as an RFC 3339 string.
    • uuid — RunId/JobId wrap a Uuid, stored as its string form.
  • [dev-dependencies]: tokio (features ["macros", "rt-multi-thread"] — the tests are #[tokio::test]), pretty_assertions — both { workspace = true }.
• crates/control-store/migrations/0001_init.sql — the schema (given below). migrate!() reads this directory at compile time.
• crates/control-store/src/lib.rs — the Store struct (a single pool: SqlitePool field), the five methods, a private row_to_run mapper, and the test module.

Expected result: cargo test -p control-store → 1 test passes: insert_then_get_run.

The spec (givens)

The schema — migrations/0001_init.sql

Two tables and two indexes. Every column is TEXT or INTEGER — SQLite has no native UUID, timestamp, boolean, or array type, so ids and timestamps are stringified and collections are JSON in a TEXT column. The status columns are plain TEXT holding the snake_case string your Part II enums already serialize to.

CREATE TABLE runs (
    id         TEXT    PRIMARY KEY,
    status     TEXT    NOT NULL,
    created_at TEXT    NOT NULL,
    manifest   TEXT    NOT NULL,
    models     TEXT    NOT NULL,           -- JSON array of model names
    epochs     INTEGER NOT NULL,
    job_count  INTEGER NOT NULL DEFAULT 0,
    done_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE jobs (
    id      TEXT    PRIMARY KEY,
    run_id  TEXT    NOT NULL REFERENCES runs (id),
    status  TEXT    NOT NULL,
    attempt INTEGER NOT NULL DEFAULT 0,
    spec    TEXT    NOT NULL,              -- JSON EvalJob
    outcome TEXT                           -- JSON JobOutcome, NULL until done
);

CREATE INDEX idx_runs_status ON runs (status, created_at);
CREATE INDEX idx_jobs_run ON jobs (run_id);

Four choices are load-bearing, and each pays off in the next chapter — note them now so the claim SQL reads naturally later:

• job_count / done_count on runs. The run carries its own progress counters. "How far along is this run?" is a single-row read, not a COUNT(*) over jobs. The transactional record bumps done_count; when it reaches job_count, the run is finished.
• outcome is nullable. A job with outcome IS NULL has not reported back yet. Everything else is NOT NULL; this one column is deliberately nullable because "no result yet" is a real, expected state.
• idx_runs_status ON (status, created_at). The claim query filters WHERE status = 'queued' and orders by created_at. This composite index is exactly that access pattern — the scheduler asks "oldest queued run?" on a hot loop, and the index makes it a cheap seek rather than a table scan.
• jobs.run_id REFERENCES runs (id). A job belongs to a run; the foreign key documents that, and idx_jobs_run makes "all jobs for this run" fast — which run_results will need. (SQLite does not enforce foreign keys unless a pragma is set, so treat this as documentation plus an index, not a guardrail.)

[→ Answer key](../appendix-answer-key.md#store-schema)

The runtime-query decision, restated in code

Every method here uses the runtime query API — sqlx::query("..."), values supplied with .bind(...), columns pulled out by name with try_get. No query!, no DATABASE_URL, no build-time database. The trade you accepted in the concept chapter is now concrete: a SQL typo surfaces when insert_then_get_run runs, not when the crate compiles. That is why the test exists.

connect is the toy's connect/in_memory, unchanged down to the two load-bearing options:

• SqliteConnectOptions::from_str(url)?.create_if_missing(true) — create the file on first run rather than erroring.
• SqlitePoolOptions::new().max_connections(1) — one writer (SQLite's model), and the one connection that keeps a sqlite::memory: database alive for the pool's life.
• sqlx::migrate!().run(&pool).await? — apply 0001_init.sql; idempotent, so calling in_memory() at the top of every test rebuilds the same fresh schema.

insert_run binds every column in order (RunId, RunStatus, and DateTime each stringified; models via serde_json::to_string) and .executes it. get_run is SELECT * FROM runs WHERE id = ? with .fetch_optional (a lookup by id may miss → Option<Run>), mapped back through row_to_run. insert_jobs inserts a slice of Jobs — and here is the first transaction in the codebase: wrap the inserts in pool.begin() … tx.commit() so a run's jobs land all-or-nothing rather than half a fan-out surviving a mid-loop failure.

[→ Answer key](../appendix-answer-key.md#store-crud)

Concepts exercised

• Modelling a Rust domain in SQLite's storage classes: UUIDs and timestamps as TEXT, Vec/structs as JSON-in-TEXT, u32 as INTEGER read back through i64.
• The runtime query API end to end: query + bind + execute for writes, fetch_optional + try_get for reads.
• A nullable column (outcome) encoding an expected "not yet" state.
• A composite index (status, created_at) shaped to a query you have not written yet.
• The first transaction: begin/commit around a batch insert for all-or-nothing.
• Denormalized progress counters (job_count/done_count) on the parent row.

The build loop (you drive)

Write the test first, predict its failure mode, run, implement to green.

Test — insert_then_get_run (in lib.rs, #[tokio::test])

1. Write the failing test. Build a Store::in_memory().await and a small Run fixture — a helper a_run(job_count: u32) -> Run with a fresh RunId::new(), RunStatus::Queued, Utc::now(), one model, and done_count: 0 is worth writing now, because the next chapter reuses it. insert_run(&run).await, then assert get_run(run.id).await.unwrap() == Some(run).
2. Predict: the round trip encodes created_at with to_rfc3339() and parses it back with parse_from_rfc3339. If you instead stored it via to_string() (a different textual form), would insert fail, or would get fail to parse it back? Name which side of the round trip a format mismatch surfaces on.
3. Run — it fails to compile (Store and its methods do not exist yet).
4. Implement the schema file, then connect/in_memory, insert_run, get_run, and row_to_run. What each does is specified above and pinned by the test; how you arrange the bind order and the mapper is yours. Build insert_jobs here too — the next chapter's tests need jobs to exist, and a clean compile is your only check on it until then.
5. Run green, commit.

Done when

cargo test -p control-store shows insert_then_get_run green, a Run round-trips through SQLite unchanged, and you can point to the two lines in connect that (a) create the file if missing and (b) keep the in-memory database alive. The schema is in place and the counters are sitting at zero — waiting for the claim and the record, which is the next chapter.