Your First Diagnostic Query — From Concept to Your Own Schema

The previous lesson showed you the templates — zero-rate, percentile, clearance. Now apply them to a schema you have never seen before.

This is where most practitioners stall. The templates make sense against the agent_signals table from the Agent Framework case. But when you face a fresh codebase — your own datastore, a system you are debugging for the first time — the template does not automatically translate. The discipline does.

The Gap This Lesson Bridges

The evidence-first track has covered:

• The “Pull the Data First” lesson — pull the data before designing the fix
• The previous lesson — SQL templates for hypothesis-driven queries
• The “Your First Diagnostic Query” lesson ← you are here: apply the loop to your own schema
• The next lesson — multi-checkpoint verification across independent sources
• The “Screenshot as Forensic Evidence” lesson — screenshot as forensic evidence

The "Multi-Checkpoint Verification" and "Screenshot as Forensic Evidence" lessons assume you already know which query to run. This lesson is the connective tissue: how you get from "I have a symptom" to "I have a query that answers a binary question" when the schema is unfamiliar.

The Four-Step Loop

The loop is the same regardless of domain. What changes is the schema introspection you do at step 02.

The most important constraint is step 01 before step 03. You must write the hypothesis before you write the query. If you write the query first, you will unconsciously select what you wanted to see. The hypothesis is the contract — the query either answers it or it does not.

Step 02 in Practice: Schema Mapping

When you encounter a new schema, do three things before touching the keyboard:

1. Name the symptom in metric terms. "The API is slow" is not a metric. "The 95th percentile response time for the POST /orders endpoint has increased" is a metric. The more specific the symptom, the easier the schema mapping.

2. Identify the table. Ask: which table records events or measurements related to this symptom? Usually one table is obvious from the name — api_requests, inference_logs, query_log. If no table is obvious, run \dt (PostgreSQL), SHOW TABLES (MySQL), or .tables (SQLite) and scan names for 30 seconds. Pick the best candidate.

3. Name exactly two columns. One column partitions the data (it goes in GROUP BY or FILTER WHERE). One column carries the signal (it goes in COUNT, AVG, percentile_cont). Write them down before opening the query editor.

-- Schema introspection: 30 seconds maximum
-- Table: api_requests
-- Partition column: client_id    (who is making requests?)
-- Signal column:    status_code  (are they failing?)

-- Hypothesis: one client is driving the bulk of 4xx/5xx errors.
-- Metric: error_rate per client_id
-- Threshold: > 0.50 on at least 10 requests

SELECT
  client_id,
  COUNT(*) FILTER (WHERE status_code >= 400) * 1.0 / COUNT(*) AS error_rate,
  COUNT(*) AS total
FROM api_requests
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY client_id
ORDER BY error_rate DESC
LIMIT 5;

One query. Five rows maximum. The result either shows a client with error_rate > 0.50 or it does not. The hypothesis is either confirmed or falsified in the time it takes to read five rows.

The Generic Schema Pattern

For any table you have never seen before, this is the full schema-to-query workflow in four minutes:

-- Step 1: Know your table shape (30 seconds)
PRAGMA table_info(api_requests);         -- SQLite
-- or: \d api_requests (PostgreSQL)
-- or: DESCRIBE api_requests (MySQL)

-- Step 2: Know your data volume and time range (30 seconds)
SELECT COUNT(*), MIN(created_at), MAX(created_at)
FROM api_requests;

-- Step 3: Write the hypothesis-driven aggregate (2 minutes)
-- Template: aggregate(signal_column) [FILTER condition] GROUP BY partition_column
SELECT
  client_id,
  COUNT(*) FILTER (WHERE status_code >= 400) * 1.0 / COUNT(*) AS error_rate,
  COUNT(*) AS total
FROM api_requests
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY client_id
ORDER BY error_rate DESC
LIMIT 5;

-- Step 4: Read the verdict (30 seconds)
-- If top row error_rate > 0.50 AND total >= 10 → CONFIRMED
-- Otherwise → FALSIFIED, update hypothesis, repeat

Beyond the Wallet: Four Domains, One Loop

The next two lessons (Multi-Checkpoint Verification and Screenshot as Forensic Evidence) will use a Polymarket wallet mismatch as their worked example. That domain — on-chain balances, derived EOAs, CLOB endpoints — is specific to trading infrastructure. But the loop you just learned is not bound to it. Here are four domains that follow the identical pattern:

The scoring system example maps directly to the previous lesson's agent_signals table. The API rate-limiter and ML inference examples use different tables and column names but the same aggregate patterns: zero-rate, error-rate, percentile. The DB latency example uses AVG and GROUP BY on a query_fingerprint column.

Notice what does NOT appear in any of these examples: SELECT *, a raw log dump, or a free-text search. Every example is a narrow aggregate that compresses a potentially large table into 1 to 5 rows.

When the First Hypothesis is Falsified

Falsification is not failure. It is the most productive result a diagnostic query can return because it eliminates a candidate and forces a more specific hypothesis.

Example: API rate-limiter, hypothesis falsified

You hypothesize that one client_id is driving failures. The query shows all clients have error_rate < 0.10. Hypothesis falsified. Update your mental model: the problem is not client-specific. What else could explain errors?

New hypothesis: a specific endpoint has a much higher error rate than others.

-- New hypothesis: one endpoint is responsible for the failures
SELECT
  endpoint,
  COUNT(*) FILTER (WHERE status_code >= 400) * 1.0 / COUNT(*) AS error_rate,
  COUNT(*) AS total
FROM api_requests
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY endpoint
ORDER BY error_rate DESC
LIMIT 5;

Same table. Different partition column. Two minutes of work. The second query either confirms the new hypothesis or sends you to a third. Each iteration narrows the search space until the diagnosis is inescapable.

Applying This to Your Own System

Before running the BuildChallenge below, do this against a real system you have access to:

1. Pick a symptom you have seen recently — anything from "the build is slow" to "users are getting logged out randomly."
2. Name one table in the relevant datastore.
3. Name exactly two columns — the partition column and the signal column.
4. Write the hypothesis sentence.
5. Write the query. Run it.

The goal is not to fix anything. The goal is to complete the loop once against a real schema before the discipline becomes abstract. One real run cements what ten examples cannot.