ops-sql-forensics

Incident-driven SQL. A synthetic operations database for a SaaS integration and sync platform, seeded with four deliberate, hidden incidents, plus a set of diagnostic queries that walk from a customer complaint to a root cause the way an on-call engineer actually would.

What it is in one line: a small, self-contained study of using SQL as a debugging tool, with a test suite that proves each query actually catches the incident it claims to.

What it is NOT: a real product, a real customer's data, or a benchmark. Every customer, job, message, and event is generated by generate.py from a fixed random seed. The incidents are planted on purpose so the queries have something true to find.

---

The 60-second version

Imagine a product that syncs data for its customers overnight and fans events out to their webhook endpoints. When something breaks, a customer does not file a tidy bug report. They say "we stopped getting data on Tuesday" or "our totals look doubled." The job is to turn that vague complaint into a specific, provable root cause.

This repo simulates that. It builds a database with twelve customers over eight weeks of normal activity, then hides four real-world incidents inside it:

Incident	What a customer would notice
Silent sync stall	"No new data has arrived in over a week, but nothing looks broken."
Duplicate-delivery burst	"Some events landed in our system two or three times."
Retry storm	"Your service was hammering our endpoint for a couple of hours."
Slow degradation	"Our nightly sync feels slower lately, but there is no outage."

The queries/ folder holds twelve SQL files. Each one opens with the incident question it answers, in plain language, and each is a rung on a ladder that climbs from a one-line WHERE filter up to window functions, gaps-and-islands, cohort comparisons, and multi-step correlations.

The point of the tests is honesty: it is easy to write a query that reads like it finds a problem. tests/test_queries.py runs each real query file against the generated data and asserts it returns the exact planted customer, day, or event, so "this query finds the incident" is a checked fact, not a claim.

---

The four incidents, as investigations

Each incident reads as symptom -> query -> finding -> root cause.

1. The silent sync stall

Symptom. A customer says no new data has landed in over a week, but the sync dashboard shows their jobs as running. Nothing is red.

Investigation.

• q01_jobs_that_never_finished.sql (a WHERE filter): which recent jobs started but have no completion timestamp? All of them belong to one customer.
• q05_last_successful_sync_per_customer.sql (per-group MAX + staleness flag): that customer's last successful sync is frozen days in the past while the whole fleet is current.
• q06_stall_streak_gaps_and_islands.sql (gaps-and-islands): the failure is one unbroken streak of "stalled" days with a clear onset date and length.
• q02_message_throughput_by_day.sql (GROUP BY + date bucket + pivot): the downstream damage. From the stall onset, the customer's outbound messages drop to zero and their inbound messages pile up unprocessed.
• q11_sync_success_rate_cohort.sql (cohort comparison): stepping back, this customer is the fleet's one reliability outlier, well below the fleet average.
• q12_missed_messages_root_cause.sql (multi-CTE correlation): the flagship. It lines up the day messages started piling up against the day syncs stopped completing and shows they happen back to back.

Finding. Jobs still start every night, so every green-light check passes, but they never finish, so no data moves and the message pipeline that depends on them silently backs up.

Root cause (in the simulation). A job that begins and never completes leaves its finished_at NULL and its status stalled. The lesson the queries teach is that "is it running?" and "is it working?" are different questions, and only the second one is answered by finished_at and by what happens downstream.

2. The duplicate-delivery burst

Symptom. A customer's downstream system recorded some events two or three times, inflating their totals.

Investigation.

• q03_duplicate_webhook_deliveries.sql (GROUP BY ... HAVING COUNT(*) > 1): which delivered events were sent more than once, and how many times each.
• q04_duplicate_burst_window.sql (COUNT(*) - COUNT(DISTINCT ...)): the number of redundant deliveries per customer per day pinpoints one customer on one day.

Finding. A cluster of events was each delivered several times within a single day for a single customer, while every other event across the fleet was delivered exactly once.

Root cause (in the simulation). At-least-once delivery without idempotency on the consumer's side. The fix a support engineer would recommend is deduplication by event id, which is exactly the guarantee real event pipelines lean on.

3. The retry storm

Symptom. Alerting shows the webhook worker hammering one customer's endpoint for a couple of hours.

Investigation.

• q07_retry_storm_events.sql (GROUP BY event + MAX(attempt) threshold): which events climbed to a high attempt count, and did they ever succeed?
• q08_api_error_spike_by_hour.sql (strftime hourly buckets): what error dominated, and in which hour? A two-hour spike of upstream 503s towers over the quiet baseline.

Finding. A handful of events were retried up to six times inside a two-hour window, correlated with a burst of 503 UPSTREAM_UNAVAILABLE errors, then eventually delivered.

Root cause (in the simulation). A transient upstream outage drove the retry loop. The error code and the retry escalation together show it was upstream, not the customer's endpoint, which is the distinction a good escalation summary draws.

4. The slow degradation

Symptom. A customer says their nightly sync "feels slower lately," with no outage and nothing in the error logs.

Investigation.

• q09_weekly_sync_duration_trend.sql (julianday durations averaged per ISO week): the raw shape. One customer's weekly average duration climbs steadily while the fleet holds flat.
• q10_degradation_week_over_week.sql (LAG window function over the weekly averages): turn the shape into a ranking. The degrading customer rose almost every week and grew by a wide margin, sorting far above everyone else.

Finding. Gradual, monotonic growth in sync duration for one customer, the kind of drift that never trips a threshold alert but ends in an outage if ignored.

Root cause (in the simulation). A steadily rising per-week duration mean. The lesson is that the most dangerous regressions are the ones with no single bad moment to alert on, and that trend queries, not threshold alerts, are what catch them.

---

Query index

File	Technique	Incident it finds
q01_jobs_that_never_finished.sql	WHERE filter on NULL + recent window	silent stall
q02_message_throughput_by_day.sql	GROUP BY, date bucket, CASE pivot	silent stall (downstream)
q03_duplicate_webhook_deliveries.sql	GROUP BY ... HAVING COUNT(*) > 1	duplicate burst
q04_duplicate_burst_window.sql	COUNT(*) - COUNT(DISTINCT ...)	duplicate burst (localize)
q05_last_successful_sync_per_customer.sql	per-group MAX + scalar subquery	silent stall (fleet view)
q06_stall_streak_gaps_and_islands.sql	gaps-and-islands (two ROW_NUMBERs)	silent stall (onset + length)
q07_retry_storm_events.sql	GROUP BY + MAX(attempt) + HAVING	retry storm
q08_api_error_spike_by_hour.sql	strftime hourly time-bucketing	retry storm (root cause)
q09_weekly_sync_duration_trend.sql	julianday duration + weekly AVG	slow degradation (shape)
q10_degradation_week_over_week.sql	LAG window function over weeks	slow degradation (rank)
q11_sync_success_rate_cohort.sql	cohort comparison via CTEs	silent stall (outlier)
q12_missed_messages_root_cause.sql	multi-CTE correlation	silent stall (root cause)

---

For engineers

Schema

Five tables model the operational surface an on-call engineer queries during an incident (schema.sql):

• customers: twelve accounts with a plan and region.
• sync_jobs: one nightly job per customer per day, with started_at, finished_at (NULL when a job never completes), and a status.
• messages: inbound and outbound messages, each processed, pending, or failed, optionally tied to the sync job that day.
• webhook_deliveries: per-event delivery attempts, with an attempt counter and a status, so both duplicates (same event delivered twice) and retries (same event, escalating attempt numbers) are representable.
• api_errors: a sparse error log with status codes, error codes, and latency.

Timestamps are ISO-8601 text so SQLite's DATE(), strftime(), and julianday() work directly. Indexes cover the columns the queries filter and group on.

The generator is the answer key

generate.py is deterministic: the same seed produces a byte-identical database (there is a test for exactly this). It lays down a plausible baseline of daily activity and then plants each incident against a named constant, for example STALL_CUSTOMER = "c007" and STALL_DATE = date(2025, 6, 6). Those same constants are imported by the tests, so the queries are graded against the answer key the generator wrote, never against hand-copied expected values that could drift.

Event ids are handed out by a global counter so the only colliding event ids in the whole dataset are the planted duplicates. That is what lets q03 assert an exact count with no false positives.

The tests prove the queries work, not just read well

tests/test_queries.py runs each real .sql file (the same file a human would open) against the generated database and asserts on the planted answer:

• the duplicate finder returns exactly the ten planted events, all for the right customer, each delivered exactly three times;
• the gaps-and-islands query returns a single 24-day stall streak starting on the planted onset date;
• the degradation ranking puts the right customer first, rising in at least six of eight weeks, with the runner-up's growth an order of magnitude smaller;
• and so on for all twelve.

This is the difference between a query that looks diagnostic and one that is. A SELECT ... GROUP BY ... HAVING reads convincingly whether or not it is correct; the assertion that it returns customer c003 on 2025-06-10 and nothing else is what makes it trustworthy.

Run it

python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"

python generate.py         # build data/ops.db and print row counts
pytest -q                  # 13 passed
ruff check .               # clean

pytest builds its own fresh database in a temp directory, so you do not need to run generate.py first for the tests to pass. Running it is only for exploring the data by hand:

sqlite3 data/ops.db < queries/q06_stall_streak_gaps_and_islands.sql

Honest scope and non-claims

• Synthetic, self-authored data throughout. No real product, customer, or operational data is involved, and there is no affiliation with any company.
• The four incidents are planted, not discovered. This demonstrates the query patterns that would catch them in real data; it is not evidence about how often they occur in the wild.
• SQLite is used for portability (no server, no container). The queries are written in portable SQL, but the exact strftime/julianday spellings are SQLite's; a Postgres port would swap those for date_trunc and interval math.
• Twelve customers over eight weeks is a small dataset chosen for readability, not a scale or performance claim.

License

MIT. See LICENSE.