autoSQL is an in-database query plane for GIMS: an interactive UI where a user picks what data and how to view, window, and transform it, and autoSQL compiles that choice into SQL that executes inside the database.
GIMS reshapes data by materializing it out of the store and running the transformation in Python. Two modules do this independently, prepositional phrases and dashboards, and both are bottlenecked by the same boundary. The bottleneck is the materialization boundary, not Python: an infinitely fast script still pays extraction. autoSQL moves the boundary rather than optimizing what sits behind it. Data viewing, window creation, and transformation all stop being Python post-passes.
Public on GitHubPython compiler · generated SQL runtime · Postgres
Of 10 rows compared, 1 differs. Key edge-01, column biggest: SQL says 1, Python says 1e+300. Nothing on the SQL side reported an error; on its own it returned a plausible number. That is the finding, not a fault the demo suffered, and it is the entire reason both answers are on screen.
Both panes are marked on that row, and the pair opens at it: a disagreement is located, not merely announced.
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, disagreement, refused at the expression (the static gate), refused at the column name, refused while running (the runtime probe). All seven paint against live data.
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.
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.
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.
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)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.
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.
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.