The schema

schema.dbml describes the tables in DBML. blendx reads it with its own parser, which accepts the part of DBML listed in packages/spec/dbml.md; anything else is an error with its line and column. From it, blendx generate writes the Drizzle tables in src/generated/schema.gen.ts, and blendx derives every default: what each action accepts, how rows are loaded, what a reply holds.

The schema says what the data is. Who may see or change it, and which actions exist, is not in the schema: that is what blends say.

Names

Names are kept as written, so use snake_case: user_id is user_id in the database, in TypeScript and in JSON. Table and enum names must be identifiers that are free in generated code; blendx generate says which ones are not.

Conventions

These are the generated columns: a request cannot set them (it gets a 422), and a calculate hook that returns one fails.

Types

Timestamps are not ISO 8601: they come back with a space and, for timestamp, no offset, exactly as PostgreSQL writes them. As input, a client may send a timestamp back in that form, or in ISO 8601. Anything else PostgreSQL would read, such as yesterday or now, is refused with a 422, so what an input means never depends on the database's settings. Index filters on date and timestamp columns take the same forms.

The full list of type spellings, and the settings each column takes, is in packages/spec/dbml.md. The input rules have ids and tests: packages/spec/derivation-rules.md.

Required, optional, null

For store:

• A not null column without a default is required.
• A column with a default is optional, and the database fills it when it is left out.
• A nullable column is optional, and accepts null.

For update every column is optional: a PATCH sends only what changes. Neither accepts keys that are not columns, nor generated columns.

Keys, references and indexes

Table expenses {
  id int [pk, increment]
  user_id int [not null, ref: > users.id]
  status expense_status [not null, default: 'draft']
  spent_on date [not null]

  indexes {
    status
    spent_on
  }
}

• unique: a value that already exists answers 409, with a pointer to the column.
• ref (a foreign key): a value that refers to no row answers 422, pointing at the column. Destroying a row that other rows still reference answers 409, unless the reference cascades (ref: > users.id [delete: cascade] in a standalone Ref, or a delete: setting).
• Every primary key, unique, foreign key and indexed column can filter and sort an index: GET /expenses?user_id=1&status=draft&sort=-spent_on. To make a column filterable, index it.

A many-to-many reference (<>) is refused: add a join table. Schemas other than public are out of scope for now.

Enums

Enum expense_status {
  draft
  submitted
  approved
  rejected
}

An enum column accepts only its values. schema.gen.ts exports each enum under its name, so a rule can reuse the values: z.enum(expense_category.enumValues).

Changing the schema

1. Edit schema.dbml.
2. bunx blendx generate. If a blend refers to something that changed (a column in hidden, pick or allow.owner), tsc points at it.
3. bunx blendx migrate generate --name add_review_note, and read the SQL it wrote in drizzle/<timestamp>_add_review_note/migration.sql. When a column disappeared and another appeared, drizzle-kit asks whether it was renamed.
4. bunx blendx migrate up, or restart a server that migrates when it starts.

The migrations are generated: never edit them. To undo a change, change the schema back and generate the next migration.

Expand, backfill, contract

Add a required relationship in three deployable changes. For example, to give every order note an author:

1. Expand: add a nullable author_id int [ref: > users.id] to order_notes, generate and apply its schema migration, and deploy code that writes the value for new rows. Leave the column nullable while old rows and older application instances still exist.

2. Backfill: add one immutable data step whose filename is its versioned ID, then run bunx blendx migrate up. Schema migrations run before this step, so it can use the new column:

   // data-migrations/20260926_backfill_order_note_authors.ts
   import { dataMigration } from 'blendx';
   import { sql } from 'blendx/drizzle';
   
   export default dataMigration({
     id: '20260926_backfill_order_note_authors',
     async up({ tx }) {
       await tx.execute(sql.raw(`
         update order_notes
         set author_id = orders.user_id
         from orders
         where order_notes.order_id = orders.id and order_notes.author_id is null
       `));
     },
   });
   

   The transaction records the ID only if the update commits. Never edit a completed step; add a higher-versioned correction if needed.

3. Contract: after the backfill succeeds everywhere and all deployed writers supply author_id, make it [not null], generate the final schema migration, and deploy it. The constraint is last so a rolling deployment cannot reject a still-old writer or an unfilled row.