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
| Rule | Holds | Default code |
|---|---|---|
permanent | No hard delete and no soft delete. Never a cascade target. | RECORD_PERMANENT |
appendOnly | No update after insert, except one-time setOnce stamps. | RECORD_APPEND_ONLY |
frozen | Fields stop changing once the stored row matches when (or always). | RECORD_FROZEN |
lockedBy | Child 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 |
restrict | No delete, listed-column change or state entry while matching rows point here. | RESTRICTED_BY_REFERENCE |
refuse | An insert, update or delete is refused when a condition on the row, or on the change itself, holds. | WRITE_REFUSED |
derived | A 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.
| Test | Means |
|---|---|
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:
| Test | Means |
|---|---|
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:
| Operand | The 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
restrictrule 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 unapplyAfter 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:
| Block | Refuses |
|---|---|
'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:
| Key | Holds when |
|---|---|
old: cond | the row before the write matches (update, delete) |
new: cond | the 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:
| Aggregate | Value |
|---|---|
single: 'col' | the one distinct non-null value of the children, else null |
count: true | the 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:
- Triggers on the line table keep
_quickback_unbalancedequal to the set of keys whose lines don't balance right now. - The runtime ends every write unit with a commit check. Inserting into
_quickback_invariant_checkfails withQB_TRIGGER_REJECTED:UNBALANCEDwhile any key is listed, and that rolls the unit back.
A write unit is one transaction:
| Where | Unit |
|---|---|
| Shared D1 | one statement, or one db.batch([...]) (a D1 batch is a transaction) |
| Tenant object | db.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"}| Field | Value |
|---|---|
code | the rule's stable code |
table | the table being written (its export name) |
rule | the rule that refused: permanent, appendOnly, frozen, lockedBy, requires, restrict, refuse, derived, balanced or match |
fields | the columns involved. For frozen, appendOnly, lockedBy and restrict update rules these are the columns this write changed |
field | the single column, when there is exactly one |
id | the row's id (for balanced, an entry that doesn't balance) |
ref | the 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
requireslower 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
matchwithadopt, and a subtree soft delete isonSoftDelete: 'cascade'. Anything else stays asql:trigger. - Existing rows. A new rule checks future writes. Check existing rows before rollout.
Triggers - Postgres-Style Table Triggers
Run data rules and side-effects on writes. SQL triggers compile to a real database trigger — SQLite on D1, a SECURITY INVOKER PL/pgSQL function on Postgres; handler triggers run as application hooks at the write chokepoint.
Actions
Custom API endpoints for business logic beyond CRUD. One file per action, in a feature's actions/ directory.