What you'll build: the two financial statements every accounting close produces — an income statement (P&L: revenue − expense = net income) and a balance sheet (assets = liabilities + equity) — directly from the ledger your GL already holds, on one DataK3 bucket. Every statement line is rolled up by account type over the gl/core account-hierarchy graph: graph_khop('gl_accounts', 4000, 5, 'in') walks from Total Revenue down to its posting leaves, and the same over Total Assets. The reports land in two merge-keyed tables (income_statement, balance_sheet) you materialize idempotently — and the balance sheet ties to the cent, not by luck but because the trial balance nets to 0.00. Two pillars, SQL and graph, over one copy of the rows — no reporting warehouse, no nightly ETL.

What you'll learn:

  • Roll the ledger up by account type over the graph — graph_khop from each statement root to its descendant posting leaves, so an arbitrarily deep sub-account tree sums to the right line.
  • Compute a period P&L — revenue − expense = net income — as exact DECIMAL(18,2), no float tail.
  • Build a balance sheet that ties: assets − (liabilities + equity + net income) = 0.00, and see why it ties — because SUM(ledger.balance) = 0.00 (double-entry), not a coincidence you check for.
  • Materialize both statements idempotently — INSERT … ON CONFLICT (period, section) DO UPDATE — so a nightly refresh or a replay overwrites the period in place instead of double-counting it.
  • Run every step two ways — by prompting an agent over the MCP, or the dodil data CLI.

The problem — and why it matters

The controller closes the books every month and the CFO signs the statements every quarter, and the one question an auditor asks first is blunt: does the balance sheet balance? If total assets don't equal liabilities plus equity to the penny, the statements are wrong, the close doesn't sign off, and — for a public company — you are staring at a restatement: re-audited periods, a filing with the regulator, a hit to the share price. "Approximately balanced" is not a thing. The sheet ties, or it isn't a balance sheet.

The classic stack makes this harder than it should be. The ledger lives in the ERP, but reporting runs in a separate warehouse the GL is ETL'd into overnight — so the statements are always a day stale, and the account rollup (which posting accounts sum into "Total Assets", which into "Revenue") lives in a third place: a BI tool's semantic layer, or a hand-maintained mapping table that drifts from the chart of accounts the moment someone adds a sub-account. Three systems, two copies, one mapping that rots — a reporting-warehouse bill and per-seat ERP reporting licences on top, and a close cycle measured in days because every statement waits on last night's ETL.

On DataK3 the statements are derived from the same ledger rows the GL already holds — read-your- writes, no ETL — and the rollup is the account graph you built in gl/core: the account record is the graph node, so Total Revenue's posting leaves are one graph_khop away and can never drift from the chart of accounts, because they are the chart of accounts. The P&L and the balance sheet are two small tables you materialize idempotently, and the sheet ties because double-entry guarantees it. No warehouse, no semantic layer, no nightly job. One bucket, two pillars.

PieceLands inPillar
Account-type rollup (leaf → Total Revenue / Total Assets)gl_accounts graph (graph_khop)Graph
Income statement (revenue − expense = net income)income_statement (keyed period, section)SQL
Balance sheet (assets = liabilities + equity + net income)balance_sheet (keyed period, section)SQL

NOTE

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

This skill consumes accounts / ledger / journal_lines and the gl_accounts graph from gl/core. If core isn't in your bucket yet, run its one-shot first — it stands up the masters, seeds the 20-account chart of accounts, builds the account graph, and posts the balanced opening-balance journal that seeds the ledger.

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 reporting period is 2026-09, the period param; the functional currency is USD.) The obvious name gl is not available: DataK3 rejects a bucket name shorter than 3 characters, which is why the whole suite lives on gl-suite.
  • gl/core scaffolded in that bucket: the accounts / ledger / journal_lines masters, the gl_accounts graph, and the opening journal 20260701 posted so the ledger is seeded.

Step 1 — Post a month of activity, re-materialize the ledger

gl/core opened the books with a single balanced journal. A financial statement needs a period of activity to report on — revenue earned, expenses incurred — so post the five operating journals that make up 2026-09: a product sale on credit, its cost of goods sold, a supplier payment, the monthly payroll, and the month's rent. Each balances (Σdebit = Σcredit); together they move the P&L accounts off zero. Then re-materialize the ledger from every posted line — the signed net SUM(debit) − SUM(credit) per account — and confirm the trial balance is still 0.00. That zero is the precondition the whole report rests on.

Journal ids here are the posting date (YYYYMMDD) — a natural key you can read, which is the same reason every table in this suite uses a natural PK rather than a sequence.

You

In the gl-suite bucket, post five balanced operating journals for period 2026-09 on top of the July and August history: 20260905 debit Accounts Receivable 95000.00, credit Product Revenue 95000.00; 20260908 debit Cost of Goods Sold 36000.00, credit Inventory 36000.00; 20260910 debit Accounts Payable 45000.00, credit Cash 45000.00; 20260915 debit Salaries 41000.00, credit Cash 41000.00; 20260918 debit Rent 9500.00, credit Cash 9500.00. Then re-materialize the ledger from journal_lines (account_id, SUM(debit)-SUM(credit) as balance, as_of 2026-09-30) with ON CONFLICT (account_id) DO UPDATE, and show the trial balance SUM(balance).

DODIL MCP tools called
data_table_upsert→data_pg→data_sql
Agent

Posted the five 2026-09 journals (10 balanced lines). Ledger re-materialized to 15 rows. Trial balance SUM(balance) = 0.00 — the books still tie after a month of activity.

NOTE

The statements below report the bucket's final state, not just these five journals. gl-suite carries the whole suite: gl/core's opening journal and the July/August history, the 2026-07 close (gl/period-close, which rolled 28000.00 of net income into Retained Earnings), the FX revaluation (gl/multi-currency, +3230.00 of unrealized gain), and the reversal of journal 20260918 (gl/journal-entry, which is why Rent nets to 0.00 in the P&L you're about to materialize). Twenty-one journals, SUM(debit) = SUM(credit) = 1286470.00, fifteen ledger rows, trial balance exactly 0.00. Reports are the last thing you run in a close, and this is what "last" means.

NOTE

ledger.balance is the signed net SUM(debit) − SUM(credit) per account. Debit-normal accounts (assets, expenses) carry a positive balance; credit-normal accounts (liabilities, equity, revenue) a negative one. That sign convention is what makes SUM(ledger.balance) the trial balance — and what lets the statements below flip a credit-normal total back to a presentation-positive number with a single -SUM(...).

Step 2 — Roll the ledger up by type over the graph (the P&L)

Here is the graph pillar. A P&L groups the ledger by account type — but "which posting accounts are revenue?" is a hierarchy question, and the hierarchy is the gl_accounts graph. Because the account edges point child → parent, a rollup account's posting leaves are the incoming direction: graph_khop('gl_accounts', 4000, 5, 'in') walks up to 5 hops inbound from Total Revenue (4000) and hands back its descendant leaves — Product Revenue (4100), Services Revenue (4200) and Unrealized FX Gain/Loss (4900). Join those to ledger, and -SUM(balance) (flip the credit-normal sign) is revenue. The same from Total Expenses (5000) gives expense, and revenue − expense is net income.

Note what the graph does for you here without being asked: 4900 Unrealized FX Gain/Loss was added to the chart by gl/multi-currency, hung under Total Revenue, and it lands in the revenue line automatically. Nobody edited a report definition. The rollup is the chart of accounts.

You

In gl-suite, roll the ledger up by account type over the gl_accounts graph. Use graph_khop from Total Revenue (4000) inbound to its posting leaves joined to the ledger for revenue, and from Total Expenses (5000) for expense. Show revenue, expense, and net income (revenue minus expense) for period 2026-09.

DODIL MCP tools called
data_pg
Agent

Revenue leaves 4100 Product (-95000.00) + 4200 Services (-75000.00) + 4900 Unrealized FX Gain/Loss (-3230.00) → revenue 173230.00. Expense leaves 5100/5200/5300/5400 → expense 129000.00. Net income = 173230.00 - 129000.00 = 44230.00.

NOTE

These rollups are live reads, so they move when the ledger moves. Run earlier in the same suite session, graph_khop('gl_accounts', 5000, 5, 'in') returned 138500.00 and the Total Assets rollup 428730.00 — both taken before gl/journal-entry reversed journal 20260918, the 9500.00 September rent. After the reversal, Rent is 0.00, expenses are 129000.00 and Total Assets is 438230.00 (the 9500.00 went back to Cash). Nothing was corrected; a reversal moved the books, and a query over live rows reported it. That is the point of having no ETL — and the reason a statement is only meaningful with an as_of on it.

The same rollup in Cypher over Bolt — anchor on the statement root, follow child_of edges inbound, and the graph plane hands back the posting leaves that feed the line:

You

Same rollup in Cypher over Bolt: from Total Expenses (account 5000), follow child_of edges inbound up to 5 hops and return the descendant expense accounts.

DODIL MCP tools called
data_bolt
Agent

Returns the four expense leaves at hop 1 — 5100 Cost of Goods Sold, 5200 Salaries, 5300 Rent, 5400 Marketing. Same set graph_khop rolled up, different front-end.

TIP

graph_khop is a top-level-SELECT table function. It projects two columns — node (the account id) and hop_distance (there is no node_id) — so join on k.node (e.g. SELECT node, hop_distance FROM graph_khop('gl_accounts', 4000, 5, 'in') k JOIN ledger l ON l.account_id = k.node). The graph plane intercepts a query of the shape SELECT … FROM graph_khop('gl_accounts', 4000, 5, 'in') …, and its start node must be an integer literal (4000), not a column. It therefore can't be embedded inside INSERT … SELECT (that path routes to plain SQL, which doesn't know graph_khop) or inside a UNION branch. The pattern below is the one that works: roll up with a SELECT to get each line's figure, then materialize with a type-grouped INSERT … ON CONFLICT — the graph proves that a leaf's type equals its statement root, so grouping by the denormalized accounts.type column lands the same numbers, idempotently.

Step 3 — Materialize the income statement (idempotent)

Write the P&L into income_statement, keyed on (period, section) so every month owns its own rows. The materialization is INSERT … SELECT … ON CONFLICT (period, section) DO UPDATE — the idempotent path: re-run it for the same period and each row updates in place instead of raising a duplicate-key error. The three sections — revenue, expense, net_income — come straight off the ledger grouped by accounts.type (the grouping the graph in Step 2 just proved equals the statement rollup). Each SELECT below keeps its FROM ledger l JOIN accounts a — that matters: a scalar INSERT … SELECT <constant> … ON CONFLICT with no FROM is rejected, so if you ever hand-materialize one section from a bare scalar, give it a dummy FROM (VALUES (1)) AS t(x).

You

In gl-suite, create an income_statement table keyed on (period, section) with amount DECIMAL(18,2) and as_of. Materialize the P&L for period 2026-09 with INSERT ... SELECT ... ON CONFLICT (period, section) DO UPDATE: revenue = -SUM(ledger.balance) over revenue accounts, expense = SUM over expense accounts, net_income = revenue - expense. Then show the statement.

DODIL MCP tools called
data_table_create→data_pg→data_sql
Agent

Created income_statement (pk period, section). Materialized 3 rows for 2026-09: revenue 173230.00, expense 129000.00, net_income 44230.00. Re-runnable — ON CONFLICT overwrites the period in place.

period is a label, not a filter — read this before you trust the number

Both statements are built from ledger, and ledger.balance is the all-time signed net per account — every posted line since the books opened, not the movement inside one month. The period column on income_statement and balance_sheet is therefore a label on the snapshot. It filters nothing.

The reason it nevertheless reads as a period P&L is the close. gl/period-close posts a closing journal that zeroes the revenue and expense accounts into Retained Earnings, so after a close those accounts carry only what has happened since it. An all-time sum over them is then exactly "earnings since the last close" — which is the period figure, as long as the close actually ran. Skip a close, or report a month whose predecessor was never closed, and the same query quietly returns a cumulative number under a monthly heading. Nothing errors. The label is still there.

The live table says it out loud, and this is the honest way to show it — materialize the same statement under two different period labels and compare:

You

In gl-suite, show every row of income_statement across all periods, with the as_of timestamp, so I can compare 2026-07 and 2026-09.

DODIL MCP tools called
data_sql
Agent

Six rows across two periods, and the amounts are identical: 2026-07 and 2026-09 both read revenue 173230.00, expense 129000.00, net_income 44230.00 — they differ only in as_of. The period column labelled the snapshot; it did not filter the ledger.

IMPORTANT

What a strict single-month P&L would take instead. Two options, both already in the suite: sum journal_lines joined to journals and filter on journals.period — the movement genuinely booked in that month, regardless of when the ledger was last derived — or snapshot at close time, which is exactly what gl/period-close does with its trial_balance table (2026-07: 9 accounts, SUM(debit) = SUM(credit) = 542000.00, frozen at the moment of the close).

This build takes the simpler route on purpose: reading ledger is one join instead of a scan over every line ever posted, and after a disciplined close it is the same number. That is a legitimate trade — but it is a trade, and an accountant reading your statements is entitled to know which one you made. Ship the scope note, don't bury it. The route's docstring in routes.py carries this same paragraph, so it travels with the code.

Step 4 — Build the balance sheet, and prove it ties

The balance sheet is the same rollup over the other three types: assets (debit-normal, positive SUM), liabilities and equity (credit-normal, -SUM). One subtlety makes it correct rather than merely close: the period's revenue and expenses are still open — gl/period-close hasn't rolled them into Retained Earnings yet — so the sheet must carry the current-period net income as its own equity line. Add it, and:

assets  =  liabilities  +  equity(booked)  +  net income

And this is the punchline: the sheet ties because the trial balance is 0.00. Since SUM(ledger.balance) = 0 over every account, assets (the debit-normal total) must equal the negated sum of all the credit-normal totals — liabilities, booked equity, and the net of revenue/expense, which is net income. The tie isn't a check that might fail; it's an identity double-entry hands you.

You

In gl-suite, create a balance_sheet table keyed on (period, section) with amount DECIMAL(18,2) and as_of. Materialize for 2026-09 with INSERT ... ON CONFLICT: assets = SUM(ledger.balance) over asset accounts, liabilities = -SUM over liability accounts, equity = -SUM over equity accounts, net_income = -SUM over revenue+expense accounts. Then assert assets - (liabilities + equity + net_income) = 0.00.

DODIL MCP tools called
data_table_create→data_pg→data_sql
Agent

Created balance_sheet (pk period, section). Materialized 4 rows for 2026-09: assets 438230.00, liabilities 36000.00, equity 358000.00, net_income 44230.00. The tie: 438230.00 - (36000.00 + 358000.00 + 44230.00) = 0.00. The sheet balances.

Two of those lines are other components' work showing up in the statement, which is what "one bucket" buys you. Retained Earnings 28000.00 is the 2026-07 net income that gl/period-close rolled into equity with closing journal 20261600 — it is booked equity now, not current-period earnings, which is why it sits in equity and not in net_income. And EUR Bank Account 111470.00 is a foreign-currency balance carried at the 2026-09-30 rate because gl/multi-currency revalued it. Neither number needed a report definition, an ETL job, or a mapping table: they are ledger rows the same query already sums.

IMPORTANT

The sheet ties because the books do. assets − (liabilities + equity + net income) = 0.00 is the same 0.00 as the trial balance — one is the rearrangement of the other. If this assertion ever returns a non-zero, the ledger itself is out of balance (an unbalanced journal slipped through, or a float money column drifted); the balance sheet is just where you'd notice. That's why the amount columns are DECIMAL(18,2), never double.

Multi-currency note — and its sharp edge. A book with foreign-currency balances must foot against the functional-currency trial balance (foreign balances converted at the period-end rate), which ties only after gl/multi-currency revalues — so revalue before you report. Here account 1400 holds 111470.00 because the reval ran, and the 3230.00 of unrealized gain it booked is inside the 173230.00 revenue line. The edge: an unbalanced ledger is loud (the tie goes non-zero), but a missing revaluation is silent — the books tie perfectly at 0.00 either way, and the statements are simply wrong by the gain. The tie is a necessary condition, never a sufficient one.

Step 5 — Prove idempotent materialization

Reports get re-run — a nightly refresh, a re-close after a late adjustment, an at-least-once trigger. A re-materialization must overwrite the period, never double it. A bare re-INSERT of a committed report row does not do that — it raises a duplicate-key error — which is exactly why the materialization uses ON CONFLICT:

You

In gl-suite, show that a bare re-INSERT of the income_statement row ('2026-09','revenue') is rejected, then re-run both report materializations with ON CONFLICT and confirm income_statement is still 3 rows for the period, balance_sheet still 4 rows, net_income still 44230.00, and the balance sheet still ties to 0.00.

DODIL MCP tools called
data_pg→data_sql
Agent

The bare re-INSERT raised SQLSTATE 23505 (duplicate key (period, section)=('2026-09','revenue')) — a re-INSERT is NOT an upsert. Re-running both materializations with ON CONFLICT left 3 income_statement rows for 2026-09, balance_sheet=4 rows, net_income 44230.00, tie 0.00 — no double-count.

TIP

DO UPDATE SET takes only EXCLUDED.<col> references. A literal on the right-hand side (SET as_of = TIMESTAMP '2026-09-30') is rejected FeatureNotSupported (42601) — put the constant in the SELECT list, as both materializations above do, and reference EXCLUDED.as_of. Related trap from the same family: a FROM-less INSERT … SELECT <scalars> … ON CONFLICT is a parser error, which is why every branch of the UNION ALL keeps its FROM ledger l JOIN accounts a.

What one bucket found — the report was right and the answer was wrong

Each of the six GL components was built and validated on its own bucket, and each passed. Then all six were run together on gl-suite, and that surfaced five integration bugs — none of which any component could have detected alone. Reporting is where the ugliest one surfaced, so it is worth telling here even though it wasn't reporting's fault.

Four of the five posting routes across the suite wrote journal_lines and never re-derived ledger. Because both statements read ledger, the failure arrived here dressed as a reporting problem: the 3230.00 FX gain that gl/multi-currency had genuinely posted existed in journal_lines and in no statement. Revenue was short by exactly that amount — and the balance sheet still tied at 0.00, because a balanced journal that is missing from a derived table breaks no invariant. The report was faithfully summarising a stale input, and it had no way to know.

That is the corollary worth carrying into your own build: a report cannot detect an input that was never derived. Adding a freshness check to the reader is the wrong fix — the reader has nothing to compare against. The re-derive has to be an obligation of the write path, so it can never be skipped by whoever happens to call last:

# every posting route in the GL, after the write path commits — a SECOND txn, idempotently
posting.write_journal(s, header, lines, actor=user["sub"])
posting.rederive_ledger([l.account_id for l in body.lines], as_of=posting.now())

Two committed transactions, not one, because DataK3 has no read-your-writes inside an open transaction — a SELECT in the same txn cannot see the lines it just inserted, so the re-derive must follow the commit. And it re-derives with SUM(debit) − SUM(credit), never +=, so N concurrent re-posts land the balance once.

The general lesson under all five: a component validated alone proves only that it is self-consistent. Composition is a separate proof, and it needs one bucket. The other stories are worth reading where they happened — the silently non-idempotent revaluation and the currency marker a re-derive kept wiping in gl/multi-currency, the sole-writer posting path in gl/journal-entry.

Routes

The CLI steps above are the data plane; the download (see Get the code) fronts the same bucket with a small FastAPI app, routes.py — post a balanced journal, re-materialize the ledger, and produce the two statements over HTTP. This is what you deploy. Every route obeys the finance/pg-wire rules the steps proved, so quoting it is documenting them.

The connection and the one write helper live in db.py. A DataK3 bucket is a Postgres endpoint — db name = the bucket, user = the literal token, password = your DODIL token — so there's no data connect step in code, just fixed region constants. The SQLAlchemy engine is psycopg over the pg wire, which is exactly why money is written correctly: a Numeric(18, 2) column binds as an exact DECIMAL, never a float. upsert() (INSERT … ON CONFLICT DO UPDATE) is the idempotent keyed writer — a bare re-INSERT of a committed key raises 23505, so upsert is what makes a retry or replay land the row once.

The routes hang off an APIRouter, not directly off the app. That one line is what lets this package run standalone for this post (uvicorn routes:app, with app = FastAPI(...) + app.include_router(router) at the bottom of the file) and be mounted alongside the other five in the suite app — see The suite below.

Post a journal — through the GL's one write path. POST /journals doesn't write the tables itself. It hands the journal to posting.write_journal(), the sole writer for journals / journal_lines in the entire GL: that function runs the balance gate (a journal that doesn't balance is a 422), the closed-period lock (a journal aimed at a closed period is a 409), writes idempotently, and appends the acting user to the append-only journal_audit. Then — separately, after the commit — the route re-derives the accounts it touched:

# routes.py — post a balanced journal through the sole writer, then re-derive in a 2nd txn
@router.post("/journals")
def post_journal(body: PostJournalIn,
                 user: dict = Depends(require_permission("gl:journal:post")),
                 s: Session = Depends(db)):
    lines = [l.model_dump() for l in body.lines]
    total_debit, total_credit = posting.assert_balanced(lines)
    header = body.journal.model_dump()
    header["journal_date"] = header.get("journal_date") or posting.now()
    posting.write_journal(s, header, lines, actor=user["sub"])
    # 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([l.account_id for l in body.lines], as_of=posting.now())
    return {"ok": True, "journal_id": body.journal.journal_id, "balanced": True,
            "debit": str(total_debit), "credit": str(total_credit)}

Note actor=user["sub"] — the audit row carries the gateway-vouched end user, not the service account that holds the bucket credential. That distinction is the whole two-plane rule, and it is what makes the audit trail worth anything.

Re-materialize the ledger — the second-transaction re-derive. POST /ledger/rematerialize re-derives the running balance per account = SUM(debit) − SUM(credit) over every posted line, in its own transaction, and idempotently — it SUMs from source (never +=), so a replay lands the same balance once. It returns the trial balance, which must be 0.00 exact — the precondition every statement rests on. It calls the same posting.rederive_ledger() the posting engine and the close use, and the docstring says why that matters:

# routes.py — re-derive the ledger in a SECOND txn (after /journals committed), idempotently
@router.post("/ledger/rematerialize")
def rematerialize_ledger(period_end: str = "2026-09-30 00:00:00",
                         user: dict = Depends(current_user), s: Session = Depends(db)):
    """...
    The re-derive itself is posting.rederive_ledger(), the SAME function the posting engine
    and the close use. This matters more than it looks: an earlier version of this route had
    its own copy of the SQL that summed EVERY journal_line regardless of journal status, so
    a draft journal would have moved the reported balance while the posting engine's ledger
    ignored it — two functions, one table, two different answers. One derivation, one truth."""
    posting.rederive_ledger(as_of=datetime.fromisoformat(period_end))
    tb, rows = s.execute(text("SELECT SUM(balance), COUNT(*) FROM ledger")).one()
    total = posting.money(tb or 0)
    return {"trial_balance": str(total), "ledger_rows": rows, "ties": total == Decimal("0.00")}

Roll up over the graph. GET /rollup/{root_account_id} sums the ledger over a statement root's descendant posting leaves — the graph pillar. graph_khop is a top-level SELECT projecting node (+ hop_distance, never node_id) and its start node must be an integer literal, so the FastAPI-validated root_account_id is inlined, not bound — and it can't sit inside an INSERT … SELECT:

# routes.py — the account-type rollup (GRAPH): graph_khop is a top-level SELECT, int-literal start
@router.get("/rollup/{root_account_id}")
def rollup(root_account_id: int, user: dict = Depends(current_user),
           s: Session = Depends(db)):
    total = s.execute(text(
        "SELECT COALESCE(SUM(l.balance), 0) "
        f"FROM graph_khop('gl_accounts', {int(root_account_id)}, 5, 'in') k "
        "JOIN ledger l ON l.account_id = k.node"
    )).scalar()
    leaves = s.execute(text(
        f"SELECT node FROM graph_khop('gl_accounts', {int(root_account_id)}, 5, 'in')"
    )).scalars().all()
    return {"root": root_account_id, "leaf_account_ids": leaves, "signed_balance": float(total)}

Live-verified: GET /rollup/4000 returns Total Revenue's leaves [4100, 4200, 4900] with signed_balance -173230.00 (flip the credit-normal sign → revenue 173230.00); GET /rollup/5000 → expense leaves [5100, 5200, 5300, 5400], 129000.00; GET /rollup/1000 → assets 438230.00.

Produce the statements — idempotent materialization. POST /reports/income-statement and POST /reports/balance-sheet each write their sections with INSERT … SELECT … ON CONFLICT (period, section) DO UPDATE SET amount = EXCLUDED.amount — the graph in Step 2 proves the type grouping equals the statement rollup, and DO UPDATE SET takes only EXCLUDED.<col> refs (a literal is FeatureNotSupported). The balance-sheet route returns the tie as a computed field:

# routes.py — materialize the balance sheet idempotently, and return the tie
@router.post("/reports/balance-sheet")
def materialize_balance_sheet(period: str = "2026-09", period_end: str = "2026-09-30 00:00:00",
                              user: dict = Depends(current_user),
                              s: Session = Depends(db)):
    s.execute(text(
        "INSERT INTO balance_sheet (period, section, amount, as_of) "
        f"SELECT '{period}', section, amount, TIMESTAMP '{period_end}' FROM ("
        "  SELECT 'assets' AS section, SUM(CASE WHEN a.type='asset' THEN l.balance ELSE 0 END) AS amount"
        "    FROM ledger l JOIN accounts a ON a.account_id = l.account_id"
        "  UNION ALL SELECT 'liabilities', -SUM(CASE WHEN a.type='liability' THEN l.balance ELSE 0 END)"
        "    FROM ledger l JOIN accounts a ON a.account_id = l.account_id"
        "  UNION ALL SELECT 'equity', -SUM(CASE WHEN a.type='equity' THEN l.balance ELSE 0 END)"
        "    FROM ledger l JOIN accounts a ON a.account_id = l.account_id"
        "  UNION ALL SELECT 'net_income', -SUM(CASE WHEN a.type IN ('revenue','expense') THEN l.balance ELSE 0 END)"
        "    FROM ledger l JOIN accounts a ON a.account_id = l.account_id"
        ") t "
        "ON CONFLICT (period, section) DO UPDATE SET amount = EXCLUDED.amount, as_of = EXCLUDED.as_of"
    ))
    s.commit()
    bs = {sec: float(amt) for sec, amt in s.execute(
        select(BalanceSheet.section, BalanceSheet.amount).where(BalanceSheet.period == period)
    ).all()}
    ties = round(bs["assets"] - (bs["liabilities"] + bs["equity"] + bs["net_income"]), 2)
    return {"period": period, "balance_sheet": bs, "ties": ties, "balanced": ties == 0.0}

Live-verified: POST /reports/income-statement returns {revenue: 173230.0, expense: 129000.0, net_income: 44230.0}; POST /reports/balance-sheet returns {assets: 438230.0, liabilities: 36000.0, equity: 358000.0, net_income: 44230.0} with ties: 0.0, balanced: true — the sheet ties over HTTP, to the cent. Adding a new report touches only routes.py — one Pydantic *In schema + one @router.<verb> function (see EXTENDING.md).

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

Here the only gate is on the posting route the package ships so it can run standalone: POST /journals carries gl:journal:post. The two report materializations (POST /reports/income-statement, POST /reports/balance-sheet), the account-tree rollup and the ledger re-derive are current_user only. A report is derived from committed rows and is idempotent — re-running it produces the same rows — so the thing worth gating is the posting that changes the inputs, not the read that summarises them.

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-financial-reporting/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only (no Dockerfile — deploy is a pure-SQL image-mode Ignite app, or run it anywhere with a token):

models.py          # SQLAlchemy — accounts, account_edges, journals, journal_lines, ledger, income_statement, balance_sheet
routes.py          # FastAPI    — post journals, re-materialize the ledger, produce the P&L + balance sheet (+ the tie)
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) + PERIOD
requirements.txt   # sqlalchemy, psycopg[binary], fastapi, uvicorn, pydantic, httpx

There is no pyjwt and nothing to configure for identity — no issuer, no audience, no JWKS. That is the gateway's job now, and the diff is the point: converting this package to header-trust auth deleted a dependency and two env vars rather than adding any.

Read PLATFORM.md before you copy this into a customer build. These packages are reference implementations, not product: you read them, derive the customer's real model, and generate their system. PLATFORM.md is the other half of that instruction — the platform lines you copy verbatim instead of regenerating (db.py / sa_token.py / auth.py, COPY *.py, numeric USER 10001, secrets by reference) and the DataK3 semantics this code depends on. Every line in it is a scar from a real failure.

Run it — point .env at your bucket, create the two report 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"; gl/core owns the masters + the gl_accounts graph. Create the report
# tables from the models (the same income_statement + balance_sheet Steps 3–4 built by CLI):
python -c "import db, models; models.Base.metadata.create_all(db.engine)"
 
# no gateway in front of uvicorn, so opt in to a stub identity and grant it the one gate:
export DEV_ALLOW_ANON=1 DEV_USER_PERMISSIONS=gl:journal:post
uvicorn routes:app --reload
# POST /journals · POST /ledger/rematerialize · GET /rollup/{root} · POST /reports/income-statement · POST /reports/balance-sheet

models.Base.metadata.create_all builds the ORM tab's classes at once — the same tables Steps 3–4 created by CLI, from the natural-key models with no migration tool.

How the pillars map

One bucket, one bill, one auth context — the account record is the graph node, and the ledger row the statements sum is the same row the trial balance nets to zero. What this reporting layer would otherwise be:

JobThe usual stackOn DataK3
"Which accounts roll into Revenue / Total Assets?"A BI semantic layer or a mapping tablegraph_khop('gl_accounts', 4000, 5, 'in') — the chart of accounts is the rollup
P&L + balance sheetA reporting warehouse, ETL'd nightlyINSERT … SELECT … ON CONFLICT over the live ledger — read-your-writes
"Does the balance sheet balance?"A reconciliation report you hope is zeroone SELECT — assets − (liab + equity + NI) = 0.00, an identity of double-entry
Refresh the statementsTruncate-and-reload, or a dedupe keyON CONFLICT (period, section) DO UPDATE — the period overwrites in place
Money that never driftsCareful float handlingDECIMAL(18,2) columns — exact SUM(), no float tail
Point a BI tool at itWarehouse creds + a syncdata connect — psql / bolt, DB = bucket

No ETL, no second copy, no rollup mapping that drifts from the chart of accounts — because it's all one bucket. This composes onto gl/core by consuming its accounts / ledger masters and the gl_accounts graph.

Customize — the decisions this skill asks you

Q1 · period — which period to report

"Which accounting period are you reporting (YYYY-MM)?" → 2026-09 (default). income_statement and balance_sheet rows are keyed on (period, section), so each month materializes its own statements without clobbering a prior period: re-running the same period is idempotent, a new period appends its rows. Remember what the key does and does not do — it partitions the report table, it does not filter the ledger the numbers come from (see period is a label, not a filter).

Q2 · functional_currency — the presentation currency

"What is the functional (reporting) currency the statements are presented in?" → USD (default). Every ledger balance the statements sum is kept in this ISO currency (set once in gl/core); gl/multi-currency revalues foreign-currency balances into it before reporting.

Q3 · rollup_via_graph — graph rollup or flat GROUP BY type?

"Roll sub-accounts up to their statement line over the account graph, or GROUP BY the flat type column?"

  • true (default, recommended) → the graph pillar: graph_khop('gl_accounts', <root>, 5, 'in') enumerates each statement root's descendant posting leaves. Correct for arbitrarily deep sub-account trees (leaf → sub-total → root), where a flat GROUP BY would miss a mid-tier rollup.
  • false → GROUP BY accounts.type directly, no traversal. Identical numbers on this single-level COA (type is denormalized on every account), simpler — but only correct if your chart is provably flat.

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 a throwaway per-component bucket. That matters: it is the only configuration in which the numbers below are the composed answer rather than one component agreeing with itself, and it is how the integration bug above was found. The bucket is still up. Real results are inline. The default branch is {period: 2026-09, functional_currency: USD, rollup_via_graph: true}.

export BUCKET=gl-suite
 
# 0) precondition — the books tie: trial balance nets to 0.00 across 15 ledger rows
dodil data sql -b "$BUCKET" \
  "SELECT SUM(balance) AS trial_balance, COUNT(*) AS n FROM ledger"
#  trial_balance = 0.00, n = 15
 
# 0b) and the source they derive from balances too — 21 journals, both sides equal
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
 
# 1) type rollup via the graph — Total Revenue (4000) inbound → its posting leaves, summed
dodil data pg -b "$BUCKET" \
  "SELECT -SUM(l.balance) AS revenue FROM graph_khop('gl_accounts', 4000, 5, 'in') k JOIN ledger l ON l.account_id = k.node"
#  revenue = 173230.00   (Total Expenses 5000 → expense 129000.00)
 
# 2) the P&L — revenue − expense = net income
dodil data sql -b "$BUCKET" "SELECT section, amount FROM income_statement WHERE period='2026-09'"
#  revenue 173230.00, expense 129000.00, net_income 44230.00
 
# 3) the balance sheet lines
dodil data sql -b "$BUCKET" "SELECT section, amount FROM balance_sheet WHERE period='2026-09'"
#  assets 438230.00, liabilities 36000.00, equity 358000.00, net_income 44230.00
 
# 4) THE TIE — assets − (liabilities + equity + net income) = 0.00
#  (see Step 4) -> ties = 0.00   [438230.00 - (36000.00 + 358000.00 + 44230.00)]
 
# 5) idempotent re-materialization — ON CONFLICT re-run leaves counts + figures unchanged;
#     a bare re-INSERT of ('2026-09','revenue') raises SQLSTATE 23505 (see Step 5)
#  -> is_rows 3, bs_rows 4, net_income 44230.00
 
# 6) the scope note, proven rather than asserted: `period` labels, it does not filter
dodil data sql -b "$BUCKET" "SELECT period, section, amount FROM income_statement ORDER BY period, section"
#  2026-07 and 2026-09 carry IDENTICAL amounts (173230.00 / 129000.00 / 44230.00)
 
# 7) drop-in clients: same bucket, your own psql / BI tool / 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 and gl/core already scaffolded in bucket gl-suite, paste this to produce the statements at once:

In the gl-suite bucket (gl/core already scaffolded — masters, gl_accounts graph, opening journal 20260701
and the July/August history), produce the 2026-09 financial statements. Confirm each step.
 
1. Post the five balanced 2026-09 operating journals: 20260905 Dr Accounts Receivable 95000.00 / Cr
   Product Revenue 95000.00; 20260908 Dr COGS 36000.00 / Cr Inventory 36000.00; 20260910 Dr Accounts
   Payable 45000.00 / Cr Cash 45000.00; 20260915 Dr Salaries 41000.00 / Cr Cash 41000.00; 20260918
   Dr Rent 9500.00 / Cr Cash 9500.00. Re-materialize the ledger with ON CONFLICT (account_id), taking
   ledger.currency from accounts.currency (never a caller default — account 1400 is EUR). Assert trial
   balance SUM(balance) = 0.00 over 15 rows.
2. Roll the ledger up by type over the gl_accounts graph: graph_khop('gl_accounts', 4000, 5, 'in') JOIN
   ledger → revenue 173230.00; graph_khop(5000) → expense 129000.00; net income 44230.00.
3. Create income_statement (key period, section; amount DECIMAL(18,2)) and materialize 2026-09 with
   INSERT … SELECT … ON CONFLICT (period, section) DO UPDATE: revenue, expense, net_income.
4. Create balance_sheet (same shape) and materialize: assets 438230.00, liabilities 36000.00, equity
   358000.00, net_income 44230.00. Assert assets − (liabilities + equity + net_income) = 0.00 — the sheet ties.
5. Prove idempotency: a bare re-INSERT of ('2026-09','revenue') → 23505; the ON CONFLICT re-run keeps
   3 income_statement rows for the period, balance_sheet = 4 rows, net income 44230.00, tie 0.00.
6. State the scope in the output: both statements read `ledger` (all-time), so `period` is a LABEL on the
   snapshot, not a filter — it reads as a period P&L only because the close zeroes the P&L accounts.

Connect your tools

Both statements live in the same DataK3 bucket, reachable by your own stack — not just the CLI. A finance team pulls the P&L into Excel, a controller ties the balance sheet in a BI tool, an auditor runs the tie assertion from psql — all over the same wire, the same rows. data connect gl-suite prints the endpoints:

  • SQL over Postgres wire — psql, psycopg/asyncpg (Python), node-postgres (TS), any BI tool. The balance-sheet tie is one SELECT; the P&L is SELECT section, amount FROM income_statement.
  • Graph over Bolt — a Neo4j driver or cypher-shell against the gl_accounts account tree, to explore which posting accounts roll into any statement line.

Full, live-validated walkthrough: Connect your tools.

The suite — six components, one app

This package runs standalone — uvicorn routes:app, which is exactly what this post walks through, and what code/gl-financial-reporting/v1.tar still gives you. Deployed, the six GL components compose into one app: gl-suite-app is a single FastAPI with a router per component (that APIRouter in routes.py), 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 and reporting, the close and the posting engine are the same session. 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, or a distinct trust boundary — and the GL has none of those.

Conclusion

You now produce the two primary financial statements from one DataK3 bucket — an income statement (revenue 173230.00 − expense 129000.00 = net income 44230.00) and a balance sheet that ties to the cent (438230.00 − (36000.00 + 358000.00 + 44230.00) = 0.00) — both rolled up by account type over the gl_accounts graph, summed over the same ledger rows the GL already holds, and materialized idempotently with INSERT … ON CONFLICT. No reporting warehouse, no nightly ETL, no rollup mapping that drifts from the chart of accounts — the account record is the rollup node, and the sheet ties because double-entry guarantees it. One bucket, two pillars — SQL and graph — over one copy of the rows.

And you know the two things a reader of these statements deserves to be told: that period is a label on a snapshot of an all-time ledger rather than a filter, and that the tie is a necessary condition, never a sufficient one — a missing revaluation leaves the books balanced and the statements wrong. Both scope notes ship in the route docstrings, so they travel with the code rather than living only here.

Next steps:

  • gl/period-close rolls the current-period net income into Retained Earnings and locks the period — after which the balance sheet's net_income line folds into booked equity, and next period starts clean. It is also what makes the period label mean what it appears to mean.
  • gl/subledger-reconciliation ties the AP/AR control accounts on this balance sheet back to their subledgers — 2100 reconciles at 27000.00 with variance 0.00, while 1200 shows a -3500.00 break the balance sheet alone could never surface.
  • The GL is the deliberate stress test of the DataK3 pg wire for finance. This skill proves aggregate correctness at scale — the trial balance, the P&L, and a balance sheet that ties — over SQL and graph on one copy of the rows.