FreeAgent Migration Wizard & Service Architecture

Migration Wizard

SpeyBooks now has a guided migration wizard at /migration. Upload a single FreeAgent .xlsx export and the wizard walks you through a six-stage pipeline: upload, verify contacts, verify opening balances, verify outstanding invoices, verify outstanding bills, and reconciliation proof.

FreeAgent XLSX Import

• Single-file upload — drop your FreeAgent export (.xlsx) and the wizard splits it into four sheets: Contacts, Trial Balance, Outstanding Invoices, and Outstanding Bills. No manual CSV splitting required.
• Two-phase upload — contacts and opening balances are parsed immediately. Invoices and bills are deferred until the opening balance is confirmed (clearing accounts must exist first). The wizard handles this automatically.
• Provider-aware UI — the upload stage shows which sheets were found, which uploaded successfully, and which are waiting for the balance confirm.

Clearing Verification

• Real-time clearing proof — the Outstanding Invoices and Bills stages now show live data from the backend: opening balance on the clearing account, total imported documents, and the difference. Previously showed £0.00 for both.
• Backend-computed values — all monetary calculations happen server-side. The frontend displays pre-formatted pounds strings with locale-aware comma separators (£20,100.00). No pence-to-pounds conversion in the browser.
• Match/mismatch logic — if invoices don’t account for the full clearing balance, the difference is shown in red and the confirm button is disabled. When they match, you see “These match perfectly” in green.

Opening Balance Verification

• Balance proof display — total debits and credits are fetched from the backend and displayed with proper formatting. Previously showed £0.00 when accessed through the migration wizard.
• Account mapping — the auto-mapper now correctly resolves FreeAgent account names to SpeyBooks chart accounts. Fixed a data type mismatch that caused all accounts to appear unmapped.

Contact Verification

• Import statistics — the contacts stage now fetches real stats from the contact import record (8 new contacts, 0 conflicts) instead of showing “0 new contacts”.
• UCI identity resolution — duplicate key handling now uses database savepoints to prevent transaction poisoning. If a VAT number already exists, the import continues gracefully instead of failing.

Service Architecture Refactor

• Eliminated HTTP simulation — the migration orchestrator previously used fastify.inject() to call standalone upload endpoints. This caused a race condition where the injected request’s transaction could commit after the parent route tried to reference the new records, causing foreign key violations. The upload logic has been extracted into service functions that run directly within the caller’s transaction.
• Single-transaction guarantee — contacts, opening balances, invoices, and bills are all parsed and stored within a single database transaction. No timing races, no pool hopping, no manual RLS reapplication.
• Three new service modules — contact import, opening balance import, and document import (direction-agnostic for both invoices and bills). Each accepts a database client, organisation ID, and CSV buffer, and returns the import record ID.

Bug Fixes

• Fixed £0.00 display on opening balance verification — the component was reading from a preview field that is null during provider uploads. Now fetches real data from the import record.
• Fixed £0.00 display on clearing verification — same root cause. Now fetches clearing proof and outstanding totals from the backend.
• Fixed “0 new contacts” display — contact statistics were not being loaded from the import record.
• Fixed transaction poisoning on duplicate identity keys — UCI contact confirm could fail with a 25P02 error if a VAT number already existed. Wrapped identity key inserts in savepoints.
• Fixed foreign key violations on import ID attachment — eliminated the fastify.inject() timing race that caused intermittent FK constraint errors when linking import records to migrations.
• Fixed account mapping failure — the opening balance service returned database column names (snake_case) instead of the camelCase property names the mapper expected, causing all accounts to appear unmapped.
• Fixed redundant upload call after confirm — the frontend was calling the upload endpoint again after confirming contacts, which returned a validation error.

Known Issues

• Clearing verification confirm button — when invoices don’t match the clearing balance (expected for test data), the confirm button is disabled. In production, a real FreeAgent export should have matching totals. A manual override for known discrepancies is planned.
• Invoice and bill detail views — the “View invoice details” and “View bill details” expandable sections show placeholder text. Detailed line-item display is planned for a future release.
• ProofPage values — the reconciliation proof page uses frontend formatting with pence values from the validate endpoint. This works correctly but should be migrated to the same backend-formatted pattern as the other verification stages for consistency.