State/sync/config persistence & kvDb hardening: crash-safe writes, migration versioning, and god-file/dedup decomposition

[arul28]

Summary: Correctness/robustness gaps in the desktop main-process state/persistence layer plus the kvDb migration liability and the broad state/sync/config decomposition that keeps these fragile.

kvDb / state persistence correctness:

• Global state file written non-atomically; a torn write resets recent/last project + pending-update tracking to empty. writeGlobalState() does fs.writeFileSync on the live file; readGlobalState() returns {} on parse error, silently losing recentProjects/lastProjectRoot/pendingInstallUpdate (read by autoUpdateService). globalState.ts:38-45,:28-36; autoUpdateService.ts:200-223. Fix: write temp + renameSync.
• sync.applyChanges/discardUnpublishedChangesForTables use deferred begin on a multi-process DB, risking avoidable SQLITE_BUSY. kvDb.ts:3213-3257,:3258-3281,:90-92; projectConfigService.ts:3096 already uses BEGIN IMMEDIATE. Fix: use BEGIN IMMEDIATE.
• AdeDb.flushNow() is a no-op but shutdown call sites comment it persists writes. kvDb.ts:3297; main.ts:4430-4433,:4578; bootstrap.ts:1271. Fix: delete or implement (PRAGMA wal_checkpoint(TRUNCATE)); remove false comments.

kvDb migrate() versioning (the single biggest state liability):

• migrate() spans ~1,863 lines: 84 create table if not exists, 75 swallow-all try{alter}catch{}, 78 empty catch{}, no PRAGMA user_version, so all CREATEs/ALTERs re-run every boot and broken ALTERs fail silently (later 'no such column'). pr_pipeline_settings is the worst (14 silent ALTERs + backfill). kvDb.ts:1085-2947,:2821-2875. Fix: ordered version-gated migration list; at minimum a safeAddColumn helper that checks rawHasColumn and rethrows non-duplicate errors with a warn.

Decompose state/sync/config god-files & dedupe SQL/coercion:

• CRR-aware run/get/all wrapper implemented twice (makeMigrateDb vs returned AdeDb). kvDb.ts:2994-3019,:3119-3143 — one makeCrrAwareDb factory.
• sessionService.reconcileStaleRunningSessions hand-builds SQL strings + parallel param arrays in two IIFEs. sessionService.ts:511-663 — build {sql,params} clause objects.
• projectConfigService hand-rolls 66 coerce/validators + 92 isRecord despite zod 4 being a dependency. projectConfigService.ts:1256-1580,:788-878 — introduce zod schemas with field-level .catch().
• syncHostService (3,477 lines) and syncRemoteCommandService (3,042 lines, 203 handlers) are god-files (desktop files are 1-line re-exports into ade-cli). Fix: split into per-domain registrar / peer-transport / changeset-pump modules. (Lead decomposition tracked under the CLI sync ticket.)
• operationService duplicates the identical 10-column operations SELECT in three methods. operationService.ts:97-118,:143-174,:198-218 — extract OPERATION_COLUMNS.

Verification confidence: High for global-state, flushNow, migrate counts (brace-matched) and all line counts; medium for the deferred-BEGIN perf finding (clean rollback, no corruption).

[linear-code]

ADE-71