blind-db-debugging

Blind DB Debugging

Overview

Diagnose database issues (missing or incorrect data) via SQL row counts without ever seeing actual data values. The agent generates hypotheses and queries; the user runs them and reports only row counts back.

Phase 0 -- Gather Context

Before writing any query:

1. Understand the expected output -- what the user sees (or should see) in the final app or product. Describe it at the product level, not the database level.
2. Trace back through the code -- follow the call chain from the UI or API response back to the database: view -> controller/resolver -> service -> query. Identify which queries, joins, or filters are responsible for producing that output.
3. Ask only what the code cannot answer:
   • Scope -- one record, a subset, or all records?
   • When it started -- after a deploy, migration, data import, or unknown?
   • A working example (optional) -- one ID or key that behaves correctly; enables control group queries.

Do not skip this phase. Hypotheses must be grounded in the actual code path, not guesses about the schema.

Phase 1 -- Generate Hypotheses

Produce 3-5 hypotheses derived strictly from the context provided. Each hypothesis must be a falsifiable claim about why the data is wrong or missing -- grounded in what is known about the schema, the recent changes, and the scope of the problem. Label them A, B, C...

Do not use a generic checklist. Every hypothesis must earn its place by being plausible given the specific context gathered in Phase 0.

Phase 2 -- Write Queries

Deliver all queries in one copy-paste block. Each query must follow this format:

-- HYPOTHESIS A: <one-line description of what could be wrong>
-- PASS: N rows (what a good result looks like)
-- FAIL: 0 rows (what a bad result looks like)
SELECT meaningful, columns
FROM ...
WHERE ...
LIMIT 20;

Control group (optional): If a working example was provided, add a sibling query per hypothesis running the same filter against the known-working record. If the working record also returns 0 rows, the issue is in the query logic, not the data.

-- HYPOTHESIS A (broken): ...
SELECT id, status FROM orders WHERE id = :broken_id AND status = 'active' LIMIT 5;

-- HYPOTHESIS A (control): same filter on known-working record
SELECT id, status FROM orders WHERE id = :working_id AND status = 'active' LIMIT 5;

Phase 3 -- Evaluate

The user reports only row counts. Evaluate each hypothesis:

Result	Meaning
CONFIRMED	Broken = 0 rows (and control = N rows if available). This is the cause.
REJECTED	Count matches what a healthy state would look like. Rule this out.
INCONCLUSIVE	Counts are unexpected or ambiguous. Refine the query or move to the next layer.

On INCONCLUSIVE: do not propose a fix. Instead, narrow the query (remove one WHERE clause at a time) to isolate which condition is filtering out the row.

Phase 4 -- Fix and Verify

• Never propose a fix until at least one hypothesis is CONFIRMED.
• After applying the fix, deliver a verification query block using the same format.
• The fix is proven only when the verification block returns the expected row count.

Phase 5 -- Iteration

If all hypotheses are REJECTED, move one layer deeper:

source / seed data
    -> config / lookup tables
        -> mapping / translation tables
            -> join / membership tables
                -> aggregated / denormalized tables
                    -> application-level read (ORM, cache, API filter)

Generate a new batch of hypotheses from the next layer. Repeat until CONFIRMED or the full stack is exhausted.

Rules

• Never use COUNT(*) -- it always returns 1 row; use a filtered SELECT instead.
• Never use SELECT 1 -- use meaningful columns so the user can validate the data.
• Add LIMIT when the WHERE is not selective -- if the query filters by a specific ID or a small known set, LIMIT is unnecessary. If the query touches potentially large tables (events, logs, status records) without a tight filter, add LIMIT 20. If uncertain, add it with a note: -- NOTE: remove LIMIT if the WHERE clause is already selective enough.
• Use a control group when available -- run the same filter against a known-working record to distinguish data issues from query logic issues.
• One block per round -- deliver all queries together so the user runs them in one pass.
• Never assume data is correct -- verify every link in the chain independently.