Skip to main content

​
The reconciliation ontology

🟢 Live. The GL and intake sources are projected today; Teable-as-a-source and the external registers are still roadmap. GreatBook’s records for one real-world transaction live in several systems at once. There is the document somebody wrote or uploaded, the row it was staged into, the AP obligation it created, the journal entry it posted, the VAT register line beside that entry, and the payment that eventually killed it. Nothing in a relational schema says those six rows are one transaction, so the question a reviewer actually asks - is this the same bill, and which copy is the document of record - has no query behind it. The reconciliation ontology is that missing model. It is the Auditor’s memory of what the systems of record collectively assert, kept in one place, in one vocabulary, so a cross-system question can be a query rather than an opinion.

​
Two layers, deliberately kept apart

The concept ontology says what kinds of thing exist and how they may relate. It is small, static, checked into git, and reviewable in a pull request. The instance graph says which things actually exist right now. It is large, changes constantly, and lives in Postgres, because it has to be fresh. Conflating the two is how an ontology rots, so they are generated and stored separately. Layer A is generated, never hand-edited: python -m agents.common.build_recon_ontology rewrites it and --check fails the build when it is stale. It is built from the document registry - each of the 146 doc types already declares an ontology_role and an accounting_effect - from the Teable schema snapshot, and from one hand-authored concept-to-table map. That last input is hand-authored on purpose: there is no machine-readable mapping between the three systems, and deriving one from name similarity would be a guess that an auditor then cites as a fact. The concept names in Layer A are CHECK-constrained on recon_nodes.concept in migration 0034_recon_graph, and recon/tests/test_ontology.py reads both files and fails the build if they drift. A projector bug cannot invent a concept; the insert is refused.
The evidences relation ranges over every concept, so an ontology that gained a seventeenth concept would silently widen it. That is why the two vocabularies are pinned to each other by a test rather than by convention.

​
The 16 concepts

Four systems may own a node: gl, intake, teable and external. The catalog marks a grain only on the three line concepts; everything else is a header, which is also what a node’s parent_id being null means. Subsumption is recorded rather than modelled as separate concepts, so a query can ask for every Obligation without enumerating bills and invoices. A node’s identity in the graph is (org, system, source_table, source_id), unique by constraint, so one source row is one node and nothing can quietly become two.

​
What a document asserts

Each of the 146 doc types carries an ontology_role - GOVERNS, CREATES, SETTLES, EVIDENCE, or one of the four mixes - and that role is the relation the document asserts. So the ontology does not need a second opinion about what a goods receipt does; the registry already said it. Of the 146 types, 75 move a balance and 71 do not. evidences is the role that proves a fact and moves no money, which is exactly why it is the one drawn dashed above: an evidencing document that never reaches the ledger is correct, and a CREATES document that never reaches the ledger is a finding. The counts by role: 47 CREATES, 38 EVIDENCE, 36 GOVERNS, 9 SETTLES, and 16 across the four mixed roles.

​
The ledger tie

posted_as is the single most load-bearing relation on the page: it is the claim that this record reached the book of record, and its absence on a document whose type moves a balance is the first thing the check family looks for. Every posted_as edge is a projection of a foreign key a writer already wrote under a gate - intake_queue.result_entry_id, obligations.gl_entry_id, settlements.gl_entry_id, tax_entries.gl_entry_id. sub_ledger_of is the control-account tie stated structurally: AP obligations tie to account 2000 (VAS 331), AR obligations to 1100 (VAS 131), and an input-VAT register line to 1300 (VAS 133).

​
The grain

Every line concept hangs off its header by line_of, and lines are what a 3-way match compares. matches is that n-way line match, and it is the one relation that can never be an asserted edge. No system anywhere commits a line match, so it exists only as a proposal - which is why recon_edges’ CHECK constraint does not list it at all. The line grain is declared here and is not projected yet, so both of these relations are roadmap rather than live.

​
Identity, and which copy is the document of record

Three relations answer “these two records are one thing”, and they are the only ones that may cross a system boundary freely. A version group is the transitive closure over supersedes, duplicate_of and same_as, and it is stated over the group, never over one edge. Forward, reverse, sibling and chain are one rule, and enumerating topologies is how the next one is missed. The closure traverses both stores: asserted edges and proposed links that a human has confirmed. suggested links are deliberately excluded, so an unconfirmed guess can neither raise a finding nor suppress one on its own. The Teable shadow node deserves a word. When an intake row names the table and record it was staged into, the projector creates a node for that Teable record from intake’s own columns - no Teable call - and the concept it takes comes from the ontology’s own table-to-concept map. An unmapped table shadows as Document, which is what an intake row is, rather than as a guessed concept the CHECK constraint would then refuse. The two-table split is the load-bearing distinction in the whole design. Every proposal names the rule that made it, so a bad rule’s entire output is findable - and revocable - in one query. Nothing writes a proposal yet. The table, its vocabulary, the accept/reject/undo endpoints and the version closure that reads confirmed links are all in place; the matcher that would fill it is the line-grain work. So recon_links is empty today, and the Auditor’s rules reach their conclusions from asserted edges alone - which is the honest half of the design, since an asserted edge is a fact and a proposal never was.
recon_links.confidence is internal only. The UI surfaces discrete states and never a score, because a number next to a match invites a reviewer to treat 0.83 as a decision.

​
Where it lives, and who may touch it

Four additive tables in the canonical migrated database, all org-scoped by row-level security exactly as intake_queue and the GL tables are: ws_anon holds the SELECT grant but no policy admits it and so reads zero rows, ws_reader sees only its session org, and the writer owns the tables. Both enable and force row level security are set, so the table owner does not bypass its own policy. The Auditor never calls the projector. That is not a convention. The Auditor’s only database channel is a session the database itself holds read-only, so if an auditor module imported the projector the first insert would raise read-only transaction from Postgres. agents/auditor/tests/test_recon_read_only.py fails the build if any auditor module imports it at all, which turns the runtime guarantee into a build-time one. The projection runs on a schedule the DevCenter owns, every three hours, against a freshness threshold of six hours. Those two numbers are one pair: a three-hourly cadence against a six-hour threshold leaves exactly one missed run of margin, so a single failed projection is absorbed and two in a row are reported inconclusive.
Postgres, not the semantic knowledge store. Reconciliation is exact matching - a tax code, an invoice serial, an amount to four decimals, a date - not semantic similarity. The SOP store keys a document by its content hash, so a rewritten instance leaves the superseded copy retrievable, and the single question this graph exists to answer is which version is the document of record. A store whose native behaviour is to keep both copies retrievably cannot answer it. It also has no org-scoped RLS, and the read-only guarantee is a database property with no equivalent over an HTTP API where the key that reads can also write.

​
Nothing in the graph is a verdict

The graph stores observations, never conclusions. A node carries its natural key, its normalised amount, its date, its party key and its source attributes. An edge carries the foreign key it came from. Neither carries a status, a score, a health flag or a reconciliation result, and there is no table in which “this book reconciled” is written down. Every verdict is derived on read, by running the rules over the graph as it stands right now. That is the same rule the settlement spine already follows - a bill’s due, a payment’s unapplied balance and a float balance are derived, never stored - applied one layer out. The consequence is the one that matters: a stale graph cannot replay an old pass as a fresh all-clear, because there is no old pass to replay. It reports inconclusive instead. The only decision the graph does persist is a human one: recon_links.status, with decided_by taken from the server-derived actor and never from a browser-supplied header. Nodes are idempotent by fingerprint - sha256(concept | natural key | amount_base | doc_date | party_key) - so re-projecting an unchanged row is a timestamp touch and a changed row updates in place. The projector also never deletes: a node whose source row has vanished keeps its row and lets source_seen_at age, so the Auditor reports a stale node rather than a graph that quietly shrank away from a finding already raised.

​
What the projector actually builds today

Everything above is the declared ontology. This is the subgraph that exists in the database right now, from the GL and intake sources. Nodes, by concept and source table: Edges, and the committed fact each one projects: The advance-pool allocation targets get no edge at all: PO_ADVANCE and SO_ADVANCE name a pool rather than an obligation, and inventing an edge to a node that does not exist is worse than no edge. That last same_as row is the one that closes the shape nothing in the product could previously find. Two intake rows filed by two versions of one document, with duplicate_of null on both, are connected only once the Typewriter version chain and the handoff trail are edges in the same graph. Party, Order, StockMovement, the three line concepts and ExternalRecord are declared and not yet projected; Period and Rail are projected as nodes with no edges yet. Two properties of that pass are worth stating because a reader would otherwise assume the opposite:
  • Both sources are a full pass, every time. The watermark records the position a pass covered; nothing filters on it. obligations and settlements carry created_at only while their status mutates, so a timestamp narrow would silently skip a settled obligation - and on the intake side an incremental cursor was tried, reproduced wrong on real Postgres, and removed. now() is transaction-start time, so a long transaction committing after a pass stamps a row older than the cursor that pass advanced to, and a strict > predicate then excludes that row forever.
  • The full VN VAT natural key comes from intake, never from the GL. obligations carries only party_code and doc_no, so the (tax code, serial, number) triple a VN VAT invoice is really keyed by is not reconstructible from the ledger. The projector walks the obligation’s entry back to the intake row that posted it to enrich the key, and when that walk fails the key is recorded partial - after which any rule needing the serial must decline to propose rather than match on amount plus date, which is how a matcher invents a link.
The projector writes line_of from an Allocation to its Settlement, which is a grain link outside that relation’s declared domain of the three line concepts. The table above lists the edge as it is written, not as the catalog declares it.

​
The recon check family

The graph exists to be asked questions. recon is the Auditor’s fourth check family, and it runs over the graph the same way the other three run over the ledger: read-only, and reporting inconclusive rather than clean whenever it could not verify. Freshness runs first, and it is a gate. When stale_projection fires, the sweep returns with no other rule having run. Reporting four clean rules over a three-day-old graph is worse than reporting nothing, because it reads as an all-clear. Three of these are worth a sentence each. cross_system_orphan asks from the document side what the GL invariant sweep asks from the intake side. The invariant sweep starts at intake_queue and joins to journal_entries, so it can only see rows that exist in intake_queue; this starts at the graph’s Document nodes, which is what will make a document projected from another system - with no intake row at all - visible. Whether a type moves a balance is read from the ontology’s accounting_effect, so the rule cannot drift from the document registry. duplicate_group_multi_live is the standing form of a one-time SQL cleanup: two intake rows for one version group with duplicate_of null on both, which nothing in the product could find and which would let one document post twice. Nothing could find it because the group is only visible once the Typewriter version chain and the handoff trail are edges in one graph. The finding is anchored on the group, not on its current version, so adding an unrelated newer draft does not make the exception register open a second row and let the first age into resolved. control_account_tie reuses the Accountant’s own oracle rather than re-deriving the comparison, for the same reason the hash-chain family reuses the ledger writer’s hash helpers: a verifier carrying its own copy of the rule verifies its copy, not the book. Those are the Close checksums AP-CONTROL and AR-CONTROL, and the finding names the workbook code so a reviewer can look it up in the close report.
The overlap with the Close checksums is deliberate and is not duplication. The Accountant runs them once at close; the Auditor runs them on every sweep, which is the difference between finding a rogue posting in January and finding it in March.

​
It is not the GL invariants, and it is not the Close checksums

Three families, three different jobs, and the words are binding:
  • the 18 GL invariants are enforced in the ledger writer, before the write, and they refuse;
  • the Close checksums run at close across the sub-ledgers, and they report;
  • the recon family runs over this graph, across systems, and it proposes and reports - it never merges two records, and it writes nothing at all.

​
What is honestly not covered

The family says what it reconciled rather than “no rule complained”, because a family that certifies amounts it never compared is the failure this design was reviewed for. A clean pass claims exactly three scopes: the AP/AR control against its sub-ledger, document-to-ledger linkage, and document versions. Everything else is explicitly outside that claim: Three further rules named in the design - unlinked_external, line_match_gap and header_line_footing - are not stubbed, on the grounds that a rule which exists and never fires reads exactly like a rule that passes.

​
Where it is enforced

​
The tests that would fail if it broke

recon/tests/test_ontology.py pins the generated catalog against the migration’s CHECK vocabularies, so the two cannot drift. test_projector.py and test_rules.py cover the projection and the five rules, test_control_tie.py covers the reuse of the Close-checksum oracle, and test_read_only_fixture.py proves the fixture the rules are tested on is genuinely read-only. On the Auditor’s side, test_recon_read_only.py fails the build if any auditor module imports the projector, and test_recon_family.py covers the family end to end.