Using SQLite generated columns in AI and Go systems

SQLite sits inside more AI and data systems than many teams admit. It shows up in local-first apps, edge workers, offline sync layers, agent memory stores, eval harnesses, and internal tooling. In Go services, it often starts as a test dependency and ends up in production because the operational cost is low and the read path is fast.

Simon Willison notes the sqlite-utils 4.2.1 release and mentions support for SQLite generated columns. That small feature points to a bigger design choice for practitioners. If you treat SQLite as a serious systems component, generated columns help you move data shaping closer to the database, reduce duplicate logic, and tighten invariants.

This matters when you build AI pipelines or business systems with Go. A lot of those stacks store semi-structured data, append event records, and need derived fields for filtering or ranking. If your derived values live only in application code, drift appears fast. One service lowercases a key, another strips punctuation, a third parses JSON in a slightly different way. Generated columns give you one place to define the rule.

Use generated columns to make semi-structured data queryable

AI and blockchain-adjacent systems often ingest messy records. Prompt traces, model outputs, wallet events, contract metadata, and webhook payloads usually arrive as JSON. You still need stable fields for indexing and filtering.

Generated columns solve part of this problem. You store the raw payload, then expose deterministic projections as columns. In SQLite, those projections derive from expressions. Your application writes the source field once. SQLite computes the rest.

A common pattern looks like this:

create table events (
  id integer primary key,
  payload text not null,
  chain_id integer generated always as (json_extract(payload, '$.chain_id')) stored,
  wallet text generated always as (lower(json_extract(payload, '$.wallet'))) stored,
  event_type text generated always as (json_extract(payload, '$.type')) stored,
  ts text generated always as (json_extract(payload, '$.timestamp')) stored
);

create index idx_events_chain_type_ts on events(chain_id, event_type, ts);
create index idx_events_wallet on events(wallet);

This gives you three benefits.

• You keep the source payload intact.
• You query derived values without repeating JSON extraction in every statement.
• You index the derived values directly.

For AI workloads, the same structure works for traces and eval records.

create table eval_runs (
  id integer primary key,
  result_json text not null,
  model text generated always as (json_extract(result_json, '$.model')) stored,
  score real generated always as (json_extract(result_json, '$.score')) stored,
  passed integer generated always as (json_extract(result_json, '$.passed')) stored
);

If you run ad hoc analysis from notebooks, CLIs, or admin panels, these columns cut a lot of friction.

Choose stored or virtual with intent

Generated columns are not one thing. The main choice is stored versus virtual. You should make that choice based on read shape, write rate, and index needs.

Stored generated columns persist computed values on disk. Reads are cheap. Writes pay the compute cost up front. Virtual generated columns compute on read. Storage stays smaller. Repeated queries pay more.

In practice:

• Use stored for fields you filter, sort, or join on often.
• Use stored for fields you plan to index.
• Use virtual for occasional projections in admin tooling.
• Use virtual when the expression is cheap and the table is small.

Where teams go wrong is simple. They add generated columns as if they were free. Then they backfill a large table with expensive expressions and wonder why migration time spikes. Or they leave everything virtual, add dashboard queries, and push CPU cost into every read.

For AI evaluation stores, stored usually fits for model, task, pass_fail, and normalized tenant identifiers. For large prompt archives, token counts derived from application code are often better stored as plain columns during ingestion, unless you have a deterministic SQL-side tokenizer, which many teams do not.

For blockchain event stores, normalize fields such as address casing, block date, topic selectors, and network id into stored generated columns if your queries depend on them. Keep heavier decoding logic outside SQLite unless the SQL expression is simple and stable.

Treat generated expressions as part of your data contract

The expression behind a generated column is application logic. You should version it with the same care as API schemas and migration files.

This is where many Go codebases drift. One package performs normalization before insert. Another relies on SQL functions in a report query. A third rewrites history during a batch repair. The result is subtle inconsistency.

A cleaner pattern is:

1. Store raw input in one or more source columns.
2. Define deterministic generated columns for stable derived values.
3. Index only the generated fields tied to real query paths.
4. Keep the expression text in migrations, not hidden in runtime string assembly.
5. Add tests which verify sample inputs against expected derived values.

In Go, this often means your migration layer owns the expression and your repository layer treats generated columns as read-only. If you use modernc.org/sqlite or a CGO-backed driver such as mattn/go-sqlite3, the SQL design principle stays the same.

A table-driven test is enough:

type row struct {
    payload string
    wallet  string
    kind    string
}

var cases = []row{
    {`{"wallet":"0xAbC","type":"transfer"}`, "0xabc", "transfer"},
    {`{"wallet":"0xDEF","type":"mint"}`, "0xdef", "mint"},
}

Insert sample rows, read the generated fields, and compare outputs. The point is not driver behavior. The point is preserving one source of truth for derivation.

Inspect index plans, not only schema shape

Generated columns help only if the planner uses them well. Many teams stop after defining the schema. They do not verify query plans.

You should inspect:

• Whether predicates hit the generated-column index.
• Whether your expression type matches the comparison type.
• Whether normalized text values match your collation expectations.
• Whether JSON extraction returns strings where you expected numbers.

A quick check:

explain query plan
select id, ts
from events
where chain_id = 1
  and event_type = 'transfer'
order by ts desc
limit 50;

If SQLite falls back to a scan, inspect the generated column type and the index order. Text timestamps sort well only if you store them in a sortable format such as ISO 8601 UTC. Numeric IDs should be numeric, not quoted strings extracted from JSON and left uncast.

For example, this is safer when source JSON is inconsistent:

chain_id integer generated always as (
  cast(json_extract(payload, '$.chain_id') as integer)
) stored

The same issue appears in AI systems. Scores, latency, token counts, and pass flags often arrive as mixed JSON types. If you do not cast them in the generated expression, your filters and aggregates become less predictable.

Another common failure is building too many indexes around every generated field. SQLite stays fast when you index for clear access paths. It slows down writes when each insert updates a wide set of indexes. Start from query logs, then add the minimum useful indexes.

Use SQLite features together, not in isolation

Generated columns work best when paired with a few other SQLite features.

First, use CHECK constraints for values with narrow allowed ranges.

create table prompts (
  id integer primary key,
  payload text not null,
  provider text generated always as (json_extract(payload, '$.provider')) stored,
  check (provider in ('openai', 'anthropic', 'local'))
);

Second, add partial indexes when only a subset matters.

create index idx_eval_failures
on eval_runs(model, score)
where passed = 0;

Third, keep raw JSON for auditability, but avoid pulling large blobs into every hot query. Select the generated columns first, then fetch full payloads only when needed.

Fourth, think about migration safety. On mature tables, introducing generated columns requires a plan for rebuilds, backfills, and compatibility with older clients. If you ship embedded databases in desktop or edge apps, version skew matters. New schema features in the file format and DDL need coordination across your release train.

For blockchain indexing tools written in Go, this combination is useful for decoded log stores, mempool snapshots, and bridge message tracking. For AI products, it fits prompt logs, annotation datasets, and model-eval ledgers. The common thread is simple. Preserve raw records. Project stable fields into generated columns. Index those fields with discipline.

What to watch next

SQLite keeps gaining features which make it a stronger base for small, serious systems. Generated columns are one of those features because they reduce drift between data storage and application logic. If your Go or AI stack leans on SQLite, review where you still recompute the same values in multiple places. Those are the first candidates to move into schema.

Also watch your file-level behavior under production load, especially write contention, migration timing, and query plans after each schema change. The best SQLite designs stay boring because the invariants live close to the data and the hot paths stay easy to inspect.