Regulated Platform Modernization

-- Public SQLite: every foreign-key edge in a schema copy, the map of which
-- tables must move together. Runs on any SQLite database.
SELECT m.name  AS from_table,
       fk."table" AS to_table,
       fk."from"  AS from_column,
       fk."to"    AS to_column
FROM sqlite_schema AS m
JOIN pragma_foreign_key_list(m.name) AS fk
WHERE m.type = 'table'
ORDER BY to_table, from_table;

-- Synthetic illustration: a shadow run. The same inputs go to the legacy
-- system and the new service; every output that differs is listed.
CREATE TEMP TABLE IF NOT EXISTS legacy_output (
  run_id TEXT NOT NULL, record_key TEXT NOT NULL, payload_sha256 TEXT NOT NULL,
  PRIMARY KEY (run_id, record_key)
);
CREATE TEMP TABLE IF NOT EXISTS new_output (
  run_id TEXT NOT NULL, record_key TEXT NOT NULL, payload_sha256 TEXT NOT NULL,
  PRIMARY KEY (run_id, record_key)
);

SELECT COALESCE(l.record_key, n.record_key) AS record_key,
       CASE
         WHEN n.record_key IS NULL THEN 'missing in new'
         WHEN l.record_key IS NULL THEN 'only in new'
         ELSE 'payload differs'
       END AS finding
FROM legacy_output l
FULL OUTER JOIN new_output n
  ON n.run_id = l.run_id AND n.record_key = l.record_key
WHERE COALESCE(l.run_id, n.run_id) = 'shadow-001'
  AND (l.record_key IS NULL OR n.record_key IS NULL
       OR l.payload_sha256 <> n.payload_sha256)
ORDER BY record_key;

-- Synthetic illustration: cutovers with no successful rollback rehearsal
-- recorded in the 14 days before go-live.
CREATE TEMP TABLE IF NOT EXISTS cutover (
  cutover_id TEXT PRIMARY KEY, capability TEXT NOT NULL, go_live_utc TEXT NOT NULL
);
CREATE TEMP TABLE IF NOT EXISTS rollback_rehearsal (
  cutover_id TEXT NOT NULL REFERENCES cutover(cutover_id),
  rehearsed_utc TEXT NOT NULL, outcome TEXT NOT NULL CHECK (outcome IN ('restored','failed'))
);

SELECT c.cutover_id, c.capability, c.go_live_utc
FROM cutover c
WHERE NOT EXISTS (
  SELECT 1 FROM rollback_rehearsal r
  WHERE r.cutover_id = c.cutover_id
    AND r.outcome = 'restored'
    AND julianday(c.go_live_utc) - julianday(r.rehearsed_utc) BETWEEN 0 AND 14
)
ORDER BY c.go_live_utc;

-- Synthetic illustration: per-table record counts and content checksums,
-- legacy copy against the new service, after each sync. Any table whose
-- count or checksum differs is listed.
CREATE TEMP TABLE IF NOT EXISTS sync_count (
  sync_id TEXT NOT NULL, side TEXT NOT NULL CHECK (side IN ('legacy','new')),
  table_name TEXT NOT NULL, row_count INTEGER NOT NULL, content_sha256 TEXT NOT NULL,
  PRIMARY KEY (sync_id, side, table_name)
);

SELECT l.table_name,
       l.row_count AS legacy_rows,
       n.row_count AS new_rows,
       n.row_count - l.row_count AS difference,
       l.content_sha256 = n.content_sha256 AS checksums_match
FROM sync_count l
LEFT JOIN sync_count n
  ON n.sync_id = l.sync_id AND n.table_name = l.table_name AND n.side = 'new'
WHERE l.side = 'legacy'
  AND l.sync_id = 'sync-001'
  AND (n.table_name IS NULL
       OR n.row_count <> l.row_count
       OR n.content_sha256 <> l.content_sha256)
ORDER BY l.table_name;