LedgerSMB (Perl + PostgreSQL) migrated to ClickHouse by EKOS Migrate, modelled in dbt, orchestrated in Python, and reconciled to the cent against LedgerSMB's own reports. Every AI interaction is logged, with tokens per model.
← → / space to navigate · O overview · T theme
The goal, the source system, the synthetic company, the tools and the rules of evidence.
Building LedgerSMB faithfully, generating data through its own procedures, EKOS knowledge and discovery.
EKOS Migrate stage by stage: drift, PII, findings, type mapping, risk, approvals, load, validation.
dbt layers and models, conventions, EKOS-sourced documentation.
P&L, balance sheet, cash, working capital, aging, products, customers, inventory, suppliers.
Four levels of data quality, the independent oracle, EKOS vs Claude Code vs other LLMs, tokens, findings.
Take a real ERP's database, move it into an analytical engine, and build reporting on it, so that every step can be checked rather than trusted.
An open-source double-entry accounting ERP. Most of its business logic lives in the database, in PL/pgSQL.
| Area | Tables |
|---|---|
| General ledger | transactions · acc_trans (journal lines) · gl · account · account_heading · account_link |
| Receivables / payables | ar · ap · open_item · payment · entity_credit_account · company |
| Materials | parts · partsgroup · invoice (lines) · inventory_report[_line] |
| Orders | oe · orderitems · oe_class |
LedgerSMB ships no data, so 18 months (2025-01 → 2026-06) of a fictional industrial-components distributor were simulated. Deterministic: the same seed produces the same books.
The first generated company ended $1.34M overdrawn: over-stocking with no financing. Credible to a computer, not to a finance reader. Reorder quantities were tightened and a revolving facility added (decision D8).
| Actor | Role |
|---|---|
| EKOS — compiled knowledge | Compiled the LedgerSMB repository (SQL, Perl, JS, docs, git) into a queryable, evidence-backed model; answered the design questions; supplied source documentation |
| EKOS Migrate (RFC 0154–0162) | The whole PostgreSQL → ClickHouse move: discover, drift, profile, assess, map, risk + approval, load, validate, report |
| Claude Code (Opus 5.5) | All engineering: loader, generator, orchestration, dbt project, pipelines, docs, deck; found and fixed 16 EKOS defects |
| DeepSeek V4 Flash (cloud) | EKOS's configured LLM: semantic naming during compile; ekos ask answers |
| Llama 3 8B (local, Ollama) | The same questions, answered locally, for comparison |
| dbt + ClickHouse | 38 models, 124 tests; the analytical engine |
All LLM traffic goes through a logging proxy (tools/llm_proxy.py): prompts, answers and provider-reported tokens in logs/llm_calls.jsonl.
ClickHouse reads PostgreSQL through a server-side named collection; generated SQL only names it.
Config names environment variables; a DSN carrying a password is refused.
Docker Compose from the EKOS repo, local-only passwords.
One command, from an empty database: python -m lsmb_pipelines.run. Each step must succeed before the next;
a failing data-quality test stops the run.
Source: logs/pipeline_runs.jsonl (last run)
The schema is built from LedgerSMB's own sql/ tree, in LedgerSMB's order. The first attempt failed on 8 files; each failure was
a way the loader differed from LedgerSMB's installer.
| Failure | Cause | Fix (= what LedgerSMB does) |
|---|---|---|
drop_arap_cols.sql: column does not exist | Listed twice in LOADORDER | Skip content already applied (LedgerSMB's db_patches hash) |
Roles.sql: syntax error at ":" | psql variable lsmb_schema unset | Pass -v lsmb_schema=public |
no-inv-entity-tables.sql + 4 cascades | TEMP … ON COMMIT DROP under autocommit | One transaction per change |
Duplicates_Functions.sql | \copy of a relative path | Run from the LedgerSMB root |
entity_credit_account: language FK | Seed data never loaded | Load initial-data.xml after the base schema, before changes |
Result: 1 base + 175 changes + 52 modules + seed, 0 failures (logs/schema_load.tsv).
Not an imitation of LedgerSMB — LedgerSMB itself. The generator calls its procedures wherever one exists.
account_heading_save, account__save — the real US chart (66 accounts)company__save, eca__save, eca__location_save — 85 counterpartiescogs__add_for_ar_line — FIFO COGS, posts Dr COGS / Cr Inventory itselfcogs__add_for_ap_line — purchase allocationpayment_post — receipts and payments settle open itemsekos build → recover → resolve → compile → commit over the whole checkout: SQL, Perl, JavaScript, Markdown, git history.
resolve (Table gl vs Perl LedgerSMB::GL). --force continues and merges nothing; an open EKOS finding.305 structural queries through EKOS's MCP tools (0 errors), every one logged with its purpose and full answer.
| Question | EKOS tool | Answer |
|---|---|---|
| Where are the finance/materials tables? | ekos_ekl | 24 of 24 located, 185 in total |
What are the columns of acc_trans, and what do they mean? | ekos_state | Columns + COMMENT ON text, cited to sql/Pg-database.sql:1142 |
What joins to acc_trans? | ekos_neighborhood | FKs and referencing code |
What depends on parts? | ekos_dependents / ekos_impact | 27 compiled dependents (becomes the risk gate) |
| Where is COGS / trial balance / open item logic? | ekos_search | COGS feature tests, xt/42-cogs-fifo.pg, modules |
EKOS also supplied the dbt source documentation: 46 of 303 deployed columns carry LedgerSMB's own comments, cited to file and line.
ekos ask: cloud vs local modelThe same six questions, answered by EKOS's grounded Q&A on DeepSeek V4 Flash (cloud) and Llama 3 8B (local). Answers shortened.
| Question | Cloud | Local |
|---|---|---|
| How does LedgerSMB compute the cost of goods sold for a sales invoice line? | LedgerSMB computes cost of goods sold using the FIFO (First In, First Out) method: the first inventory in the system that is unallocated is the first inventory used for costs. This behavior is described in connection with the `Led | LedgerSMB computes the cost of goods sold for a sales invoice line by considering the inventory activity screens, parts groups, price groups, and FIFO or First In, First Out method. |
| Which table stores general ledger journal lines, and how are debits and credits represented? | The table is `journal_line` (also referenced as `public.journal_line`); it replaces `acc_trans` as the main account transaction line table. The evidence does not describe how debits and credits are represented in that table. | The table that stores general ledger journal lines is `public.journal_line`. Debits and credits are represented in this table. |
| What is an open item in LedgerSMB and how are AR invoices settled by payments? | An open item in LedgerSMB is a tracking mechanism for items to be cleared or handled in subsequent transactions, as described in the open-item-tracking SQL change. The evidence does not describe how AR invoices are settled by pay | An open item in LedgerSMB is an item that has not been cleared or settled, allowing for tracking of items to be handled in subsequent transactions. AR invoices are settled by payments through the LedgerSMB::Report::Invoices::Paym |
| What does the inventory_report table record and how is a count variance posted? | Insufficient evidence. I looked for claims describing what the inventory_report table records and how a count variance is posted, but the available claims only contain partial term matches and no descriptive content about either. | The inventory_report table records the variance between budgeted and actual amounts for a given period, as well as other relevant information such as report dates. A count variance is posted by calculating the difference between |
| How does LedgerSMB distinguish customers from vendors in entity_credit_account? | LedgerSMB's `entity_credit_account` table stores the general relationship for moneys owed on invoices, covering both AR and AP sides, and the claims show it has a column named `public.entity_credit_account.entity_class`. That colu | LedgerSMB distinguishes customers from vendors in the entity_credit_account by using the entity_class attribute, which specifies whether the credit account is for a customer or vendor. |
| Which database objects depend on the parts table? | Insufficient evidence. I looked for any claim naming database objects or a dependency on a "parts table"; the provided claims only describe a Perl subroutine named `database` in `xt/lib/PageObject/Setup/CredsSection.pm`. | The parts table depends on the database object. |
Verdict: cautious. It refused ("insufficient evidence") rather than invented, which is the right failure mode. But it was thin: structural MCP tools answered the same questions better, and every rule the model relies on was confirmed in LedgerSMB's source.
| Rule | Established by |
|---|---|
acc_trans.amount_bc: negative = debit, positive = credit | LedgerSMB sql/modules/trial_balance.sql |
| COGS is FIFO, posted by the database, lines tagged with the sales-invoice line | EKOS ask + COGS.sql + measured (9,322/9,322 tagged) |
| Invoice qty: positive on sales, negative on purchases | COGS.sql |
AR/AP lines carry open_item_id; payments settle open items | trigger_open_item_maintenance, payment_post |
Customer vs vendor = entity_class 2 / 1 | EKOS ask (column) + measured |
| Ids are identity columns since 1.12 | measured + changes/1.12/migrate_to_identity.sql |
tax.validto = infinity means "no end date" | measured + EKOS finding |
A migration proof system, not a SQL converter. Every scope decision, finding, mapping, approval and validation result is a ledger fact with provenance, and the report cites those facts or does not ship.
Types from profiled data; findings with the SQL that measured them.
Approvals cannot be made through MCP; the requester cannot approve their own request.
Three tiers plus planted controls; a green without a fired control proves nothing.
| Kind | Count | Kind | Count |
|---|---|---|---|
| primary keys | 158 | indexes | 62 |
| unique constraints | 66 | sequences | 86 |
| check constraints | 72 | triggers | 29 |
| views | 16 | extensions | 2 |
The first attempt died on unknown pg_constraint.contype "t": LedgerSMB uses a constraint trigger that EKOS's fixtures never had. Fixed (defect #1).
EKOS compares its compiled view of the repository with the live catalog. 316 findings, 298 of which change the migration.
public.acc_trans.amount — in the repository's DDL but not deployedpublic.acc_trans.amount_bc — deployed but absent from the repository's DDLPredicted during discovery (EKOS's ekos_state listed amount) and confirmed here. Part of the drift is real history. Part is an EKOS limitation: it compiles the base CREATE TABLE but does not replay the 175 ALTER scripts (open finding).
P0 catalog only · P1 bounded sample · P2 exact and budgeted. The demo profiles at P1.
28 columns classified as personal data: no bounds and no top-k are ever recorded for them, at any tier.
tax as 0 rows.TABLESAMPLE made the DDL differ run to run, and approvals pinned to its hash could never match (defect #8). Now seeded, and small tables are read whole.Data-quality and ClickHouse-compatibility rules, each measured against real rows with the SQL kept as evidence.
| Finding | What it means |
|---|---|
BLOCK COMPAT.CH.INFINITE_TIMESTAMP — tax.validto, 1 row | PostgreSQL infinity, which no ClickHouse type holds. Predicted here; the load later failed on exactly that row. |
| BLOCK COMPAT.CH.NO_CONSTRAINT_ENFORCEMENT | ClickHouse will not enforce PKs/FKs: duplicates would accumulate silently |
BLOCK COMPAT.CH.NUMERIC_UNCONSTRAINED — amount_bc | Unconstrained numeric; the measured scale is 22 (LedgerSMB's payment_post stores unrounded products) |
noise DQ.UNIQ.001 on acc_trans.trans_id | Flags FK columns as "looks like a key": open EKOS finding |
| 28 inferred-FK candidates | From real code joins + pg_stat_statements; wrong attributions stayed "not measurable", never claimed |
-- source: public.account
-- engine: no updates or deletes recorded against the source, so the table is append-only
-- order by: no query-shape evidence available for this table, so this is the primary key
-- codecs chosen from the profile, NOT emitted: the RFC 0160 classifier cannot parse a CODEC clause
CREATE TABLE `lsmb_raw`.`acc_trans` (
`trans_id` Int32, `chart_id` Int32, `transdate` Date32,
`source` LowCardinality(Nullable(String)), -- was Nullable(LowCardinality(…)): rejected (defect #7)
`amount_bc` Decimal128(22), -- scale measured, not guessed
`amount_tc` Decimal64(2), `curr` String, `open_item_id` Nullable(Int32), …
) ENGINE = MergeTree ORDER BY (`entry_id`);
DDL for 158 tables: clickhouse/ddl/ekos_generated_raw.sql.
EKOS computes risk from statement class × environment × lossiness × blast radius (compiled dependents) × rows. Above R1 a load needs an approval pinned to the artifact hashes and an evidence snapshot.
| Unit | Computed | Requester tried to approve | Load |
|---|---|---|---|
account | R3 | refused | 10.96 s |
acc_trans | R3 | refused | 11.17 s |
transactions | R3 | refused | 11.03 s |
parts | R3 | refused | 10.84 s |
entity | R3 | refused | 10.84 s |
entity_credit_account | R3 | refused | 10.87 s |
country | R3 | refused | 10.85 s |
location | R3 | refused | 10.82 s |
Three governance defects were found here and fixed: #4 the requester could approve their own request by typing a prefixed name; #15 blast radius matched tables by suffix, so review said R1 while load said R3; #16 load accepted an approval granted at a lower class.
Every statement is parsed and classified before it runs; unparseable, unknown or credential-carrying statements are refused.
INSERT INTO `lsmb_raw`.`acc_trans` SELECT * FROM postgresql(lsmb_source, schema = 'public', table = 'acc_trans') WHERE `entry_id` >= 1 AND `entry_id` < 100001
Bounded, half-open key-range chunks.
One whole-table statement, stated as unbounded. account is keyed by text, which broke the old planner (defect #2).
Loaded by Python with the recorded decision infinity → NULL; reconciled 1/1.
| Tier | Compares | Result |
|---|---|---|
| V1 | row counts | 30/30 |
| V2 | per column: nulls, min, max, total byte length of the canonical form | 30/30 |
| V3 | engine-independent row hashes, summed per bucket | 30/30 |
Before defect #9 was fixed, unconstrained numeric was hashed at scale 0: V3 could not see cents at all, and "passed". The planted control is how you know a green is real.
Each one only shows up on real data: non-ASCII text, empty tables, all-NULL columns, unconstrained numerics.
| # | PostgreSQL side | ClickHouse side | Fix |
|---|---|---|---|
| 9 | to_char(604.74, scale 0) → 605 | truncates → 604 | scale from the deployed type; never 0 |
| 10 | NULL bool → 'f' | NULL → \N | decide NULL on the column, both sides |
| 11 | length() = characters | length() = bytes | octet_length |
| 12 | empty min/max = NULL | type default ''/0 | aggregate_functions_null_for_empty |
| 13 | SQL NULL → '' | raw TSV NULL = sentinel bytes | format_tsv_null_representation |
Verified against real PostgreSQL 16 and ClickHouse 24.8 with EKOS's live three-way tests (12 pass).
The migration report is compiled from 2,217 ledger facts with groundedness 1.000. It refuses sign-off, and it is right to.
| Blocker | Why |
|---|---|
| Units not validated in the state machine | The lifecycle needs assessed → … → validated; units with blocking findings cannot advance until dispositions exist, and load/validate swallow the illegal-transition error (open EKOS finding) |
| Unexplained divergences | No approved dispositions yet (the disposition workflow is not built) |
| Planted control missed | The one-cent control was run by hand, not through ekos migrate validate |
The data is proven elsewhere (V1–V3, the planted control, the reconciliation). The report correctly refuses to sign what its own workflow has not recorded.
1:1 with sources: renamed, typed, cents-exact; debit/credit split from LedgerSMB's signed amount so nothing downstream re-derives it.
Journal lines + accounts + natural sign; sales lines + exact FIFO COGS (via the invoice-line tag); open-item balances as of a date.
Facts, dimensions and report-shaped marts for finance (11) and materials (11).
Session join_use_nulls = 1 gives standard outer joins; account numbers are dbt vars; aliases never shadow aggregated columns (a ClickHouse trap hit twice).
fct_journal_lines, dim_account, dim_counterparty, dim_datemart_trial_balance_monthly: account × month, zero rows includedmart_income_statement_monthly, mart_balance_sheet_monthlymart_ar_aging, mart_ap_agingmart_cash_flow_monthly (direct method), mart_working_capital_monthlyfct_sales_lines, fct_purchase_lines, dim_partmart_sales_by_part_group_monthly, mart_customer_profitabilitymart_inventory_movements_monthly, mart_inventory_positionmart_stock_count_variance, mart_order_backlogmart_abc_analysis, mart_supplier_spend_monthlydbt source docs are generated from EKOS's compiled knowledge: LedgerSMB's own COMMENT ON text, with file and line.
- name: acc_trans
description: "This table stores line items for financial transactions. Please note that payments
in 1.3 are not full-fledged transactions. [EKOS: defined in sql/Pg-database.sql]"
columns:
- name: "source"
description: "Document Source identifier for individual line items, usually used for
payments. [EKOS: COMMENT ON COLUMN] ClickHouse type LowCardinality(Nullable(String))."
Source: logs/kpis.json — each figure is a query on the marts (queries in docs/confluence/05-kpis.md)
Revenue and FIFO cost of goods sold by month, net income as the line. Seasonality and growth are visible, and so is Q1.
mart_income_statement_monthly
Assets = liabilities + equity at every month-end: a dbt test, and independently LedgerSMB's
report__balance_sheet agrees account by account.
No year-end close in the ledger, so earnings to date are an explicit equity line (as LedgerSMB's own report does).
Classified by the other side of each bank transaction. Financing includes share capital and the revolving facility.
The subledger ties to the AR control account (dbt test) and to the open-item ledger in PostgreSQL (371/371 items, exact).
Services carry no COGS (100% margin by construction); raw materials run thin; hydraulics is the volume business.
A = the parts making up the first 80% of revenue. A handful of parts carry the business, a standard Pareto shape.
Movements (receipts − sales + count adjustments) reproduce LedgerSMB's on-hand for every part (dbt test).
Variance value ties to the inventory-adjustment postings (dbt test).
On the real data every check passes. To prove the checks are real, a defect was planted at each level, the check was shown to catch it, and the defect was removed: a check that has only ever passed proves nothing.
| Level | What | On the real data | Planted defect → caught | After removing it |
|---|---|---|---|---|
| Migration | EKOS V1 / V2 / V3 | 30/30 pass | +0.01 on one journal line, ClickHouse only → V3 failed (bucket 43, exit 1); V1/V2 blind by design | pass |
| Structure | dbt generic: unique, not-null, relationships, accepted values, non-negative, row count vs source | 114/114 pass | account 1200 duplicated in lsmb_raw.account → 2 uniqueness tests failed, 70 downstream nodes skipped | 162/162 |
| Accounting | 10 invariants: balances, trial balance, balance sheet, subledgers, revenue, COGS, inventory, shrinkage | 10/10 pass | +10.00 on a sales line → 2 invariants failed, 24 downstream skipped | 162/162 |
| Oracle | marts vs LedgerSMB's own reports in PostgreSQL | 6/6 exact | stale target (PostgreSQL regenerated, ClickHouse not reloaded) → 6/6 failed | 6/6 |
The duplicate was removed with OPTIMIZE … FINAL DEDUPLICATE (drops only identical rows) and the table re-validated V1–V3 against PostgreSQL, so the revert is exact. Logs: logs/dbt_planted_defect*.log, logs/negative_checks/.
dbt tests check the model against itself. This checks it against LedgerSMB's own reporting functions, run in PostgreSQL.
| Check | LedgerSMB oracle | Compared | Result |
|---|---|---|---|
| trial balance | trial_balance__generate() | 66 | exact |
| income statement (accrual) | pnl__income_statement_accrual() | 10 | exact |
| balance sheet | report__balance_sheet(…, 'ultimo') | 19 | exact |
| AR open items | acc_trans on the AR control account | 371 | exact |
| stock on hand | parts.onhand | 108 | exact |
| raw row counts | count(*) per table | 31 | exact |
Two conventions had to be matched rather than "fixed": heading subtotal rows in the statements, and raw ledger sign on the balance sheet. LedgerSMB's own aging reports fail on this 1.14 development schema, an upstream finding.
| Model | Calls | Succeeded | Input | Output | Total |
|---|---|---|---|---|---|
| qwen2.5:1.5b (ollama) | 1 | 1 | 35 | 3 | 38 |
| deepseek-v4-flash (openai) | 63 | 28 | 21,688 | 102,117 | 123,805 |
| llama3:latest (ollama) | 6 | 6 | 7,287 | 670 | 7,957 |
The first 32 cloud calls failed with HTTP 403 (the proxy sent a Python-urllib User-Agent) and consumed no tokens. DeepSeek's hidden reasoning tokens count as output.
Measured from the session's context counter at each phase. It is context consumed in the conversation, not a billing figure, which the session does not expose.
| Phase | What | Remaining |
|---|---|---|
| start | counter when the demo request arrived (session context budget; an ESTIMATE of Claude Code usage, not a billing figure) | 15,000,000 |
| 0-scaffold | plan + repo scaffold + environment discovery | 14,985,472 |
| 2-ekos-rebuild | schema loader (4 fixes) + EKOS config/proxy + LedgerSMB rebuild + schema/procedure research | 14,919,734 |
| 3-data | discovery + generator (5 fixes found by checking against LedgerSMB) + integrity verification | 14,868,014 |
| 5-validated | migration: 9 EKOS defects found+fixed (pg-live, load, policy, approval, evidence, typemap, sampling, canonical scale, NULL/length/empty-set, exit code) | 14,730,494 |
| 6-pipelines | dbt project (38 models/124 tests) + python pipelines + realism fix; starting full end-to-end run | 14,648,042 |
| 7-docs | confluence pages, report/query-log generators, 50-slide deck, analyst SQL, DDL dump; 2 more EKOS governance defects fixed | 14,524,750 |
| 8-final | final release run (8 R3 gates, 30/30, 6/6, 162/162) + regeneration | 14,509,816 |
| 9-complete | final docs/report/deck regeneration and commit | 14,500,123 |
305 structural queries, 0 errors: exact locations, columns, comments, dependents. 584 migrate commands.
It predicted the infinity blocker and the amount → amount_bc drift before they bit. Its risk gate and validator caught real problems once fixed.
Wrote everything, and refused to trust green: planted defects at every level, the oracle reconciliation, and root-causing 16 EKOS defects, including two critical governance holes and a cents-blind validator.
Cloud: semantic naming in the compile, cited Q&A (cautious, sometimes thin). Local Llama 3: fast via cache, weaker answers. Neither was on the critical path. Every modelling rule was confirmed in source.
| # | Defect | Severity |
|---|---|---|
| 4 | Requester could approve their own R3 request (--as cli:<me>) | critical |
| 9 | Validator hashed unconstrained numeric at scale 0: cents invisible | critical |
| 15 | Blast radius by table-name suffix: review R1, load R3 | critical |
| 16 | Load accepted an approval granted at a lower class | critical |
| 1, 2, 7 | Constraint triggers; text-keyed chunking; invalid Nullable(LowCardinality) | blocker |
| 5, 6, 8 | Evidence collisions / prefix matching; non-reproducible DDL | major |
| 10–14 | NULL, byte-length, empty-set, TSV-NULL asymmetries; validate exited 0 on failure | major |
| 3 | Policy file kebab-case | minor |
All fixed, each with a regression test that fails without its fix (EKOS devlog_226).
resolveALTER history not replayedjsonb has no canonical ruleask --json logs to stdout; a compile whose LLM calls all fail exits 0USING)db_patches)payment_post stores scale-22 amountscd ../EKOS && docker compose -f docker-compose.migrate.yml up -d # once: migration/clickhouse_setup.sql (named collection + databases) python3 tools/llm_proxy.py 8765 & cd pipelines && PROJECT=my-run ../.venv/bin/python -m lsmb_pipelines.run
| Path | What |
|---|---|
sql/, data_generator/ | LedgerSMB schema loader + seed; synthetic data through LedgerSMB's procedures |
migration/, clickhouse/ddl/ | EKOS Migrate scripts, policy, EKOS-generated DDL |
dbt/ | 38 models, 124 tests, macros, docs |
pipelines/ | orchestrator, tax loader, reconciliation, KPI and usage reports |
docs/ | Confluence pages, REPORT.md, QUERY_LOG.md, migration_report.md |
logs/ | every query, answer, LLM call, command and run |
A real ERP's schema and logic, a migration whose every step is a recorded fact, an analytical layer tested four ways and reconciled to LedgerSMB's own books — and an honest account of where each tool, human and model helped.