DEV Community: Theodore P.

Verification-first architecture for language-model repair pipelines.

Theodore P. — Thu, 27 Aug 2026 15:00:41 +0000

The Harness Is All You Need

Verification-first architecture for language-model repair pipelines.

A method for putting a language model inside a maintenance system without letting it decide anything.

Abstract

A large class of engineering maintenance work has the same shape. A small machine-readable rule, which locates or transforms a value inside an input the system does not control, silently stops producing correct output because the input changed. The rule is short, the failures are many, and a competent engineer can repair any single case in minutes once the evidence is in front of them. The cost is not difficulty. It is volume and triage.

This is an attractive target for a language model and a dangerous one. The model can propose a repair from evidence, but a wrong repair is worse than no repair, because it produces plausible output that no alarm fires on. The naive pipeline, which collects failures, shows them to a model and applies the answers, fails in three independent ways at once, and every one of those failures is invisible in aggregate metrics.

This document describes a harness that makes the approach work. It is derived from a production deployment that maintains roughly 1,400 such rules against inputs from as many independent external sources, covering about 1.6 million monitored items, and it has been checked against two further domains. The vocabulary is deliberately abstract so that the method can be mapped onto any problem with the same shape.

The central claim is measurable and, in that deployment, measured. The quality of such a system is governed by the oracle and by the evidence given to the generator, not by the capability of the generator. Tripling model size produced no usable improvement. Constraining the prompt and narrowing the evidence roughly doubled precision.

When this method applies
Terminology
Five invariants
Reference architecture
Stage A. Attribution
Stage B. Compression
Stage C. Generation
Stage D. The oracle
Stage E. The human gate
Designing the outcome taxonomy
Evaluation protocol
Diagnosing failure: model or evidence?
Cost model
Anti-patterns
Design checklist
Instantiating the method in a new domain

1. When this method applies

The method fits a problem when six conditions hold together. If any of them fails, a different design is appropriate, and section 16 says which.

The first condition is that the rule is small and structured. It must be short enough to generate in full and, more importantly, short enough to verify exhaustively rather than by sampling.

The second is that correctness is machine-checkable against evidence you already hold. This is the load-bearing condition. If you cannot decide, mechanically, whether a proposed repair is correct using evidence already in your possession, you do not have this problem. You have a research problem. Do not build a harness; build the oracle first, then reconsider whether the harness is still needed.

The third is that failures are numerous and repetitive. Below some volume a human repairs them faster than you can build the machinery, and the machinery will be obsolete before it pays for itself.

The fourth is that a wrong repair is silent: it produces output, just wrong output. This is what separates the problem from code generation. A generated function that is wrong usually throws, fails a test, or refuses to compile. A wrong rule returns a value. The pipeline stays green and the data quietly rots. Every design decision in this document follows from taking that seriously.

The fifth is that the evidence for a repair is local, meaning one or two examples suffice. If a repair requires reasoning across the whole system, the generator has the wrong shape and no harness will fix that.

The sixth is that a human can adjudicate a proposal in under a minute given the right display. The gate is deliberate, but it must not become the bottleneck, and whether it does is a property of your interface rather than of your reviewers.

Field note. In our deployment the oracle predated the model by a year and could be replayed over historical evidence at will. That single fact is why the project was tractable at all: every design question could be settled by re-running the oracle over inputs already on disk, and no question required a new data collection.

2. Terminology

The vocabulary below is used consistently throughout. It is worth fixing before the design discussion begins, because most disagreements about systems of this kind turn out to be disagreements about which component is being described.

A rule is the small structured object being repaired. A source is the external system whose inputs the rule is applied to, and which changes without warning or coordination. An instance is one failing case, meaning a rule together with the evidence of its failure, and evidence is the stored input on which the rule produced wrong output or none at all.

The generator is the model, in its single narrow role: evidence in, candidate rule out. The oracle is deterministic code that decides whether a candidate is acceptable, and a verdict is the oracle's typed judgement on one candidate. A gate is a particular kind of oracle check, one that can only demote a verdict and never promote it. A trial is empirical evaluation of a candidate against a held-out population of real instances. The adjudicator is the human who approves or rejects, and is the only writer to production. The harness is everything except the generator.

The asymmetry in that list is deliberate and should be stated explicitly. The generator has one job and no authority. Everything that decides anything is either deterministic code or a person.

Field note. We introduced the word "gate" only after the second production incident, when it became clear that the team was arguing about whether a check should be able to rescue a candidate. Naming the demote-only property made the answer obvious and closed the argument permanently.

3. Five invariants

State these in your design document and test them. They are not style preferences. Each one was learned by violating it.

I1. Deterministic before generative

Every filtering, grouping, classification and verification step is ordinary code. The generator is invoked only on instances that deterministic code could not dispose of, and only for the one judgement that genuinely requires reading unstructured evidence.

The consequence is that the generator sees a tiny fraction of the input. This is what makes the economics work, and it is also what makes the results interpretable: when quality moves, you know which stage moved it.

Field note. In one production run, 435,702 failing cases entered the pipeline and 137 pieces of evidence reached the model. That is roughly one in three thousand. Everything else was disposed of by rules that cost nothing to run.

I2. The oracle runs the production code path

Verify a candidate by executing the same code that will consume it in production, with the same parser, the same normalisation and the same coercion. Never a reimplementation, never an approximation, and never "the model says it matches".

The consequence is that a candidate which passes verification passes because production would pass. A reimplemented oracle drifts from production silently, and its drift is indistinguishable from a model failure, which contaminates every subsequent measurement.

Field note. Our generation loop needed a fast in-process check for speed, so a second, lighter implementation was unavoidable. Rather than assume the two agreed, we measured: 3.2% of candidates were decided differently by the two parsers. Small enough to tolerate, and large enough that assuming zero would have been a mistake we could not have detected later.

I3. Every claim is downgraded to a measurement

Do not let a qualitative judgement survive into the pipeline. "The candidate looks right" becomes a verdict from the oracle. The verdict becomes a hit rate over a held-out population. The hit rate becomes a comparison against what production currently achieves on the same evidence.

Field note. Three candidates in one review batch carried an identical verdict. Under trial, the first was a genuine repair, moving from zero successes in fifty to fifty in fifty. The second was pure churn: thirty-three successes in thirty-three both before and after, with byte-identical output. The third had no testable population at all, so no evidence existed in either direction. Three correct decisions, three different actions, one verdict. Without the trial layer all three would have been treated alike.

I4. Precision over recall, deliberately

In a human-gated system the scarcest resource is the adjudicator's trust. A queue at high precision gets reviewed. A queue at low precision gets ignored, and once ignored it is dead regardless of what it contains.

The consequence is that most tuning should make the generator answer less often. Design the contract so that "no answer" is an explicitly correct response, and say so in the prompt rather than hoping it is inferred.

Field note. Across our whole sequence of prompt changes, the count of genuinely good candidates barely moved, staying between eight and eleven on a fixed evaluation set, while precision rose from 40% to 91%. The entire gain was the model learning to decline. Nothing we did made it better at finding answers; everything we did made it better at not inventing them.

I5. Decisions are durable

An approval is never silently overwritten by a later run. A rejection is never resurrected: the same candidate must not reappear as though it were new, and a genuinely different candidate for the same slot should arrive annotated with what was already rejected there.

Field note. Before we added rejection memory, a monthly cycle re-proposed the same rejected candidates every month. Reviewers learned within two cycles that the queue contained things they had already dismissed, and review throughput fell accordingly. The fix was a few lines of persistence and it recovered more reviewer time than any model change we ever made.

4. Reference architecture

flowchart TD
    IN["Failing instances"] --> A
    A["A. ATTRIBUTION<br/>metadata only, no evidence decoded"] --> Aq{"Can repairing the<br/>rule fix this?"}
    Aq -->|no| ROUTE["Routed to the owning<br/>discipline, with the reason"]
    Aq -->|yes| B
    B["B. COMPRESSION<br/>group by structural equivalence"] --> Bq{"Is this evidence<br/>a valid basis<br/>for a repair?"}
    Bq -->|no| EXCL["Excluded, reason recorded"]
    Bq -->|yes| C
    C["C. GENERATION<br/>focused evidence to candidate"] --> CC["critique loop<br/>(mechanical, one round)"]
    CC --> D
    D["D. ORACLE<br/>production code path + gates"] --> Dq{"verdict"}
    Dq -->|not acceptable| FILE["Filed with a typed verdict"]
    Dq -->|acceptable| Q["Review queue"]
    Q --> E["E. ADJUDICATION<br/>evidence displayed, trial on demand"]
    E -->|approve| PROD["Production rule"]
    E -->|reject| MEM["Durable rejection"]

Figure 1. The five stages, and the single path into production.

Two properties of this structure matter more than the individual stages. The first is that the funnel is monotone in cost: each stage costs more per instance than the one before it, so each stage must remove work rather than add it. If a stage does not reduce the population by roughly an order of magnitude, it is not earning its place and should be merged into its neighbour. The second is that exactly one arrow reaches production, and a person is standing on it. Everything else writes to a queue, a file, or a log.

5. Stage A. Attribution

The purpose of this stage is to partition failures by which discipline can fix them, using metadata alone, with no evidence decoded and no model invoked. It is the highest-value stage relative to its cost, and it is almost always skipped by teams who begin from "we have a lot of failures, let us use a language model".

Four rules govern its design.

Buckets must be mutually exclusive and exhaustive. Compute them with a single first-match-wins rule chain rather than with independent conditions. Independent conditions allow one failure to count in two buckets and allow some failures to count in none, and both errors make every downstream number wrong. Assert that the buckets sum to the total, and fail loudly when they do not.

Order the chain by ownership rather than by frequency. The question the chain answers is "who fixes this?", so the most specific owner should be tested first.

Each bucket names a team, not a symptom. "Rejected" is not a bucket. "Access control, owned by the anti-abuse team" is a bucket, because it tells you where the work goes.

A bucket with no owner is a bug in the taxonomy. Add an explicit catch-all and watch it.

Field note. Across one estate, 78% of failures attributed to something other than the rule: access denials, entities that had been deleted at the source, and infrastructure faults. In a single run, 29,852 failures traced to one unreachable gateway, which was a five-minute configuration fix. Had those cases reached the model, it would have produced confident and useless repairs for a problem no repair could touch. Separately, when we replaced four independent bucket conditions with one exclusive chain, 4,307 previously uncounted failures appeared in the catch-all. They had been invisible for months, not because anyone hid them, but because no condition matched them and nothing asserted that the buckets summed to the total.

One blind spot must be documented rather than solved here. A failure can look healthy at the metadata level and be semantically dead, for example an input that announces success and contains only a notice that the entity no longer exists. Metadata cannot see that. Stage B must.

6. Stage B. Compression

The purpose of this stage is to collapse N failing instances into K distinct modes, where K is the number of genuinely different repairs required. Four rules govern it.

Group by structure, never by content. Two instances belong together when the shape of the evidence matches: the vocabulary of identifiers, the schema, the template. Content differs within one mode by definition, so keying on content makes every instance its own mode and the stage accomplishes nothing.

Hard-split before you cluster softly. Partition first on cheap categorical facts, then apply similarity only within each partition. This prevents a similarity threshold from merging categories that are qualitatively different but superficially close.

Take one representative per mode, and carry a sibling. The representative is what the generator sees. The sibling, a second instance of the same mode, is what makes the resulting candidate testable for generality rather than memorisation. This rule is not optional. A candidate validated on exactly one example is indistinguishable from a candidate that encodes that example, and requiring it to hold on a second instance is the cheapest generality test available.

Classify the representative's validity, and exclude with a recorded reason. Not every piece of evidence is a legitimate basis for repair. Evidence drawn from the wrong kind of entity produces a rule that is correct for that evidence and wrong forever afterwards.

Field note. One run compressed 26,481 failing instances to 114 representatives, in 88 seconds of ordinary computation. Most sources had exactly one failure mode; a minority had two or three genuinely distinct variants, and that minority is precisely the population a naive one-instance-per-source deduplication would have mis-repaired without ever revealing that it had. In the same run, 424 of 561 candidate groups were excluded because their evidence was the wrong kind: listings rather than single entities, notices rather than content. Our highest-ranked source by raw failure count was one of them. It had thousands of failures, and no repair existed, because 99.9% of its evidence did not contain the value at all. Ranking by raw failure count points at the loudest source. Ranking by attributable, valid failures points at the fixable one.

7. Stage C. Generation

The purpose of this stage is narrow by construction: given focused evidence and the current rule, propose a candidate or decline.

7.1 Evidence selection is the highest-leverage variable

Do not hand the model the whole input. Anchor on the strongest signal for the value you want, expand to a bounded context around that anchor, and attach only the structured metadata that could plausibly carry the answer.

This is not primarily a cost optimisation. The excluded region is where the wrong answers come from: repeated blocks, adjacent entities, alternate renderings of the same value. Removing that region removes a class of error rather than trading it for another.

The corresponding hazard is real and must be handled explicitly, because a focused view that omits the answer is worse than an unfocused one. Fall back to the full input whenever the anchor is not found with confidence, and measure how often the answer lies outside the focused region.

Field note. Our focused view is roughly 5% of the input. When we finally measured the hazard rather than assuming it away, we found that for one of the two fields we repair, the correct value lay outside the focused region on about one instance in ten. On those instances the model mostly declined, which cost recall and protected precision. That measurement converted a suspicion into a stated bound, and it also identified the single highest-value improvement available to us, which is widening the anchor for that field rather than changing anything about the model.

7.2 The contract

The prompt is a contract rather than a request, and it must state five things.

It must state the task as repair, not discovery. "This rule stopped working, here is the evidence, what should it be" outperforms "find the value", because the framing keeps the model anchored to the semantic slot the original rule meant rather than to any plausible-looking value in the input.

It must state the current rule and the observed failure, which are context the model cannot infer.

It must state hard constraints as prohibitions with reasons: uniqueness, generality across instances, and forbidden constructs such as positional indexes, instance identifiers and state-dependent conditions. Give the reason for each. A constraint with a stated failure mode is followed more reliably than a bare rule.

It must state that declining is a correct answer, explicitly, with an example of when. This single clause carries a large share of the precision gain described in I4.

It must specify a machine-parseable output shape, with a place for the model to record what it saw and why. That rationale is not for the pipeline. It is for the adjudicator.

Field note. Two constraints in our contract exist because of specific incidents. The prohibition on positional constructs was added after a candidate that counted siblings returned an unrelated value the moment an optional element appeared. The prohibition on state-dependent conditions was added after a candidate that keyed on a status marker worked perfectly while the entity stayed in that state and returned nothing, silently, the moment it changed. Neither would have been caught by a check on the evidence in front of us, because on that evidence both were correct.

7.3 Determinism

Fix the sampling temperature at zero and pin the seed. Without this you cannot attribute a metric change to a design change, and every comparison is contaminated by sampling noise.

Field note. Before pinning, two runs of an identical configuration disagreed on 4 of 46 slots. That is enough to move a headline number by two points, which is the same magnitude as several of the design changes we were trying to evaluate. Residual nondeterminism from batched inference remained even after pinning; we measured it once, recorded it, and stopped worrying about it.

7.4 The critique loop, exactly one round

After the first answer, run the cheap mechanical checks. If any fail, return them to the model together with the evidence of what its candidate actually did, not a bare "invalid" but a statement of the form "it matched these three things, whose contents are X, Y and Z". Allow exactly one revision.

The reason for exactly one is that iterating against the same evidence teaches the model to satisfy that evidence, which is overfitting with extra steps.

Field note. In our measurements the second round mostly produces withdrawal to a null answer rather than a better candidate. Under I4 withdrawal is a win, so the round pays for itself without ever needing to produce a repair. When we later gave a much larger model the same feedback on the same cases, it did not do better, which is the subject of section 12.

7.5 A note on reasoning budgets

If your generator supports an explicit reasoning phase, be aware of a specific failure mode. When reasoning and answer draw from one output budget, the reasoning expands to consume the entire budget and the model returns nothing.

The fix is a separate hard limit on the reasoning phase, leaving the answer budget intact. The general lesson outlives this one setting: verify that "more of a good thing" is monotone before assuming it, particularly where two behaviours draw from a shared pool.

Field note. We enabled reasoning with a generous budget and got 19 empty answers out of 23. Assuming the budget was too small, we raised it, and the failure got worse rather than better, because the reasoning simply expanded to fill whatever it was given. Bounding the reasoning phase separately at a few hundred tokens produced the best configuration we have measured, better than no reasoning at all, and it also stopped the model proposing state-dependent constructs without being told to.

8. Stage D. The oracle

The oracle is the component that makes the system trustworthy, and it deserves more design attention than the prompt.

8.1 Layers

Run the checks in increasing order of cost and stop at the first that rejects.

Begin with syntax, which asks whether the candidate parses or compiles at all. Then uniqueness, which asks whether it resolves to exactly one thing. Then value, which asks whether the production consumer accepts what the candidate yields. Then generality, which asks whether it also holds on the sibling instance and on other evidence from the same source. Then form, which asks whether it violates a stated constraint such as an embedded instance identifier or a positional index, and which is nearly free because it is a property of the candidate's text rather than of its execution.

Two further checks are more expensive and are described separately below, because they are the ones most systems omit.

8.2 Gates demote, never promote

A gate can only lower a verdict, never raise one. This asymmetry is what lets you add gates over time without re-validating everything that came before: a new gate can only make the queue more conservative, so its introduction can never invalidate a past approval.

8.3 The two gates people forget

The regression gate requires something most pipelines do not keep, namely evidence of success. Store, for each source, the most recent input on which the rule worked, together with the value production derived from it. Without that store, every candidate is evaluated only where the incumbent fails, which is the single place where the incumbent is guaranteed to lose, and the system will ship candidates that repair the broken minority while breaking the working majority.

The null-hypothesis gate asks whether the incumbent already works on the failing evidence. If it does, this rule is not the cause of the failure, something else is, and the candidate should be demoted with a pointer to the real question.

Field note. The success store was the single highest-value addition we made after the first production run. It is inexpensive, being one bounded, compressed input per monitored item with a two-week lifetime, and it immediately caught thirteen candidates in one run that would otherwise have shipped. It also supplies the population for the trial in stage E, so one storage decision paid for two features. The null-hypothesis gate, added at the same time, reclassified a large fraction of a subsequent run's queue: those cases were failing on the other field, and every minute spent reviewing them as repairs was wasted.

8.4 Merging, not overwriting

If a source can have several modes, several candidates will arrive for the same slot. Do not key your queue by source alone.

The correct behaviour is to verify everything first and then merge per slot. Identical candidates merge with their impact summed. Genuinely different candidates either compose, when the consumer supports ordered alternatives, in which case compose them and verify the composition as a unit, or are published as one winner with the alternatives attached and visible to the adjudicator.

A corollary applies as soon as more than one entry point feeds the queue. The queue key must include the entry point, or one stream's run will silently retire the other stream's work.

Field note. We keyed the queue by source and slot. One source had four modes and therefore four candidate repairs, which overwrote one another in arrival order. The survivor came from the smallest mode, representing 18 failing cases, while three larger repairs covering 252 cases vanished without any record that they had existed. After the fix, the same source published a composed candidate whose honest impact was 189 cases, and which the trial confirmed at fifty successes in fifty. Separately, when we later added a second entry point, its first run retired the first entry point's pending work, because both wrote the same keys. The review queue emptied overnight. Both incidents were schema decisions made in week one and paid for in week three.

9. Stage E. The human gate

The adjudicator is part of the system, and the display is part of the design. The card should show evidence rather than conclusions.

It should show the current rule and the candidate side by side, with what each yields on the same evidence. It should show the counts behind the verdict, meaning how many things matched and how many sibling instances the candidate held on. It should show the impact, summed honestly across merged modes rather than reported from whichever mode happened to survive. It should show the reason string for any demotion, phrased so a reviewer can argue with it. It should link to the raw evidence and state that evidence's classification. Finally, it should show the model's self-reported confidence in visually de-emphasised form, because it is the only unverified number present.

Make the candidate editable before approval. A large share of near-miss candidates need one qualifier added. An editable field converts them from rejections into repairs in seconds, and it is the cheapest recall you will ever buy.

Approval must be minimal and targeted. Write exactly one field, to the location the consumer actually reads, and record on the approval record the exact path written.

Field note. We shipped a version in which approval wrote to a location the consumer never read for one whole class of sources. The review showed green, the record said approved, and production behaviour did not change at all. Nothing failed, nothing alerted, and the only reason we found it was that a later audit compared what had been approved against what production was doing. Since then, every approval records its own write path, and the audit is a one-line query.

9.1 The trial

The strongest instrument available to the adjudicator runs both the incumbent and the candidate over a sample of real instances drawn from both populations, meaning instances that currently fail and instances that currently succeed, and reports per-instance results.

Compute a verdict from that comparison and persist it, so it survives the session. Five outcomes suffice: gain, where the candidate wins on failing evidence and loses nothing; no gain, where behaviour is identical to the incumbent and the change is therefore churn; loss; regression; and unverifiable, where no testable population exists.

The trial is the only stage that samples instances nobody selected, which is exactly why it catches what every earlier stage missed.

Field note. Our trial samples fifty failing and fifty working instances per candidate. It is also the mechanism that rescues a class of candidate the oracle demotes: when the regression gate reports that a candidate returns a different value on working inputs rather than no value, that is sometimes precisely the intent, because the incumbent had been reading an adjacent value all along. The only approved repair of that shape in our system was demoted by the oracle, rescued by the trial, and confirmed correct in production.

10. Designing the outcome taxonomy

Verdicts are the interface between the harness and the human, so design them as a typed enumeration with an action attached to each. Four rules apply.

Every verdict names an action. If two verdicts imply the same action, merge them. If one verdict implies "it depends", split it until it does not.

Distinguish "produces nothing" from "produces something different". These look alike in a diff and are opposites in meaning. The first is a break. The second is frequently the entire point of the repair.

Include a verdict for "not a repair at all". Some instances are telling you that the entity is gone, the reference is stale, or the access is broken. A taxonomy without that bucket forces them into a repair verdict, where they are reviewed forever and never resolved.

A verdict says the rule is valid, not that the action is wise. A candidate can be perfectly correct and still derive a value from evidence you should not be processing at all.

Field note. Two candidates in our queue were marked acceptable by every check and were correct in the strict sense. One faithfully returned a sentence stating that the entity no longer existed; the other faithfully returned a notice that the caller was not authorised to see it. Both were flawless rules applied to evidence we should never have collected. The display now carries the evidence classification next to the verdict, because no verdict can express this on its own. Our regression verdict still conflates the two meanings named above and separates them only in its reason string; in hindsight they should have been two verdicts, and that is the change we would make first.

11. Evaluation protocol

Most reported numbers for systems of this kind are not comparable across time, because the checks change as the system improves. Fix that before anything else.

11.1 Re-score history with today's oracle

Keep every run's raw generator output. Score all runs, always, with the current oracle. A run from month one and a run from month three then differ only in what changed by design.

This has a consequence people find uncomfortable and should not avoid. A stricter oracle makes past runs look worse, and it makes the current run look worse than last month's. That is the metric working correctly. A benchmark that only ever improves is measuring your optimism.

Field note. Adding the regression gate dropped a headline precision figure from 51% to 49% on comparable populations, because the gate rejected candidates the previous run had happily called acceptable. We published the drop with the explanation attached. The alternative, quoting the older number because it was more flattering, would have meant reporting a quality improvement that consisted entirely of a weaker test.

11.2 Define precision to exclude declining

Precision is acceptable candidates divided by candidates proposed. An instance the generator declined is not counted against it, because declining on evidence with no valid answer is correct behaviour, and penalising it would optimise directly against I4.

Report the decline rate separately. It is a real quantity, since it bounds recall, but it is not an error rate and must not be folded into one.

11.3 Two populations, reported separately

The laboratory population is a fixed, hand-picked set of twenty to fifty instances, used to attribute the effect of one change at a time. It is not representative and must never be quoted as system performance. Its job is causal attribution, and for that job a small fixed set is exactly right.

The field population is unselected production traffic. This is the honest number, and it will be lower.

Field note. Our laboratory figure is 91%, on twenty-three instances chosen because a repair was known to exist in each. Our field figures are 76% where the evidence is healthy and 49% where the evidence is already failing. All three are true, all three are quoted in different documents, and each one answers a different question. Quoting the laboratory number as system performance would have been the single most misleading thing we could have done, and it would have been entirely defensible from the raw data.

11.4 Stratify by difficulty, and say why the strata differ

Do not average across populations of different difficulty. Say what makes them differ, in terms of the evidence rather than in terms of the outcome.

Field note. Our two entry points differ structurally. One draws evidence where the rule already fails, which is hard, because a substantial share of that evidence has no valid answer at all: the entity is gone, or the value was never present. The other draws evidence where the system is healthy, which is easier, because the value is present and a reference value exists for comparison. The gap between 49% and 76% is almost entirely that difference, and averaging the two would produce a number describing no workload we actually run.

11.5 One change at a time, with the question written down

For each experiment record five things: the question it tests, the single change made, the population it ran on, the result, and the decision taken. A sequence of such records is the most useful artifact your project will produce. It stops the team re-running a dead end six months later, and it lets a newcomer see that the gains came from constraints rather than from scale.

12. Diagnosing failure: model or evidence?

When precision plateaus, the instinct is to reach for a larger generator. Test that instinct, because it is cheap to test and it is usually wrong.

12.1 First, categorise every failure mechanically

Take a full run's rejected candidates and bucket them by why the oracle rejected them, then compute the agreement rate on the accepted ones.

Field note. Across 442 rejections in one run, just under a third, 31%, resolved to nothing at all: a guessed chain of identifiers matching nothing in the evidence. A further 37% resolved to several things, meaning the candidate was not scoped to the intended entity and would silently read a neighbouring one. Both are generator failures, and both are aggravated, because the model had already been told in its revision round exactly what its candidate matched. A further 13% violated a form constraint stated in the prompt, typically a positional index or an embedded identifier. Another 9% resolved to exactly one thing whose content was not a value at all but a label, an identifier or an unrelated attribute. A residual 2% did not parse.

The remaining 3% were ours: a defect in our own numeric coercion, which read a comma-decimal amount as an integer a hundred times larger for sources using European conventions. No model change would have fixed those, and without the categorisation we would have attributed them to the model and tuned the prompt against our own bug.

Two conclusions followed immediately. First, 68% of the failures were candidates the generator's own in-loop check had already flagged, and the model returned them regardless. Those never reach a human, so queue quality is unaffected, but they establish that the ceiling is bounded by the model's compliance rather than by its ability to find answers. Second, where candidates were accepted, they reproduced the value production itself derived from the same evidence in 98.5% of cases, which established that the evidence was sound and the failures were not a data-quality problem wearing a disguise.

12.2 Then run the escalation experiment

There is a cheap and decisive test of the "bigger model" hypothesis. Take only the candidates the oracle rejected. Send each to a larger or differently-trained generator, with the full context: the same evidence, the first generator's answer, and the oracle's explanation of exactly what that answer did. Score with the same oracle, and report four outcomes: repaired, withdrawn, still failing, and damaged, where damaged means slots that were correct before and are wrong now.

Field note. We ran this with a model roughly three times the size of our production one, trained specifically on code. On healthy evidence, 129 failing slots were sent; 28 of them, or 22%, were repaired; 4 were withdrawn; 97 remained wrong; and 24 slots that had been working were damaged. On failing evidence, 34 slots were sent and none at all were repaired: 14 were withdrawn, 20 remained wrong, and 5 working slots were damaged. The larger model made the same two mistakes as the smaller one, resolving to nothing and resolving to several things, while looking at an explicit list of what its predecessor's candidate had matched. Twelve minutes of computation and two dollars settled a question the team had been arguing about for weeks.

The inference is that the limiting factor was the evidence window rather than the parameter count. The information needed to disambiguate lay outside the focused region we were sending. That is a retrieval and context problem, and it is fixed by widening and structuring the evidence, not by scaling the generator.

12.3 Escalate per-slot, never per-instance

If you do escalate, apply the second generator only to the specific slot that failed, and merge its answer back into the existing record.

Field note. Letting the second model rewrite whole instances made the system worse, moving it from 50% to 46% on the same population, entirely through collateral damage to slots that had been correct and were not in question. Restricting it to the failing slot and merging moved the same population from 75% to 80%. The difference between those two outcomes is not the model. It is the scope it was allowed to touch.

13. Cost model

At realistic volumes, cost is dominated by fixed setup rather than by inference, and the intuition that inference is the expensive part leads directly to the wrong optimisations.

Attribution and compression are effectively free, running in seconds to minutes with no model involved. Generator provisioning is the dominant fixed cost. Inference itself costs cents and a few seconds per instance. The oracle is free. Adjudication is the real cost, and it is measured in human minutes rather than in currency.

Five consequences follow. Batch aggressively, because two runs of a hundred instances cost far more than one run of two hundred. Parallelise by sharding the input rather than by scaling the machine, since several modest workers on several shards beat one large worker. Keep the environment warm across iterations during development, where the setup cost would otherwise be paid on every experiment. Make teardown independent of your session, because a crashed terminal must never leave a meter running: track resources by an external identifier, provide a one-command teardown, and, because local state files lie, verify against the provider's own interface that nothing is still running. Finally, optimise the adjudicator's minute rather than the inference cent, because at our volumes review time exceeds compute cost by orders of magnitude, and every display improvement that removes a click is worth more than a model change that removes a fraction of a cent.

Field note. On a typical run of ours, environment setup and model loading consumed 21 of 46 billed minutes. The inference for the whole run cost less than a dollar. Separately, a state file recording a single rented machine led us to believe a teardown had succeeded when three machines were still running, one of them expensive; only an audit against the provider's own listing revealed it. Both lessons are cheap to learn from someone else's writing and expensive to learn from an invoice.

14. Anti-patterns

Each of the following was either done by us or seriously proposed, and each carries a specific cost.

Going straight to the model, skipping attribution and compression, is the fastest thing to build and the most expensive to own. The model spends its budget on problems no repair can fix, and because nothing upstream partitions the input, the results cannot be attributed to any cause.

One instance per source looks like obvious deduplication and silently hides every source with more than one failure mode. You repair the smallest mode and declare the source fixed.

Reimplementing the consumer inside the oracle is faster to write and produces an oracle that drifts from production. The drift then masquerades as model error, and you tune the prompt to chase a bug in your own verifier.

Verifying only on failing evidence is tempting because that is the evidence you collected. It guarantees the incumbent loses every comparison, and you will ship regressions with confidence.

Auto-applying high-confidence candidates removes the bottleneck by removing the only component that can see semantic wrongness. Because wrong repairs are silent, you will not notice.

Trusting self-reported confidence is tempting because the number is right there in the output. It is uncalibrated, and it correlates with fluency rather than with correctness.

Iterating the critique loop until it passes looks like convergence and is overfitting to the single instance inside the loop.

Keying the queue by source alone is the simplest schema and causes multi-mode repairs to overwrite one another, and parallel entry points to destroy each other's work.

Reaching for a bigger model at the plateau is the culturally default move, and in our measurement it repaired 22% of one population and none of the other while damaging slots that already worked.

Reporting a single precision number makes for a cleaner narrative and averages populations of different difficulty, producing a figure that describes no real workload.

Normalising values into a fixed vocabulary before storing them produces tidier data and destroys the evidence. A substring match once mapped a negative statement onto its positive counterpart, inverting the meaning; because only the mapped value was retained, the error was undetectable after the fact. Store what the source said, and normalise on read.

15. Design checklist

The three lists below are ordered as they arise. Each item is a condition to be satisfied rather than a task to be ticked, and a design that cannot satisfy one should record why in the same place it records the rest of its decisions.

Before the first model call.

The oracle exists, runs the production code path, and can be replayed over historical evidence.
Attribution buckets are mutually exclusive, exhaustive, named after their owning discipline, and asserted to sum to the total.
Compression groups by structure and carries a sibling instance for every mode.
Evidence validity is classified, and invalid evidence is excluded with a recorded reason.
A store of successful evidence exists, holding the value production derived from each stored input.
The queue key includes the source, the slot and the entry point.
Verdicts are typed, each carries an action, and gates can only demote.
The generator's contract states its constraints, the reason for each, and that declining is a correct answer.
Sampling is deterministic and seeded.
The critique loop is bounded at one round.
Approval writes a single field, to the location the consumer reads, and records that path on the approval.
Teardown of paid resources works from a cold start and is verified against the provider rather than against local state.

Before quoting a number.

Every run has been re-scored with the current oracle.
Laboratory and field populations are reported separately and labelled as such.
Populations of different difficulty are reported separately, with the structural reason for the difference stated.
The decline rate is reported alongside precision rather than folded into it.
The experiment log records, for each change, the question it tested and the decision it produced.

When precision plateaus.

Rejections have been categorised mechanically by oracle reason before any model change is considered.
The agreement rate on accepted candidates has been computed, to rule out an evidence problem.
Escalation has been tested on rejected slots only, scored with the same oracle.
Damage to previously-correct slots has been counted, not only repairs.

16. Instantiating the method in a new domain

Map your problem onto the terminology of section 2, then work through the checklist. Three domains illustrate how little the method changes.

Rules that locate a value inside a semi-structured input. The rule is a path or pattern; the source is an external system that emits inputs on its own schedule; the evidence is a stored input on which location failed. The oracle is the production parser together with the value coercion that follows it. Uniqueness means the candidate resolves to exactly one element, and generality means it holds on a second input of the same shape. The regression gate replays stored successful inputs and requires their known values back. The null-hypothesis gate asks whether the current rule already succeeds on the failing evidence. The trial runs both rules over fifty failing and fifty working inputs, and the adjudicator sees the value each one derives, side by side.

Field mappings in schema or configuration migration. The rule is a mapping or a configuration key; the source is an upstream system; the evidence is a rejected record. The oracle is the real ingestion validator rather than a copy of its rules. Uniqueness means the candidate maps to exactly one destination field, and generality means it holds on a second record of the same shape. The regression gate replays previously-valid records and requires that they still validate. The null-hypothesis gate asks whether the current mapping already accepts the record. The trial runs both mappings over a held-out batch, and the adjudicator sees which records each accepts and rejects.

Assertions and wait conditions in flaky-test repair. The rule is an assertion or a wait condition; the source is a test suite; the evidence is a failing run's trace and timing. The oracle is the real runner executing the real suite. Uniqueness means the condition resolves to one target, and generality means the behaviour holds across N repeated runs rather than one lucky one. The regression gate requires that previously-passing tests still pass. The null-hypothesis gate asks whether the test is failing for a reason unrelated to timing. The trial runs both versions fifty times, and the adjudicator sees the pass rate and the timing distribution of each.

Four situations call for something other than this method.

When no mechanical oracle exists and condition C2 fails, build the oracle. If the oracle turns out to be the hard part, then the model was never your bottleneck and a harness will not help you.

When instances are few and individually valuable and condition C3 fails, use an interactive agent with tool access on individual cases. It costs an order of magnitude more per case and reads context a batch pipeline cannot, which makes it right for a handful of hard sources and wrong for thousands.

When repairs require global reasoning and condition C5 fails, restructure the problem until the unit of repair is local, or accept that what you have is design work rather than maintenance work and staff it accordingly.

When wrong repairs are loud and condition C4 fails, you may be able to auto-apply with a rollback and skip the human gate entirely. Verify first that the failure really is loud in production rather than merely loud in a test environment, because the two are routinely confused and only one of them protects you.

Field note. We run the first domain in bulk and reserve an interactive agent for a handful of sources whose inputs carry their values inside embedded state rather than in the structure itself. The agent costs roughly forty times more per case and is the only tool we have that reads those inputs correctly. The boundary between the two is volume, not difficulty, and it is worth stating in the design document so that nobody has to rediscover it under deadline.

Summary

The generator is the least important component of the system. What determines whether such a system produces trustworthy repairs is seven things: attribution, which refuses to work on failures no repair can fix, and which accounted for 78% of ours; compression, which yields one representative per genuine mode with a sibling for generality; evidence selection, a small and well-chosen window, because the excluded region is where the errors live; the oracle, running the production code path through layered checks and gates that only demote; a store of success, without which regression cannot be detected and will therefore be shipped; typed verdicts and durable decisions, so that reviewer effort accumulates rather than resetting; and honest evaluation, meaning re-scored history, separated populations, and a metric that is permitted to get worse.

Build those seven and a small general-purpose model is sufficient. Skip them and no model is.

Derived from a production deployment maintaining roughly 1,400 rules over inputs from as many independent external sources, covering approximately 1.6 million monitored items. All quantitative claims are measurements from that deployment. They are offered as evidence for the design rules, not as benchmarks to reproduce.

Your scraper is identified before it sends a single request

Theodore P. — Sat, 22 Aug 2026 20:30:26 +0000

I spent a long time believing that a blocked scraper was a headers problem. Wrong User-Agent, missing Accept-Language, something in that family. So I would copy a browser's headers field by field, hit the target again, and get the same 403. Then I would add a proxy. Same 403. Then I would add a delay. Same 403, just slower.

The thing I was missing is that the server had already classified my client before my request headers arrived. It did it in the first packet of the TLS handshake, and no amount of header polishing reaches that packet.

This post is about three things that, once I understood them, reorganized how I build fetchers: the handshake that identifies you, the line between what an HTTP client can present and what it structurally cannot, and the fact that a challenge page usually arrives wearing a 200.

1. The handshake is a fingerprint

Before any HTTP request, your client opens a TLS connection. The first message it sends is the ClientHello. It is plaintext, it is not secret, and it is an announcement: here are the cipher suites I support, in my preferred order; here are the extensions I understand, in this order; here are the elliptic curves I accept; here are the signature algorithms; here is the ALPN list of protocols I am willing to speak afterwards.

Your code chose none of this. The TLS library under your HTTP library chose it. Python's ssl module makes one set of choices. Go's crypto/tls makes another. Chrome's BoringSSL makes a third. The choices are stable per stack and different across stacks, which is exactly the property a fingerprint needs.

The defense hashes the ordered set into a short identifier. The older scheme is JA3, the newer and more structured one is JA4. Then it looks the identifier up in a table. Real Chrome builds are in the table. Real Firefox builds are in the table. python-requests is also in the table, filed under automation.

Here is what the two announcements look like side by side, abbreviated:

real Chrome ClientHello:
  ciphers:   [GREASE, 0x1301, 0x1302, 0x1303, 0xc02b, 0xc02f, ...]
  ext order: [GREASE, server_name, ..., GREASE, padding]
  curves:    [GREASE, X25519, secp256r1, secp384r1]
  -> JA4 matches a known Chrome build

language-default ClientHello:
  ciphers:   [0xc02c, 0xc030, 0x009f, ...]
  ext order: [server_name, ...]
  -> JA4 matches "known automation"

Two details in there caught me out for a long time.

Order is part of the signal. Two clients can support the identical set of ciphers and still fingerprint differently because they list them in a different sequence. Enabling the same algorithms as Chrome is insufficient. You have to offer them in Chrome's order, with Chrome's extensions in Chrome's positions.

Browsers are deliberately messy. Those GREASE entries are meaningless values that browsers insert at randomized positions to keep the TLS ecosystem tolerant of unknown extensions. A naive imitation that omits them, or places them predictably, stands out precisely by being too tidy. A convincing handshake has to be messy in the exact way a real browser is messy.

The fix: impersonate, do not reconstruct

You do not rebuild Chrome's cipher order by hand. You use a library that links against a patched TLS stack and ships presets, each one a faithful copy of a specific browser version's ClientHello. In Python that library is curl_cffi:

from curl_cffi.requests import AsyncSession

session = AsyncSession(impersonate="chrome")
# cipher order, extension order, curves, ALPN, GREASE padding
# all reproduced from the named preset, not Python's ssl defaults
resp = await session.get("https://example.com/")

One value names the browser. Everything below it follows.

Two disciplines come with this, and both are where it breaks in the field.

First, the impersonation target has to agree with the identity you claim elsewhere. If the handshake is Chrome 120's and your User-Agent says Chrome 131, the defense sees two identities that cannot both be true. That mismatch is more incriminating than an honest Python fingerprint would have been, because an honest library is merely automation while a contradiction is an attempt.

Second, presets age. A preset that reproduces a three-year-old Chrome build is itself anomalous once almost no real user runs that build. Keeping the impersonation target on versions real users actually run is maintenance, not setup.

What the handshake cannot do

A perfect Chrome handshake from a data-center IP is still a perfect Chrome handshake from a data-center IP, and the address earns its own suspicion. The handshake also runs none of the JavaScript probes that come later if a challenge engages. It is the first thing the server reads and the cheapest thing to get right, and that is the whole of its job.

2. The twenty and the eighty

This is the part that would have saved me the most time.

Think about what actually leaves your machine when an HTTP client makes a request. The TLS handshake. The HTTP/2 settings and frame ordering above it. The request headers, including the whole family of client hints. The source IP. These travel as bytes, and an HTTP client can present all of them with complete fidelity, because presenting them is nothing more than writing the right bytes.

Now think about what a defense's challenge script probes once it is running inside a browser. Screen dimensions and pixel density. A WebGL scene rendered and hashed, which depends on the exact GPU and driver combination. Text drawn to a canvas and measured, which exercises the font stack. A short computation pushed through the audio pipeline, where the rounding varies by CPU and OS. Hardware concurrency. Device memory. And the motion of the pointer, graded for humanity.

Not one of those has a representation in an HTTP request. There is no header for the hash of a rendered scene, because the hash is the output of code running against a graphics stack that your HTTP client does not have. You can send a client hint claiming a screen width, but a hint is a claim and the script makes a measurement, and a defense that cares cross-checks the two.

Roughly a fifth of the total fingerprint surface travels on the wire. The other four fifths exist only inside a running browser. Faking a WebGL hash over HTTP is a category error. It is the result of a computation your tool cannot perform.

That line through the middle of the problem decides the architecture:

A target that reads only the wire surfaces is served by an HTTP client, fast and cheap. Effort goes into making the wire presentation coherent.
A target that probes the runtime or grades behavior cannot be served by any HTTP client. The choice narrows to running a real browser or leaving the target alone.

Every system of any size I have seen ends up with both tools and a rule for choosing between them. Start cheap. Escalate only when the cheap path is refused. Keep the expensive tier idle until a target proves it needs it.

There is one bridge across the line, and it is the most valuable pattern I know for the harder defenses: a browser can do the runtime work once, earn the trust token, and hand that token to the HTTP client to spend on a burst of fast requests. It works because a token, unlike a rendered-scene hash, is just bytes, and bytes travel. It comes with three conditions (same exit IP as the solve, client identity coherent with the solving browser, burst finished before the token expires) and each one is a place the naive version dies silently. That pattern gets its own chapter in the book; here I only want the line itself.

3. The challenge arrives as a 200

Everything above assumes you can tell when you were challenged. You mostly cannot, unless you build the detector on purpose.

A defense rarely returns an honest error. It returns a page with a normal status code and a body of plausible size that is actually a shell: a script tag, an interstitial skeleton, and no content. A system that cannot distinguish the shell from the real page stores shells as data, counts challenges as successes, and burns its identities on the wrong signals.

Two detectors, layered:

Content markers. Every challenge script declares characteristic variables and identifiers that real content never contains. Keep a set per defense and scan every response body against it. False positives are rare. The cost is that markers drift as vendors revise their scripts, so a sudden rise in undetected shells is usually the first sign your marker set went stale.

A size floor. The stricter defenses serve a deliberately economical challenge, a few KB with a terse marker a generic scan might miss. Real content has a weight that a shell falls far below. A body under the floor is treated as a shell regardless of status code.

Here is a real trace of one work item crossing the line mid-crawl:

client GET /product/48812  -> HTTP/2 200, body 9.4 KB
  marker scan: "var chlgeId = ''" present
  size:        9.4 KB < 15 KB floor
  -> challenge wearing a 200

The important decision is what happens next. The naive reaction is to retry with a fresh identity. That earns the same shell, because the target is probing the runtime and no HTTP identity answers a runtime probe. The challenge is information about which tool the target requires:

burn persona
re-queue { url: /product/48812, force_js: True, priority: 0 }
browser tier: warm, solve, serve -> 200, body 148 KB

Which status codes mean what, in the retry policy I ended up with:

BURN_AND_RETRY = frozenset({429, 403, 503})   # re-queue AND burn the identity
RETRY_ONLY     = frozenset({500, 502, 504})   # transient: re-queue, keep identity
# 503 lives with the burn set: Akamai challenge shells return 503.

And one more detector for the case where the address itself has been condemned rather than the identity. A block page looks different from a challenge, and the correct response reaches below the persona to the exit IP, because fresh identities from a condemned address only get spent uselessly:

def _is_ip_blocked(html: str) -> bool:
    if len(html) > 100_000:          # real content never trips this
        return False
    low = html.lower()
    return (
        any(t in low for t in ("ip", "address", "adresse", "adres"))
        and any(p in low for p in (
            "blocked", "access denied", "too many requests",
            "gesperrt", "bloqué", "bloqueado", "geblokkeerd",
        ))
    )

What this changed in practice

I stopped treating headers as the first fix and started treating the handshake as the first fix. impersonate="chrome" and a User-Agent that names the same Chrome version, before anything else.
I stopped asking "how do I make requests pass this target" and started asking "which side of the line is this target on." If it probes the runtime, the answer is a browser, and the interesting engineering becomes how rarely I can afford to use it.
I stopped counting 200s as successes. A 200 is a status code. Content is a marker scan plus a size floor.

None of this is secret; it is all visible in the bytes if you look. The expensive part was learning which bytes to look at and in what order, and that is what the months went on.

The book

These three sections are condensed from chapters III, IV, and XIV of a book I wrote called The Quiet Fetch: Engineering Web Acquisition Against Modern Bot Defenses, One Layer at a Time. It is 25 chapters and four appendices, about 110 pages, and it walks the whole stack in the order a request meets it:

the six fingerprint surfaces and the two kinds of defense
the wire layer: TLS, HTTP/2 settings as a fingerprint, headers and client hints, referer and timing tells
personas as a coherent unit, pools, cooldown, and burn
Cloudflare and Akamai up close, why Akamai is harder, the cookie-handoff warmup, challenge detection
the browser tier: Slots and profiles, coupling the browser identity to the client identity
fleet and scale: splitting the pipeline into three services, queues and pacing, scaling on backlog, the proxy grid, retries and breakers and self-healing selectors
recon and operating: mapping a surface, auditing your own origin for leaks, observability, and the honest ceiling where the right answer is to stop

Every chapter ends with a Field Verdict box: the mechanism, the fix, and the conditions under which it fails. The code is production code from fetchers I run, with the business stripped out.

If the three things above were useful, the rest is here: The Quiet Fetch on Gumroad.

Questions about any of it, or a defense that behaves differently from what I described, are welcome in the comments. The marker sets drift and so does my understanding.

Grapple Dojo

Theodore P. — Thu, 13 Aug 2026 10:36:28 +0000

I built a game for BJJ players who want to keep training off the mats.

Meet Grapple Dojo: basically "grappling chess" powered by 3D animations, featuring authentic positions and transitions.

It utilizes Eelis van der Weegen’s incredible GrappleMap dataset—which maps 843 positions and 1,485 transitions from top-tier instructionals (Marcelo Garcia, Ryan Hall, Eddie Bravo, Demian Maia, etc.), complete with exact DVD chapter/second references.

Key Features:

Active Recall: Test your positional knowledge in tactical duels.

Full 3D Viewer: Rotate and analyze moves from any angle through a White-to-Black belt curriculum.

Virtual Open Mat: Set up positions on an interactive dummy and share techniques via live chat.

Stat Tracking: Track your mastered techniques and overall rank progress.

Huge credit to GrappleMap for doing the heavy lifting with the data—I just gamified it!

Try it out and tell me: what’s your go-to position? 🤙

https://grapple-dojo.onrender.com/

P.S. Still in active development! Working on tighter move validation, better 3D models, and bug fixes.

Stack Panic

Theodore P. — Thu, 13 Aug 2026 10:34:06 +0000

I really miss hand-crafted, line-by-line coding, but the motivation just isn’t there anymore.

Every time I reopened LeetCode, I’d end up reading old theory books instead of writing code.

To fix that, I decided to gamify the experience.

I built a game-like take on LeetCode where you write code, learn, and directly apply that knowledge in Stack Panic.

Check it out, you're going to love it!

https://stack-panic.onrender.com/

Streaming SQL Engine: Lightweight Cross-Data Source Integration for Resource-Constrained Environments.

Theodore P. — Mon, 05 Jan 2026 11:28:45 +0000

One month ago, I shared how we deployed a streaming SQL engine that lets you many data sources using standard SQL syntax.

Original post is here:

https://dev.to/theodore_p_9749548f7dd03/how-i-built-a-python-library-that-lets-you-join-mysql-postgresql-mongodb-rest-apis-and-files-in-h5d

Today, I want to present our research paper: "Streaming SQL Engine: Lightweight Cross-Data Source Integration for Resource-Constrained Environments."

Read it here:

https://github.com/Ierofantis/streaming_sql_engine/blob/master/Streaming_SQL_Engine_Paper.pdf

The problem we aimed to solve is simple: many organizations need ETL and big data capabilities without the overhead of heavy infrastructure.

Not every team can spin up large compute clusters or dedicate substantial resources to data pipelines.

Our solution is a streaming, iterator-based SQL engine optimized for memory efficiency and cross-source integration.

This work isn’t about claiming the "fastest" solution, it’s about providing a practical, optimized tool that solves real problems for teams with limited resources.

All comparisons are transparent, with fair benchmarks.

I believe the best solutions come from honest evaluations of trade-offs, not marketing hype.

Join Data from Anywhere: The Streaming SQL Engine That Bridges Databases, APIs, and Files

Theodore P. — Tue, 16 Dec 2025 21:55:43 +0000

Have you ever needed to join data from a MySQL database with a PostgreSQL database, a MongoDB collection, and a REST API all in one query? Traditional databases can't do this. That's why I built the Streaming SQL Engine.

The Problem: Data Lives Everywhere

Modern applications don't store all their data in one place. You might have:

User data in PostgreSQL
Order data in MySQL
Product catalog in MongoDB
Pricing information from a REST API
Inventory data in CSV files
Product feeds in XML files

The challenge: How do you join all this data together?

Traditional solutions require:

Exporting data from each system
Importing into a central database
Writing complex ETL pipelines
Maintaining data synchronization

There had to be a better way.

The Solution: Streaming SQL Engine

I built a lightweight Python library that lets you join data from any source using standard SQL syntax without exporting, importing, or setting up infrastructure.

Example:

from streaming_sql_engine import Engine
import psycopg2
import pymysql
from pymongo import MongoClient
import requests
import csv

engine = Engine()

# Register PostgreSQL source (iterator function)
def postgres_users():
    conn = psycopg2.connect(host="localhost", database="mydb", user="user", password="pass")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, email FROM users")
    for row in cursor:
        yield {"id": row[0], "name": row[1], "email": row[2]}
    conn.close()
engine.register("postgres_users", postgres_users)

# Register MySQL source (iterator function)
def mysql_products():
    conn = pymysql.connect(host="localhost", database="mydb", user="user", password="pass")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, price FROM products")
    for row in cursor:
        yield {"id": row[0], "name": row[1], "price": row[2]}
    conn.close()
engine.register("mysql_products", mysql_products)

# Register MongoDB source (iterator function)
def mongo_inventory():
    client = MongoClient("mongodb://localhost:27017")
    for doc in client.mydb.inventory.find():
        yield doc
engine.register("mongo_inventory", mongo_inventory)

# Register REST API source (iterator function)
def api_prices():
    response = requests.get("https://api.example.com/prices")
    for item in response.json():
        yield item
engine.register("api_prices", api_prices)

# Register CSV source (iterator function)
def csv_suppliers():
    with open("suppliers.csv") as f:
        for row in csv.DictReader(f):
            yield row
engine.register("csv_suppliers", csv_suppliers)

# Join them all in one SQL query!
query = """
    SELECT
        mysql_products.name,
        postgres_users.email,
        mongo_inventory.quantity,
        api_prices.price,
        csv_suppliers.supplier_name
    FROM mysql_products
    JOIN postgres_users ON mysql_products.user_id = postgres_users.id
    JOIN mongo_inventory ON mysql_products.sku = mongo_inventory.sku
    JOIN api_prices ON mysql_products.sku = api_prices.sku
    JOIN csv_suppliers ON mysql_products.supplier_id = csv_suppliers.id
    WHERE api_prices.price > 100
"""

for row in engine.query(query):
    process(row)

That's it. No clusters, no infrastructure, no data export - just pure Python and SQL.

Why I Built This: The Problem That Needed Solving

I was working on a data reconciliation project where I needed to join data from multiple sources:

MySQL database (product catalog)
PostgreSQL database (user data)
MongoDB collection (inventory)
REST API (pricing information)
CSV files (supplier data)

The challenge: Traditional databases can't join across different systems. I had three options:

Export everything to one database - Time-consuming, requires ETL pipelines, data becomes stale
Write custom Python code - Complex, error-prone, hard to maintain
Use existing tools - Spark/Flink require clusters, DuckDB requires data import, Presto needs infrastructure

None of these worked for my use case. I needed something that:

Could join data from different systems without export
Was simple to use (SQL syntax)
Required zero infrastructure
Worked with Python natively
Processed data efficiently (streaming)

So I built the Streaming SQL Engine.

How I Built It: Architecture and Design Decisions

Core Design Philosophy

The engine follows a pipeline architecture inspired by database query execution engines (like PostgreSQL and SQLite), but implemented in pure Python using iterators.

Key insight: Python's iterator protocol is perfect for streaming data. Each operator in the pipeline is an iterator that processes rows one at a time.

Why Iterator-Based Architecture?

Key advantages:

Memory efficiency: Only one row in memory at a time (except for join indexes)
Lazy evaluation: Processing starts only when you iterate over results
Composability: Operators can be chained arbitrarily
Extensibility: Easy to add new operators
Python-native: Uses standard Python iterator protocol

Trade-offs:

Performance: Python iterators are slower than compiled code, but flexibility is worth it
Memory: Join indexes require memory, but this is necessary for efficient joins
Complexity: Iterator chains can be complex, but they're composable and testable

Expression Evaluation

How WHERE clauses are evaluated:

The engine uses recursive AST traversal to evaluate expressions:

Why recursive: SQL expressions are trees. Recursive evaluation naturally handles nested expressions like (a > 10 AND b < 20) OR c = 5.

Join Algorithm Selection

How the engine chooses join algorithms:

The engine follows this priority order when selecting a join algorithm:

Check if both sides are sorted (ordered_by metadata)

If yes, use MergeJoinIterator (most memory-efficient for sorted data)
Only used when use_polars=False

Check if right side is a file (filename metadata)

If yes, use MmapLookupJoinIterator (memory-mapped, 90-99% memory reduction)
Only used when use_polars=False

Check if Polars is available (use_polars flag)

If yes, use PolarsLookupJoinIterator (vectorized, SIMD-accelerated)
Used when use_polars=True is explicitly set

Default fallback
- Use LookupJoinIterator (Python hash-based, most compatible)

Note: When use_polars=False (default), Merge Join and MMAP Join are checked first before falling back to Python Lookup Join. When use_polars=True is explicitly set, the engine prioritizes Polars over MMAP and Merge Join.

Protocol-Based Optimization

The key innovation: Automatic optimization detection via function signature inspection.

How it works:

Engine inspects source function signature using inspect.signature()
If function accepts dynamic_where or dynamic_columns parameters, it supports optimizations
Engine passes optimization parameters automatically
Source function applies optimizations (filter pushdown, column pruning)

Why this approach:

No flags needed: Detection is automatic
Flexible: Any Python function can be a source
Backward compatible: Simple sources still work (no optimizations)
Extensible: Easy to add new optimization parameters

Example:

# Simple source (no optimizations)
def simple_source():
    return iter([{"id": 1, "name": "Alice"}])

# Optimized source (with protocol)
def optimized_source(dynamic_where=None, dynamic_columns=None):
    query = "SELECT "
    if dynamic_columns:
        query += ", ".join(dynamic_columns)
    else:
        query += "*"
    query += " FROM table"
    if dynamic_where:
        query += f" WHERE {dynamic_where}"
    return execute_query(query)

# Both work the same way:
engine.register("users", simple_source)  # No optimizations
engine.register("products", optimized_source)  # Optimizations apply automatically!

Memory Management

Key strategies:

Streaming: Process one row at a time, never load full tables
Join indexes: Only right side of joins is materialized (necessary for lookups)
Memory-mapped files: For large JSONL files, use OS virtual memory
Column pruning: Only extract needed columns
Filter pushdown: Filter at source, reduce data transfer

Memory footprint:

Left side of join: O(1) - one row at a time
Right side of join: O(n) - hash index in memory
Total: O(n) where n = size of right side

Why this works: In most queries, the right side is smaller (e.g., joining large product table with small category table). The engine is designed to put smaller tables on the right side.

Performance Optimizations

1. Polars Vectorization

When Polars is available, the engine uses vectorized operations:

# Instead of row-by-row filtering:
for row in rows:
    if row['price'] > 100:
        yield row

# Use Polars batch filtering:
df = pl.DataFrame(rows)
filtered_df = df.filter(pl.col('price') > 100)
for row in filtered_df.iter_rows(named=True):
    yield row

Why faster: Polars uses SIMD instructions and columnar processing, 10-200x faster than row-by-row Python loops.

2. Memory-Mapped Joins

For large JSONL files, use OS virtual memory instead of loading into RAM:

# Instead of loading entire file:
with open('large_file.jsonl') as f:
    data = [json.loads(line) for line in f]  # 10GB in memory!

# Use memory-mapped file:
import mmap
with open('large_file.jsonl', 'rb') as f:
    mm = mmap.mmap(f.fileno(), 0, access=mmap.ACCESS_READ)
    # OS handles memory, can process files larger than RAM

Why this works: OS virtual memory allows accessing file data without loading it all into RAM. 90-99% memory reduction for large files.

3. First Match Only Optimization

For joins where only the first match matters (prevents Cartesian products).

Why useful: Prevents Cartesian products when right side has duplicate keys. Significantly reduces output size and processing time.

4. Column Pruning

Only extracts columns needed for the query, reducing I/O and memory:

# Query only requests 'name' and 'price'
query = "SELECT name, price FROM products"

# Engine automatically requests only these columns from source
def source(dynamic_columns=None):
    columns = ", ".join(dynamic_columns)  # ['name', 'price']
    query = f"SELECT {columns} FROM table"

5. Filter Pushdown

Pushes WHERE conditions to data sources when possible:

# Query has WHERE clause
query = "SELECT * FROM products WHERE price > 100"

# Engine automatically pushes filter to source
def source(dynamic_where=None):
    query = f"SELECT * FROM table WHERE {dynamic_where}"

6. Merge Joins

Efficient joins for pre-sorted data (O(n+m) time complexity):

# Register with ordered_by to enable merge join
engine.register("users", users_source, ordered_by="id")

# Engine uses merge join algorithm
# Both sides must be sorted by join key

Design Decisions and Trade-offs

1. Why Python iterators instead of compiled code?

Decision: Use Python iterators for flexibility

Trade-off: Slower than compiled code, but:

Works with any Python data source
Easy to extend and customize
No compilation step needed
Python-native integration

2. Why separate logical planning from execution?

Decision: Two-phase approach (plan then execute)

Trade-off: Extra step, but:

Enables query optimization
Easier to test and debug
Can reuse planning logic
Clear separation of concerns

3. Why protocol-based optimization instead of flags?

Decision: Automatic detection via function signature

Trade-off: Slightly more complex detection, but:

No flags to remember
Automatic optimization
Backward compatible
More Pythonic

4. Why materialize right side of joins?

Decision: Build hash index on right side

Trade-off: Memory usage, but:

O(1) lookup time per left row
Necessary for efficient joins
Can use memory-mapped files for large files
Standard database approach

5. Why limit SQL features (no GROUP BY, aggregations)?

Decision: Focus on joins and filtering

Trade-off: Less SQL support, but:

Simpler implementation
Faster execution
Focuses on core use case (cross-system joins)
Can add later if needed

The Result

A lightweight Python library that:

Joins data from any source using SQL
Processes data row-by-row (streaming)
Requires zero infrastructure
Automatically optimizes when possible
Works with any Python iterator

The key insight: Python's iterator protocol is perfect for streaming SQL execution. By combining SQL parsing, logical planning, and iterator-based execution, I created a tool that solves a real problem: joining data from different systems without complex infrastructure.

How It Works: Streaming Architecture

The engine processes data row-by-row, never loading entire tables into memory:

SQL Query
    |
Parser -> AST
    |
Planner -> Logical Plan
    |
Executor -> Iterator Pipeline
    |
Results (Generator)

Iterator Pipeline:

ScanIterator -> FilterIterator -> JoinIterators -> ProjectIterator -> Results

Each iterator processes rows incrementally, enabling true streaming execution. This means:

Low memory footprint
Can process data larger than RAM
Results yielded immediately
No buffering required

Supported Data Sources

The engine works with any Python iterator, making it incredibly flexible:

Databases

All databases are accessed via Python iterator functions. The engine doesn't use connectors - it works with any Python function that returns an iterator:

PostgreSQL - Create iterator function that queries PostgreSQL and yields rows
MySQL - Create iterator function that queries MySQL and yields rows
MongoDB - Create iterator function that queries MongoDB and yields documents

Files

CSV - Standard CSV files
JSONL - JSON Lines format with memory-mapped joins
JSON - Standard JSON files
XML - XML parsing with ElementTree

APIs

REST APIs - Any HTTP endpoint
GraphQL - Via custom functions
WebSockets - Streaming data sources

Custom Sources

Any Python function that returns an iterator
Generators - Perfect for streaming data
Custom transformations - Apply Python logic between joins

SQL Features

The engine supports standard SQL syntax:

Supported Features

SELECT - Column selection, aliasing, table-qualified columns

SELECT users.name, orders.total AS order_total
FROM users
JOIN orders ON users.id = orders.user_id

JOIN - INNER JOIN and LEFT JOIN with equality conditions

SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
LEFT JOIN table3 t3 ON t1.id = t3.id

WHERE - Comparisons, boolean logic, NULL checks, IN clauses

SELECT *
FROM products
WHERE price > 100
  AND status IN ('active', 'pending')
  AND description IS NOT NULL

Arithmetic - Addition, subtraction, multiplication, division, modulo

SELECT
  price - discount AS final_price,
  quantity * unit_price AS total
FROM orders

Not Supported

GROUP BY and aggregations (COUNT, SUM, AVG)
ORDER BY
HAVING
Subqueries

These limitations keep the engine focused on joins and filtering - its core strength.

Real-World Examples

Example 1: Microservices Data Integration

In a microservices architecture, data is distributed across services:

from streaming_sql_engine import Engine
import psycopg2
import pymysql
import requests

engine = Engine()

# Service 1: User service (PostgreSQL) - iterator function
def users_source():
    conn = psycopg2.connect(host="user-db", port=5432, user="user", password="pass", database="users_db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, email FROM users")
    for row in cursor:
        yield {"id": row[0], "name": row[1], "email": row[2]}
    conn.close()
engine.register("users", users_source)

# Service 2: Order service (MySQL) - iterator function
def orders_source():
    conn = pymysql.connect(host="order-db", port=3306, user="user", password="pass", database="orders_db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, user_id, total FROM orders")
    for row in cursor:
        yield {"id": row[0], "user_id": row[1], "total": row[2]}
    conn.close()
engine.register("orders", orders_source)

# Service 3: Payment service (REST API) - iterator function
def payment_source():
    response = requests.get("https://payments.service/api/transactions")
    for item in response.json():
        yield item
engine.register("payments", payment_source)

# Join across services
query = """
    SELECT users.name, orders.total, payments.status
    FROM users
    JOIN orders ON users.id = orders.user_id
    JOIN payments ON orders.id = payments.order_id
"""

Why this matters: No need for a shared database or complex ETL pipelines. The engine accepts any Python function that returns an iterator, making it incredibly flexible.

Example 2: Real-Time Price Comparison

Compare prices from multiple XML feeds and match with MongoDB:

def parse_xml(filepath):
    tree = ET.parse(filepath)
    for product in tree.findall('.//product'):
        yield {
            'ean': product.find('ean').text,
            'price': float(product.find('price').text),
            'name': product.find('name').text
        }

engine.register("xml1", lambda: parse_xml("prices1.xml"))
engine.register("xml2", lambda: parse_xml("prices2.xml"))
engine.register("mongo", mongo_source)

query = """
    SELECT
        xml1.ean,
        xml1.price AS price1,
        xml2.price AS price2,
        mongo.sf_sku
    FROM xml1
    JOIN xml2 ON xml1.ean = xml2.ean
    JOIN mongo ON xml1.ean = mongo.ean
    WHERE xml1.price != xml2.price
"""

Example 3: Python Processing Between Joins

Apply Python logic (ML models, custom functions) between joins:

def enriched_source():
    """Source that processes data with Python before joining"""
    import psycopg2
    conn = psycopg2.connect(host="localhost", database="mydb", user="user", password="pass")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, category_id FROM products")
    for row in cursor:
        product = {"id": row[0], "name": row[1], "category_id": row[2]}
        # Apply Python logic
        product['ml_score'] = ml_model.predict(product)
        product['custom_field'] = custom_function(product)
        yield product
    conn.close()

def categories_source():
    import psycopg2
    conn = psycopg2.connect(host="localhost", database="mydb", user="user", password="pass")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name FROM categories")
    for row in cursor:
        yield {"id": row[0], "category_name": row[1]}
    conn.close()

engine.register("enriched_products", enriched_source)
engine.register("categories", categories_source)

query = """
    SELECT p.name, p.ml_score, c.category_name
    FROM enriched_products p
    JOIN categories c ON p.category_id = c.id
"""

Why this matters: Seamless integration with Python ecosystem - use any library, apply any logic.

Comparison with Alternatives

vs DuckDB

Feature	Streaming SQL Engine	DuckDB
Cross-system joins	✅ Direct	⚠️ Requires import
API + Database join	✅ Direct	❌ Must export API
Real-time streaming	✅ True streaming	⚠️ Buffering needed
Python processing	✅ Native	⚠️ Export/import
GROUP BY / Aggregations	❌ Not supported	✅ Full support
Performance (same DB)	⚠️ Moderate	✅ Very fast

Use Streaming SQL Engine when: You need to join data from different systems that can't be imported into DuckDB.

Use DuckDB when: All data can be imported and you need aggregations.

vs Pandas

Feature	Streaming SQL Engine	Pandas
Cross-system joins	✅ Direct	❌ Must load all data
Memory efficiency	✅ Streaming	❌ Loads all in memory
SQL syntax	✅ Standard SQL	❌ DataFrame API
Large datasets	✅ Can exceed RAM	❌ Limited by RAM
Real-time data	✅ Streaming	❌ Batch only
File formats	✅ Any Python iterator	⚠️ Limited formats
Performance (large data)	✅ Streaming	⚠️ Slower for large data

Use Streaming SQL Engine when: You need to join data from different systems, process data larger than RAM, or use SQL syntax.

Use Pandas when: All data fits in memory and you prefer DataFrame API.

Are There Other Tools Like This?

Short answer: Not exactly.

While there are many tools that do parts of what Streaming SQL Engine does, none combine all these characteristics:

What Makes Streaming SQL Engine Unique

Zero Infrastructure + Cross-System Joins

Most tools require clusters (Spark, Flink, Drill)
Or require specific infrastructure (ksqlDB needs Kafka)
Streaming SQL Engine: Just Python

Any Python Iterator as Data Source

Most tools require specific connectors
Streaming SQL Engine: Any Python function works

Direct API Joins

Most tools can't join REST APIs directly
Streaming SQL Engine: Native support

Python-Native Architecture
- Most tools are Java/Rust with Python wrappers
- Streaming SQL Engine: Pure Python, seamless integration

Similar Tools (But Different)

Apache Drill - Similar cross-system capability, but requires cluster and Java

ksqlDB - Streaming SQL, but Kafka-only and requires infrastructure

Materialize - Streaming database, but requires database server

DataFusion - Fast SQL engine, but limited to Arrow/Parquet data

Polars SQL - Fast SQL, but requires loading data into DataFrames first

Presto/Trino - Cross-system SQL, but requires cluster infrastructure

None of these combine:

Zero infrastructure
Any Python iterator as source
Direct API joins
Pure Python implementation
Simple deployment

That's what makes Streaming SQL Engine unique.

Start Here: The Most Secure Way

Step 1: Install and Basic Setup

pip install streaming-sql-engine

The Most Stable Join Option

Lookup Join (Default)

What it is: Python hash-based join - the default when no special metadata is provided.

How to use:

engine = Engine()  # use_polars=False (default)
engine.register("products", products_source)
engine.register("images", images_source)
# No special metadata - uses Lookup Join automatically

How it works:

Builds hash index on right side (loads all right rows into memory)
Streams left side row-by-row
Looks up matches in hash index (O(1) per lookup)

When to use:

Default choice - works with any data
Small to medium datasets (< 1M rows)
When right side fits in memory
When data is not sorted

Pros:

✅ Most compatible (works with any data types)
✅ No special requirements
✅ Reliable and stable
✅ Good performance for medium datasets

Cons:

⚠️ Loads entire right side into memory
⚠️ Not optimal for very large right tables

Some recommendations from the author.

Recommendation 1: Use Merge Join if Data is Sorted (Fastest + Lowest Memory)

Best for: Pre-sorted data or data that can be sorted once

Configuration:

engine = Engine(use_polars=False)  # Merge Join requires use_polars=False
engine.register("products", products_source, ordered_by="product_id")
engine.register("images", images_source, ordered_by="product_id")

Why it's best:

✅ Lowest memory overhead
✅ True streaming (both sides stream simultaneously)
✅ Fast execution (comparable to other options)
✅ Can handle datasets larger than RAM

When to use:

Data is already sorted by join key
Memory is a concern
You can sort data once (e.g., sort JSONL files before processing)
Both sides of join are sorted

Example: Preparing Data for Merge Join

# Step 1: Sort your JSONL files (one-time operation)
# Use a utility script or database ORDER BY
python examples/sort_jsonl.py products.jsonl products_sorted.jsonl product_id
python examples/sort_jsonl.py images.jsonl images_sorted.jsonl product_id

# Step 2: Use sorted files with Merge Join
engine = Engine(use_polars=False)
engine.register("products",
    lambda: load_jsonl("products_sorted.jsonl"),
    ordered_by="product_id")
engine.register("images",
    lambda: load_jsonl("images_sorted.jsonl"),
    ordered_by="product_id")

# Step 3: Query - Merge Join will be used automatically
query = """
    SELECT products.product_id, products.title, images.image
    FROM products
    LEFT JOIN images ON products.product_id = images.product_id
    WHERE products.checked = 1
"""

Recommendation 2: Use MMAP Join if Data is NOT Sorted (Low Memory)

Best for: Unsorted data, large files, memory-constrained environments

Configuration:

engine = Engine(use_polars=False)  # MMAP Join requires use_polars=False
engine.register("products", products_source, filename="products.jsonl")
engine.register("images", images_source, filename="images.jsonl")

Why it's best:

✅ Low memory overhead
✅ Works with unsorted data (no sorting required)
✅ OS-managed memory mapping (can handle files larger than RAM)
✅ 90-99% memory reduction compared to loading entire file

When to use:

Data is NOT sorted (or sorting is expensive)
Large files (> 100MB)
Memory-constrained systems
Files larger than available RAM
You want low memory without sorting

Example:

engine = Engine(use_polars=False)

def jsonl_source():
    with open("products.jsonl", "r") as f:
        for line in f:
            if line.strip():
                yield json.loads(line)

# Register with filename parameter to enable MMAP Join
engine.register("products", jsonl_source, filename="products.jsonl")
engine.register("images", images_source, filename="images.jsonl")

Recommendation 3: Use Polars + Optimizations for Best Throughput

Best for: Maximum speed when you can filter early and normalize data types

Configuration:

engine = Engine(use_polars=True, first_match_only=True)

def optimized_source(dynamic_where=None, dynamic_columns=None):
    """
    Source with all optimizations:
    - Filter pushdown (dynamic_where)
    - Column pruning (dynamic_columns)
    - Data normalization (for Polars)
    """
    # Build optimized query
    query = build_query(dynamic_where, dynamic_columns)

    for row in execute_query(query):
        # Normalize types for Polars stability
        yield normalize_types(row)

engine.register("products", optimized_source)
engine.register("images", images_source)

Why it's best:

✅ Fastest execution time
✅ Low memory overhead
✅ Early filtering reduces data volume significantly
✅ Vectorized operations (SIMD acceleration)
✅ All optimizations combined (filter pushdown + column pruning)

When to use:

You can filter data early (WHERE clause can be pushed to source)
Data types are consistent (can normalize)
Speed is priority
Polars is available
You want maximum performance

Trade-offs:

⚠️ Requires protocol support in source function (dynamic_where, dynamic_columns)
⚠️ Requires data normalization for Polars stability
⚠️ Requires Polars dependency
⚠️ Processes fewer rows (because of early filtering)

Summary: Choosing the Right Configuration for 100K+ Records

Follow this decision tree:

Is your data sorted (or can you sort it)?

✅ YES Use Merge Join (ordered_by parameter)
- Best memory efficiency
- Fast execution
- True streaming

Is your data NOT sorted?

✅ YES Use MMAP Join (filename parameter)
- Low memory
- Works with unsorted data
- Good for large files

Do you need maximum throughput and can filter early?
- ✅ YES Use Polars + Optimizations (use_polars=True + protocols)
  - Fastest execution
  - Low memory
  - Requires protocol support

Default fallback: If none of the above apply, use Lookup Join (default Python) - it's stable and works with any data.

Performance

Comparison Table: 100K+ Records (150K+ Joined Rows)

Based on comprehensive benchmarks with 150,048 joined rows (50,089 products with ~3 images each, filtered to checked=1):

Configuration	Rows Processed	Time (s)	Memory Overhead (MB)	CPU %	Throughput (rows/s)
1. Merge Join (Sorted Data)	150,048	159.51	69.50	3.9%	941
2. Lookup Join (Default Python)	150,048	159.44	236.12	1.8%	941
3. Polars Join	150,048	157.74	285.64	4.1%	951
4. MMAP Join	150,048	165.74	77.48	5.0%	905
5. Polars + Column Pruning	150,048	157.72	243.85	3.1%	951
6. Polars + Filter Pushdown	150,048	157.74	239.38	2.0%	951
7. All Optimizations Combined	50,089	54.30	46.68	6.6%	922

Note: Configuration 7 processes fewer rows (50,089 vs 150,048) because it applies filter pushdown early, filtering products with checked=1 before the join. This demonstrates the power of early filtering.

Key Insights for 100K+ Records

Fastest Execution: All Optimizations Combined (54.30s)

Uses Polars Join + Column Pruning + Filter Pushdown + early filtering
Processes 50,089 rows (filtered early) vs 150,048 without filtering
Best for speed priority when you can filter early

Lowest Memory (Full Join): Merge Join (69.50 MB)

True streaming, no index needed
Best for memory-constrained environments with sorted data
Processes all 150,048 rows with minimal memory

Lowest Memory (Unsorted Data): MMAP Join (77.48 MB)

Works with unsorted data
OS-managed memory mapping
Best for large files when data is not sorted

Highest Throughput: Polars + Column Pruning (951 rows/s)

Vectorized operations + reduced I/O
Best for processing large volumes
Processes all 150,048 rows efficiently

Best Balance: Merge Join (159.51s, 69.50 MB)

Good speed with lowest memory overhead
Best for sorted data when memory matters
True streaming architecture

Performance Guide

By Dataset Size

Size	Configuration	Why
< 10K rows	`use_polars=False` (default)	Fastest, most stable
10K-100K rows	`use_polars=False` (default)	Still fastest, handles mixed types
100K-1M rows	See recommendations below	Choose based on data characteristics
> 1M rows	All optimizations	Maximum performance

For 100K-1M rows, choose based on the data:

Data is sorted Merge Join (ordered_by parameter)
Data is NOT sorted MMAP Join (filename parameter) -
Need maximum speed + can filter early Polars + Optimizations (use_polars=True + protocols)

Common Pitfalls

Pitfall 1: Using Polars Without Normalization

Problem:

engine = Engine(use_polars=True)
# Mixed types cause schema inference errors

Solution:

def normalized_source():
    for row in raw_source():
        yield {
            "id": int(row.get("id", 0)),
            "price": float(row.get("price", 0.0)),
        }

Pitfall 2: Using MMAP Without Polars (Very Slow)

Problem:

engine = Engine(use_polars=False)
engine.register("table", source, filename="data.jsonl")  # Very slow!

Solution:

# MMAP uses Polars internally for index building if available
# But you still need use_polars=False for MMAP Join algorithm
engine = Engine(use_polars=False)  # MMAP Join requires this
engine.register("table", source, filename="data.jsonl")
# MMAP will use Polars internally for faster index building

Pitfall 3: Using MMAP for Small Files

Problem:

# MMAP overhead > benefit for small files
engine.register("table", source, filename="small.jsonl")  # Slower!

Solution:

# No filename for small files
engine.register("table", source)  # Faster for < 100MB

Summary

Start Here (Most Secure)

engine = Engine()  # Default: use_polars=False
engine.register("table1", source1)
engine.register("table2", source2)

Why: Most stable, handles all edge cases, works with any data types

Then Experiment

Add debug mode: Engine(debug=True) - See what's happening
Try Polars: Engine(use_polars=True) - For large datasets
Try MMAP: filename="data.jsonl" - For large files
Try Merge Join: ordered_by="key" - For sorted data

Conclusion

The Streaming SQL Engine fills a unique niche: cross-system data integration. While it may not match the raw performance of specialized tools for their specific use cases, it excels at joining data from different systems - a problem that traditional databases cannot solve.

Key strengths:

Cross-system joins (databases, APIs, files)
Zero infrastructure requirements
Memory-efficient streaming architecture
Python-native integration
Automatic optimizations
Simple deployment

Best suited for:

Microservices data aggregation
Cross-system ETL pipelines
Real-time data integration
Memory-constrained environments
Python-native workflows

For cross-system data integration, the Streaming SQL Engine provides a unique solution that balances performance, simplicity, and flexibility.

DEV Community: Theodore P.

Verification-first architecture for language-model repair pipelines.

The Harness Is All You Need

Abstract

Contents

1. When this method applies

2. Terminology

3. Five invariants

I1. Deterministic before generative

I2. The oracle runs the production code path

I3. Every claim is downgraded to a measurement

I4. Precision over recall, deliberately

I5. Decisions are durable

4. Reference architecture

5. Stage A. Attribution

6. Stage B. Compression

7. Stage C. Generation

7.1 Evidence selection is the highest-leverage variable

7.2 The contract

7.3 Determinism

7.4 The critique loop, exactly one round

7.5 A note on reasoning budgets

8. Stage D. The oracle

8.1 Layers

8.2 Gates demote, never promote

8.3 The two gates people forget

8.4 Merging, not overwriting

9. Stage E. The human gate

9.1 The trial

10. Designing the outcome taxonomy

11. Evaluation protocol

11.1 Re-score history with today's oracle

11.2 Define precision to exclude declining

11.3 Two populations, reported separately

11.4 Stratify by difficulty, and say why the strata differ

11.5 One change at a time, with the question written down

12. Diagnosing failure: model or evidence?

12.1 First, categorise every failure mechanically

12.2 Then run the escalation experiment

12.3 Escalate per-slot, never per-instance

13. Cost model

14. Anti-patterns

15. Design checklist

16. Instantiating the method in a new domain

Summary

Your scraper is identified before it sends a single request

1. The handshake is a fingerprint

The fix: impersonate, do not reconstruct

What the handshake cannot do

2. The twenty and the eighty

3. The challenge arrives as a 200

What this changed in practice

The book

Grapple Dojo

Stack Panic

Streaming SQL Engine: Lightweight Cross-Data Source Integration for Resource-Constrained Environments.

Join Data from Anywhere: The Streaming SQL Engine That Bridges Databases, APIs, and Files

The Problem: Data Lives Everywhere

The Solution: Streaming SQL Engine

Example:

Why I Built This: The Problem That Needed Solving

How I Built It: Architecture and Design Decisions

Core Design Philosophy

Why Iterator-Based Architecture?

Expression Evaluation

Join Algorithm Selection

Protocol-Based Optimization

Memory Management

Performance Optimizations

Design Decisions and Trade-offs

The Result

How It Works: Streaming Architecture

Supported Data Sources

Databases

Files

APIs

Custom Sources

SQL Features

Supported Features

Not Supported