[Senate] Orphan-version detector - broken parent_version_id and is_latest sweep open

Goal

The artifacts table encodes versioning via two columns: parent_version_id
(self-FK, currently NOT enforced as an FK in PG — \d artifacts shows no
foreign-key constraint on it) and is_latest int (1 == this row is the
current head of its lineage). Because Postgres doesn't enforce the parent
FK and writers update is_latest manually, the table accumulates two
classes of corruption:

• Orphan parents: parent_version_id references a row that has been

deleted or that never existed (e.g., a backfill landed v2 with the
wrong UUID).

• is_latest drift: a lineage chain has 0 or ≥ 2 rows with

is_latest=1, breaking every "current version of artifact X" query.

This task ships a periodic sweep that finds and fixes both — read-only by
default, opt-in to repair — and a daily report that surfaces the count of
each problem class as a governance metric.

Acceptance Criteria

pure-query functions (no writes):
- find_orphan_parents(db) -> list[{artifact_id, missing_parent_id}]
runs SELECT a.id, a.parent_version_id FROM artifacts a
LEFT JOIN artifacts p ON p.id = a.parent_version_id
WHERE a.parent_version_id IS NOT NULL AND p.id IS NULL.
- find_lineages_without_head(db) -> list[{root_id, version_count}]:
groups by lineage (computed via recursive CTE following
parent_version_id to the root), reports lineages with
SUM(is_latest) = 0.
- find_lineages_with_multiple_heads(db) -> list[{root_id,
head_ids: list, head_count}]: same lineage grouping, reports
SUM(is_latest) > 1. scidex/senate/version_integrity_repair.py, opt-in flag):
- repair_orphan_parents(db, action: 'null'|'mark_root', dry_run):
either NULLs the bad FK or sets the row as a new root version
(depending on policy); writes artifact_provenance with
action_kind='gate_decision' (already in CHECK) +
justification text.
- repair_multiple_heads(db, dry_run): keeps the row with the
max version_number, demotes the rest to is_latest=0.
- repair_no_head(db, dry_run): promotes the max version_number
row to is_latest=1. [--dry-run] [--max-fix N]: prints per-class counts, fixes if
--repair, caps at --max-fix (default 100) per run to keep
blast radius bounded. scidex/senate/scheduled_tasks.py in read-only (audit) mode;
writes one row per problem class to the
governance_metrics_snapshots table introduced by
q-gov-metrics-dashboard so trends show up on the dashboard. or > 10 multiple-head lineages, write a senate_alerts row. latest snapshot per class; POST /api/senate/version_integrity/repair
(admin-only) triggers a repair run. - Synthetic 4-row lineage with FK pointing to deleted row →
find_orphan_parents returns 1 row; repair_orphan_parents
(action='null') clears the FK.
- 3-row lineage with two is_latest=1 rows → repair keeps max
version_number.
- 3-row lineage with zero is_latest=1 → repair promotes max.
- Dry-run never writes (assertion via SELECT count(*) FROM
edit_history before/after).

Approach

Write the recursive CTE that buckets artifacts by lineage root —

needs handling for cycles (defensive WITH RECURSIVE
lineage(id, root, depth) AS (...) WHERE depth < 100).

Implement the three query functions; unit test against synthetic

data.

Implement repair functions; each one transactional with savepoint;

each one writes provenance.

CLI + scheduled task + API.

Smoke run on production in --dry-run; record counts in Work Log.

Commit.

Dependencies

• q-gov-metrics-dashboard — emits snapshot rows for the trend chart.

Dependents

• Future: a quest for "recompute citation_count / embed_count /

derivation_count" can reuse the lineage CTE built here.

Work Log