Skip to main content

Table Schema

The table schema section defines one table's columns, keys, constraints, indexes, relationships, computed fields, comments, and more.

--- table:<table_name>:schema

Shape​

name: sessions
columns:
  id: uuid
  worker_id: uuid
  start_time: timestamp
  end_time: timestamp
required:
  - id
  - worker_id
  - start_time
defaults: {}

primary_key:
  columns: [id]

unique: []

foreign_keys:
  - name: sessions_worker_fk
    columns: [worker_id]
    references:
      table: workers
      columns: [id]
    on_delete: restrict
    on_update: cascade
    relationship: worker

constraints:
  - name: end_after_start
    expression: end_time IS NULL OR end_time >= start_time

indexes:
  - name: sessions_worker_id_idx
    columns: [worker_id]
    unique: false

computed_fields:
  duration:
    type: interval
    expression: end_time - start_time
    stored: false

comments:
  table: Work sessions.

Required Fields​

Every table schema must contain:

name:
columns:

All other table schema fields are optional.

Optional Fields​

When omitted, optional fields use these defaults:

required: []
defaults: {}
primary_key: null
unique: []
foreign_keys: []
constraints: []
indexes: []
computed_fields: {}
comments: {}

When optional fields are present but empty, empty collections must be written as [] or {}.

Columns​

columns is an ordered map of column names to type definitions.

columns:
  id: uuid
  name: text
  email: text

The CSV header must contain these columns in this order, plus any stored computed fields.

Types​

Each columns value is either a CSDB type name or a type object. See Types and Values.

columns:
  id: uuid
  name: text
  email:
    type: varchar
    length: 255

Every column must declare a type.

Use the scalar form when a type has no options. Use the object form when the type needs metadata such as length, precision, scale, labels, dimensions, element_type, or type_name.

Examples:

columns:
  username:
    type: varchar
    length: 40

  amount:
    type: numeric
    precision: 12
    scale: 2

  status:
    type: enum
    labels: [draft, active, archived]

  tags:
    type: array
    element_type: text

  matrix:
    type: array
    element_type: integer
    dimensions: 2

  location:
    type: custom
    type_name: geography_point

Required Columns​

required lists columns that may not contain null.

required:
  - id
  - name
  - email

Primary key columns are always required. If a primary key is declared, its columns must appear in required.

Defaults​

defaults maps columns to default values. Columns without defaults are omitted.

defaults:
  hourly_pay: 0
  created_at:
    expression: current_timestamp

Primary Key​

Primary keys are optional. When present, they use one or more columns:

primary_key:
  columns: [id]

Composite primary key:

primary_key:
  columns: [worker_id, work_date]

Primary key columns must be declared in columns and listed in required.

Unique Constraints​

unique:
  - [email]
  - [tenant_id, email]

Each unique entry is a column list. Single-column unique constraints still use a one-item list so the shape stays consistent with composite unique constraints.

Constraints​

constraints:
  - name: pay_nonnegative
    expression: hourly_pay >= 0

Constraint expressions must refer only to columns in the same row.

Indexes​

Supported indexes:

  • Single-column indexes.
  • Composite indexes.
  • Unique indexes.
  • Expression indexes.
  • Partial indexes.

Column index:

indexes:
  - name: sessions_worker_id_idx
    columns: [worker_id]
    unique: false

Composite index:

indexes:
  - name: sessions_worker_start_idx
    columns: [worker_id, start_time]
    unique: false

Expression index:

indexes:
  - name: sessions_duration_idx
    expression: end_time - start_time
    unique: false

Partial index:

indexes:
  - name: sessions_open_idx
    columns: [worker_id]
    where: end_time IS NULL
    unique: false

Unique index:

indexes:
  - name: workers_email_idx
    columns: [email]
    unique: true

Computed Fields​

Computed fields are derived values.

computed_fields:
  duration:
    type: interval
    expression: end_time - start_time
    stored: false

If stored is false, the field is computed by readers. If stored is true, the field must also appear in table data.