Quickback Docs

Invariants - Rules the Database Enforces

Declare permanent rows, append-only rows, frozen states, parent locks, reference requirements, restrict-while-referenced, refused writes, derived columns and balanced journals. Each compiles to database triggers that hold on every write path, including a buggy action.

invariants: declares record rules that the database enforces. Each rule compiles to compiler-owned SQLite triggers, so it holds on every write path: CRUD routes, batch routes, actions writing through the scoped db or unsafeDb, changesets, cascades, queue and cron handlers, and raw SQL on the binding. A bug in an action can't get past it.

Guards, access and transitions decide what the API accepts. Invariants decide what the data may become. Use both: guards shape the routes, and invariants are the backstop.

// features/books/bills.ts
import { feature, q } from '@quickback/compiler';

export default feature('bills', {
  columns: {
    id:             q.id(),
    status:         q.enum(['draft', 'posted', 'void']).default('draft').required(),
    totalCents:     q.int().required(),
    memo:           q.text({ maxLength: 200 }).optional(),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  guards: { createable: ['totalCents', 'memo'], updatable: ['totalCents', 'memo'], protected: { status: ['post', 'void'] } },
  read:   { access: { roles: ['owner'] } },
  create: { access: { roles: ['owner'] } },
  update: { access: { roles: ['owner'] } },
  transitions: {
    post: { field: 'status', from: 'draft', to: 'posted', access: { roles: ['owner'] } },
    void: { field: 'status', from: ['draft', 'posted'], to: 'void', access: { roles: ['owner'] } },
  },
  invariants: {
    permanent: { message: 'bills are never deleted; void the bill instead' },
    frozen: [
      { when: { status: 'posted' }, allow: ['status'] },
      { when: { status: 'void' } },
    ],
  },
});

A refused write returns 422 TRIGGER_REJECTED on every path. The rule's message is the error text, and details says where it was refused:

{
  "code": "TRIGGER_REJECTED",
  "error": "this bills row is frozen in its current state",
  "details": { "code": "RECORD_FROZEN", "table": "bills", "rule": "frozen", "fields": ["totalCents"], "field": "totalCents", "id": "bil_8KQ2M" }
}

Every rule takes an optional code (uppercase letters, digits and underscores) and message, so you can keep the codes your clients already read. In an action, the refusal reaches the caller the same way. Inside a db.batch it rolls the whole batch back, and the response carries details.committed: false. What a refusal says lists every field.

The rules

RuleHoldsDefault code
permanentNo hard delete and no soft delete. Never a cascade target.RECORD_PERMANENT
appendOnlyNo update after insert, except one-time setOnce stamps.RECORD_APPEND_ONLY
frozenFields stop changing once the stored row matches when (or always).RECORD_FROZEN
lockedByChild rows can't be inserted, changed or deleted while the parent matches when.PARENT_LOCKED
requires (on .references())The referenced row must match a condition.REFERENCE_REQUIREMENT
restrictNo delete, listed-column change or state entry while matching rows point here.RESTRICTED_BY_REFERENCE
refuseAn insert, update or delete is refused when a condition on the row, or on the change itself, holds.WRITE_REFUSED
derivedA column equals an aggregate of child rows. Only the database writes it.DERIVED_COLUMN
balancedΣ debit = Σ credit per row across its lines, when each write commits.UNBALANCED

Conditions

when, where and target take a row condition: an object of column tests that must all hold.

TestMeans
status: 'posted'equal (null-safe: null matches a null column)
status: ['posted', 'void']one of
status: { not: 'draft' } / { not: ['a', 'b'] }anything else, including null
voidedAt: { null: true } / { notNull: true }null / not null
proposalId: { ref: { status: 'sent' } }the FK's row matches (one hop per ref; nest them for more)
any: [cond, cond] / all: [...] / none: [...]one of the conditions / every one / none of them
exists: { by: 'table.column', on?, where? }a row of table points at this row (notExists for none)

Soft-deleted rows are not skipped implicitly. Write deletedAt: null where a deleted row should stop counting. Conditions compile to SQL that is never NULL, so { not: 'draft' } on a null status is true, and ['a'] on a null column is false.

Comparisons. A column can also be compared with a value that isn't a literal:

TestMeans
amount: { eq: x } / { ne: x }equal / different, null-safe (two nulls are equal)
entryDate: { lt: x } / { lte: x } / { gt: x } / { gte: x }ordered; false when either side is null

x is a literal, or one of:

OperandThe value of
{ column: 'id' }another column of the same row
{ lookup: 'studioSettings.lockedThrough', on: 'organizationId' }that column of one row of another table: the row whose organizationId equals this row's. Use on: { theirColumn: 'thisColumn' } when the names differ, and leave on out for a table that holds a single row. where narrows it further
{ old: 'status' } / { new: 'status' }a column of the row being written, before / after the write (refuse only)

Dates compare as text, so store them as q.date() (YYYY-MM-DD) or ISO timestamps.

permanent

invariants: { permanent: true },

Refuses a hard DELETE and the soft-delete transition (deletedAt becoming set), whoever runs it. A permanent table:

  • can't declare delete:, because that would generate a route the database refuses. That's a compile error.
  • is never a soft-delete cascade target. Deleting a parent leaves its permanent children in place. To refuse the parent's delete instead, add a restrict rule to the parent.

This is stronger than leaving out delete:, which only removes the route. The rows also survive cascades, actions and raw SQL.

appendOnly

invariants: { appendOnly: true },                                  // journal lines
invariants: { appendOnly: { setOnce: ['unappliedAt'] } },          // one-time unapply

After insert, no column changes. Each setOnce column may go from null to a value once, together with the audit columns (modifiedAt, modifiedBy), and never back. That fits a transition with a stamp:

transitions: {
  unapply: { field: null, guard: { unappliedAt: { null: true } }, stamp: { at: 'unappliedAt' }, access: { roles: ['owner'] } },
},

Deleting is a separate rule: pair appendOnly with permanent for a ledger. An append-only table can't have a general update: route. Declare none, or limit guards.updatable to the setOnce columns, or the compile fails.

Columns that move with the stamp. A stamp usually has an actor (unappliedBy). Give the stamp as { stamp, with } and the with columns can change only in the write that sets the stamp: not before, and not again after.

invariants: {
  appendOnly: {
    setOnce: [{ stamp: 'unappliedAt', with: ['unappliedBy', 'modifiedAt', 'modifiedBy'] }],
  },
},

The audit columns (modifiedAt, modifiedBy) and the soft-delete pair are free by default. List them in with and they hold still too, except in the stamping write. Without that, a bare UPDATE … SET modified_by = … passes an append-only table.

frozen

invariants: {
  frozen: [
    { when: { status: 'posted' }, allow: ['status'] },  // a posted bill can only change status
    { when: { status: 'void' } },                         // a void bill never changes
    { fields: ['kind'] },                                 // kind never changes, in any state
  ],
},

when is tested on the stored row, so the update that moves a row into a frozen state still goes through. Give fields to freeze only those columns, or allow to freeze every column except those. With neither, every column is frozen except the audit columns (modifiedAt, modifiedBy) and the soft-delete pair, because deleting is the job of permanent or restrict. Without when, frozen: { fields } makes columns immutable at the database, including for actions. guards.immutable only binds the routes.

lockedBy

// features/bills/bill-allocations.ts
invariants: {
  lockedBy: { parent: 'billId', when: { status: ['posted', 'void'] } },
},

While the row parent points at matches when, a child row can't be inserted, updated, moved to or from that parent, soft-deleted or deleted. allow lists columns that may still change. For a grandparent, nest a ref:

lockedBy: { parent: 'clientInvoiceLineId', when: { invoiceId: { ref: { status: ['issued', 'void'] } } } },

parent must be a .references() column.

Only some writes, one code each. on picks the writes the lock refuses ('insert', 'update', 'delete', or a list; default all three), and code and message take one value per write:

lockedBy: {
  parent: 'billId',
  when: { status: ['posted', 'void'] },
  code: { insert: 'BLOCK_POSTED_INSERT', update: 'LOCK_POSTED_ALLOCATION' },
},

on: 'insert' stops new children only: existing ones stay editable, so pair it with another rule if they shouldn't be. A write with no code of its own answers PARENT_LOCKED.

requires (on .references())

The referenced row must meet a condition while the reference is set:

itemLineId: q.text().required().references(() => itemLines.id, {
  match: { source: 'projectId', target: 'projectId' },
  requires: { kind: 'reimbursable' },
}),

It's checked from both sides. A source insert or update that points at a row failing the condition is refused. So is a target update that would leave a live reference pointing at a row that no longer meets it. Soft-deleted source rows don't hold the target.

The list form gives each rule its own when (on this row), code and message:

appliesToLineId: q.text().optional().references('self', {
  requires: [
    { when: { kind: 'vendor_tax' }, target: { kind: ['merchandise', 'reimbursable'] }, code: 'CHECK_APPLIES_TO' },
    { when: { kind: { not: 'vendor_tax' } }, target: { kind: 'merchandise' }, code: 'CHECK_APPLIES_TO' },
  ],
}),

Values must be inline literals: the source path reads them from the file.

restrict

Refuse a change to this row while rows of another table point at it:

// features/items/items.ts
invariants: {
  restrict: [
    { by: 'billAllocations.itemLineId', blocks: 'delete' },
    { by: 'approvals.itemId', where: { revokedAt: null }, blocks: ['delete', { enter: { archivedAt: { notNull: true } } }] },
    { by: 'proposalLines.itemLineId', where: { deletedAt: null, proposalId: { ref: { status: 'sent' } } }, blocks: { update: ['cost', 'price'] }, code: 'LINE_LOCKED' },
  ],
},

by names the referencing column as 'table.column'. It points at this row's id, or at the column you name in on. blocks takes one value or a list:

BlockRefuses
'delete'a hard delete or the soft-delete transition
'update'any column change
{ update: ['cost', 'price'] }a change to those columns
{ enter: { status: 'withdrawn' } }an update after which the row matches the condition, from a state where it didn't

{ enter } is how a transition gets an existence check at the database: "a proposal can't be withdrawn while it has live approvals" is { by: 'approvals.proposalId', where: { revokedAt: null }, blocks: { enter: { status: 'withdrawn' } } }.

refuse

Refuse a write when a condition holds. Use it for a rule the other shapes don't fit: a comparison between columns, a comparison with a settings row, or a rule about how a row changes.

// features/journal/journal-entries.ts
invariants: {
  refuse: [
    {
      on: 'insert',
      when: {
        sourceType: { not: ['manual', 'year_end'] },
        entryDate: { lte: { lookup: 'studioSettings.periodLockedThrough', on: 'organizationId' } },
      },
      code: 'PERIOD_LOCKED',
      message: 'entry date {entryDate} is on or before the locked period end {studioSettings.periodLockedThrough}',
    },
  ],
},

on is 'insert', 'update', 'delete' or a list. when is tested on the row being written: the new row on insert and update, the stored row on delete. A soft delete counts as a delete. It is also an UPDATE, so an update rule sees it too: a rule about a column can't be passed by deleting the row in the same statement.

Inside when, four more keys read the write itself:

KeyHolds when
old: condthe row before the write matches (update, delete)
new: condthe row after the write matches (insert, update)
changed: ['a', 'b']the update changes one of these columns
unchanged: ['a', 'b']the update changes none of them

The { old: 'col' } and { new: 'col' } operands work at any depth, so a nested ref or exists can compare another row with the one being written.

Columns of one row, or of two rows. An item line can't apply to itself, and the line it applies to must be on the same project:

refuse: [
  { on: ['insert', 'update'], when: { appliesToLineId: { eq: { column: 'id' } } }, code: 'CHECK_APPLIES_TO' },
  { on: ['insert', 'update'], when: { appliesToLineId: { ref: { projectId: { ne: { new: 'projectId' } } } } }, code: 'CHECK_APPLIES_TO' },
],

How a row may change. The ledger and its cut-over date change together, and the date is fixed once anything has been sent to that ledger:

refuse: [
  { on: 'update', when: { changed: ['ledger'], any: [{ ledgerCutoverDate: null }, { unchanged: ['ledgerCutoverDate'] }] }, code: 'LEDGER_CUTOVER_REQUIRED' },
  {
    on: 'update',
    when: {
      unchanged: ['ledger'],
      changed: ['ledgerCutoverDate'],
      exists: { by: 'ledgerSyncItems.organizationId', on: 'organizationId', where: { status: 'sent', ledger: { eq: { new: 'ledger' } } } },
    },
    code: 'LEDGER_CUTOVER_FIXED',
  },
],

A transition the database enforces, and a delete that depends on state. Transitions check a move in the generated action. refuse holds it for every writer. A sent proposal can only be withdrawn, and is never deleted:

refuse: [
  {
    on: 'update',
    when: {
      old: { status: 'sent' },
      none: [
        { new: { status: 'withdrawn' }, unchanged: ['deletedAt'] },
        { old: { deletedAt: null }, new: { deletedAt: { notNull: true } } },   // a soft delete: the next rule answers it
      ],
    },
    code: 'LOCK_SENT',
  },
  { on: 'delete', when: { status: 'sent' }, code: 'BLOCK_SENT_DELETE' },
],

When two rules refuse the same write, either code may answer. Make their conditions exclusive, as the none arm above does, where the code matters.

details.fields lists the columns of the written row that when reads. Give fields: [...] on the rule to name them yourself.

derived

A column the database keeps equal to an aggregate of child rows:

// features/shipments/shipments.ts
invariants: {
  derived: {
    projectId: { single: 'packageId.projectId', from: 'shipmentPackages.shipmentId', where: { deletedAt: null } },
  },
},

from names the children as 'table.column', where the column is the child's FK to this row. Give one aggregate:

AggregateValue
single: 'col'the one distinct non-null value of the children, else null
count: truethe number of children
sum: 'col'their sum, 0 for none
min: 'col' / max: 'col'their minimum / maximum

The value path may take one hop through a child's FK ('packageId.projectId'). The column is recomputed in the same statement as any child insert, update, soft delete or delete, and when the hop table's value changes. Writing the column directly is refused unless the value equals the aggregate. Rows written before the rule was added correct themselves on their next child write. Other rules (frozen, appendOnly, lockedBy) don't count the recompute as a change.

balanced

A journal entry's lines must balance, checked once the whole write has run, not row by row:

// features/journal/journal-entries.ts
invariants: {
  permanent: true,
  appendOnly: true,
  balanced: { lines: 'journalLines.entryId', debit: 'debitCents', credit: 'creditCents' },
},

debit and credit must be integer columns (q.int or q.bigint). Store money as integer cents. Decimal columns are refused at compile time.

SQLite has no deferred constraints, so balance can't be a row trigger. The compiler emits two pieces:

  1. Triggers on the line table keep _quickback_unbalanced equal to the set of keys whose lines don't balance right now.
  2. The runtime ends every write unit with a commit check. Inserting into _quickback_invariant_check fails with QB_TRIGGER_REJECTED:UNBALANCED while any key is listed, and that rolls the unit back.

A write unit is one transaction:

WhereUnit
Shared D1one statement, or one db.batch([...]) (a D1 batch is a transaction)
Tenant objectdb.transaction(cb), an atomic batch, a changeset, or one statement outside them

So write an entry's lines together: one db.batch, one multi-row insert(...).values([...]), a batch route, or (in a tenant object) a changeset or db.transaction. Awaiting one line at a time fails at the first line, because that statement commits alone. On shared D1 a changeset is not one transaction (it writes op by op), so it can't create an entry with balanced lines in one request. Use an action with db.batch there.

The check is appended by the features database handle (createDb) and by the queue, cron and email runners. SQL run through env.DB directly, or with exec, bypasses the check but not the triggers. An unbalanced key it leaves behind makes the next checked write fail until the key is fixed.

What a refusal says

Every refusal carries the same details, on every write path. The generated Worker logs them too, with the status and the message as reason:

[quickback:trigger-rejected] {"event":"TRIGGER_REJECTED","status":422,"code":"PERIOD_LOCKED","table":"journalEntries","rule":"refuse","fields":["entryDate"],"field":"entryDate","id":"je_1042","reason":"entry date 2026-03-31 is on or before the locked period end 2026-03-31"}
FieldValue
codethe rule's stable code
tablethe table being written (its export name)
rulethe rule that refused: permanent, appendOnly, frozen, lockedBy, requires, restrict, refuse, derived, balanced or match
fieldsthe columns involved. For frozen, appendOnly, lockedBy and restrict update rules these are the columns this write changed
fieldthe single column, when there is exactly one
idthe row's id (for balanced, an entry that doesn't balance)
refthe other side of the rule, where there is one: the parent table, or the referencing table.column

A rule declared on one table can refuse a write to another. requires and match check the referenced row too, and then table is the row being written and ref is the column that declares the rule.

Values in a message. A message can show the values it is about. Write {column} for the written row's value ({old.column} or {new.column} to pick a side), and {table.column} for a lookup the rule compares the row against:

message: 'entry date {entryDate} is on or before the locked period end {studioSettings.periodLockedThrough}',
// → "entry date 2026-03-31 is on or before the locked period end 2026-03-31"

A value is shown as text on one line, cut at 120 characters, and null when null. Naming a masked, encrypted or q.secret() column is a compile error, because the message goes to the response and the log. details holds column names and the row id, never column values.

An authored sql: trigger can report the same details by ending its message with the block the compiler writes.

In tenant databases

Under tenancy.isolation: 'durable-object', a tenant table's invariants install inside each tenant object, exactly like its sql: triggers. Every table a rule names must live in the same object, or the build fails with TENANCY_TRIGGER_CROSS_OBJECT.

Not covered

  • Postgres. Invariants and requires lower to SQLite only (D1 and tenant objects). A Postgres project declaring them fails the build.
  • Rules that write. An invariant refuses or recomputes; it doesn't run arbitrary statements. A parent value set by its first child is match with adopt, and a subtree soft delete is onSoftDelete: 'cascade'. Anything else stays a sql: trigger.
  • Existing rows. A new rule checks future writes. Check existing rows before rollout.

On this page