#!/usr/bin/env python3 """Append an SQLite log snapshot without replacing rows or their training bodies. Dry-run by default. Apply to a stopped deployment with an exclusive backup path. The stable source-id + original row id ledger makes retries/incremental snapshots idempotent while keeping legitimately identical calls as distinct training rows. """ import argparse import hashlib import json import os from pathlib import Path import sqlite3 TABLES = ("completions", "requests", "decisions", "evidence") def connect_readonly(path): uri = Path(path).resolve().as_uri() db = sqlite3.connect(uri + "?mode=ro", uri=True) try: db.execute("PRAGMA schema_version").fetchone() return db except sqlite3.OperationalError as error: db.close() if str(error) != "unable to open database file": raise # macOS SQLite can require a writable VFS handle to create missing WAL # sidecars, even for reads. Allow that housekeeping, but prohibit SQL writes. # mode=rw also refuses to create a missing database. db = sqlite3.connect(uri + "?mode=rw", uri=True) try: db.execute("PRAGMA query_only=ON") db.execute("PRAGMA schema_version").fetchone() return db except BaseException: db.close() raise def tables(db): return {row[0] for row in db.execute("SELECT name FROM sqlite_master WHERE type='table'")} def columns(db, table): return [row[1] for row in db.execute('PRAGMA table_info("%s")' % table)] def fingerprint(values): return hashlib.sha256(json.dumps(values, ensure_ascii=False, separators=(",", ":")).encode()).hexdigest() def snapshot(source, destination): destination = Path(destination) destination.parent.mkdir(parents=True, exist_ok=True) fd = os.open(destination, os.O_CREAT | os.O_EXCL | os.O_WRONLY, 0o600) os.close(fd) src, dst = connect_readonly(source), sqlite3.connect(destination) try: src.backup(dst) # A transferred snapshot must be self-contained. WAL-mode headers can # require missing -wal/-shm files on a read-only open on another host. if dst.execute("PRAGMA journal_mode=DELETE").fetchone()[0] != "delete": raise RuntimeError("could not make snapshot self-contained") if dst.execute("PRAGMA integrity_check").fetchone()[0] != "ok": raise RuntimeError("snapshot integrity check failed") finally: src.close() dst.close() def merge(source, target, source_id, backup=None, apply=False): if Path(source).resolve() == Path(target).resolve(): raise ValueError("source and target must differ") if not source_id.strip(): raise ValueError("use a stable, nonempty source identity") src, dst = connect_readonly(source), connect_readonly(target) try: for db in (src, dst): if db.execute("PRAGMA integrity_check").fetchone()[0] != "ok": raise ValueError("database integrity check failed") existing = tables(dst) report = {} for table in TABLES: if table not in tables(src): continue if table not in existing: raise ValueError("initialize the target with the go service first: missing " + table) if columns(src, table) != columns(dst, table): raise ValueError("source/target column mismatch in " + table) report[table] = { "sourceRows": src.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0], "targetBefore": dst.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0], "inserted": 0, "alreadyImported": 0, } except BaseException: src.close() raise finally: dst.close() if not apply: src.close() return {"dryRun": True, "tables": report} if not backup: src.close() raise ValueError("apply requires a new backup path") try: snapshot(target, backup) dst = sqlite3.connect(target, timeout=30) except BaseException: src.close() raise try: dst.execute("BEGIN IMMEDIATE") dst.execute("""CREATE TABLE IF NOT EXISTS log_imports ( source_id TEXT NOT NULL, table_name TEXT NOT NULL, source_row_id INTEGER NOT NULL, target_row_id INTEGER NOT NULL, row_sha256 TEXT NOT NULL, PRIMARY KEY(source_id,table_name,source_row_id))""") for table, counts in report.items(): names = columns(src, table) id_index = names.index("id") payload_names = [name for name in names if name != "id"] projection = ",".join('"%s"' % name for name in payload_names) insert = 'INSERT INTO "%s" (%s) VALUES (%s)' % (table, projection, ",".join("?" for _ in payload_names)) for row in src.execute('SELECT * FROM "%s" ORDER BY id' % table): row_id = row[id_index] values = tuple(value for i, value in enumerate(row) if i != id_index) digest = fingerprint(values) imported = dst.execute("SELECT target_row_id,row_sha256 FROM log_imports WHERE source_id=? AND table_name=? AND source_row_id=?", (source_id, table, row_id)).fetchone() if imported: saved = dst.execute('SELECT %s FROM "%s" WHERE id=?' % (projection, table), (imported[0],)).fetchone() if imported[1] != digest or saved is None or fingerprint(saved) != digest: raise ValueError("previously imported row changed: %s:%s" % (table, row_id)) counts["alreadyImported"] += 1 continue # A matching request_id already in the destination is ambiguous; # do not silently merge two correlated episodes or guess a dedupe. request_id = values[payload_names.index("request_id")] if request_id and dst.execute('SELECT 1 FROM "%s" WHERE request_id=? AND id NOT IN (SELECT target_row_id FROM log_imports WHERE source_id=? AND table_name=?) LIMIT 1' % table, (request_id, source_id, table)).fetchone(): raise ValueError("request_id overlaps a different origin in " + table) target_id = dst.execute(insert, values).lastrowid dst.execute("INSERT INTO log_imports VALUES(?,?,?,?,?)", (source_id, table, row_id, target_id, digest)) counts["inserted"] += 1 counts["targetAfter"] = dst.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0] if counts["targetAfter"] != counts["targetBefore"] + counts["inserted"]: raise ValueError("unexpected target row count in " + table) if dst.execute("PRAGMA integrity_check").fetchone()[0] != "ok": raise ValueError("merged database integrity check failed") dst.commit() return {"dryRun": False, "sourceId": source_id, "backup": str(backup), "tables": report} except BaseException: dst.rollback() raise finally: src.close() dst.close() if __name__ == "__main__": parser = argparse.ArgumentParser(description=__doc__) parser.add_argument("--source", required=True) parser.add_argument("--target", required=True) parser.add_argument("--source-id", required=True) parser.add_argument("--backup") parser.add_argument("--apply", action="store_true") args = parser.parse_args() print(json.dumps(merge(args.source, args.target, args.source_id, args.backup, args.apply), indent=2))