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.

primaryKey: { type: auto-increment, field: id }

# 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).

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.

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.

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.

foreignKeys:
  - name: fk_permissions_tenant_user
    fields: [tenant_id, user_id]
    referencedTable: tenant_users
    referencedFields: [tenant_id, user_id]
    onDelete: cascade
    onUpdate: cascade