[Bug] SQLite migration is half-done: orchestrator designs, builds, and journal never persist to the DB

[sjohnston1972]

Background

NetBud is a self-hosted network-design tool. A user uploads a customer brief; an orchestrator splits it into sections (LAN/WAN/EDGE/CLOUD/QOS), runs a designer/critic loop per section, then a build agent turns an approved design into a Containerlab/GNS3 sandbox.

The project recently pivoted from flat-file storage (data/designs/*.json, data/builds/*.json, data/build_journal.jsonl) to SQLite (SQLModel tables in backend/app/db/models.py). The README states this migration is complete:

"Persistent SQLite store (data/netbud.sqlite) covers customers, briefs, runs, section designs, builds, and the build journal."
"SQLite is the source of truth from this version on."
"Every dry-run and push is appended to the SQLite build_journal table."

None of that is true for the runtime code. The migration is only half-done: the orchestrator and intake write to SQLite, but the designer/critic loop, the build agent, and the build journal all still read and write flat files. The SQLite builds and build_journal tables are only ever populated by the one-shot legacy_import script — never during normal operation.

Problem / Goal

The two halves of the app cannot see each other's data:

1. The orchestrator writes each section design to the SQLite section_designs table (orchestrator.py step 3). But the build agent looks a design up via app.agents.loop.get_design(), which only reads data/designs/<id>.json (loop.py:92). A user cannot build a sandbox from any orchestrator-produced design — the headline v2 workflow dead-ends.
2. New builds are written to data/builds/<id>.json (build.py:120 save_build), not the BuildRecord table. The builds table stays empty at runtime.
3. The build journal is appended to data/build_journal.jsonl (build.py:97 append_journal) and read back from the same file (build.py:105 list_journal), not the BuildJournalEntry table. The README's SQLite-journal claim is false.
4. Because builds never land in BuildRecord, the orphan-handling code in runs.py:99 and customers.py:92 (which nulls BuildRecord.section_design_id on run/customer delete) operates on an always-empty table — dead code that silently does nothing.

Goal: finish the migration so SQLite is genuinely the source of truth. After this work, an orchestrator-run section can be built into a sandbox, and builds + journal entries persist to (and are read from) SQLite.

Where to look

• backend/app/agents/loop.py — flat-file design store (_designs_dir, save_design, get_design, list_designs, delete_design).
• backend/app/agents/build.py — flat-file build store + journal (_builds_dir, save_build, get_build, list_builds, delete_build, _journal_path, append_journal, list_journal) and the build flow converse_build (calls loop.get_design).
• backend/app/db/models.py — the target tables already exist: SectionDesign, BuildRecord, BuildJournalEntry.
• backend/app/db/session.py — engine, get_session, init_db.
• backend/app/api/build.py, backend/app/api/design.py, backend/app/api/journal.py — HTTP surface that currently calls the flat-file helpers.
• backend/app/db/legacy_import.py — shows the exact mapping from flat-file shape → each table; reuse it as the reference for field names.
• README.md — the claims that must end up true (or be corrected).

Suggested approach

1. Read legacy_import.py first — it already maps every flat-file field onto the SQLite tables, so it is the spec for what each column holds.
2. Decide on a single design-lookup contract: builds are created from a SectionDesign (SQLite id, an integer) rather than a flat-file hex design_id. The build agent should fetch the design record from SectionDesign.record.
3. Move build persistence and the journal onto their tables (BuildRecord, BuildJournalEntry), keeping the same record JSON shape so the frontend needs no changes.
4. Update the API layer (api/build.py, api/journal.py, and the design endpoints in api/design.py) to call the new DB-backed helpers.
5. Update README.md so its persistence claims match reality.
6. Run locally to verify end to end (see Testing).

This is large; it is split into the linked sub-issues. Do them in order — 1a unblocks the user-visible workflow, 1b/1c move the remaining stores, 1d cleans up the read/list/export paths and the docs.

Acceptance criteria

• A section design produced by an orchestrator run can be built into a sandbox through the normal build flow (no flat file required).
• New builds appear as rows in the builds table; data/builds/*.json is no longer written by runtime code.
• New journal events appear as rows in build_journal; data/build_journal.jsonl is no longer written by runtime code.
• GET /api/build/list, GET /api/build/{id}, and GET /api/journal/list return data sourced from SQLite.
• The orphan-nulling logic in runs.py / customers.py actually has rows to operate on.
• README persistence claims are accurate.

Testing

Run the backend locally: cd backend && uvicorn app.main:app --reload --port 8080. (init_db() creates the SQLite file under NETBUD_DATA_DIR on startup.)

Manual end-to-end:

1. Upload/paste a brief (POST /api/intake/text), wait for status=ready.
2. Start a run (POST /api/runs), poll GET /api/runs/{id} until a section is approved.
3. Build that section (POST /api/build/converse) — it must succeed and return build_saved.
4. sqlite3 data/netbud.sqlite "select count(*) from builds; select count(*) from build_journal;" — both should be non-zero.
5. Confirm no new files under data/builds/ or data/build_journal.jsonl.

Add unit coverage for the new DB helpers (see the separate test-suite issue).

Out of scope

• Deleting or rewriting the legacy_import.py one-shot importer (it stays, for pre-migration data).
• Removing the old flat files from disk (leave existing user data in place).
• Any frontend redesign — the record JSON shape must stay identical so the UI is unaffected.
• Swapping the ad-hoc _ensure_added_columns migration shim for Alembic.