Skip to main content
View as Markdown

Indexes & Constraints

Beyond fields, a table can declare a primary key, indexes for query performance and uniqueness, unique-constraint sugar, CHECK constraints for cross-field rules, and composite foreign keys.

Primary Key

Set primaryKey to control how each row is uniquely identified. When omitted, an auto-generated id column is the primary key.

Property Description
type Generation strategy: auto-increment (sequential integers), uuid (random unique ids), or composite (multi-field).
field Field name for a single-column key. Only used with auto-increment or uuid. Must match ^[a-z][a-z0-9_]*.
fields Array of field names for a composite primary key.
app.yaml
primaryKey: { type: auto-increment, field: id }
app.yaml
# Composite primary key
primaryKey: { type: composite, fields: [tenant_id, slug] }

Indexes

indexes is an array of index definitions. Index names must be unique within the table.

Property Description
name Index name. Must match ^[a-z][a-z0-9_]*. Use descriptive names like idx_users_email.
fields Array of field names covered by the index. At least one required.
unique Boolean. When true, enforces uniqueness across the indexed fields.
where SQL WHERE clause for a partial index — indexes only rows satisfying the condition (e.g. deleted_at IS NULL).
app.yaml
indexes:
  - { name: idx_users_email, fields: [email], unique: true }
  - { name: idx_orders_status, fields: [status] }
  # Partial unique index — uniqueness only on non-deleted rows
  - { name: idx_active_slug, fields: [slug], unique: true, where: 'deleted_at IS NULL' }

Unique Constraints (top-level unique)

The table-level unique array is sugar for declaring uniqueness over one or more fields. Single-field entries fold into the equivalent of field.unique = true; multi-field entries become a unique btree index.

app.yaml
unique:
  - { fields: [slug] } # single-field uniqueness
  - { fields: [tenant_id, slug] } # composite uniqueness

CHECK Constraints

constraints enforces complex business rules at the database level with SQL boolean expressions. Constraint names must be unique within the table and match ^[a-z][a-z0-9_]*.

Property Description
name Unique constraint name (lowercase, alphanumeric, underscores).
check PostgreSQL boolean expression that must evaluate to TRUE for valid data.
app.yaml
constraints:
  - { name: chk_price_positive, check: 'price > 0' }
  - { name: chk_end_after_start, check: 'end_date > start_date' }
  - { name: chk_active_members_have_email, check: '(is_active = false) OR (email IS NOT NULL)' }

Composite Foreign Keys

Single-column foreign keys are created automatically from relationship fields. Use foreignKeys for multi-column references to a composite primary key in another table.

Property Description
name Constraint name (lowercase, underscores, max 63 chars).
fields Local columns forming the foreign key.
referencedTable Parent table containing the referenced columns.
referencedFields Columns in the parent table that are referenced.
onDelete Referential action on delete: cascade, set-null, restrict, or no-action.
onUpdate Referential action on update: cascade, set-null, restrict, or no-action.
app.yaml
foreignKeys:
  - name: fk_permissions_tenant_user
    fields: [tenant_id, user_id]
    referencedTable: tenant_users
    referencedFields: [tenant_id, user_id]
    onDelete: cascade
    onUpdate: cascade

Last updated August 11, 2026

Edit this pageReport an issue

This documentation was written with AI, so errors or outdated content are possible. Sovrium is in beta. Contributions and corrections are welcome.

Built with Sovrium