A rewrite that passed every gate and still moved the number
Imagine a team approving an agent-drafted rewrite of a fourteen-table revenue query. The parser gate passed, the read-only guard passed, and the dashboard still drifted by three percent overnight. The defect was one LEFT JOIN that the draft had promoted into an inner join, which is invisible to syntax checks and obvious to anyone comparing rows.
That gap defines the real shipping problem. Getting an agent to produce syntactically valid SQL is a solved loop; proving the draft returns the same answers as the query it replaces is the part that still causes incidents. Two credible engineering positions exist for closing it, and they disagree about where equivalence should be measured.
Position A: shadow replay proves equivalence before any user sees it
The first camp argues that correctness is a property of results, so you should capture real queries, replay them against a disposable copy of the data, and diff the outputs offline. Nothing reaches production until the diff is clean, which makes the risk surface small and the review artifact auditable.
- Capture a workload sample with normalized parameters instead of literal values.
- Restore a snapshot or seed a disposable schema with representative data.
- Execute the baseline query and the agent draft under identical session settings.
- Normalize both result sets, then diff them with explicit tolerance rules.
- Block the draft when the diff exceeds the threshold, and store the report as evidence.
The strength of this position is repeatability. A failing case becomes a regression test you can rerun on every prompt change, and reviewers can read the diff without touching production. The weakness is fidelity: a snapshot goes stale, and replay never exercises concurrency, lock contention, or the query plans your real data volume produces.
Position B: a read-only canary measures equivalence on live data
The second camp argues that snapshots lie, because correctness depends on data the snapshot does not have. Instead, route a sampled fraction of real read traffic to the agent draft on a replica, compare the paired results in flight, and alert on divergence.
- Grant the drafting path a role with read-only transactions and a hard statement timeout.
- Sample by query fingerprint, not by user, so the same query stays in or out consistently.
- Run baseline and draft for the sampled requests and compare normalized rows.
- Emit a divergence metric per fingerprint and trip a circuit breaker on threshold breach.
- Promote the draft to full traffic only after a clean window you define in advance.
The strength here is realism, since skew, nulls, and timezone handling all show up exactly as production presents them. The weakness is noise: partial failures, transient replica lag, and nondeterministic ordering generate diffs that are not semantic bugs, and someone has to triage them.
The artifact: a replay harness you can build in an afternoon
The following sketch is a harness, not a finished tool. It assumes read-only queries, a disposable database, and a normalization policy you have already agreed on with the people who own the numbers.
# replay.py - sketch; normalize() must match your domain rules
import hashlib, json
NORMALIZE_DECIMALS = 6
def fingerprint(sql: str) -> str:
return hashlib.sha256(" ".join(sql.split()).encode()).hexdigest()[:16]
def normalize(rows):
out = []
for row in rows:
keys = [row[k] for k in row.keys()]
norm = []
for v in keys:
if v is None:
norm.append("\x00NULL")
elif isinstance(v, float):
norm.append(round(v, NORMALIZE_DECIMALS))
else:
norm.append(str(v))
out.append(tuple(norm))
return sorted(out)
def run(conn, sql, params, timeout_ms=5000):
with conn.cursor() as cur:
cur.execute("SET TRANSACTION READ ONLY")
cur.execute(f"SET LOCAL statement_timeout = {timeout_ms}")
cur.execute(sql, params)
return normalize(cur.fetchall())
def replay(cases, baseline_conn, draft_conn, max_diff_rows=0):
report = {"pass": [], "fail": [], "error": []}
for case in cases:
fid = fingerprint(case["baseline_sql"])
try:
base = run(baseline_conn, case["baseline_sql"], case["params"])
draft = run(draft_conn, case["draft_sql"], case["params"])
except Exception as exc:
report["error"].append({"id": fid, "error": str(exc)[:200]})
continue
delta = len(set(base).symmetric_difference(set(draft)))
bucket = "pass" if delta <= max_diff_rows else "fail"
report[bucket].append({"id": fid, "row_delta": delta})
return report
if __name__ == "__main__":
cases = [json.loads(line) for line in open("cases.jsonl")]
print(json.dumps(replay(cases, None, None), indent=2))
The harness is deliberately blunt, because a clever comparator hides the disagreements you most need to see. Pass real connections in place of None, keep cases.jsonl under version control, and treat every fail entry as a future test rather than a one-off investigation.
Normalization rules are the actual engineering decision
Most debate about replay versus canary is really a debate about what counts as equal. Row order without an ORDER BY is not a contract, so sort before comparing. Floating point sums need a decimal tolerance, and timestamps need an explicit timezone policy rather than a lucky server default.
Write those three rules down before you write the harness. Teams that skip this step spend weeks arguing about diffs that are artifacts of comparison, and the agent gets blamed for noise the harness created.
Where a free server and free model access change the math
Replay is cheap to describe and expensive to run, because it multiplies queries by two and adds a drafting call for every captured case. Running the drafting loop and the harness on the same free server keeps the captured cases, the draft outputs, and the diff report in one place, which is the difference between a ritual and a routine. MonkeyCode's free model access and free server option are the parts that make that loop affordable for a small team; check the project's current terms for limits, since free-tier quotas change and restating a number here would age badly.
Disclosure: This article was prepared as part of MonkeyCode's product outreach.
The decision rule
| Signal | Shadow replay first | Read-only canary first |
|---|---|---|
| Workload volume | High, repetitive fingerprints | Low volume, long tail of one-offs |
| Data volatility | Snapshot can be refreshed nightly | Business day changes the answer |
| Schema churn | Stable tables, few migrations | Frequent migrations and renames |
| On-call maturity | No one to triage live diffs | Team already triages alerts |
| Blast radius | Every draft costs a review | Only sampled requests are exposed |
Use replay when you need an auditable artifact and cannot staff live triage. Use a canary when correctness is data-dependent and your alerting is mature enough to absorb noise. Most teams I would advise run replay as the gate and a small canary as the confirmation, in that order.
Limitations and who should skip this
Neither approach covers writes, so do not extend this pattern to UPDATE or DELETE without a separate rollback design. Replay cannot reproduce lock waits, and a canary cannot prove coverage of paths that receive no traffic during the window.
Skip both if you have no disposable environment or no agreed definition of equivalence. A harness built on an undefined contract produces confident reports that mean nothing, which is worse than shipping behind a manual review.
Start with the diff, not the demo
Pick twenty captured queries, normalize them honestly, and see what your current drafts actually return. If the diffs are clean, you have a regression suite; if they are not, you have found the incident before your users did.
Top comments (0)