Integration System Tables¶
Integration Providers (integration_providers)¶
Purpose: Registry of available third-party integrations. Developer seed data only.
Use Case Example: Stores provider definitions for QuickBooks, Companies House, and HMRC with their authentication configuration and API endpoints.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| code | TEXT | Machine-readable unique key (e.g., quickbooks, companies_house, hmrc) |
| name | TEXT | Human-readable display name |
| description | TEXT | Description of what this integration provides |
| logo_attachment_id | UUID | FK to attachments table for provider logo (nullable, ON DELETE SET NULL) |
| auth_method | ENUM | Authentication method: integration_auth_method |
| credential_scope | ENUM | Whether credentials are global or per-project: integration_credential_scope |
| oauth_config | JSONB | OAuth configuration (URLs, scopes, grant_type). NULL for non-OAuth providers |
| auth_config | JSONB | Configuration template for non-OAuth auth (field definitions, NOT actual secrets) |
| auto_enable_for_country | BOOLEAN | Whether to automatically enable this integration when a project matches a supported country |
| base_url | TEXT | Production API base URL |
| sandbox_base_url | TEXT | Sandbox/development API base URL. Used when NODE_ENV !== production |
| documentation_url | TEXT | URL to external API documentation |
| developer_portal_url | TEXT | URL to the developer portal for creating OAuth apps |
| developer_portal_label | TEXT | Custom label for the developer portal link |
| setup_instructions | JSONB | Array of setup steps [{label, url}] for credential configuration |
| entity_urls | JSONB | Map of entity type to production URL template (e.g., {account, bill, vendor}). Supports {companyId}, {entityId} placeholders |
| sandbox_entity_urls | JSONB | Map of entity type to sandbox URL template. Same structure as entity_urls. Falls back to entity_urls when NULL |
| provider_type | ENUM | Provider category: integration_provider_type (accounting, government) |
| is_active | BOOLEAN | Whether this provider is currently available (default: true) |
| display_order | INTEGER | Display order in the UI (default: 0) |
| created_at | TIMESTAMPTZ | Timestamp when the provider was created |
| updated_at | TIMESTAMPTZ | Timestamp when the provider was last updated |
Constraints: uq_integration_providers_code UNIQUE(code)
Indexes: idx_integration_providers_code (code), idx_integration_providers_active (is_active), idx_integration_providers_logo (logo_attachment_id)
RLS: Enabled. Authenticated users can SELECT all providers (read-only).
Seed Data: Companies House (api_key, global, auto-enable for GB), HMRC (oauth2, global, auto-enable for GB), QuickBooks (oauth2, per_project, manual enable)
Integration Provider Countries (integration_provider_countries)¶
Purpose: Junction table linking integration providers to supported countries.
Use Case Example: Companies House and HMRC are linked to GB, while QuickBooks has no country restrictions.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| integration_provider_id | UUID | FK to integration_providers (ON DELETE CASCADE) |
| country_code | TEXT | FK to countries table |
| created_at | TIMESTAMPTZ | Timestamp when the link was created |
Constraints: uq_provider_country UNIQUE(integration_provider_id, country_code)
Indexes: idx_ipc_integration_provider (integration_provider_id), idx_provider_countries_country (country_code)
RLS: Enabled. Authenticated users can SELECT all records (read-only).
Integration Features (integration_features)¶
Purpose: Sync features/points per integration provider. Self-referencing for hierarchy (e.g., Transactions > Expenses). Developer seed data.
Use Case Example: QuickBooks has features like "Chart of Accounts" (inbound, on_link), "Vendors" (outbound, on_status_change), and "Transactions > Expenses" (hierarchical, outbound).
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| integration_provider_id | UUID | FK to integration_providers (ON DELETE CASCADE) |
| parent_integration_feature_id | UUID | FK to integration_features for hierarchy. NULL = top-level feature (ON DELETE CASCADE) |
| code | TEXT | Machine-readable code, unique within provider |
| name | TEXT | Human-readable display name |
| description | TEXT | Description of what this feature syncs |
| sync_direction | ENUM | Direction of data flow: sync_direction (default: 'bidirectional') |
| default_trigger | ENUM | Default trigger type when feature is enabled: sync_trigger_type (default: 'on_manual') |
| default_trigger_config | JSONB | Default configuration for the trigger (e.g., {"entity": "transaction", "status": "Approved"}) |
| supported_triggers | sync_trigger_type[] | Array of allowed trigger types for this feature (default: ARRAY['on_manual']) |
| run_on_link | BOOLEAN | Whether to run this feature immediately when integration is linked (one-time initial sync, default: false) |
| api_endpoint | TEXT | API endpoint path (appended to provider base_url). Supports {{companyNumber}} and {{targetVrn}} placeholders |
| feature_category | ENUM | integration_feature_category ('tax_validation', 'company_validation', 'company_details'). NULL for non-categorized features |
| is_active | BOOLEAN | Whether this feature is currently available (default: true) |
| display_order | INTEGER | Display order in the UI (default: 0) |
| created_at | TIMESTAMPTZ | Timestamp when the feature was created |
| updated_at | TIMESTAMPTZ | Timestamp when the feature was last updated |
Constraints: uq_integration_features_provider_code UNIQUE(integration_provider_id, code)
Indexes: idx_if_integration_provider (integration_provider_id), idx_if_parent_integration_feature (parent_integration_feature_id), idx_integration_features_active (is_active)
RLS: Enabled. Authenticated users can SELECT all features (read-only).
Integration Feature Dependencies (integration_feature_dependencies)¶
M:N dependency relationships between integration features within the same provider. A feature cannot be enabled unless all its prerequisites are enabled.
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Unique identifier for the dependency |
| integration_feature_id | UUID | FK to integration_features — the dependent feature (ON DELETE CASCADE) |
| depends_on_integration_feature_id | UUID | FK to integration_features — the prerequisite feature (ON DELETE CASCADE) |
| created_at | TIMESTAMPTZ | Timestamp when the dependency was created (DEFAULT NOW()) |
Constraints: uq_feature_dependency UNIQUE(integration_feature_id, depends_on_integration_feature_id), chk_no_self_dependency CHECK(integration_feature_id != depends_on_integration_feature_id)
Indexes: idx_ifd_integration_feature (integration_feature_id), idx_ifd_depends_on_integration_feature (depends_on_integration_feature_id)
RLS: Enabled. Authenticated and anonymous users can SELECT (read-only reference data).
Seed Data (QuickBooks):
qb_expensesdepends onqb_vendorsqb_expensesdepends onqb_chart_of_accountsqb_billsdepends onqb_vendorsqb_billsdepends onqb_chart_of_accounts
Project Integrations (project_integrations)¶
Purpose: Tracks which integrations are enabled per project and their connection status.
Use Case Example: A UK film project auto-enables Companies House and HMRC on creation. QuickBooks is manually linked when the production accountant connects their account.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_id | UUID | FK to projects (ON DELETE CASCADE) |
| integration_provider_id | UUID | FK to integration_providers (ON DELETE RESTRICT) |
| organisation_id | UUID | FK to organisations, used for RLS scoping (ON DELETE CASCADE) |
| connection_status | ENUM | Current connection status: integration_connection_status (default: 'Disconnected') |
| is_auto_enabled | BOOLEAN | Whether this integration was automatically enabled based on project country |
| is_active | BOOLEAN | Soft disable flag (default: true) |
| connected_at | TIMESTAMPTZ | When the integration was last connected |
| disconnected_at | TIMESTAMPTZ | When the integration was last disconnected |
| external_account_id | TEXT | Account ID in the external system (e.g., QuickBooks realm_id) |
| external_account_name | TEXT | Account name in the external system |
| external_account_country | TEXT | ISO-3166 alpha-2 country of the connected account (e.g. the QuickBooks company country), extracted from the company-details payload at connect time. Advisory — compared against projects.country_code to warn on mismatch. |
| external_metadata | JSONB | Provider-specific metadata |
| connected_by_user_id | UUID | FK to public.users — User who connected this integration |
| disconnected_by_user_id | UUID | FK to public.users — User who disconnected this integration |
| created_at | TIMESTAMPTZ | Timestamp when the record was created |
| updated_at | TIMESTAMPTZ | Timestamp when the record was last updated |
| created_by_user_id | UUID | User who created this record (default: auth.uid()) |
| updated_by_user_id | UUID | User who last updated this record (default: auth.uid()) |
Constraints: uq_project_integration UNIQUE(project_id, integration_provider_id)
Indexes: idx_project_integrations_project (project_id), idx_pi_integration_provider (integration_provider_id), idx_project_integrations_organisation (organisation_id), idx_project_integrations_status (connection_status), idx_project_integrations_active (is_active), idx_project_integrations_connected_by_user_id (connected_by_user_id), idx_project_integrations_disconnected_by_user_id (disconnected_by_user_id)
RLS: Enabled. SELECT for authenticated users with project access via v_user_accessible_projects. INSERT, UPDATE, DELETE additionally require caller_has_permission('integration:manage', project_id) — project membership alone is not sufficient to write.
Integration Credentials (integration_credentials)¶
Purpose: Stores vault secret IDs for integration credentials. NEVER stores plaintext tokens or passwords.
Use Case Example: After OAuth2 flow with QuickBooks, the access and refresh token vault secret UUIDs are stored here alongside token expiry metadata.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_integration_id | UUID | FK to project_integrations. NULL for global credentials (ON DELETE CASCADE) |
| integration_provider_id | UUID | FK to integration_providers (ON DELETE RESTRICT) |
| credential_scope | ENUM | Whether this credential is global or per-project: integration_credential_scope |
| access_token_secret_id | UUID | Vault secret UUID for OAuth2 access token |
| refresh_token_secret_id | UUID | Vault secret UUID for OAuth2 refresh token |
| client_id_secret_id | UUID | Vault secret UUID for OAuth2 client_id |
| client_secret_secret_id | UUID | Vault secret UUID for OAuth2 client_secret |
| token_expires_at | TIMESTAMPTZ | When the access token expires |
| token_refreshed_at | TIMESTAMPTZ | When the token was last refreshed |
| token_scopes | TEXT[] | Granted OAuth scopes |
| api_key_secret_id | UUID | Vault secret UUID for API key |
| username_secret_id | UUID | Vault secret UUID for basic auth username |
| password_secret_id | UUID | Vault secret UUID for basic auth password |
| additional_secrets | JSONB | Maps field names to vault secret UUIDs for extensibility |
| name | TEXT | Human-readable name for the credential set (e.g., "Ecobride QuickBooks") |
| can_be_shared | BOOLEAN | Whether credential can be copied to other projects in same org (default: true) |
| is_active | BOOLEAN | Whether this credential is currently active (default: true) |
| created_at | TIMESTAMPTZ | Timestamp when the record was created |
| updated_at | TIMESTAMPTZ | Timestamp when the record was last updated |
| created_by_user_id | UUID | User who created this record (default: auth.uid()) |
| updated_by_user_id | UUID | User who last updated this record (default: auth.uid()) |
Indexes: idx_integration_credentials_project_integration (project_integration_id), idx_ic_integration_provider (integration_provider_id), uq_integration_credentials_global_active UNIQUE(integration_provider_id) WHERE credential_scope = 'global' AND is_active = true
RLS: Enabled. Authenticated users can SELECT limited columns (id, project_integration_id, integration_provider_id, credential_scope, is_active, name, can_be_shared, and vault secret UUID references) for credential existence checks. Actual secret values are only accessible via service_role through the Vault. All credential mutations must go through service_role.
Project Integration Features (project_integration_features)¶
Purpose: Tracks which sync features are enabled per project integration with trigger configuration.
Use Case Example: A project's QuickBooks integration has "Vendors" feature enabled with on_status_change trigger configured to fire when entity status changes to "Active".
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_integration_id | UUID | FK to project_integrations (ON DELETE CASCADE) |
| integration_feature_id | UUID | FK to integration_features (ON DELETE RESTRICT) |
| is_enabled | BOOLEAN | Whether this feature is currently enabled (default: true) |
| sync_trigger | ENUM | Primary trigger type (copied from feature default, user-overridable): sync_trigger_type |
| trigger_config | JSONB | Trigger parameters (e.g., {"entity": "transaction", "status": "Approved"} for on_status_change) |
| last_sync_at | TIMESTAMPTZ | When this feature was last synced |
| last_sync_status | ENUM | Status of the last sync operation: sync_status |
| next_scheduled_sync_at | TIMESTAMPTZ | When the next scheduled sync should run (for on_schedule triggers) |
| is_active | BOOLEAN | Soft disable flag (default: true) |
| created_at | TIMESTAMPTZ | Timestamp when the record was created |
| updated_at | TIMESTAMPTZ | Timestamp when the record was last updated |
| created_by_user_id | UUID | User who created this record (default: auth.uid()) |
| updated_by_user_id | UUID | User who last updated this record (default: auth.uid()) |
Constraints: uq_project_integration_feature UNIQUE(project_integration_id, integration_feature_id)
Indexes: idx_pif_project_integration (project_integration_id), idx_pif_integration_feature (integration_feature_id), idx_pif_next_sync (next_scheduled_sync_at) WHERE next_scheduled_sync_at IS NOT NULL, idx_pif_enabled (is_enabled)
RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects. INSERT, UPDATE additionally require caller_has_permission('integration:manage', project_id) on the parent project integration's project — project membership alone is not sufficient to write.
Integration Sync Logs (integration_sync_logs)¶
Purpose: Audit trail for all sync operations. Append-mostly table.
Use Case Example: Records every sync operation including trigger type, direction, record counts, errors, and duration for troubleshooting and monitoring.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_integration_id | UUID | FK to project_integrations (ON DELETE CASCADE) |
| project_integration_feature_id | UUID | FK to project_integration_features. NULL for full-integration syncs (ON DELETE SET NULL) |
| sync_trigger | ENUM | What triggered this sync: sync_trigger_type |
| sync_direction | ENUM | Direction of data flow: sync_direction |
| status | ENUM | Current status: sync_status (default: 'pending') |
| started_at | TIMESTAMPTZ | When the sync started (default: NOW()) |
| completed_at | TIMESTAMPTZ | When the sync completed |
| duration_ms | INTEGER | Duration of the sync in milliseconds |
| records_processed | INTEGER | Total records processed (default: 0) |
| records_created | INTEGER | Records created during sync (default: 0) |
| records_updated | INTEGER | Records updated during sync (default: 0) |
| records_failed | INTEGER | Records that failed to sync (default: 0) |
| records_skipped | INTEGER | Records skipped during sync (default: 0) |
| error_message | TEXT | Error message if sync failed |
| error_details | JSONB | Detailed error information (stack trace, API response) |
| webhook_event_type | TEXT | Webhook event type that triggered this sync |
| webhook_payload_hash | TEXT | Hash of webhook payload for deduplication |
| request_metadata | JSONB | Sanitized request details |
| response_metadata | JSONB | Response summary |
| triggered_by_user_id | UUID | User who triggered this sync. NULL for system triggers (FK to users) |
| created_at | TIMESTAMPTZ | Timestamp when the record was created |
Indexes: idx_sync_logs_project_integration (project_integration_id), idx_sync_logs_feature (project_integration_feature_id), idx_sync_logs_status (status), idx_sync_logs_started_at (started_at), idx_sync_logs_triggered_by (triggered_by_user_id)
RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects. Writes via service_role only.
Integration Entity Mappings (integration_entity_mappings)¶
Purpose: Maps Delta records to external system records for sync continuity.
Use Case Example: A Delta entity (supplier) is mapped to a QuickBooks Vendor with external_id "123", allowing bidirectional updates and conflict detection via sync_hash.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_integration_id | UUID | FK to project_integrations (ON DELETE CASCADE) |
| entity_type | TEXT | Delta table/entity name (e.g., entities, transactions, transaction_items) |
| entity_id | UUID | Delta record UUID |
| external_id | TEXT | ID in the external system |
| external_type | TEXT | Type in the external system (e.g., Customer, Invoice, Account) |
| external_metadata | JSONB | Additional reconciliation data from external system |
| last_synced_at | TIMESTAMPTZ | When this mapping was last synced |
| sync_token | TEXT | External system version token (e.g., QuickBooks SyncToken). Per-record tracking |
| sync_hash | TEXT | Hash of last synced data for change detection |
| is_out_of_sync | BOOLEAN | True when our sync_token is stale compared to external system (default: false) |
| created_at | TIMESTAMPTZ | Timestamp when the mapping was created |
| updated_at | TIMESTAMPTZ | Timestamp when the mapping was last updated |
Constraints: uq_entity_mapping_internal UNIQUE(project_integration_id, entity_type, entity_id), uq_entity_mapping_external UNIQUE(project_integration_id, external_id, external_type)
Indexes: idx_entity_mappings_project_integration (project_integration_id), idx_entity_mappings_entity (entity_type, entity_id), idx_entity_mappings_external (external_id, external_type), idx_entity_mappings_out_of_sync (is_out_of_sync) WHERE is_out_of_sync = true
RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects.
Integration Reference Data (integration_reference_data)¶
Purpose: Generic table for reference data pulled from integrations (Chart of Accounts, Tax Codes, Classes, Exchange Rates, etc.).
Use Case Example: After connecting QuickBooks, the Chart of Accounts is pulled and stored here with reference_type = 'chart_of_accounts', enabling line item categorization in Delta.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| project_integration_id | UUID | FK to project_integrations (ON DELETE CASCADE) |
| reference_type | TEXT | Type of reference data (e.g., chart_of_account, tax_code, tracking_category, exchange_rate) |
| external_id | TEXT | ID in the external system |
| code | TEXT | Account/tax code (e.g., 4000, VAT20) |
| name | TEXT | Display name (e.g., Sales Revenue, Standard Rate VAT) |
| description | TEXT | Description of the reference data entry |
| parent_external_id | TEXT | Parent external ID for hierarchical data (e.g., chart of accounts parent) |
| category | TEXT | Classification (e.g., Revenue, Expense, Asset, Liability) |
| is_active | BOOLEAN | Whether this entry is active in the external system (default: true) |
| metadata | JSONB | Type-specific additional fields as JSONB |
| sync_token | TEXT | External system version token |
| last_synced_at | TIMESTAMPTZ | When this entry was last synced |
| created_at | TIMESTAMPTZ | Timestamp when the record was created |
| updated_at | TIMESTAMPTZ | Timestamp when the record was last updated |
Constraints: uq_reference_data UNIQUE(project_integration_id, reference_type, external_id)
Indexes: idx_reference_data_project_integration (project_integration_id), idx_reference_data_type (reference_type), idx_reference_data_active (is_active), idx_reference_data_parent (parent_external_id), idx_reference_data_category (category)
RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects.
Tax Rate Mappings (tax_rate_mappings)¶
Purpose: Maps internal tax rates to external integration tax codes. Provider-agnostic — one mapping per tax rate per project integration.
Use Case Example: Map Delta's "Standard Rate 20%" tax rate to QuickBooks Online's "20.0% S" tax code for a specific project integration, enabling correct tax code assignment when syncing transactions.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier (auto-generated) |
| tax_rate_id | UUID | FK to tax_rates (ON DELETE CASCADE) |
| integration_reference_data_id | UUID | FK to integration_reference_data — the external tax code (ON DELETE CASCADE) |
| project_integration_id | UUID | FK to project_integrations — scopes the mapping (ON DELETE CASCADE) |
| created_at | TIMESTAMPTZ | Timestamp when the mapping was created |
| updated_at | TIMESTAMPTZ | Timestamp when the mapping was last updated |
| created_by_user_id | UUID | User who created the mapping (default: auth.uid()). FK to public.users |
| updated_by_user_id | UUID | User who last updated the mapping (default: auth.uid()). FK to public.users |
Constraints: uq_tax_rate_mapping UNIQUE(tax_rate_id, project_integration_id)
Indexes: idx_tax_rate_mappings_tax_rate_id, idx_tax_rate_mappings_reference_data_id, idx_tax_rate_mappings_project_integration_id, idx_tax_rate_mappings_created_by_user_id, idx_tax_rate_mappings_updated_by_user_id
RLS: Enabled. SELECT, INSERT, UPDATE, DELETE for authenticated users with project access via project_integrations -> v_user_accessible_projects.