SQL · an in-database query plane for GIMS · AGPL on GitHub

Do the calculation where the data already lives.

autoSQL turns a user’s data selections into SQL that runs inside the database, with a comparison path that exposes numerical disagreements.

Extracting data just to reshape it creates an extra boundary to move, manage, and check. GIMS had two Python paths paying that cost. autoSQL moves the computation into the database and tests whether the result still agrees with the original calculation.

Public on GitHubPython compiler · generated SQL runtime · Postgres

autoSQL on GitHub ↗

The problem

Viewing and transforming data requires pulling it out of the database first. Faster application code still pays that extraction cost.

What changed

A compiler turns a declarative selection into SQL. The demo runs the same selection through SQL and Python and flags any row where the two disagree.

Why it matters

A silent wrong number becomes a visible discrepancy. Correctness evidence is available; performance remains a separate measurement question.

Architecture / autosqlThe design behind the outcome

One specification, two places to check the result.

The same specification drives the widget and artifact renderers. A Python compiler emits SQL for PostgreSQL, moving viewing, window creation, and transformation out of application-side post-processing. The side-by-side demo below compares every row and marks any that disagree. The documented timing run remains open; this portfolio does not claim a measured speedup.

The demo the same pick, two answers, every disagreement located

The autoSQL picking screen in the Reconciled state: the pick on the left with nine operations, a green banner reading Both panes agree, to the digit, 10 of 10 identical, the GATE / COMPILE / PROBES / EXECUTE strip, and two answer panes where row edge-01 reads 123 on both sides

autoSQL demo · state 4, Reconciledcaptured 2026-09-25 from a local run against the demo’s own invented database; every record on it is invented, and the screen says so

The row that used to disagree

This state points at key edge-01, column biggest. The two engines once returned different numbers here, and nothing on the SQL side reported an error; on its own it returned a plausible number. Only the side-by-side comparison caught it, which is the entire reason both answers are on screen.

That divergence has since been fixed. Both engines now read the value as 123, and all 10 rows are identical. The comparison still runs on every pick: if a row ever differs again, both panes mark it and the pair opens at it, so a disagreement is located, not merely announced.

What the screen is

The picking column on the left with its nine numbered operations: a source, computed columns, one filter, a sort field, a row cap, an aggregate, time buckets. The verdict banner spanning both panes. The four-stage GATE / COMPILE / PROBES / EXECUTE strip. The generated SQL last and full width: session values set on every connection, the probes, then the pick’s own query.

Seven states are reachable by URL fragment: agreement, time buckets, only what changed, reconciled (the row that used to disagree), refused at the expression (the static gate), refused at the column name, refused while running (the runtime probe). All seven paint against live data.

Both panes agree, to the digit

8 rows compared, 8 identical, value for value, column for column, over the whole result and not the page below it. There is no tolerance anywhere in the comparison: two numbers agree when they are the same number. Both sides round half-up to six decimal places, which is why a value like 48.333333 is the same six digits in both panes rather than nearly the same.

autoSQL demo · state 1, Agreement8 of 8 identical

The design one spec, one compiler, two renderers

Why the expression language is the seam

GIMS’s dashboard expression language is a real tokeniser and parser producing a tagged-tuple AST, not eval. Three properties make it the integration point. It already has two runtimes, one in Python and one in JavaScript, held identical by a shared vector fixture, so a SQL compiler is a third runtime for the same AST with a ready-made equivalence oracle. It stops exactly where autoSQL starts: per-record scalars only; windowing and aggregation are the missing tier. And field types are declared, not guessed, driven by the schema, which is what makes typed compilation feasible.

Artifacts are unaffected by design

Artifact generation is already a stage separate from data acquisition. It keeps making artifacts from templates; it simply receives an exact result set instead of a scanned-and-filtered pile. Same spec, two renderers.

Not everything compiles, and that is reported

A hybrid is required: compile what compiles, fall back in-memory otherwise, and report which happened. A silent fallback would recreate the very problem being solved.

            ┌──────────────── autoSQL ────────────────┐
  UI  ───▶  │  DataSource spec  +  window/aggregate   │
 (pick)     │              ↓ compiler                 │
            │         generated SQL                   │
            └────────────────┬────────────────────────┘
                             │ runs in the DB
                 ┌───────────┴───────────┐
                 ▼                       ▼
        dashboards resolve()      pphrase db_inputs
         (live widgets)                  │
                                         ▼
                                 post_doc + template
                                    (artifacts)

Scope, deliberately

autoSQL owns the read/query plane and owns no writes, no locks, and no invariants. Postgres-as-single-datastore is a different, larger claim, argued against elsewhere, and out of scope here.

Where it stands 2026-09-01, from the project’s own working notes

The correctness thread is closed. The timing question is not.

The compiled SQL and the Python evaluator agree: 0 wrong numbers over 11,367 expressions, contract fixture 130 of 130, and the numbers no longer depend on a Postgres session setting at all. Two directories are the source of truth, the generated SQL runtime and the Python compiler, and everything under spikes/ is frozen evidence with checksums the tests assert.

The first spike ruled no-go on the standalone-compiler-plus-thin-adapter architecture as scoped. Not “impossible”, not “discard the work”: do not fund this on this evidence; run the two follow-up experiments first. It turns on three facts: the resolver in GIMS has no field through which a fallback could ever be reported; the compiled path measured 3.79×–7.15× slower than today’s Python, with no crossover; and 18 of 33 ways the two engines can disagree cannot be detected at query time by any mechanism.

One ticket remains, the timing run, and it is blocked for a measured reason: it needs a quiet machine, because it is measured in absolute milliseconds and numbers taken at an elevated load are not weaker, they are void. It is the last thing between this project and the GIMS gate.

Also learned on the way

A declared field type is not a guarantee about stored content: six of seven GIMS write paths never check it. Any design reaching for a per-path typed expression index must guard it or expect the failure. Written down so it is not rediscovered as a surprise.

Put the engineering in context

Recognize a problem in your own workflow?

Show me one task your team does by hand. In 20 minutes, I’ll tell you if it’s worth automating and what it would cost.

Book a free 20-minute check Explore the other projects →