tutorial · schemas

Build your first schema

Let's build a schema for a product catalog from scratch. We'll start with the smallest possible manifest and add one piece at a time, explaining why each piece is there before we add it.

By the end of this page you'll have a real, working schema with columns, an index, a graph relation, a foreign key, and CHECK constraints. ~15 minutes if you read carefully.

Name the table.

Every schema needs three things at the top: a namespace, a table, and a primary_key. The namespace groups related tables; the table is the name you'll query with; the primary key tells OriginChain which column uniquely identifies a row.

# manifest.toml
namespace   = "shop"
table       = "products"
primary_key = ["id"]

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

We declared id as a string column and marked it required = true so writes that omit it are rejected. The primary_key = ["id"] array names which column (or columns) form the key. For now, single-column.

Add the data columns.

A product needs a name, a category, and a price. Notice how we store price: as price_cents with type i64, not a float. Floats lose precision on multiplication - $0.10 + $0.20 ≠ $0.30 in float arithmetic. Always store money as integer minor units.

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"          # money in minor units - never f64

Add an index for fast filtering.

We'll often want "all products in category X". Without an index, OriginChain has to scan every row. An [[indexes]] block changes that to a direct lookup.

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"

# Without this, WHERE category = ... has to scan every row.
[[indexes]]
name    = "by_category"
columns = ["category"]

Rule of thumb: any column you filter on in WHERE or use in graph relations should be indexed. Indexes have a write cost, so don't add them speculatively.

Link products to suppliers.

Every product comes from a supplier. We could just add a supplier_id column and join on it. But if we declare it as a [[relations]] block, OriginChain treats it as a graph edge - we can later walk "all products from supplier X" with a single graph call, no JOIN needed.

# (everything from step 3, plus:)
[[columns]]
name = "supplier_id"
ty   = "str"

# Declare supplier_id as a graph edge. The row write creates the edge
# automatically; you can later walk it with the /graph endpoint.
[[relations]]
name          = "supplied_by"
from_col      = "supplier_id"
bidirectional = true

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

name is the verb you'll use to walk the edge ("supplied_by"). from_col is the column on this table that points at the target. bidirectional = true means the reverse edge ("which products does supplier X supply?") is also indexed automatically.

Refuse rows pointing at nothing.

A relation alone doesn't enforce that supplier_id actually exists - you could write a product with supplier_id = "ghost-42" and it would happily save. A foreign key rejects that write at the door.

# (everything from step 4, plus:)
[[foreign_keys]]
name          = "fk_supplier"
from_col      = "supplier_id"
target_schema = "shop.suppliers"
target_col    = "id"
on_delete     = "restrict"     # no_action | restrict | set_null

on_delete = "restrict" means the engine refuses to delete a supplier that still has products pointing at it. Other options: no_action (same as restrict at write time), set_null (clears the column on the orphaned rows).

Add data-quality checks.

CHECK constraints reject writes that violate rules. Negative prices and unknown category strings are the kind of bugs that quietly corrupt a database over months. Stop them at write time.

# (everything from step 5, plus:)
[[check_constraints]]
name       = "price_positive"
expression = "price_cents > 0"

[[check_constraints]]
name       = "category_in_set"
expression = "category IN ('electronics', 'books', 'shoes')"

CHECK expressions support = != < <= > >=, AND/OR/NOT, IS [NOT] NULL, and IN (...). They run on every write - keep them simple to avoid write-time overhead.

The finished schema.

Put it all together. This is what you save as manifest.toml and send to OriginChain.

# manifest.toml - the finished schema.
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"

[[columns]]
name = "supplier_id"
ty   = "str"

[[indexes]]
name    = "by_category"
columns = ["category"]

[[relations]]
name          = "supplied_by"
from_col      = "supplier_id"
bidirectional = true

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

[[foreign_keys]]
name          = "fk_supplier"
from_col      = "supplier_id"
target_schema = "shop.suppliers"
target_col    = "id"
on_delete     = "restrict"

[[check_constraints]]
name       = "price_positive"
expression = "price_cents > 0"

[[check_constraints]]
name       = "category_in_set"
expression = "category IN ('electronics', 'books', 'shoes')"

To register it, see Schemas → Register a schema on the overview page. The same POST /v1/tenants/:t/schemas call works whether your schema is 10 lines or 200.

What you can do now.

SQL: SELECT * FROM shop.products WHERE category = 'shoes' - fast because of the index.
Graph: "all products supplied by sup-44" via GET /graph/shop.products/neighbors?rel=supplied_by&pk=sup-44.
Foreign key enforcement: writes with non-existent supplier IDs return 400 fk_violation.
CHECK enforcement: negative prices and unknown categories return 400 check_violation.

Want vector search or full-text search on top? Those live on their own runtime endpoints, not in the schema. See Vector reference and Full-text reference.