sqlite-predict

Prediction as a SQL primitive. forecast(), detect_anomalies(), and predict() become SQL functions. The core is one small C99 file with no dependencies, so it runs wherever SQLite already does: in your app, on a phone, in the browser, in the per-database state an AI agent keeps.

.load ./predict0

-- forecast() is an aggregate: your statement supplies the rows, WHERE and
-- joins and bound parameters compose, and GROUP BY splits the series.
-- Each group returns one JSON document: {"model", "status", "rows"}.
SELECT city, forecast(ts, value, 24) FROM readings GROUP BY city;

-- want typed rows instead? expand the document in SQL:
SELECT r.* FROM forecast_rows((SELECT forecast(ts, value, 24)
                               FROM readings)) AS r;
-- ┌──────┬──────────────────────┬──────────┬─────────────┬─────────────┬────────┐
-- │ step │  forecast_timestamp  │ forecast │ lower_bound │ upper_bound │ status │
-- └──────┴──────────────────────┴──────────┴─────────────┴─────────────┴────────┘

None of this ships your data to a cloud model. It runs in-process, on CPU, in microseconds. And when a stronger model (your own trained model, or a foundation model you are licensed to distill) is too heavy to call per query, you distill it once into a tiny native student that lives inside your database and runs anywhere.

Is it accurate?

Yes, and the numbers are measured and reproducible (benchmarks/), not a demo on a toy series.

• Forecasting. A Chronos foundation model distilled into a zero-dependency DLinear/TiDE student reaches 0.89 MASE on m4_hourly, closing ~70% of the gap from the seasonal-naive floor (1.11) to the foundation model itself (~0.80), and beating every classical method (Theta 1.03). The student is a few kilobytes and serves in microseconds. (details)
• Anomaly detection. The sub-pca detector scores at the published state-of-the-art level (~0.44 median VUS-PR) on TSB-AD-U, the reliable univariate benchmark, about 2x a forecast-residual detector on the typical series.
• Tabular. Distilling TabFM into a gradient-boosted student matches or beats tuned XGBoost on most TabArena tasks, at a couple of kilobytes and microseconds per row (an evaluation under TabFM's non-commercial license; see the license note below).
• TabPFN, including the teacher you can actually ship. Same subset: the latest TabPFN-3 beats xgboost on 49/51 tasks zero-shot (~1.5 s/call on Apple GPU), and distilling TabPFN-2, whose license permits commercial distillation, into our ~100 KB student costs a median half point of accuracy and serves in microseconds with no Python and no runtime.
• Calibrated uncertainty. On smooth data the default Gaussian prediction band is overconfident (measured 0.57 coverage at a nominal 0.90); the conformal option lands at the nominal level, and backtest() lets a caller verify coverage on its own data locally.

All of it runs on CPU, in-process, with no network and no GPU.

Warning

Pre-1.0. The SQL surface and option keys may change between minor versions; the changelog documents every break. What survives upgrades: your stored students and the model registry. The on-disk formats are versioned and stay servable, and migrations are idempotent.

Distill a model into your database

A strong model is usually a deployment problem: your production pipeline needs Python and a container, and a foundation model is too slow to call per query and too heavy to ship inside an app. Distillation is sqlite-predict's answer, and it is a SQL primitive like everything else: run the teacher once, compress what it learned into a native student a few kilobytes big, and serve that student in microseconds from the zero-dependency core.

Any teacher works, and the common ones rank by what you may ship:

1. Your own labels or your own model's predictions: the default path, with no license question at all. Compress the XGBoost or sklearn pipeline you already trust into a student that serves inside SQLite, no Python at serve time.
2. Permissively licensed foundation models: Chronos (Apache-2.0) for time series; TabICL (Inria's BSD-3 model, which matches TabPFN-2 on our suite), TabPFN-2 (Prior Labs License, distillation expressly permitted with attribution), and Mitra (Amazon, Apache-2.0) for tabular.
3. Evaluation-only frontier models (TabFM, TabPFN-3): benchmark them zero-shot to see what accuracy is on the table; distilling them for commercial use requires a license from their vendor.

The student is not a file on the side. It is a row in your database, so it snapshots, forks, branches, and syncs with the data it predicts, and it travels wherever the database file goes. An agent that distills a model owns that model the same way it owns its tables.

-- once: distill. The target column holds your labels, or your existing
-- model's predictions (which compresses that model into the database).
SELECT model_id, holdout_metric FROM distill_predict(
  'SELECT tenure, spend, churned FROM customers',
  '{"target":"churned","student_id":"churn-v1","student_kind":"gbt"}');

-- forever: serve the student per row, in microseconds, no runtime attached
SELECT id, predict('churn-v1', tenure, spend) FROM customers;

-- the same move for time series: distill a Chronos-class teacher's forecasts
-- into a DLinear/TiDE student, then serve it through forecast()
SELECT forecast(ts, value, 24, '{"model":"traffic-v1"}') FROM readings;

The numbers above are this mechanism measured: the distilled forecast student reaches 0.89 MASE where the teacher sits at ~0.80 and every classical method is above 1.0, and the distilled gbt student matches or beats tuned XGBoost on most TabArena tasks. The Models section covers the student architectures and soft-label distillation; the distillation guide walks the whole flow.

Why

Prediction is becoming a query primitive. BigQuery has AI.FORECAST, Databricks has ai_forecast(), Snowflake has ML functions. All of them run in the cloud, with your data shipped to the model. sqlite-predict brings the same shape to the database that already runs everywhere else: in apps, on phones, in the browser, and in the per-database state AI agents increasingly keep.

That last one is the point. Agents are getting a database each. This is the layer that lets an agent forecast its own metrics, flag anomalies in what it observes, and predict outcomes, right where its state already sits.

Two things follow from being a SQL primitive instead of a service. It is permissively licensed (MIT/Apache-2.0), so you ship it inside your product rather than rent it. And it composes with the rest of the in-database AI toolbox: sqlite-vec gave SQLite vector search; this gives it prediction.

Agent skills

For agents, the SQL surface is the whole API, so what ships in skills/ is judgment, not wrappers: four agentskills.io-format skills covering core usage, provenance receipts (record what was asked, which model answered, and a hash that replays; serving determinism makes this verifiable), the distillation lifecycle (verify before serving, re-distill on drift, license discipline), and backtest interpretation. Install them into any skills-capable agent, or read them as the condensed operator's manual.

Operations

Function	Question	Returns
forecast(ts, value, horizon [, options])	Where is this metric going?	an aggregate over your rows: forecast steps with prediction intervals, one JSON document per group
detect_anomalies(ts, value [, options])	Which points are abnormal?	an aggregate over your rows: per-point anomaly probability and interval, one JSON document per group
backtest(ts, value, horizon [, options])	How accurate is the model here?	an aggregate over your rows: per-fold MAE, RMSE, MASE, sMAPE and coverage from rolling-origin evaluation
fit(f1, ..., fN, label [, options])	Train a model on labeled rows	an aggregate over your rows: a native tabular student, registered by id or returned as a blob
predict(model, f1, ..., fN [, options])	Classify or regress a row	a scalar: one prediction per row from a fitted student
distill_predict(train_query [, options])	Compress a slow teacher into a fast student	a tiny native model (decision tree, gradient-boosted forest, or MLP), registered and served by predict
distill_forecast(train_query [, options])	Compress a forecast foundation model into a fast student	a native DLinear/TiDE forecast net (a linear skip plus a small residual), registered and served by forecast

One rule for the whole surface. forecast, detect_anomalies, backtest, and fit are aggregates: your statement supplies the rows, so WHERE, joins, bound parameters, and GROUP BY all compose naturally, from the CLI or from an ORM (Drizzle, SQLAlchemy, Diesel). The first three are pure functions: nothing is written, so they run on read-only databases and inside views. Each returns one JSON document; expand it with forecast_rows() / anomaly_rows() / backtest_rows(), or JSON.parse it in your app. predict is a scalar, one prediction per row, so it drops into any SELECT beside your other columns. Each language's getting-started guide shows the pattern through its native ORM (SQLAlchemy, Drizzle, Diesel).

distill_predict, distill_forecast, and predict_batch take read-only SELECT queries (training needs labeled or windowed row sets, batched serving re-runs a query); their results are ordinary rows you can join, filter, and materialize. predict_batch(train_query, apply_query, ...) scores the rows of apply_query, which is required; train_query is optional, used only by in-context models, and is NULL when serving a registered model: predict_batch(NULL, apply_query, '{"model":"..."}'). Options everywhere are a trailing JSON object ('{"confidence_level":0.9}').

Calibrated intervals, auto-selection, and backtesting

• Auto-selection is the default. A call with no model option picks the model with the lowest rolling-origin error per series, deterministically, over the bundled statistical models plus every eligible registered forecast student: distill once and your model competes with the baselines automatically, at every call site. '{"candidates":[...]}' narrows the pool; '{"model":"theta-classic"}' pins one. The result document's model field reports the winner.
• Conformal intervals. '{"interval_method":"conformal"}' replaces the default Gaussian band with a distribution-free one calibrated on out-of-sample residuals. On smooth data the default band is overconfident; conformal lands at the nominal coverage. (Statistical models only; foundation-model students already emit their own quantile band.)
• backtest(). Rolling-origin evaluation with per-fold MAE/RMSE/MASE/sMAPE and interval coverage, so a caller can score its own forecasts and validate conformal coverage locally. folds sets the number of origins; gap inserts a leakage guard between train and test.

-- confirm the conformal band actually covers at the nominal 90%
SELECT avg(coverage) FROM backtest_rows((SELECT backtest(ts, value, 6,
  '{"interval_method":"conformal","confidence_level":0.9,"folds":25}') FROM readings));

Models

The default build is pure C with no dependencies. It ships small, well-studied statistical models:

• theta-classic (the Theta method) and stub-seasonal-naive for forecast() and detect_anomalies(); tsb (Teunter-Syntetos-Babai) for intermittent / sparse-demand forecast() (rare events: errors, retries). auto picks per series by rolling-origin error over these plus every eligible registered forecast student; a candidates list narrows the pool
• sub-pca for detect_anomalies(ts, value, '{"model":"sub-pca"}'): a subsequence-reconstruction detector (windowed PCA reconstruction error), the method family that leads the TSB-AD-U benchmark; ~2x the residual detector on the typical series there
• knn5-incontext (z-scored 5-NN) for predict_batch()

Foundation models are treated as teachers, not serving paths: in benchmarking they were far too slow to call per query on CPU. The path to their accuracy is distillation into a small native student that runs in the zero-dependency core with no onnxruntime and serves in microseconds. distill_predict() does this for the tabular side (the teacher we benchmark is TabFM, see benchmarks/results/tabarena-full.md), and distill_forecast() does it for time series: a Chronos teacher distilled into a native DLinear/TiDE student (a direct linear map plus a small nonlinear residual, the architecture that actually fits seasonal data where a plain MLP does not) closes most of the gap to the FM (see benchmarks/results/forecast.md).

By default distill_predict() trains directly on the target column. That column can hold your labels, or a strong teacher's predictions computed offline: run the teacher once over your training rows on a GPU box, store what it predicts, and distill compresses it into a student that runs anywhere (example above). Pass a teacher to instead relabel the rows with a registered model first (for example, '{"teacher":"knn5-incontext", ...}' compresses the in-context k-NN into a standalone tree).

Teacher licenses govern what you may distill. A distilled student is derived from its teacher, and some model licenses restrict that: TabFM's non-commercial license, for example, permits testing and evaluation (which is what our benchmarks are) but expressly excludes distilling it for commercial use or deploying its outputs in production. Chronos is Apache-2.0. TabPFN is version-dependent: the TabPFN-2 weights are under the Prior Labs License, which permits distillation, including commercially, if you attribute ("Built with PriorLabs-TabPFN" and a TabPFN-prefixed model name) when you distribute the student, while the later TabPFN-2.5/2.6/3 weights are non-commercial. Your own labels or models carry no such limits. The extension's license gate (accept_license) makes a restrictively-licensed teacher an explicit opt-in rather than an accident.

Important

The license descriptions above are our lay summaries as of mid-2026, not legal advice. Model licenses change between versions and over time, and how they apply depends on your use. Before distilling any third-party model or shipping a student derived from one, read the license that came with the exact weights you are using, and consult your own counsel where it matters. Compliance is your responsibility, not something this tool or its documentation can determine for you.

Three student kinds: 'tree' is a single decision tree (a few kilobytes, interpretable); 'gbt' is a gradient-boosted forest with second-order (Newton) leaves that on the TabArena benchmark matches or beats tuned XGBoost on most tasks; 'mlp' is a one-hidden-layer neural net (classification only) for boundaries an axis-aligned tree ensemble renders poorly. All three run in the zero-dependency core and are deterministic. Distillation feature columns must be numeric: encode categorical text before distilling (the in-context knn5-incontext handles text features itself; the distillers do not).

When the teacher gives calibrated probabilities (as a foundation model does), distill its whole distribution instead of its hard label with proba and classes: name the per-class probability columns, and the gbt student matches the teacher's soft targets rather than its argmax, keeping the calibration a hard label throws away.

-- distill the teacher's predicted probabilities (columns p_stay, p_churn),
-- while the true `churned` label scores the holdout
SELECT model_id, holdout_metric FROM distill_predict(
  'SELECT tenure, spend, p_stay, p_churn, churned FROM scored',
  '{"target":"churned","proba":["p_stay","p_churn"],
    "classes":["stay","churn"],"student_id":"churn-soft"}');

An opt-in ONNX build (make loadable-onnx) runs exported models through onnxruntime. Point predict_register() at a model file and call it by name; the io_spec is read off the model (input/output tensors, output kind, class count), so the common case is one line:

SELECT predict_register('churn', '/models/churn.onnx');
SELECT * FROM predict_batch(NULL, 'SELECT id, tenure, spend FROM customers',
                            '{"model":"churn"}');

Feature columns map by position (apply-query order); pass an explicit io_spec only to override: real class labels, a named-feature mapping, or a model whose tensors introspection can't disambiguate. It serves two shapes, with a cached session and batched inference:

• vector: a self-contained model (a distilled student, or any exported tabular classifier/regressor) mapping a feature vector to a prediction. A bare weights path is enough.
• in_context: a teacher that ingests the train_query rows as context on each call and labels the apply_query rows against them, the way TabFM works. Register it with the weights path plus a target (the training label column, which introspection can't infer): predict_register('t', '{"weights_uri":"/m.onnx","target":"label"}').

Both run on CPU today. A GPU build (make loadable-onnx-gpu) adds the CUDA and TensorRT execution providers and fp16/int8 precision; it needs an onnxruntime-gpu install, is compile-checked in CI, and its GPU execution is validated on a dedicated GPU job. Provider selection is explicit and fails loud: asking for cuda on a build without it errors, never a silent drop to CPU. The default build never links onnxruntime at all.

Installing

Python (the fastest way to try it):

pip install sqlite-predict          # also: npm install sqlite-predict, cargo add sqlite-predict

import sqlite3, sqlite_predict

db = sqlite3.connect("app.db")
sqlite_predict.load(db)
db.execute("SELECT forecast(ts, value, 24) FROM readings")

Or drop the extension into any SQLite client directly. Three ways, in order of least effort, all producing a loadable you open with .load:

1. Precompiled binary. Download predict0-<os>-<arch>.{so,dylib,dll} for your platform from a release (checksums in SHA256SUMS), then:

.load ./predict0
SELECT predict_version();

2. Single-file amalgamation. Grab sqlite-predict.c from a release (or make amalgamation) and compile the whole zero-dependency core from one file, no build system or vendored headers:

cc -O3 -fPIC -shared sqlite-predict.c -o predict0.so   # loadable, or
cc -c -DSQLITE_CORE sqlite-predict.c                   # static, into your app

3. From source.

make loadable        # builds dist/predict0.{dylib,so,dll}

make fetches the SQLite amalgamation headers into vendor/ on first build; it needs a C99 compiler. From any SQLite client:

.load ./dist/predict0

For the ONNX serving path, install onnxruntime (macOS: brew install onnxruntime; Linux: extract an onnxruntime release and point ONNXRUNTIME_PREFIX at it) and build make loadable-onnx. For GPU execution, point ONNXRUNTIME_PREFIX at an onnxruntime-gpu install and build make loadable-onnx-gpu.

How it works

forecast, detect_anomalies, backtest, and fit are SQL aggregate functions; predict is a scalar; distill_predict, distill_forecast, and predict_batch are eponymous table-valued functions (virtual table modules) over the rows of an inner query (predict_batch takes two: an optional train_query and the required apply_query). Statistical models run in-process on CPU and are deterministic. Registered models and distilled students live in one _predict_models table the extension manages, content-addressed so the exact bytes are pinned and verified before deserialization.

See ARCHITECTURE.md for the design and SECURITY.md for the trust model.

Development

make loadable            # build the loadable extension
make test                # pytest suite (uses uv)
make test-asan           # AddressSanitizer + UBSan over the C soak driver
make test-valgrind       # real valgrind in a Linux container (needs Docker)
make fuzz                # libFuzzer (needs an LLVM with the fuzzer runtime)
make fuzz-docker         # libFuzzer + ASan in a Linux container

CI builds and tests on Linux and macOS (the full pytest suite, plus AddressSanitizer, UBSan, valgrind, and a libFuzzer smoke run), builds the loadable DLL on Windows through MinGW, and compiles to WebAssembly with emscripten and runs it under node. The Windows and wasm checks drive a standalone C soak driver that exercises every operation, so "runs anywhere" is a thing CI proves rather than a thing the README claims.

Contributions are welcome; see CONTRIBUTING.md.

License

Licensed under either of MIT or Apache-2.0 at your option (SPDX-License-Identifier: MIT OR Apache-2.0). Unless you state otherwise, any contribution you intentionally submit for inclusion shall be dual-licensed as above, without additional terms.

Acknowledgements

Structure and spirit follow Alex Garcia's sqlite-vec. Built on SQLite. Our recommended tabular teacher is TabICL from the SODA team at Inria, the scikit-learn lineage carried into foundation models; Chronos (Amazon) is the time-series teacher our forecast students distill from, and Mitra (Amazon) is the second permissively licensed tabular teacher we benchmark. The out-of-fold teacher-labeling technique in our benchmark harness comes from Lexsi Labs' "Pocket Foundation Models". The TabPFN line is the intellectual lineage for in-context tabular models, and Google's TabFM is among the models our benchmarks evaluate.