What you'll build: the period close of a General Ledger — the month-end routine that takes a period from open to closed and immutable — on one DataK3 bucket, reading the journals, journal_lines, ledger, and accounts masters that gl/core owns. Three moves: a trial balance snapshot (trial_balance, keyed period + account_id) whose grand total must net to 0.00; a period lock (periods, keyed period_id) that makes a closed period reject any new operational journal; and a close to retained earnings — a balanced closing journal that zeroes every revenue and expense account and rolls net income into equity. It is pure SQL over the pg wire — no model, no second system — and every write is idempotent, so a re-run of the close never double-rolls.

What you'll learn:

  • Snapshot a trial balance with INSERT … SELECT … ON CONFLICT (period, account_id) DO UPDATE and assert the grand total is exactly 0.00 — the books tie to the cent, no float drift.
  • Make a period immutable with a periods lock row and a sole-writer posting engine — a closed period rejects a late operational journal 409, while the close's own closing entry still posts. And learn why it has to be an engine: DataK3 accepts a subquery CHECK and a FOREIGN KEY at DDL and then never enforces either, so a schema can look locked and not be.
  • Close to retained earnings — compute Σrevenue − Σexpense, post a balanced closing journal that zeroes the P&L, and watch equity grow by exactly net income.
  • Prove the close is idempotent — a bare re-INSERT raises 23505; the ON CONFLICT re-close leaves net income rolled once.
  • 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 the close is the controller, and the deadline is real money. Public companies file on a statutory clock; a close that slips, or a period that won't stay closed, is a material weakness an auditor writes up. The two failure modes are specific. First, a period that isn't truly locked: a late journal back-dated into a closed quarter silently changes a number the CFO already signed — and the fix for a signed-then-changed number has a name, a restatement. Second, a close routine that isn't idempotent: re-run it after a retry or a crash and it double-rolls net income into equity, overstating retained earnings by a full period's profit. Both are the kind of error that ends up in a filing, not a bug ticket.

So the close reads like a correctness proof. The trial balance must net to 0.00 before you're allowed to close — debits equal credits to the cent, which is only true if money never drifted (no floats). The period must lock — a closed period rejects new operational journals, and it has to reject them somewhere a stray script cannot walk around; that turns out to be a design decision with only one honest answer on DataK3, and it gets its own section after Step 3. And the close must be idempotent — net income rolls into retained earnings exactly once, no matter how many times the routine runs.

The classic stack does this with a batch job against the ERP, a lock flag nobody enforces at write time, and a warehouse the trial balance is ETL'd into overnight. On DataK3 the close is three SQL moves over the same journals and ledger rows the rest of the GL already holds — read-your-writes, no ETL, no second copy. The lock is a row the one posting engine checks; the trial balance is one SUM; the close is one balanced journal written ON CONFLICT. Pure SQL, no model in the loop.

PieceLands inPillar
Period register + lockperiods (merge-keyed on period_id)SQL
Trial-balance snapshottrial_balance (merge-keyed on period + account_id)SQL
Closing journal (net income → retained earnings)journals / journal_lines (from gl/core)SQL
Post-close balancesledger (from gl/core, re-materialized)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 close was built and validated by prompting an agent over this MCP.

This skill consumes accounts / journals / journal_lines / ledger from gl/core. If core isn't installed yet, Step 1 stubs just those masters — with a minimal balanced COA and a few posted journals — so this runs standalone.

Prerequisites

  • A DODIL organization with the dodil CLI authenticated (dodil auth login) or the DODIL MCP connected to your agent (Claude Code, Cursor, VS Code, Codex). Headless? Check auth_status first — an agent can't do the browser login for you.
  • export BUCKET=gl-suite — one bucket is the whole GL's data plane. (This is the bucket param, default gl-suite; the period to close is 2026-07, the period param; net income rolls into account 3200, the retained_earnings_account param.) The obvious name gl is not available — DataK3 rejects a bucket name under 3 characters — which is why all six GL components share gl-suite.

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

Period close reads four gl/core masters: accounts (to tell a revenue account from an expense one), journals + journal_lines (the postings), and ledger (the running balances). If gl/core is already in your bucket, skip this step — those tables and their data exist. Standalone, create the masters (every non-key column nullable:true — data table create defaults to NOT NULL, and a partial seed would 500 with NotNullViolation), seed the chart of accounts, then post the period's balanced journals so there is real net income to close: an opening-balance journal, a sale, its cost of goods, payroll, and a rent accrual.

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 a period's postings that drift breaks the trial-balance-nets-to-0.00 invariant the whole close rests on. Every amount column (debit, credit, ledger.balance, trial_balance.balance) is DECIMAL(18,2).

You

In a DataK3 bucket gl-suite, stub the gl/core masters period-close reads. Create four merge-keyed tables, every non-key column nullable, money as DECIMAL(18,2): accounts (key account_id: name, type, normal_balance, parent_account_id long, currency, active boolean); journals (key journal_id: journal_date timestamp, period, source, status, 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).

DODIL MCP tools called
data_bucket_create→data_table_create
Agent

Created bucket gl-suite and 4 masters — accounts (pk account_id), journals (pk journal_id), journal_lines (pk journal_id+line_no; debit/credit DECIMAL(18,2)), ledger (pk account_id; balance DECIMAL(18,2)). Every optional column nullable. Note: the name gl was rejected — a DataK3 bucket name needs at least 3 characters. Skip this step when gl/core owns the masters.

Seed the chart of accounts, then the five balanced journals that give 2026-07 its net income of 28000.00 (revenue 120000.00 − expenses 92000.00), and materialize the ledger idempotently. Journal ids are the posting date (YYYYMMDD) — a natural key you assign, so a re-import upserts instead of duplicating:

You

In gl-suite, seed a chart of accounts: 5 roots (1000 Total Assets, 2000 Total Liabilities, 3000 Total Equity, 4000 Total Revenue, 5000 Total Expenses) and 15 posting leaves — 1100 Cash, 1200 Accounts Receivable, 1300 Inventory, 1400 EUR Bank Account (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 Cost of Goods Sold, 5200 Salaries, 5300 Rent, 5400 Marketing — each with type and normal_balance, currency USD except 1400, active true. Then post five balanced journals in period 2026-07, all status posted: 20260701 opening balances (debit Cash 250000.00, Inventory 80000.00; credit Common Stock 330000.00); 20260710 product sale on credit (debit Accounts Receivable 120000.00; credit Product Revenue 120000.00); 20260711 cost of goods sold (debit COGS 45000.00; credit Inventory 45000.00); 20260715 July salaries (debit Salaries 38000.00; credit Cash 38000.00); 20260720 July rent accrual (debit Rent 9000.00; credit Accrued Expenses 9000.00). Then materialize the ledger from posted lines with ON CONFLICT (account_id).

DODIL MCP tools called
data_table_upsert→data_pg→data_sql
Agent

Seeded 20 accounts and 5 posted journals in 2026-07 (all balanced). Ledger materialized; the trial balance SUM(balance) = 0.00. Period net income (revenue 120000.00 − expense 92000.00) = 28000.00, ready to close.

NOTE

This workflow owns two tables, not the masters. Period close consumes accounts / journals / journal_lines / ledger from gl/core and adds only its own periods (the lock register) and trial_balance (the snapshot). One row, many readers, no second copy. Installed alongside gl/core, you skip the stub above — core owns the masters and their data.

Step 2 — Stand up the period register and snapshot the trial balance

The close's own two tables: periods is the lock register (period_id PK, status open/closed), and trial_balance is the per-account snapshot keyed on (period, account_id). Create them, mark the period open, then snapshot the trial balance — each account's signed net Σdebit − Σcredit for the period — with INSERT … SELECT … ON CONFLICT (period, account_id) DO UPDATE, the idempotent path (re-run the snapshot as many times as you like; a conflicting key updates in place instead of raising 23505).

You

In gl-suite, create two merge-keyed tables, money DECIMAL(18,2), every non-key column nullable: periods (key period_id: status, opened_at timestamp, closed_at timestamp, locked_by) and trial_balance (composite key period, account_id: debit DECIMAL(18,2), credit DECIMAL(18,2), balance DECIMAL(18,2), as_of timestamp). Seed period 2026-07 as status open. Then snapshot the trial balance for 2026-07 from posted journal_lines — per account SUM(debit), SUM(credit), SUM(debit) minus SUM(credit) as balance — with INSERT SELECT ON CONFLICT (period, account_id) DO UPDATE, and show that the grand total nets to 0.00.

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

Created periods (pk period_id) + trial_balance (pk period, account_id); seeded 2026-07 open. Snapshotted 9 account rows. Grand total: total_debit 542000.00, total_credit 542000.00, SUM(balance) = 0.00 — the books tie to the cent.

IMPORTANT

The trial balance is the go/no-go gate. If SUM(balance) is anything but 0.00, the books don't tie and the close stops — a non-zero total means a journal posted unbalanced, or money drifted. Because every amount is DECIMAL(18,2), the SUM() is exact: 542000.00 − 542000.00 = 0.00, no 0.0000000001 tail to chase.

Multi-currency note. This is a single-currency (functional) book, so SUM(ledger.balance) is the trial balance. In a book with foreign-currency balances, the canonical trial balance is the functional-currency one (each foreign balance converted at the period-end rate), and it only nets to 0.00 after gl/multi-currency has revalued — so run revaluation before you snapshot. See the GL suite for that seam, resolved live.

Step 3 — Lock the period (a closed period rejects new journals)

Closing a period locks it: no new operational journal may post into it. The lock itself is just a row — flip periods.status to closed. What makes it hold is that every journal in the GL, in all six components, is written by one function: posting.write_journal(), which reads that row before it writes anything. The check is nine lines, and it ships byte-identical in every package:

# posting.py — gate 2: the closed-period lock (the GL's sole write path runs this)
def assert_period_open(s, period: Optional[str], source: Optional[str]) -> None:
    """Reject an operational posting into a CLOSED period (409). `source='close'` is exempt —
    the closing journal must be able to post into the period it closes.
 
    A journal naming a period with no `periods` row is allowed: not every deployment opens
    periods up front, and there is no enforced FK that would have caught it anyway."""
    if not period or source == CLOSING_SOURCE:
        return
    status = s.execute(text("SELECT status FROM periods WHERE period_id = :p"),
                       {"p": period}).scalar()
    if status == "closed":
        raise HTTPException(409, f"period {period} is closed — operational posting rejected")

The one exemption is source = 'close': the closing entry Step 4 posts has to be able to land inside the period it is closing, or a period could never be closed at all.

You

In gl-suite, close period 2026-07: upsert periods 2026-07 to status closed, closed_at 2026-09-08, locked_by [email protected], and open 2026-08 so an accepted post has somewhere to land. Then show me the lock row the posting engine reads before every write, and confirm which periods are open.

DODIL MCP tools called
data_table_upsert→data_sql
Agent

Closed 2026-07 (locked_by [email protected]); 2026-08 and 2026-09 remain open. The posting engine reads periods.status on every journal write: an operational post into 2026-07 now returns 409 from all five posting routes, while source='close' is exempt. Probe journal 99990005 (source close, period 2026-07) landed; probe 99990003 (source manual, period 2026-09) landed in the open period.

Live on gl-suite, with 2026-07 closed: an operational journal aimed at it is rejected 409 period 2026-07 is closed — operational posting rejected from all five posting routes — core's, journal-entry's, period-close's own guarded post, multi-currency's, and financial-reporting's — because they all call the same function. The two probe journals are still in the bucket as evidence: 99990005 (source='close', period 2026-07) landed in the closed period, proving the exemption works, and the control 99990003 (source='manual', period 2026-09) landed in an open one. Both are header-only, so neither disturbs a single total.

TIP

Concurrency: retry on SerializationFailure. Two closers, or a closer racing a poster, run under serializable isolation over the wire — one may abort with 40001. Every write in the GL goes through the same bounded retry loop, so a serialization abort is retried, not lost:

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

Why the close lock is an engine, not a constraint

That design deserves its reasoning, because "the lock is application code" sounds like the weak answer and an auditor will ask why it isn't in the database. It isn't the weak answer. It is the only answer, and we know that because the alternatives were probed on DataK3 before a line of the close was written.

Start with what the rule actually says. "This period is closed" means: a journals row is illegal if and only if the matching periods row says closed. That is a claim about another table — and that turns out to be exactly the thing the tables engine cannot express.

Here is the whole enforcement surface, probed live on 2026-09-08:

Constraint formResult
PRIMARY KEY, including compositeEnforced. A re-INSERT of a committed key raises 23505.
CHECK with a literal predicateEnforced. CHECK (period <> '2026-08') really does reject the row.
CHECK with a subqueryAccepted at DDL — and never enforced. The row lands.
FOREIGN KEYAccepted at DDL — and never enforced. A journal may name a period that doesn't exist.
CREATE TRIGGER / CREATE RULERejected — not in the SQL surface.
CREATE VIEWRejected — so you cannot funnel writes through a guarded view.
REVOKE INSERT ON <table>Rejected — the direct-write privilege cannot be taken away.

WARNING

Two of those seven forms are worse than useless. A subquery CHECK and a FOREIGN KEY are both accepted at DDL time and then silently never enforced — no error, no warning, no rejected row. A schema can look locked, review as locked, and not be. That is worth carrying well beyond the GL: on DataK3, "I added a constraint" is not evidence of anything until you have watched it reject a write.

The only enforced form, CHECK with a literal, cannot see another table — so there is no database object on DataK3 today that can express a closed period. The lock has to be an application control. And the honest way to make an application control real is not to write it down in five places and hope: it is to leave exactly one code path that can write.

That path is posting.write_journal(). It runs the balance gate, then assert_period_open(), writes journals and journal_lines idempotently (INSERT … ON CONFLICT (pk) DO UPDATE), and appends the acting user to the append-only journal_audit. It is byte-identical across the five journal-writing packages and the suite app, and nothing else in the GL touches those two tables. One writer is a property you can actually audit — grep for the table name and count the call sites.

Now the part you say to the auditor out loud, rather than burying it in a footnote:

  • COVERED — every write through the GL's HTTP API, in every component and every workflow, because they all land in write_journal(), and every one is recorded in journal_audit with the gateway-vouched user who did it.
  • NOT COVERED — a principal holding k3.editor on the bucket writing journal_lines directly over psql, data pg or data_table_upsert. The database will accept it. That is verified, not hypothetical: a direct INSERT into the closed 2026-07 succeeds. The control against that is credential custody — in a deployed GL the app's service account should be the only identity holding bucket write rights — plus the audit trail, which will show a journal with no matching journal_audit event. It is not, and cannot be, the schema.

A control whose scope is stated is a control. A control whose scope is implied is a finding waiting to happen — and the failure mode of pretending the schema enforces this is that nobody ever checks who holds k3.editor.

Step 4 — Close to retained earnings (roll net income, zero the P&L)

The heart of the close. Net income is Σrevenue − Σexpense over the period's posted lines. Rolling it to equity is a balanced closing journal that zeroes every P&L account and books the difference to retained earnings: debit each revenue account (they're credit-normal, so a debit brings them to zero), credit each expense account (debit-normal, so a credit zeroes them), and credit retained_earnings_account (3200) by net income — a credit to a credit-normal equity account grows equity. The closing journal carries source = 'close', the one write the period lock lets through.

The closing journal id is derived, not allocated — close_id = int(period.replace("-", "")) * 100 + 900, so closing 2026-07 always produces journal 20261600. A deterministic id is what makes a re-close an upsert of the same rows instead of a second closing entry.

You

In gl-suite, compute net income for 2026-07 as Σrevenue − Σexpense over posted non-close lines (join accounts for type), then post a balanced closing journal 20261600 (source close, period 2026-07): credit Rent 5300 by 9000.00, debit Product Revenue 4100 by 120000.00, credit COGS 5100 by 45000.00, credit Salaries 5200 by 38000.00, and credit Retained Earnings 3200 by net income 28000.00. Use ON CONFLICT on the lines. Confirm the closing journal balances, then re-materialize the ledger with ON CONFLICT and show retained earnings and the trial balance.

DODIL MCP tools called
data_pg→data_sql
Agent

Net income = revenue 120000.00 − expense 92000.00 = 28000.00. Closing journal 20261600 posted (source close, which the period lock exempts): SUM(debit) = SUM(credit) = 120000.00, balance 0.00. After re-materializing the ledger: Retained Earnings 3200 = -28000.00 (grown by exactly net income), trial balance SUM(balance) = 0.00. Re-snapshotting 2026-07 now shows every revenue and expense account at 0.00.

Retained earnings moved from 0.00 to -28000.00 — a credit-normal account carries a negative signed balance, so that is equity grown by exactly the 28000.00 of net income. Every revenue and expense account in 2026-07 now nets to 0.00: the period's P&L is closed out, ready for the next month to start from zero. And the trial balance is still 0.00, because the closing journal was itself balanced.

IMPORTANT

The close zeroes the period's P&L — ledger is all-time. Read the proof off the period's trial_balance snapshot, as above, not off ledger. On the shared gl-suite bucket 2026-08 and 2026-09 had already been posted when 2026-07 was closed, so the close route's own response reported revenue_net -173230.00 and expense_net 138500.00 — not zeros — because those are the later periods' activity, with 2026-07's contribution correctly removed. retained_earnings -28000.00 and trial_balance 0.00 are the numbers that mean something at the ledger level. Standalone, with only 2026-07 in the bucket, the same call returns 0.00 for both nets. gl/financial-reporting hits the same all-time-ledger seam and says so plainly.

Step 5 — Prove the re-close is idempotent (no double-roll)

The correctness proof that matters most for a close: run it twice, roll net income once. A close that double-rolls overstates equity by a full period's profit. A bare re-INSERT of a committed closing line does not protect you — it raises a duplicate-key error; the ON CONFLICT re-close is what makes a re-run a no-op.

You

In gl-suite, prove the close is idempotent. First try a bare re-INSERT of closing line (20261600, 5) to show it is rejected. Then re-run the whole close: re-post the 5 closing lines with INSERT ON CONFLICT (journal_id, line_no) DO UPDATE, re-materialize the ledger with ON CONFLICT, and confirm the closing journal still has 5 lines, retained earnings is still -28000.00, and the trial balance is still 0.00 — net income rolled exactly once.

DODIL MCP tools called
data_pg→data_sql
Agent

The bare re-INSERT of (20261600,5) raised SQLSTATE 23505 (duplicate key) — a re-INSERT is NOT an upsert. The ON CONFLICT re-close + ledger re-materialize left close_lines=5, close_journals=1, retained_earnings=-28000.00, trial_balance=0.00 — no double-roll.

Two things make that safe, and both are properties of the engine, not of the SQL you happen to type. The closing journal's id is derived from the period, so a re-close addresses the same five rows rather than allocating a sixth; and close_period refuses outright — 409 period 2026-07 is already closed — unless the period is explicitly reopened first, so the ordinary way to run the close twice is simply blocked.

Routes

The download (see Get the code) fronts this bucket with a small FastAPI app, routes.py — CRUD over the models plus the three moves the close is. This is what you deploy. Every route obeys the same finance / pg-wire rules the steps above proved live, so quoting it is documenting them.

The connection and the one write helper live in db.py (shared byte-identical with the rest of the suite). A DataK3 bucket is a Postgres endpoint — db name = the bucket, user = the literal token, password = your DODIL token — so there's no data connect step in code, just fixed region constants. upsert() is the only keyed writer, INSERT … ON CONFLICT DO UPDATE; a bare re-INSERT of a committed PK raises 23505, so this is what makes a re-close land each row once. Money moves over the pg wire (SQLAlchemy/psycopg), never the gRPC upsert path — every amount is Numeric(18,2), exact to the cent.

The guarded post — the period lock, enforced by being the only writer. POST /journals doesn't implement the lock; it delegates to posting.write_journal(), which every posting route in every GL component calls. That is the whole enforcement story, and it is why the route body is this short:

# routes.py — the guarded posting path (the lock lives in posting.write_journal)
@router.post("/journals")
def post_journal(j: JournalIn,
                 user: dict = Depends(require_permission("gl:journal:post")),
                 s: Session = Depends(db)):
    """Post a journal THROUGH THE GUARD. An operational journal (source <> 'close') into a
    CLOSED period is rejected 409 — and closed-period immutability holds only because EVERY
    posting route in the GL, in every component, routes through the same
    posting.write_journal(). See posting.py for what that does and does not cover."""
    lines = [{"journal_id": j.journal_id, **l.model_dump()} for l in j.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"])
    # 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 j.lines], as_of=posting.now())
    return {"posted": True, "journal_id": j.journal_id, "period": j.period,
            "lines": len(j.lines)}

That comment is not decoration — it is a scar, and it is the reason this module was re-validated with all six components on one bucket rather than six. Doing so surfaced five integration bugs, none of which was reachable while each component had a bucket to itself: a component validated alone proves only that it is self-consistent, and self-consistency is not composition. The two worst are told where they happened — a revaluation that balanced the books perfectly while being silently wrong, in gl/multi-currency, and the posting routes that disagreed about whether ledger was current, in gl/journal-entry.

Live-verified on gl-suite: an operational journal aimed at the closed 2026-07 returns 409 period 2026-07 is closed — operational posting rejected, from this route and from the four other posting routes in the suite; the closing entry (source='close') posts into the same period unimpeded; and a post into the open 2026-08 lands normally.

The trial-balance snapshot — the go/no-go gate. POST /periods/{period}/trial-balance aggregates the posted lines per account with INSERT … SELECT … ON CONFLICT (period, account_id) DO UPDATE (idempotent), then returns whether the grand total ties:

# routes.py — snapshot + THE assertion: SUM(balance) must net to exactly 0.00
posting.retry(lambda: (s.execute(_SNAPSHOT_TB, {"period": period, "as_of": posting.now()}),
                       s.commit()))
row = s.execute(text(
    "SELECT SUM(debit) AS td, SUM(credit) AS tc, SUM(balance) AS grand_total, "
    "       COUNT(*) AS accounts "
    "FROM trial_balance WHERE period = :period"), {"period": period}).mappings().one()
ties = (row["grand_total"] or ZERO) == ZERO      # 0.00 exact, no float tail — the books tie

Live on gl-suite for 2026-07: total_debit = total_credit = 542000.00, grand_total = 0.00, 9 accounts — ties = true. It is current_user only, with no permission gate: a snapshot is an idempotent re-derive from already-committed rows, so it changes nothing a gate could protect.

The close — roll net income, in three transactions. POST /periods/{period}/close computes net income = Σrevenue − Σexpense over the period's posted non-close lines, posts a balanced closing journal (source='close', which the lock exempts) that debits every revenue account and credits every expense account — zeroing the P&L — credits retained_earnings_account by net income, then sets the lock. The critical shape: it commits the closing lines, then re-derives the ledger in a SECOND transaction — DataK3 has no read-your-writes inside an open txn, so the balance re-derive (a SUM, not a +=) must run after the lines commit, which is also what makes N concurrent re-closes land the amount once:

# routes.py — close_period, gated on gl:period:close (the only route that freezes a month)
@router.post("/periods/{period}/close")
def close_period(period: str, body: CloseIn,
                 user: dict = Depends(require_permission("gl:period:close")),
                 s: Session = Depends(db)):
    # txn 1: the closing journal, through the SAME sole writer every posting uses
    posting.write_journal(s, header, lines, actor=user["sub"], audit_event="closed")
    # txn 2: set the lock. The periods row IS the lock; assert_period_open reads it.
    locked_by = body.locked_by or user.get("email") or user["sub"]
    posting.retry(lambda: (upsert(s, Period, [{
        "period_id": period, "status": "closed", "opened_at": p.opened_at,
        "closed_at": as_of, "locked_by": locked_by}], key="period_id"), s.commit()))
    # txn 3: re-derive the ledger from ALL posted lines — a SEPARATE txn is mandatory
    #        (no in-txn read-your-writes); SUM(...) not += makes re-closes idempotent.
    posting.rederive_ledger(as_of=as_of, currency="USD")

Order matters, and it is deliberate: the closing journal is posted before the lock is set. The exemption means the reverse order would also work — but posting first means a close that fails halfway leaves the period open and re-runnable rather than locked with no closing entry. Fail open, not stuck. Note locked_by defaults to the signed-in user's email: on the live run that is where [email protected] came from, and the matching journal_audit row reads closed:dev-controller. Neither is typed in by the caller — the sole writer stamps whoever the gateway vouched for.

Live on gl-suite: net income 28000.00 (revenue 120000.00 − expense 92000.00); closing journal 20261600 balanced (120000.00 = 120000.00); retained earnings -28000.00, trial balance 0.00. Re-running the close returns 409 already closed; forcing the same rows through again leaves close_lines at 5 and retained earnings at -28000.00 — net income rolled exactly once.

Adding a new op touches only routes.py — one Pydantic *In schema + one @router.<verb> function; journals via posting.write_journal(…), other writes via upsert(…) inside posting.retry(…) then s.commit(), and re-derive any balance in a second txn (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

gl-period-close is where gl:period:close lands: POST /periods/…/close carries it, and the guarded POST /journals carries gl:journal:post. POST /periods (open a period), POST /periods/…/trial-balance (snapshot it) and the GETs are current_user only — opening a period and snapshotting a trial balance are idempotent re-derives from committed rows, and a permission that guards nothing teaches an auditor to discount the ones that do.

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.

That identity is not just a gate, either: it is what ends up in the books. close_period stamps periods.locked_by from the signed-in user ([email protected] on the live run) and write_journal appends closed:<sub> to journal_audit. The bucket credential is the app's service account, so without the pool the only identity on record for every posting in the company would be one robot.

Get the code

The package is a real download — code/gl-period-close/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only (no Dockerfile — deploy is an image-mode Ignite app, covered below):

models.py          # SQLAlchemy — accounts, journals, journal_lines, ledger (gl/core) + periods, trial_balance
routes.py          # FastAPI    — CRUD + the guarded post + trial-balance snapshot + close-to-retained-earnings
posting.py         # THE guarded journal write path: balance gate + closed-period lock   (SHARED, byte-identical)
db.py              # the engine + the ON CONFLICT upsert helper every route uses         (SHARED, byte-identical)
auth.py            # gateway header-trust identity — the app ships NO auth code          (SHARED, byte-identical)
sa_token.py        # mints/refreshes the service-account token = the pg-wire password    (SHARED)
PLATFORM.md        # the platform invariants you COPY, not generate — identical in every DODIL package
.env.example       # BUCKET + PG_HOST/PG_PORT + DODIL_TOKEN (+ DEV_ALLOW_ANON for a laptop)
requirements.txt   # sqlalchemy, psycopg[binary], fastapi, uvicorn, pydantic, httpx

Note what is not in that list: no JWKS client, no issuer/audience config, and no pyjwt — the gateway owns authentication, so the dependency went with it.

Run it — point .env at your bucket, create the two owned 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), and
                          # DEV_ALLOW_ANON=1 + DEV_USER_PERMISSIONS for a laptop with no gateway
# 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)"
 
uvicorn routes:app --reload
# POST /accounts · POST /periods · POST /journals (guarded) ·
# POST /periods/{period}/trial-balance · POST /periods/{period}/close

models.Base.metadata.create_all is the ORM tab's every class at once — the same tables Steps 1–2 built by CLI, created from the natural-key models with no migration tool. Deploy is the same image-mode Ignite app the GL suite standardizes on — a plain HTTP server behind a Dockerfile, its service account granted k3.editor + ignite.app-developer (pure SQL, no ignite.model-user).

How the pillars map

One bucket, one bill, one auth context — the period lock is a row the posting path reads, the trial balance is one SUM over the same ledger the postings write, and the close is one balanced journal — all over one copy of the rows. What this close would otherwise be:

JobThe usual stackOn DataK3
"Do the books tie?" (trial balance)A reporting warehouse, ETL'd nightlyone SUM(balance) over trial_balance — read-your-writes, no ETL
Make a closed period immutableA flag nobody enforces at write timea periods lock row + one write path (posting.write_journal) that reads it — with the scope of the control stated, not implied
Roll net income to retained earningsA batch job against the ERPone balanced closing journal, INSERT … ON CONFLICT
Re-run the close safelyApp-level dedupe + a lock fileON 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(), 0.00 to the cent

No ETL, no second copy, and no lock flag that every writer has to remember — because there is only one writer. This composes onto gl/core by consuming its four masters and adding just periods + trial_balance.

The suite — six components, one app

This package runs standalone — uvicorn routes:app, which is exactly what this post walks through, and what the /code/gl-period-close download 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 they all share — plain imports, no importlib loader — over one bucket (gl-suite) and one dodil-appid pool, so a controller signs in once for the whole ledger. It is one app rather than six because the ERP default is one app with a router per module, and you split only for a stated reason — a public surface versus a private engine, independent scaling, a distinct trust boundary. The GL has none of those; it has the opposite, a lock that only works because every component calls the same function. Shipping it is the ordinary git cycle (repo → CI → registry → CD): Ship a DODIL app.

Customize — the decisions this skill asks you

Q1 · period — which period to close?

"Which accounting period do you want to trial-balance and close (YYYY-MM)?" → 2026-07 (default). The trial-balance snapshot aggregates this period's posted lines, the lock closes this period_id, and the closing journal rolls this period's net income — into the deterministic journal id int(period.replace("-", "")) * 100 + 900, so 2026-07 closes as 20261600. Every number below re-derives from it; point it at another month and the whole close follows.

Q2 · retained_earnings_account — where net income lands

"Which equity account does net income roll into at close?" → 3200 (Retained Earnings, default). The closing journal credits this credit-normal equity account by net income (Σrevenue − Σexpense). Point it at a different equity account and the roll target moves; the P&L accounts still zero out the same way.

Q3 · functional_currency — the reporting currency

"What functional (reporting) currency is the ledger kept in? (inherited from gl/core)" → USD (default). The trial-balance snapshot and every closing-journal amount are in this currency, inherited from gl/core so the close never re-declares it. gl/multi-currency revalues foreign balances into it before the close.

TIP

Industry overlays. This close is the cross-industry base. finserv adds a regulated close — a segregation-of-duties sign-off (the person who prepares the close journal can't be the one who approves the lock) and an audit trail on every posting; manufacturing adds cost-accounting close steps (variance accounts rolled at period end) — see the per-industry GL pages.

Test

Every command below ran live against DataK3 on 2026-09-08 against bucket gl-suite (org IHDIASH) — with all six GL components running together on that one bucket, not on a throwaway bucket of its own. That is the point: the close has to hold while five other components are writing the same journals and ledger rows. The bucket is still up. Real results are inline; the default branch is {period: 2026-07, retained_earnings_account: 3200, functional_currency: USD}.

# 1) the pre-close trial balance ties — grand total nets to 0.00
dodil data sql -b "$BUCKET" \
  "SELECT SUM(debit) AS d, SUM(credit) AS c, SUM(balance) AS grand_total, COUNT(*) AS n
     FROM trial_balance WHERE period = '2026-07'"
#  d = 542000.00, c = 542000.00, grand_total = 0.00, n = 9
 
# 2) the period is locked, by a real signed-in user
dodil data sql -b "$BUCKET" \
  "SELECT period_id, status, locked_by FROM periods ORDER BY period_id"
#  2026-07  closed  [email protected]
#  2026-08  open
#  2026-09  open
 
# 3) the lock holds at the write path: an operational post into 2026-07 is rejected 409 from ALL FIVE
#    posting routes; source='close' is exempt (probe journal 99990005 landed in the closed period),
#    and the control (99990003, source manual) landed in the open 2026-09.
dodil data sql -b "$BUCKET" \
  "SELECT journal_id, period, source FROM journals WHERE journal_id IN (99990003, 99990005)"
#  99990003  2026-09  manual   <- control, open period
#  99990005  2026-07  close    <- the exemption, into the CLOSED period
 
# 4) net income = Σrevenue − Σexpense
#  total_revenue = 120000.00, total_expense = 92000.00, net_income = 28000.00
 
# 5) the closing journal 20261600 balances
dodil data sql -b "$BUCKET" \
  "SELECT SUM(debit) AS d, SUM(credit) AS c, SUM(debit)-SUM(credit) AS balance
     FROM journal_lines WHERE journal_id = 20261600"
#  d = 120000.00, c = 120000.00, balance = 0.00
 
# 6) after close — the PERIOD's P&L zeroed, retained earnings grew by net income
dodil data sql -b "$BUCKET" \
  "SELECT account_id, balance FROM trial_balance WHERE period='2026-07' ORDER BY account_id"
#  10 rows; 4100/5100/5200/5300 all 0.00; 3200 = -28000.00; SUM(balance) = 0.00
#  (ledger is all-time, so its revenue/expense nets are the LATER periods' activity — see Step 4)
 
# 7) the whole bucket still ties, with all six components' journals in it
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 d,
          (SELECT SUM(credit) FROM journal_lines) AS c,
          (SELECT SUM(balance) FROM ledger) AS trial_balance"
#  journals = 21, ledger_rows = 15, d = c = 1286470.00, trial_balance = 0.00
 
# 8) idempotent re-close — a bare re-INSERT 23505s; a re-close returns 409 already closed, and
#    forcing the same rows through ON CONFLICT changes nothing
#  close_lines = 5, close_journals = 1, retained_earnings = -28000.00, trial_balance = 0.00
 
# 9) 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 run the whole close at once (assumes gl/core's masters are in the bucket — otherwise stub them first, per Step 1):

Close accounting period 2026-07 on the DataK3 bucket gl-suite. Confirm each step.
 
1. Create periods (key period_id: status, opened_at, closed_at, locked_by) and trial_balance (composite key
   period, account_id: debit/credit/balance DECIMAL(18,2), as_of). Seed 2026-07 as open, 2026-08 as open.
2. Snapshot the trial balance for 2026-07 from posted journal_lines (per account SUM(debit), SUM(credit),
   SUM(debit)-SUM(credit)) with INSERT SELECT ON CONFLICT (period, account_id) DO UPDATE. Assert
   SUM(debit) = SUM(credit) = 542000.00 over 9 accounts and SUM(balance) = 0.00 (the grand total ties).
   Stop the close if it is non-zero.
3. Lock 2026-07: upsert status=closed, [email protected]. The lock is enforced by the
   sole write path (posting.write_journal -> assert_period_open), NOT by a table constraint: on DataK3 a
   subquery CHECK and a FOREIGN KEY are accepted at DDL and never enforced, and TRIGGER/VIEW/REVOKE are
   rejected outright. Prove it: an operational post into 2026-07 is rejected 409; source='close' is exempt;
   a post into open 2026-08 lands. Note the documented limit — a direct psql INSERT bypasses it.
4. Compute net income = Σrevenue − Σexpense (join accounts for type) = 28000.00 (revenue 120000.00, expense
   92000.00). Post balanced closing journal 20261600 (source close, id = period digits * 100 + 900): debit
   revenue accounts, credit expense accounts, credit Retained Earnings 3200 by net income. Lines via
   ON CONFLICT (journal_id, line_no). Re-derive the ledger in a SECOND txn, taking currency from
   accounts.currency. Assert the closing journal balances (120000.00 = 120000.00), retained earnings
   = -28000.00, trial balance = 0.00, and the re-snapshotted 2026-07 shows every P&L account at 0.00.
5. Prove idempotency: a bare re-INSERT of a closing line 23505s; re-closing returns 409 already closed; the
   ON CONFLICT re-close keeps close_lines = 5, retained earnings -28000.00, trial balance 0.00 — net income
   rolled once.

Connect your tools

Everything this close wrote lives in one DataK3 bucket, reachable by your own stack — not just the CLI. A finance team runs the close 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 trial_balance WHERE period = '2026-07'; the close is one balanced journal INSERT … ON CONFLICT — wrap it in a SerializationFailure retry loop (Step 3) for concurrency safety.

One caveat worth repeating here, because this is the section that hands people a direct connection: a client on the pg wire is the uncovered path. psql holding k3.editor can write journal_lines into a closed period, and the database will accept it. Read the whole ledger from here freely; route every write through the API so it passes the balance gate, the period lock and the audit trail.

Full, live-validated walkthrough: Connect your tools.

Conclusion

You now have the period close of a General Ledger on one DataK3 bucket: a trial-balance snapshot that proves the books tie to 0.00 (542000.00 on both sides for 2026-07, over 9 accounts), a periods lock that makes a closed period reject a late journal 409 at the one write path that exists, and a close to retained earnings that rolled 28000.00 of net income into equity with a balanced closing journal — all pure SQL over gl/core's masters, with money as DECIMAL(18,2) so it never drifts and INSERT … ON CONFLICT so a re-close never double-rolls. No batch job, no reporting warehouse and the ETL between them. One bucket, one bill, one copy of the rows.

And one thing you can say to an auditor without crossing your fingers: the lock is an application control enforced by being the sole writer, it covers every write through the API with the acting user recorded, and it does not cover a credential writing the table directly — which is why credential custody is part of the control, not an afterthought. That is a better answer than "there's a constraint on it", and on DataK3 it is the only true one.

Next steps:

  • Compose the rest of the GL suite onto the same masters: gl/financial-reporting (P&L + a balance sheet that ties over the account graph — it reads the very trial_balance this close snapshots), gl/subledger-reconciliation (AP/AR control-account tie-out), gl/multi-currency (FX revaluation with DECIMAL(18,6) rates, revalued before the close).
  • The GL is the deliberate stress test of the DataK3 pg wire for finance. This close proves three of its hardest invariants live: money exactness (0.00 to the cent), period immutability (a closed period rejects new postings), and idempotent aggregate correctness (net income rolls exactly once).