What you'll build: the posting path of a General Ledger — the single most correctness-sensitive operation in the company — over one DataK3 bucket. A journal only posts when it balances (Σdebit = Σcredit) — you enforce that as a hard gate before a single row touches the ledger. It posts idempotently — a journal that arrives twice from a retry, a shard replay, or an at-least-once queue lands once, via INSERT … ON CONFLICT DO UPDATE, not a bare re-INSERT that would raise a duplicate-key error. It reverses cleanly — a mirror journal, debit and credit swapped, that nets the affected accounts back to their pre-post balances. And every posting appends to an immutable journal_audit trail. Pure SQL over gl/core's journals / journal_lines / ledger — no second system, no model in the loop.

What you'll learn:

  • Enforce the double-entry balance invariant as a pre-post gate — reject an unbalanced journal before it can move the ledger, exactly because money is DECIMAL(18,2) and the check is exact.
  • Post idempotently with INSERT … ON CONFLICT (journal_id, line_no) DO UPDATE — and see why a bare re-INSERT is a bug (SQLSTATE 23505), not a retry.
  • Reverse a journal by mirror entry linked with reverses_journal_id, and prove it nets to zero impact — the trial balance stays 0.00.
  • Keep an append-only journal_audit event log, and wrap concurrent posters in a SerializationFailure retry loop so parallel posts never corrupt a balance.
  • Run every step two ways — by prompting an agent over the MCP, or the dodil data CLI.

The problem — and why it matters

The person who owns this path is the controller. Every month they sign a trial balance; every quarter the CFO signs financial statements on top of it. The one outcome they exist to prevent has a name — a restatement — and it is a board-level event: re-audited periods, a filing with the regulator, a hit to the share price, sometimes a clawback of executive pay. A ledger that does not tie is not a bug ticket.

So the posting path reads like a correctness proof, not a feature list. Every journal must balance — debits equal credits to the cent — or it must not post at all. Every posting must be idempotent — a journal that a retrying queue delivers twice must hit the ledger once, or the books are overstated by the amount of the duplicate. Money must be exact — store an amount as a float and 0.1 + 0.2 reads back as 0.30000000000000004, and that drift, multiplied over millions of postings, breaks the balance invariant. And a mistake must be reversible without erasing history — you post a mirror entry, you do not delete the original.

The classic stack meets those requirements with a transactional database plus a wall of application code — dedupe tables, idempotency keys, a numeric library so money doesn't drift, and a warehouse the whole thing is ETL'd into for the trial balance at yesterday's freshness. On DataK3 the journal you post is the row the trial balance sums, over one copy of the rows in one bucket, and the wire itself does the dedupe: an INSERT … ON CONFLICT on the line's primary key posts once no matter how many times it arrives. This is the deliberate stress test of the DataK3 pg wire for finance — and this page proves the load-bearing rows of it live.

PieceLands inPillar
The balance gate (reject unbalanced)a query over journal_lines — no write until it passesSQL
Idempotent posting (post exactly once)journals / journal_lines / ledger (ON CONFLICT)SQL
Reversal (mirror journal, nets to zero)journals / journal_lines (reverses_journal_id)SQL
The audit trail (append-only)journal_audit (uuid PK, plain INSERT)SQL

NOTE

Connect the DODIL MCP once — then every step shows an Ask your agent tab (the default — DODIL is agent-native) and a CLI tab. This whole posting path was built and validated by prompting an agent over this MCP.

This skill consumes accounts / journals / journal_lines / ledger from gl/core. If core isn't installed yet, Step 1 stubs just those masters (same locked shapes, money DECIMAL(18,2)) plus a balanced opening balance, so this runs standalone.

Prerequisites

  • A DODIL organization with the dodil CLI authenticated (dodil auth login) or the DODIL MCP connected to your agent (Claude Code, Cursor, VS Code, Codex). Headless? Check auth_status first — an agent can't do the browser login for you.
  • export BUCKET=gl-suite — one bucket is the whole GL's data plane. (This is the bucket param, default gl-suite; the functional currency is USD, the functional_currency param.) The obvious name gl is rejected — DataK3 requires a bucket name of at least 3 characters — which is why the whole suite lives on gl-suite.

Step 1 — Stub the masters you post into (standalone only)

This skill posts into gl/core's four masters. If gl/core is already in your bucket, skip to Step 2 — those tables and the opening balance exist; you only need journal_audit. Standalone, create the masters with the locked shapes (money DECIMAL(18,2), every non-key column nullable:true — data table create defaults columns to NOT NULL), seed the chart of accounts, post the balanced opening journal (20260701), and materialize the ledger so the trial balance starts at 0.00. Then add this skill's own append-only journal_audit log.

TIP

Journal ids are the date. Every journal in this build is keyed YYYYMMDD — 20260701 is the opening balance, 20260918 is the September rent you'll post and then reverse. A natural, human-readable id you assign yourself is what makes the write idempotent: there is no sequence to race, so a redelivered journal carries the same key and upserts in place.

IMPORTANT

Money is DECIMAL(18,2), never double. A float money column is a bug — 0.1 + 0.2 is not 0.3 in binary floating point, and over millions of postings that drift breaks the balance invariant. Every amount column (debit, credit, ledger.balance) is DECIMAL(18,2).

You

In bucket gl-suite, stub the gl/core masters this posting skill writes to, using the locked shapes with money DECIMAL(18,2) and every non-key column nullable. accounts (key account_id: name, type, normal_balance, parent_account_id long, currency, active boolean); journals (key journal_id: journal_date timestamp, period, source, status draft/posted/reversed, memo, reverses_journal_id long); journal_lines (composite key journal_id, line_no: account_id long, debit DECIMAL(18,2), credit DECIMAL(18,2), line_memo); ledger (key account_id: balance DECIMAL(18,2), currency, as_of timestamp). Also create journal_audit (key audit_id string: journal_id long, event string, event_at timestamp). Seed 20 accounts (5 roots 1000/2000/3000/4000/5000 + 15 leaves), post the balanced opening journal 20260701 (debit Cash 250000.00, Inventory 80000.00; credit Common Stock 330000.00), and materialize the ledger so the trial balance is 0.00.

DODIL MCP tools called
data_bucket_create→data_table_create→data_table_upsert→data_pg
Agent

Created bucket gl-suite + accounts, journals, journal_lines (pk journal_id+line_no; debit/credit DECIMAL(18,2)), ledger, and journal_audit (pk audit_id). Seeded 20 accounts, posted the balanced opening journal 20260701 (SUM(debit)=SUM(credit)=330000.00), and materialized the ledger — 3 rows, trial balance 0.00. journal_audit uses event_at (not the reserved word `at`).

NOTE

event and at are the two column-name traps here. at is a reserved word in the DuckDB SQL dialect — name the timestamp column event_at (a bare at breaks the parser). event is fine unquoted in a SELECT list. If you ever must select a reserved identifier, double-quote it ("at").

Step 2 — The balance gate: reject a journal that doesn't balance

This is the invariant the whole ledger rests on: Σdebit = Σcredit, per journal, before it posts. Stage a candidate journal as status=draft, then run the gate — one aggregate over its lines. If the imbalance isn't 0.00, the journal is rejected and never touches the ledger. Here we stage journal 20260918 (September rent) deliberately short — 9500.00 of rent expense against only 9000.00 of cash — and the gate catches it: a 500.00 imbalance.

You

In gl-suite, stage a draft journal 20260918 for September rent: header status draft, source rent. Then two lines: debit Rent (5300) 9500.00, credit Cash (1100) 9000.00 — deliberately unbalanced. Run the balance gate: SUM(debit), SUM(credit), and SUM(debit)-SUM(credit) as imbalance, grouped by journal_id, and flag whether it may post.

DODIL MCP tools called
data_table_upsert→data_sql
Agent

Staged draft journal 20260918 with two lines (debit 9500.00, credit 9000.00). The gate returns imbalance 500.00 -> UNBALANCED, rejected. It will not be flipped to posted and never touches the ledger.

Through the API the same gate is a 422 carrying the arithmetic, not a generic validation error — the message is built from the two exact Decimal sums, so the controller reads the imbalance rather than guessing at it:

422  unbalanced journal: debit 9500.00 != credit 9000.00 (imbalance 500.00)

Now correct the journal — bump the cash credit to 9500.00. Because journal_lines is merge-keyed on (journal_id, line_no), the fix is an ON CONFLICT upsert of line 2, not a new row. Re-run the gate: 0.00, balanced, cleared to post. (One thing to know for every data pg write here: a data pg DML statement always reports row_count: 0 no matter how many rows it wrote — so confirm a write with a follow-up SELECT, exactly as the re-run gate below does.)

You

In gl-suite, correct journal 20260918 line 2 to credit Cash 9500.00 (was 9000.00) using INSERT ON CONFLICT so it updates the existing line, not a new one. Then re-run the balance gate for journal 20260918.

DODIL MCP tools called
data_pg→data_sql
Agent

Updated line (20260918,2) to credit 9500.00 via ON CONFLICT DO UPDATE. The gate now returns imbalance 0.00 -> balanced, may post.

NOTE

The gate has a sibling: the closed-period lock. posting.write_journal() runs two guards before it writes — the balance gate above, and assert_period_open(), which rejects an operational posting into a closed period with a 409 (source='close' is exempt, or a period could never be closed at all). Validated live on this bucket: 2026-07 is closed, and a posting into it is rejected from all five posting routes in the suite. Both are application controls, not database constraints — DataK3 accepts a subquery CHECK and a FOREIGN KEY at DDL and then never enforces either, and rejects CREATE TRIGGER / CREATE VIEW / REVOKE INSERT outright, so a cross-table rule can only be held by leaving exactly one code path that writes. The full enforcement probe and the honest COVERED / NOT-COVERED scope label live in Close the period.

Step 3 — Post idempotently, and prove no double-post

The journal balances, so post it: flip journals.status to posted (with ON CONFLICT, so the flip is itself idempotent), append a journal_audit posted event, and re-materialize the ledger as the signed net over every posted-or-reversed journal. The ledger moves: Cash drops 9500.00 (to 58260.00) and Rent goes to 9500.00. The trial balance stays exactly 0.00.

NOTE

The balances from here on are the real gl-suite ones. This bucket carries gl/core's full seed and the other components' journals — 21 journals across 2026-07 … 2026-09 — so Cash starts at 67760.00, not at the Step 1 stub's opening figure. Follow the deltas if you're running the stub alone; the absolute numbers below are what the shared bucket returns.

You

In gl-suite, post journal 20260918. Flip its status to posted with INSERT ON CONFLICT (journal_id) DO UPDATE. Append a journal_audit event (a fresh uuid audit_id, journal_id 20260918, event posted, event_at 2026-09-18). Then re-materialize the ledger: for every journal with status in (posted, reversed), sum debit minus credit per account into ledger with ON CONFLICT (account_id) DO UPDATE. Show the affected accounts (Cash 1100, Rent 5300) and the trial balance.

DODIL MCP tools called
data_pg→data_table_upsert→data_sql
Agent

Posted journal 20260918. Ledger moved: Cash 67760.00 -> 58260.00, Rent 0.00 -> 9500.00. Trial balance still 0.00 across 15 ledger rows. Appended one journal_audit posted event.

Now the proof that matters most for a ledger: posting is idempotent. A journal that arrives twice — a retry, a shard replay, an at-least-once queue redelivery — must post once. A bare re-INSERT of a committed line does not do that; it raises a duplicate-key error. The correct re-post is INSERT … ON CONFLICT DO UPDATE — run it any number of times and the line count, the ledger, and the trial balance never move.

You

In gl-suite, first try a bare re-INSERT of journal 20260918 line 1 to show it is rejected. Then re-post both of its lines with INSERT ON CONFLICT (journal_id, line_no) DO UPDATE, re-materialize the ledger the same way, and confirm journal_lines for 20260918 is still 2, the ledger is still 15 rows, Cash is still 58260.00, Rent still 9500.00, and the trial balance is still 0.00.

DODIL MCP tools called
data_pg→data_sql
Agent

The bare re-INSERT raised SQLSTATE 23505 (duplicate key (journal_id,line_no)=(20260918,1)) — a re-INSERT is NOT an upsert. Re-posting both lines ON CONFLICT and re-materializing left journal_lines=2, ledger_rows=15, Cash 58260.00, Rent 9500.00, trial balance 0.00 — no double-post.

IMPORTANT

ON CONFLICT is the idempotency mechanism, not a nicety. The DataK3 pg wire (DuckDB dialect) supports INSERT … ON CONFLICT (<pk>) DO UPDATE — verified live. A committed primary key that is re-INSERTed bare raises SQLSTATE 23505. So the posting engine (and any queue consumer that replays) must write with ON CONFLICT (or the managed data table upsert); that is what makes a redelivered journal post once.

Step 4 — Reverse a journal — a mirror entry that nets to zero

You never delete a posted journal — that erases history an auditor needs. You reverse it: post a mirror journal with the debits and credits swapped, linked back by reverses_journal_id. Mark the original status=reversed — it stays on the books; the mirror offsets it. The rent above went into the wrong period, so it gets reversed by 20269918. Re-materialize, and the affected accounts net straight back to their pre-post balances: Cash returns to 67760.00, Rent to 0.00. Net impact of the two journals: zero.

You

In gl-suite, reverse journal 20260918 by posting a mirror journal 20269918: status posted, source reversal, reverses_journal_id 20260918, memo Rent posted to the wrong period, with the debits and credits swapped — credit Rent (5300) 9500.00, debit Cash (1100) 9500.00. Mark journal 20260918 status reversed. Append two journal_audit events (20269918 posted, 20260918 reversed). Re-materialize the ledger over posted+reversed journals, then show Cash, Rent, the net of the two journals, and the trial balance.

DODIL MCP tools called
data_table_upsert→data_pg→data_sql
Agent

Posted mirror journal 20269918 (reverses_journal_id=20260918), marked 20260918 reversed, appended two audit events. After re-materializing: Cash back to 67760.00, Rent 0.00, net(20260918+20269918)=0.00, trial balance 0.00 across 15 ledger rows — the reversal nets to zero impact.

The trail the auditor actually reads is journal_audit, and it names a person:

journal_id  event                  event_at
20260918    posted:dev-controller  2026-09-18 00:00:00
20260918    reversed:dev-controller 2026-09-18 00:00:00

The suffix is the gateway-vouched user, taken from X-Dodil-User and passed to posting.write_journal(..., actor=user["sub"]) — not the service account that holds the bucket credential. That distinction is the whole point of the two-plane rule: every row in the ledger was written by one service account, and the audit trail still tells you which human asked for it. On this run it reads dev-controller because the components were driven locally with DEV_ALLOW_ANON=1 and DEV_USER_SUB=dev-controller; deployed behind the gateway it is the pool identity (the 2026-07 close row, for instance, carries locked_by = [email protected]).

NOTE

Generating the audit UUID in SQL: bare UUID() only. If you'd rather the database mint the audit_id than pass a literal, the generator is UUID() (or GENERATE_UUID()) — INSERT INTO journal_audit (audit_id, …) VALUES (UUID(), …). gen_random_uuid() is not available here, and a cast on the generator (UUID()::text) raises a parse error (SQLSTATE 42601) — call the bare function and let the column's uuid type stand.

TIP

A reversed journal is not deleted — it's offset. Both the original and its mirror stay in journal_lines, and the ledger sums lines from journals with status IN ('posted','reversed'). That's why the reversal nets to zero and the full history (post, then reverse) survives for the audit trail. The journal_audit log records the same story: a posted event, then a reversed event.

NOTE

A recurring/scheduled accrual reversal is different — don't mark the original reversed. The pattern above is error correction: the original was wrong, so it's marked status='reversed' and offset. A scheduled accrual (e.g. an accrued expense you book at month-end and unwind on day 1 of next month) is a legitimate posting, not a mistake — so both journals stay posted, linked by reverses_journal_id, with a distinct source (e.g. accrual / accrual-reversal), and the reversal posts on day 1 of the next open period. Marking the original reversed would drop a valid accrual from any status='posted' report and push the offset into the wrong period, distorting both months' P&L.

Step 5 — Concurrency: the SerializationFailure retry loop

Everything above is one poster at a time. In production, many post at once — and the DataK3 tables engine is serializable, so two transactions touching the same account can collide and one is aborted with a SerializationFailure (SQLSTATE 40001). The rule is simple: retry the whole transaction. Because every write is INSERT … ON CONFLICT DO UPDATE, a retry is safe — it re-applies the same balanced post and lands once. In the package this is nine lines in posting.py, the module every journal write in the GL goes through:

# posting.py — the retry loop the sole writer runs every write inside
def retry(fn, attempts: int = 5):
    """Run a write under the serializable tables engine, retrying a SerializationFailure
    (SQLSTATE 40001) — two closers, or a closer racing a poster, will abort one txn."""
    for attempt in range(attempts):
        try:
            return fn()
        except DBAPIError as e:  # SQLAlchemy wraps the psycopg error; match on SQLSTATE
            if getattr(e.orig, "sqlstate", None) == "40001" and attempt < attempts - 1:
                continue
            raise

Retrying is only safe because of what it retries. write_journal() is the path — no other code in the GL touches journals or journal_lines — and it runs both gates, writes idempotently, and appends the acting user to the trail:

# posting.py — the sole writer (docstring, verbatim from the package)
def write_journal(s, header: dict, lines: list, actor: Optional[str] = None,
                  audit_event: str = "posted", skip_balance_check: bool = False) -> dict:
    """The ONE path that writes `journals` + `journal_lines`.
 
    Runs both gates, then writes idempotently — `INSERT … ON CONFLICT (pk) DO UPDATE`, so a
    retry / at-least-once redelivery / shard replay posts the journal exactly once — and
    appends the acting user to the append-only `journal_audit` trail. Commits.
 
    The caller re-derives the ledger AFTERWARDS with `rederive_ledger()`: DataK3 has no
    read-your-writes inside an open txn, so the re-derive must be a second committed txn.
    """

NOTE

The posting path is validated live under concurrent load. This page proves the data correctness query by query; the same logic was also deployed for real as a pure-SQL image-mode Ignite app (service account carrying only k3.editor + ignite.app-developer — no ignite.model-user, because there is no model in a ledger post) and hammered with true concurrent HTTP: 24 simultaneous posts of the same journal collapsed to exactly one (two lines, not forty-eight; the ledger moved once); 60 distinct journals fired at once all posted, trial balance 0.00, with 37 SerializationFailure/backpressure retries absorbed automatically; unbalanced journals were rejected under load with nothing leaked; and a fresh-connection re-read found zero ledger-vs-source mismatches across 112 journals. The DataK3 pg wire is finance-ready under load. Two things that combination needs: a client-side admission semaphore below the tables reader's hard 16-concurrent-read cap, and a pinned-warm replica (--reserved 1 --max-replicas 1). Deploy is now the suite app's job — see The suite below.

How the pillars map

This skill is deliberately one pillar — SQL. No graph, no vector, no model. Correctness in a ledger is arithmetic and constraints, not judgement, and that's what makes the posting path cheap, auditable, and always provable.

JobThe usual stackOn DataK3
"Do the debits equal the credits?"App-level validation before an INSERTone SUM(debit) - SUM(credit) gate over journal_lines
Post a journal exactly onceDedupe table + idempotency keys + a queueINSERT … ON CONFLICT (journal_id, line_no) DO UPDATE — the wire dedupes
Running per-account balanceA trigger, or a nightly warehouse rebuildre-derive ledger ON CONFLICT in a second committed txn — no ETL
Reverse a mistakeA soft-delete flag + compensating logica mirror journal linked by reverses_journal_id; nets to zero
Money that never driftsA numeric library, careful roundingDECIMAL(18,2) columns — exact SUM(), no float tail
Survive concurrent postersRow locks, an app-side mutexserializable engine + a SerializationFailure retry loop

One bucket, one bill, one auth context — the journal you post is the row the trial balance sums, over one copy of the rows. This composes directly onto gl/core and is consumed in turn by gl/period-close (which locks the period this posting path writes into).

Routes

Everything above ran as raw SQL to prove the invariant. The download (see Get the code) wraps exactly that logic in a small FastAPI app, routes.py — the posting engine as an HTTP API. It's what you deploy, and quoting it is documenting the finance rules, because every rule is a line of code you can point at.

The connection and the one write helper live in db.py — a DataK3 bucket is a Postgres endpoint (db name = the bucket, user = the literal token, password = your DODIL token), so there's no data connect step in code, just fixed region constants. upsert() is the primitive underneath every write, and it is the whole idempotency story: money over the pg wire, INSERT … ON CONFLICT DO UPDATE. Journals don't call it directly, though — they go through posting.write_journal(), the GL's sole writer, which is where the two gates live.

# db.py — INSERT ... ON CONFLICT DO UPDATE (idempotent keyed write); DO NOTHING for pure edge rows
def upsert(session, model, rows, key):
    keys = [key] if isinstance(key, str) else list(key)
    table = model.__table__
    # normalise to a uniform column set — a multi-row VALUES needs every row to name the
    # same columns; fill any a caller omitted with None.
    cols = {c for r in rows for c in r}
    rows = [{c: r.get(c) for c in cols} for r in rows]
    stmt = pg_insert(table).values(rows)
    update_cols = [c.name for c in table.columns if c.name not in keys and c.name in cols]
    if update_cols:
        stmt = stmt.on_conflict_do_update(
            index_elements=keys,
            set_={c: getattr(stmt.excluded, c) for c in update_cols},
        )
    else:
        # every column is part of the key (a pure edge/junction row) — nothing to update.
        stmt = stmt.on_conflict_do_nothing(index_elements=keys)
    session.execute(stmt)

The and c.name in cols filter is load-bearing: a caller that writes only some columns (the reversal below re-writes a journal header with no lines) must not have the omitted ones clobbered to NULL by the DO UPDATE set.

On DataK3 a bare re-INSERT of an already-committed primary key raises 23505 — a plain INSERT is not an upsert on re-write. upsert makes a retry, a shard replay, or an at-least-once redelivery land the row once. And because it runs through SQLAlchemy → psycopg → the pg wire, the Numeric(18,2) amounts are written exactly; the gRPC data_table_upsert path would coerce an integer 0 into a DECIMAL column, so money never goes through it.

The balance gate — a write-path guard, not a DB constraint. assert_balanced runs before a single row writes. An unbalanced journal is rejected with a 422; nothing touches the ledger. It's exact because amounts are Decimal — the live run rejected a 9500.00 / 9000.00 journal with imbalance 500.00, then accepted it at 9500.00 / 9500.00 (imbalance 0.00). Note where it lives: not in routes.py, but in posting.py, the module that owns the only write path, so no route can post around it:

# posting.py — gate 1: THE balance gate. Reject before any write; exact because amounts are Decimal.
def assert_balanced(lines: Iterable[dict]) -> tuple:
    """Σdebit = Σcredit, to the cent, BEFORE anything writes. Exact because every amount is
    a Decimal over a DECIMAL(18,2) column — a float column would make this check flaky."""
    total_debit = sum((money(l.get("debit")) for l in lines), ZERO)
    total_credit = sum((money(l.get("credit")) for l in lines), ZERO)
    if total_debit != total_credit:
        raise HTTPException(
            422, f"unbalanced journal: debit {total_debit} != credit {total_credit} "
                 f"(imbalance {total_debit - total_credit})")
    return total_debit, total_credit

Its sibling, assert_period_open, is gate 2 — a 409 if the period is closed, with source='close' exempt so the closing journal can post into the month it closes:

# posting.py — gate 2: the closed-period lock
def assert_period_open(s, period: Optional[str], source: Optional[str]) -> None:
    if not period or source == CLOSING_SOURCE:
        return
    status = s.execute(text("SELECT status FROM periods WHERE period_id = :p"),
                       {"p": period}).scalar()
    if status == "closed":
        raise HTTPException(409, f"period {period} is closed — operational posting rejected")

Post a balanced journal — the money step. POST /journals gates, flips the header to posted, writes the lines ON CONFLICT (money over the pg wire), appends the audit event, commits — then re-derives the ledger in a second transaction. That second txn is load-bearing: DataK3 has no read-your-writes inside an open txn, so re-deriving the balance in the same transaction that wrote the lines would read a stale ledger. The re-derive opens a fresh session and recomputes SUM(debit) - SUM(credit) — idempotent, never += — so N concurrent re-posts land the balance once:

# routes.py — post a balanced journal, exactly once (gates → sole writer → SECOND-txn re-derive)
@router.post("/journals")
def post_journal(j: JournalIn,
                 user: dict = Depends(require_permission("gl:journal:post")),
                 s: Session = Depends(db)):
    as_of = j.journal_date or posting.now()
    lines = [{"journal_id": j.journal_id, "line_no": l.line_no, "account_id": l.account_id,
              "debit": l.debit, "credit": l.credit, "line_memo": l.line_memo}
             for l in j.lines]
    header = {"journal_id": j.journal_id, "journal_date": as_of, "period": j.period,
              "source": j.source, "status": "posted", "memo": j.memo,
              "reverses_journal_id": None}
    posting.write_journal(s, header, lines, actor=user["sub"])   # ── txn 1 commits ──
    posting.rederive_ledger([l.account_id for l in j.lines], as_of, FUNCTIONAL_CURRENCY)
    return {"journal_id": j.journal_id, "status": "posted", "lines": len(j.lines),
            "posted": True}

Note what the route doesn't do: it never calls upsert on journals or journal_lines itself, and it never runs the gates inline. It hands a header and lines to posting.write_journal() and passes actor=user["sub"] — the gateway-vouched identity — so the audit row records who. Then it re-derives.

The re-derive is the idempotent recompute, scoped to the touched accounts (DuckDB has no = ANY(array), so the ids are inlined as an IN (…) of validated ints), ON CONFLICT (account_id) DO UPDATE:

# posting.py — the SECOND-txn ledger re-derive: fresh session, idempotent SUM, ON CONFLICT
def rederive_ledger(account_ids=None, as_of: Optional[datetime] = None,
                    currency: str = "USD") -> None:
    where = " AND j.status IN ('posted','reversed') "
    if account_ids is not None:
        ids = ",".join(str(int(a)) for a in dict.fromkeys(account_ids) if a is not None)
        if not ids:
            return
        where += f" AND jl.account_id IN ({ids}) "
 
    def _write():
        with SessionLocal() as s2:      # a second COMMITTED txn after the lines commit
            s2.execute(
                text("INSERT INTO ledger (account_id, balance, currency, as_of) "
                     "SELECT jl.account_id, SUM(jl.debit) - SUM(jl.credit), "
                     "       COALESCE(MAX(a.currency), :cur), :as_of "
                     "  FROM journal_lines jl "
                     "  JOIN journals j ON j.journal_id = jl.journal_id "
                     "  LEFT JOIN accounts a ON a.account_id = jl.account_id "
                     " WHERE 1=1 " + where +
                     " GROUP BY jl.account_id "
                     "ON CONFLICT (account_id) DO UPDATE "
                     "  SET balance = EXCLUDED.balance, as_of = EXCLUDED.as_of, "
                     "      currency = EXCLUDED.currency"),
                {"cur": currency, "as_of": as_of or now()})
            s2.commit()
    retry(_write)

Two details in there are scars, not style. The currency comes from COALESCE(MAX(a.currency), :cur) — the account's own currency, with the argument only as a fallback — and the whole write runs inside retry. Both are explained in What running all six on one bucket found, below.

Live-verified on gl-suite: posting the balanced September-rent journal moved Cash 67760.00 → 58260.00 and Rent 0.00 → 9500.00, trial balance still 0.00 across 15 ledger rows. Re-posting the same journal left journal_lines = 2, ledger_rows = 15, and the balances unchanged — exactly once. (A bare re-INSERT of a committed line raised 23505, as it must.)

Reverse a journal — a mirror that nets to zero. POST /journals/{journal_id}/reverse posts a mirror journal with debit and credit swapped, linked by reverses_journal_id, marks the original reversed (it stays on the books), audits both sides, and re-derives — same second-txn recompute. Live, the reversal returned Cash to 67760.00 and Rent to 0.00, net(20260918 + 20269918) = 0.00, trial balance 0.00, with the original journal status = reversed and both audit events on record. It carries its own permission — gl:reversal:approve, not gl:journal:post:

# routes.py — reverse by a mirror journal (debit/credit swapped), original stays on the books
@router.post("/journals/{journal_id}/reverse")
def reverse_journal(journal_id: int, r: ReverseIn,
                    user: dict = Depends(require_permission("gl:reversal:approve")),
                    s: Session = Depends(db)):
    orig = s.get(Journal, journal_id)
    if not orig:
        raise HTTPException(404, "no such journal")
    if orig.status == "reversed":
        raise HTTPException(409, "journal already reversed")
    orig_lines = s.execute(
        select(JournalLine).where(JournalLine.journal_id == journal_id)
    ).scalars().all()
    if not orig_lines:
        raise HTTPException(422, "journal has no lines to reverse")
    as_of = r.journal_date or posting.now()
    period = r.period or orig.period
 
    # the mirror — debit and credit swapped, linked by reverses_journal_id. It goes through
    # the SAME guard, so a reversal into a closed period is rejected 409 like any posting.
    mirror = {"journal_id": r.reversal_journal_id, "journal_date": as_of, "period": period,
              "source": "reversal", "status": "posted",
              "memo": r.memo or f"Reversal of journal {journal_id}",
              "reverses_journal_id": journal_id}
    mirror_lines = [{"journal_id": r.reversal_journal_id, "line_no": ln.line_no,
                     "account_id": ln.account_id, "debit": ln.credit,   # swapped
                     "credit": ln.debit,
                     "line_memo": f"Reverse: {ln.line_memo or ''}".strip()}
                    for ln in orig_lines]
    posting.write_journal(s, mirror, mirror_lines, actor=user["sub"])
 
    # the original stays on the books, marked reversed — read the row, merge, write the FULL
    # row back through the same guarded path (its lines are unchanged, so none are passed).
    orig_row = {c.name: getattr(orig, c.name) for c in Journal.__table__.columns}
    orig_row["status"] = "reversed"
    posting.write_journal(s, orig_row, [], actor=user["sub"], audit_event="reversed",
                          skip_balance_check=True)
    posting.rederive_ledger([ln.account_id for ln in orig_lines], as_of, FUNCTIONAL_CURRENCY)
    return {"reversed_journal_id": journal_id, "reversal_journal_id": r.reversal_journal_id,
            "status": "reversed", "reversed": True}

Even the "mark it reversed" step goes through write_journal — with skip_balance_check=True, because it writes a header and no lines. That is the sole-writer rule taken seriously: there is no back door, not even for a one-column status flip, which is also how the reversed event gets into journal_audit with the right actor attached.

Reads round it out — GET /journals/{journal_id} (header + lines), GET /journals/{journal_id}/audit (the trail), GET /ledger/{account_id}, and GET /trial-balance (SELECT SUM(balance) FROM ledger, the one number a controller signs). Adding a new posting op touches only routes.py: write via posting.write_journal(...), then posting.rederive_ledger(...) after the commit (see EXTENDING.md).

What running all six on one bucket found

Every GL component in this suite was built and validated on its own bucket, and every one of them passed. Then all six were pointed at gl-suite — one chart of accounts, one ledger, one set of journals — and the composition immediately broke in ways none of the six could have detected alone.

The bug that belongs to this page: POST /journals was not the only route writing journal lines, and it was not the only one that forgot to re-derive. The FX revaluation had the omission first — it posted a perfectly balanced journal and never touched ledger — but the audit that followed found the same omission in three other posting routes. Five routes wrote journal lines; they disagreed about whether ledger was current; and which one you happened to call last decided what the trial balance and the balance sheet said. Standalone, not one of them was wrong: each component was the only writer on its own bucket, so its own view was always self-consistent. The inconsistency is the composition.

The fix is structural rather than a patch to five call sites. There is now exactly one function that writes journals and journal_lines — posting.write_journal() — and the contract that comes with it is that the caller re-derives afterwards, via posting.rederive_ledger(account_ids=…), in a second committed transaction. The second txn is not defensiveness: DataK3 has no read-your-writes inside an open transaction, so a re-derive that ran in the same txn as the lines would sum a ledger that cannot yet see them. And the re-derive is a full SUM(debit) - SUM(credit), never a +=, so N concurrent re-posts land the balance once.

Two smaller scars from the same run are visible in the rederive_ledger code above. The currency is read from accounts.currency, because an earlier version stamped every touched ledger row with the calling component's FUNCTIONAL_CURRENCY — quietly wiping the EUR marker on the EUR bank account that the FX revaluation reads to pick its rate pair, so any posting anywhere disarmed the reval (that story is Revalue foreign-currency balances). And the write runs inside retry, because with six components on one bucket, a closer racing a poster is no longer hypothetical.

The general lesson is worth more than any of the five bugs: a component validated alone proves only that it is self-consistent. Composition is a separate proof, and it needs one bucket.

Auth — config at the edge, a role gate in the app

On Ignite, end-user login is configuration, not code. The GL deploys with the gl-suite dodil-appid pool attached (user_pool: gl-suite in .dodil/deploy.yaml) and the per-cluster Ignite gateway runs the entire browser login at the edge — PKCE S256 against the pool issuer, an AEAD-sealed host-only session cookie, single-flight refresh, EdDSA JWT verification against trust anchors this app does not hold — then injects the verified identity into every request it forwards: X-Dodil-User (sub, email, connection, app_roles), X-Dodil-User-Jwt (the raw verified token, carrying the catalog-expanded permissions claim) and X-Dodil-Auth-Source. Any inbound copy of those headers is stripped first, so a caller can never forge them.

What survives in the package is a small auth.py that ships no verifier — no JWKS client, no issuer/audience env, no crypto dependency, and no pyjwt in requirements.txt. It reads the injected header and keeps the one job the app still owns: role-based gating.

# auth.py — a header-trust reader, not a verifier (the gateway already checked the JWT)
def current_user(request: Request) -> dict:
    raw = request.headers.get("x-dodil-user")   # {"sub","email","connection","app_roles"}
    ...                                          # + permissions read off x-dodil-user-jwt
    raise HTTPException(401, "end-user login required — no X-Dodil-User from the gateway")
 
def require_permission(perm: str):
    """Gate a route on a pool permission: Depends(require_permission("gl:journal:post"))."""
    def _dep(user: dict = Depends(current_user)) -> dict:
        if perm not in user["permissions"] and perm not in user["roles"]:
            raise HTTPException(403, f"missing permission: {perm}")
        return user
    return _dep

The GL is the first DODIL module on namespaced permissions — <module>:<object>:<verb>, so one customer pool can carry every ERP module's roles without collision (gl:journal:post is not ap:journal:post). Across all six components the audit left exactly three gates standing:

PermissionGatesWhy this one and not the rest
gl:journal:postevery route that posts a journalit moves money
gl:reversal:approvereversing a posted, audited journalerasing an audited entry is an approval act, not a posting act — deliberately split out of gl:journal:post so the person who posts is not the person who can un-post
gl:period:closeclosing a periodit freezes a month: after it, operational postings into that period are rejected 409

This component is where two of the three gates land, and the split between them is the point: POST /journals carries gl:journal:post, while POST /journals/…/reverse carries gl:reversal:approve. Posting a journal and erasing an audited one are different acts, so they are different permissions — the accountant who posts cannot un-post. GET /journals/…, GET /journals/…/audit, GET /ledger/… and GET /trial-balance are current_user only.

Everything else takes a signed-in user and nothing more. Reads, ledger re-derives and report materializations move no money and are idempotent, so gating them would be ceremony — and ceremony is exactly what an auditor discounts.

The pool is created once for the whole suite, with the role catalog those gates check — accounting's segregation of duties expressed as permissions:

You

Create a dodil-appid pool gl-suite with email+password, and set its role catalog: an accountant may post journals; a controller may also approve reversals and close a period.

Agent

Pool gl-suite created — issuer https://appid.dodil.io/ihdiash/gl-suite, audience pool:gl-suite, email+password (local) enabled. Catalog set: accountant = gl:journal:post; controller adds gl:reversal:approve and gl:period:close. A user's next token carries app_roles plus the catalog-expanded permissions claim the gates read.

The two-plane rule is unchanged: the pool identifies the user; the app still reaches DataK3 through its own service account (sa_token.py mints and refreshes a client_credentials token for the pg-wire password) — an app-user is never a bucket principal. Locally, with no gateway in front of uvicorn routes:app, opt in to a stub identity with DEV_ALLOW_ANON=1; the stub carries no permissions unless you grant them (DEV_USER_PERMISSIONS=gl:journal:post,…), so the gated routes stay gated on a laptop too. The full flow — creating the pool, the redirect_uris allowlist, what the gateway injects, and the off-gateway path where you do verify the pool JWT yourself — is App authentication; the catalog mechanics are App roles.

Get the code

The package is a real download — code/gl-journal-entry/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only (no Dockerfile — deploy is the suite app's job, see below):

models.py          # SQLAlchemy — accounts, journals, journal_lines, ledger, journal_audit (money Numeric(18,2))
routes.py          # FastAPI    — post a balanced journal + reverse (both gates, both permissions)
posting.py         # THE guarded write path: balance gate + closed-period lock + the ledger re-derive (SHARED)
db.py              # the engine + the ON CONFLICT upsert helper every write uses (SHARED)
auth.py            # gateway header-trust identity — the app ships NO auth code (SHARED)
sa_token.py        # mints/refreshes the app's service-account token = the pg-wire password (SHARED)
PLATFORM.md        # the platform invariants you COPY (not generate) — identical in every package
.env.example       # BUCKET + PG_HOST/PG_PORT + DODIL_TOKEN + FUNCTIONAL_CURRENCY (no issuer, no audience)
requirements.txt   # sqlalchemy, psycopg[binary], fastapi, uvicorn, pydantic, httpx

The five files marked SHARED are byte-identical across all six GL packages and the suite app — that is what makes six components compose into one binary without a merge. PLATFORM.md travels with the tar on purpose: if you are copying this package into a customer build, it is the list of lines to copy verbatim rather than regenerate.

Run it — point .env at your bucket, create the tables from the models, serve:

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
 
cp .env.example .env      # then set DODIL_TOKEN (your `dodil auth login` token)
# BUCKET defaults to "gl-suite"; create it once (Step 1), then create the tables from the models:
python -c "import db, models; models.Base.metadata.create_all(db.engine)"
 
# no gateway in front of uvicorn, so opt into a stub identity and grant it the two gates this
# component uses — without them, POST /journals and .../reverse correctly 403.
export DEV_ALLOW_ANON=1 DEV_USER_SUB=dev-controller
export DEV_USER_PERMISSIONS=gl:journal:post,gl:reversal:approve
uvicorn routes:app --reload

Customize — the decisions this skill asks you

Q1 · functional_currency — the reporting currency

"What is the functional (reporting) currency the ledger is kept in?" → USD (default) stamps every ledger row this skill writes. It must match gl/core's functional_currency — set it once in core and every downstream skill inherits it. gl/multi-currency later revalues foreign-currency balances into it.

Q2 · audit_log — write the append-only trail?

"Write an append-only journal_audit event (posted | reversed) on every posting?"

  • true (default) → each post and reversal appends one journal_audit row (a fresh uuid PK, so a plain INSERT is safe — a new uuid never collides). This is the immutable history a controller reads; ## Test asserts 3 events after a post + reverse.
  • false → no audit trail; the journals.status transitions are the only history. Keep it on for a real ledger — an auditor expects the event log.

Q3 · concurrency_retries — how many SerializationFailure retries?

"How many attempts should the write path make before giving up on a SerializationFailure?" → 5 (posting.retry(fn, attempts=5)). The tables engine is serializable, so two posters hitting the same account — or a closer racing a poster — can collide, and one txn is aborted with SQLSTATE 40001. Every write in posting.py runs inside that loop. Idempotent ON CONFLICT writes make every retry safe: a re-run posts once.

Test

Every command below ran live against DataK3 on 2026-09-08, on bucket gl-suite (org IHDIASH) — with all six GL components running together on that one bucket, not on a per-component throwaway. The bucket is still up. The default branch is {functional_currency: USD, audit_log: true, concurrency_retries: 5}. Real returned values are inline.

# 1) the balance gate REJECTS an unbalanced journal (debit 9500.00 vs credit 9000.00)
dodil data sql -b "$BUCKET" \
  "SELECT SUM(debit)-SUM(credit) AS imbalance FROM journal_lines WHERE journal_id = 20260918"
#  imbalance = 500.00   (before the fix) -> rejected 422; after the fix -> 0.00 -> may post
 
# 2) the whole bucket ties: 21 journals, Sdebit = Scredit, trial balance exactly 0.00
dodil data sql -b "$BUCKET" \
  "SELECT (SELECT COUNT(*) FROM journals) AS journals,
          (SELECT SUM(debit) FROM journal_lines) AS total_debit,
          (SELECT SUM(credit) FROM journal_lines) AS total_credit,
          (SELECT COUNT(*) FROM ledger) AS ledger_rows,
          (SELECT SUM(balance) FROM ledger) AS trial_balance"
#  journals = 21, total_debit = 1286470.00, total_credit = 1286470.00,
#  ledger_rows = 15, trial_balance = 0.00
 
# 3) no double-post: a bare re-INSERT of a committed line raises 23505; the ON CONFLICT re-post is a no-op
dodil data pg -b "$BUCKET" \
  "INSERT INTO journal_lines (journal_id, line_no, account_id, debit, credit, line_memo)
   VALUES (20260918, 1, 5300, 9500.00, 0.00, 'Rent')"
#  ERROR: duplicate key value violates unique constraint "journal_lines_pkey" ... (SQLSTATE 23505)
#  (re-posting both lines ON CONFLICT + re-deriving -> rent_lines 2, ledger_rows 15, trial_balance 0.00)
 
# 4) reversal nets to zero — Cash returns to its pre-post balance, the two journals cancel
dodil data sql -b "$BUCKET" \
  "SELECT (SELECT balance FROM ledger WHERE account_id=1100) AS cash,
          (SELECT balance FROM ledger WHERE account_id=5300) AS rent,
          (SELECT SUM(debit)-SUM(credit) FROM journal_lines WHERE journal_id IN (20260918,20269918)) AS net_pair,
          (SELECT status FROM journals WHERE journal_id=20260918) AS orig_status,
          (SELECT reverses_journal_id FROM journals WHERE journal_id=20269918) AS mirror_of"
#  cash = 67760.00, rent = 0.00, net_pair = 0.00, orig_status = reversed, mirror_of = 20260918
 
# 5) the append-only audit trail — one row per posting event, naming the acting user
dodil data sql -b "$BUCKET" \
  "SELECT journal_id, event, event_at FROM journal_audit WHERE journal_id = 20260918 ORDER BY event_at"
#  20260918  posted:dev-controller    2026-09-18 00:00:00
#  20260918  reversed:dev-controller  2026-09-18 00:00:00
 
# 6) the closed-period lock — 2026-07 is closed, so an operational posting into it is rejected 409
dodil data sql -b "$BUCKET" "SELECT period_id, status, locked_by FROM periods ORDER BY period_id"
#  2026-07  closed  [email protected]
#  2026-08  open
#  2026-09  open
 
# 7) drop-in clients: same bucket, your own psql / cypher-shell
dodil data connect "$BUCKET"            # pg / bolt / grpc endpoints
#  pg   postgresql://token:…@pg.uk-lon-1.dodil.io:5432/gl-suite

One-shot

With the DODIL MCP connected, paste this to run the whole posting path at once:

On DataK3 bucket `gl-suite`, post a balanced journal to the ledger exactly once, then reverse it. Confirm each step.
(The bucket name must be at least 3 characters, so it is `gl-suite`, not `gl`.)
 
1. (Standalone only — skip if gl/core is present) Stub gl/core's masters (accounts, journals, journal_lines,
   ledger) with the locked shapes, money DECIMAL(18,2), every non-key column nullable, plus journal_audit
   (key audit_id string: journal_id, event, event_at). Seed the 20-account chart + the balanced opening
   journal 20260701 (debit Cash 250000.00, Inventory 80000.00; credit Common Stock 330000.00); materialize
   the ledger -> trial balance 0.00.
2. Stage a draft journal 20260918 (September rent): debit Rent 5300 9500.00, credit Cash 1100 9000.00.
   Run the balance gate SUM(debit)-SUM(credit) -> imbalance 500.00, REJECT (422 through the API). Correct
   line 2 to credit 9500.00 with ON CONFLICT; gate -> 0.00, may post.
3. Post journal 20260918: flip status posted (ON CONFLICT), append a journal_audit posted event naming the
   acting user, re-derive the ledger over status IN (posted,reversed) with ON CONFLICT in a SECOND committed
   txn. Cash 67760.00 -> 58260.00, Rent -> 9500.00, trial balance 0.00 across 15 ledger rows. Prove no
   double-post: a bare re-INSERT of line (20260918,1) raises 23505; the ON CONFLICT re-post keeps counts +
   balances unchanged.
4. Reverse journal 20260918 with a mirror journal 20269918 (reverses_journal_id 20260918, debit/credit
   swapped), mark the original reversed, audit both. Re-derive -> Cash back to 67760.00, Rent 0.00, net 0.00,
   trial balance 0.00.
5. Assert the whole bucket ties: 21 journals, SUM(debit) = SUM(credit) = 1286470.00, 15 ledger rows,
   trial balance exactly 0.00.
6. Route EVERY journal write through one function (balance gate + closed-period lock + idempotent
   ON CONFLICT + an audit row naming the gateway-vouched user), and re-derive touched accounts afterwards in
   a second committed txn. Service account: k3.editor + ignite.app-developer only (no model-user).

Connect your tools

Everything this build wrote lives in one DataK3 bucket, reachable by your own stack — not just the CLI. A finance team drives the ledger from psql, a Python close script, or a BI tool over the same wire. data connect gl-suite prints the endpoints; point your tools straight at the same rows:

  • SQL over Postgres wire — psql, psycopg/asyncpg (Python), node-postgres (TS), any BI tool. A journal posts with INSERT … ON CONFLICT; the trial balance is one SELECT SUM(balance) FROM ledger.

One caveat this page has to state plainly, because it is the honest limit of the sole-writer control: a principal holding k3.editor on the bucket can write journal_lines directly over that same wire, around the balance gate, the closed-period lock and the audit trail. The database will accept it — verified live. What the control covers is every write through the GL's HTTP API; what protects the rest is credential custody (in a deployed GL the app's service account should be the only identity with bucket write rights) plus the trail. The full COVERED / NOT-COVERED label is in Close the period.

Full, live-validated walkthrough: Connect your tools.

The suite — six components, one app

This package runs standalone (uvicorn routes:app) — that is what this post walks through, and code/gl-journal-entry/v1.tar is still exactly that. Deployed, the six GL components compose into one app: gl-suite-app is a single FastAPI with a router per component, ONE canonical models.py, and the one posting.py write path — plain imports, no importlib loader — over one bucket (gl-suite) and one pool, so a controller signs in once for the whole ledger. It ships by the ordinary git cycle (repo → CI → registry → CD), which is Ship a DODIL app. One app rather than six is the ERP default — you split only for a stated reason (a public surface versus a private engine, independent scaling, a distinct trust boundary), and the GL has none of those: every component reads and writes the same rows behind the same login.

Conclusion

You now have the posting path of a General Ledger on one DataK3 bucket: a balance gate that rejects any journal where debits don't equal credits, idempotent posting via INSERT … ON CONFLICT DO UPDATE so a retry or shard replay lands once (a bare re-INSERT is a 23505, not a retry), reversal by mirror journal that nets the affected accounts straight back to zero, and an append-only journal_audit trail — with money as DECIMAL(18,2) so it never drifts, and a SerializationFailure retry loop so concurrent posters never corrupt a balance. No dedupe tables, no idempotency-key plumbing, no warehouse ETL for the trial balance. This is the correctness showcase of the DataK3 pg-wire finance stress test, proven live on gl-suite: 21 journals, SUM(debit) = SUM(credit) = 1286470.00, trial balance exactly 0.00.

The structural lesson is the one to carry into your own build. Both invariants are application controls — DataK3 cannot express "this journal balances" or "this period is closed" as an enforced constraint — so they are only real because there is exactly one function that writes journals and journal_lines, and it records the gateway-vouched user on every write. And the bugs that made that necessary were invisible until all six components ran on one bucket: a component validated alone proves only that it is self-consistent.

Next steps:

  • Scaffold the GL Core — the chart of accounts, account-hierarchy graph, and shared masters this posting path writes to.
  • Compose the rest of the GL suite onto core: period-close (lock the period this path posts into, roll net income to retained earnings), financial-reporting (a P&L and a balance sheet that ties), subledger-reconciliation (AP/AR control-account tie-out), multi-currency (FX revaluation with DECIMAL(18,6) rates).