What you'll build: the master-data core of a General Ledger — the system of record every other workflow (journal entry, period close, financial reporting, subledger reconciliation, multi-currency) writes to — on one DataK3 bucket, fronted by a small FastAPI app you can download and run. Four merge-keyed tables (accounts, journals, journal_lines, ledger) are the double-entry spine; the account-hierarchy graph rolls any leaf account up to Total Assets in one hop-ranked query; and one balanced opening-balance journal seeds the ledger so the trial balance nets to 0.00 before you post a single real transaction. The same rows answer by content (SQL) and by relationship (graph) over one copy — no ETL, no Postgres + Neo4j + a reporting warehouse to keep in sync.

What you'll learn:

  • Model a chart of accounts and the shared journal / ledger shapes three ways at once — an agent prompt, the dodil data CLI, and a plain SQLAlchemy model — over merge-keyed DataK3 tables, with money as DECIMAL(18,2) (Numeric(18, 2) in the ORM), never a float, so the balance never drifts.
  • Project the COA into an account-hierarchy graph and roll it up — a leaf account's ancestors, or a parent's descendant leaves, in one statement.
  • Post a balanced double-entry journal and materialize the ledger idempotently — INSERT … ON CONFLICT DO UPDATE, so a retry or shard replay posts once, and prove the trial balance = 0.00.
  • Serve it all behind a FastAPI app (routes.py) — CRUD plus the balance gate, the ledger re-derive, and the account-tree rollup — with business users signed in by the Ignite gateway against a dodil-appid pool, so the app itself ships no authentication code at all.

The problem — and why it matters

A general ledger is the one system in the company that is not allowed to be approximately right. The controller signs the trial balance every month; the CFO signs the financial statements every quarter; and if the books don't tie — if debits don't equal credits to the cent, if a journal posted twice under a retry, if a rounding error crept in because someone stored money as a float — the outcome has a name: a restatement. A public-company restatement is a material event: re-audited periods, a filing with the regulator, a hit to the share price, and in the worst case a clawback of executive pay. The ledger that doesn't tie is not a bug ticket. It is a board-level incident.

So the ledger's requirements read like a correctness proof, not a feature list: money must be exact (no float drift over millions of postings), every posting must be idempotent (a journal that arrives twice from an at-least-once queue must land once), and every journal must balance (Σdebits = Σcredits) before it is allowed to post. The classic stack meets those requirements with three engines — Postgres for the transactions, Neo4j for the account-rollup tree, and a warehouse the whole thing is ETL'd into for reporting — plus the glue code that keeps them agreeing at yesterday's freshness.

On DataK3 the account record is the graph node, and the journal you post is the row the trial balance sums, over one copy of the rows in one bucket. The master core is four tables, one graph, one balanced opening journal — and exactly one guarded code path that is allowed to write a journal. That is the payoff, and the reason master data is where the money starts: get the shapes and the invariants right here, and every other gl/* skill composes onto the same rows.

That single write path matters more than it looks. Every journal in the entire GL — a sale, an accrual, a reversal, an FX revaluation, the closing entry itself — goes through posting.write_journal() in the shared posting.py. The balance gate, the closed-period lock and the journal_audit row that records who posted all live there, and nothing else in the GL touches journals / journal_lines. That is not a style preference: DataK3 enforces PRIMARY KEY and a literal CHECK, but a rule that spans tables ("this period is closed") has no database object that can express it, so the only honest way to make it real is to leave exactly one code path that can write. gl/period-close has the probe and the scope label.

PieceLands inPillar
Chart of accounts (assets/liabilities/equity/revenue/expense)accounts (merge-keyed)SQL
Account rollup (leaf → parent → Total Assets)account_edges (typed) → graph gl_accountsGraph
Journals + lines + running balancesjournals / journal_lines / ledger (DECIMAL(18,2))SQL
Who posted what (append-only)journal_audit (uuid key)SQL

NOTE

Connect the DODIL MCP once — then every data step shows an Ask your agent tab (the default — DODIL is agent-native), a CLI tab, and (where a table is created) an ORM tab: the exact SQLAlchemy model that ships in the downloadable package. Three front-ends, one set of rows. This whole core was built and validated by prompting an agent over this MCP.

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 — Stand up the master tables

The GL is the schema: merge-keyed tables in one bucket. account_id keys the chart of accounts; journal_id keys a journal header; journal_lines is keyed on the composite (journal_id, line_no) so a journal owns its numbered lines; ledger keys on account_id for an O(1) balance read; and journal_audit is the append-only trail posting.write_journal() writes a row to on every post, keyed on a uuid so a fresh event never collides. A --merge-key (PRIMARY KEY) is required — writes are keyed, so re-runs and shard retries upsert idempotently, and reads are read-your-writes across committed transactions (a fresh row is visible to the next JOIN, no compaction step).

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). (An FX rate column, in gl/multi-currency, would be DECIMAL(18,6) — more scale for rates, but still exact.)

TIP

data table create makes non-PK columns NOT NULL by default. Set nullable:true on every optional column — a draft journal has no reverses_journal_id, an opening line has no line_memo you care about — or a partial row fails with NotNullViolation.

You

Create a DataK3 bucket gl-suite, then five merge-keyed master tables. accounts (key account_id) with name, type (asset/liability/equity/revenue/expense), normal_balance (debit/credit), parent_account_id (long), currency, active (boolean). journals (key journal_id) with journal_date (timestamp), period, source, status (draft/posted/reversed), 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, a string uuid) with journal_id (long), event, event_at (timestamp). Make every non-key column nullable, and use DECIMAL(18,2) for all money.

DODIL MCP tools called
data_bucket_create→data_table_create
Agent

Created bucket gl-suite and 5 tables — accounts (pk account_id, 7 cols), journals (pk journal_id, 7 cols), journal_lines (pk journal_id+line_no, 6 cols; debit/credit DECIMAL(18,2)), ledger (pk account_id, 4 cols; balance DECIMAL(18,2)), journal_audit (pk audit_id, 4 cols). Upserts are idempotent; the same bucket also holds the account-hierarchy graph.

TIP

journal_audit is mapped, so Base.metadata.create_all builds it with everything else. posting.write_journal() still writes the audit row with raw SQL — a uuid key minted server-side with bare UUID(), and an ON CONFLICT so a retry never collides — but the table has a model, which is what a package needs to be runnable from a cold bucket. (It briefly did not: four of the six GL packages shipped posting.py without the model, so the very first POST /journals failed against a freshly created schema. If you are copying this pattern, the rule is: anything the code writes needs a mapped class, even when the write itself is raw SQL.)

NOTE

This core IS the masters. Every gl/* workflow skill (journal-entry, period-close, financial-reporting, subledger-reconciliation, multi-currency) consumes these four masters and adds only its own sidecar tables (periods, fx_rates, subledger_items) — one row, many readers, no second copy. Installed standalone without core, each workflow ships a stub_masters step that recreates just the masters it reads (same column defs, money DECIMAL(18,2)); once two workflows share masters, you install gl/core once here instead. Core has nothing to stub — it is the root of the DAG.

Step 2 — Seed the chart of accounts (coa_preset=demo)

Load a realistic COA: five roots on the classic numbering plan — assets 1000s, liabilities 2000s, equity 3000s, revenue 4000s, expenses 5000s — and fifteen posting leaves under them. Each account carries its type and its normal balance: assets and expenses are debit-normal, liabilities, equity and revenue are credit-normal. These accounts rows are the graph's nodes in Step 3 and the account_id every journal line points at.

Two leaves are here for the rest of the suite rather than for this post, and it is worth knowing why now: 1400 EUR Bank Account carries currency EUR, not the functional USD — that one column is what gl/multi-currency reads to decide which rate pair to revalue with — and 4900 Unrealized FX Gain/Loss is where that revaluation's gain lands. An account's currency is a property of the account; nothing downstream is allowed to overwrite it (there is a bug at the end of Step 5 that says why).

You

In gl-suite, upsert a chart of accounts: 5 root accounts with parent_account_id 0 — 1000 Total Assets (asset, debit), 2000 Total Liabilities (liability, credit), 3000 Total Equity (equity, credit), 4000 Total Revenue (revenue, credit), 5000 Total Expenses (expense, debit). Then 15 posting leaves under them: 1100 Cash, 1200 Accounts Receivable, 1300 Inventory, 1400 EUR Bank Account (assets, debit, parent 1000 — 1400 has currency EUR); 2100 Accounts Payable, 2200 Accrued Expenses (liabilities, credit, parent 2000); 3100 Common Stock, 3200 Retained Earnings (equity, credit, parent 3000); 4100 Product Revenue, 4200 Services Revenue, 4900 Unrealized FX Gain/Loss (revenue, credit, parent 4000); 5100 Cost of Goods Sold, 5200 Salaries, 5300 Rent, 5400 Marketing (expenses, debit, parent 5000). Currency USD except 1400, active true on all.

DODIL MCP tools called
data_table_upsert
Agent

Upserted 20 accounts (wal_written: true) — 5 roots + 15 leaves. By type: asset 5, liability 3, equity 3, revenue 4, expense 5. Each leaf points at its root via parent_account_id; 1400 EUR Bank Account carries currency EUR, every other account USD.

TIP

Keyed upserts drop null/empty keys. A JSON null — or an empty string — in the account_id merge-key column reads back as null and the row silently disappears, with no error. account_id is always a real number. The roots use 0 rather than null for "no parent" for the same reason the graph edges are a separate table: a sentinel keeps every parent lookup a plain integer comparison, with no IS NULL branch.

Step 3 — Build the account-hierarchy graph (hierarchy=true)

This is the pillar financial reporting rolls up over. A DataK3 graph is table-backed: the accounts table is the node set (KEY account_id), an account_edges table holds src → dst tagged by rel, and CREATE GRAPH binds them. Every edge is a child_of — src is the child leaf, dst is the parent — so the tree points from the leaves up toward Total Assets. Populate account_edges fully, then CREATE GRAPH — the graph snapshots its edges at creation, so edges added afterwards aren't traversable until you re-create it.

You

In gl-suite, create an account_edges table with composite key (src, dst, rel), then populate the 15 child_of edges from each leaf account up to its root: 1100/1200/1300/1400 to 1000, 2100/2200 to 2000, 3100/3200 to 3000, 4100/4200/4900 to 4000, 5100/5200/5300/5400 to 5000. Then CREATE GRAPH gl_accounts over node table accounts (KEY account_id) and edges account_edges (SRC src DST dst).

DODIL MCP tools called
data_table_create→data_table_upsert→data_pg
Agent

Created account_edges (pk src,dst,rel), upserted 15 child_of edges, and CREATE GRAPH gl_accounts NODES (accounts KEY account_id) EDGES (account_edges SRC src DST dst) succeeded — edges snapshotted at creation.

Because the edges point child → parent, the descendant leaves of a rollup account are the incoming direction. graph_khop('gl_accounts', 1000, 5, 'in') walks up to 5 hops inbound from Total Assets (1000) and returns its whole subtree — here the four asset leaves — with names hydrated by joining accounts in the same statement. graph_khop projects two columns — node (the account id) and hop_distance (there is no node_id) — so join on k.node:

You

In gl-suite, what are all the descendant accounts of Total Assets (account 1000)? Use graph_khop on gl_accounts in the incoming direction, joined to accounts for names and types.

DODIL MCP tools called
data_pg
Agent

Four descendant leaves — Cash (1100), Accounts Receivable (1200), Inventory (1300), EUR Bank Account (1400), all type asset. This is the set financial-reporting sums to a balance-sheet line.

The same rollup in Cypher over Bolt — anchor on the root, follow child_of edges inbound, and the graph plane hands back the descendant node keys:

You

Same account tree in Cypher over Bolt: from Total Assets (account 1000), follow child_of edges inbound up to 5 hops and return the descendant accounts.

DODIL MCP tools called
data_bolt
Agent

Returns the four asset leaves at hop 1 — 1100 Cash, 1200 Accounts Receivable, 1300 Inventory, 1400 EUR Bank Account. Same answer as graph_khop, different front-end.

Step 4 — Post the balanced opening-balance journal (opening_balances=true)

Opening the books is itself a journal: journal 20260701, status=posted, that debits the asset leaves and credits equity so the books start in balance. (The GL numbers its journals by date — YYYYMMDD — so a journal id sorts chronologically and never collides with the next one.) The balance invariant is the whole ballgame: before a journal is allowed to post, SUM(debit) must equal SUM(credit) exactly. Northwind opens with 250000.00 cash and 80000.00 of inventory against 330000.00 of common stock.

IMPORTANT

Why DECIMAL(18,2) and not double, in one worked line. Split that opening debit across three accounts carrying cents — 500000.10 + 150000.20 + 349999.70 — and the invariant depends on the column type. Over a double, those three read back summing to 1000000.0000000001; the journal does not balance and the posting gate rejects a journal that is arithmetically correct. Over DECIMAL(18,2) the same three sum to exactly 1000000.00. There is no amount of careful application code that fixes a float money column, which is why debit, credit and ledger.balance are all DECIMAL(18,2) from Step 1.

You

In gl-suite, post the opening-balance journal. First upsert journals row 20260701: journal_date 2026-07-01, period 2026-07, source opening-balance, status posted, memo Opening balances. Then upsert its 3 journal_lines (composite key journal_id, line_no): debit Cash 250000.00, debit Inventory 80000.00, credit Common Stock 330000.00. Then check that SUM(debit) equals SUM(credit).

DODIL MCP tools called
data_table_upsert→data_sql
Agent

Journal 20260701 posted with 3 lines. SUM(debit) = 330000.00, SUM(credit) = 330000.00, SUM(debit)-SUM(credit) nets to 0 (exact, no float tail). The journal balances.

Everything after this is an ordinary business day, and the rest of the suite supplies it: a credit sale, the COGS relief against inventory, payroll, a rent accrual, an inventory purchase on account, an AR collection, funding the EUR bank account, marketing on account. By the end of the suite run this bucket holds 21 journals — and the invariant has not moved.

IMPORTANT

The balance is the gate, not an afterthought. In the package this check is not in the route at all — it is posting.assert_balanced(), run by posting.write_journal() before a single row is written, and a journal that does not net to 0.00 is rejected 422. Every posting route in every GL component calls that one function, so there is no path that can post an unbalanced journal by forgetting to check.

Step 5 — Materialize the ledger, and prove idempotency

The ledger is the running per-account balance — derivable from journal_lines, but materialized here so a balance read is O(1) instead of a re-aggregation over every line ever posted. Each row is the signed net SUM(debit) - SUM(credit) per account: a debit-normal account (Cash) carries a positive balance, a credit-normal account (Accounts Payable) a negative one. Summing the whole ledger is then the trial balance — and it must net to 0.00.

Materialize it with INSERT … SELECT … ON CONFLICT (account_id) DO UPDATE — the idempotent path. Run it once or a hundred times; each account ends at the same balance, because a conflicting key updates in place instead of raising a duplicate-key error. One thing to know: a data pg DML statement always reports row_count: 0 regardless of how many rows it actually wrote — so never read the write's count as proof; verify with a follow-up SELECT (the trial-balance query below is exactly that check).

One detail in that statement is load-bearing and was not there in the first draft: currency comes from accounts, not from a literal. Stamping a constant here is how you silently wipe the EUR marker on account 1400 — the bug at the end of this step.

You

In gl-suite, materialize the ledger from journal_lines: insert account_id, SUM(debit) minus SUM(credit) as balance, the account's own currency from accounts, and as_of, grouped by account_id, with ON CONFLICT (account_id) DO UPDATE so it is idempotent. Then show the ledger with account names, and the trial balance = SUM(balance).

DODIL MCP tools called
data_pg→data_sql
Agent

Ledger materialized — 3 rows from the opening journal alone. Cash +250000.00, Inventory +80000.00, Common Stock -330000.00. Trial balance SUM(balance) = 0.00. After all six GL components have posted onto this bucket the same query returns 15 rows and the trial balance is still exactly 0.00.

Three rows after the opening journal; fifteen rows once the whole suite has posted onto this bucket — SUM(debit) = SUM(credit) = 1286470.00 across 21 journals — and in both cases the trial balance is exactly 0.00. That is the point of the invariant: it is not a number that gets closer to zero as the books mature. It is 0.00 after journal one and 0.00 after journal twenty-one, or something is wrong.

Now the correctness 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:

You

In gl-suite, try a bare re-INSERT of the opening line (journal_id 20260701, line_no 1) to show it is rejected, then re-post all 3 lines with INSERT ON CONFLICT DO UPDATE and re-materialize the ledger, and confirm the line count is still 3, the ledger is still 3 rows, 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_lines_pkey (20260701,1) already exists) — a re-INSERT is NOT an upsert. Re-posting the 3 lines with ON CONFLICT and re-materializing the ledger left lines=3, ledger_rows=3, journal balance 0.00, trial balance 0.00 — no double-post.

What running all six components on one bucket found

Every GL component in this suite was built and validated on its own throwaway 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 that run surfaced five integration bugs. Not one of them was findable on a private bucket, because on a private bucket each component is the only writer and the only reader, and a component that is wrong in a self-consistent way looks exactly like a component that is right.

Four of the five were the same shape: a route wrote journal_lines and did not re-derive ledger. Alone that is invisible — nothing else reads ledger, and the component's own reads go back to journal_lines. Composed, five posting routes disagreed about whether ledger was current, and which one you happened to call last decided what the balance sheet said. The fix is the two-line discipline this step teaches, made mandatory: post in one committed transaction, then re-derive the touched accounts in a second one (posting.rederive_ledger(...)), because DataK3 has no read-your-writes inside an open transaction. The worst of the four is in gl/multi-currency: an FX gain of 3230.00 sat in journal_lines and appeared in no report at all.

The fifth belongs to this step, because the re-derive is core's. rederive_ledger() used to stamp ledger.currency from the calling component's FUNCTIONAL_CURRENCY default of USD — so any posting, anywhere in the GL, quietly overwrote the EUR marker on account 1400, and the FX revaluation, which reads ledger.currency to choose its rate pair, then had nothing to revalue. It now takes the currency from accounts.currency: an account's currency is a property of the account, and a re-derive must not be able to change it. gl/multi-currency tells that story from the other end.

IMPORTANT

A component validated alone proves only that it is self-consistent. Composition is a separate proof, and it needs one bucket. This is the argument for the ERP shape the whole suite is built to: one customer, one bucket, one app with a router per module. The integration bugs are not a tax you pay for putting everything in one bucket — they are the bugs you already had, finally visible.

Routes

The download (see Get the code) fronts this bucket with a small FastAPI app, routes.py — CRUD over the models plus the three operations that make a ledger a ledger: post a balanced journal (the SUM(debit) = SUM(credit) gate), materialize the ledger so the trial balance nets to 0.00, and roll a leaf up its account tree (graph). This is what you deploy; quoting it is documenting the GL rules the package bakes in.

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 = the app's token — so there's no data connect step in code, just fixed region constants. Every non-journal write goes through upsert():

# db.py — INSERT ... ON CONFLICT DO UPDATE (idempotent keyed write)
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)

Note and c.name in cols in the update_cols filter: the DO UPDATE set is restricted to the columns the caller actually supplied, so a partial write updates what it names and leaves the rest of the row alone instead of blanking it.

Why it matters for a ledger: on DataK3 a bare re-INSERT of an already-committed primary key raises duplicate-key 23505 — a plain INSERT is not an upsert on re-write. upsert makes a retry, a shard replay, or an at-least-once redelivery post the journal once. And because the ORM engine is Numeric(18, 2) over the pg wire (SQLAlchemy + psycopg), money lands as an exact DECIMAL — never the gRPC upsert path, which would drop an integer 0 into a DECIMAL column.

CRUD — each write is an upsert then a commit (DataK3 has no read-your-writes inside an open transaction; the engine is expire_on_commit=False, so routes commit before they return):

# routes.py — CRUD over the models, keyed on the natural PK
@router.post("/accounts")
def upsert_account(a: AccountIn, user: dict = Depends(current_user),
                   s: Session = Depends(db)):
    upsert(s, Account, [a.model_dump()], key="account_id")
    s.commit()
    return {"ok": True, "account_id": a.account_id}
 
 
@router.get("/accounts/{account_id}")
def get_account(account_id: int, user: dict = Depends(current_user),
                s: Session = Depends(db)):
    row = s.get(Account, account_id)
    if not row:
        raise HTTPException(404, "no such account")
    return row.__dict__ | {"_sa_instance_state": None}

The routes hang off an APIRouter, not the app object, so the suite app can mount all six components on one FastAPI under per-component prefixes; a module-level app = FastAPI(...) at the bottom of the file keeps this package independently runnable with uvicorn routes:app.

Workflow op 1 — post a balanced journal (the Σdebit = Σcredit gate). POST /journals is the whole point of a GL, and notice how little of it is in the route: the route shapes the lines and hands them to posting.write_journal(), which runs both gates (balance, and the closed-period lock), writes idempotently, and appends the journal_audit row naming the acting user. Then — in a second call, therefore a second committed transaction — it re-derives the accounts it touched. This is the route that carries the gl:journal:post gate:

# routes.py — workflow op 1: post a BALANCED journal (the Σdebit = Σcredit gate)
@router.post("/journals")
def post_journal(j: JournalIn,
                 user: dict = Depends(require_permission("gl:journal:post")),
                 s: Session = Depends(db)):
    """Post a double-entry journal — but ONLY if it balances, and ONLY into an open period.
    Both gates live in posting.write_journal(), the GL's single guarded write path: Σdebit
    must equal Σcredit to the cent (422 otherwise, checked in exact Decimal before any row is
    written), and an operational posting into a CLOSED period is rejected 409. Money is
    Numeric(18, 2) end to end, so the sum has no float tail."""
    lines = [{"journal_id": j.journal_id, "line_no": ln.line_no, "account_id": ln.account_id,
              "debit": ln.debit, "credit": ln.credit, "line_memo": ln.line_memo}
             for ln in j.lines]
    total_debit, total_credit = posting.assert_balanced(lines)
    header = {"journal_id": j.journal_id, "journal_date": j.journal_date or posting.now(),
              "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 ──
    # Post → the ledger is current. Every posting route in the GL re-derives the accounts
    # it touched, in a SECOND committed txn (no in-txn read-your-writes). Four of the six
    # components shipped without this: standalone, each was the only writer on its own
    # bucket and its own reads happened to agree; on ONE bucket they disagreed with each
    # other about whether `ledger` was current after a post.
    posting.rederive_ledger([ln.account_id for ln in j.lines], as_of=posting.now())
    return {"ok": True, "journal_id": j.journal_id, "status": "posted",
            "total_debit": str(total_debit), "total_credit": str(total_credit),
            "balance": str(total_debit - total_credit)}

Live-verified on gl-suite: posting the opening journal (the 3 lines from Step 4) returns total_debit 330000.00, total_credit 330000.00, balance 0.00; drop a cent from any line and the same call returns 422 unbalanced journal — the write-path guard that keeps the invariant true, since DataK3 can't enforce it as a DB constraint. Note the amounts come back as strings, not floats: serialising an exact Decimal through a JSON float is the same mistake as storing it in one.

Workflow op 2 — materialize the ledger + the trial balance (a second-txn re-derive). POST /ledger/materialize re-derives each account's signed balance from journal_lines. It runs in its own transaction — DataK3 has no read-your-writes inside the txn that posted the lines — and as a full SUM(...) (never a +=), so N concurrent re-materializations land the same amount once:

# routes.py — workflow op 2: materialize the ledger + trial balance (2nd-txn re-derive)
@router.post("/ledger/materialize")
def materialize_ledger(user: dict = Depends(current_user), s: Session = Depends(db)):
    """Re-derive the running per-account balance from journal_lines, idempotently. This is a
    RE-DERIVE, so it runs in its OWN transaction (DataK3 has no read-your-writes inside the
    txn that posted the lines) and is a full SUM — never a `+=` — so N concurrent
    re-materializations land the same amount once. `INSERT … ON CONFLICT (account_id) DO
    UPDATE` makes the write idempotent; the trial balance SUM(balance) then nets to 0.00."""
    posting.rederive_ledger()  # every account, in its own committed txn
    tb = s.execute(text("SELECT SUM(balance), COUNT(*) FROM ledger")).one()
    return {"ledger_rows": tb[1], "trial_balance": str(posting.money(tb[0] or 0))}

Live-verified on gl-suite: after the opening journal alone this returns ledger_rows 3, trial_balance 0.00; after all six components have run, ledger_rows 15, trial_balance 0.00. Calling it again leaves both unchanged (the ON CONFLICT re-derive is idempotent) — no double-count.

Workflow op 3 — roll a rollup account up its account tree (GRAPH). GET /accounts/{account_id}/rollup walks the gl_accounts graph from a rollup account to its descendant leaves, then sums their ledger balances. DataK3 runs a Cypher subset embedded in SQL — cypher('<graph>', 'MATCH …') — with three rules the code obeys: it's a top-level table function (no UNION/subquery), the anchor id must be an integer literal (so the FastAPI-validated account_id is inlined, not bound), and you feed the returned node ids into a SQL IN (…) (DuckDB has no = ANY(array)):

# routes.py — workflow op 3: account-tree rollup (GRAPH)
@router.get("/accounts/{account_id}/rollup")
def rollup(account_id: int, user: dict = Depends(current_user), s: Session = Depends(db)):
    kids = s.execute(text(
        "SELECT node FROM cypher('gl_accounts', "
        f"'MATCH (root)<-[*1..5]-(child) WHERE id(root) = {account_id} RETURN child')"
    )).scalars().all()
    if not kids and not s.get(Account, account_id):
        raise HTTPException(404, "no such account")
    # DuckDB has no `= ANY(:array)` (UNNEST), so sum the subtree over an inlined IN (…) of ints.
    ids = ",".join(str(int(i)) for i in kids) or "NULL"
    total = s.execute(text(
        f"SELECT COALESCE(SUM(balance), 0) FROM ledger WHERE account_id IN ({ids})"
    )).scalar()
    return {"root": account_id, "descendant_account_ids": list(kids),
            "subtree_balance": str(posting.money(total or 0))}

Live-verified on gl-suite: GET /accounts/1000/rollup returns the four asset leaves (1100, 1200, 1300, 1400) and subtree_balance 428730.00 — Total Assets summed over its subtree, graph and SQL over one copy of the rows. The same walk from 5000 gives Total Expenses 138500.00. Both were read before the rent reversal that gl/journal-entry posts later in the suite run; after it, Total Assets is 438230.00 and Total Expenses 129000.00, which is exactly what you want a rollup to do — reverse the journal and the tree total moves with it, with nothing to re-sync. No flat query gives you that number.

Adding a new GL operation (a reversal, an accrual, a recurring journal) touches only routes.py (and maybe models.py) — the plumbing in db.py and posting.py is fixed. The pattern is one Pydantic *In schema + one @router.<verb> function: money as Decimal, journals via posting.write_journal(...), anything else via upsert(...), re-derive a balance after the write commits, graph via cypher(…) as a top-level SELECT with an integer-literal anchor (see EXTENDING.md in the package).

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

In gl-core exactly one gate survives: POST /journals carries gl:journal:post. POST /accounts, POST /ledger/materialize and GET /accounts/…/rollup take Depends(current_user) and nothing more — they read, or re-derive idempotently from already-committed rows.

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-core/v1.tar. This post is a walkthrough of exactly those files. The tarball is source only, no Dockerfile — on purpose: a component package is something you read and copy from, and the deploy story belongs to the suite app that mounts all six (see below):

models.py          # SQLAlchemy — accounts, journals, journal_lines, ledger (+ account_edges)
routes.py          # FastAPI    — CRUD + post a balanced journal + materialize the ledger + account-tree rollup
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

posting.py, auth.py and sa_token.py are byte-identical across all six GL packages and the suite app — that is deliberate, and PLATFORM.md says why: they are platform code you copy, not business code you regenerate. Everything in PLATFORM.md is a scar from a real failure on this platform, and it travels inside the tar so an agent that downloads the code gets the rules with it.

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)"
# journal_audit has no mapped class here — posting.py writes it with raw SQL. Create it with
# the `dodil data table create journal_audit …` from Step 1 (or ask your agent) before posting.
 
DEV_ALLOW_ANON=1 DEV_USER_PERMISSIONS=gl:journal:post uvicorn routes:app --reload
# POST /accounts · POST /journals (balanced) · POST /ledger/materialize · GET /accounts/{id}/rollup

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 (money as Numeric(18, 2)) with no migration tool. The DEV_ALLOW_ANON=1 is the laptop case only: with no gateway in front of uvicorn there is no X-Dodil-User header, so every request would otherwise be a 401. Never set it in a deployed environment.

How the pillars map

One bucket, one bill, one auth context — the account record is the graph node, and the journal you post is the row the trial balance sums, over one copy of the rows. What this core would otherwise be:

JobThe usual stackOn DataK3
Chart of accounts, journals, ledgerPostgres (or a SaaS ERP module)SQL master tables in the bucket
"Roll Cash up to Total Assets"Neo4j + a nightly syncgl_accounts graph — graph_khop('gl_accounts', 1000, 5, 'in') JOINed to accounts
"Do the books tie?" (trial balance)A reporting warehouse, ETL'd nightlyone SUM(balance) over ledger — read-your-writes, no ETL
Post a journal exactly onceApp-level dedupe + a queueINSERT … ON CONFLICT (journal_id, line_no) DO UPDATE — the wire dedupes
Money that never driftsCareful float handling, or a numeric libDECIMAL(18,2) columns — exact SUM(), no float tail
"Who posted this?"An audit table plus a trigger to fill itone guarded write path (posting.write_journal) appends journal_audit with the gateway-vouched user
Point your own tools at itPer-system drivers & credsdata connect — psql / bolt, DB = bucket

No ETL, no second copy, no drift between the account and its rollup node or its ledger balance — because it's all one bucket. This is the anchor the rest of the GL suite (gl/journal-entry, gl/period-close, gl/financial-reporting, gl/subledger-reconciliation, gl/multi-currency) composes onto by consuming these masters.

Customize — the decisions this skill asks you

Q1 · coa_preset — demo COA or empty schemas?

"Load the demo chart of accounts + a balanced opening-balance journal, or ship empty schemas?"

  • demo (default) → loads 20 accounts (5 roots + 15 leaves) + journal 20260701 (Σdebit = Σcredit = 330000.00) + the seeded ledger, so ## Test asserts the account-type counts, the Total Assets rollup, and the trial balance = 0.00.
  • empty → schemas + graph tables only, no journal; ## Test switches to structural assertions (tables exist, the graph traverses 0 rows without error, the ledger is empty).

Q2 · functional_currency — the reporting currency

"What is the functional (reporting) currency the ledger is kept in?" → USD (default) stamps every account + ledger row with this ISO currency. gl/multi-currency later revalues foreign-currency balances into it. Set it once here and every downstream skill inherits it.

Q3 · hierarchy — build the account graph?

"Build the account-hierarchy graph (account_edges + CREATE GRAPH gl_accounts)?" → true (default, recommended) builds the child_of edges + the gl_accounts graph (Step 3). false skips them — a flat COA where reporting must GROUP BY type instead of traversing the tree. Keep it on: the parent/child rollup is what financial reporting sums account types over.

Q4 · opening_balances — seed the ledger?

"Post the opening-balance journal and seed the ledger?"

  • true (default) → posts journal 20260701 (balanced at 330000.00) + materializes the ledger so the trial balance nets to 0.00 from day one.
  • false → an empty ledger; the first real journal (via gl/journal-entry) opens the books instead.

TIP

Industry overlays. This core is the cross-industry base. Overlays add a small additive diff — finserv adds a regulated close (preparer ≠ approver segregation of duties, an audit trail on every posting); manufacturing adds cost-accounting accounts (inventory / WIP / COGS, standard-cost variance journals) — see the per-industry GL pages.

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 private per-component bucket, which is the point: the numbers below are the numbers a composed GL actually produces, and the bucket is still up. Real results are inline. The route functions in Routes were validated against the same bucket. The default branch is {coa_preset: demo, functional_currency: USD, hierarchy: true, opening_balances: true}. (The empty branch swaps assertions 2–5 for: tables exist, the graph traverses 0 rows without error, the ledger is empty.)

# 1) the master tables exist
dodil data table list -b "$BUCKET"
#  accounts, journals, journal_lines, ledger, journal_audit, account_edges
#  (+ the sidecar tables the other five components own: periods, trial_balance,
#     fx_rates, fx_reval, subledger_items, reconciliation, income_statement, balance_sheet)
 
# 2) the COA seeded — 20 accounts, by type
dodil data sql -b "$BUCKET" "SELECT type, count(*) AS n FROM accounts GROUP BY type ORDER BY type"
#  asset 5, equity 3, expense 5, liability 3, revenue 4   (20 total)
 
# 3) the account graph rolls Total Assets (1000) up to its 4 descendant leaves
dodil data pg -b "$BUCKET" \
  "SELECT a.name FROM graph_khop('gl_accounts', 1000, 5, 'in') k JOIN accounts a ON a.account_id = k.node ORDER BY k.node"
#  Cash, Accounts Receivable, Inventory, EUR Bank Account
 
# 4) the opening journal balances — Σdebit = Σcredit, exact
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 = 20260701"
#  d = 330000.00, c = 330000.00, balance = 0.00
 
# 5) and so does the whole book — 21 journals later
dodil data sql -b "$BUCKET" \
  "SELECT SUM(debit) AS d, SUM(credit) AS c, COUNT(DISTINCT journal_id) AS journals FROM journal_lines"
#  d = 1286470.00, c = 1286470.00, journals = 21
 
# 6) the trial balance nets to 0.00 across the materialized ledger
dodil data sql -b "$BUCKET" "SELECT SUM(balance) AS trial_balance, COUNT(*) AS ledger_rows FROM ledger"
#  trial_balance = 0.00, ledger_rows = 15
 
# 7) idempotent re-run — a bare re-INSERT 23505s; ON CONFLICT re-post leaves counts + balance unchanged
#     (see Step 5) -> journal 20260701 lines 3, trial_balance 0.00
 
# 8) 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
#  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 scaffold the whole GL master-data core at once:

Scaffold the General Ledger master-data core on DataK3 (one bucket = SQL + graph). Confirm each step.
 
1. Create a DataK3 bucket `gl-suite` (`gl` is rejected — DataK3 needs a name of at least 3 characters), then
   five merge-keyed tables (money DECIMAL(18,2), every non-key column nullable):
   - accounts (key account_id): name, type (asset/liability/equity/revenue/expense), normal_balance
     (debit/credit), 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).
   - journal_audit (key audit_id, string uuid): journal_id(long), event, event_at(timestamp).
2. Seed the chart of accounts: 5 roots (1000 Total Assets, 2000 Total Liabilities, 3000 Total Equity,
   4000 Total Revenue, 5000 Total Expenses; parent 0) + 15 leaves (1100 Cash, 1200 Accounts Receivable,
   1300 Inventory, 1400 EUR Bank Account with currency EUR; 2100 Accounts Payable, 2200 Accrued Expenses;
   3100 Common Stock, 3200 Retained Earnings; 4100 Product Revenue, 4200 Services Revenue, 4900 Unrealized
   FX Gain/Loss; 5100 COGS, 5200 Salaries, 5300 Rent, 5400 Marketing). Currency USD except 1400.
3. Build the account graph: account_edges(src,dst,rel) with 15 child_of edges (each leaf up to its root),
   THEN CREATE GRAPH gl_accounts over accounts (KEY account_id) + account_edges. graph_khop('gl_accounts',
   1000, 5, 'in') -> Cash, Accounts Receivable, Inventory, EUR Bank Account.
4. Post the opening journal (journal 20260701, period 2026-07, status posted): debit Cash 250000.00, debit
   Inventory 80000.00; credit Common Stock 330000.00. Assert SUM(debit) = SUM(credit) = 330000.00 before
   posting.
5. Materialize the ledger idempotently: INSERT INTO ledger SELECT jl.account_id, SUM(jl.debit)-SUM(jl.credit),
   COALESCE(MAX(a.currency),'USD'), as_of FROM journal_lines jl LEFT JOIN accounts a ON a.account_id =
   jl.account_id GROUP BY jl.account_id ON CONFLICT (account_id) DO UPDATE — currency from the ACCOUNT, never
   a literal. Trial balance SUM(balance) = 0.00. Prove idempotency: a bare re-INSERT 23505s; the ON CONFLICT
   re-post keeps counts + balance unchanged.

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. The trial balance is one SELECT SUM(balance) FROM ledger; a journal posts with INSERT … ON CONFLICT.
  • Graph over Bolt — a Neo4j driver or cypher-shell against the gl_accounts account tree.

That reach is also the honest limit of the GL's closed-period lock, and gl/period-close states it in those terms: a principal holding k3.editor on the bucket can write journal_lines straight over this wire, and the database will accept it. The control against that is credential custody — in a deployed GL the app's service account should be the only identity with bucket write rights — plus the audit trail, not the schema.

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 the code/gl-core download 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 dodil-appid pool, so six components mean one sign-in and one bill. It ships by the ordinary git cycle (repo → CI → registry → CD): 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.

Conclusion

You now have the master-data core of a General Ledger on one DataK3 bucket: merge-keyed tables you upsert idempotently, an account-hierarchy graph that rolls any leaf up to Total Assets in one hop-ranked query, one guarded write path that every journal in the suite goes through, and a balanced opening-balance journal that seeds a ledger whose trial balance nets to 0.00 — with money as DECIMAL(18,2) so it never drifts, and INSERT … ON CONFLICT so a retry never double-posts. 0.00 after journal one; still 0.00, over 15 ledger rows and 1286470.00 of postings, after all six components have run. No Postgres + Neo4j + a reporting warehouse + the ETL between them. One bucket, one bill, two pillars over one copy of the rows. This is the system of record the rest of the GL suite reads.

Next steps:

  • Compose the workflow skills onto this core: journal-entry (post balanced journals idempotently, reverse by mirror), period-close (lock a period, roll net income to retained earnings, trial balance = 0.00), financial-reporting (P&L + a balance sheet that ties over the account graph), subledger-reconciliation (AP/AR control-account tie-out), multi-currency (FX revaluation with DECIMAL(18,6) rates).
  • The GL is the deliberate stress test of the DataK3 pg wire for finance — idempotent posting, the balance invariant, money exactness. This core proves the first three; the workflows prove the rest.