Cross-Service Reconciliation

On this page

Why

CRAIG’s services own their own Postgres databases (per the OpenStack-style independence pattern), but cross-service references travel as UUIDs in JSONB / FK-shaped columns rather than database-level foreign keys. A row in placement.placements with case_id = X is asserting that cases.cases.id = X exists, but Postgres can’t enforce that across DB boundaries.

Drift happens. Dual-writes, missed events, manual SQL fixups, partial restores, half-merged migrations — any of these can leave a row pointing at a target that no longer exists. Without a periodic eyes-on signal, that drift compounds silently.

cargo xtask reconcile (ADR-022 §D7 / platform-stabilization Step 11) is the eyes-on signal: a read-only walker that enumerates every documented cross-service UUID reference and reports the orphans. No --fix flag — orphans need human triage; auto-deletion would destroy evidence of the underlying drift.

Usage

$ cargo xtask reconcile
=== Cross-service reconciliation (ADR-022 §D7 / Step 11) ===
  OK   cases.reports.partner_id → security.partners.id | scanned=12 orphans=0
  OK   cases.report_persons.linked_by → (Keycloak sub) | scanned=0 orphans=0
  OK   placement.placements.case_id → cases.cases.id | scanned=18 orphans=0
  OK   placement.placements.child_id → cases.persons.id | scanned=18 orphans=0
  OK   financial.payments.case_id → cases.cases.id | scanned=46 orphans=0
  OK   financial.payments.placement_id → placement.placements.id | scanned=46 orphans=0
  OK   exchange.icpc_requests.case_id → cases.cases.id | scanned=1 orphans=0

  Report: test-results/reconciliation/2026-04-30.md

Exit code is non-zero when any orphan is found, so the tool is CI-safe.

Filtering

--scope defaults to all. Pass a comma-separated list of reference keys (the first column of the §D7 table — report_to_partner, placement_to_case, etc.) to walk a subset:

$ cargo xtask reconcile --scope placement_to_case,placement_to_child

Output path

--output <path> overrides the default location (test-results/reconciliation/<UTC-date>.md).

Reading the report

The markdown report has one section per reference. Each lists scanned-row count, orphan count, and a (source_id, missing_target_id) table for triage.

Triage flow per orphan:

  1. Reproduce in dev if possible — restore a prod snapshot or replicate the steps that produced the row.

  2. Find the producing event in the audit log (craig-security audit_log). The event payload + timestamp tell you whether the FK was wrong at write time or whether the target row was later deleted.

  3. Decide:

    • Target was deleted by an explicit operation: the orphan represents broken referential intent. Either restore the target or null the FK with operator approval.

    • Target was never written: the orphan is upstream-bug evidence. File an issue against the producing service.

    • Reference is stale by design (e.g. archived target retained by policy): the reference table in xtask/src/cmd/reconcile.rs should be updated to mark this case allow-listed, not the row deleted.

There is no "auto-fix" because each orphan’s correct resolution depends on context the tool cannot recover.

When to run

  • On demand, before a release, to catch drift before customers see it.

  • Weekly, scheduled in the GitLab triage pipeline (cron 0 14 * * 1 — Mondays 14:00 UTC, one hour after triage:stale). The job uploads the markdown report as an artifact.

  • Post-incident, after any operator-initiated SQL fixup against a production-shaped DB.

References tracked

The current §D7 table (live in xtask/src/cmd/reconcile.rs):

Key From To Notes

report_to_partner

cases.reports.partner_id

security.partners.id

Public-intake report ↔ partner identity

report_persons_linked_by

cases.report_persons.linked_by

(Keycloak sub)

External target — count only, no orphan check (Keycloak is not queried)

placement_to_case

placement.placements.case_id

cases.cases.id

The single most-load-bearing cross-service reference

placement_to_child

placement.placements.child_id

cases.persons.id

Child identity owned by cases; placements reference

payment_to_case

financial.payments.case_id

cases.cases.id

Placement-origin payments are allocated against a case; subsidy-origin rows (#1068) may have none (case_id nullable — the walker’s IS NOT NULL filter skips them by design)

payment_to_placement

financial.payments.placement_id

placement.placements.id

Placement-origin payments only — subsidy-origin rows (#1068) have a NULL placement_id by payments_origin_exclusive; the walker’s IS NOT NULL filter skips them by design

icpc_to_case

exchange.icpc_requests.case_id

cases.cases.id

ICPC request originates from a case

subsidy_agreement_to_case

financial.subsidy_agreements.case_id

cases.cases.id

Subsidy agreement’s owning case (#1067; existence-only — semantic consistency is the creating flows' job)

subsidy_agreement_to_child

financial.subsidy_agreements.child_id

cases.persons.id

The subsidized child

subsidy_party_to_person

financial.subsidy_agreement_parties.person_id

cases.persons.id

Agreement signatory (caregiver/co-caregiver)

subsidy_agreement_to_placement

financial.subsidy_agreements.placement_id

placement.placements.id

Anchoring placement (ERR evidence linkage)

Adding a new reference: append a row to the REFERENCES array in xtask/src/cmd/reconcile.rs, supply the source query + target table/column, and the test suite enforces the schema invariants (unique key; external targets have to_table = None).

Edit this page · latest