Source: github.com/AnalyticsLibrary/Analytics — Dutch financial-audit open-source project (Apache 2.0).
Cloned at: /Users/sarkout/projects/prive/financial-audit/analyticslibrary-reference/
Reviewed: 2026-09-02
Purpose: Map the check logic from every format (SQLite SQL, Invantive SQL, ACL scripts, E2A scripts, Lavastorm pipelines) to their exact conditions and compare each with what Athena's analysis/ package currently implements.
The repository is structured as B_ANALYTICS/ with sub-folders per tool: B1_ACL/ (ACL scripts), B2_INVANTIVE_SQL/ (Invantive SQL for XAF driver), B3_Lavastorm/ (Lavastorm data flow pipelines), B5_SQL/ (SQLite SQL), B6_E2A/ (E2A scripts), B7_IDEA/, B8_Python/. All checks read from Dutch XAF auditfile (XML) fields: accID, jrnID, jrnTp, periodNumber, trDt, effDate, amnt, amntTp, custSupID, leadReference (RGS code), vatID, vatPerc, vatAmnt.
| Check | Name | Athena Status | |-------|------|---------------| | B012 | Benford's Law leading-digit distribution | Fully implemented | | A001 | Period balance pivot (saldibalans per periode) | Partially implemented | | A006 | Journal totals by type (dagboektotalen) | Partially implemented | | A004 | Totals per relation (debtor/creditor) | Not implemented | | B010 | Out-of-period bookings | Not implemented (gap) | | B003 | Debtor/creditor write-off via unexpected journals | Not implemented (gap) | | B011 | Purchase invoice consistency per supplier | Not implemented (gap) | | B001 | Duplicate entry detection | Fully implemented (richer) | | B002 | Negative P&L account balances | Not implemented | | B004 | Negative cash balance per period | Not implemented (gap) | | B005 | Large cash transactions ≥ €15,000 | Partially implemented | | B006 | Negative debtor balance in sales journal | Not implemented (gap) | | B007 | Positive creditor balance in purchase journal | Not implemented (gap) | | A002 | Journal/account cross-tab matrix | Partially implemented | | G001 | Payroll tax / gross wages ratio | Not implemented | | H001 | Effective VAT percentage per relation | Not implemented |
What it detects: Whether the distribution of leading digits in transaction amounts follows Benford's Law (expected: ~30% start with 1, ~18% with 2, etc.). Deviation is an indicator of fabricated or manipulated amounts.
SQLite SQL (B5_SQL/B012 (SQL).txt):
SELECT COUNT(amnt) AS T,
SUM(CASE WHEN substr(cast(abs(amnt * 100) as text),1,1)='1' THEN 1 ELSE 0 END) AS N1,
SUM(CASE WHEN substr(cast(abs(amnt * 100) as text),1,1)='2' THEN 1 ELSE 0 END) AS N2,
-- ... N3 through N9
FROM Transactions
Key logic:
amnt * 100 first to remove decimal leading zeros (e.g. 0.15 → 15, leading digit = 1 not 0).abs() to handle credit amounts.substr(..., 1, 1) — the first character.Athena implementation (PopulationAnalysisService.java):
Fully implemented with the following specifics:
effectiveAmount(tx) using amount.abs().toPlainString(), scanning for the first digit 1–9. Logically equivalent to the reference's abs(amnt * 100) approach.BENFORD_EXPECTED_FREQUENCIES = {0.30103, 0.17609, 0.12494, 0.09691, 0.07918, 0.06695, 0.05799, 0.05115, 0.04576} (derived from log10(1 + 1/d) for d = 1..9).BENFORD_OVER_REPRESENTATION_FACTOR).BENFORD_LAW.Delta from reference: The AnalyticsLibrary just produces raw counts for manual review. Athena adds the chi-square significance test and per-transaction flagging, which is richer. No functional gap.
What it detects: A cross-tab of GL account balances across periods 0–12, distinguishing balance accounts (cumulative) from P&L accounts (period-only).
SQLite SQL (B5_SQL/A001 SQL(ite) periode balans.txt):
SELECT Transactieregels.accID, Grootboek.accDesc, Grootboek.accTp,
SUM(CASE WHEN periodNumber = '0' THEN
CASE WHEN amntTp='D' THEN amnt ELSE amnt * -1 END ELSE 0 END) AS "P0",
SUM(CASE WHEN periodNumber = '1' THEN ... END) AS "P1",
-- ... P2 through P12
SUM(CASE WHEN amntTp='D' THEN amnt ELSE amnt * -1 END) AS Saldo
FROM Transactieregels
INNER JOIN Grootboek ON (Transactieregels.accID = Grootboek.accID)
GROUP BY Transactieregels.accID
This is a simple period pivot without cumulative logic — each column is the net for that exact period. P0 is the opening balance period.
ACL script (B1_ACL/B001.aclscript + B001a + B001b):
Implements cumulative-for-balance vs period-for-P&L logic:
accTp = "B"): SUMMARIZE ... IF a_periode <= v_period — cumulative up to and including the period.accTp = "P"): SUMMARIZE ... IF a_periode = v_period — that period only.Invantive SQL (B2_INVANTIVE_SQL/A001.sql):
SELECT ... coalesce(opening_balance, 0) + coalesce(balance, 0) total_balance
FROM (
SELECT gat.accid, prd.periodnumber,
obe.balance opening_balance,
sum(tle.balance) balance
FROM generalledgeraccounts gat
JOIN periods prd ON prd.interface_url = gat.interface_url
LEFT JOIN transactionlines tle ON tle.accid = gat.accid
AND tle.transaction_periodnumber <= prd.periodnumber -- cumulative!
LEFT JOIN OpeningBalanceLines obe ON obe.accid = gat.accid
GROUP BY gat.accid, prd.periodnumber, obe.balance
) dtl
WHERE coalesce(opening_balance, 0) != 0 OR coalesce(balance, 0) != 0
ORDER BY periodnumber, accid
Key conditions:
transaction_periodnumber <= prd.periodnumber — all periods up to current, making it cumulative for all accounts.OpeningBalanceLines joined separately to add opening balances.E2A script (B6_E2A/E2A_A001.txt):
crosstable all "grootboekrekeningcode;grootboekrekeningnaam;grootboekrekeningtype"
"fiscalyear;periode" "+debet;-credit" "Totaal" "table:FA.14_Rekening_dc_periode" dec:2
Simple crosstab, debit positive and credit negative per period, no cumulative logic — same as the SQLite version.
Athena implementation (AnalysisController.getPeriodebalans()):
Partially implemented:
transactionDate field — not from XAF's periodNumber field.OpeningBalanceLines.Genuine gaps:
periodNumber (0–12). In Dutch XAF files period 0 is the opening balance and period 13 is sometimes used for year-end adjustments.What it detects: Aggregate debit, credit, and net per journal/dagboek, with journal type classification.
SQLite SQL (B5_SQL/A006 SQL(ite) dagboektotalen.txt):
SELECT Transactieregels.jrnID, Dagboek.desc, Dagboek.jrnTp,
COUNT(amnt) AS LinesCount,
SUM(CASE WHEN amntTp='D' THEN amnt ELSE 0 END) AS Debet,
SUM(CASE WHEN amntTp='C' THEN amnt ELSE 0 END) AS Credit,
SUM(CASE WHEN amntTp='D' THEN amnt ELSE amnt * -1 END) AS Saldo
FROM Transactieregels
INNER JOIN Dagboek ON (Transactieregels.jrnID = Dagboek.jrnID)
GROUP BY Transactieregels.jrnID
Output: one row per jrnID, with jrnTp (B=bank, S=sales, P=purchase, M=memorial/memoriaal, K=cash).
Athena implementation (AnalysisController.getJournaalbalans()):
Partially implemented. Athena computes a matrix of GL account × dagboek code, showing debit/credit per account per dagboek. But:
dagboekCode from the prefix of transactionId (everything before the first -), not from a journal metadata field. This is a heuristic.jrnTp (bank/sales/purchase/cash/memorial) because that field is not separately parsed into TransactionDto.Gap: A summary-per-journal endpoint equivalent to A006 (one row per dagboek with type, count, debit, credit, net) does not exist. The journaalbalans endpoint is the account-by-journal matrix (A002 equivalent) rather than A006.
What it detects: Net sales/purchase amount per customer or supplier, with debtor/creditor type classification.
SQLite SQL (B5_SQL/A004 SQL(ite) Totalen per relatie.txt):
SELECT Transactieregels.custSupID, Relaties.custSupName, Relaties.custSupTp,
COUNT(amnt) AS LinesCount,
SUM(CASE WHEN amntTp='D' THEN amnt ELSE 0 END) AS Debet,
SUM(CASE WHEN amntTp='C' THEN amnt ELSE 0 END) AS Credit,
SUM(CASE WHEN amntTp='D' THEN amnt ELSE amnt * -1 END) AS Saldo
FROM Transactieregels
INNER JOIN Relaties ON (Transactieregels.custSupID = Relaties.custSupID)
GROUP BY Transactieregels.custSupID
ACL A004 (B1_ACL/A004.aclscript):
Filters to only debtor accounts (RGS prefix BVORDEBHAD) or creditor accounts (RGS prefix BSCHCREHAC), then summarizes by custSupID.
Athena implementation: Not directly implemented. getTopAccounts() (DA-047) approximates this by ranking GL accounts by debit/credit volume, but TransactionDto does not expose custSupID (the counterparty/relation ID). XAF custSupID is parsed at the trLine level and Athena's data model currently flattens only GL-account-level data into TransactionDto.
Gap: No relation-level (debtor/creditor) summary endpoint. Would require parsing custSupID from trLine into TransactionDto.
What it detects: Transactions where the booking date (trDt) falls outside the period's official start/end date, indicating a period-allocation error or deliberate backdating.
ACL script (B1_ACL/B010.aclscript):
DEFINE FIELD a_buiten_periode COMPUTED
"ja" IF trDt < periods.startDatePeriod AND periodNumber <> "0"
"ja" IF trDt > periods.endDatePeriod AND periodNumber <> "0"
""
Joins transactions to periods table on (fiscalYear + "_" + periodNumber), then flags where trDt falls before the period's start date or after the period's end date. Excludes period 0 (opening balance).
E2A script (B6_E2A/E2A_B010.txt):
create filter (month([mutatiedatum];"yyyy-mm-dd") <> [periode])
Simpler: month of mutation date (mutatiedatum) does not equal periode (period number). Assumes period number equals the month number.
Lavastorm (B3_Lavastorm/B010.txt):
trdt = month(TrDt)
emit * where trdt <> PeriodeNumber
Same as E2A: month(TrDt) ≠ PeriodeNumber.
Athena implementation: Not implemented as a detector. CUTOFF_EXCEPTIONS flags transactions within ±5 business days of the fiscal year end date — this is conceptually different (it's about cut-off risk at year-end, not about within-year period misassignment).
Genuine gap: No check that month(transactionDate) ≠ periodNumber. The simple form of this check requires parsing periodNumber from the XAF trLine into TransactionDto (currently not in the data model). The ACL version requires comparing against period date ranges parsed from the XAF periods section (currently not parsed).
Required logic (simple E2A form): transactionDate.getMonthValue() != periodNumber, filtering out period 0.
What it detects: Transactions on debtor or creditor accounts posted through journal types other than the expected ones. Normal flow: debtors are only touched by the sales journal ("S") and bank ("B"); creditors by the purchase journal ("P") and bank ("B"). Anything else is a potential write-off, fraud, or error.
ACL script (B1_ACL/B003.aclscript):
DEFINE FIELD a_afwijkend_dagboek COMPUTED
"nee" IF UPPER(SUBSTRING(leadReference 1 10)) = "BVORDEBHAD" AND jrnTp = "B"
"nee" IF UPPER(SUBSTRING(leadReference 1 10)) = "BVORDEBHAD" AND jrnTp = "S"
"nee" IF UPPER(SUBSTRING(leadReference 1 10)) = "BSCHCREHAC" AND jrnTp = "B"
"nee" IF UPPER(SUBSTRING(leadReference 1 10)) = "BSCHCREHAC" AND jrnTp = "P"
"ja"
Identifies debtor accounts by RGS prefix BVORDEBHAD and creditor accounts by BSCHCREHAC. Expected journals: bank (B) or sales (S) for debtors; bank (B) or purchase (P) for creditors. Everything else is flagged.
E2A script (B6_E2A/E2A_B003.txt):
create filter ([saldo]<0 and @debiteuren and not @bank_kasboek)
Simpler: negative balance on a debtor account not in bank/cash — i.e., a credit on a debtor not through bank.
Lavastorm (B3_Lavastorm/B003.txt):
emit * where 'Reknr' == "1310" or 'Reknr' == "1300"
then:
emit * where 'Dagboek' <> "Verkoopboek" and 'Dagboek' <> "ING BANK"
Hardcoded to accounts 1310/1300 (typical debtor accounts), excluding sales book and ING BANK journal.
Athena implementation: Not implemented. Athena has no check that cross-references journal type against GL account type/RGS classification.
Genuine gap: Requires:
leadReference prefix (BVORDEBHAD/BSCHCREHAC) or by account number pattern.jrnTp) per transaction — currently jrnTp is not in TransactionDto.What it detects: For each supplier in the purchase journal, shows all GL accounts used. An inconsistency flag: if a supplier that normally posts to account X suddenly posts to account Y, it may indicate an error or fraud (invoice routing to wrong cost center, or an unexpected debit note).
ACL script (B1_ACL/B011.aclscript):
OPEN transactions
EXTRACT FIELDS ALL IF jrnTp = "P" TO scr01
-- joins customersSuppliers and generalLedger
SORT ON custSupID custSupName accID accDesc
SUMMARIZE ON custSupID custSupName accID accDesc SUBTOTAL trLine_amnt TO scr03
Filters to purchase journal only (jrnTp = "P"), then summarizes by supplier + GL account, showing total amount per combination. No numeric threshold — it's a completeness/pattern view.
Athena implementation: Not implemented. There is no endpoint that shows which GL accounts a supplier uses in the purchase journal.
Genuine gap: Requires jrnTp, custSupID, and accID all present on the same transaction line. Currently jrnTp and custSupID are not in TransactionDto.
What it detects: Entries with identical amounts on the same account, indicating potential double posting.
Lavastorm B001 (B3_Lavastorm/B001.txt):
Uses a "Duplicate Detection" node on key (Reknr, TrLineAmount) — flags any two rows with the same account ID and same line amount.
E2A B001 (B6_E2A/E2A_B001.txt):
sort numeric ascending "saldo"
create filter (recoffset([saldo];1) = [saldo] or recoffset([saldo];-1) = [saldo])
After sorting by amount, flags rows where the adjacent row (next or previous) has the same saldo value. This catches adjacent duplicates in sorted order.
Athena implementation: Fully implemented and richer with three layers:
POTENTIAL_DUPLICATES: Exact match on (accountId, date, description, debitAmount, creditAmount).DUPLICATE_AMOUNT_DATE (DA-026): Same (accountId, date, debitAmount, creditAmount) regardless of description.FUZZY_DUPLICATES (DA-027): Same (accountId, date), amounts within 1% tolerance (FUZZY_AMOUNT_TOLERANCE_PCT = 0.01), description similarity ≥ 75% (Levenshtein ratio FUZZY_DESCRIPTION_SIMILARITY_MIN = 0.75).Athena's duplicate detection is meaningfully more sophisticated than the reference — no gap.
What it detects: P&L accounts (cost/revenue accounts 4000–8000 range in Dutch chart of accounts) with a negative balance, which may indicate a revenue reversal, mis-posting, or opening balance error.
Lavastorm (B3_Lavastorm/B002.txt):
emit * where 'Reknr' >= "4000" and 'Reknr' < "8000"
-- then:
emit * where 'Saldo' < 0.0
Hardcoded to accounts in the 4000–8000 range with a negative running balance.
Athena implementation: Not implemented. Athena has LOW_ACTIVITY_ACCOUNTS but no check for abnormal balance signs on P&L accounts.
Note: This check is highly chart-of-accounts-specific (the 4000–8000 range is conventional in Dutch bookkeeping). Without RGS classification, the range is the only reliable indicator.
What it detects: A cumulative cash account balance going negative at any point, which is physically impossible and indicates a posting error, missing transaction, or fraud.
ACL script (B1_ACL/B004.aclscript):
BLIMKAS.E2A script (B6_E2A/E2A_B004.txt):
totalsperday %dveld% %dformat% "saldo" "FF.08_Negatieve_kassen"
Computes running total per day for cash accounts, then flags days with negative running balance.
Lavastorm (B3_Lavastorm/B004.txt):
Verloop = sum(Saldo) -- running cumulative
emit * where 'Verloop' < 0.0
For account Reknr == "1000" (Kasboek), computes running cumulative sum of Saldo ordered by PeriodeNumber, flags where it goes negative.
Athena implementation: Not implemented. No check for cumulative negative balance on any specific account type.
Genuine gap: Requires:
BLIMKAS or account number pattern like 1000).What it detects: Individual cash transactions at or above €15,000. In the Netherlands, cash transactions above €15,000 trigger mandatory reporting requirements (Wwft/anti-money-laundering). This is a regulatory compliance check, not just a statistical outlier check.
Lavastorm (B3_Lavastorm/B005.txt):
emit * where 'Dagboek' == "Kasboek"
-- then:
emit * where 'Saldo' >= 15000 or 'Saldo' <= -15000
Fixed threshold: €15,000 (absolute value), applied only to the cash journal (Dagboek == "Kasboek").
Athena implementation: Partially implemented. LARGE_ENTRIES flags transactions above the 95th percentile of the population. This is a relative population-based threshold, not the fixed €15,000 regulatory threshold, and it applies to all accounts, not just cash.
Gap: The specific €15,000 cash reporting threshold (Wwft) is not implemented. This is a legally significant threshold distinct from a statistical large-entry detector.
What it detects: Debit notes / negative invoices in the sales journal — debtor accounts with a credit balance (customer owes nothing, entity owes customer). May indicate a refund, write-down, or over-billing adjustment that needs review.
Lavastorm (B3_Lavastorm/B006.txt):
emit * where 'Dagboek' == "Verkoopboek"
-- then:
emit * where ('Reknr' == "1310" and Saldo < 0) or ('Reknr' == "1300" and Saldo < 0)
Accounts 1300/1310 are typical debtor (debiteur) accounts. Flags negative saldo in the sales journal.
Athena implementation: Not implemented. No journal-type-filtered balance sign check.
Genuine gap: Requires jrnTp == "S" filter and checking debtor account saldo < 0.
What it detects: Credit notes / debit notes in the purchase journal — creditor accounts with a debit balance (entity owes nothing to supplier, supplier owes entity). Indicates supplier credit notes, advance payments, or over-payment.
Lavastorm (B3_Lavastorm/B007.txt):
emit * where 'Dagboek' == "Inkoopboek"
-- then:
emit * where ('Reknr' == "1610" and Saldo > 0) or ('Reknr' == "1600" and Saldo > 0)
Accounts 1600/1610 are typical creditor (crediteur) accounts. Flags positive saldo in the purchase journal.
Athena implementation: Not implemented. Same gap as B006, mirror image for creditors.
What it detects: For each GL account, the total per period — a matrix view of which accounts are active in which periods.
ACL script (B1_ACL/A002.aclscript):
CROSSTAB ON accID accDesc COLUMNS a_Period SUBTOTAL a_Amount TO "A002.FIL"
Where a_Period = fiscalYear + "_" + ZONED(VAL(periodNumber, 0), 2).
Athena implementation: Partially implemented via getPeriodebalans() which gives the same structure (account × period matrix), but using calendar months derived from transactionDate rather than XAF periodNumber. Same limitation as A001.
What it detects: Whether the withheld payroll tax (loonheffing/loonbelasting) is a plausible percentage of the gross wages. Abnormal ratios may indicate under/over-reporting of payroll tax obligations.
ACL script (B1_ACL/G001.aclscript):
SUMMARIZE ON periode SUBTOTAL bedrag IF MATCH(GeneralLedger.leadReference "BSchLheAfb")
AND bedrag < 0 TO loonheffing
SUMMARIZE ON periode SUBTOTAL bedrag IF MATCH(GeneralLedger.leadReference
"WPerLesTep" "WPerLesLon" "WPerLesOwe" "WPerLesOnr" "WPerLesGra" "WPerLesLin" "WPerLesOnu" "WPerLesOlr")
TO lonen
-- then:
DEFINE FIELD percentage COMPUTED
(loonheffing * -1.00) / lonen * 100.00 IF lonen <> 0
100.00
RGS codes used:
BSchLheAfb — only negative amounts (tax payable)WPerLesTep, WPerLesLon, WPerLesOwe, WPerLesOnr, WPerLesGra, WPerLesLin, WPerLesOnu, WPerLesOlr)Athena implementation: Not implemented. KengetallenService computes current ratio, solvency, gross margin, debtor days, creditor days from RGS codes — but does not compute the payroll tax ratio.
Note: This is domain-specific to payroll-heavy engagements. Can be added to KengetallenService when RGS codes are present.
What it detects: The effective VAT percentage per customer/supplier in the sales and purchase journals. Anomalous percentages (e.g., 0% where 21% is expected, or a non-standard rate) indicate mis-coded invoices, VAT fraud, or data entry errors.
ACL script (B1_ACL/H001.aclscript):
SORT a_fiscalYear a_companyName periodNumber jrnTp custSupID vatID
IF MATCH(jrnTp "P" "S") AND (custSupID <> " ") AND (a_VAT_Amount <> 0.00)
-- then:
SUMMARIZE ON jrnTp custSupID custSupName vatID SUBTOTAL a_Amount a_VAT_Amount
DEFINE FIELD percentage COMPUTED
(a_VAT_Amount / a_Amount) * 100.00 IF a_Amount <> 0.00
100.00
Filter: only purchase ("P") and sales ("S") journals, only lines with a counterparty ID, only where VAT amount is non-zero. Computes vatAmount / grossAmount * 100.
Athena implementation: Not implemented. The XAF parser reads vatID, vatPerc, vatAmnt fields per trLine, but they are not exposed in TransactionDto and no VAT analysis exists.
Gap: VAT data exists in the XAF but is not surfaced. This is a significant analysis category for Dutch engagements.
These are checks where the AnalyticsLibrary has concrete, tested logic and Athena has nothing equivalent:
| # | Check | What's missing in Athena's data model |
|---|-------|----------------------------------------|
| 1 | B010 Out-of-period | periodNumber not in TransactionDto; period date ranges not parsed |
| 2 | B003 Unexpected journals for debtors/creditors | jrnTp not in TransactionDto; RGS prefix filtering not wired |
| 3 | B004 Negative cash balance | No running balance per account; cash account identification missing |
| 4 | B006 Negative debtors in sales journal | jrnTp not in TransactionDto |
| 5 | B007 Positive creditors in purchase journal | jrnTp not in TransactionDto |
| 6 | B011 Purchase invoice consistency | jrnTp and custSupID not in TransactionDto |
| 7 | B005 €15,000 cash threshold | jrnTp needed to filter to cash journal; fixed threshold not configurable |
| 8 | H001 VAT percentage per relation | vatAmnt, vatPerc, custSupID not in TransactionDto |
Root cause for most gaps: TransactionDto is built from XAF trLine elements but currently maps only account-level fields. The XAF trLine also carries jrnID, jrnTp, periodNumber, custSupID, vatID, vatPerc, vatAmnt. Adding these to TransactionDto (or creating a richer TransactionLineDto) would unblock checks B003, B005, B006, B007, B010, B011, and H001 simultaneously.
Checks already covered well:
Reacties