Schema for Materialized views.

schema · materialized views

MVs read against schemas that already exist. The schema TOML doesn't carry MV declarations — you register an MV via a separate HTTP endpoint, pass the SQL query in the body, and the engine snapshots the rows. Refresh re-runs the query on demand.

Engine surface: POST /v1/tenants/:t/sql/materialized-views { name, query } · POST /…/:name/refresh · GET /…/:name.

Required schema fields.

Without these, this query surface doesn't function at all.

field	effect
(the underlying SQL query's schemas — namespace + table + primary_key + [[columns]])	MVs are reads against existing schemas. No extra TOML fields are added to the source tables.

Optional fields — what each one unlocks.

Add only the fields whose effect you need. Each one buys a specific capability — speed up a predicate, guard a write, or unlock a new query shape.

field	type	default	effect
POST /sql/materialized-views body { name, query }	object	—	Registers a precomputed view. v0 has a ONE-MV-per-tenant cap; this lifts in v0.7.
POST .../:name/refresh	—	—	Re-runs the underlying SQL and lands the snapshot in one WAL frame.
GET .../:name	—	—	Reads the materialised rows back as JSON.

What you can call.

Any SQL query that compiles + executes can back an MV — same SQL grammar, same planner
Refresh is on-demand (no auto-refresh schedule in v0)
MV reads bypass the planner — directly returns the cached rows

Current limits.

The engine returns a typed 400 with a hint instead of running these. Knowing them up front avoids a debugging round-trip.

shape	why
More than one MV per tenant in v0	Engine carries a singleton manifest cache. Second install collides with the first's schema descriptor. Lifts in v0.7.
Auto-refresh on a schedule	Not in v0 — call POST /:name/refresh from your own scheduler (cron, EventBridge, etc).
MV on a query that uses unsupported SQL shapes	If the underlying query 400s (e.g. UNION), the MV install 400s too. Compile the query as a plain SELECT first.

Abbreviation legend.

token	meaning
MV	Materialized view — a SQL query whose rows are pre-computed and stored as a snapshot
refresh	Recompute the snapshot by re-running the underlying query and overwriting the stored rows
singleton cap	v0 limit: one MV per tenant. Lifts in v0.7
WAL frame	Write-ahead log unit. MV refresh writes the entire snapshot in one atomic frame

Worked example.

Schema TOML — copy + register via POST /v1/tenants/:t/schemas with Content-Type: text/plain.

# MVs do NOT change the schema TOML of the source tables.
# Source schemas just need to be SQL-queryable in the normal way:

namespace   = "shop"
table       = "products"
primary_key = ["id"]

[[columns]]
name = "id"          
ty = "str" 
required = true
[[columns]]
name = "name"        
ty = "str"
[[columns]]
name = "category"    
ty = "str"
[[columns]]
name = "price_cents" 
ty = "i64"

Queries it enables.

# Install (the body's "query" field can be any compilable SELECT)
curl -X POST $BASE/v1/tenants/$T/sql/materialized-views -H "Authorization: Bearer $BEARER" \
  -H "Content-Type: application/json" \
  -d '{
    "name":  "mv_products_only",
    "query": "SELECT id, name, price_cents FROM shop.products"
  }'

# Refresh — recomputes the snapshot, one WAL frame
curl -X POST $BASE/v1/tenants/$T/sql/materialized-views/mv_products_only/refresh -H "Authorization: Bearer $BEARER" \
  -d '{}'

# Read the snapshot
curl $BASE/v1/tenants/$T/sql/materialized-views/mv_products_only -H "Authorization: Bearer $BEARER"