05 — Timestamp-Based Projection-Lag Detector (Objective D)

Problem

The inherited projection_lag detector reused the structural reconcile flag (divergent/no_label_rule) — not time-based. After Objective A resolved those, it correctly drops to 0 but never measured lag.

Timestamp availability (verified)

• governance: axis_assignment.assigned_at = timestamptz NOT NULL, 0 nulls.
• operational: entity_labels.assigned_at = timestamp (UTC), 0 nulls / 803,955 rows.
• linkage: entity_labels.rule_id → label_rules.id; collection rules carry condition->>'collection'.

Detector (sql/D_projection_lag_timing.sql)

v_projection_lag_timing, scoped to collections that HAVE an active collection label_rule (distinct from no_label_rule). Per topic assignment: proj_at = max(entity_labels.assigned_at) over that collection's rule-produced labels; lag_flag = never_projected (proj_at null / label_rows 0) | lagging (gov_at > proj_at UTC) | fresh; lag_days = gov_at::date - proj_at::date. Excludes accept_projection_pending/quarantine_hold decisions. Integrated as detector projection_lag_timing in v_drift_finding (never_projected→warning; lagging>30d→warning; else info) and registered in v_drift_event_map.

Test result (live)

agents/ai-ops lagging 75d (8 labels) warning; modules/workflow lagging 66d (6) warning; tasks/ai-ops lagging 5d (13) info; workflows/workflow never_projected (0 labels) warning. 4 findings, 0 critical. Genuine lag: topic assigned 2026-06, labels last projected 2026-03 (or never for workflows).

Idempotency

fn_governance_scan ×3 → open=38, new=0/resolved=0 on passes 2-3. First post-change scan resolved 7 (6 structural projection_lag + 1 coverage_rule_missing) and added 4 timing findings.

Fallback (documented, not needed)

If a future projection target lacks timestamps: require projected_at/updated_at + rule_id linkage. Fallback detector compares existence/version (never_projected when rule produces 0 rows; drift_suspected when assignment revision advanced past last projection batch). Weaker (no lag magnitude) but sound.