What you'll build: the FX revaluation step of a month-end close — the one that takes a foreign-currency balance sitting in your ledger and restates it into your functional currency at the period-end rate, booking the difference as an unrealized gain or loss. On one DataK3 bucket, onto the gl/core masters, you add two tables — fx_rates (rates at DECIMAL(18,6)) and an fx_reval register — then revalue an open EUR bank balance, post a balanced reval journal, and reverse it next period so the two periods net to 0.00. Rates and money are exact decimals, so the gain/loss lands to the cent; every write is INSERT … ON CONFLICT DO UPDATE, so re-running the close never double-posts.

What you'll learn:

  • Store FX rates as DECIMAL(18,6) and prove they read back exact — no binary-float tail on a rate.
  • Revalue an open foreign-currency balance into the functional currency: re-derive the transaction-date carrying value from posted lines, recover the foreign units at the rate it was booked at, re-convert at the period-end rate, and book the difference — rounded to functional cents, in exact Decimal.
  • Post a balanced reval journal (FX gain/loss vs the account) and auto-reverse it next period, so the reval leaves no permanent trace once the position settles.
  • Make the close idempotent twice over — the write with ON CONFLICT (a bare re-INSERT is rejected 23505), and the computation by excluding the reval's own journals from its base. The second one is the hard one, and skipping it produces books that are silently wrong while still balancing perfectly.
  • Run every step two ways — by prompting an agent over the MCP, or the dodil data CLI.

The problem — and why it matters

Your company sells into Germany, so treasury funds a euro bank account to pay European suppliers out of. On 2026-08-20 you moved USD 108,240.00 into it and got EUR 100,000 at 1.0824. Six weeks later the euro has strengthened to 1.1147, and that same EUR 100,000 is worth USD 111,470.00. Nothing about the account changed — there are still exactly EUR 100,000 in it — but its value in your functional currency did, and the accounting standards (ASC 830 in the US, IAS 21 under IFRS) are unambiguous: at each balance-sheet date you must revalue every open foreign-currency monetary balance at the closing rate and recognize the difference — here an unrealized gain of USD 3,230.00 — in the income statement.

Get it wrong and it is not a rounding nit. The controller who signs the close is attesting that the balance sheet states foreign assets at the closing rate; a reval that is off by a cent — because someone stored a rate as a float and 1.1147 came back as 1.1146999… — is a misstatement. And because the gain is unrealized (you haven't converted the euros back), it must reverse next period so it doesn't double-count against the actual gain or loss you'll book when they are finally sold. So the reval has three hard requirements: the rate must be exact, the reval journal must balance, and it must be cleanly reversible.

There is a fourth requirement that nobody writes on the list, and it is the one that actually bit us: the reval must be safe to run twice. It posts to the very account it revalues, so a second run that reads the current balance revalues its own output — and because each run posts a balanced journal, the trial balance stays at exactly 0.00 while the gain quietly walks away from the truth. Step 3 and What composing the six components found are about that.

The classic stack does this with a rates feed in one system, the ledger in another, and a spreadsheet in the middle where the reval is actually computed and then keyed back in by hand. On DataK3 the rate table, the ledger balance, the reval register, and the journal you post all live in one bucket, over one copy of the rows — the reval is a single SELECT against the same wire that holds the ledger, with money as DECIMAL(18,2) and rates as DECIMAL(18,6) so nothing drifts.

PieceLands inPillar
FX rates (prior + period-end, per currency pair)fx_rates (DECIMAL(18,6))SQL
The reval register (orig / revalued / gain-loss per account)fx_reval (DECIMAL(18,2))SQL
The reval + reversing journalsjournals / journal_lines (from gl/core)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. Every number in this tutorial was produced live over this MCP on 2026-09-08 (org IHDIASH, bucket gl-suite) — and not by this component on a bucket of its own, but by all six GL components running together on that one bucket. That distinction is the subject of a whole section below; four of the five bugs it found are in this component.

Prerequisites

  • A DODIL organization with the dodil CLI authenticated (dodil auth login) or the DODIL MCP connected to your agent. Headless? Check auth_status first — an agent can't do the browser login for you.
  • The gl/core masters (accounts, journals, journal_lines, ledger) on your bucket. If you've run the GL core tutorial, you already have them — skip Step 1. If not, Step 1 stubs just the masters this skill reads, so the tutorial stands alone.
  • 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 period being revalued is 2026-09.) The obvious name gl is not available: DataK3 rejects a bucket name shorter than three characters, which is why the whole suite lives on gl-suite.

Step 1 — Stub the masters (standalone — skip if gl/core is present)

FX revaluation reads the gl/core masters and adds its own two tables. Installed without gl/core, it stubs just the masters it needs, with the locked shapes — money DECIMAL(18,2), every non-key column nullable — then seeds the scenario: a euro bank account (1400, currency EUR), the Cash account it was funded from (1100, USD), an Unrealized FX Gain/Loss account (4900), and the treasury journal 20260820 that bought EUR 100,000 for USD 108,240.00 at 1.0824 — the position we'll revalue.

It also stubs journal_audit, which gl/journal-entry owns: every journal in the GL is written by the one shared posting.write_journal() path, and that path appends the acting user to the audit trail on every post. Standalone, the table has to exist or the first post fails.

IMPORTANT

ledger.balance is the FUNCTIONAL-currency signed net — always. It is SUM(debit) − SUM(credit) over the account's posted lines, in the currency the lines were booked in, which for a US-functional ledger is USD. The ledger.currency column sitting next to it is a different thing: it marks the account's transaction currency — the EUR flag that tells the reval this balance needs a rate pair at all. One column pair, two jobs. Reading balance as euros rather than dollars is a real bug we shipped and caught, and it is bug 3 in the composition section below. The euro face amount is never stored — it is recovered by dividing the carrying value by the rate it was booked at.

You

On bucket gl-suite, stub the GL masters I consume from gl/core, then seed an FX scenario. Create merge-keyed tables with money as DECIMAL(18,2) and every non-key column nullable: accounts (key account_id) with name, type, normal_balance, parent_account_id (long), currency, active (boolean); journals (key journal_id) with journal_date (timestamp), period, source, status, memo, reverses_journal_id (long); journal_lines (composite key journal_id, line_no) with account_id (long), debit DECIMAL(18,2), credit DECIMAL(18,2), line_memo; ledger (key account_id) with balance DECIMAL(18,2), currency, as_of (timestamp); journal_audit (key audit_id, string) with journal_id (long), event, event_at (timestamp). Seed three accounts: 1100 Cash, asset, debit, currency USD; 1400 EUR Bank Account, asset, debit, currency EUR; 4900 Unrealized FX Gain/Loss, revenue, credit, currency USD. Post treasury journal 20260820 (period 2026-08, status posted): debit 1400 108240.00, credit 1100 108240.00 — EUR 100000 bought at 1.0824. Then materialize the ledger as SUM(debit) minus SUM(credit) per account, taking each row's currency from accounts.currency.

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

Created bucket gl-suite + 5 tables (accounts, journals, journal_lines, ledger, journal_audit), seeded 3 accounts, posted journal 20260820 (SUM(debit) = SUM(credit) = 108240.00), and materialized the ledger — account 1400 carries a functional-currency carrying value of 108240.00 with currency EUR, account 1100 carries -108240.00 USD. Ready to revalue.

Every data step below carries that third ORM tab — the exact class from the package's models.py (plain SQLAlchemy, natural PKs, money as Numeric(18, 2)), so the same op reads three ways: an agent prompt, the dodil data CLI, or the model. models.Base.metadata.create_all(db.engine) builds all six at once (see Get the code).

NOTE

When gl/core is present, skip this step. Core owns accounts / journals / journal_lines / ledger (and gl/journal-entry owns journal_audit); this skill adds only fx_rates + fx_reval and reads the masters. Installed standalone, the stub above recreates just those masters with the same locked shapes — money DECIMAL(18,2), every non-key column nullable — so the tutorial runs end-to-end on its own. On the shared gl-suite bucket you install gl/core once and every component reads the same rows: that is the whole reason the suite is one bucket, and — as the composition section below shows — the only configuration in which some of these bugs are even findable.

Step 2 — Seed the FX rate table (fx_rates, DECIMAL(18,6))

The rate table is the provenance of the whole reval — a controller signing the close traces every rate to where it came from. It's keyed on the composite (from_ccy, to_ccy, rate_date), so one row is the rate for a currency pair on a date, and re-seeding a corrected rate is idempotent. Rates are DECIMAL(18,6) — more scale than money (six places), and, crucially, exact: a rate is never a float.

You

In gl-suite, create an fx_rates table keyed on the composite (from_ccy, to_ccy, rate_date) with rate DECIMAL(18,6) and source. Seed EUR to USD rates: 2026-08-20 = 1.082400, 2026-08-31 = 1.082400 and 2026-09-30 = 1.114700, source ECB. Also add GBP to USD rates: 2026-08-31 = 1.267845 and 2026-09-30 = 1.291200, to show the full six-decimal precision reads back exact. Then select the rates back.

DODIL MCP tools called
data_table_create→data_table_upsert→data_sql
Agent

Created fx_rates (pk from_ccy, to_ccy, rate_date; rate DECIMAL(18,6)), upserted 5 rows. Read back exact: EUR->USD 1.082400 (2026-08-20 and 2026-08-31) and 1.114700 (2026-09-30), GBP->USD 1.267845 and 1.291200 — every rate stores and reads at full (18,6) precision with no float tail.

IMPORTANT

A rate is DECIMAL(18,6), never double. Store 1.1147 as a float and it comes back as 1.1146999999999999 — and 100000 × 1.1146999… is not 111470.00, so the gain/loss is off by a cent and the balance sheet misstates. DECIMAL(18,6) is exact: 1.267845 reads back as exactly 1.267845.

Step 3 — Revalue the open balance and post the reval journal (fx_reval)

Now the reval itself, in one SELECT against the same bucket. Three functional-currency figures: the original carrying value, the revalued carrying value at the period-end rate, and the gain/loss between them, each ROUNDed to functional cents. Write them to fx_reval with INSERT … SELECT … ON CONFLICT so the register write is idempotent.

The subtlety is where the original carrying value comes from, and it is worth slowing down for — it is the difference between a reval you can run twice and one you cannot:

  • Not from ledger.balance. The reval posts to the account it is revaluing, so after the first run the ledger already contains the gain. A second run would revalue its own output.
  • From the posted lines, excluding the reval's own journals. SUM(debit) − SUM(credit) over the account's posted/reversed lines where the journal source is not fx-reval or fx-reval-reversal. That is the transaction-date carrying value — 108240.00, the dollars actually spent on the euros — and it does not move no matter how many times the reval runs.
  • The euro face amount is then recovered, not read: 108240.00 ÷ 1.0824 = EUR 100,000, re-converted at the period-end 1.1147 to 111470.00. Gain: 3230.00.
You

In gl-suite, create an fx_reval register keyed on reval_id (account_id long, ccy, orig_balance DECIMAL(18,2), revalued_balance DECIMAL(18,2), gain_loss DECIMAL(18,2), rate DECIMAL(18,6), period). Then revalue the open EUR balance on account 1400. Derive the base as SUM(debit) minus SUM(credit) over its posted or reversed journal lines, EXCLUDING journals whose source is fx-reval or fx-reval-reversal, so the reval never revalues its own output. That base is orig_balance. Recover the euro units by dividing by the 2026-08-31 rate, re-convert at the 2026-09-30 rate for revalued_balance, and set gain_loss = revalued_balance minus orig_balance. Write it as reval_id 1 for period 2026-09 with INSERT SELECT ON CONFLICT (reval_id) DO UPDATE. Show the row.

DODIL MCP tools called
data_table_create→data_pg→data_sql
Agent

Created fx_reval. Revalued account 1400 (EUR Bank Account): orig_balance 108240.00 — the transaction-date carrying value re-derived from posted lines excluding fx-reval journals — recovered as EUR 100000 at 1.082400, re-converted at 1.114700 to revalued_balance 111470.00, gain_loss = 3230.00. Exact, no float tail.

The euros are worth more dollars than when they were bought, so we recognize an unrealized gain. The reval journal makes it real to the books: a gain debits the account (its functional carrying value rose) and credits Unrealized FX Gain/Loss — and, like every journal, it must balance before it posts.

Posting it is not a bare upsert. Every journal in the GL — in every one of the six components — goes through the single guarded write path, posting.write_journal(), which runs the balance gate, checks the target period is open (an operational posting into a closed period is rejected 409; the full story is in Period close), writes idempotently, and appends the acting user to journal_audit. And then, separately, the caller re-derives the ledger — see bug 1 below for what happens when it doesn't.

You

In gl-suite, post the reval journal for the 3230.00 unrealized FX gain. Upsert journal 20269001 (period 2026-09, source fx-reval, status posted, memo Unrealized FX revaluation for account 1400 at 1.114700). Then its 2 lines: debit account 1400 3230.00, credit account 4900 3230.00. Then check that SUM(debit) equals SUM(credit), and re-derive the ledger for the two accounts it touched.

DODIL MCP tools called
data_table_upsert→data_pg→data_sql
Agent

Journal 20269001 posted with 2 lines — debit EUR Bank Account (1400) 3230.00, credit Unrealized FX Gain/Loss (4900) 3230.00. SUM(debit) = 3230.00, SUM(credit) = 3230.00, SUM(debit)-SUM(credit) = 0.00. Ledger re-derived in a second transaction: 1400 is now 111470.00 (still currency EUR), 4900 is -3230.00. Trial balance across the bucket still 0.00.

Now the correctness proof a close depends on: the reval is idempotent — a month-end job that runs twice must revalue once. There are two separate things to prove, and only the first is the familiar one.

The write. A bare re-INSERT of the register row is rejected 23505; ON CONFLICT DO UPDATE is the safe path. The computation. That is the one the base exclusion above buys, and it is the reason the whole end-to-end reval was re-run three more times against the live bucket: fx_reval stayed one row, gain_loss stayed 3230.00, ledger 1400 stayed 111470.00, and the trial balance stayed 0.00.

You

In gl-suite, prove the reval is idempotent. First try a bare re-INSERT of fx_reval reval_id 1 to show it is rejected. Then re-run the whole reval end to end three more times and confirm fx_reval is still 1 row, gain_loss is still 3230.00, the ledger balance on 1400 is still 111470.00, and the reval journal still has 2 lines summing to 0.00.

DODIL MCP tools called
data_pg→data_sql
Agent

The bare re-INSERT raised SQLSTATE 23505 (duplicate key fx_reval_pkey (1) already exists) — a re-INSERT is NOT an upsert. Three further end-to-end re-runs left fx_reval = 1 row, gain_loss = 3230.00, ledger 1400 = 111470.00, reval journal lines = 2, journal balance 0.00 — no double-revaluation and no drift. journal_audit shows the repeated posted events against journal 20269001: the runs happened, the journal landed once.

WARNING

The version of this that we shipped first was idempotent in its writes and wrong anyway. It read ledger.balance as its base, so run two revalued run one's result: the gain went 3230.00, then 3326.39, then further — and the trial balance read exactly 0.00 at every step, because each run posted a perfectly balanced journal. INSERT … ON CONFLICT makes a write idempotent. It does nothing for a computation that reads its own previous output; that is a separate obligation, and double-entry will not warn you when you miss it.

Step 4 — Reverse the reval into the next period (auto_reverse=true)

The gain is unrealized — you haven't sold the euros. So the reval reverses on the first day of the next period, and next month's close revalues the fresh position from scratch. The reversal is the mirror journal: debit and credit swapped, linked back by reverses_journal_id, source set to fx-reval-reversal so the base-exclusion in Step 3 skips it too. Over the two periods, every account it touched nets to 0.00 — the reval leaves no permanent trace once the position actually settles.

POST /revalue does this for you when auto_reverse is true (the default); here is the same op by hand, so you can see the shape. The live gl-suite run left the September reval standing rather than reversing it — the numbers below are the pattern, not a reading off that bucket:

You

In gl-suite, reverse the September FX reval into October. Upsert journal 20269002 (journal_date 2026-10-01, period 2026-10, source fx-reval-reversal, status posted, memo Reversal of the September FX reval, reverses_journal_id 20269001) with the mirror lines: credit account 1400 3230.00, debit account 4900 3230.00. Then show, over the two journals, the net debit-minus-credit per account, and confirm the mirror journal itself balances.

DODIL MCP tools called
data_table_upsert→data_sql
Agent

Journal 20269002 posted, reverses_journal_id = 20269001 — the debit/credit mirror. Over the two journals: account 1400 net 0.00, account 4900 net 0.00. The mirror journal balances on its own (SUM(debit)-SUM(credit) = 0.00). The unrealized reval nets to zero across the two periods.

TIP

Why the reversal carries its own source. fx-reval-reversal is excluded from the Step 3 base alongside fx-reval. If it weren't, reversing would drag the base down by the gain and next month's reval would be computed off a number that no transaction ever produced. The exclusion list and the source values are one design, not two.

Routes

The download (see Get the code) fronts this bucket with a small FastAPI app, routes.py — CRUD over the masters plus the period-end reval this skill owns. This is what a close team deploys (or runs on a schedule as the gl-reval-engine). Every route follows the same finance rules the package bakes in, so quoting it is documenting them.

The connection and the one write helper live in db.py — byte-identical to every other DODIL suite. 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 only writer every route uses — INSERT … ON CONFLICT (<pk>) DO UPDATE:

# db.py — the idempotent keyed write (SQLAlchemy + psycopg = the pg wire; Decimal carried exact)
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)

Why it matters here: a month-end reval that runs twice — a retry, a shard replay — must revalue once. A bare re-INSERT of the fx_reval register row raises duplicate-key 23505; upsert makes the recompute land the row once. And because money is Numeric and the write goes over the pg wire (SQLAlchemy + psycopg), Decimal is carried exact — the gRPC upsert path would silently drop an integer 0 into a DECIMAL, so the reval money never leaves the wire.

CRUD + the two gates. A journal is never written by a route directly. It goes through posting.write_journal(), the GL's single guarded write path, shared byte-identical across all six components: it runs the balance gate (422 if SUM(debit) ≠ SUM(credit)), the closed-period lock (409), writes idempotently, and records the acting user in journal_audit. Both gates are write-path guards, not DB constraints — which is precisely why there is only one write path:

# routes.py — post a journal through the one guarded write path, then re-derive
@router.post("/journals")
def post_journal(j: JournalIn,
                 user: dict = Depends(require_permission("gl:journal:post")),
                 s: Session = Depends(db)):
    """Post a journal + its lines through the GL's single guarded write path. Both gates run
    inside posting.write_journal(): SUM(debit) must equal SUM(credit) (422 otherwise) and the
    target period must be open (409 otherwise). They are write-path guards, not DB
    constraints — which is exactly why there is only one write path."""
    lines = [{"journal_id": j.journal_id, **ln.model_dump()} for ln in j.lines]
    total_debit, total_credit = posting.assert_balanced(lines)
    header = {k: v for k, v in j.model_dump().items() if k != "lines"}
    header.setdefault("journal_date", posting.now())
    header["journal_date"] = header.get("journal_date") or posting.now()
    posting.write_journal(s, header, lines, actor=user["sub"])
    posting.rederive_ledger([ln.account_id for ln in j.lines], as_of=posting.now())
    return {"ok": True, "journal_id": j.journal_id,
            "total_debit": str(total_debit), "total_credit": str(total_credit),
            "balance": str(total_debit - total_credit)}

That last-but-one line — posting.rederive_ledger(...) — is the fix for bug 1, and it is one line, which is exactly why four routes were missing it.

Workflow op — the period-end reval (POST /revalue). This is the skill's reason to exist, in one handler: revalue one open foreign-currency ledger balance at the period-end rate, write the register idempotently, post the balanced reval journal, and auto-reverse it next period. Every figure is exact Decimal; _round2 is the SQL ROUND(x, 2) in Decimal (never a binary float):

# routes.py — the period-end FX revaluation
@router.post("/revalue")
def revalue(r: RevalueIn,
            user: dict = Depends(require_permission("gl:journal:post")),
            s: Session = Depends(db)):
    led = s.get(Ledger, r.account_id)
    if not led or led.balance is None or led.currency is None:
        raise HTTPException(404, "no open ledger balance for that account")
 
    prior = s.get(FxRate, {"from_ccy": led.currency, "to_ccy": r.functional_currency,
                           "rate_date": r.prior_rate_date})
    new = s.get(FxRate, {"from_ccy": led.currency, "to_ccy": r.functional_currency,
                         "rate_date": r.period_end_rate_date})
    if not (prior and new and prior.rate is not None and new.rate is not None):
        raise HTTPException(422, "missing prior or period-end rate for the currency pair")
 
    # ── the two things this calculation must get right, both found live ─────────────────
    #
    # (1) WHAT `ledger.balance` IS. This code used to read `led.balance` as a FOREIGN-currency
    #     amount and convert it twice (balance x prior_rate, balance x new_rate). Every other
    #     component writes that column as the FUNCTIONAL-currency signed net of the journal
    #     lines (posting.rederive_ledger). One column, two incompatible readings — invisible
    #     standalone, because on its own bucket this component was the only writer.
    #
    # (2) THE BASE MUST EXCLUDE THE REVAL'S OWN JOURNALS. A reval posts to the very account it
    #     revalues, so reading the CURRENT ledger balance means the second run revalues the
    #     first run's result: 3,230.00 became 3,326.39 on a re-run, and the trial balance still
    #     netted to 0.00 the whole time — silently wrong, not loudly broken. The ON CONFLICT
    #     writes were idempotent; the INPUT was not. So the base is re-derived here from posted
    #     lines EXCLUDING every fx-reval journal: the transaction-date carrying value, which
    #     does not move no matter how many times the reval runs.
    base = s.execute(text(
        "SELECT COALESCE(SUM(jl.debit) - SUM(jl.credit), 0) "
        "  FROM journal_lines jl JOIN journals j ON j.journal_id = jl.journal_id "
        " WHERE jl.account_id = :a AND j.status IN ('posted','reversed') "
        "   AND COALESCE(j.source, '') NOT IN ('fx-reval', 'fx-reval-reversal')"),
        {"a": r.account_id}).scalar()
    orig_balance = _round2(Decimal(base))                     # transaction-date carrying value
    foreign_units = orig_balance / prior.rate                 # implied FC units (exact Decimal)
    revalued_balance = _round2(foreign_units * new.rate)      # re-converted at period-end
    gain_loss = revalued_balance - orig_balance
 
    # (1) idempotent write of the reval register — INSERT ... ON CONFLICT (reval_id) DO UPDATE
    upsert(s, FxReval, [{
        "reval_id": r.reval_id, "account_id": r.account_id, "ccy": led.currency,
        "orig_balance": orig_balance, "revalued_balance": revalued_balance,
        "gain_loss": gain_loss, "rate": new.rate, "period": r.period,
    }], key="reval_id")
    s.commit()  # commit BEFORE we post the journal (no read-your-writes inside a txn)
 
    # (2) post the BALANCED reval journal — a GAIN debits the asset, credits FX gain/loss
    #     … via posting.write_journal(...), then:
    posting.rederive_ledger([debit_acct, credit_acct], as_of=posting.now())
    # (3) if auto_reverse: post the debit<->credit mirror linked by reverses_journal_id

Four DataK3 rules are load-bearing in that handler. Idempotent writes — the fx_reval write is upsert, so a re-run recomputes in place (23505 on a bare re-INSERT). Idempotent inputs — the base excludes the reval's own journals, so the recompute produces the same number rather than a new one. Money over the pg wire — base, prior.rate, new.rate are all Decimal, divided and multiplied exactly and rounded with _round2; nothing becomes a float. No read-your-writes inside a txn — the register write is committed before the journal is posted, and rederive_ledger opens its own session for a second committed transaction, because a SELECT cannot see rows the same transaction just wrote.

Live-verified on gl-suite: POST /revalue on account 1400 returns gain_loss = "3230.00" (orig_balance 108240.00, revalued_balance 111470.00, rate 1.114700), posts a reval journal that balances (0.00), leaves ledger 1400 at 111470.00 still flagged EUR, and the bucket-wide trial balance still ties:

# routes.py — the invariant that proves the books tie: total debits = total credits
@router.get("/trial-balance")
def trial_balance(user: dict = Depends(current_user), s: Session = Depends(db)):
    """The invariant that proves the books tie: over every posted line, total debits equal
    total credits, so the trial balance nets to 0.00 (exact — no float tail)."""
    d, c = s.execute(
        select(func.coalesce(func.sum(JournalLine.debit), 0),
               func.coalesce(func.sum(JournalLine.credit), 0))
    ).one()
    d, c = _round2(Decimal(d)), _round2(Decimal(c))
    return {"total_debit": str(d), "total_credit": str(c), "balance": str(_round2(d - c))}

On the full gl-suite bucket — all six components, 21 journals — GET /trial-balance returns total_debit "1286470.00", total_credit "1286470.00", balance "0.00": exact, no float tail, before and after the reval. Adding a new workflow touches only routes.py (and maybe models.py); the plumbing in db.py / auth.py / posting.py is fixed and shared (see EXTENDING.md).

What composing the six components found

Every number in this post came from a bucket where all six GL components had run — core, journal-entry, period-close, subledger-reconciliation, financial-reporting and this one, over the same accounts / journals / journal_lines / ledger rows. Before that, each component had been validated on a bucket of its own, and each one passed.

Composing them surfaced five bugs. Four are in this component, and not one of them was findable on a bucket where FX revaluation was the only writer. That is the lesson, and it is worth more than the FX math: a component validated alone proves only that it is self-consistent. Composition is a separate proof, and on DataK3 it needs one bucket — which is the same reason an ERP belongs on one bucket in the first place.

1 · The reval posted a journal and never re-derived the ledger. The 3230.00 gain went into journal_lines and appeared in no report: not ledger, not the trial balance by account, not the balance sheet. Standalone this was invisible, because on its own bucket the reval read ledger as an input and nothing else ever wrote it — the table simply never needed to be current. Composed, the reval journal landed and every report kept showing the pre-reval balance. The fix is the one line at the end of the handler: post through posting.write_journal(), then posting.rederive_ledger(...) in a second committed transaction — second, because DataK3 has no read-your-writes inside an open transaction, so a re-derive in the same txn would not see the lines it is meant to aggregate.

2 · Three other posting routes had the same omission. Composed, five routes disagreed about whether ledger was current, and the one you called last decided what the balance sheet said. Journal entry tells that half of the story, and it is why the re-derive now sits inside the shared write path's contract rather than in five separate places by convention.

3 · The re-derive silently disarmed the reval. rederive_ledger used to stamp ledger.currency from the calling component's FUNCTIONAL_CURRENCY default — "USD". So any posting, from any component, anywhere in the suite, overwrote the EUR marker on account 1400. The reval reads ledger.currency to pick the rate pair; with the marker gone it had nothing to revalue. A payroll journal could disable FX revaluation, and nothing anywhere would raise. The fix is a single COALESCE over a LEFT JOIN, and the principle behind it is worth stating: an account's currency is a property of the account, so a re-derive must not be able to change it.

# posting.py — rederive_ledger: the currency comes from accounts, never from the caller
    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:
            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 DataK3 details are visible in that fragment and both are load-bearing: the touched ids are inlined as an IN (…) of validated integers because DuckDB has no = ANY(array), and every value rides in the SELECT list because DO UPDATE SET accepts only EXCLUDED.<col> references — a literal is rejected FeatureNotSupported.

4 · ledger.balance had two incompatible readings. This component assumed foreign-currency units; every other component wrote the functional-currency signed net. One column, two meanings, no error message — so the reval multiplied an already-converted number by a rate again. Double conversion is a particularly nasty bug because the result is plausible: still money, still the right order of magnitude, still balancing. The discipline that ends it is stated once and obeyed everywhere: ledger.balance is always the functional-currency signed net, and the foreign face amount is recovered by dividing the carrying value by the rate it was booked at — never by re-reading the column as units.

5 · The reval was not idempotent, and the books balanced anyway. Covered in Step 3, and it is the one to remember. Re-running walked the gain 3230.00 → 3326.39 → …, and the trial balance read exactly 0.00 throughout, because every run posted a balanced journal. Silently wrong, not loudly broken. The double-entry invariant — the thing a ledger is for — cannot detect it, because nothing about it is unbalanced. INSERT … ON CONFLICT makes a write idempotent; making a computation idempotent when it reads its own previous output is a separate design obligation, and it belongs on the checklist next to "does it balance".

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

Both write routes here carry gl:journal:post — POST /journals and, less obviously, POST /revalue, because a revaluation is a journal posting: it debits the foreign-currency account and credits Unrealized FX Gain/Loss. POST /accounts, POST /fx-rates, GET /fx-reval/… and GET /trial-balance are current_user only — a published rate is a fact, not a posting.

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-multi-currency/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only, no Dockerfile — the deploy story lives in the post, and the deployable artifact is the suite app the six components compose into (below):

models.py          # SQLAlchemy — accounts/journals/journal_lines/ledger (consumed) + fx_rates + fx_reval (owned)
routes.py          # FastAPI    — CRUD + POST /revalue (reval → balanced journal → auto-reverse) + /trial-balance
db.py              # the engine + the ON CONFLICT upsert helper every route uses
posting.py         # THE guarded journal write path: balance gate + closed-period lock (SHARED, byte-identical)
auth.py            # gateway header-trust identity — the app ships NO auth code   (SHARED, byte-identical)
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 (or the service-account pair)
requirements.txt   # sqlalchemy, psycopg[binary], fastapi, uvicorn, pydantic, httpx

There is no pyjwt and no issuer/audience configuration: with the gateway in front, there is nothing left to verify. And read PLATFORM.md before you copy any of this into a customer build — these packages are reference implementations to generate from, and that file is the short list of lines you copy verbatim instead.

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 the tables from the models:
python -c "import db, models; models.Base.metadata.create_all(db.engine)"
# journal_audit belongs to gl/journal-entry, so it is not in THIS package's models — create it
# with the CLI from Step 1 (or install gl/core + gl/journal-entry) before the first post.
 
# no gateway in front of uvicorn, so opt the stub identity in — and grant it the gate
export DEV_ALLOW_ANON=1 DEV_USER_SUB=dev-controller DEV_USER_PERMISSIONS=gl:journal:post
uvicorn routes:app --reload
# POST /accounts /fx-rates /journals · POST /revalue · GET /fx-reval/{id} · GET /trial-balance

models.Base.metadata.create_all is the ORM tab's every class at once — the same tables Steps 1–3 built by CLI, created from the natural-key models with no migration tool.

The suite — six components, one app

This package runs on its own — uvicorn routes:app — and that is what the post above walks through; the /code/gl-multi-currency download is exactly that. Deployed, the six GL components are 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, wired with plain imports (no importlib loader), over one bucket — gl-suite — and one dodil-appid 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 against a private engine, independent scaling, a distinct trust boundary — and the GL has none of those. It does, as the section above shows, have a strong reason to stay on one bucket.

How the pillars map

One bucket, one bill, one auth context — the rate you quote, the ledger balance you revalue, the register you write, and the journal you post are all rows in the same bucket, over one copy. What this reval would otherwise be:

JobThe usual stackOn DataK3
Store FX rates exactlyA rates feed / a spreadsheet tabfx_rates — DECIMAL(18,6), exact, keyed on (from_ccy, to_ccy, rate_date)
"What's the EUR position worth in USD now?"Export the ledger, compute in Excel, key it backone SELECT — the posted lines JOIN fx_rates, ROUND(carrying / prior × new, 2)
Gain/loss to the centCareful float handling, or a numeric libDECIMAL(18,2) columns — exact ROUND, no float tail
Revalue exactly once (retry-safe)App-level dedupeINSERT … ON CONFLICT (reval_id) DO UPDATE for the write, plus a base that excludes the reval's own journals for the computation
Reverse it next periodA manual reversing entry someone remembers to booka mirror journal linked by reverses_journal_id; nets to 0.00
Point your own tools at itPer-system drivers & credsdata connect — psql / a BI tool, DB = bucket

No rates feed + a ledger + a spreadsheet in the middle — the reval is a query against the same wire that holds the ledger. This skill consumes the gl/core masters and adds only fx_rates + fx_reval, so it composes onto the same rows every other gl/* workflow writes.

Customize — the decisions this skill asks you

Q1 · functional_currency — the currency you revalue into

"What functional (reporting) currency do you revalue foreign balances INTO?" → USD (default) is the currency every foreign balance is converted to at the period-end rate, and the currency the gain/loss is expressed and rounded in. fx_rates are quoted from_ccy → USD. Inherit it from gl/core — set it once there and every reval uses it.

Q2 · reval_period — the period being revalued

"Which accounting period are you revaluing (YYYY-MM)?" → 2026-09 (default) makes the reval read the 2026-09-30 period-end rate (1.114700) and the prior period-end rate (2026-08-31, 1.082400), and stamps fx_reval.period + the reval journal's period. The engine reads the rate from fx_rates, never a hard-coded constant — change the period and it re-derives against that period's rate. The period must be open: posting.assert_period_open() rejects an operational posting into a closed one with 409.

Q3 · auto_reverse — reverse next period?

"Auto-reverse the revaluation at the start of the next period?"

  • true (default) → posts the reversing mirror journal (reverses_journal_id set) so the two periods net to 0.00 — the standard treatment for an unrealized reval, re-struck fresh each month.
  • false → the reval stands as a period-end carrying adjustment (this is what the live gl-suite run did); next month's reval still computes off the transaction-date carrying value, because the base excludes fx-reval journals either way — which is exactly what makes both settings safe to re-run.

Q4 · rate_source — the rate provenance

"What is the source/provenance stamped on each FX rate?" → ECB (default) stamps every fx_rates row with where the rate came from (a central-bank fix, your treasury desk, a rate feed) so a controller signing the reval can audit each rate to its source.

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 throwaway bucket of this component's own. That is what makes assertions 2–5 meaningful, and it is where the five bugs above came from. The bucket is still up. The default branch is {functional_currency: USD, reval_period: 2026-09, auto_reverse: true, rate_source: ECB}.

# 1) FX rates read back EXACT at DECIMAL(18,6) — no float tail
dodil data sql -b "$BUCKET" "SELECT from_ccy, to_ccy, rate_date, rate FROM fx_rates ORDER BY from_ccy, rate_date"
#  EUR USD 2026-08-20 1.082400 · EUR USD 2026-08-31 1.082400 · EUR USD 2026-09-30 1.114700
#  GBP USD 2026-08-31 1.267845 · GBP USD 2026-09-30 1.291200
 
# 2) the reval — gain/loss exact
dodil data sql -b "$BUCKET" "SELECT account_id, ccy, orig_balance, revalued_balance, gain_loss, rate FROM fx_reval WHERE reval_id = 1"
#  1400 EUR, orig_balance 108240.00, revalued_balance 111470.00, gain_loss 3230.00, rate 1.114700
#  (EUR 100,000 recovered as 108240.00 / 1.082400, re-converted at 1.114700)
 
# 3) the reval journal balances
dodil data sql -b "$BUCKET" "SELECT SUM(debit) AS d, SUM(credit) AS c, SUM(debit)-SUM(credit) AS balance FROM journal_lines WHERE journal_id = 20269001"
#  d 3230.00, c 3230.00, balance 0.00
 
# 4) the gain reached the LEDGER — bug 1's assertion, and the EUR marker survived (bug 3's)
dodil data sql -b "$BUCKET" "SELECT account_id, balance, currency FROM ledger WHERE account_id IN (1400, 4900) ORDER BY account_id"
#  1400  111470.00  EUR      4900  -3230.00  USD
 
# 5) idempotent END TO END — the whole reval re-run three more times changed nothing
#     → fx_reval 1 row, gain_loss 3230.00, ledger 1400 111470.00, reval journal 2 lines, balance 0.00
 
# 6) the books still tie across the whole suite — 21 journals, six components, one bucket
dodil data sql -b "$BUCKET" "SELECT SUM(debit) AS d, SUM(credit) AS c, (SELECT SUM(balance) FROM ledger) AS trial_balance, (SELECT COUNT(*) FROM ledger) AS rows FROM journal_lines"
#  d 1286470.00, c 1286470.00, trial_balance 0.00, rows 15
 
# 7) drop-in clients: same bucket, your own psql / BI tool
dodil data connect "$BUCKET"            # pg / bolt / grpc endpoints
#  pg   postgresql://token:…@pg.uk-lon-1.dodil.io:5432/gl-suite
#  bolt bolt+s://bolt.uk-lon-1.dodil.io:7687   ·   grpc table-rpc.uk-lon-1.dodil.io:443

One-shot

With the DODIL MCP connected, paste this to run the whole FX revaluation at once (assumes the gl/core masters, or Step 1's stub, are in place):

Run the month-end FX revaluation on DataK3 bucket gl-suite (functional currency USD, period 2026-09).
Confirm each step.
 
1. Seed fx_rates (composite key from_ccy, to_ccy, rate_date; rate DECIMAL(18,6), source): EUR→USD 2026-08-20
   = 1.082400, 2026-08-31 = 1.082400 and 2026-09-30 = 1.114700; GBP→USD 2026-08-31 = 1.267845 and 2026-09-30
   = 1.291200. Source ECB. Rates must read back EXACT at (18,6).
2. Create fx_reval (key reval_id: account_id, ccy, orig_balance DECIMAL(18,2), revalued_balance DECIMAL(18,2),
   gain_loss DECIMAL(18,2), rate DECIMAL(18,6), period). Revalue account 1400 (EUR Bank Account). Derive the
   base as SUM(debit) − SUM(credit) over its posted/reversed lines EXCLUDING journals whose source is
   fx-reval or fx-reval-reversal — that is orig_balance, and it is what makes a re-run a no-op. Recover the
   euro units by dividing by the 2026-08-31 rate, re-convert at the 2026-09-30 rate for revalued_balance,
   gain_loss = revalued − orig. Write reval_id 1 with INSERT … SELECT … ON CONFLICT (reval_id) DO UPDATE.
   orig_balance = 108240.00, revalued_balance = 111470.00, gain_loss = 3230.00.
3. Post the balanced reval journal (journal 20269001, status posted, period 2026-09, source fx-reval): debit
   1400 3230.00, credit 4900 3230.00. Assert SUM(debit) = SUM(credit) = 3230.00 before posting. THEN
   re-derive the ledger for accounts 1400 and 4900 in a separate statement — taking each row's currency from
   accounts.currency — so the gain reaches the reports: ledger 1400 = 111470.00 (currency still EUR),
   4900 = -3230.00.
4. Prove idempotency both ways: a bare re-INSERT of fx_reval reval_id 1 raises SQLSTATE 23505; and re-running
   steps 2–3 end to end three more times leaves fx_reval = 1 row, gain_loss = 3230.00 and ledger 1400 =
   111470.00 unchanged.
5. Reverse into the next period (auto_reverse): journal 20269002 (period 2026-10, source fx-reval-reversal,
   reverses_journal_id 20269001), mirror lines credit 1400 3230.00 / debit 4900 3230.00. Over the two
   journals each account nets to 0.00.

Connect your tools

Everything this build wrote lives in one DataK3 bucket, reachable by your own stack — not just the CLI. A treasury or close team drives the reval 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. The reval is one INSERT … SELECT … ON CONFLICT joining the posted lines to fx_rates; the gain/loss is one SELECT.

Full, live-validated walkthrough: Connect your tools.

Conclusion

You now have FX revaluation on one DataK3 bucket, onto the gl/core masters: an fx_rates table where rates are DECIMAL(18,6) and exact, an fx_reval register that restates a EUR 100,000 bank position carried at 108240.00 into 111470.00 with an unrealized gain of exactly 3230.00, a balanced reval journal, a ledger re-derive that puts that gain in front of every report, and an auto-reversal that nets the two periods to 0.00. Money is DECIMAL(18,2), rates are DECIMAL(18,6), every write is INSERT … ON CONFLICT — and, the part that took a shared bucket to learn, the reval's input is idempotent too, so a re-run of the close recomputes the same number instead of revaluing its own output. No rates feed + a ledger + a spreadsheet in the middle. One bucket, one bill, one copy of the rows.

If you take one thing from this post that is not about currency: components that pass on their own can still be wrong together, and on DataK3 the cheapest way to find out is to run them on one bucket. Four of the five bugs above were in code that had already been validated, twice, and every one of them was invisible until this component had neighbours.

Next steps:

  • Compose the rest of the GL suite onto the same masters: journal-entry (post balanced journals idempotently, reverse by mirror), period-close (lock a period, roll net income to retained earnings), financial-reporting (a balance sheet that ties over the account graph), subledger-reconciliation (AP/AR control-account tie-out).
  • Run the reval on a schedule with the gl-reval-engine — a pure-SQL, image-mode Ignite app that recomputes fx_reval + posts the reval journal for every open foreign-currency balance over the pg wire. Being pure SQL, its service account needs only k3.editor + ignite.app-developer (no ignite.model-user).
  • The GL is the deliberate stress test of the DataK3 pg wire for finance — this workflow proves the multi-currency corner: rates exact at DECIMAL(18,6), the reval balanced, the gain/loss exact, and the whole thing reversible.