SQL query interface over the artifact store (virtual tables) — CLI-first, MCP optional

[avrabe]

Design: REQ-229 + DD-068 (PR above). Expose the live store as SQLite virtual tables (rusqlite vtab) for agent-legible SQL queries incl. JOINs. Must work without MCP/server — baseline is rivet sql "<query>" CLI; serve POST /sql + MCP rivet_sql are additive. Read-only MVP first (REQ-229); writable xUpdate→mutate is a gated second slice. v0.20.0-track.

[avrabe]

Triage from the issue-fixing routine (first pass on this new issue).

The body names the design anchors (REQ-229 + DD-068 in the linked PR), the runtime target (rusqlite virtual tables — xBestIndex / xFilter / xColumn over the live artifact store), the layering (CLI baseline; serve + MCP additive), and the slicing (read-only MVP first; writable xUpdate → mutate gated as a second slice on the v0.20.0 track). That's enough scope to recognise the shape, but no ## Acceptance Criteria block matching the kill-criterion shape the routine looks for (per #532) — and "CLI-first, MCP optional" + "read-only MVP first" leaves several seams where two implementations could both satisfy the body and one of them be wrong for the second slice.

Recommend pinning these before any branch opens:

1. Table set for the MVP. Minimum probably is one virtual table per artifact type (requirements, features, design_decisions, controllers, hazards, … — schema-driven from the loaded preset) plus a links join table (source_id, link_type, target_id, source_type, target_type). Worth confirming whether the MVP exposes a single artifacts table with a type column instead — the latter is simpler but loses per-type column typing for JOINs.
2. Schema-vs-fields surfacing. Schema-declared fields ↔ SQLite columns is straightforward; ad-hoc fields: map entries are not. Two defensible postures: (a) only declared fields become columns, undeclared fields[k] are accessible via a JSON column (fields_json); (b) one fields virtual column per known key across the loaded store. Pin (a) for the MVP unless there's a query that needs (b).
3. Refresh / cache semantics. "Live store" — does each SELECT re-walk the source files (zero-cache, correct, slow on large stores), or does it serve from the loaded salsa snapshot at session start (fast, possibly stale within a long REPL session)? Salsa snapshot is the obvious match given REQ-029 — worth saying so.
4. xBestIndex shape. Whether the MVP supports indexed lookups (WHERE id = ? short-circuit) or always returns the full row set and lets SQLite filter. Cheap to start with the full-scan posture and add xBestIndex indices in a follow-up; cheaper to say so up front.

Sketch matching #532's kill-criterion shape — feel free to revise / re-scope; suggest splitting into two PRs so the read-only slice can land independently of the gated mutate path:

## Acceptance Criteria — read-only MVP (REQ-229, lands first)
- [ ] `rivet sql "<query>"` CLI subcommand runs read-only SQL against the loaded artifact store and prints results in `--format table` (default) / `--format json` / `--format csv`. Non-zero exit on parse error, on a `WHERE` referencing an unknown column, and on a query that mutates (today: any non-`SELECT` / `WITH` statement → "writable SQL not enabled; see #579 part 2").
- [ ] Virtual table set is schema-driven: at minimum a `links` table (`source_id`, `link_type`, `target_id`, `source_type`, `target_type`) and one row-per-artifact view (single `artifacts` table with `type` discriminator + per-type JSON column for undeclared `fields:`, OR per-type tables — pick one in the design call; AC pins the choice once made). Declared schema fields are first-class columns; undeclared `fields[k]` available via a documented JSON-access path.
- [ ] JOIN works across at least: `requirements ⨝ links ⨝ features` for "which requirements satisfy which features" and `requirements ⨝ links ⨝ verifications` for the V/V closure question driving #555/#554. Fixture tests in `rivet-cli/tests/sql/`.
- [ ] `rivet sql` shares the loaded store with `rivet list` — same salsa snapshot, no double-walk. Documented in the SQL command's `--help` ("queries reflect the snapshot at process start; long-running REPL holds the snapshot for its lifetime").
- [ ] `rivet validate` PASS unchanged on a project where `rivet sql` has been run; no side effects to source files.
- [ ] `cargo test -p rivet-core -p rivet-cli` green; `clippy --all-targets -- -D warnings` clean; `cargo fmt --check` clean.
- [ ] CHANGELOG entry under "Added"; commit trailers `Implements: REQ-229` + `Refs: #579, DD-068`.
- [ ] **Explicitly out of scope (file separately):** writable SQL (`UPDATE` / `INSERT` / `DELETE` → mutate path — part 2); the `POST /sql` HTTP surface; the `rivet_sql` MCP tool; `xBestIndex` indexed lookups beyond the trivial `id = ?` short-circuit.

## Acceptance Criteria — additive surfaces (read-only)
- [ ] `rivet serve` exposes `POST /sql` accepting `{ "query": "..." }`, returns `{ "columns": [...], "rows": [...] }`, same read-only enforcement as CLI. Behind the existing dashboard auth posture; no new auth surface.
- [ ] MCP `rivet_sql` tool with input `{ query: string }` returns the same shape. Tool description warns "read-only; for mutations use rivet_modify".
- [ ] Both surfaces share the CLI's snapshot semantics and the same query parser / virtual table set — one implementation, three call sites.
- [ ] Commit trailers `Refs: #579, REQ-229, FEAT-010`.
- [ ] **Explicitly out of scope:** rate limiting / per-token quotas on `POST /sql` (separate posture call); a SQL-shaped MCP resource (`mcp://.../sql/<query>` rather than tool — different ergonomics).

## Acceptance Criteria — writable second slice (gated; lands on top once part 1 ships)
- [ ] `xUpdate` on each virtual table is wired to the existing `mutate::*` API path so `UPDATE requirements SET status = 'verified' WHERE id = 'REQ-001'` calls the same code as `rivet modify REQ-001 --set-status verified`. Same validators, same write-back, same flow-style-fields safety from #573.
- [ ] Behind a CLI flag (`rivet sql --write`) and a serve / MCP feature flag, both default off. Read-only is the default posture across all three surfaces.
- [ ] Transactions: a `BEGIN; ... COMMIT` batch is atomic on the file system — either all mutations land or none do. `ROLLBACK` is supported. Fixture covers a batch where the second statement fails validation; verifies the first statement's effect is reverted on disk.
- [ ] Provenance: every row written via `xUpdate` carries the same `created-by` / `model` / `session-id` trailer treatment as `rivet modify` (REQ-104 / #104).
- [ ] Commit trailers `Implements: REQ-229` (or a separate REQ for the mutate slice) + `Refs: #579, DD-068`.
- [ ] **Explicitly out of scope:** DDL (`CREATE TABLE`, `ALTER TABLE`) — schema is owned by the preset, not by SQL; `JOIN`-driven multi-table updates; the JSON-column "fields" path being writable (declared columns only for the mutate MVP).

Process note (carried from recent triage threads on this routine): https://pulseengine.eu/blog/ is reachable in this run via curl -k (the blog index served fine — 11 posts listed) but the origin certificate is still expired, which is why WebFetch returns 503 and prior runs reported the blog as down. The expired origin cert has been the gating factor on 30+ consecutive routine runs (#350, #420, #422, #431, #436, #456, #468, #479, #482, #488, #490, #500, #508, #509, #516, #522, #523, #530, #532, #538, #543, #546, #547, #548, #549, #550, #552, #554, #555, #556, #557, #567, #573, #574, #577). Worth a cert renewal — the routine has been producing AC sketches without ever landing implementation. The blog's process-tagged posts (oracle-gated agents, spec-driven dev) inform methodology but don't override the PR conventions in AGENTS.md, so the routine could in principle proceed against AGENTS.md alone if a per-issue AC arrives.

Not implemented this run.

---

Generated by Claude Code — issue-triage agent run 2026-06-24.

---

Generated by Claude Code