SQLite settings & app-state store

Mark It Down's main process keeps all persistent app state in a SQLite database at <userData>/mid.sqlite. Settings, recent files, pinned folders, workspaces, warehouse links, and an exports audit log all flow through this single file. The renderer never sees the DB — it talks to the existing mid:read-app-state / mid:patch-app-state IPCs, which are now backed by the database.

Why SQLite (vs. JSON file)

The previous store was a single <userData>/state.json blob. That worked while the surface was small but had three problems:

1. No concurrency story. Multiple writes from different IPC handlers (notes, settings, repo sync) would race each other on the JSON file.
2. Listing/filtering required loading everything. Recent-files and pinned-folders queries forced a full read+parse on every call.
3. No history. We can now record every export to export_history so the unique-id story from #238 is queryable ("what did I export last week?").

SQLite gives us atomic writes, indexed lookups, transactions for the migration step, and zero extra runtime dependencies on macOS / Windows / Linux. WAL journaling means the DB file stays usable even if the process crashes mid-write.

Schema

Settings rows store JSON blobs so individual keys can evolve their shape (object → array, scalar → object) without further DB migrations. Trade-off: you can't query inside a setting from SQL — but settings are read in bulk by getAllSettings() and parsed in JS, so it's not a real loss.

Lifecycle

openDB is called exactly once at startup and the handle is cached as a module singleton. getDB() returns the cached handle and throws if called before openDB.

Migration from state.json

migrateLegacyState(userDataDir) is the bridge for users upgrading from the JSON-state build:

1. If <userData>/state.json does not exist → no-op, returns { migrated: false }.
2. If it exists but is malformed → log a warning, leave the file in place, return { migrated: false, error }.
3. Otherwise, in a single transaction:
   • Settings keys (lastFolder, splitRatio, fontFamily, fontSize, theme, previewMaxWidth, codeExportGradient, activeWorkspace, ghToken) → settings table.
   • recentFiles array → recent_files (timestamps reverse-deduced from array order so head = most recent).
   • pinnedFolders array → pinned_folders with stable pin-legacy-<idx> ids.
   • workspaces array → workspaces.
   • A migrationVersion = 1 setting marker is written.
4. state.json is renamed to state.json.migrated (kept, not deleted, so a panicked rollback can still see the original blob).

The rename is what makes the migration idempotent: the next launch's readFile short-circuits before doing any work. Re-running migrateLegacyState is safe even if the rename fails — the transaction uses INSERT … ON CONFLICT DO UPDATE for settings.

API surface

Everything lives in apps/electron/db.ts:

Group	Functions
Lifecycle	openDB(userDataDir), closeDB(), getDB(), getDBPath(), migrateLegacyState(userDataDir)
Settings	getSetting<T>(key), setSetting(key, value), getAllSettings()
Recent files	listRecentFiles(limit?), pushRecentFile(path), clearRecentFiles()
Pinned folders	listPinnedFolders(), replacePinnedFolders(rows)
Workspaces	listWorkspaces(), replaceWorkspaces(rows)
Warehouses	listWarehouses(workspace?), upsertWarehouse(row), deleteWarehouse(id)
Export history	recordExport(row), listExportHistory(limit?)

apps/electron/main.ts keeps its existing readAppState() / writeAppState(patch) helpers as the only seam — they project the DB into the AppState shape the renderer expects, and the renderer never had to change.

New IPCs (#238 follow-up)

• mid:record-export — { id, sourcePath?, format, filePath } → inserts into export_history.
• mid:list-export-history — (limit?: number) → returns the last N exports for "Recent exports" UI surfaces.

These are exposed on window.mid as recordExport(...) and listExportHistory(limit).