Append-Only Journal Enforcement with PostgreSQL Roles and a Complete-Journal API

A useful ledger lets an operator inspect what the system accepted before deciding how to correct it. That becomes difficult when the same service account can quietly update a journal's amount, delete its inconvenient predecessor, or truncate a table.

Append-only journal enforcement gives that service a narrower capability: submit a complete new journal through a reviewed entry point. Accepted records stay available for inspection, and mistakes receive new linked records. The practical result is a clearer history for operations staff and a testable write boundary for engineers.

This tutorial builds a bounded PostgreSQL 18 teaching profile and compares it with portable Python/TypeScript models. All accounts and amounts belong to fictional Cedar Teaching Bank. There are no real customer records or claims of legal retention compliance.

Start with the actor

“The journal is immutable” hides a material question: immutable to whom?

Our runtime writer cannot directly insert, update, delete or truncate protected journal tables. It can execute a function that validates and appends a complete journal. A separate no-login owner controls schema objects and grants. That owner remains capable of changing the controls. PostgreSQL's privilege model makes this distinction explicit: ordinary grants and object ownership are different kinds of authority. Official privileges documentation.

The diagram places that owner outside the routine path. Notice that the new fee becomes a new journal; it does not replace the original funding.

Open this diagram at full size.

The operational promise is precise: the named service role's granted capabilities cannot rewrite previously accepted records. Independently retained commitments, backup policy, and administrator investigations require additional controls.

Preserve a prefix, then extend it

Let H be the ordered accepted history and j one complete new journal. An allowed append produces H followed by j; all old records retain their original contents. A denied mutation produces the same H. A retry of an already accepted instruction also leaves H unchanged while returning the original result.

This is a prefix invariant, not a claim that any accepted business instruction was correct. A journal can balance perfectly and still name the wrong beneficiary. The Double-Entry Posting Engine explains the account/sign checks required before a journal may be accepted.

The command contract is strict:

• Entity/ledger, journal ID and durable business-operation ID are explicit.
• The journal contains one supported currency and 2..32 ordered debit/credit legs, covering at least two distinct accounts.
• Each amount is a positive canonical decimal string up to 10^30-1 minor units. Python uses integers, TypeScript bigint, and SQL exact numeric sums.
• Accounts are trusted active records in that same scope and currency.
• Economic time, source availability and open booking period remain distinct from server recording time.

Unknown fields, malformed timestamps, future information and unbalanced totals reject the whole candidate. A current-period adjustment to an older effective month requires explicit trusted authority and a retained reason. That is an educational policy, not a universal accounting recognition rule.

Route all runtime writes through one gate

The SQL profile stores each complete command, including its leg array, and accepted result in one journal row. A header is never committed first while later requests fill in missing legs. Non-null unique constraints protect business identity, journal identity and per-ledger sequence.

The runtime cannot bypass the gate with a direct table insert. Its allowed route resembles:

REVOKE ALL ON ALL TABLES IN SCHEMA d30
FROM PUBLIC, d30_writer, d30_reader;

GRANT USAGE ON SCHEMA d30 TO d30_writer;
GRANT EXECUTE ON FUNCTION d30.post_journal(jsonb)
TO d30_writer;

These lines are only the privilege portion. The complete migration additionally separates owner/runtime roles, removes default public function execution, restricts helper functions, and checks the authenticated session's authorized ledger scope. A caller cannot gain authority by adding "actor":"owner" to a command.

The append function uses SECURITY DEFINER with a fixed search_path of pg_catalog,d30,pg_temp. Temporary schema comes last, and protected objects are schema-qualified. Without that discipline, an attacker-writable lookalike object could change which object a privileged function reaches. The vendor documents this concern and the selective execution-grant pattern. Safe definer functions.

Enabled rejection triggers provide another guard for update/delete/truncate. They help catch accidental owner DML, but the owner can remove them. The acceptance suite deliberately demonstrates that boundary instead of treating the presence of a trigger as permanent retention.

Make acceptance a transaction result

The following flow shows the ordering of decisions. An accepted old identity is resolved before changed current account or period policy; a new command must satisfy the current rules.

All writers for one ledger lock its control row through transaction completion. That row contains a transactional counter. A rollback undoes both the counter increment and journal append. The next writer waits for commit or rollback before deciding what history it sees. PostgreSQL explicit locking.

The mutable counter is internal control state. Financial journal history remains append-only. A PostgreSQL sequence would be a different mechanism: sequence allocations can leave gaps after aborts and do not alone prove committed order. Sequence functions.

An assigned recording timestamp describes when it was evaluated inside the transaction. It is not a precise physical commit instant. A function result returned before an enclosing rollback is not accepted history. The final success boundary is acknowledged COMMIT. Transaction semantics.

Walk through an attempted rewrite

Begin with this synthetic funding journal, in USD minor units:

The bank owns cash as an asset and owes Ada a deposit liability. Both normal-side movements increase by USD1000.00. Debit does not mean “money left” in every account type.

Now the runtime tries to rewrite the original credit as 120000. The actual SQL role lacks UPDATE privilege and receives SQLSTATE 42501. In the portable teaching model, the requested mutation reports APPEND_ONLY_VIOLATION. Both representations preserve the accepted 100000 amounts; their wire error formats are intentionally distinct.

Next, append a new teaching fee journal: debit Ada's deposit liability 250, credit fee income 250. The original funding remains sequence1 and the new journal becomes sequence2. Author-derived normal movements are:

Cash: 100000
Ada: 100000 - 250 = 99750
Fee income: 250
Accumulated debit totals = credit totals = 100250

The fee is a fictional posting example, with no claim about a bank tariff or whether a customer should be charged. If the original funding was wrong, use an authorized exact reversal and replacement, preserving its original observation. Correcting and Reversing Entry Algorithm.

Open the guided lab to step through ten attempts, then compare a large-money boundary and invalid requests. The role selector simulates trusted context; it is not a client-controlled permission field. The lab never connects to SQL. Its static fallback is the diagram and arithmetic above.

Verify the actual boundary

A browser animation cannot establish database permissions. An in-memory array cannot establish durable identity after a process restart. This package therefore keeps two evidence streams separate.

The portable Python and TypeScript suites compare 86 complete transition outputs, including reasons, nullability, original results and resulting state. Independent expected cases include 9007199254740000 + 993 = 9007199254740993, invalid amounts, denied mutations, retry after policy change and 32 maximum-value legs. Caller snapshots must remain unchanged.

The PostgreSQL runner initializes its own disposable cluster, uses actual restricted login sessions and SCRAM credentials generated in process, and does not accept an existing database DSN. Its 24 named evidence groups cover:

• Direct runtime mutation, DDL and owner-role switch denial.
• Two independent sessions, an observed blocking barrier, duplicate/conflicting commands, rollback and per-ledger commit order.
• A lost response followed by unchanged retry; original replay after current account/period closure.
• Invalid replacement rolling back its staged reversal and counter; competing corrections allowing one full reversal.
• Committed identity surviving controlled process restart.
• A sacrificial owner removing the trigger and changing a test record, making the trust boundary observable.

See the raw SQL evidence and parity evidence. SQL returns the stored original result with its original committed status. The portable transition wrapper can label the delivery replayed; no SQL/reducer wire-format equivalence is asserted.

The SQL run enables local fsync, full_page_writes and synchronous_commit. A process restart is still not a power-loss experiment, replica-retention test or disaster-recovery guarantee. WAL settings.

Continue from preserved history

The useful result is inspectable accepted history under a named service boundary. The owner and infrastructure remain trusted, and an accepted instruction can still be mistaken or unauthorized in the business sense. Those are explicit limits, not reasons to leave routine write privileges undefined.

Historical example: not useful for this control. Official platform documentation, reproducible synthetic transactions and observed permission tests supply the necessary evidence without invented customer incidents. The detailed validation record states the exact acceptance evidence and its limits.

Next, Idempotent Journal Posting examines the durable identity that lets a caller recover the original journal when a successful response disappears.