Embedded DB consolidation — reconnaissance

Synced read-only from /home/synnet/mirrors/synvirt-product/docs/modules/db-consolidation/RECONNAISSANCE.md. Edit at the source, not here.

Embedded DB consolidation — reconnaissance

Branch: agent/db-unification/sqlite-consolidation-260523-1343 Date: 2026-05-23 Status: reconnaissance only. No code changes proposed in this phase.

Note (post-R1): this recon predates the R1 network extraction. The network-virtswitches sled tree, its VirtSwitchSpec value type, and virtswitch_pending.rs it references no longer exist — network state moved to /etc/synvirt/network.yaml (owned by synvirt-network) and policies.db now holds only usb-policy + console-policy. Kept as a historical reconnaissance record.

---

0. Headline findings (read this first)

1. There is no SQLite in this repo today. No rusqlite, no sqlx, no sea-orm, no diesel, no *.sqlite / *.sqlite3 files. The branch name and task title say “SQLite” but the actual embedded-DB layer is sled 0.34 everywhere. The reconnaissance below covers what actually exists; the “preliminary proposal” (§7) presents two valid reads of the task — consolidate sled vs. introduce SQLite — and recommends one.

2. CLAUDE.md is significantly out of sync with master. The file describes 31 crates, a deployed synvirt-network (R1) data plane, and a 9-phase Settings module backed by synvirt-time / synvirt-bmc / synvirt-certs / synvirt-firewall / synvirt-swap / synvirt-packages / synvirt-services / synvirt-resources / synvirt-update. None of those crates exist on master. Master has 22 crates; the entire Settings module + R1 refactor + the AuditStore/EncryptionKey invariants live only on paper. Recommend flagging this separately to Tony — it affects every doc- driven assumption agents make about this repo.

3. The real surface is small. Five persistent sled DBs, fourteen trees, one serializer (serde_json), zero migrations, zero schema versioning. Consolidation is feasible, but the lack of versioning means any reshuffle needs a one-shot importer.

---

1. Inventory of embedded DBs

All five are sled 0.34. Paths are hardcoded with a single SYNVIRT_DEV env / --dev CLI fallback that redirects to $HOME/.synvirt/. No external config controls these paths.

#	DB	Prod path	Dev path	Owner crate	Open site	Path origin
1	policies.db	/var/lib/synvirt/policies.db	$HOME/.synvirt/policies.db	daemon	crates/daemon/src/policy.rs:154	hardcoded in crates/daemon/src/main.rs:155-165
2	activity.db	/var/lib/synvirt/activity.db	$HOME/.synvirt/activity.db	daemon	crates/daemon/src/activity/store.rs:50	hardcoded in crates/daemon/src/main.rs:171-181
3	iso-detection.db	/var/lib/synvirt/iso-detection.db	$HOME/.synvirt/iso-detection.db (or /tmp fallback)	daemon	crates/daemon/src/iso_detection/store.rs:87	hardcoded default_path() in crates/daemon/src/iso_detection/store.rs:23-30, lazily opened via OnceLock singleton (line 36)
4	guest-tools.db	/var/lib/synvirt/guest-tools.db	(same, no dev override)	synvirt-guest-tools	crates/synvirt-guest-tools/src/state.rs:128	hardcoded literal in crates/daemon/src/main.rs:222
5	migrator/state.db	/var/lib/synvirt/migrator/state.db	(same, no dev override)	migrator	crates/migrator/src/state.rs:34	hardcoded in crates/daemon/src/main.rs:357,363,368

Two additional sled::Config::new().temporary(true) open sites exist (crates/synvirt-guest-tools/src/state.rs:135, crates/migrator/src/inventory.rs:182) — they are test fixtures, not persistent stores. Not part of the consolidation scope.

The migrator DB is shared with four “satellite” stores (InventoryStore, SourceRegistry, UploadStore) that accept the migrator’s Arc<sled::Db> and open trees against the same handle — the only place in the repo that already follows the “single-handle / pass &Db” pattern CLAUDE.md aspires to.

Tree map (14 trees)

DB	Trees	File:line
policies.db	usb-policy, console-policy, network-virtswitches	crates/daemon/src/policy.rs:162,187,208 (+ aliases at 175, 197, 221, 231, 241)
activity.db	activity	crates/daemon/src/activity/store.rs:55
iso-detection.db	iso-detection	crates/daemon/src/iso_detection/store.rs:93
guest-tools.db	guest-tools	crates/synvirt-guest-tools/src/state.rs:129
migrator/state.db	jobs, plans, checklists, watermarks, migrator-inventories, migrator-manifests, sources, uploads	crates/migrator/src/state.rs:37-46, crates/migrator/src/inventory.rs:68,71, crates/migrator/src/uploads.rs:79, crates/migrator/src/sources.rs:94

No tree-name collisions across DBs today — every tree name is unique. (One soft collision risk: network-virtswitches in policies.db shares a domain with the legacy boot_reapply.rs and the future R1 split, but the names themselves don’t overlap.)

---

2. Schema per DB

No DB uses bincode, postcard, CBOR, or msgpack. Every value is serde_json::to_vec() / from_slice() of a Rust struct. No versioning, no migrations, no schema-version key.

2.1 policies.db

• Tree usb-policy — key: VM UUID as UTF-8 bytes; value: UsbPolicyBody (crates/daemon/src/policy.rs:52-66) — mode (AllowAll | DenyAll | ClassFiltered) + class list.
• Tree console-policy — key: VM UUID as UTF-8 bytes; value: ConsolePolicy (crates/daemon/src/console/policy.rs:30-54) — preferred backend, RDP/VNC credential modes, serial-allow flag.
• Tree network-virtswitches — key: switch name as UTF-8 bytes; value: VirtSwitchSpec (defined under crates/daemon/src/api/) — name, bond mode, NIC list, primary uplink, VLAN list.

2.2 activity.db

• Tree activity — key: 24 bytes = 8-byte big-endian u64::MAX - millis_since_epoch (inverted timestamp, sorts newest-first) + 16-byte UUID v4. Construction in crates/daemon/src/activity/store.rs:61-68. Value: ActivityEvent (crates/daemon/src/activity/types.rs:134) — id, timestamp, action_type, optional vm_name, user, free-form serde_json::Value details, severity.
• Inverted-timestamp key is the only non-trivial key-shape in the whole repo. Preserve it on consolidation.

2.3 iso-detection.db

• Tree iso-detection — key: SHA-256 hex digest as 64-byte UTF-8 string (content-addressed). Value: Entry (crates/daemon/src/iso_detection/store.rs:76-83) — iso_path, sha256, size, DetectionResult, detected_at_unix. Corrupted entries are silently dropped on read (lines 106-108).

2.4 guest-tools.db

• Tree guest-tools — key: 16-byte VM UUID raw bytes; value: VmToolsState (crates/synvirt-guest-tools/src/state.rs:24-59) — agent-connected flag, agent version, OS metadata, hostname, last hello / last seen / disconnect reason, five memory metrics.

2.5 migrator/state.db

Tree	Key	Value (struct)
jobs	16-byte UUID bytes	MigrationJob (crates/migrator/src/types.rs)
plans	16-byte UUID bytes	PlanRecord (plan + updated_at)
checklists	16-byte job UUID bytes	ChecklistRecord
watermarks	UTF-8 "{job_uuid}:{disk_id}" composite	u64 little-endian (raw bytes, not JSON)
migrator-inventories	source UUID bytes	InventoryRecord
migrator-manifests	source UUID bytes + | + vm_id UTF-8 (composite)	VmManifest
sources	16-byte source UUID bytes	SourceRecord (encrypted secrets + metadata)
uploads	16-byte upload UUID bytes	UploadRecord

Two oddities here: watermarks is the only tree that doesn’t use serde_json (raw to_le_bytes/from_le_bytes), and sources is the only tree whose value contains AES-256-GCM-sealed secrets, unlocked at runtime with the keyring at /var/lib/synvirt/migrator/keyring.bin.

2.6 Migrations / versioning

None. No __schema_version key anywhere. No refinery / sqlx migrate / hand-rolled migration runner. Stores assume their serde_json schema is forward-compatible — adding optional fields is the only safe evolution today.

---

3. Access patterns

3.1 Per-DB ownership

DB	Handle ownership	Sharing model
policies.db	Arc<sled::Db> held by PolicyStore	cloned into AppState, every handler reads/writes through it
activity.db	Arc<sled::Db> held by ActivityStore	cloned into AppState, also tapped by lifecycle hooks in daemon/src/activity/hooks.rs
iso-detection.db	Arc<sled::Db> inside Store, behind a OnceLock<Option<Store>> static	process-global singleton accessed via Store::global(); soft-fails if unavailable
guest-tools.db	Arc<StateStore> inside AgentState::Inner	shared across guest-agent connector tasks
migrator/state.db	StateStore exposes db() -> Arc<sled::Db>; passed to InventoryStore, SourceRegistry, UploadStore	already follows the “shared handle” pattern

3.2 Flush / durability semantics

Different stores have different durability contracts that consolidation must preserve:

• Daemon DBs (policies, activity, iso-detection) call tree.flush() on every write (e.g. crates/daemon/src/activity/store.rs:107, crates/daemon/src/policy.rs:179, crates/daemon/src/iso_detection/store.rs:121). Durable per mutation.
• guest-tools.db does not flush — relies on sled’s lazy background flush. Acceptable because the worst case is one missed agent-status snapshot.
• migrator/state.db also does not flush per-write — migration jobs are restartable from the source side anyway.

A consolidated DB needs a per-tree (or per-call-site) policy so we don’t accidentally regress activity durability or pay the fsync cost on hot guest-tools updates.

3.3 Cross-DB transactions today

None — and they’re impossible today because each DB is a separate sled::Db handle (sled transactions are scoped to a single Db). After consolidation, atomic multi-tree transactions become possible via sled::Db::transaction(&[tree1, tree2], …) — useful for the few places that today write to two stores serially:

• daemon/src/api/usb_policy.rs writes usb-policy then emits an ActivityEvent to activity.db. Today: two non-atomic writes. Could become atomic post-consolidation.
• daemon/src/api/virtswitch_pending.rs (and friends) write network-virtswitches then emit an ActivityEvent. Same shape.
• Migrator job state machines write jobs + checklists + emit activity. Today these are serialized inside the job runner, not atomic.

This is an opportunity, not a requirement. Recommend not opening that can in this consolidation — keep the behaviour identical first, add atomicity in a follow-up if Tony wants it.

---

4. Coupling with the Edit VM flow (DO NOT TOUCH)

Confirmed: an Edit-VM module is actively under construction. The handlers and modules it touches are listed below. All of these are out of scope for this consolidation.

4.1 Edit-VM REST handlers in crates/daemon/src/api/

• vm_hardware.rs — POST /api/v1/vms/:name/hardware (vCPU / RAM / video adapter edits)
• vm_nic_edit.rs — PUT /api/v1/vms/:name/nics/:mac
• hardware.rs, nics.rs, disks.rs — sibling hardware surfaces
• usb_policy.rs — PUT /api/v1/vms/:uuid/usb-policy

4.2 Crates / modules involved

• crates/daemon/src/policy.rs — owns policies.db (all three trees). Edit-VM reads network-virtswitches to validate NIC target switches and writes usb-policy when the USB-policy editor saves. Shared contention surface.
• crates/daemon/src/libvirt_renderer/ — Edit-VM renders domain XML deltas through this module. Not DB-coupled but the other agent owns it.
• crates/daemon/src/vm_profiles/ — Edit-VM validates hardware specs against this catalog. Not DB-coupled but the other agent owns it.

4.3 Web-UX v2 components owned by the Edit-VM agent

(Listed for awareness only — we don’t touch the frontend in this work.)

• crates/web-ux-v2/src/components/vm-detail/HardwareEditCpuModal.vue
• crates/web-ux-v2/src/components/vm-detail/HardwareEditMemoryModal.vue
• crates/web-ux-v2/src/components/vm-detail/tabs/HardwareTab.vue
• crates/web-ux-v2/src/components/vm-detail/tabs/NetworkTab.vue
• crates/web-ux-v2/src/components/vm-detail/ChangeIsoModal.vue

4.4 RED ZONES — DO NOT TOUCH in this consolidation

policies.db :: usb-policy                — Edit-VM USB editor read/write path
policies.db :: network-virtswitches      — Edit-VM NIC target validation
policies.db :: console-policy            — adjacent in the same DB; touching the DB at all
                                            would conflict with the other agent
crates/daemon/src/policy.rs              — module that owns policies.db
crates/daemon/src/api/vm_hardware.rs     — Edit-VM handler
crates/daemon/src/api/vm_nic_edit.rs     — Edit-VM handler
crates/daemon/src/api/hardware.rs        — sibling Edit-VM surface
crates/daemon/src/api/nics.rs            — sibling Edit-VM surface
crates/daemon/src/api/disks.rs           — sibling Edit-VM surface (future attach/detach work)
crates/daemon/src/api/usb_policy.rs      — Edit-VM USB handler
crates/daemon/src/libvirt_renderer/      — Edit-VM uses this for XML deltas
crates/daemon/src/vm_profiles/           — Edit-VM uses this for hardware validation

Operational rule for this consolidation: treat policies.db as sealed. Touch the other four DBs only.

---

5. Risks and blockers

5.1 Concurrent agent on policies.db

Already covered in §4 — the single biggest blocker. Any sled-level consolidation that moves policies.db’s trees into a unified DB collides directly with the Edit-VM agent. Leave it for a follow-up phase after Edit-VM lands.

5.2 Hardcoded paths, no external config

Every DB path is a Rust literal in crates/daemon/src/main.rs (lines 155-165, 171-181, 222, 357-368) or in the store module itself (crates/daemon/src/iso_detection/store.rs:23-30). The only override is the dev/prod toggle via SYNVIRT_DEV env or --dev CLI.

Violates the spirit of CLAUDE.md §4.1 (“config-driven, no magic constants”). Consolidation is a natural moment to introduce a single storage.state_dir key in DaemonConfig and derive the consolidated path from it.

5.3 The OnceLock singleton in iso_detection

crates/daemon/src/iso_detection/store.rs:36 opens its sled DB via a process-global OnceLock<Option<Store>> accessed through Store::global(). The other four stores are constructed in main.rs and cloned through AppState. Consolidation must pick one model (strong preference: explicit construction in main.rs, no singletons), which means rewriting every Store::global() call site in iso_detection/api.rs and iso_detection/worker.rs.

5.4 sled 0.34 is effectively unmaintained

The sled project has been on the 0.34 line since 2020 with the 1.0 release perpetually “coming soon”. The author has publicly put the project in maintenance mode and pivoted to other work. This isn’t a blocker for consolidation, but it does argue for the “introduce SQLite” path in §7 — if we’re going to do invasive surgery on the storage layer, doing it twice (once to consolidate sled, once to migrate off sled) is wasted effort.

5.5 No schema versioning → consolidation needs an importer

Because no store records a schema version today, the consolidation release must ship a one-shot importer that:

1. Detects the legacy on-disk layout (any of the 4 in-scope DBs present in their old location).
2. Streams every tree’s contents into the new consolidated DB, preserving key bytes and value bytes verbatim.
3. Renames the legacy directories to *.pre-consolidation (don’t delete — let the operator clean up).
4. Records consolidation_version = 1 in the new DB.

Pattern is identical for sled→sled or sled→SQLite; only the writer side changes.

5.6 Activity store flush-on-write contract

Easy to lose during a refactor. Add a regression test that writes an event, drops the DB, reopens, and asserts the event is present — guards against a future change that batches flushes.

5.7 Migrator credential blob

migrator/state.db :: sources holds AES-256-GCM-sealed credentials unlocked via /var/lib/synvirt/migrator/keyring.bin. Consolidation must keep the keyring path (or migrate it). Don’t change both at the same time.

5.8 Doc drift (CLAUDE.md / MEMORY.md)

Not a technical blocker for consolidation, but worth surfacing: several CLAUDE.md sections (§2 crate count, §5.9-5.14, §9 paths table) and three persistent-memory entries (feedback_static_cache, the Settings-module project memory, the R1-deployed reference) describe state that doesn’t exist on master. Any agent reading those docs and trying to import synvirt_time::audit::AuditStore will fail. Recommend separate issue: reconcile docs with reality, or restore the missing branches.

---

6. Summary table

Property	Today	Consolidated target
Engine	sled 0.34 (5 DBs)	one engine, one DB (sled or SQLite — see §7)
Path discovery	Rust literals + SYNVIRT_DEV toggle	one storage.state_dir in DaemonConfig
Serializer	serde_json (13 trees) + raw LE bytes (1 tree)	serde_json for sled path; typed columns for SQL path
Schema versioning	none	__schema_version key (sled) or _meta table (SQL)
Flush policy	per-store (some flush-on-write, some lazy)	per-call-site, with flush_durable() helper
Singletons	1 (iso_detection::Store::global())	0 — all stores constructed in main.rs
Cross-DB tx	impossible (separate handles)	possible (single handle) — but not adopted in this phase
Edit-VM contention	policies.db actively contended	policies.db excluded from consolidation phase 1

---

7. Preliminary proposal — two paths

Path A — Consolidate sled (low-risk, internal-only)

Target: one sled DB at /var/lib/synvirt/state.db/ (path config-driven via storage.state_dir). All in-scope trees move to it with domain-prefixed names to avoid collisions and make grep useful:

activity__events
iso__detections
guest_tools__state
migrator__jobs
migrator__plans
migrator__checklists
migrator__watermarks
migrator__inventories
migrator__manifests
migrator__sources
migrator__uploads

# (policies.db trees migrate in a later phase, after Edit-VM lands)
# policy__usb
# policy__console
# policy__virtswitches

Stores become thin wrappers around a shared Arc<sled::Db> (the pattern the migrator already follows). The OnceLock in iso_detection is removed. Path resolution moves from main.rs literals to DaemonConfig::storage.state_dir.

Pros: minimal blast radius, no new dependency, all existing serde schemas survive byte-for-byte, importer is trivial.

Cons: still on unmaintained sled 0.34, still serde_json-blob storage with no JOINs, no real schema migrations.

Path B — Introduce SQLite (deeper, durable)

Target: one SQLite file at /var/lib/synvirt/synvirt.db (path config-driven). New synvirt-storage crate owns the connection pool (r2d2_sqlite or sqlx::SqlitePool). Real tables, real PRIMARY KEY / FOREIGN KEY constraints, real schema migrations (refinery for rusqlite, or sqlx migrate for sqlx).

Trees become tables, prefixed by domain:

activity_events
iso_detections
guest_tools_state
migrator_jobs
migrator_plans
migrator_checklists
migrator_watermarks
migrator_inventories
migrator_manifests
migrator_sources
migrator_uploads

The big-endian-inverted timestamp in activity becomes a real timestamp_millis INTEGER NOT NULL column with a descending index. watermarks becomes a (job_uuid TEXT, disk_id TEXT, offset INTEGER) table with a composite PK. JSON blobs that don’t need querying stay as TEXT columns containing serde_json (sqlite’s json functions are available for ad-hoc admin queries).

Pros: actively maintained engine, real migrations, JOINs across domains (e.g. “list all activity for migration jobs that failed yesterday”), durable foundation that doesn’t need redoing in a year.

Cons: bigger surgery, adds a runtime dependency on libsqlite3 (or bundled), needs migration files written for every existing schema, needs careful planning around durability (PRAGMA journal_mode=WAL, PRAGMA synchronous=NORMAL) to match the activity flush-on-write contract.

Recommendation

Path B if Tony is willing to absorb the larger refactor. Justification: sled 0.34 is unmaintained, the repo will eventually need to leave it anyway, and doing storage surgery twice is wasted work. SQLite is the boring-and-correct choice for an embedded durable store; the WAL mode + synchronous=NORMAL combination matches sled’s current durability profile while giving real schema migrations and JOIN capability essentially for free.

If Tony wants minimum risk and is fine with sled long-term, Path A is a clean win — the migrator already proves the pattern works (one handle, many trees, prefixed names).

Suggested order of consolidation (works for either path)

Order chosen to leave the contended policies.db for absolute last and to start with the most contained module:

1. migrator/state.db — pilot. Already groups multiple stores around one handle. Smallest blast radius. Closest to the target shape. The shared-handle pattern is already live, so this is mostly a path / tree-prefix rename + adopting the new engine if we go Path B.
2. guest-tools.db — single tree, simple schema, no other crates depend on it. Good second pilot.
3. activity.db — needs the flush-on-write durability contract carried over verbatim. Add a regression test.
4. iso-detection.db — last of the four in-scope DBs. Requires killing the OnceLock singleton (rewrite call sites in iso_detection/api.rs and iso_detection/worker.rs).
5. (Out of scope — wait for Edit-VM to land) policies.db — migrate its three trees in a follow-up branch once the other agent merges. Same pattern, same importer, same prefixed names (policy__usb, policy__console, policy__virtswitches).

Each step is one PR. Each PR ships its own importer so the previous release can be rolled back to without losing data written between release boundaries.

---

8. What this reconnaissance does not include

• No design for the new consolidated schema beyond the tree-prefix proposal in §7. Real SQL DDL (Path B) needs a follow-up doc.
• No benchmark of sled vs. SQLite under daemon workloads.
• No analysis of policies.db’s internals beyond what’s needed to mark it as out-of-scope.
• No proposal for cross-DB atomic transactions (deliberately deferred per §3.3).
• No fix for the CLAUDE.md / MEMORY.md doc drift identified in §0.2 — flagged for separate triage.
• No code changes. Reconnaissance only.