sqlite-insert-or-replace-not-null-preserve

Answer

Root cause — why 5-of-9 columns failed. SQLite's INSERT OR REPLACE is not an upsert: it is DELETE + INSERT. Every column not present in the insert column-list (and without a DEFAULT) must be nullable, or the re-insert raises NOT NULL constraint failed: ss_meta.config_json. A rebuild that only touches 6 of the 9 ss_meta columns therefore blew up on every meta update.

Fix A — the GAP-007 pattern (INSERT OR REPLACE ... SELECT). Pull the preserved NOT NULL columns out of the existing row so the re-insert always supplies them:

INSERT OR REPLACE INTO ss_meta
    (workspace_id, name, updated_at, revision, config_json, backend, created_at, owner)
SELECT ?, ?, ?, ?, config_json, backend, created_at, ?
  FROM ss_meta
 WHERE workspace_id = ?;

config_json, backend, created_at are copied from the surviving row; the four ? are the new rebuild values. Verified: NOT NULL violation gone, preserved columns byte-identical after replacement, single row (no duplicates), 5 consecutive rebuilds stable.

Caveat (edge case T3): if the row does not yet exist, the SELECT matches 0 rows and the statement is a silent no-op — nothing is inserted. Fine for a pure "rebuild existing meta" path, but it will not seed a new workspace.

Fix B — recommended, covers both paths. ON CONFLICT DO UPDATE is a true upsert (no delete/re-insert; unchanged columns are never written, so NOT NULL columns can't be violated):

INSERT INTO ss_meta
    (workspace_id, name, updated_at, revision, config_json, backend, created_at, owner)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(workspace_id) DO UPDATE SET
    name       = excluded.name,
    updated_at = excluded.updated_at,
    revision   = excluded.revision,
    owner      = excluded.owner;

Existing row → update path: config_json/backend/created_at preserved untouched. Missing row → insert path: seeds with fresh values. Use this if first-seed and rebuild share one statement.

atomicFileSwap — rename-swap is unsafe for live connections. Renaming a new file over a live WAL database leaves the connection and the filesystem in inconsistent states. Demonstrated failure modes (SQLite 3.46.1): the stale -wal sidecar is replayed by a fresh connection over the new file (old rows returned instead of swapped content — the swap is silently reverted/corrupted); the live connection keeps serving the old unlinked inode (reads diverge, writes are lost). On other builds/VFSes (and as observed in the report), the HAS_MOVED/lock path surfaces as SQLITE_READONLY_DBMOVED (1032). Safe replacements:

# Safe A: quiesce first — close ALL connections, drop sidecars, then rename, then reopen
for conn in connections: conn.close()
for ext in ("-wal", "-shm"):
    p = db_path + ext
    if os.path.exists(p): os.remove(p)
os.replace(staging_path, db_path)

# Safe B: no rename at all — SQLite backup API into the live target (connection-safe)
with sqlite3.connect(target_db) as dst:
    staging.backup(dst)