data-integrity-investigation

name

data-integrity-investigation

description

Investigate data-integrity / "why does this data look wrong" questions in the Commons codebase (mismatched IDs, orphaned rows, cross-entity divergence, unexpected duplicates, values that don't agree across tables). Use when a ticket or user asks WHY a piece of data is in a surprising state and you must trace it to a root cause through code + production data. Pairs with the commons-sql-query skill for the SQL itself.

Data-Integrity Investigation

A discipline for finding the true root cause of a data anomaly without committing to a wrong one. The failure mode this skill prevents: confidently declaring a hypothesis "confirmed," then having it collapse when one already- knowable fact (a feature-flag mode, a runtime config, the actual patientId on the row) finally arrives. Slow is smooth; smooth is fast.

The method (in order)

1. Pin the symptom precisely

Restate the anomaly as concrete, checkable facts ("row X has patientId = A but patientEnrollmentId resolves to patient B"). Do not paraphrase the ticket — quote the exact IDs/columns. Note what you have not yet proven (e.g. "I have not confirmed which patientId the offending row actually carries").

2. Confirm runtime config BEFORE forming hypotheses

This is the step most often skipped, and the most decisive. Before reasoning about how a value got written, establish the environment it was written in:

• Feature-flag / switch modes. What mode does production run? (e.g. MemberModelV2Switch = OLD_ONLY / DUAL_SUPPORT / NEW_ONLY.) The same code path behaves differently per mode; the wrong assumption invalidates whole hypotheses.
• Which code is even live. A create-time write path is irrelevant if the flag that gates it is off in prod.
• Environment / data freshness. prod vs staging; live Postgres vs a dbt snapshot (base_commons.*).

If you cannot determine the mode from code/config, ask the user once — they usually know, and it is cheaper than chasing a dead hypothesis for 40 minutes. Grep for the switch/flag definition and its production value before guessing.

3. Enumerate EVERY write path for the suspect column

Dispatch sub-agents (explore) in parallel to find, for the column in question, every mechanism that can write it: create paths, update paths, decorators, backfill jobs, migrations, merge/move/migrator tooling, and any caller-supplied override. For each, note the precondition under which it runs (tie back to the runtime config from step 2 — cross out any path the live config disables).

4. Form hypotheses with explicit confidence + a disconfirming check

For each surviving hypothesis, write:

• a confidence label (e.g. low/medium/high) — and resist "high" before data;
• the single check that would DISCONFIRM it (the query whose result kills it);
• what each possible result would mean.

Never call a root cause "confirmed" until its disconfirming check has actually run and survived. Treat "this fits" as a hypothesis, not a verdict. Phrases like "confirmed beyond doubt" / "highest-probability mechanism" are banned until the disconfirming evidence is in hand.

5. Batch the SQL — minimize human round-trips

The user runs SQL themselves (see commons-sql-query). Each round-trip is slow, so bundle all of a round's queries into one copy-pasteable block: the rule-out queries AND the would-be confirmation query together, numbered, each with a one-line "what this result tells us." Do not dribble out one query, wait, then ask for the next. Anticipate the next branch and include its query now.

6. Re-derive, don't assume

When data returns, re-read what it actually proves before layering interpretation. A classic trap: assuming both anomalous rows share the expected patientId when the data never said so. If a result is surprising, suspect your assumption first.

7. Converge and write up

When one hypothesis survives every check and explains every observed fact (and the rivals are each killed by a specific piece of evidence), state the verdict with the kill-shot for each dead suspect. Cite file:line for the mechanism and the exact production rows/timestamps for the evidence. Note blast radius (is this one row, or a class of rows / tables?) and flag follow-ups without scope-creeping the current ticket.

Output shape for the verdict

• TL;DR — one paragraph: the mechanism, in plain terms.
• Suspects ruled out — each with the single fact that kills it.
• Confirmed root cause — the sequence of events, file:line cited.
• Production evidence — the rows/timestamps that prove it.
• Blast radius — scope (this row vs many tables), with a sizing query.
• Follow-ups (informational) — not implemented unless asked.

Pairs with

• commons-sql-query — for writing the actual copy-pasteable SQL (engine choice, base_commons vs federated, soft-delete filtering, schema discovery). Load it alongside this skill.
• ticket-writer — if the investigation spawns remediation tickets.

Anti-patterns (each seen cause a long detour)

• Reasoning about all flag modes without asking which one production runs.
• Declaring a hypothesis "confirmed" before the disconfirming query returns.
• Sending SQL one query per turn instead of a batched block.
• Interpreting a result past what it literally proves.