Stock balances belong in application code, not a database trigger

Rechvix tracks inventory as an append-only ledger of stock movements. Every receipt, sale, return and adjustment is a row that is never edited. Asking how much of a product is in a warehouse by summing the ledger would be correct and slow, so there is also a balance table that holds the current quantity and average cost per product and warehouse.

The balance table is a projection. It is always re-derivable by replaying the movements. The architectural question I left open at the start, and decided in September 2026, was who keeps it in sync: a database trigger, or the application, inside the same transaction as the movement insert.

The two options

A trigger is attractive. It lives next to the data, fires whatever code inserts the row, and cannot be forgotten. The alternative is a function in Go, called by whatever records the movement, that locks the balance row, computes the new values and writes both in one transaction.

Why I chose application code

The costing rule is already Go. Rechvix uses weighted-average cost. Recalculating it on a receipt needs the current balance, the movement's quantity and cost, and a formula over decimal values. Writing it again in PL/pgSQL would give me one business rule in two languages with no shared tests, and the result feeds stock valuation reports.

The locking is explicit anyway. The movement function takes the balance row lock with SELECT ... FOR UPDATE before computing the new value. That is the serialisation a trigger would have to provide implicitly, so the trigger buys no extra correctness.

It matches how the rest of the code works. Audit entries and session rotation, the other cases where one record is derived from another in the same transaction, are application code too. Keeping stock the same means there is one answer to the question of where a side effect happens. Row-level security is the one database-level mechanism in the project, and it is a security boundary, which is a different category.

It is debuggable. During an incident I would rather step through a function with a stack trace and a log line than reason about a trigger firing under an INSERT. This table decides whether an order can be fulfilled and what stock is worth.

What it costs

There is no database-level backstop. If a future migration or admin script inserts straight into the movement table, the balance silently stops agreeing with the ledger. I wrote that risk into the decision record instead of leaving it for someone to discover.

Two things contain it. The balance is documented as always re-derivable from the movements, and the backup and restore drill checks for exactly this kind of drift. Nothing in the repository writes movements except through the one function, and that rule is stated in the record where a new contributor will read it.

When I would reverse the decision

If measured write volume ever made the application path a bottleneck, the arithmetic could move into a trigger without changing the movement table's schema. I do not expect that at the scale this system targets, which is a warehouse's movement rate and not a trading ledger. I would want a measurement before making the change.

What I take from this

The useful part of an architecture decision record is the consequence section. Listing what you gave up, and what contains it, is what lets someone else change the decision safely later.