An audit-ready ledger: how MEXAR keeps money records immutable

When a regulator, an auditor, or your own back office asks “what did this transaction look like an hour ago, and who changed it?”, the answer has to be exact. A money business cannot run on records that can be edited in place. So MEXAR was built around a simple rule: monetary records are never overwritten — they are versioned.

Versioned transaction items

The financial parameters of a transaction — its rate, amounts, and currencies — live in transaction items, and every item carries a lifecycle status: active, superseded, or cancelled (with an optional draft).

A correction never mutates the existing numbers. Instead, inside a single database transaction, MEXAR:

1. reads the current active item,
2. inserts a new row with the updated values and status = active, and
3. marks the previous row status = superseded, stamping replaced_by_item_id, replaced_at, and replaced_by_user_id.

Cancellations work the same way, recording cancelled_at and cancelled_by_user_id. The old numbers stay exactly as they were — now linked to the user and moment that replaced them. That chain is the audit trail: every version of every amount, with attribution, available forever.

The database enforces “one active version”

Application logic can have bugs; a unique constraint cannot be argued with. MEXAR pushes the core invariant — at most one active item per transaction — down to the database itself, in a way that works on both MySQL and PostgreSQL.

On MySQL, a generated column collapses to the transaction id only while a row is active, and a unique index guards it:

active_key BIGINT GENERATED ALWAYS AS (
  CASE WHEN status = 'active' AND deleted_at IS NULL
       THEN transaction_id ELSE NULL END
) STORED;

CREATE UNIQUE INDEX uniq_items_active_key ON transactions_items (active_key);

On PostgreSQL, the same guarantee is expressed as a partial unique index:

CREATE UNIQUE INDEX uniq_items_active
  ON transactions_items (transaction_id)
  WHERE status = 'active' AND deleted_at IS NULL;

Historical rows store NULL in the guarded slot, so any number of versions can coexist — but a second active version for the same transaction is physically impossible to insert. The integrity rule survives application bugs, race conditions, and manual database access alike.

Append-only account flows

Balances follow the same philosophy. MEXAR models money in three layers — an Account, its per-currency AccountCurrencyBalance, and a stream of AccountFlow records — and flows are append-only. Every movement writes a new flow row that captures balance_before and balance_after, plus its cost and profit contribution, tagged with an action such as STOCK_IN, INFLOW, OUTFLOW, CONVERSION, DEBT_CLEARANCE, or their revert counterparts (INFLOW_REVERT, OUTFLOW_REVERT).

Because a reversal is itself a new flow rather than a deletion, you can always reconstruct the running balance of any account at any point in time by replaying its flows. Nothing is patched away; corrections are visible as additional entries.

Why it matters to operators

This design is unglamorous on purpose. There is no clever in-place “fix” path, because that is precisely the path auditors distrust. What you get instead:

• Provable history — every rate and amount change is a row, attributed to a user and a timestamp.
• No silent edits — superseded and cancelled records are retained, not rewritten.
• Database-guaranteed integrity — the “one active version” rule holds even if application code misbehaves.
• Reconstructable balances — append-only flows with before/after snapshots let you rebuild any balance on demand.

For a licensed operator, that is the difference between saying your books are trustworthy and being able to show it — line by line, on the day a regulator asks. It is the same principle that runs through the rest of the platform: swappable provider contracts keep the architecture flexible; an append-only ledger keeps it honest.