Balance sheet, P&L, trial balance and account ledger

Purpose

A workspace's Bilanz (balance sheet), Erfolgsrechnung (P&L), Probebilanz (trial balance) and one account's Kontoauszug (ledger) are computed live, on every request, as SUM(debit)/SUM(credit) aggregates over accounting_journal_entry_lines rows whose parent accounting_journal_entries.status = 'posted', signed by whether the account is debit-normal (asset, expense) or credit-normal (liability, equity, revenue) (report_service.py 41 to 105, 157 to 213, 215 to 311); nothing computed here is ever written back to a table, so there is no report-specific storage to keep in sync or go stale. The invariant that makes the output trustworthy is double-entry itself: because every posted line pairs an equal debit and credit at post time (enforced by DoubleEntryEngine.post_entry, a neighbouring recipe, out of scope here), summing the same posted rows two different ways - assets vs. liabilities+equity, total debits vs. total credits - always agrees, which is exactly what test_balance_sheet_aktiven_equals_passiven and test_trial_balance_debit_equals_credit assert end to end through the real HTTP route (test_voila_g12_endpoint_layer_...py 499 to 523).

When to use

Read it before touching ReportService.balance_sheet, .profit_loss, .trial_balance, .account_ledger, ._account_balances, ._get_fiscal_year, or the four routes under POST /api/accounting/reports/*; when someone asks "why don't Aktiven and Passiven match", "why is this account missing from the balance sheet but on the trial balance", "what's this account's running balance", or "why does the ledger's opening balance look wrong". Trigger phrases: "Bilanz", "Erfolgsrechnung", "Probebilanz", "Kontoauszug", "trial balance", "account ledger", "balance sheet", "P&L", "aktiven passiven".

Not this recipe: fiscal-year CRUD and closing (accounting_router.py 397 to 493), chart-of-accounts CRUD (accounting_router.py 494 to 529), journal-entry CRUD and the actual double-entry validation/posting/voiding engine that makes every line these reports read trustworthy in the first place (accounting_router.py 530 to 704; engine.py post_entry/void_entry 140 to 246), a neighbouring recipe on the same table; VAT rate lookup, VAT calculation and VAT report generate/submit (accounting_reports_router.py 57 to 94, vat_service.py), a separate concern that happens to be routed from the same file; Capital overview, partner CRUD and dividend withholding calculation (accounting_reports_router.py 99 to 134, capital_service.py), likewise routed alongside but functionally unrelated; the dashboard summary widget and its AI narrative (accounting_router.py /dashboard-data 67 to 207, /ai-insight 208 to 396), a different aggregation surface this recipe's four functions are never called from.

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on a sqlalchemy.orm.Session (SQLAlchemy==2.0.43), psycopg2-binary==2.9.10, pydantic==2.12.0 for the request body. Foundations assumed: _shared/fastapi-project-layout for the app graph and mount_router (the four routes are mounted at prefix="/api/accounting", app_fastapi.py 918); _shared/workspace-scoping for WorkspaceID, the dependency every report call is scoped by; _shared/error-envelope for the translation a raised HTTPException goes through (Security & tenancy); _shared/test-layout for the TestClient + transactional db_session pattern the endpoint tests run on.

No environment variable or config key is read anywhere in report_service.py or accounting_reports_router.py (grepped both files for os.environ/settings./config.: zero hits) - this recipe has no feature flag and no fail-closed-on-missing-config path, unlike a seam that talks to an external system. Fail behaviour: neither report_service.py nor the router catches anything; _get_fiscal_year (27 to 39) and account_ledger's own account lookup (225 to 235) raise HTTPException(404, ...) directly on a miss, and that propagates to FastAPI's registered HTTPException handler (Security & tenancy) rather than being caught and translated locally the way the auto-topup engine's public wrappers do - there is no "never raises" guarantee on this recipe's functions.

Data model

No table of its own - the whole point of this recipe (Purpose). It reads four tables it does not own, all in models/accounting_models.py, through the columns the report queries actually touch:

accounting_accounts (                              -- 103-134
  id               uuid PRIMARY KEY,
  workspace_id     uuid NOT NULL REFERENCES workspaces(id),
  account_number   varchar(10) NOT NULL,
  name_de          varchar(255) NOT NULL,
  name_en          varchar(255),
  account_type     accounttype NOT NULL,            -- asset | liability | equity | revenue | expense
  is_header        boolean DEFAULT false,            -- excluded from every report (_account_balances 52)
  deleted_at       timestamptz                        -- soft-deleted accounts excluded (_account_balances 51)
);
INDEX ix_accounting_accounts_ws_type (workspace_id, account_type)     -- backs _account_balances' filter (131)

accounting_fiscal_years (                           -- 136-155
  id, workspace_id, name, start_date, end_date, is_closed
);
CHECK ck_accounting_fiscal_years_dates: start_date < end_date

accounting_journal_entries (                        -- 158-193
  id, workspace_id, fiscal_year_id, entry_number, entry_date, description,
  status  journalentrystatus NOT NULL DEFAULT 'draft'  -- draft | posted | void; every report query filters = 'posted'
);
INDEX ix_journal_entries_ws_date   (workspace_id, entry_date)
INDEX ix_journal_entries_ws_status (workspace_id, status)

accounting_journal_entry_lines (                    -- 195-223
  id, journal_entry_id, account_id, line_number, description,
  debit_amount   numeric(15,2) NOT NULL DEFAULT 0.00,
  credit_amount  numeric(15,2) NOT NULL DEFAULT 0.00
);
CHECK ck_jel_amounts_non_negative: debit_amount >= 0 AND credit_amount >= 0
INDEX ix_jel_account (account_id)

What is NOT stored anywhere in this recipe: a computed balance, a rendered report, a PDF, a snapshot of a closed period - every call recomputes from the posted lines above. No function in report_service.py calls db.commit(), db.add() or db.flush() (grepped): these four entry points are read-only and cannot mutate the ledger they report on.

API surface

Route	Auth	Tenancy	Body	Codes
POST /reports/balance-sheet (accounting_reports_router.py 27 to 31)	ActiveUser	WorkspaceID	ReportRequest {fiscal_year_id: UUID, start_date?: date, end_date?: date, comparison_fiscal_year_id?: UUID} (accounting_schemas.py 618 to 622; comparison_fiscal_year_id is accepted but never read by report_service.balance_sheet)	200; 404 fiscal year not found or foreign workspace; 401/400 unauthenticated (Security & tenancy); 422 malformed body
POST /reports/profit-loss (34 to 38)	ActiveUser	WorkspaceID	same ReportRequest	same
POST /reports/trial-balance (41 to 45)	ActiveUser	WorkspaceID	same ReportRequest	same
POST /reports/account-ledger/{account_id} (48 to 52)	ActiveUser	WorkspaceID	account_id: UUID path param + ReportRequest body	200; 404 fiscal year OR account not found/foreign workspace; 401/400; 422

The four ReportService methods a rebuild reimplements, not routes but the actual surface:

Method	Raises	Returns
balance_sheet(db, workspace_id, fiscal_year_id, start_date=None, end_date=None) (107-130)	HTTPException(404) via _get_fiscal_year	{aktiven, passiven, total_aktiven, total_passiven, period}
profit_loss(...) (132-155)	same	{revenue, expenses, total_revenue, total_expenses, net_profit, period}
trial_balance(...) (157-213)	same	{accounts, total_debit, total_credit, period}
account_ledger(db, workspace_id, account_id, fiscal_year_id, ...) (215-311)	HTTPException(404) (fiscal year, or the account itself at 233-235)	{account, entries, opening_balance, closing_balance, period}

All monetary fields are returned as decimal strings (str(Decimal(...))), never a float.

Application logic

None of these four methods opens a transaction or commits; each is a sequence of SELECTs. In the order each method actually runs:

1. _get_fiscal_year (27 to 39) resolves the fiscal year filtered by both id and the caller's workspace_id in one query; a miss or a foreign-workspace id both raise HTTPException(404, "Fiscal year not found"), indistinguishable from each other by design (Security & tenancy).
2. The date window defaults to the fiscal year's own start_date/end_date but an explicit start_date/end_date on the request overrides either independently (sd = start_date or fy.start_date, repeated in all four methods) - nothing checks the override falls inside the fiscal year's own bounds, or that start_date <= end_date (Security & tenancy).
3. balance_sheet (107-130) calls the shared helper _account_balances twice, once for [AccountType.asset] (aktiven) and once for [AccountType.liability, AccountType.equity] combined (passiven), then sums each list's Decimal(balance) into total_aktiven/total_passiven.
4. profit_loss (132-155) calls _account_balances for [AccountType.revenue] and [AccountType.expense], net_profit = total_revenue - total_expenses.
5. _account_balances (41-105), the helper both reports share: loads active, non-header accounts of the requested types ordered by account_number (46-55); if none, returns [] early (57-58); otherwise runs ONE grouped aggregate - SUM(debit_amount), SUM(credit_amount) per account_id, joined to JournalEntry, filtered to the workspace, status == posted, and the date window (64-83) - then signs each account's balance inline (92-95: debit-normal = debit minus credit, credit-normal = credit minus debit) and drops any account whose balance is exactly zero (96) before sorting by account number (104). A zeroed-out account therefore never appears on the balance sheet or the P&L.
6. trial_balance (157-213) does not call _account_balances. It is a separately written query joining AccountingAccount to JournalEntryLine to JournalEntry, grouped by (account_number, name_de, account_type) (167-187), and unlike step 5 it keeps every matched account regardless of whether its net balance is zero - an account with equal debit and credit turnover in the window shows up on the trial balance (with balance: "0.00") but not on the balance sheet or P&L, for the exact same posted lines.
7. account_ledger (215-311): resolves the account by id + workspace_id (225-232), 404 if not found (233-235, "Account not found"); computes an opening balance as a separate aggregate over every posted line for that one account strictly before sd (238-258), signed by the same debit/credit-normal rule as step 5 (255-258); then loads every posted line for that account inside [sd, ed] ordered by (entry_date, entry_number) (260-280) and walks it, accumulating a running balance per row starting from opening_balance (282-298); returns the account, the per-line list, opening_balance and the final closing_balance.

Invariants

• Every reported number is an aggregate over status == 'posted' journal entry lines only: draft and void entries never reach a report, enforced independently by the JournalEntryStatus.posted filter present in all four query builders (report_service.py 75, 180, 247, 273; test_balance_sheet_aktiven_equals_passiven, test_trial_balance_debit_equals_credit post an invoice through /invoices/{id}/send first, precisely because a draft entry would not count).
• Aktiven equals Passiven, and trial-balance total debit equals total credit, purely as a consequence of double-entry posting - these four methods contain no reconciliation or balancing step of their own, they only regroup the same rows two ways (test_balance_sheet_aktiven_equals_passiven, test_trial_balance_debit_equals_credit, test_voila_g12_...py 499-523).
• A report is tenant-scoped by construction, not by an application-level check: _get_fiscal_year and account_ledger's account lookup both filter workspace_id in the SQL itself, so a foreign fiscal year or account id returns 404 rather than leaking another workspace's numbers (test_report_cross_tenant_fiscal_year_404, 566-572).
• Nothing here writes: grepped, zero calls to db.commit()/db.add()/db.flush() in report_service.py.
• Measured discrepancy, not invented: report_service.py imports DoubleEntryEngine as engine (line 18, from services.accounting.engine import engine) but never calls it - grep -n "engine\." over the file returns nothing. DoubleEntryEngine.get_account_balance (engine.py 250-297) is a near-identical single-account version of the exact debit/credit-normal balance math that _account_balances (92-95) and account_ledger (255-258, 287-290) each reimplement independently inline. The task that produced this recipe assumed the reports derive balances "via the shared DoubleEntryEngine account-balance calculation"; the measured code does not do that - it duplicates the calculation three times (_account_balances, trial_balance's own inline d - c, and account_ledger) instead of calling engine.get_account_balance once per account. This is a real gap between the intended design and the shipped code, not a limit of what could be determined.

Frontend

bookkeeping/ui/bookkeeping-financial-dashboard   the tab-switching report viewer this recipe's four
                                                  endpoints are the whole data source for; reference:
                                                  alchemy_labs/frontend/src/pages/accounting/Reports.tsx
                                                  (227 lines), calling accountingApi.balanceSheet /
                                                  .profitLoss / .trialBalance / .accountLedger
                                                  (frontend/src/services/accountingApi.ts 366-370)

Minimum admin/debug surface: none dedicated exists at this commit. A human chasing a mismatched total re-runs trial-balance for the same fiscal_year_id/window and compares its per-account rows against the balance sheet's, since (per the Application logic gap above) the two do not share a code path and a bug could in principle affect one and not the other; there is no log line or query these two files ship for this specifically.

Security & tenancy

Auth dependency: ActiveUser (get_current_active_user) on all four routes. Tenancy: WorkspaceID (_get_workspace_id, api/dependencies.py 680 onward - pre-validated from request.state.workspace_id when a middleware already resolved it, else the X-Workspace-ID header re-validated against the caller, else a single-workspace fallback, else a 409 telling the client to bootstrap a workspace). An unauthenticated call to any of the four routes is rejected as 401 or 400 depending on which sub-dependency FastAPI evaluates first - not asserted to be always one or the other, only that it is always one of the two (UNAUTH_REJECTED = (400, 401), test file 69-72; test_reports_require_auth 558-563 checks membership in that tuple, not an exact code).

Fail direction: closed on tenancy - _get_fiscal_year and account_ledger's account lookup both filter workspace_id in the query itself (Application logic), so a fiscal year or account id from another workspace 404s rather than ever returning that workspace's numbers, proven live through the real route by test_report_cross_tenant_fiscal_year_404 (566-572). Errors: a raised HTTPException(404, "...") is plain-string detail, which reaches the client through the app-wide @app.exception_handler(HTTPException) (app_fastapi.py 724-793): status 404 maps to ErrorCode.RESOURCE_NOT_FOUND, and because /api/accounting/... is not under the gateway prefix, the response is Shape B - {"error": "Fiscal year not found", "code": "RES_001", "request_id": "...", "timestamp": "...", "details": null} - not FastAPI's bare {"detail": "..."}.

Secrets and PII at rest: none - a report reads already-workspace-scoped rows and calls nothing external; no credential, token or PAN passes through this code. Rate limits: none dedicated to these four routes (grepped both files; whatever platform-wide limiter UnifiedMiddleware applies is uniform and out of this recipe's scope). Attacker-controlled inputs, all behind ActiveUser+WorkspaceID: fiscal_year_id/account_id (UUID-typed by Pydantic, tenancy-checked in SQL as above); start_date/end_date (dates, entirely unvalidated against each other or against the fiscal year's own bounds - genuinely not determinable from the code whether an inverted or out-of-year range is an intentional escape hatch or an oversight; no test in either file exercises one, and the query builders simply apply both bounds as plain WHERE clauses, so the practical effect is an empty or partial report, never an error).

Tests

Two files, run 2026-09-12 against alchemy_labs/backend/.venv (Python 3.13) at 62ba4fc.

tests/accounting/test_reports.py (9 def test_, hermetic, no database - its own docstring: "unit tests for the report service logic"):

python -m pytest tests/accounting/test_reports.py -q --no-cov -o addopts="" -p no:cacheprovider

prints 9 passed, measured directly in this session. These 9 tests do not call any ReportService method or hit the database: TestAccountTypeClassification (7 tests) asserts DEBIT_NORMAL_TYPES/CREDIT_NORMAL_TYPES classify every AccountType correctly and disjointly; TestBalanceSheetInvariant (2 tests) asserts plain-Decimal arithmetic identities (assets == liabilities + equity, sum(debits) == sum(credits)) on literal numbers, not on anything report_service.py computed. There is no unit test in this repo that calls balance_sheet/profit_loss/trial_balance/account_ledger directly and asserts on their return dict.

tests/v2/coverage/test_voila_g12_endpoint_layer_invoices_expenses_reports_vat_capital_qr_bill_auth_tenant_lifecycle_via_testclient_.py (748 lines total; 6 tests scoped to Reports, lines 499-572), a real-DB TestClient suite driving the actual route: test_balance_sheet_aktiven_equals_passiven (499-510, CRITICAL per the module docstring, the double-entry invariant), test_trial_balance_debit_equals_credit (513-523, also asserts the exact total 1081.00 for one sent invoice), test_profit_loss_reports_revenue (526-536, asserts total_revenue == net_profit == 1000.00 net of the invoice's 8.1% VAT line), test_account_ledger_for_debitoren (539-555, asserts closing_balance == 1081.00 and exactly one entry), test_reports_require_auth (558-563), test_report_cross_tenant_fiscal_year_404 (566-572). This file needs a migrated Postgres on the TEST_DATABASE_URL its conftest.py defaults to (postgresql://test_user:test_password@localhost:5434/test_db, docker-compose.test.yml's postgres-test service).

Genuine gap, stated rather than invented: I did not run this second file to completion in this session. The shared scratch Postgres on port 5434 (container deap-postgres-test, confirmed reachable) currently has zero tables - alembic upgrade head has not been run against it - and I chose not to run migrations against that shared container myself, since other agents were working in this environment concurrently at the time and a schema migration on shared infrastructure is outside the "touch nothing outside my one file" boundary this task set. What I can confirm statically: the six test names above exist at the cited line numbers, all six assert on the real HTTP route through report_service.py, and the file's own module docstring (1-41) states the same CRITICAL invariants this recipe's own Invariants section states. What I cannot confirm without a migrated database: that all six currently pass.

Deploy & ops

No config keys, no migration and no scheduler job belong to this recipe - it adds no table and reads tables owned by a neighbouring recipe (journal entries / chart of accounts, accounting_router.py), created by backend/migrations/versions/20260214_add_accounting_tables.py (revision 20260214_accounting_tables, down-revision 20260206_webhook_events). Grepped services/scheduled_jobs.py and every file under services/accounting/ for report_service/balance_sheet/profit_loss/trial_balance/account_ledger: no hits outside report_service.py and accounting_reports_router.py themselves - nothing schedules or pre-computes a report. Rollback: nothing to unwind; these four routes read, they never write, so rolling back the code changes nothing about stored data.

Gotchas

• 2026-09-12 (measured at 62ba4fc): report_service.py imports DoubleEntryEngine as engine (line 18) and never calls it. The task brief that produced this recipe describes the reports as derived "via the shared DoubleEntryEngine account-balance calculation" - that is not what the code does. _account_balances, trial_balance's own query, and account_ledger each reimplement the same debit/credit-normal signing (engine.py's DEBIT_NORMAL_TYPES/CREDIT_NORMAL_TYPES are imported and used correctly for classification, but the actual arithmetic engine.get_account_balance performs is duplicated, not called). A rebuild that "fixes" this by routing all four methods through engine.get_account_balance per account would change from one aggregate query per report to N queries (one per account), a real performance trade a rebuild should make deliberately, not by accident.
• 2026-09-12 (measured at 62ba4fc): _account_balances drops any account whose net balance is exactly zero (line 96); trial_balance's separate query does not. The same posted lines can therefore make an account appear on the trial balance and simultaneously be invisible on the balance sheet or P&L for the same period - this is current, shipped behavior, not asserted as intentional by any comment or test found.
• 2026-09-12 (measured at 62ba4fc): start_date/end_date on ReportRequest are unvalidated against each other or against the fiscal year's own bounds; see Security & tenancy. No test in either file exercises an inverted or out-of-year window.
• 2026-09-12 (measured at 62ba4fc): I was unable to run the six real-DB endpoint tests in this session because the shared scratch Postgres on :5434 has no schema applied, and running alembic upgrade head against shared infrastructure was outside this task's file boundary given concurrent agents. See Tests.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/services/accounting/report_service.py (314 lines, whole file in scope): _get_fiscal_year (27-39); _account_balances (41-105); balance_sheet (107-130); profit_loss (132-155); trial_balance (157-213); account_ledger (215-311).
• alchemy_labs/backend/api/routers/accounting_reports_router.py (134 lines): the four report routes (27-52); VAT and Capital routes (55-134, out of scope, cited in When to use).
• alchemy_labs/backend/services/accounting/engine.py (300 lines): DEBIT_NORMAL_TYPES/CREDIT_NORMAL_TYPES (24-25); DoubleEntryEngine.get_account_balance (250-297, imported but never called by this recipe, Gotchas).
• alchemy_labs/backend/models/accounting_models.py: AccountType (22-28); JournalEntryStatus (31-35); AccountingAccount (103-134); AccountingFiscalYear (136-155); JournalEntry (158-193); JournalEntryLine (195-223).
• alchemy_labs/backend/schemas/accounting_schemas.py: ReportRequest (618-622).
• alchemy_labs/backend/api/dependencies.py: ActiveUser (566); DatabaseSession (572); WorkspaceID (779); _get_workspace_id (680 onward).
• alchemy_labs/backend/app_fastapi.py: the router mount (918); the HTTPException handler (724-793).
• alchemy_labs/backend/frontend/src/pages/accounting/Reports.tsx (227 lines) and frontend/src/services/accountingApi.ts (366-370): the one real consumer, cited under Frontend.
• Tests: alchemy_labs/backend/tests/accounting/test_reports.py (56 lines, whole file); alchemy_labs/backend/tests/v2/coverage/test_voila_g12_endpoint_layer_invoices_expenses_reports_vat_capital_qr_bill_auth_tenant_lifecycle_via_testclient_.py (748 lines; Reports section 495-573).

Deviations of the reference from an ideal implementation, all dated 2026-09-12: the unused engine import and the resulting triplicated balance arithmetic (Gotchas); _account_balances' zero-balance drop is inconsistent with trial_balance's own query over the identical rows; no validation on start_date/end_date ordering or fiscal-year containment; no dedicated unit test calls any of the four ReportService methods directly, coverage of the actual arithmetic exists only through the six real-DB endpoint tests.

Acceptance checks

Run from alchemy_labs/backend at 62ba4fc41009b454e1c033e5036c7e8577db2061.

1. .venv/bin/python -m pytest tests/accounting/test_reports.py -q --no-cov -o addopts="" -p no:cacheprovider prints 9 passed - measured directly in this session, needs no database.
2. grep -c "def test_" tests/accounting/test_reports.py prints 9.
3. grep -n "^def test_balance_sheet_aktiven_equals_passiven\|^def test_trial_balance_debit_equals_credit\|^def test_profit_loss_reports_revenue\|^def test_account_ledger_for_debitoren\|^def test_reports_require_auth\|^def test_report_cross_tenant_fiscal_year_404" "tests/v2/coverage/test_voila_g12_endpoint_layer_invoices_expenses_reports_vat_capital_qr_bill_auth_tenant_lifecycle_via_testclient_.py" prints all six at the line numbers cited under Tests.
4. grep -n "engine\." services/accounting/report_service.py prints nothing (confirms the unused-import gap in Gotchas); grep -n "^from services.accounting.engine import engine" services/accounting/report_service.py prints line 18.
5. grep -c "db.commit()\|db.add(\|db.flush()" services/accounting/report_service.py prints 0 (confirms the read-only invariant).
6. Once the scratch Postgres on TEST_DATABASE_URL (default postgresql://test_user:test_password@localhost:5434/test_db) is migrated to head: TEST_DATABASE_URL=postgresql://test_user:test_password@localhost:5434/test_db .venv/bin/python -m pytest "tests/v2/coverage/test_voila_g12_endpoint_layer_invoices_expenses_reports_vat_capital_qr_bill_auth_tenant_lifecycle_via_testclient_.py" -k "balance_sheet or trial_balance or profit_loss or account_ledger or reports_require_auth or report_cross_tenant" -q --no-cov -o addopts="" -p no:cacheprovider is expected to print 6 passed based on static reading of the six tests; not executed to completion in this session (Tests, Gotchas).