Opening Balance Closure Engine (TMADD 5.0)

Opening Balance Import

SpeyBooks now supports importing opening balances from a trial balance CSV — the critical first step for users switching from another accounting platform.

Upload & Auto-Map

• Smart column detection — automatically identifies Account, Debit, and Credit columns (or a single signed Amount column) from trial balance exports.
• Deterministic account mapping — source account names are matched to your SpeyBooks chart of accounts using exact match, a known dictionary of common names from Xero, FreeAgent, and QuickBooks, or fuzzy similarity. Unmapped accounts are flagged for manual resolution.
• Cutover date — defaults to the last day of the previous month. Must be a month-end date.

Balance Proof

• Hard-gated trial balance — the import cannot be confirmed unless total debits exactly equal total credits. No tolerance, no warnings, no “fix it later”.
• Rounding injection — if the imbalance is ≤ £0.05 (a common rounding artefact from other systems), a mathematically valid rounding line is automatically injected to close the balance.
• Retained Earnings required — the system does not infer or auto-create balancing entries. You must provide the Retained Earnings (or Profit and Loss Account) row explicitly.

Singleton & Immutability

• One active opening balance per organisation — enforced at the database level. A second import is structurally blocked until the first is voided.
• Locked on commit — the opening balance journal cannot be edited or deleted after confirmation. Only an explicit void restores the ability to re-import.
• Audit-safe void — voiding marks the journal with a timestamp rather than deleting it. No audit gaps.

API Endpoints

Seven new endpoints power the opening balance lifecycle:

• Check if an opening balance exists
• Upload and preview a trial balance CSV
• Retrieve a stored preview
• Update account mappings or cutover date
• Confirm (atomic commit with triple gate)
• Void an active opening balance
• Delete a pending (uncommitted) import

Database Migrations

• 063 — Adds source, locked, voided_at, and voided_by columns to support migration journals. Singleton partial unique index. Immutability triggers on journals and journal lines. Delete protection triggers.
• 064 — Extends the source type constraint to include migration.
• 065 — Allows nullable created_by on imports for API key authentication contexts.

Opening Balance Import — Frontend Wizard

A 4-step wizard for importing opening balances, matching the bank import and contact import patterns.

• Upload — drag-and-drop CSV upload with singleton guard. If an opening balance already exists, shows the active journal reference with a “Void & Re-import” option.
• Map Accounts — auto-mapped accounts shown with method badges (Exact, Dictionary, Fuzzy). Unmapped accounts get a dropdown to select from your chart of accounts. Balance proof card shows Total DR, Total CR, and delta in real time. Cutover date picker restricted to month-end dates.
• Preview — journal lines table with account code, name, type, debit, and credit columns. Rounding injection lines highlighted. Confirm button gated on balance proof — disabled unless debits equal credits.
• Done — transaction reference, balance proof confirmation, and links to the journal and reports.

Contact Import — Table Redesign

• Consolidated preview table — the 5-column layout (Action, Contact, Email, Hard Keys, Evidence) has been replaced with a 3-column layout (Action, Contact, Email) that eliminates horizontal scrolling on standard viewports. Hard keys and evidence are now stacked vertically within the Contact cell.
• Responsive email column — the Email column hides on small screens and displays inline within the Contact cell instead.

Known Issues

• The account mapping dictionary does not yet cover all common account names from Xero, FreeAgent, and QuickBooks. Some accounts that should auto-map (e.g. Wages & Salaries, Advertising, Insurance) currently appear as unmapped and require manual selection. A dictionary expansion is planned.