ArchitectureNo. 06
Offline-first billing for electricity stations: one id from the phone to the receipt, why bill totals must never be added, an outbox that knows what to retry, and the 500 that looped.
Did this article help you?
A heart tells me what to write more of.
Architecture
How I keep a 322,000-line accounting system correct on my own: one save gate, append-only money, a hash-chained audit trail, real-database tests, and the audit that caught what they missed.
Arabic interfaces
Why ESC/POS printers mangle Arabic, how to print it as a 1-bit image at the head's exact width, and the geometry bug that turned a 12 cm receipt into a roll on the floor.
Security
Six layers in production, each built for the day the one above it fails: forced row-level security, logins that cannot bypass it, sealed containers, phone certificates, weekly restores.
22:02, Aden time, 18 October. At a station in Yemen, two phones start sending the same receipt to my API. The API answers 500. The phones send it again, and again. By 22:06 the receipt has arrived 105 times, and the platform's health screen reads 9% server errors.
No balance moved by a single rial. The database refused every one of those inserts.
And every one of those requests passed through an idempotency layer, built for exactly this: phones that resend.
This is how FinyxWatt bills electricity customers from phones that lose the network. It covers the id that follows a payment from the phone to the receipt, the reason bill totals must never be added, the outbox that decides what to retry, and the rule I was missing that night. By the end you will be able to build an offline queue for money where a retry can never charge twice and a refusal can never loop.
105
sends of one receipt in 3.5 minutes
9%
server errors on the health screen
0
duplicate receipts recorded
1
function in the fix
A private electricity station runs on a chain of small steps. A collector reads the meter, a bill is issued, the customer gets an SMS, someone collects the money, and the cash moves up to the manager. FinyxWatt is the platform I built for that chain: four Flutter apps on one NestJS API over PostgreSQL. The station app is the one in the collectors' hands, and stations lose the network often.
So the app never waits for the network. It shows saved copies first, and every write goes into an outbox on the phone. When the connection returns, one coordinator uploads in dependency order: new customers first, then the bills and payments that may refer to them, then chat. A payment for a bill that so far exists only on the phone waits its turn. After a failed run the coordinator waits 30 seconds, doubling up to five minutes, so a struggling server is not hammered.
The first version ran on Firebase. Moving it to my own backend while stations kept working left me with one rule: the phone proposes, the server decides.
Retries are the whole design, which makes two questions unavoidable. If a retry is normal, what stops a retry from charging twice? And what stops a retry from never ending? The first has a clean answer. The second is the night of 18 October.
Every command gets a random UUID the moment it is first saved on the phone, and keeps it across retries, app restarts and lost responses. The id is also the identity of what it creates: a queued bill is created on the server under that id, and so is a payment.
/// A new random (version 4) id, created when a command is first queued.
String newOperationId() {
final random = Random.secure();
final bytes = List<int>.generate(16, (_) => random.nextInt(256));
bytes[6] = (bytes[6] & 0x0f) | 0x40;
bytes[8] = (bytes[8] & 0x3f) | 0x80;
return _format(bytes);
}
/// The id stored with a queued command, or a derived one for older items.
String operationIdFor(Map<String, dynamic> payload, String queueItemId) {
final stored = payload['operationId'];
if (stored is String && isCanonicalUuid(stored)) return stored.toLowerCase();
return derivedOperationId(queueItemId);
}The second function handles the trap that shows up after an upgrade. A new version of the app can land on a phone that still holds commands queued by an older version, which stored no id. Generate a fresh id on every retry and you have built the double charge you set out to prevent. So an old item gets an id derived from its own permanent queue id. It is the same value on every retry, and it is marked as UUID version 8 so anyone reading it knows it was derived.
A second trap hides inside that derivation. It uses 32-bit FNV-1a, and the multiplication by the FNV prime is spelled out as shifts:
hash = (hash + (hash << 1) + (hash << 4) + (hash << 7) + (hash << 8) + (hash << 24)) & 0xffffffff;The same Dart code also builds for the browser, where integers are doubles. A plain multiplication there can pass 2^53 and lose its low bits, and the browser would derive a different id from the same queue item than the phone does.
On the server the id arrives as the Idempotency-Key header, and the API claims it before it touches any money. The claim and the command share one transaction:
const claimed = await trx.insertInto('idempotency_receipts').values({
tenant_id, actor_identity_id, idempotency_key: key, operation,
request_hash: sha256(stableStringify(request)),
status: 'processing',
}).onConflict((c) => c.columns(['tenant_id', 'actor_identity_id', 'idempotency_key']).doNothing())
.returning('idempotency_key').executeTakeFirst()
if (claimed === undefined) {
const existing = await readReceiptForUpdate(trx, key) // SELECT … FOR UPDATE
if (existing === undefined) throw conflict('IDEMPOTENCY_RECEIPT_UNAVAILABLE') // fail closed
if (existing.operation !== operation || existing.request_hash !== requestHash) throw conflict('IDEMPOTENCY_KEY_REUSED')
if (existing.status !== 'completed') throw conflict('OPERATION_IN_PROGRESS')
return { replayed: true, status: existing.response_status, body: existing.response_body }
}
const result = await execute(trx) // first time: do the work
await markCompleted(trx, key, result) // and store its response in the same transaction
return { replayed: false, ...result }Each line closes a door.
ON CONFLICT wait for the first transaction, and the loser reads the finished receipt.processing answers OPERATION_IN_PROGRESS, and the phone files that under "try again later".One trap here took a migration to fix. My first receipts table was keyed by tenant, actor, operation and key. It looked tidy, and it allowed one key to be used once per endpoint. If a retry were ever routed to the wrong endpoint, the same key could create a second effect. Migration 0015 made each key single-use per actor across every command type.
A lost response is now harmless. Hold on to one detail from that code, though: the response is stored in the same transaction as the work. It matters later.
An idempotent request is only safe if it carries no money the phone made up. The phone sends a meter reading. The server does the rest.
A bill's charge is (current reading − previous reading) × price per kWh, rounded to two decimals in PostgreSQL numeric. The previous reading comes from the meter row, which the server locks for the length of the transaction. The price is the customer's own while it is within its dates, otherwise the station's. A price sent by a phone counts only for a station that has never stored one, so a phone that has been offline for weeks cannot bill at an old price.
Side effects need the same discipline. The customer's SMS is created in the same transaction as the bill, under the key invoice:<id>:issued:v1, with a unique index on source, template and recipient. A replayed bill cannot queue a second message.
Every amount a customer owes lives in one table, ledger_entries. Bill charges and payment reversals are debits. Payments and voids are credits. The balance is the sum. The API's database role has only SELECT and INSERT on that table, and a trigger refuses the rest:
CREATE TRIGGER ledger_entries_append_only
BEFORE UPDATE OR DELETE ON ledger_entries
FOR EACH ROW EXECUTE FUNCTION app_private.prevent_ledger_mutation();
-- raises: 'ledger entries are append-only; post a reversal entry instead'That design still let one bug through, and an accounting audit caught it. A bill's total includes the customer's whole balance as arrears, which is what the customer needs to see on the bill. Yet every older unpaid bill stayed payable, and each payment was checked against its own bill only.
Bill A: 1,000, unpaid. Bill B: 500 + 1,000 arrears = 1,500. A payment of 1,000 on A and a payment of 1,500 on B were both accepted, for a real debt of 1,500. Two meters, or a voided bill, opened the same hole.
Migration 0080 changed three rules. Issuing a bill carries every open bill of the customer into it, and a carried bill takes no payment. A payment is capped at the bill's remaining amount and at the customer's ledger balance. After any money change, the database sets every bill's status from the ledger, oldest debt first, so paid or unpaid is never worked out from paid_amount against total_amount.
The same mistake was hiding in a dashboard. The website's dashboard added up a page of 250 rows to show what was billed and owed. Now the database sums every row the member is allowed to see, and the dashboard reads that one number.
Back to the outbox. When an upload fails, the phone must decide what the failure means. It sorts every error into four kinds:
| Kind | Examples | What the outbox does |
|---|---|---|
| Deferred | no network, signed out, phone awaiting approval | stops this run and keeps everything for later |
| Retryable | timeouts, 408, 429, 500, 502, 503, 504, OPERATION_IN_PROGRESS |
keeps the command and tries again on a later run |
| Blocked | 400, 403, 404, 409, 410, 412, 422, 501 | stops sending it and shows it to a person for review |
| Unknown | anything else | stops this run and tries again later |
Read the 500 in that table once more. Now the night of 18 October.
The receipt carried a payment provider's transaction number, the reference of a transfer. That number was already on another live receipt from the same provider. A unique index, payments_provider_reference_unique, refused the insert, exactly as designed. The API had no mapping for that error, so it surfaced as a 500. The phones read a 500 as a passing failure, and sent the receipt again.
Now the question from the opening. Why did the idempotency layer not stop it?
Because a key replays successes. The claim lives in the same transaction as the work, and that is exactly what makes it safe: the work and its stored response commit together or not at all. When the insert failed, PostgreSQL aborted the transaction and the claim vanished with it. Nothing remembered the failure, so every resend was a first attempt that failed the same way. Storing failures would be worse, because a failure that is only passing, a deadlock or a restart, would then be replayed forever.
The key did its job. The status code was the bug. The fix is one function: that unique violation becomes a 409, which the station app and the website explain in plain Arabic. On the phone a 409 is blocked, shown for review and never resent.
function duplicateReference(error: unknown): never {
const failure = error as { code?: unknown; constraint?: unknown } | null
if (failure?.code === '23505' && failure.constraint === 'payments_provider_reference_unique') {
throw new ConflictException({ code: 'PAYMENT_REFERENCE_DUPLICATE' })
}
throw error
}The commit that fixed it is timestamped 22:29 the same night. Its integration test sends the same command twice with the same key and expects the same 409 both times. It also checks that a different provider may carry the same number.
Offline phones raise one more question. What if a phone syncs a receipt dated inside a period the station has already closed? Closing a period writes, for every customer, a closing entry and an opening entry that sum to zero, and the closed period has to keep summing to zero. So a trigger moves a late entry to the start of the open period:
CREATE FUNCTION app_private.ledger_entry_into_open_period() RETURNS trigger AS $$
DECLARE open_from timestamptz;
BEGIN
PERFORM pg_advisory_xact_lock_shared(hashtextextended('archive:' || NEW.tenant_id::text, 0));
open_from := app_private.archive_boundary(NEW.tenant_id);
IF NEW.effective_at < open_from AND NEW.entry_kind::text <> 'period_close' THEN
NEW.effective_at := open_from;
END IF;
RETURN NEW;
END $$ LANGUAGE plpgsql;Closing takes the same advisory lock exclusively, per station. A close waits for the writes in progress, and new writes wait for the close.
invoice:<id>:issued:v1.The network will keep dropping, and phones will keep resending. The balance has to be right anyway, and a refusal has to stay a refusal.
This is how I build mobile apps that have to work offline.
Comments
Join the conversation
Sign in with Google to like articles and comment. It takes one tap.