A 13-part series on the UmimaClean stack. 1 — AdonisJS · 2 — VineJS · 3 — Lucid · 4 — Inertia · 5 — Transformers · 6 — Transmit · 7 — Leaflet · 8 — Midtrans · 9 — Fonnte · 10 — ExcelJS · 11 — Frontend · 12 — Security · 13 — Testing

Lucid is Adonis’s ORM: active record, so a model is a class with methods rather than an object you hand to a repository. PostgreSQL is underneath it.

This part is about the division of labour between the two — what I let the ORM do, what I insisted the database do, and the one type-system gotcha that shipped a real bug.

Models Are Generated, Then Extended

Lucid 22 does something I had not seen in a Node ORM: column definitions are generated from migrations.

Running node ace migration:run regenerates database/schema.ts, which holds a base class per table with every column typed from the actual schema. Models then extend those and add only relations:

import { OrderSchema } from '#database/schema'
import { belongsTo, hasMany } from '@adonisjs/lucid/orm'

export default class Order extends OrderSchema {
	@belongsTo(() => User, { foreignKey: 'userId' })
	declare user: BelongsTo<typeof User>

	@belongsTo(() => Address, { foreignKey: 'addressId' })
	declare address: BelongsTo<typeof Address>

	@hasMany(() => OrderItem, { foreignKey: 'orderId' })
	declare items: HasMany<typeof OrderItem>

	@hasMany(() => OrderAction, { foreignKey: 'orderId' })
	declare actions: HasMany<typeof OrderAction>

	@hasMany(() => Transaction, { foreignKey: 'orderId' })
	declare transactions: HasMany<typeof Transaction>
}

That is the entire Order model — thirty-five lines for the spine of the whole system. No @column() declarations, no nullable flags kept in sync by hand.

Why this matters more than it sounds. In the usual arrangement, a migration and a model both describe the same table, and nothing enforces that they agree. Add a nullable column in a migration, forget the model, and TypeScript cheerfully tells you the property does not exist — or worse, you declare it non-nullable and the compiler assures you it is never null while the database happily returns one.

Here the migration is the single source of truth and the types are derived from it. A drift between schema and model is not possible, because there is only one description.

The trade: database/schema.ts is generated and must never be hand-edited, and it must be regenerated after every migration. In practice that is the same command you already run.

The model layer keeps one deliberate rule: no business logic, and no lifecycle hooks. Not one beforeSave in the codebase. Hooks are the classic place for logic to hide — invisible at the call site, fired by writes you did not know performed a write. Every rule is in a service, where you can see it being called.

Migrations Carry the Reasoning

Migrations are ordinary Knex-style schema builders:

export default class extends BaseSchema {
	protected tableName = 'orders'

	async up() {
		this.schema.createTable(this.tableName, (table) => {
			table.increments('id')
			table
				.integer('user_id')
				.nullable()
				.index()
				.references('id')
				.inTable('users')
				.onDelete('RESTRICT')
			table
				.integer('address_id')
				.nullable()
				.index()
				.references('id')
				.inTable('addresses')
				.onDelete('RESTRICT')

			table.string('customer_name').notNullable()
			table.string('customer_phone').notNullable()
			table.string('order_number').notNullable().unique()
			table.string('status').notNullable().index()
			table.date('pickup_date').nullable().index()
			table.decimal('total_price', 10, 2).nullable()
			table.string('type').notNullable().index()

			table.timestamp('created_at').notNullable()
			table.timestamp('updated_at').nullable()

			table.index(['user_id', 'created_at'])
			table.index(['status', 'pickup_date'])
			table.index(['status', 'created_at'])
		})
	}

	async down() {
		this.schema.dropTable(this.tableName)
	}
}

Three composite indexes, and each one exists for a screen: a customer’s order history sorted by date, the staff queue of orders in a status due on a date, and the admin monitor filtered by status and sorted newest-first. Indexes added because a query needed them, not sprinkled on every column.

The nullable columns are the interesting part of this table:

address_id IS NULL is not an absence of data — it is the fact that decides whether a washed order goes out on the van or moves to Siap Diambil on the shelf. Nulls carrying meaning is usually a smell; here each one is a genuine “this stage has not happened yet” and the alternative is a boolean that can disagree with it.

Migrations That Explain Themselves

A later migration adds the task lock, and it documents the change it is undoing:

/**
 * Who is currently holding an order's task, stored on the order itself.
 *
 * The lock used to be derived by replaying an order's whole action log in
 * memory, which meant every queue query had to `preload('actions')` and then
 * filter the results in JavaScript — the queue could not be asked for "the
 * free ones" in SQL at all. These three columns are that same answer as
 * state, so the queues filter on an index instead.
 *
 * The claim is still written to the action log as well: the log is the audit
 * trail of who did what, and that must not become a column that the next
 * claim overwrites.
 */

Migrations are the one part of a codebase that is genuinely append-only. You cannot read the current schema and know what it replaced or why. A comment in a migration is the closest thing to a decision log the database has.

That last paragraph is the important one: the lock became state, and the audit log stayed a log. Denormalising a derived value into a column is fine; overwriting the history it was derived from is not.

Rules the Database Enforces

Two rules I refused to leave to application code.

CREATE UNIQUE INDEX one_active_address_per_user
  ON addresses (user_id) WHERE is_active = true;

CREATE UNIQUE INDEX transactions_order_id_pending_unique
  ON transactions (order_id) WHERE status = 'pending';

Partial unique indexes — unique, but only over rows matching a condition.

A customer may have many addresses and exactly one active. An order may accumulate many transactions — a QR expires, the customer retries — and have at most one pending.

Neither is expressible as a plain UNIQUE, and both are trivially expressible as a check in a service:

const existing = await Address.query().where('user_id', user.id).where('is_active', true).first()
if (existing) {
	/* deactivate it first */
}

That check is correct until two requests run it concurrently, or until the next feature writes an address without going through that service. An index cannot be raced and cannot be forgotten. If a rule must always be true, it belongs where “always” is enforceable.

RESTRICT as History Preservation

Every foreign key in this schema is RESTRICT. None cascade.

table.integer('user_id').nullable().index().references('id').inTable('users').onDelete('RESTRICT')

CASCADE is the friendlier default — delete a user, their orders go too, no dangling rows. It is also completely wrong for a system of record. Those orders are what happened. Deleting them because an account was closed erases the shop’s own history, and the revenue report silently changes.

So the database refuses. A customer who has ordered cannot be deleted; a staff member who has worked cannot be deleted; a service that has priced an order cannot be deleted. The application layer answers this with deactivation instead, and pre-computes which rows are undeletable so the UI can disable the button with an explanation rather than failing on submit:

getUndeletableIds() // users who appear in the order record
getInUseServiceIds() // services that have priced something

The button being disabled is UX. The RESTRICT is the actual guarantee. Both exist because either alone is wrong: the constraint alone gives you an ugly error, the UI alone gives you no protection.

The one place rows genuinely are deleted is a nightly command, and only for rows nothing points at:

const orphans = await Address.query()
	.where('is_active', false)
	.whereDoesntHave('orders', (query) => query)

Deactivated and referenced by no order. An address that ever received a collection stays forever, because it is where an order was picked up.

Query Building

The API is a chainable builder. The method I use most is .if(), which conditionally applies a fragment:

return Order.query()
	.where('status', OrderStatus.AWAITING_PAYMENT)
	.if(filters.search, (query) => {
		query.where((matches) => {
			matches.whereILike('order_number', searchTerm).orWhereILike('customer_name', searchTerm)
		})
	})
	.preload('transactions', (transactionsQuery) => {
		transactionsQuery.orderBy('created_at', 'desc')
	})

Filterable admin tables are most of the admin area — orders, users, services, the reconciliation backlog — and every one of them is “a base query plus zero or more optional narrowings”. Without .if() that is a pile of if statements mutating a query variable, which works and reads badly.

Two details in there.

The nested where callback is a parenthesis. matches.whereILike(...).orWhereILike(...) inside a callback produces AND (a ILIKE ? OR b ILIKE ?). Flatten it and you get AND a ILIKE ? OR b ILIKE ?, which — because AND binds tighter than OR — quietly returns every row matching the second term regardless of status. That is the single easiest way to write a search filter that leaks rows, and it looks correct.

preload with a callback is eager loading with an ordering. The alternative is loading transactions per order in a loop, which is the N+1 problem with extra steps.

Search itself is prefix-only, deliberately:

const searchTerm = `${filters.search}%`

ORD2607% rather than %ORD2607%. A trailing wildcard can use a B-tree index; a leading one cannot and forces a sequential scan. Staff search by order number prefix and customer name from the start, so the restriction costs nothing real and keeps the query indexable.

Transactions Around Multi-Table Writes

Anything touching more than one table gets an explicit transaction:

await db.transaction(async (trx) => {
	await transaction
		.merge({ status, midtransTransactionId: payload.transaction_id })
		.useTransaction(trx)
		.save()

	if (status === TransactionStatus.PAID && order.status === OrderStatus.AWAITING_PAYMENT) {
		await order.merge({ status: OrderStatus.IN_CLEANING }).useTransaction(trx).save()
	}
})

A payment that marks the transaction paid but fails before advancing the order leaves money received and an order still waiting for it. The transaction makes both happen or neither.

.useTransaction(trx) on each model call is the part that is easy to forget. Miss it on one and that write silently escapes the transaction — no error, just a write that does not roll back. It is the most common Lucid mistake I made, and it is invisible until something fails at exactly the wrong moment.

Query-builder calls take the client differently:

Order.query({ client: trx }).where('id', order.id).update({ ... })

Two syntaxes for the same idea, and the compiler does not care if you use neither.

Letting Postgres Decide

The claim lock is the one place the query is doing something more interesting than filtering — a conditional UPDATE whose WHERE clause is the precondition:

const won = affectedRows(
	await this.whereUnlocked(Order.query({ client: trx }).where('id', order.id)).update({
		locked_by_id: staff.id,
		locked_task: type,
		locked_until: now.plus({ hours: CLAIM_DURATION_HOURS }).toSQL()
	})
)

if (won === 0) {
	return false
}

function affectedRows(result: unknown): number {
	return Number(Array.isArray(result) ? result[0] : result) || 0
}

Postgres answers an UPDATE with the number of rows it touched, and Lucid passes that through. Zero means the row stopped matching — somebody else claimed it first. There is no window between checking and writing because the check is the write. I covered why that matters operationally in the project write-up; here the point is narrower: the ORM’s job was to get out of the way and let the database answer.

That affectedRows helper exists because the return shape varies by driver and by whether a returning clause is present. Normalising it once beats getting it subtly wrong at each call site.

The numeric Gotcha

This one shipped a real bug and is worth the section.

The pg driver returns numeric and decimal columns as JavaScript strings. Not a bug — a numeric can hold values a float64 cannot represent exactly, so returning a string is the lossless choice. The driver is right.

The consequence is that order.totalPrice is '85000.00' at runtime, whatever its declared type says. Which means:

order.totalPrice + shipping // '85000.005000'

String concatenation, no error, plausible-looking output.

The screens never hit it, because transformers run every amount through formatRupiah, which coerces on the way past:

export function formatRupiah(value: number | string) {
	return new Intl.NumberFormat('id-ID', {
		style: 'currency',
		currency: 'IDR',
		minimumFractionDigits: 0,
		maximumFractionDigits: 0
	}).format(Number(value))
}

The number | string parameter type is not permissiveness. It is a description of the problem, sitting in the signature.

The exports had no such step, so amounts landed in the spreadsheet as text: right-aligned, unsummable, and looking entirely normal until an admin selected the column and Excel reported no total. Fixed with one explicit coercion at the boundary:

export function excelNumber(value: number | string | null | undefined): number | null {
	if (value === null || value === undefined || value === '') return null

	const parsed = Number(value)
	return Number.isFinite(parsed) ? parsed : null
}

The transformers do the same thing on the way to the page:

totalPrice: this.resource.totalPrice === null ? null : Number(this.resource.totalPrice),

The general lesson: TypeScript types describe what you meant, not what the driver returned. Anywhere data crosses a boundary the compiler cannot see — a database driver, JSON.parse, a webhook body, localStorage — the type is a claim. This one is easy to miss precisely because the claim is nearly true: the value looks like a number in every log line and behaves like one in every comparison, right up until something adds it.

Aggregation Stays in SQL

The dashboard and revenue report are read-only aggregation, and they run as SQL rather than as JavaScript over loaded rows:

export const DEFAULT_RANGE_DAYS = 30
export const TOP_SERVICE_LIMIT = 5

Grouped totals come back sparse — a day with no orders produces no row — which produces a specific and very convincing chart bug:

/**
 * A chart drawn straight from grouped SQL rows silently omits the quiet days,
 * which stretches the remaining ones across the axis and makes a week with two
 * orders look as busy as a week with fourteen. Filling the gaps with zeros is
 * what makes the shape of the line honest.
 */
export function buildDailySeries(
	rows: { date: string; total: number }[],
	from: DateTime,
	to: DateTime
): SeriesPoint[] {
	const totalsByDate = new Map(rows.map((row) => [row.date, Number(row.total)]))

	return eachDay(from, to).map((date) => ({
		date,
		label: DateTime.fromISO(date).setLocale('id').toFormat('d LLL'),
		total: totalsByDate.get(date) ?? 0
	}))
}

Note Number(row.total) again — aggregate results are numeric too, and arrive as strings.

The report is also restricted to paid orders throughout, which is a business rule rather than a query optimisation: an order awaiting payment is work in progress, not revenue, and counting it overstates every figure on the page.

What I Would Do Differently

Money as decimal returning strings is a permanent tax. Storing rupiah as an integer number of rupiah — there are no cents in practice — would have sidestepped the whole class of bug. numeric(10,2) is the reflex for money and it was probably the wrong reflex here.

whereDoesntHave in the prune command loads then deletes in a loop. Fine at this scale, and it should be one DELETE ... WHERE NOT EXISTS.

Status is a string column, not a Postgres enum. Deliberate — adding a status is a code change rather than a migration, and the as const object gives the type safety — but it does mean the database will accept a typo that TypeScript would have caught. A check constraint would close that without the rigidity of a real enum type.

What I would not change: RESTRICT everywhere, partial indexes for the invariants, and generated model schemas. Those three between them removed entire categories of failure — deleted history, raced uniqueness checks, and models that quietly disagree with their tables.

Next: Part 4 — Inertia: Deleting the API Layer, where controllers render React pages with props and the entire REST layer stops existing.