Organization Integration
Data Entity
Description
Stores the configuration for a connection between the platform and an external system (Xledger accounting, HLF Dynamics portal, Bufdir API, member management systems). Each record includes integration type, encrypted credential references, field mapping JSONB, and sync schedule. Only one active integration per type per organization is permitted.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key generated on creation. Used as the stable reference for credential vault entries, sync schedule FK, and audit log correlation. | PKrequiredunique |
organization_id |
uuid |
Foreign key to the organizations table. All queries are scoped by this column via Supabase RLS. Determines which org owns this integration configuration. | required |
integration_type |
enum |
Identifies the external system this record configures. Drives adapter resolution in rest-api-adapter-registry and determines which credential fields and field-mapping schemas are required. | required |
is_active |
boolean |
Soft-activation flag. The system enforces a uniqueness constraint on (organization_id, integration_type) where is_active = true, ensuring only one live configuration per type per org. Setting to false deactivates without deleting history. | required |
field_mappings |
json |
JSONB column storing an array of FieldMapping objects. Each entry maps an internal data field name to the external system's field name and optional format/transform specification. Consumed by field-mapping-resolver when serializing export payloads for Xledger or Dynamics. | - |
credential_references |
json |
JSONB map of credential key names to Supabase Vault secret IDs. Plaintext credentials are never stored here; only opaque vault references. Example: {"api_key": "vault-secret-uuid-1", "client_secret": "vault-secret-uuid-2"}. Resolved at runtime by integration-credential-vault. | - |
sync_schedule_config |
json |
Embedded sync schedule configuration stored as JSONB. Contains frequency (daily, weekly, monthly, manual_only), preferred_time_utc (HH:MM), next_scheduled_at (ISO datetime), and last_execution_outcome (success, failed, partial, never_run). Kept embedded to avoid a separate table join for the common read path. | - |
last_sync_at |
datetime |
UTC timestamp of the most recent completed sync execution, regardless of outcome. Null if a sync has never been attempted. Updated by sync-scheduler on every execution cycle. | - |
last_sync_status |
enum |
Outcome of the most recent sync run. Used in the admin dashboard to indicate whether the last automated or manual sync succeeded, partially completed, or failed. | - |
health_status |
enum |
Current connectivity health of the integration endpoint as assessed by the most recent health check. Displayed with a status indicator on the integration configuration dashboard. Updated by integration-health-monitor independently of sync runs. | required |
health_checked_at |
datetime |
UTC timestamp of the most recent health check probe. Null if no health check has been performed since configuration. Allows the admin dashboard to show staleness of the health indicator. | - |
connection_test_status |
enum |
Outcome of the most recent admin-triggered connection test (distinct from periodic health checks). Used to confirm credentials are valid before first live sync. | required |
connection_tested_at |
datetime |
UTC timestamp when the most recent credential connection test was run. Null if credentials have never been tested. | - |
excluded_features |
json |
JSONB array of feature flag key strings that are excluded from this integration's scope. Primarily used for the HLF Dynamics integration where certain platform features must not be synced to avoid conflicts with the Dynamics portal's own data ownership. | - |
configuration_version |
integer |
Optimistic concurrency counter incremented on every update to field_mappings, sync_schedule_config, or is_active. Prevents stale-write races when two admin sessions edit the same integration simultaneously. | required |
display_name |
string |
Human-readable label for this integration shown in the admin dashboard. Defaults to a formatted version of integration_type but can be customized per organization (e.g., 'Xledger Regnskap' vs 'Accounting Export'). | - |
created_at |
datetime |
UTC timestamp of record creation. Set by the database default and never modified. | required |
updated_at |
datetime |
UTC timestamp automatically updated by a database trigger on any row modification. Used for cache invalidation in integration-health-monitor and sync-scheduler. | required |
created_by |
uuid |
User ID of the org admin who created this integration record. Stored for audit trail purposes. | required |
updated_by |
uuid |
User ID of the last user to modify this record. Updated on every write operation alongside updated_at. | - |
Database Indexes
idx_organization_integration_pk
Columns: id
idx_organization_integration_org_id
Columns: organization_id
idx_organization_integration_unique_active_type
Columns: organization_id, integration_type
idx_organization_integration_health_status
Columns: health_status
idx_organization_integration_last_sync_at
Columns: last_sync_at
idx_organization_integration_type
Columns: integration_type
Validation Rules
organization_id_references_active_organization
error
Validation failed
integration_type_in_supported_set
error
Validation failed
credential_references_valid_json_map
error
Validation failed
required_credential_fields_present_for_type
error
Validation failed
field_mappings_is_array_of_mapping_objects
error
Validation failed
sync_schedule_frequency_value_valid
error
Validation failed
configuration_version_optimistic_lock
error
Validation failed
display_name_max_length
error
Validation failed
excluded_features_array_of_known_flag_keys
warning
Validation failed
Business Rules
unique_active_integration_per_type_per_org
At most one record with is_active = true may exist per (organization_id, integration_type) pair. Enforced by a partial unique index. Attempting to activate a second integration of the same type for the same org must first deactivate the existing one.
credentials_stored_in_vault_only
Plaintext API keys, client secrets, and passwords must never be written to the credential_references column. Only Supabase Vault secret IDs may be stored. The integration-config-service must call integration-credential-vault to store credentials before persisting the integration record.
integration_type_immutable_after_create
The integration_type field cannot be changed after the record is created. Changing the target system requires creating a new integration record and deactivating the old one to preserve sync history integrity.
field_mappings_validated_against_integration_type
Field mapping entries must reference source fields that exist in the internal data schema and target fields listed in the integration type's external schema definition (from integration-type-registry). Mappings referencing unknown fields are rejected with a detailed error.
credential_vault_cleanup_on_delete
When an integration record is deleted, the corresponding Supabase Vault secrets referenced in credential_references must be explicitly revoked and deleted by integration-credential-vault. Orphaned vault secrets are a security risk.
health_status_updated_on_sync_completion
After every sync execution (automated or manual), the integration-health-monitor must update health_status, health_checked_at, last_sync_at, and last_sync_status in a single atomic write to prevent partial state.
org_admin_scope_required_for_write
Only users with org_admin or super_admin roles for the owning organization_id may create, update, or delete integration records. Supabase RLS policies enforce this constraint at the database level.
sync_must_not_run_on_inactive_integration
The sync-scheduler and integration-edge-functions must check is_active = true before executing any sync job. Inactive integrations must be skipped silently without raising errors.
excluded_features_scoped_to_hlf_dynamics
The excluded_features configuration is only meaningful for the hlf_dynamics integration_type. For all other types, this field should be empty or null. The integration-config-validator warns (not errors) if excluded_features is populated for a non-Dynamics integration.
CRUD Operations
Storage Configuration
Entity Relationships
An organization may have multiple external system integrations (Xledger, Dynamics, member management) each with its own config
An integration configuration contains multiple field mapping entries defining how internal fields map to the external system schema
Each integration has exactly one sync schedule configuring its automated execution frequency and preferred time window