reference · schemas

Schema reference

Every field, every option, every constraint shape. Use this page to look something up; use the tutorial if you want to learn it for the first time.

1. Top-level fields.

Field	Type	Required	Notes
namespace	string	yes	Letter-prefixed, snake_case. Groups related tables.
table	string	yes	Letter-prefixed, snake_case. The table's name. Queries address it as `namespace.table`.
primary_key	string[]	yes	One or more column names that uniquely identify a row. Composite keys are supported in SQL queries; the `/rows/:schema/:pk` shortcut is single-column only.
version	int	no	Monotonic counter. Defaults to `1`. Schema migrations require an incremented value - see migrations.

2. Columns.

One [[columns]] block per column. Three fields per block.

Field	Type	Notes
name	string	The column name. Snake_case. Used as the JSON key in row writes.
ty	enum	One of: `str`, `i64`, `u64`, `f64`, `bool`, `bytes`.
required	bool	Default `false`. When `true`, writes that omit this column are rejected.

Picking the right type for common data

Your data	Use	Why
User ID, order ID, ULID, UUID	str	Travels as canonical text form.
Email, name, category	str	Lowercase emails at write time if you want case-insensitive equality.
Price, amount, balance	i64	Store as minor units (cents, paise). Float rounding errors compound.
Quantity, refundable count	i64	Signed so refunds can be negative.
Timestamp (epoch ms or µs)	u64	Pick ms or µs once per table and stay consistent.
Rate, percentage	f64	Float is fine for ratios where rounding doesn't matter.
Active flag, deleted flag	bool	Single bit.
Compressed blob, encoded payload	bytes	Opaque to the engine.

3. Indexes.

One [[indexes]] block per index. Without indexes, every filter does a full table scan.

[[indexes]]
name    = "by_status"
columns = ["status"]                  # single-column

[[indexes]]
name    = "by_customer_and_time"
columns = ["customer_id", "placed_ms"]  # composite, in declared order

Field	Notes
name	A name for the index. Used in EXPLAIN output.
columns	List of column names. Composite indexes serve left-prefix matches - `[customer_id, placed_ms]` can serve `WHERE customer_id = ?` alone, but not `WHERE placed_ms = ?` alone.

When to add an index: any column you filter on in WHERE or use as a foreign-key target. Indexes cost write throughput - don't add them speculatively.

4. Relations (graph edges).

A relation turns a foreign-key column into a graph edge. The forward and reverse edges are written atomically when you save a row.

[[relations]]
name          = "supplied_by"         # the verb you'll walk in code
from_col      = "supplier_id"         # the FK column on THIS table
bidirectional = true                  # default; reverse edge written atomically

[relations.target]
namespace = "shop"                    # target table's namespace
table     = "suppliers"               # target table's name
pk        = "id"                      # target table's primary-key column

# Self-relations work - direction tags resolve the collision:
[[relations]]
name          = "follows"
from_col      = "followee_id"
bidirectional = true

[relations.target]
namespace = "social"
table     = "users"
pk        = "id"

Field	Notes
name	The edge verb - used as the `rel=` param when walking the edge with the graph endpoint.
from_col	The column on this table that holds the target's primary key.
bidirectional	Default `true`. When true, the reverse edge is also indexed - "who points at this row?" becomes a one-call lookup.
[relations.target] namespace	Target table's namespace.
[relations.target] table	Target table's name.
[relations.target] pk	Target table's primary-key column.

Relations alone do not enforce that the target row exists. For that, also declare a foreign key (next section).

5. Foreign keys.

Reject writes that point at non-existent rows. The integrity check is a single key fetch, so it's fast.

[[foreign_keys]]
name          = "fk_customer"         # optional - defaults to "<from_col>_fk"
from_col      = "customer_id"         # column on THIS table
target_schema = "shop.customers"      # "namespace.table" of the target
target_col    = "id"                  # PK or unique-indexed column on target
on_delete     = "no_action"           # no_action | restrict | set_null

Field	Notes
name	Optional. Defaults to `"<from_col>_fk"`. Used in error messages.
from_col	Column on this table.
target_schema	The target schema as `"namespace.table"`.
target_col	Must be the target's primary key, or a unique-indexed column.
on_delete	`no_action` (default), `restrict`, or `set_null`. `cascade` and `set_default` are not yet supported.

6. CHECK constraints.

Reject writes whose values violate a rule. Checks run on every write - keep them simple.

[[check_constraints]]
name       = "qty_positive"
expression = "qty > 0"

[[check_constraints]]
name       = "country_or_continent_set"
expression = "country IS NOT NULL OR continent IS NOT NULL"

[[check_constraints]]
name       = "status_in_set"
expression = "status IN ('pending', 'paid', 'cancelled')"

Expression grammar

Operator	Example
= != < <= > >=	qty > 0
AND OR NOT	price > 0 AND price < 1000000
IS NULL / IS NOT NULL	description IS NOT NULL
IN (...)	status IN ('pending', 'paid')

Three-valued logic applies: NULL on any operand yields NULL, which fails the CHECK. If a column can be NULL and you want the CHECK to ignore null values, use col IS NULL OR col > 0.

7. JSON extractions.

Pull a value out of a nested JSON row into a queryable column at write time. Useful when your row body is already-structured JSON and you don't want to flatten it manually.

# Pull a derived column out of a nested JSON row at write time.
# The dotted path walks into the JSON; missing fields emit zero values.

[[extractions]]
name = "customer_country"
path = "customer.address.country"
ty   = "str"

[[extractions]]
name = "order_total_cents"
path = "totals.grand_cents"
ty   = "i64"

Field	Notes
name	The derived column's name. Queryable with SQL after extraction.
path	Dotted JSON path walked at write time. Missing fields emit the type's zero value.
ty	One of the six column types.

Extractions are not the same as vector or full-text indexes. There is no [[extractions.fts]] or [[extractions.vector]] block - those live on separate runtime endpoints.

8. Full example.

Every block from this page in one schema.

# A schema using every block at once.
namespace   = "shop"
table       = "orders"
primary_key = ["id"]

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "customer_id"
ty   = "str"
required = true

[[columns]]
name = "product_id"
ty   = "str"
required = true

[[columns]]
name = "qty"
ty   = "i64"

[[columns]]
name = "total_cents"
ty   = "i64"

[[columns]]
name = "placed_ms"
ty   = "u64"

[[indexes]]
name    = "by_customer"
columns = ["customer_id"]

[[indexes]]
name    = "by_product_and_time"
columns = ["product_id", "placed_ms"]

[[relations]]
name          = "bought_product"
from_col      = "product_id"
bidirectional = true

[relations.target]
namespace = "shop"
table     = "products"
pk        = "id"

[[relations]]
name          = "by_customer"
from_col      = "customer_id"
bidirectional = true

[relations.target]
namespace = "shop"
table     = "customers"
pk        = "id"

[[foreign_keys]]
name          = "fk_customer"
from_col      = "customer_id"
target_schema = "shop.customers"
target_col    = "id"
on_delete     = "restrict"

[[check_constraints]]
name       = "qty_positive"
expression = "qty > 0"

[[check_constraints]]
name       = "total_positive"
expression = "total_cents > 0"