← Files Codex SecurityARCHIVED FILE
scripts/workbench_schema.py
17.8 KB · Nov 18, 2026 · 06:11 UTC
"""SQLite schema history for the Codex Security workbench."""
import argparse
import json
import sqlite3
from collections.abc import Callable
from pathlib import Path
_SHARED = Path(__file__).resolve().parents[1] / "shared"
_HISTORY = json.loads((_SHARED / "workbench-migrations.json").read_text(encoding="utf-8"))
_STATEMENTS = {migration["version"]: migration["statements"] for migration in _HISTORY}
_REPAIR_HISTORY = json.dumps(_HISTORY)
_REPAIR_PLAN = json.loads((_SHARED / "workbench-history-repairs.json").read_text(encoding="utf-8"))
MIGRATIONS = tuple(
(migration["version"], migration["name"], "\n".join(migration["statements"]))
for migration in _HISTORY
)
def migration_json_extract(document: str, path: str) -> str | int | float | None:
# The fixed scalar paths in migrations 38/39 must retain embedded NULs on older SQLite.
value = json.loads(document)
for key in path[2:].split("."):
value = value.get(key) if isinstance(value, dict) else None
return value
def migrate_finding_workflow_results(connection: sqlite3.Connection) -> None:
for row in connection.execute("SELECT id, results_json FROM finding_workflows").fetchall():
stages = json.loads(row["results_json"])["stages"]
results = {stage: value["result"] for stage, value in stages.items() if "result" in value}
if "pendingWrite" in stages["dedupe"]:
results["dedupePendingWrite"] = stages["dedupe"]["pendingWrite"]
# SQLite JSON extraction on older runtimes truncates embedded NULs in diagnostics.
connection.execute(
"UPDATE finding_workflows SET scan_error = ?, publish_error = ?, dedupe_error = ?, "
"results_json = ? WHERE id = ?",
(
stages["scan"].get("error"),
stages["publish"].get("error"),
stages["dedupe"].get("error"),
json.dumps(results, allow_nan=False),
row["id"],
),
)
def apply_migrations(
connection: sqlite3.Connection,
migrations: tuple[tuple[int, str, str], ...],
now: Callable[[], str],
backfill_security_targets: Callable[[sqlite3.Connection], None],
*,
immediate: bool = False,
discovery_roots: tuple[Path, ...] = (),
) -> None:
"""Apply missing schema versions and repair supported historical migrations.
Commit pending work on the connection before starting the migration transaction.
Set immediate to acquire the SQLite write lock up front. The backfill callback
links older scans and workspaces to stable targets when required.
"""
from workbench_target import scoped_git_discovery
connection.commit()
connection.execute("BEGIN IMMEDIATE" if immediate else "BEGIN")
with (
connection,
scoped_git_discovery(
lambda: [*migration_target_paths(connection), *discovery_roots],
recorded_git_roots=lambda: recorded_git_checkout_roots(connection),
),
):
connection.execute(
"""
CREATE TABLE IF NOT EXISTS schema_migrations (
version INTEGER PRIMARY KEY,
name TEXT NOT NULL,
applied_at TEXT NOT NULL
)
"""
)
normalize_pre_release_migrations(connection, now())
applied = {
row["version"] for row in connection.execute("SELECT version FROM schema_migrations")
}
should_backfill_targets = False
for version, name, sql in migrations:
if version == 6:
# Preserve the original one-line index declaration in sqlite_schema.
for statement in _STATEMENTS[6]:
apply_repair_statement(connection, statement.replace("\n", " "))
elif version == 16:
should_backfill_targets = repair_stable_targets_migration(connection)
elif version == 67:
should_backfill_targets = (
repair_repository_identity_migration(connection)
or version not in applied
or should_backfill_targets
)
elif version in applied:
if version in (
2,
12,
13,
26,
28,
31,
32,
42,
47,
50,
54,
55,
58,
59,
60,
61,
69,
70,
):
repair_schema(connection, version)
elif version == 11:
repair_deep_scan_migration(connection)
else:
if version in (38, 39):
connection.create_function("migration_json_extract", 2, migration_json_extract)
sql = sql.replace("json_extract(", "migration_json_extract(")
for statement in sql_statements(sql):
connection.execute(statement)
if version == 38:
migrate_finding_workflow_results(connection)
elif version == 68:
from workbench_target_state import upgrade_linux_repository_generations
upgrade_linux_repository_generations(connection)
if version not in applied:
connection.execute(
"INSERT INTO schema_migrations (version, name, applied_at) VALUES (?, ?, ?)",
(version, name, now()),
)
if 27 in applied:
repair_deep_scan_failure_counter_migration(connection)
if should_backfill_targets:
backfill_security_targets(connection)
def migration_target_paths(connection: sqlite3.Connection) -> list[Path]:
paths: set[Path] = set()
for table, column in (
("security_targets", "current_path"),
("workspaces", "target_path"),
("scans", "target_path"),
):
columns = {row["name"] for row in connection.execute(f"PRAGMA table_info({table})")}
if column in columns:
condition = (
" AND (target_path_unvalidated = 0 OR target_id IS NOT NULL)"
if table == "workspaces" and "target_path_unvalidated" in columns
else ""
)
paths.update(
Path(row[0])
for row in connection.execute(
f"SELECT DISTINCT {column} FROM {table} WHERE {column} IS NOT NULL{condition}"
)
)
return list(paths)
def recorded_git_checkout_roots(connection: sqlite3.Connection) -> set[Path] | None:
"""Return saved boundaries, or None if any historical target lacks one."""
target_columns = {
row["name"] for row in connection.execute("PRAGMA table_info(security_targets)")
}
boundaries = (
{
Path(target): Path(root)
for target, root in connection.execute(
"SELECT current_path, git_checkout_root FROM security_targets "
"WHERE git_checkout_root IS NOT NULL"
)
}
if "git_checkout_root" in target_columns
else {}
)
# Even an unversioned scan may have come from an unborn Git checkout.
if any(path not in boundaries for path in migration_target_paths(connection)):
return None
roots = set(boundaries.values())
columns = {row["name"] for row in connection.execute("PRAGMA table_info(workspaces)")}
if "target_path_unvalidated" in columns:
saved = (
"rejected_checkout_roots_json" if "rejected_checkout_roots_json" in columns else "NULL"
)
for (root,) in connection.execute(
f"SELECT DISTINCT value FROM workspaces LEFT JOIN json_each({saved}) "
f"WHERE {saved} IS NOT NULL OR "
"(target_path IS NOT NULL AND target_path_unvalidated = 1 AND target_id IS NULL)"
):
if root is None:
return None
roots.add(Path(root))
return roots
def normalize_pre_release_migrations(connection: sqlite3.Connection, timestamp: str) -> None:
for query in _REPAIR_PLAN:
actions = connection.execute("\n".join(query), {"history": _REPAIR_HISTORY}).fetchall()
for action in actions:
operation, value = action["operation"], action["value"]
if operation == "error":
raise SystemExit(value)
if operation == "additive":
repair_schema(connection, value)
elif operation == "column":
add_migration_column(connection, value)
else:
connection.execute(value, (timestamp,) if operation == "timestamp" else ())
def repair_deep_scan_migration(connection: sqlite3.Connection) -> None:
scan_columns = {row["name"] for row in connection.execute("PRAGMA table_info(scans)")}
owner_column_missing = "deep_scan_owner_thread_id" not in scan_columns
expected_objects = {
"scans_one_running_deep_per_owner_target",
"deep_scan_runs",
"deep_scan_workers",
"deep_scan_workers_completion_sequence",
"deep_scan_workers_by_scan_status",
"deep_scan_dedup_inputs",
}
existing_objects = {
row["name"]
for row in connection.execute(
"SELECT name FROM sqlite_master WHERE name LIKE 'deep_scan_%' "
"OR name = 'scans_one_running_deep_per_owner_target'"
)
}
if not owner_column_missing and expected_objects <= existing_objects:
return
for statement in _STATEMENTS[11]:
if statement.startswith("UPDATE scans") and not owner_column_missing:
continue
apply_repair_statement(connection, statement)
if statement.startswith("UPDATE scans") and "continuation_thread_id" in scan_columns:
connection.execute(
"UPDATE scans SET deep_scan_owner_thread_id = continuation_thread_id "
"WHERE mode = 'deep' AND status = 'running' "
"AND continuation_thread_id IS NOT NULL"
)
def repair_deep_scan_failure_counter_migration(connection: sqlite3.Connection) -> None:
threshold, count, backfill = _STATEMENTS[27]
if add_migration_column(connection, threshold):
connection.execute(backfill)
add_migration_column(connection, count)
def repair_stable_targets_migration(connection: sqlite3.Connection) -> bool:
"""Repair a partial target-ID migration and return whether the schema required repair.
Create missing target tables, columns, and indexes. Clear scan references
to missing target records so they can be backfilled.
"""
workspace_columns = {row["name"] for row in connection.execute("PRAGMA table_info(workspaces)")}
scan_columns = {row["name"] for row in connection.execute("PRAGMA table_info(scans)")}
existing_objects = {
row["name"]
for row in connection.execute(
"SELECT name FROM sqlite_master WHERE name IN ('security_targets', 'scans_by_target')"
)
}
if (
"target_id" in workspace_columns
and "target_id" in scan_columns
and existing_objects == {"security_targets", "scans_by_target"}
):
validated_workspace = (
"workspace.target_path_unvalidated = 0 AND "
if "target_path_unvalidated" in workspace_columns
else ""
)
needs_backfill = bool(
connection.execute(
f"""
SELECT EXISTS (
SELECT 1 FROM workspaces AS workspace
WHERE ({validated_workspace}workspace.target_id IS NULL
AND workspace.target_path IS NOT NULL)
OR (
workspace.target_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM security_targets
WHERE security_targets.id = workspace.target_id
)
)
) OR EXISTS (
SELECT 1 FROM scans AS scan
WHERE scan.target_id IS NULL
OR NOT EXISTS (
SELECT 1 FROM security_targets
WHERE security_targets.id = scan.target_id
)
)
"""
).fetchone()[0]
)
if not needs_backfill:
return False
else:
repair_schema(connection, 16)
for table in ("workspaces", "scans"):
connection.execute(
f"""
UPDATE {table}
SET target_id = NULL
WHERE target_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM security_targets
WHERE security_targets.id = {table}.target_id
)
"""
)
return True
def repair_repository_identity_migration(connection: sqlite3.Connection) -> bool:
columns = {row["name"] for row in connection.execute("PRAGMA table_info(security_targets)")}
index_exists = connection.execute(
"SELECT 1 FROM sqlite_master WHERE type = 'index' "
"AND name = 'security_targets_by_repository_identity'"
).fetchone()
identity_changed = "repository_identity" not in columns or index_exists is None
if (
"repository_identity" in columns
and connection.execute(
"SELECT 1 FROM schema_migrations WHERE version = 67 AND name = ?",
("persist repository identities",),
).fetchone()
is not None
and not any(
row["name"] == "repository_generation"
for row in connection.execute("PRAGMA table_info(scans)")
)
):
from workbench_target_state import normalize_pre_release_repository_identities
normalize_pre_release_repository_identities(connection)
migration_sql = next(sql for version, _, sql in MIGRATIONS if version == 67)
for statement in sql_statements(migration_sql):
if statement.startswith(("ALTER TABLE security_targets", "ALTER TABLE scans")):
add_migration_column(connection, statement)
continue
for prefix in ("CREATE UNIQUE INDEX ", "CREATE INDEX ", "CREATE TRIGGER "):
if statement.startswith(prefix):
statement = statement.replace(prefix, f"{prefix}IF NOT EXISTS ", 1)
break
connection.execute(statement)
from workbench_scan_history import _scan_completion_order
if connection.execute(
"SELECT 1 FROM scans WHERE status = 'complete' AND completion_sequence IS NULL LIMIT 1"
).fetchone():
# SQL preserves the recorded sequence. Both projections omit it so the merge
# compares each pending row with the timestamp of the next recorded completion.
recorded = connection.execute(
"SELECT id, started_at, completed_at FROM scans "
"WHERE status = 'complete' AND completion_sequence IS NOT NULL "
"ORDER BY completion_sequence"
).fetchall()
unsequenced = connection.execute(
"SELECT id, started_at, completed_at FROM scans "
"WHERE status = 'complete' AND completion_sequence IS NULL"
).fetchall()
pending = iter(sorted(unsequenced, key=_scan_completion_order))
candidate = next(pending, None)
completed = []
for scan in recorded:
while candidate is not None and _scan_completion_order(
candidate
) <= _scan_completion_order(scan):
completed.append(candidate)
candidate = next(pending, None)
completed.append(scan)
if candidate is not None:
completed.append(candidate)
completed.extend(pending)
connection.execute("UPDATE scans SET completion_sequence = NULL WHERE status = 'complete'")
sequence = connection.execute(
"SELECT COALESCE(MAX(completion_sequence), 0) FROM scans"
).fetchone()[0]
for scan in completed:
sequence += 1
connection.execute(
"UPDATE scans SET completion_sequence = ? WHERE id = ?",
(sequence, scan["id"]),
)
return identity_changed
def repair_schema(connection: sqlite3.Connection, version: int) -> None:
for statement in _STATEMENTS[version]:
apply_repair_statement(connection, statement)
def apply_repair_statement(connection: sqlite3.Connection, statement: str) -> None:
if statement.startswith("ALTER TABLE "):
add_migration_column(connection, statement)
return
for prefix in ("CREATE UNIQUE INDEX ", "CREATE INDEX ", "CREATE TABLE "):
if statement.startswith(prefix):
statement = statement.replace(prefix, f"{prefix}IF NOT EXISTS ", 1)
break
connection.execute(statement)
def add_migration_column(connection: sqlite3.Connection, statement: str) -> bool:
_, _, table, _, _, column, definition = statement.removesuffix(";").split(None, 6)
columns = {row["name"] for row in connection.execute(f"PRAGMA table_info({table})")}
if column in columns:
return False
# Preserve the compact declarations used by historical repairs, including CHECK errors.
definition = " ".join(definition.split()).replace("( ", "(").replace(" )", ")")
connection.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")
return True
def sql_statements(script: str) -> list[str]:
statements: list[str] = []
buffer = ""
for line in script.splitlines():
buffer = f"{buffer}\n{line}".strip()
if sqlite3.complete_statement(buffer):
statements.append(buffer)
buffer = ""
if buffer:
raise ValueError("Incomplete SQLite migration statement.")
return statements
if __name__ == "__main__":
argparse.ArgumentParser(description=__doc__).parse_args()
SHA-256: 08d7451b45ac74c211ad1be360854f12719cb02e876ca9ed03274c9e1315044c