Schema design

Collection schemas are durable API contracts. Model fields for validation and policy, not merely for how one screen happens to look.

Example: issue tracker

{
  "name":"issues",
  "fields":[
    {"name":"title","type":"text","required":true},
    {"name":"status","type":"select","required":true,"default":"open"},
    {"name":"priority","type":"number","default":2},
    {"name":"owner_id","type":"relation","required":true},
    {"name":"due_at","type":"datetime"},
    {"name":"metadata","type":"json","default":{}}
  ]
}

Naming

Names begin with a lowercase ASCII letter, contain lowercase letters, digits and underscores, and are at most 63 characters. Treat them like API identifiers: prefer due_at over a display label such as “Due date”. Leading underscores and reserved names are rejected.

Choose field types deliberately

• Use select for a controlled state such as open/closed, not free text.
• Use datetime for comparable timestamps and normalize to UTC.
• Use relation when a value identifies another record, while remembering target-aware physical foreign keys are not yet generated.
• Use json for genuinely variable metadata, not as an escape hatch for every field.
• Use unique only for application invariants; it has write and migration costs.

Defaults and required fields

Defaults must be valid JSON and match the field type. A required field with a sensible default can make additive evolution safe. A required field without a default needs a data backfill before existing rows can satisfy it.

Design for access rules

Store policy-relevant values such as owner_id, team_id or visibility as explicit fields. Hiding ownership inside a JSON blob makes rules, indexes and migrations harder to reason about.

Metadata and storage

Trestle assigns stable internal IDs to collections and fields. User names never become SQL identifiers. The complete metadata definition and physical SQLite table change in one transaction.