Money code in a church app: integer cents, advisory locks and idempotency keys

Aparima is a church management platform I built with Next.js 16, Payload CMS 3 and PostgreSQL. It has public pages, a member area and staff workspaces for households, events, attendance, venue hire, giving and reporting, in seven languages. In August I spend one month to build it:

It is a demo system with synthetic data. The payment provider in demo is simulated, and so are email and accounting sync. But I built the money logic as if real money was moving, because that is the part that is hardest to change later. Giving money looks like a form with one number field. Behind it there are five rules.

Rule 1: money is integer cents, checked in three layers

Floating point numbers cannot represent most decimal amounts exactly. In JavaScript 0.1 + 0.2 are not 0.3. So every amount in Aparima is an integer number of cents, and this is checked three times.

Layer 1: the browser parses a string, not a float

function cents(value: string) {
  const normalized = String(value || '').trim()
  if (!/^\d+(\.\d{1,2})?$/.test(normalized)) return Number.NaN
  const [whole, fraction = ''] = normalized.split('.')
  return Number(whole) * 100 + Number(fraction.padEnd(2, '0'))
}
// "12.3" → 1230, "12.345" → rejected

Layer 2: the server validates, and computes what it can

• Number.isInteger(amountCents) in a fixed range, and unknown fields are rejected with a 400.
• Some amounts are never accepted from the client at all. A venue deposit is 25% of the accepted quote, rounded up, and the server calculates it. The client never can send the deposit amount.

export const calculateVenueDepositCents = (quoteTotalCents: number) => {
  if (!Number.isInteger(quoteTotalCents) || quoteTotalCents < 1)
    throw new APIError('Accepted Quote total is invalid', 409)
  return Math.ceil(quoteTotalCents / 4)
}

Layer 3: the database refuses bad rows

Payload stores number fields as numeric, which allows fractions. So each money column has a CHECK constraint that requires a whole number, and the payments table has a balance identity for each status:

CHECK (
  amount_cents > 0 AND amount_cents = trunc(amount_cents) AND (
    (status = 'declined'           AND refunded_cents = 0 AND remaining_cents = 0) OR
    (status = 'succeeded'          AND refunded_cents = 0 AND remaining_cents = amount_cents) OR
    (status = 'partially-refunded' AND refunded_cents > 0 AND remaining_cents > 0
                                   AND amount_cents = refunded_cents + remaining_cents) OR
    (status = 'refunded'           AND refunded_cents = amount_cents AND remaining_cents = 0)
  )
)

The quotes table checks that the total equals base plus equipment plus cleaning plus setup. Payment, Giving and Receipt each keep their original, refunded and remaining cents, and before a refund is written, the service checks that all three rows agree and that the refund is not bigger than the remaining balance.

A small example of why this matters: the dashboard had a "near capacity" check written as remaining <= Math.ceil(capacity * 0.2). It marked an event with 1 seat and 1 remaining as nearly full. The auditor suggest a pure integer version, remaining * 5 <= capacity, and I tested it with every capacity from 1 to 25.

Rule 2: one workflow, one transaction

A successful gift writes seven things, and each workflows run in one database transaction, so you get all seven or nothing:

1. the person (for a new public donor)
2. the payment
3. the giving record
4. the receipt
5. a timeline entry, without the amount
6. an automation run, which stores the full response for replays
7. an audit event

Payload has its own transaction helpers, but I also need raw SQL for locks inside the same transaction. The code gets the transaction ID from the request and uses the matching Drizzle session, so the lock and the Payload writes share one transaction. If anything throws, the transaction is killed.

The recurring giving processor is different. It opens a separate transaction for each due plan. One broken plan should not roll back the payments of everybody else, so failures are counted and reported instead of thrown.

Rule 3: serialise with advisory locks, always in sorted order

I did not use SELECT ... FOR UPDATE in business code. All serialisation uses pg_advisory_xact_lock, which is released automatically at the end of the transaction. The lock number comes from a readable name, hashed with SHA-256 and cut to 32 bits, and the numbers are always sorted before locking, so two workflows that need the same locks cannot deadlock.

const lockNumber = (name: string) =>
  createHash('sha256').update(name).digest().readInt32BE(0)

for (const lock of locks.map(lockNumber).sort((a, b) => a - b))
  await db.execute(`SELECT pg_advisory_xact_lock(${lock})`)

Pausing a plan and processing it share the giving-plan-state lock, so a pause and a payment for the same plan can never run at the same time. The venue deposit re-checks inside the calendar lock that no confirmed booking overlaps.

The lock keys is only 32 bits, so two names can collide. That is fine. A collision only makes two unrelated requests wait for each other. It never makes a wrong result.

Rule 4: unique constraints are the second line of defence

Locks are the first line. If some code path forgets a lock, the database still says no:

• The workflow key is unique on payments, givings and refunds.
• Payment, Giving and Receipt are strictly one to one to one.
• The recurring executions table is unique on (plan_id, due_at), so two processors cannot charge the same instalment twice.
• Financial foreign keys use ON DELETE RESTRICT, so deleting a payment is an error, not a cascade.
• Only one paid deposit per booking:

CREATE UNIQUE INDEX demo_payments_one_paid_venue_deposit_idx
  ON demo_payments (venue_booking_id)
  WHERE purpose = 'venue-deposit'
    AND status IN ('succeeded', 'partially-refunded', 'refunded');

Rule 5: idempotency keys that survive a lost response

The most common real problem with payments is not an attack. It is a phone on a bad connection. The request reaches the server, the payment succeeds, and the response is lost. The user taps the button again.

In the browser

• The key is crypto.randomUUID(), stored in sessionStorage together with a signature of the exact request body.
• The same intent reuses the same key, after a timeout or even after a page reload.
• The key is cleared only when the browser gets a definite answer.

This design came from a bug that was found by auditor. In the first version, a lost response made the client generate a new key on retry, so the quote version went from 2 to 3 and the history got a duplicate row. The fix was the sessionStorage mapping, and a Playwright test that drops the response of a request that already committed.

On the server

1. The key arrives in an Idempotency-Key header and must match ^[A-Za-z0-9_-]{8,128}$.
2. The server stores purpose:sha256(key) as the workflow key, plus a fingerprint of the normalised input.
3. After taking the locks, it looks for an existing automation run with that workflow key.

Recurring payments don't have a user clicking, so their key is deterministic: a stable hash of the plan ID and the due date.

Testing the races, not only the happy path

Each rule has an integration test against a real PostgreSQL database:

• Two identical requests with the same key via Promise.all: one commits, one replays.
• Two different refunds of 4,000 cents at the same time against a 6,500-cent deposit: exactly one succeeds, the other gets 409. Then a refund of the remaining 2,500 is accepted, and one more cent is rejected.
• Two recurring processors at once: one execution row in total.
• Monthly plans on the 31st are clamped to each month's length but keep their anchor day, so February does not move every later month to the 28th. Schedules use Pacific/Auckland civil time, and daylight saving gaps are handled explicitly.

My favourite test is fault injection. The test creates a temporary trigger that raises an exception in the middle of the workflow, checks that every row count is unchanged, drops the trigger, then retries with the same key and expects replayed: false, because nothing was committed the first time.

CREATE FUNCTION fail_receipt_update() RETURNS trigger AS $$
BEGIN
  IF NEW.refunded_cents > OLD.refunded_cents THEN
    RAISE EXCEPTION 'synthetic receipt failure';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

Access control: fail closed, and don't leak through side doors

There are seven fixed roles. Custom API routes check the role twice, once in the route and once in the service. Payload access functions return a query filter, not a yes or no:

export const readGivings = (args) => {
  if (isFinance(args.req.user)) return true
  if (getUserRole(args.req.user) !== 'member') return false
  return ownGivingPersonId(args).then((person) =>
    person ? { person: { equals: person } } : false)
}

All financial collections set create, update and delete to false, so the service workflows are the only way to write. A few patterns came directly from audits:

• An empty set of allowed records uses { id: { in: [-1] } }, which matches nothing, instead of accidentally matching everything.
• A plan that belongs to someone else and a plan that does not exist both return the same 404, so IDs cannot be enumerated.
• A member submitting a protected field, like an internal note, gets a 403 instead of the field being silently dropped. Silent dropping hides bugs.
• Refund details leaked to non-finance users through timeline entries, automation results, audit metadata and props passed to the booking page. Each one got a role-filtered projection.

An audit log that really is append-only

1. Payload: create, update and delete are all false; only the audit helper writes, with overrideAccess.
2. PostgreSQL trigger: any UPDATE, DELETE or TRUNCATE raises an error, and the app role has those privileges revoked anyway.
3. Separate roles for the app, the migrator and maintenance, and none of them is a superuser.

CREATE TRIGGER audit_events_reject_update_delete
  BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH ROW EXECUTE FUNCTION protect_audit_events();
-- protect_audit_events(): RAISE EXCEPTION 'audit_events is append-only' USING ERRCODE = '42501';
REVOKE UPDATE, DELETE, TRUNCATE ON TABLE audit_events FROM aparima_app;

The demo needs a reset button, which must clear the log. Despite of the append-only rule, this is possible through one parameterless SECURITY DEFINER function with a fixed search path, which only the app and maintenance roles can execute. A test runs raw UPDATE, DELETE and TRUNCATE statements and expects each one to fail.

How the work was organised

• Every feature was a vertical slice, and every slice got an independent audit in a read-only worktree.
• A blocker or a "must fix" finding stopped the merge.
• Auditors weakened a guard on purpose to check that a test turns red.
• No .skip or .only. A rejected request had to prove two things: the error, and an unchanged database.

There are tradeoffs. Playwright end-to-end tests run locally and before a release candidate, but not in CI yet. Venue overlap is protected by a lock and a re-check, not by a database exclusion constraint. I think both is acceptable for now, but I wrote them down.

The short version

1. Money in integer cents, checked in the browser, the server and the database.
2. One transaction per workflow.
3. Advisory locks in sorted order.
4. Unique constraints as a second line.
5. Idempotency keys that survive a lost response.

And tests that attack the races on purpose. None of it is visible in the UI. You only notice it when it is missing.