Skip to content

Integration System

This document describes the integration system architecture for managing third-party integrations (QuickBooks, Companies House, HMRC) per project.

Table of Contents

  1. Overview
  2. Architecture
  3. IMPORTANT: Client vs Server Processor Architecture
  4. Data Model
  5. Auto-Enable Logic
  6. Sync Features
  7. Credential Management
  8. Permissions
  9. Menu Structure
  10. Supported Providers

Overview

The integration system allows projects to connect with external services for data synchronisation. It supports:

  • Provider registry - Developer-seeded catalogue of available integrations
  • Per-project connections - Each project independently enables/disables integrations
  • Country-based auto-enable - Integrations automatically activate based on project country
  • Hierarchical sync features - Each provider exposes configurable sync features with triggers
  • Credential storage - Secrets stored in Supabase Vault (service_role only)
  • Entity mapping - Links internal entities to external system IDs
  • Reference data caching - Local cache of external lookup data (chart of accounts, tax codes, etc.)

Architecture

integration_providers (seed data)
  └── integration_provider_countries (country support)
  └── integration_features (sync capabilities)

project_integrations (per-project connection)
  └── integration_credentials (Vault-stored secrets)
  └── project_integration_features (enabled features + triggers)
  └── integration_sync_logs (audit trail)
  └── integration_entity_mappings (internal ↔ external ID links)
  └── integration_reference_data (cached external lookups)

IMPORTANT: Client vs Server Processor Architecture

Each integration provider follows a 4-file pattern that separates pure/synchronous logic (client) from async/server logic (server). This split exists because some operations must run instantly in the browser (e.g., validating whether a "Send To" button should be enabled) while others require database access, OAuth tokens, or external API calls.

File Structure Per Provider

Using QuickBooks as the example (services/integration/quickbooks/):

File Registry Function Execution Context Characteristics
clientProcessor.ts registerClientProcessor() Browser (client) Registers pure/sync functions in the client registry
clientService.ts N/A (called by clientProcessor) Browser (client) Implements pure/sync validation functions with zero server dependencies
processor.ts registerProcessor() Server Registers async functions in the server registry
service.ts N/A (called by processor) Server Implements async server functions (API calls, DB mutations, sync operations)

Global Registries

The two registries live at the integration service root:

  • services/integration/clientProcessor.ts -- Client processor registry. Collects all provider client processors. Imported by UI components.
  • services/integration/processor.ts -- Server processor registry. Collects all provider server processors. Imported by server actions and API routes.

When to Use Which

Client processors -- Use for anything that must be instant and run in the browser with no async/DB/API calls:

  • UI validation before enabling a "Send To" button
  • Account category filtering for dropdowns
  • Any pure function that derives a result from in-memory data

Server processors -- Use for anything that needs database access, OAuth tokens, or external API calls:

  • Actual sync operations (push vendor to QuickBooks, push transaction, etc.)
  • Reference data fetching (chart of accounts, tax codes, classes)
  • Hash verification to detect out-of-sync records
  • Connection testing

QuickBooks Registered Actions

Client (3 actions):

  • validate_relationship_send_to -- Checks if a relationship has all required fields to sync
  • validate_transaction_send_to -- Checks if a transaction has all required fields to sync
  • payment_account_categories -- Returns valid account categories for payment method dropdowns

Server (8 actions):

  • testConnection -- Verify OAuth connection is alive
  • qb_company_info -- Fetch company info from QuickBooks
  • qb_chart_of_accounts -- Sync chart of accounts reference data
  • qb_classes -- Sync classes reference data
  • qb_tax_codes -- Sync tax codes reference data
  • sync_vendor -- Push a vendor/supplier to QuickBooks
  • sync_transaction -- Push a transaction (expense/bill) to QuickBooks
  • verify_*_hash -- Check if local data matches what was last synced

Data Flow: "Send To" Action

The following shows how both registries work together for a typical "Send To" flow:

[Browser]                                           [Server]
    |                                                   |
    |  1. User opens transaction detail                 |
    |  2. UI calls clientProcessor:                     |
    |     validate_transaction_send_to(transaction)     |
    |     --> pure/sync, instant                        |
    |  3. Returns { valid: false,                       |
    |       errors: ["Missing account mapping"] }       |
    |  4. "Send To" button stays DISABLED               |
    |                                                   |
    |  ... user fixes the mapping ...                   |
    |                                                   |
    |  5. UI calls clientProcessor again                |
    |     --> { valid: true, errors: [] }               |
    |  6. "Send To" button becomes ENABLED              |
    |                                                   |
    |  7. User clicks "Send To"                         |
    |  --------server action call---------------------->|
    |                                                   |  8. Server calls processor:
    |                                                   |     sync_transaction(transaction)
    |                                                   |     --> async: reads DB, calls QBO API,
    |                                                   |        writes entity mapping + sync log
    |  9. UI shows success/error result                 |
    |<-------------------------------------------------|

This separation keeps the UI responsive (no server round-trips for validation) while keeping secrets and mutations safely on the server.

Data Model

Enums

Enum Values Description
integration_auth_method oauth2, api_key, basic_auth, saml, jwt, certificate, webhook, none Auth method used by a provider
integration_credential_scope global, per_project Whether credentials are shared or per-project
integration_connection_status Connected, Disconnected, Error, Expired, Pending Project integration connection state
sync_trigger_type on_status_change, on_manual, on_schedule, on_link, realtime What triggers a sync operation
sync_status pending, in_progress, success, partial_success, failed Status of a sync run
sync_direction inbound, outbound, bidirectional Data flow direction

Key Tables

Table Purpose
integration_providers Registry of available integrations (seed data)
integration_provider_countries Which countries each provider supports
integration_features Sync capabilities per provider (e.g., "Sync Invoices")
project_integrations Per-project integration connections
integration_credentials Encrypted credentials (service_role access only)
project_integration_features Per-project feature enable/disable with trigger config
integration_sync_logs Audit log of every sync operation
integration_entity_mappings Maps internal entity IDs to external system IDs
integration_reference_data Cached external lookup data (chart of accounts, tax codes)

Views

View Purpose
v_project_integrations Project integrations joined with provider details and country codes
v_integration_sync_logs Sync logs with provider and feature names for UI display

Auto-Enable Logic

When a project is created or its country_code changes, the auto_enable_integrations() trigger function:

  1. On INSERT: Finds providers where auto_enable_for_country = true and the provider's country list includes the project's country. Creates project_integrations and enables all features with default triggers.

  2. On UPDATE (country change):

  3. Disables auto-enabled integrations whose providers no longer cover the new country
  4. Enables integrations for providers that cover the new country
  5. Auto-enables features for newly activated integrations

Sync Features

Each integration provider defines features — discrete sync capabilities such as:

  • Sync Chart of Accounts
  • Sync Invoices
  • Sync Payments
  • Lookup Company Profile

Features have:

  • Default trigger (sync_trigger_type): What event triggers the sync
  • Default trigger config (JSONB): Trigger-specific settings (e.g., cron schedule, status values)
  • Sync direction: inbound, outbound, or bidirectional
  • Entity type: Which internal entity type this feature relates to

Per-project features (project_integration_features) can override the trigger and config.

Trigger Config Schema

The default_trigger_config JSONB follows a consistent entity + filter pattern:

{
  "entity": "project_relationships",
  "filter": {
    "entity_type": "Business",
    "role_can_be_vendor": true
  }
}
Feature Entity Filter
Vendors project_relationships entity_type: "Business", role_can_be_vendor: true
Expenses transactions transaction_type: ["Expense"], transaction_direction: "inbound", status: "Approved"
Bills transactions transaction_type: ["Invoice", "Payroll Invoice", "Reimbursement"], transaction_direction: "inbound", status: "Approved"

Trigger Types

Trigger Description
on_status_change Sync when a record's status changes to a specified value
on_manual User-initiated sync only
on_schedule Cron-based periodic sync
on_link Sync when an entity is linked to an external ID
realtime Continuous sync via webhooks or polling

Credential Management

  • Credentials are stored in integration_credentials with RLS denying all authenticated access
  • Only service_role can read/write credentials (bypasses RLS)
  • Supports both global (shared across projects) and per_project scopes
  • OAuth tokens include access_token, refresh_token, token_expires_at, and scopes
  • Non-OAuth credentials stored as encrypted JSONB

Permissions

Permission Key Description
integration:manage Enable/disable integrations, configure sync features
integration:logs:view View sync logs for all integrations in a project
integration:sync:quickbooks Trigger manual syncs for QuickBooks
integration:sync:companies_house Trigger manual syncs for Companies House
integration:sync:hmrc Trigger manual syncs for HMRC

All permissions are assigned to the project:owner role by default.

Under Setup > Integrations:

Setup
  └── Integrations (icon: Plug, order: 35)
        ├── Configuration (icon: Settings, order: 10, permission: integration:manage)
        └── Logs (icon: ScrollText, order: 20, permission: integration:logs:view)

Supported Providers

QuickBooks Online

  • Auth: OAuth 2.0
  • Scope: Per-project
  • Countries: GB, US, CA, AU, IE
  • Features: Chart of Accounts sync, Invoice sync, Payment sync, Supplier/Customer sync

Companies House (UK)

  • Auth: API Key
  • Scope: Global (shared across all UK projects)
  • Countries: GB
  • Features: Company profile lookup, Officer lookup, Filing history, PSC data
  • SIC Codes Reference: UK SIC Codes

HMRC (UK)

  • Auth: OAuth 2.0
  • Scope: Per-project
  • Countries: GB
  • Features: VAT return submission, Corporation Tax reference, PAYE data sync