Accounting Schema Cross-Reference Report
Analysis date: 2026-05-21 · Revised: 2026-09-29
Leisure maps to the shared posting-line table liesureenstries, feature commits reference those rows, and financial reporting sums them. Several workflows implement valid double-entry effects. What is missing is a unified, immutable, database-protected transaction contract that owns, balances, identifies, and reverses every complete posting set consistently.
Evidence Boundary
Sources read
The brainstorming Markdown report, exported SQL schema, and a clean clone of migrations, models, repositories, controllers, and routes at API commit 1ce2358f.
Schema evidence
Tables, columns, posting links, amount types, update/delete surfaces, balance fields, and post-export migrations.
Not proven here
Production data quality, live constraints omitted from export, all route behavior, and runtime behavior outside reviewed code paths.
The SQL export is dated 2026-05-18 and omits constraint/index detail, while current master contains later migrations. Migration history is also mixed: only 7 of 37 files mentioning ledger_id explicitly foreign-key it to liesureenstries.id. Live constraint introspection remains mandatory.
Code Evidence
| Question | Code-backed answer | Evidence |
|---|---|---|
| Central ledger table | Leisure maps to liesureenstries. | app/Leisure.php. |
ledger_id target | Semantically points to liesureenstries.id, but referential enforcement is inconsistent. | Seven reviewed migration files define the explicit FK; many other feature commit migrations do not. |
| Balance source | Account balances and financial reports sum debit and credit columns from ledger rows. | AccountsRepository, ReportsRepository. |
| Balance enforcement | Manual journals validate totals; major feature workflows often use DB transactions, but generic ledger writing remains line-oriented without one universal balance gate. | journalsController, leisuresController, invoice/bill/bank-transfer repositories. |
| Immutability | Edits and deletes remove or soft-delete ledger rows in multiple financial flows. | Expense, journal, bank transfer, invoice payment, and reconciliation paths. |
| Idempotency | No ledger-wide posting idempotency contract found; several payment providers use local reference/status checks. | Stripe, Paystack, Flutterwave, AT, TandaIO, NSANO, Monnify, and related callback paths. |
Second-Pass Verification Against Current Master
| Claim | Verdict | Evidence at 1ce2358f |
|---|---|---|
| A central ledger exists. | Confirmed | Leisure maps to liesureenstries; account balances, reports, dashboards, exports, and customer/supplier views sum its debit and credit columns. |
| The store owns complete transactions. | Not supported | The ledger schema has no universal posting transaction ID. recordLedger and recordLedgerFast each save one line. |
| Double-entry knowledge exists. | Confirmed | Invoice, bill, expense, transfer, journal, sale/POS, payroll, tax, wallet, and opening-balance paths create debit/credit sets. |
| Major workflows are never atomic. | Rejected as too broad | Many major workflows use DB::transaction. The issue is inconsistent aggregate ownership and controls, not the absence of database transactions. |
| Every posting uses one balance gate. | Not supported | Journal posting checks equality; generic ledger storage accepts independent arrays without the same gate, and line writers save one row at a time. |
| Posted history is immutable. | Rejected | Expense, bank-transfer, journal, invoice-payment, and reconciliation correction paths soft-delete ledger rows and often recreate them. |
| Lineage is centralized and FK-protected. | Rejected | At least 24 feature commit types are resolved through branching code; only 7 of 37 reviewed ledger_id migration files define the explicit ledger FK. |
| Wallet and ledger state are one record. | Rejected | BW::credit/debit mutate wallet transactions and balances; particular callers separately write ledger lines and commit links. |
| Idempotency is absent everywhere. | Rejected as too broad | Provider-specific reference/status checks exist, but no universal uniquely constrained event/posting idempotency contract was found. |
| Branch is a ledger dimension. | Not supported | Ledger rows have neither business_id nor branch_id; tenant scope is normally inferred through accounts. |
Controls worth preserving
- Database transactions around many major workflows.
- Business-owned chart-of-accounts validation.
- Cut-off checks in selected correction paths.
- Feature commit tables providing fragmented but real lineage.
- Reports derived from central ledger lines.
- Wallet before/after balances and provider references.
- Local payment-provider duplicate/status checks.
Assumption Cross-Reference
| Brainstorming claim | Schema evidence | Assessment |
|---|---|---|
| Current accounting is too CRUD-shaped. | Invoices, bills, sales, wallets, bank accounts, synced transactions, and many source documents store mutable state and balance-like columns. | Needs narrower wording A central ledger line store exists. The CRUD concern applies to feature-owned orchestration, mutable document state, and correction paths. |
| Balances are mutable rather than derived. | invoices.balance, bills.balance, sales.balance, wallets.balance, accounts.live_balance, linked bank balances, wallet balance-before/after fields. |
Supported Some are operational or external snapshots, but the split must be explicit. |
| Double-entry needs a unified transaction contract. | liesureenstries has debit and credit amounts; journals and opening balances split debit and credit lines. |
Refined Double-entry behavior exists, but a universal database-protected balanced transaction boundary is not visible. |
| Posted financial history should be immutable. | updated_at and deleted_at appear on posting and financial tables, including the central ledger posting-line table. |
Current shape conflicts Reversals and append-only posting need to be explicit. |
| Business events and ledger effects should be separated. | Many source tables link to postings via module-specific commit tables. | Partially supported The separation exists in fragments rather than one posting event contract. |
| Idempotency is required. | References, UUIDs, transaction IDs, and local provider callback checks exist, but no universal idempotency receipt, provider-event uniqueness, or posting key is visible. | Locally implemented Provider-specific checks do not prove retry-safe posting across all financial events. |
How Posting Works In Reviewed Paths
- A source document or money movement is created in a feature table such as invoice, bill, expense, sale, payroll, wallet, tax, bank transfer, or reconciliation.
- Application code creates posting rows in
liesureenstries. - Each posting row identifies one account and carries debit or credit amounts plus date, narrative, and FX context.
- A module-specific commit table records which posting row belongs to the source feature record.
- Manual journals and opening balances use separate debit and credit tables that also carry
ledger_id.
Operational record -> module posting logic -> central ledger posting rows in liesureenstries -> feature-specific *_commit or debit/credit bridge rows
Correction to the earlier framing: do not call this "no ledger" or "no double entry." The code shows a shared ledger line store, ledger-derived reports, and substantial debit/credit behavior. The stronger finding is that complete posting sets are not visibly owned and protected by one universal transaction-level contract.
Code-confirmed flow examples
| Flow | Current posting behavior |
|---|---|
| Expense | Posts payment-account credit and expense-account debit rows, then two expense commit rows. |
| Bank transfer | Posts two ledger rows inside a DB transaction, then deletes and recreates them on update. |
| Invoice issue | Posts receivable, sales, tax, discount, and inventory/cost rows as needed, all linked through invoice commits. |
| Manual journal | Checks debit total equals credit total before writing ledger and journal-side rows. |
| Wallet/payment | Wallet credit/debit methods update operational transactions and cached balance; particular payment/service workflows separately write counterpart ledger rows and wallet commit links. |
Evidence-Based Posting Walkthroughs
The code evidence supports a sharper conclusion: the product often posts sensible accounting lines, but the posting contract lives inside each feature workflow. A central ledger line table exists; a central immutable posting transaction contract is what appears to be missing.
Ledger write primitive
leisuresController::recordLedger(...) writes one Leisure row into liesureenstries. The caller supplies account, date, description, FX context, debit amount, and credit amount.
Readout: this is a posting-line writer, not a full double-entry transaction writer.
Expense
ExpenseRepository saves the expense, credits the payment account, debits the receiving or expense account, then creates ExpenseCommit rows.
Dr Expense Cr Bank / Cash
Risk: delete logic removes linked ledger rows; if commits are missing, it falls back to account/date/amount matching.
Invoice issue
InvoiceRepository debits receivables, credits sales, credits taxes, optionally posts inventory/cost entries, and handles discounts.
Dr Accounts Receivable Cr Sales Revenue Cr Output Tax
Readout: real accounting knowledge exists, but it is embedded in invoice persistence instead of a shared posting engine.
Invoice payment
InvoiceRepository::addPayment(...) updates invoice balance/status, debits cash or bank, credits receivables, and can post FX gain/loss and overpayment deposits.
Dr Bank Cr Accounts Receivable
Risk: mutable invoice balances can diverge from ledger-derived truth if every correction path is not perfectly synchronized.
Payment removal
InvoiceRepository::removePayment(...) loads InvoicePaymentCommit, deletes linked ledger rows, deletes the payment, and recalculates invoice payment state.
Rebuild rule: payment removal should post a reversal, failure, refund, or reallocation event; it should not erase financial history.
Manual journal
journalsController checks debit total equals credit total before posting. On update it deletes old journal-side rows and old ledger rows, then creates replacements.
Readout: the system understands journal balancing, but the strongest balance gate is feature-specific.
Business and branch scope
The codebase strongly reinforces business_id as the tenant boundary across accounts, invoices, bills, expenses, wallets, payroll, banking, taxes, settings, and reports. The branch signal is weaker in the sampled accounting paths: branch-like data appears around tax authority branches and payroll company/department structures, but not as a clear first-class ledger dimension.
Rebuild decision: if SME branch accounting is in scope, branch_id must be a first-class dimension on posting requests, ledger transactions, ledger entries, and report projections. For allocation-heavy SME use cases, entry-level branch dimensions are safer than transaction-level branch only.
financial_event business_id branch_id source_type source_id event_type ledger_transaction business_id branch_id source_event_id ledger_entry business_id branch_id account_id debit / credit
Relevant Table Map
| Area | Tables | Readout |
|---|---|---|
| Chart of accounts | accounts, accounttype, accountsubtype | Account vocabulary exists, but stronger posting semantics are not visible. |
| Central ledger posting lines | liesureenstries | Shared account postings used by feature workflows and reporting; includes debit/credit amounts, date, FX fields, match flag, and soft delete. |
| Manual journals | journals, journal_debits, journal_credits | Double-entry vocabulary exists but line tables are split by side. |
| Opening positions | opening_balances, opening_balances_debits, opening_balances_credits | Opening positions repeat journal-side split. |
| AR and AP | Invoice, payment, credit, bill, bill payment, withholding tables and commits | Source documents and posting bridges are feature-specific. |
| Cash and imported money movement | banktransfers, wallet_transactions, synced_transactions, reconciliation tables | External and operational balances must be separated from ledger truth. |
| Audit clues | user_actions, larametrics_models | Generic logging exists, but posting lineage is scattered. |
Priority Findings
H1. No unified protected posting unit
The central line store is functional, but it is not the same as an immutable transaction aggregate with complete grouped entries and a universal balance gate.
H2. Immutability is not encoded
Financial records expose update and soft-delete shapes. Posted history needs reversal rules.
H3. Money uses double
Binary floating-point in amounts creates avoidable rounding and reconciliation risk.
M1. Posting bridges are fragmented
Many *_commit tables imply module-owned posting contracts and uneven controls.
M2. Ledger FKs are inconsistent
The semantic ledger_id convention is broad, but explicit foreign-key protection appears in only 7 of 37 reviewed migration files mentioning that column.
M2. Balance truth is ambiguous
Document balances, wallet balances, bank snapshots, and accounting balances need separate roles.
M3. Reconciliation needs lineage
A match flag is not enough for bank feed, ledger, actor, confidence, and unmatch history.
H4. Line writer bypass risk
Generic ledger line writers do not provide the same aggregate balance gate visible in journal posting.
M4. Idempotency is local
One webhook check is not a tenant-wide financial posting idempotency contract.
Rebuild Target
| Layer | Responsibility | Core tables |
|---|---|---|
| Operational documents | Invoice, bill, expense, payroll, tax, wallet transfer, bank import. | Domain tables and document state. |
| Posting engine | Translate durable financial events through versioned posting requests into balanced immutable transactions. | financial_events, posting_requests, ledger_transactions, ledger_entries. |
| Projections | Trial balance, general ledger, AR/AP aging, dashboard balances, tax and reconciliation views. | Queries, materialized projections, and caches with rebuild rules. |
Logical schema alignment
The original short schema sketch is no longer the complete recommendation. The proposed schema explorer is the detailed logical companion. It separates concerns that the simulator and wallet review showed must not be collapsed:
| Boundary | Proposed tables | Reason |
|---|---|---|
| Tenant, branch, and periods | businesses, branches, fiscal_periods, accounts | Make SME ownership, branch allocation, account policy, and period locks explicit. |
| Event and posting contract | financial_events, posting_requests, ledger_transactions, ledger_entries | Separate durable business facts, versioned validation, balanced transaction ownership, and immutable lines. |
| Wallet and e-payment | wallets, wallet_transactions, payment_intents, payment_settlements | Keep requested payment, provider-confirmed money movement, operational wallet statement, and accounting posting distinct but linked. |
| External evidence | bank_statement_lines, reconciliation_matches | Preserve imported evidence and auditable partial/many-to-many matching without mutating the ledger. |
| Migration controls | migration_batches, legacy_record_mappings, migration_exceptions | Make transformations repeatable, trace every source record, and quarantine ambiguity instead of silently coercing it. |
| Reliability and audit | outbox_events, audit_events | Publish committed outcomes reliably and retain append-only actor/control evidence. |
This is a proposed logical model, not approved migration SQL. Physical types, partitioning, retention, regulatory controls, and final ownership boundaries remain design decisions.
Database protection boundary
- Ordinary application roles cannot write, update, or delete ledger entries directly.
- One privileged posting operation writes the transaction header, complete entry set, source state, wallet movement where applicable, and outbox intent atomically.
- Foreign keys, checks, and unique constraints enforce tenant/account scope, valid sides, positive minor-unit amounts, event identity, provider identity, and idempotency.
- Debit/credit equality is validated across the complete entry set inside the posting boundary before commit. It should not be described as a trivial row-level SQL
CHECK. - Posted rows are append-only; corrections create linked reversals and replacements.
Required invariants
- Posted transactions have at least two entries.
- Debits equal credits in functional currency.
- Money uses fixed precision or integer minor units.
- Posted entries are not updated or deleted.
- Corrections use reversals or adjustments.
- Idempotency keys prevent duplicate posting.
- Source state and posting state change atomically.
- Closed period rules are explicit.
- Reconciliation matches do not mutate posted amount history.
- Wallet caches reconcile to their ledger control accounts.
- Every migrated record has target lineage or a reviewed exception.
Financial Event Catalog
The rebuild should lock event contracts before locking table names.
| Family | Examples the current code implies |
|---|---|
| Sales and revenue | Invoice issued, payment allocated, credit applied, discount, sales tax, withholding. |
| Purchases and spend | Bill recognized, bill settled, expense paid, supplier withholding, prepayment and amortization. |
| Cash movement | Bank transfer, wallet top-up, payout, deposit, bank sync, reconciliation adjustment. |
| Payroll and statutory | Payroll accrued, salary paid, PAYE/SSNIT/tier liabilities settled, deductions, reimbursements. |
| Inventory | Stock received, cost recognized, stock adjustment, stock taking variance. |
| Controls | Opening balance, journal post, reversal, adjustment, period lock, reconciliation match. |
Migration Questions
- Does every existing
ledger_idresolve toliesureenstries.id? - Can existing source records be grouped into balanced posting sets?
- Are there posting rows with both sides populated, neither side populated, negatives, or soft deletes?
- Which document balance columns are accounting truth, workflow convenience, or provider snapshots?
- Which flows can duplicate posts after retries or repeated user actions?
- How do taxes, withholding, payroll liabilities, FX differences, and inventory costing post today?
- Can each legacy source and commit row be classified as directly mapped, commit-grouped, heuristically grouped, or unresolved?
- Do operational wallet balances and provider settlements reconcile to wallet-linked ledger rows by business, currency, and cut-off?
Migration stance: preserve domain knowledge from the current system, but do not make the current posting schema the rebuild target. Every source record must produce target lineage, an explicit skip reason, or a reviewed migration exception.
Practical Verdict
The next characterization phase should document each current module's trigger, source state, ledger grouping, account rules, mutation/correction path, branch and currency behavior, retry behavior, and live-data exceptions before implementation tickets are generated.