Tables / Schema

Your database schema lives in instancez.yaml. When you run inz dev, the migrator watches the file and applies any changes automatically — no migration files to write or track by hand.

Declaring a table

Tables go under the top-level tables: key. Each table gets a name and a list of fields:

tables:
  posts:
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        foreign_key:
          references: auth.users.id
          on_delete: cascade
      - name: title
        type: text
        required: true
      - name: published_at
        type: timestamptz
        default: now()

Every table must have at least one field marked primary_key: true. The migrator will not inject one for you.

Mark two or more fields to get a composite primary key, one PRIMARY KEY (a, b) constraint over the marked columns in field order. Upserts default to the whole key, and onConflict: 'team_id,user_id' targets it explicitly:

tables:
  memberships:
    fields:
      - name: team_id
        primary_key: true
        foreign_key:
          references: teams.id
      - name: user_id
        type: uuid
        primary_key: true
      - name: role
        type: text

Which columns form the primary key is fixed once the table exists. Adding or removing a primary_key flag on a live column fails the migration, and the config API returns 422 primary_key_change. One swap is allowed: drop the old key column and add one new primary_key: true column in the same change (a destructive change, so it needs --allow-destructive). Reordering the key fields is not a change.

Foreign keys are single-column, so an FK can only point at one column of a composite key, and only if that column is unique on its own. Give it a unique index, or Postgres rejects the FK. unique: true on an existing column adds no constraint, so use an index. inz validate warns about this case:

tables:
  devices:
    fields:
      - name: tenant_id
        type: uuid
        primary_key: true
      - name: serial
        type: text
        primary_key: true
    indexes:
      - columns: [serial]   # serials are globally unique
        unique: true
  readings:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: device_serial
        foreign_key:
          references: devices.serial

Field types

The type field accepts standard Postgres type names. The most commonly used ones:

Type	Notes
bigserial	Auto-incrementing 64-bit integer. Use for surrogate PKs.
uuid	UUID. Pair with default: uuid_v7() or default: uuid_v4().
text	Unbounded string. Also accepts varchar(n) and char(n).
int / bigint / smallint	Integer variants.
bool / boolean	True/false.
numeric / decimal	Exact decimal arithmetic.
float / real / double	Floating-point numbers.
date	Calendar date (no time).
time / timetz	Time of day, with or without timezone.
timestamp / timestamptz	Date + time. timestamptz is timezone-aware and usually what you want.
jsonb / json	JSON data. Prefer jsonb for indexing and operators.
bytea	Raw binary data.
inet / cidr	IP address or network.
serial / smallserial	Smaller auto-increment variants.

type is validated against a fixed allowlist (roughly: the integer/serial variants, text/varchar/char, boolean, numeric/decimal/real/double/float, the date/time types, uuid, json/jsonb, bytea, inet/cidr/macaddr, money, the geometric types, tsquery/tsvector, xml, and bit) — not an arbitrary Postgres type name. varchar(n)/char(n)/bit(n) parameterization and [] array suffixes are supported; unlisted types (e.g. citext, hstore, ltree, custom enums) fail validation with unknown type.

Unsupported column features

The schema model has no equivalent for a few Postgres column features. Use bigserial/serial for auto-incrementing keys, and a default (see below) for computed defaults.

Postgres feature	Status	If you migrate from Supabase
GENERATED ALWAYS AS (expr) STORED (computed columns)	Not supported	The column becomes a plain column. Its current values are copied as static data and won’t auto-recompute — the migration flags this as an advisory.
GENERATED ALWAYS/BY DEFAULT AS IDENTITY	Not supported	Values are preserved, but auto-increment is not carried over. Add a default or re-seed a sequence afterward. The migration flags this as an advisory. Prefer bigserial for new tables.
Unlisted / custom types (enums, citext, hstore, …)	Not supported	Blocks the migration with unknown type until the column is changed to an allowed type.

Field options

Option	Type	Description
name	string	Column name. Required.
type	string	Postgres type. Required unless foreign_key is set (see Foreign keys for how the type is picked in that case).
primary_key	bool	Marks this column as the primary key.
required	bool	Adds a NOT NULL constraint.
unique	bool	Adds a UNIQUE constraint.
default	string or literal	Column default. See defaults below.
enum	list of strings	Restricts column values to the given set. Only valid on text/varchar/char types.
foreign_key	object	Declares a foreign key. See Foreign keys.
check	string	Arbitrary SQL check expression added as a CHECK constraint.
min / max	number	Numeric range constraints. Applied as check constraints.
pattern	string	Regex pattern constraint (applied as a check).

Defaults

The default option accepts:

• A literal value: true, 0, "pending"
• One of the allowed SQL functions: now(), uuid_v7(), uuid_v4(), current_date, current_time

Note: uuid_v7() and uuid_v4() currently both generate a v4 UUID (gen_random_uuid()) — true v7 generation isn’t wired up yet. Use whichever name reads better; there’s no behavioral difference today.

- name: status
  type: text
  required: true
  enum: [draft, published, archived]
  default: draft

- name: created_at
  type: timestamptz
  required: true
  default: now()

- name: id
  type: uuid
  primary_key: true
  default: uuid_v7()

Foreign keys

Declare a foreign key with foreign_key.references pointing to table.column or schema.table.column:

- name: user_id
  foreign_key:
    references: auth.users.id
    on_delete: cascade

When foreign_key is the only option on a field, the column type uses a heuristic: UUID for auth.users.id, BIGINT for all other references. The type is not inferred from the actual referenced column.

on_delete options:

Value	Postgres behavior
cascade	Delete child rows when the parent is deleted.
restrict	Prevent deletion of the parent if children exist.
set_null	Set the FK column to NULL when the parent is deleted.

Omit on_delete to default to restrict.

Table schema

By default a table lives in the public Postgres schema. Set schema: on the table to place it elsewhere:

tables:
  events:
    schema: analytics
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true

auth and storage are reserved — they’re owned by the framework, and declaring a table with schema: auth or schema: storage fails validation. Table and column names must start with a lowercase letter and contain only lowercase letters, digits, and underscores, and can’t be a reserved SQL keyword.

Indexes

Add indexes under the table’s indexes: key:

tables:
  posts:
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: author_id
        type: bigint
    indexes:
      - columns: [author_id]
      - columns: [author_id, id]
        unique: true

Set where: for a partial index: where: "status = 'published'".

Set method: to pick the index type (btree default, hash, gin, gist, brin, spgist). A gin index on a tsvector column enables full-text search:

    indexes:
      - columns: [search]
        method: gin

Auto-updated and immutable fields

    fields:
      - name: updated_at
        type: timestamptz
        auto_updated_at: true   # set to now() on every update
      - name: owner_id
        type: uuid
        immutable: true         # updates that change it fail with 400

Both are enforced by one engine-managed BEFORE UPDATE trigger per table, so they apply to every caller, including the secret key. immutable does not affect INSERT, and writing the same value back is allowed. Remove the option and the trigger is dropped on the next deploy.

How migrations work

• Additive changes apply immediately. New tables, new columns, new indexes, new policies — the migrator adds them on the next run.
• Drops need an opt-in under inz serve. Removing a table or column from the YAML drops it, along with its data. inz serve refuses such a plan unless you pass --allow-destructive (or set INSTANCEZ_ALLOW_DESTRUCTIVE=true), and the error names what would have been dropped. inz dev still applies drops without asking, since rebuilding the schema is the normal dev loop, but it logs a warning listing each one. Be careful with watch mode, which re-applies the diff on every save.
• One migration at a time. Instances that share a database take a Postgres advisory lock before migrating. If several boot at once, one applies the change and the rest wait, then find nothing left to do. Each statement waits at most --migrate-lock-timeout (default 5s) for a table lock, so a long-running query makes the migration fail instead of stalling requests. The server keeps the last applied config and reports drift until a later boot or watch reload applies the change.
• Renames are declared, not detected. The migrator compares two versions of your YAML, and a rename looks exactly like a drop plus an add. Use renamed_from: so it renames in place instead. See below.
• The migrator never injects columns. No hidden id, created_at, or updated_at is added. Every column must be declared.
• Dev mode watches for changes. inz dev re-applies the schema diff on every save, so the feedback loop is instant. inz validate checks the YAML for errors without touching the database.

Renaming a table or column

The migrator only sees two versions of your config. If you change a column’s name, the old name is gone and a new one has appeared, which is indistinguishable from deleting one column and adding another. Applied literally, that discards the column’s data.

Declare the previous name with renamed_from: and the migrator issues a rename instead:

tables:
  notes:
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: content
        type: text
        renamed_from: body

That produces ALTER TABLE notes RENAME COLUMN body TO content, so the values stay put. Tables take the same key:

tables:
  articles:
    renamed_from: posts
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true

Renames run before everything else in the migration, so you can rename a table and its columns in one edit, and combine a rename with a type change on the same column.

A few things worth knowing:

• Once the rename has been applied, renamed_from: does nothing on later runs, because the stored config already carries the new name. You can leave it in place or delete it at your next cleanup.
• If the old name never existed, the field is treated as a plain new column.
• If both names exist in the live schema, no rename happens. The migrator will not overwrite a column that is already there.
• Foreign keys follow a rename automatically, since Postgres renames the constraint’s target with the object. Remember to update any references: in your YAML that pointed at the old name.

Renaming is the one schema change you have to declare. Everything else is inferred from the config.

What’s next

• RLS — lock down table access with row-level security policies
• Auth — configure authentication, JWT expiry, and OAuth providers