What you'll build: the subledger-to-GL reconciliation control — the month-end tie-out that proves the detail behind a control account agrees with the ledger to the cent. An AP/AR subledger (subledger_items: the open bills and invoices, each tagged with the control_account_id it rolls into) is reconciled against its GL control account so the variance is provably 0.00, and the tie-out lands in a reconciliation snapshot the controller signs. It reads gl/core's ledger and accounts on one DataK3 bucket — and it's pure SQL: one cross-table JOIN, no model in the loop.

What you'll learn:

  • Model an AP/AR subledger as merge-keyed open detail — one row per open bill/invoice, tagged with its GL control account.
  • Tie the control account's signed ledger balance to the sum of its open subledger items, normalized to the account's natural side, so a credit-normal AP and a debit-normal AR reconcile through the same JOIN.
  • Recompute the tie-out idempotently — INSERT … ON CONFLICT (recon_id) DO UPDATE, so a nightly re-run never doubles a row — and prove a bare re-INSERT raises 23505 instead.
  • Detect a break the instant a bill or invoice lands in the subledger with no GL entry: the variance goes non-zero, reconciled flips to false, and the account is flagged with the exact amount it's off — down to the single document that caused 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 person who owns this control is the AP/AR accountant, and the person who signs off on it is the controller — and what they are guarding against has a name auditors use: an unrecorded liability. The general ledger carries a single Accounts Payable control account — one number, say 27,000.00 owed. Behind that number is the subledger: the itemized list of every open vendor bill that sums to it. The tie-out is the assertion that those two views agree. When they don't, it is rarely benign: a bill that sits in the subledger but was never posted to the GL means the balance sheet understates what the company owes — the classic way period-end liabilities get missed, and a finding that lands in the management letter. The same control runs the other way on the asset side, where the break this post finds lives: an AR invoice raised in the subledger and never journalled means the balance sheet understates what the company is owed, and the revenue behind it was never recognized.

The classic stack makes this tie-out a spreadsheet exercise. The subledger lives in the AP module, the control-account balance in the GL, and once a month someone exports both, VLOOKUPs them together, and eyeballs the difference — at yesterday's freshness, with money that may have been stored as a float somewhere upstream, so a tie that should be 0.00 reads -0.004 and nobody trusts it. Worse, the reconciliation is a point-in-time artifact: re-run it and you get a second copy, not an update.

On DataK3 the subledger and the control account are rows in the same bucket — the tie-out is a single JOIN over subledger_items and the ledger, read-your-writes, no export. Money is DECIMAL(18,2), so the sum of open items ties to the exact cent the ledger carries. And the recompute is idempotent: run it every close cycle and the same reconciliation row updates in place, the variance re-derives, and the instant a bill lands with no GL entry the account flags itself. It is a control you can audit, because the tie-out is SQL you can read.

PieceLands inPillar
Subledger open detail (bills/invoices behind the control account)subledger_items (merge-keyed)SQL
Tie-out snapshot (gl_balance vs subledger_sum → variance)reconciliation (merge-keyed on recon_id)SQL
The GL side (control-account balance)ledger / accounts (from gl/core)SQL

NOTE

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

This skill consumes ledger and accounts from gl/core. If core isn't installed yet, Step 1 stubs just the two masters the tie-out reads so this runs standalone.

Prerequisites

  • A DODIL organization with the dodil CLI authenticated (dodil auth login) or the DODIL MCP connected to your agent (Claude Code, Cursor, VS Code, Codex). Headless? Check auth_status first — an agent can't do the browser login for you.
  • export BUCKET=gl-suite — one bucket is the whole GL's data plane. (This is the bucket param, default gl-suite.)

Step 1 — Stub the masters you consume (standalone only)

The tie-out reads two gl/core masters: accounts (the control account's normal_balance — is it debit- or credit-normal?) and ledger (its signed running balance). If gl/core is already in your bucket, skip this step — those tables exist. Standalone, create minimal stubs (money DECIMAL(18,2), every non-key column nullable:true — data table create defaults columns to NOT NULL, and a partial seed would 500 with NotNullViolation), then seed the GL side the tie-out reconciles against: two control accounts, because a real close reconciles both sides of the house — Accounts Payable 2100 (credit-normal, ledger balance -27000.00) and Accounts Receivable 1200 (debit-normal, ledger balance 200000.00). These are the balances the rest of the GL suite posted into this bucket.

NOTE

The bucket is gl-suite, not gl. DataK3 requires a bucket name of at least 3 characters, so the obvious gl is rejected at creation. The whole suite — all six GL components — lives on gl-suite.

IMPORTANT

A credit-normal control account carries a negative ledger balance. ledger.balance is the signed net SUM(debit) - SUM(credit). AP is credit-normal, so 27,000.00 owed is stored as -27000.00; AR is debit-normal, so 200,000.00 receivable is stored as +200000.00. The tie-out normalizes each back to the account's natural side before comparing — that's why the recompute reads accounts.normal_balance instead of hard-coding a sign per subledger.

Every table-creation step below carries a third ORM tab — the exact class from the downloadable package's models.py (see Get the code), plain SQLAlchemy 2.0. Natural primary keys (account_id, item_id, a recon_id you assign — never SERIAL) map the ORM 1:1 to the CLI with no generated-PK round-trip, and money is Numeric(18,2) — a fixed-scale DECIMAL that SQLAlchemy hands back as a Python Decimal, so the tie-out arithmetic is exact to the cent (size the width to your GL; the CLI here uses DECIMAL(18,2)).

You

In bucket gl-suite, stub the two gl/core masters the subledger tie-out reads. Create accounts (key account_id: name, type, normal_balance, parent_account_id long, currency, active boolean) and ledger (key account_id: balance DECIMAL(18,2), currency, as_of timestamp). Make every non-key column nullable and use DECIMAL(18,2) for money. Then seed the two control accounts 2100 Accounts Payable (liability, normal_balance credit) and 1200 Accounts Receivable (asset, normal_balance debit), and the ledger balances a posted GL would carry: account 2100 balance -27000.00, account 1200 balance 200000.00, currency USD.

DODIL MCP tools called
data_bucket_create→data_table_create→data_table_upsert
Agent

Created bucket gl-suite and stubbed accounts (pk account_id) + ledger (pk account_id, balance DECIMAL(18,2)) with every optional column nullable. Seeded 2100 Accounts Payable (credit-normal) + 1200 Accounts Receivable (debit-normal), and ledger 2100 = -27000.00 (27000.00 owed, signed negative), 1200 = +200000.00. Note: the name gl is rejected — DataK3 requires at least 3 characters. Skip this step when gl/core owns accounts + ledger.

Step 2 — Load the subledger detail (subledger_items)

The subledger is the itemized detail behind the control account — one row per open bill or invoice, each tagged with the control_account_id it rolls into. Seed both sides:

  • AP, under control account 2100 — two open vendor bills, Acme Supplies 15,000.00 and Globex Parts 12,000.00, which sum to the 27,000.00 the GL carries; plus one cleared bill, Initech Ltd 45,000.00, paid off by journal 20260910 and therefore out of the open tie-out.
  • AR, under control account 1200 — four open customer invoices (Northwind Retail 30,000.00, Contoso Services 75,000.00, Fabrikam Inc 95,000.00, Adventure Works 3,500.00) and one cleared one (Northwind Retail 90,000.00, collected by journal 20260812).

item_id is the merge key, so re-loading the subledger is idempotent.

TIP

Only open items tie to the control account. A cleared bill has already been paid — its GL entry (a debit to AP, a credit to Cash) has landed, so it's out of the outstanding balance. The tie-out sums WHERE status = 'open'; the cleared row is detail you keep for the audit trail, not part of the balance.

You

In gl-suite, create a subledger_items table (key item_id: subledger, control_account_id long, party, amount DECIMAL(18,2), status, doc_date timestamp), every non-key column nullable, money DECIMAL(18,2). Then upsert three AP items under control account 2100 — open bills Acme Supplies 15000.00 and Globex Parts 12000.00, and cleared bill Initech Ltd 45000.00 — and five AR items under control account 1200: open invoices Northwind Retail 30000.00, Contoso Services 75000.00, Fabrikam Inc 95000.00 and Adventure Works 3500.00, plus cleared Northwind Retail 90000.00. Then show each control account's ledger balance next to its open subledger sum.

DODIL MCP tools called
data_table_create→data_table_upsert→data_sql
Agent

Created subledger_items (pk item_id, amount DECIMAL(18,2)) and upserted 8 rows. AP/2100: 2 open (15000.00 + 12000.00 = 27000.00) + 1 cleared (Initech 45000.00, excluded). AR/1200: 4 open (30000.00 + 75000.00 + 95000.00 + 3500.00 = 203500.00) + 1 cleared (Northwind 90000.00, excluded). AP ties to its -27000.00 ledger balance; AR's open detail is 3500.00 more than the GL's 200000.00.

Step 3 — Tie out the control account (variance = 0.00)

Now the tie-out itself. For each control account, normalize its signed ledger balance to its natural side (a credit-normal AP's -27000.00 becomes +27000.00 owed), sum its open subledger items, and take the difference. variance = gl_balance - subledger_sum; the account is reconciled when the absolute variance is within tolerance (0.00 by default — an exact tie). Materialize it into reconciliation with INSERT … SELECT … ON CONFLICT (recon_id) DO UPDATE — the idempotent path, so the nightly recompute updates the same snapshot instead of raising a duplicate-key error.

You

In gl-suite, tie out AP control account 2100. Compute gl_balance as the account's normal-balance-normalized ledger balance (credit-normal so negate the signed balance), subledger_sum as the sum of its open subledger_items, variance = gl_balance - subledger_sum, reconciled = absolute variance within 0.00. Upsert it into a reconciliation table keyed recon_id 'AP-2100' with INSERT ON CONFLICT (recon_id) DO UPDATE so it is idempotent. Then show the row.

DODIL MCP tools called
data_table_create→data_pg→data_sql
Agent

Reconciliation 'AP-2100' materialized: gl_balance 27000.00, subledger_sum 27000.00, variance 0.00, reconciled true. The control account ties to its open subledger to the exact cent — the two open bills (15000.00 + 12000.00) are exactly what the GL says is owed.

IMPORTANT

The tie-out reads normal_balance, not a hard-coded sign. Because gl_balance normalizes the signed ledger balance to the account's natural side, the same JOIN reconciles a credit-normal AP (2100) and a debit-normal AR (1200, where gl_balance = +ledger.balance). Point recon_id and the WHERE at another control account and the tie-out just works — no per-subledger branch. Step 4 does exactly that, and finds the break.

Step 4 — Detect a break, and name it (variance != 0.00)

A reconciliation is only worth running if it catches a break. Point the same recompute at the AR control account 1200 — only recon_id and the WHERE change — and it comes back red: the GL carries 200,000.00 receivable, the open subledger detail sums to 203,500.00, so variance = -3500.00 and reconciled flips false.

Read the sign. variance = gl_balance - subledger_sum, so a negative variance means the subledger holds more than the GL — something was invoiced and never posted. A positive variance is the mirror: the GL carries a balance the detail can't account for. Here it's negative, which points at exactly one thing, and the detail names it: item 9105, Adventure Works, 3,500.00, dated 2026-09-06 — an invoice that exists in the AR subledger and was never journalled into the general ledger. One counterparty, one amount, one missing journal. That is a break a controller can act on before the auditor writes it up.

You

In gl-suite, tie out AR control account 1200 with the same idempotent recompute, keyed recon_id AR-1200 — it is debit-normal so the signed ledger balance is used as-is. Then show the row, and list the open AR subledger items so I can see which one the GL is missing.

DODIL MCP tools called
data_pg→data_sql
Agent

AR-1200 materialized: gl_balance 200000.00, subledger_sum 203500.00, variance -3500.00, reconciled false. The subledger exceeds the GL by exactly 3500.00. The open AR detail is Northwind Retail 30000.00, Contoso Services 75000.00, Fabrikam Inc 95000.00 and Adventure Works 3500.00 — the first three trace to posted journals (20260710, 20260810, 20260905); Adventure Works item 9105 has no matching GL entry. That is the break.

Because the recompute is INSERT … ON CONFLICT (recon_id) DO UPDATE, re-running it is safe — the AR-1200 row updates in place, it never doubles. A bare re-INSERT of a committed recon_id, by contrast, is rejected — which is exactly why the recompute uses ON CONFLICT:

You

In gl-suite, prove the reconciliation recompute is idempotent: first try a bare INSERT of a second reconciliation row with recon_id AR-1200 to show it is rejected, then confirm the reconciliation table still has exactly one row for AR-1200 with variance -3500.00.

DODIL MCP tools called
data_pg→data_sql
Agent

The bare re-INSERT raised SQLSTATE 23505 (duplicate key recon_id 'AR-1200' already exists) — a plain INSERT is NOT an upsert. The ON CONFLICT recompute leaves reconciliation at exactly 1 row for AR-1200, variance -3500.00 unchanged — no double-row.

Routes

The download (see Get the code) fronts this bucket with a small FastAPI app, routes.py — CRUD over the masters and open detail, plus the one question a spreadsheet does by hand: does the subledger tie to its GL control account? This is what you deploy as the standing reconciliation engine. Every route follows the same DataK3 rules the package bakes in, 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. upsert() is the only writer every route uses:

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

Why it matters here: the tie-out is recomputed every close cycle, and on DataK3 a bare re-INSERT of an already-committed recon_id raises duplicate-key 23505 — a plain INSERT is not an upsert on re-write (proven live in Step 4). upsert makes the nightly recompute land the AP-2100 snapshot once, variance re-derived, never a second row.

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). Money is a Python Decimal (Numeric(18,2)), written over the pg wire — never the gRPC upsert path, which silently drops an integer 0 into a DECIMAL column:

# routes.py — CRUD over the models, keyed on the natural PK; money fields are Decimal
@router.post("/subledger-items")
def upsert_item(i: SubledgerItemIn, user: dict = Depends(current_user),
                s: Session = Depends(db)):
    # subledger_items is merge-keyed on item_id — upsert makes a re-load / replay land once.
    upsert(s, SubledgerItem, [i.model_dump()], key="item_id")
    s.commit()
    return {"ok": True, "item_id": i.item_id}

Two details worth naming. The routes hang off an APIRouter, not the FastAPI app directly — that is what lets the suite app mount all six components on one server (see The suite below); a module-level app = FastAPI(...) at the bottom of the file keeps this package independently runnable. And every route takes user: dict = Depends(current_user): a signed-in user is required everywhere, and no route here carries a permission gate. Why, in Auth below.

Workflow op 1 — the tie-out (POST /reconcile). This is the whole control. It reads the committed control account, its ledger balance, and the sum of its open subledger items, derives the tie-out in Decimal (exact — no float tail), and materializes the snapshot idempotently. gl_balance normalizes the signed ledger balance to the account's natural side, so the same function reconciles a credit-normal AP (gl_balance = -ledger.balance) and a debit-normal AR (gl_balance = +ledger.balance) with no per-subledger branch:

# routes.py — workflow op 1: tie out a control account (the reconciliation snapshot)
@router.post("/reconcile")
def reconcile(r: ReconcileIn, user: dict = Depends(current_user),
              s: Session = Depends(db)):
    acct = s.get(Account, r.control_account_id)
    led = s.get(Ledger, r.control_account_id)
    if not acct or not led:
        raise HTTPException(404, "no such control account (need accounts + ledger rows)")
 
    balance: Decimal = led.balance or Decimal("0.00")
    gl_balance = -balance if acct.normal_balance == "credit" else balance
 
    # only OPEN items tie to the outstanding GL balance; a cleared bill already hit the GL.
    subledger_sum: Decimal = s.execute(
        select(func.coalesce(func.sum(SubledgerItem.amount), Decimal("0.00")))
        .where(SubledgerItem.control_account_id == r.control_account_id)
        .where(SubledgerItem.status == "open")
    ).scalar_one()
 
    variance = gl_balance - subledger_sum
    reconciled = abs(variance) <= r.variance_tolerance
    recon_id = f"{r.subledger}-{r.control_account_id}"
 
    upsert(s, Reconciliation, [{
        "recon_id": recon_id, "control_account_id": r.control_account_id,
        "gl_balance": gl_balance, "subledger_sum": subledger_sum,
        "variance": variance, "reconciled": reconciled, "as_of": func.now(),
    }], key="recon_id")
    s.commit()
    return {"recon_id": recon_id, "gl_balance": float(gl_balance),
            "subledger_sum": float(subledger_sum), "variance": float(variance),
            "reconciled": reconciled}

reconcile reads the committed masters and open detail, then writes the snapshot in the same call — it never re-reads a row it wrote in that transaction. That's the DataK3 rule that bites GL engines that post then re-derive a balance: there is no read-your-writes inside an open transaction, so a handler that posts rows and then sums them must do the re-derive in a second txn after the first commits, and idempotently (SUM(...), not +=). This tie-out sidesteps it — it only ever reads already-committed rows.

Live-verified on the shared gl-suite bucket: POST /reconcile {"subledger":"AP", "control_account_id":2100} returns gl_balance 27000.00, subledger_sum 27000.00, variance 0.00, reconciled true; the same call with {"subledger":"AR","control_account_id":1200} returns gl_balance 200000.00, subledger_sum 203500.00, variance -3500.00, reconciled false — the exact amount off, with no per-subledger branch in between.

Workflow op 2 — the controller's break list (GET /breaks). Every control account that doesn't tie, worst variance first — one SELECT … WHERE reconciled = false:

# routes.py — workflow op 2: the controller's break list
@router.get("/breaks")
def breaks(user: dict = Depends(current_user), s: Session = Depends(db)):
    rows = s.execute(
        select(Reconciliation.recon_id, Reconciliation.gl_balance,
               Reconciliation.subledger_sum, Reconciliation.variance)
        .where(Reconciliation.reconciled == False)  # noqa: E712 — SQL boolean
        .order_by(func.abs(Reconciliation.variance).desc())
    ).all()
    return {"breaks": [{"recon_id": rid, "gl_balance": float(gb),
                        "subledger_sum": float(ss), "variance": float(v)}
                       for rid, gb, ss, v in rows]}

Adding a new close-cycle operation touches only routes.py (and maybe models.py) — the plumbing in db.py is fixed. The pattern is one Pydantic *In schema + one @router.<verb> function: money is Decimal, write via upsert, re-derive a balance in a second txn (see EXTENDING.md in the package).

One route carries a warning in its own docstring: POST /ledger is standalone-only. ledger is a derived table — gl/core re-derives it from journal_lines — so this route exists purely to let the package run on its own, without the posting engine, by setting a control account's balance by hand. In the composed suite it is not mounted: a hand-set balance would be silently overwritten by the next re-derive, and a reconciliation that ties against a hand-set number proves nothing.

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

gl-subledger-reconciliation carries zero permission gates, on purpose. Its routes.py imports current_user and not require_permission — the only component in the suite that does. It is also the only one that ships no posting.py, the suite's guarded journal write path, because it never writes a journal: reconciliation moves no money. It re-derives a comparison from rows that are already committed, idempotently, and records the answer. Re-running it changes nothing, so there is nothing to gate. The reader who needs that explanation is the auditor asking why the other three gates are there, and "this one guards a re-derive" is the answer that makes them credible. A gate that guards nothing teaches people to ignore the ones that do. The break list this component produces is what a controller acts on — and that action, an adjusting journal for the missing Adventure Works invoice, is gated where it belongs: in the posting path.

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-subledger-reconciliation/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only (no Dockerfile — deploy is the image-mode Ignite engine, k3.editor + ignite.app-developer, no ignite.model-user because the tie-out is pure SQL):

models.py          # SQLAlchemy — accounts, ledger (consumed) + subledger_items, reconciliation (owned)
routes.py          # FastAPI    — CRUD + /reconcile (the tie-out) + /breaks (the break list)
db.py              # the engine + the ON CONFLICT upsert helper every route uses
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  (no pyjwt)

No posting.py — the guarded journal write path the other five components share. This is the one component that never writes a journal.

PLATFORM.md is worth opening before you copy any of 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 rather than regenerate (db.py / sa_token.py / auth.py, COPY *.py, numeric USER 10001, secrets by reference) and the DataK3 semantics the code depends on. Every line in it is a scar from a real failure.

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

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
 
cp .env.example .env      # then set DODIL_TOKEN (your `dodil auth login` token)
# BUCKET defaults to "gl-suite"; create it once (Step 1), then create the tables from the models:
python -c "import db, models; models.Base.metadata.create_all(db.engine)"
 
# no gateway in front of uvicorn, so opt in to a stub identity for local dev:
export DEV_ALLOW_ANON=1
uvicorn routes:app --reload
# POST /reconcile · GET /breaks · GET /reconciliation/{id} · POST /subledger-items, /accounts, /ledger

The suite — six components, one app

This package runs standalone — uvicorn routes:app, which is what this post walks through, and what the /code/gl-subledger-reconciliation download still gives you. 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. Six components, one sign-in. 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 in front of a private engine, independent scaling, a distinct trust boundary — and the GL has none of those.

What one bucket found that six could not

Every component in this suite was built and validated on its own bucket, and every one of them passed. Then all six were run together on gl-suite, and that surfaced five integration bugs — every one of them invisible on separate buckets. The lesson is worth stating plainly, because it is not about the GL: a component validated alone proves only that it is self-consistent. Composition is a separate proof, and it needs one bucket. The stories are in gl/multi-currency (an FX revaluation that was silently non-idempotent while the books stayed perfectly balanced) and gl/journal-entry (five posting routes that disagreed about whether the ledger was current).

Reconciliation is the interesting counter-example: it was the one component whose answer was right standalone and right composed. That is not luck, and the reason is a rule you can apply to any component you write. It derives everything from already-committed rows, it writes no journal, and it stores only a result that a re-run reproduces exactly. The components that broke were the ones that wrote to shared state and did not re-derive it.

There is one honest caveat, and it is the sharper version of the same lesson. This component reads ledger. Four of the five bugs left ledger stale — so for a while, the tie-out faithfully reported a stale number. Correct code over wrong input still gives you a wrong answer, and no amount of care inside this component would have caught it. That is precisely why composition has to be proven on one bucket, and why POST /ledger is standalone-only: the moment a real posting engine owns the ledger, nothing else may set it by hand.

How the pillars map

One bucket, one bill, one auth context — the subledger detail and the control-account balance are rows in the same place, so the tie-out is a JOIN, not an export. What this reconciliation would otherwise be:

JobThe usual stackOn DataK3
Control-account balanceGL module (SaaS ERP)ledger.balance (signed net) in the bucket
Open subledger detailAP/AR module, a separate systemsubledger_items in the same bucket
"Does the subledger tie to the GL?"Monthly export + a VLOOKUP spreadsheetone JOIN — SUM(open items) vs the normalized ledger.balance, read-your-writes
Money that ties to the centCareful float handling, or it never quite tiesDECIMAL(18,2) — SUM() exact, no float tail
Re-run the tie-out without a messA second spreadsheet copy each monthINSERT … ON CONFLICT (recon_id) DO UPDATE — the snapshot updates in place
Point your own tools at itPer-system drivers & credsdata connect — psql / a BI tool, DB = bucket

No export, no second copy, no float that won't tie — because the subledger and the ledger are one bucket. This composes onto gl/core by consuming its ledger + accounts masters; the balances it reconciles are what gl/journal-entry posted.

Customize — the decisions this skill asks you

Q1 · control_accounts — which subledgers, and to which GL accounts?

"Which subledgers do you reconcile, and to which GL control accounts?" → Default AP → 2100, AR → 1200. Each subledger's open detail ties to one control account. The recompute reads each control account's normal_balance to normalize the signed ledger balance, so the same JOIN reconciles a credit-normal AP (gl_balance = -ledger.balance) and a debit-normal AR (gl_balance = +ledger.balance) — point recon_id and the WHERE at another control account and it just works, which is exactly what Steps 3 and 4 do with the same statement.

Q2 · variance_tolerance — exact tie, or a rounding tolerance?

"How close counts as reconciled — an exact tie, or a small rounding tolerance?"

  • 0.00 (default) → an exact tie: reconciled = (variance = 0.00). The finance-correct default — a control account that's even a cent off is flagged.
  • a positive DECIMAL(18,2) (e.g. 0.01) → auto-reconcile when the absolute variance is at most the tolerance, to absorb legitimate rounding across a multi-currency subledger. The variance is still recorded exactly; only the reconciled flag relaxes.

TIP

Industry overlays. This tie-out is the cross-industry base. Overlays add a small additive diff — finserv wraps the reconciliation in a regulated close (a reviewer sign-off on every non-zero variance, an audit trail on the recompute); manufacturing adds inventory/GR-IR control accounts (goods-received vs invoice-received clearing). See the per-industry GL pages.

Test

Every command below ran live against DataK3 on 2026-09-08 — bucket gl-suite, org IHDIASH, with all six GL components running together on that one bucket, not a per-component throwaway. The bucket is still up. Real results are inline. The default branch is {control_accounts: {AP: 2100, AR: 1200}, variance_tolerance: "0.00"}.

# 1) the seed — each control account's GL side against its open subledger sum
dodil data sql -b "$BUCKET" \
  "SELECT control_account_id, subledger,
          SUM(CASE WHEN status = 'open'    THEN amount ELSE 0.00 END) AS open_sum,
          SUM(CASE WHEN status = 'cleared' THEN amount ELSE 0.00 END) AS cleared_sum
     FROM subledger_items GROUP BY control_account_id, subledger ORDER BY control_account_id"
#  1200 AR  open_sum = 203500.00, cleared_sum = 90000.00   (ledger.balance =  200000.00)
#  2100 AP  open_sum =  27000.00, cleared_sum = 45000.00   (ledger.balance =  -27000.00)
 
# 2) AP ties to the cent — the clean tie-out
dodil data sql -b "$BUCKET" \
  "SELECT gl_balance, subledger_sum, variance, reconciled FROM reconciliation WHERE recon_id = 'AP-2100'"
#  gl_balance = 27000.00, subledger_sum = 27000.00, variance = 0.00, reconciled = true
 
# 3) AR does not — the break, with the exact amount
dodil data sql -b "$BUCKET" \
  "SELECT gl_balance, subledger_sum, variance, reconciled FROM reconciliation WHERE recon_id = 'AR-1200'"
#  gl_balance = 200000.00, subledger_sum = 203500.00, variance = -3500.00, reconciled = false
#  the break is item 9105 Adventure Works 3500.00 (2026-09-06) — invoiced, never journalled
 
# 4) idempotent recompute — a bare re-INSERT of recon_id 'AR-1200' raises SQLSTATE 23505; the
#     ON CONFLICT recompute leaves reconciliation at exactly 1 row, variance re-derived unchanged
#  recon_rows = 1, ar_variance = -3500.00
 
# 5) the books the tie-out reconciles against still balance, across all six components
dodil data sql -b "$BUCKET" \
  "SELECT (SELECT COUNT(*) FROM journals) AS journals,
          (SELECT COUNT(*) FROM ledger)   AS ledger_rows,
          (SELECT SUM(debit)  FROM journal_lines) AS total_debit,
          (SELECT SUM(credit) FROM journal_lines) AS total_credit,
          (SELECT SUM(balance) FROM ledger) AS trial_balance"
#  journals = 21, ledger_rows = 15, total_debit = total_credit = 1286470.00, trial_balance = 0.00
 
# 6) drop-in clients: same bucket, your own psql / BI tool
dodil data connect "$BUCKET"            # pg / bolt / grpc endpoints
#  pg   postgresql://token:…@pg.uk-lon-1.dodil.io:5432/gl-suite

One-shot

With the DODIL MCP connected, paste this to build + validate the whole subledger reconciliation at once:

Reconcile AP and AR subledgers to their GL control accounts on DataK3 (one bucket, pure SQL). Confirm
each step.
 
1. In bucket `gl-suite` (the name `gl` is rejected — DataK3 needs at least 3 characters), stub the gl/core
   masters this reads (skip if gl/core is installed): accounts (key account_id: name, type, normal_balance,
   parent_account_id long, currency, active) and ledger (key account_id: balance DECIMAL(18,2), currency,
   as_of timestamp) — every non-key column nullable, money DECIMAL(18,2). Seed account 2100 Accounts
   Payable (liability, credit) + 1200 Accounts Receivable (asset, debit), and ledger 2100 = -27000.00,
   1200 = 200000.00.
2. Create subledger_items (key item_id: subledger, control_account_id long, party, amount DECIMAL(18,2),
   status, doc_date). Upsert under control account 2100: open bills 9001 Acme Supplies 15000.00 and 9002
   Globex Parts 12000.00 (SUM open = 27000.00) + cleared 9003 Initech Ltd 45000.00 (excluded). Under
   control account 1200: open invoices 9101 Northwind Retail 30000.00, 9102 Contoso Services 75000.00,
   9103 Fabrikam Inc 95000.00, 9105 Adventure Works 3500.00 (SUM open = 203500.00) + cleared 9104
   Northwind Retail 90000.00 (excluded).
3. Create reconciliation (key recon_id: control_account_id long, gl_balance/subledger_sum/variance
   DECIMAL(18,2), reconciled boolean, as_of). Recompute AP-2100 idempotently with INSERT … ON CONFLICT
   (recon_id) DO UPDATE: gl_balance = normal-balance-normalized ledger balance (credit -> negate),
   subledger_sum = SUM(open items), variance = gl_balance - subledger_sum, reconciled = |variance| <= 0.00.
   Assert gl_balance 27000.00, subledger_sum 27000.00, variance 0.00, reconciled true.
4. Run the SAME statement for AR-1200 (debit-normal, so the signed balance is used as-is). Assert
   gl_balance 200000.00, subledger_sum 203500.00, variance -3500.00, reconciled false — the negative
   variance means the subledger holds more than the GL. Then list the open AR items and identify the break:
   item 9105 Adventure Works 3500.00, an invoice with no matching journal. Prove idempotency: a bare
   re-INSERT of recon_id 'AR-1200' raises 23505; the ON CONFLICT recompute keeps reconciliation at 1 row.

Connect your tools

Everything this build wrote lives in one DataK3 bucket, reachable by your own stack — not just the CLI. A finance team runs the month-end tie-out 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 tie-out is one INSERT … SELECT … ON CONFLICT you can schedule on your close-cycle cron; the break list is one SELECT … WHERE reconciled = false.

Full, live-validated walkthrough: Connect your tools.

Conclusion

You now have the subledger-to-GL reconciliation control on one DataK3 bucket: an AP/AR subledger of open detail, tied out against its GL control account through a single cross-table JOIN, materialized into a reconciliation snapshot. On the live books that is AP-2100 at variance 0.00, reconciled true — 27,000.00 of open bills against 27,000.00 of GL — and AR-1200 at variance -3500.00, reconciled false, naming one invoice (Adventure Works, 3,500.00) that was raised and never journalled. Money is DECIMAL(18,2) so the sum of open items ties to the cent; the recompute is INSERT … ON CONFLICT so the nightly tie-out re-runs without doubling a row. No monthly export, no VLOOKUP spreadsheet, no float that won't tie — one bucket, one JOIN, read-your-writes.

And one rule worth carrying to whatever you build next, which this component earned by being the only one in the suite that composed cleanly: derive from committed rows, write no shared state you don't re-derive, and store only results a re-run reproduces.

Next steps:

  • Compose the rest of the GL suite onto the same masters: gl/journal-entry (post the balanced journals whose balances you reconcile), gl/period-close (lock the period once every control account ties), gl/financial-reporting (the balance sheet that ties over the account graph), gl/multi-currency (FX revaluation with DECIMAL(18,6) rates).
  • The GL is the deliberate stress test of the DataK3 pg wire for finance — this skill proves the subledger ↔ GL tie-out row of that scorecard: a cross-table reconciliation whose variance is exact and whose recompute is idempotent.