Integration Config
Data Entity
Description
Per-organization configuration for external system integrations including Xledger, Dynamics, Cornerstone, Consio, and HLF's Dynamics portal. Stores encrypted connector credentials, field mapping definitions, and feature flag states per connector type.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, auto-generated UUID for each integration configuration record | PKrequiredunique |
organization_id |
uuid |
Foreign key referencing the organizations table. Each organization may have multiple configs, one per connector_type | required |
connector_type |
enum |
Identifies which external system this configuration targets. Each organization may have at most one active config per connector type | required |
credentials_ref |
string |
Reference key or ARN pointing to the encrypted credentials in a secure vault (e.g., Supabase Vault secret name or external secret manager key). The actual API keys, OAuth2 tokens, or Azure AD credentials are never stored in plaintext in this table | - |
oauth2_token_cache |
json |
Server-side cache of the current OAuth2 access token and expiry timestamp for this connector. Refreshed by the gateway on expiry. Never exposed to clients | - |
field_mappings |
json |
JSON object defining how local app data model fields map to the external system's entity/field names. Structure: { 'app_field': 'external_field' }. Used by sync services to transform records before submission | - |
feature_flags |
json |
Boolean flags controlling which integration capabilities are enabled for this organization. Keys include: auto_sync_on_approval, delta_sync_enabled, webhook_enabled, manual_sync_allowed, honorarium_sync_enabled, portal_ownership_overrides. Default: all false | - |
sync_schedule |
json |
Cron-style schedule configuration for automated sync jobs. Structure: { 'full_sync_cron': '0 2 * * *', 'delta_sync_cron': '*/30 * * * *' }. Null means no scheduled sync | - |
webhook_secret |
string |
HMAC secret used to verify inbound webhook payloads from the external system (e.g., Dynamics portal, Cornerstone). Stored encrypted via Supabase Vault reference. Null if webhook delivery is not configured | - |
webhook_endpoint_url |
string |
The public HTTPS URL of the Supabase Edge Function endpoint that receives inbound webhook events from this external system | - |
base_api_url |
string |
The root API URL for the external system. Allows per-organization overrides when organizations use different regional or tenant-specific endpoints (e.g., Xledger tenant URLs) | - |
honorarium_threshold_config |
json |
Organization-specific honorarium threshold tiers used by the Honorarium Calculation Service. Structure: { 'tiers': [{ 'min_assignments': 3, 'rate': 150 }, { 'min_assignments': 15, 'rate': 300 }] }. Only relevant for connector_type where honorarium sync applies | - |
auto_approval_thresholds |
json |
Per-organization thresholds for automatic reimbursement approval. Structure: { 'max_km_auto_approve': 50, 'max_amount_auto_approve': 0, 'require_receipt_above': 100 }. Used by Auto-Approval Service | - |
is_active |
boolean |
Whether this integration configuration is currently enabled and will be used for sync operations. Setting to false disables all outbound sync without deleting the config | required |
last_verified_at |
datetime |
Timestamp of the most recent successful connectivity test against the external system. Null if never tested or if the last test failed | - |
last_sync_at |
datetime |
Timestamp of the most recent completed sync operation (full or delta). Informational; authoritative sync history is in sync_logs | - |
created_by |
uuid |
User ID of the admin who created this integration configuration. Used for audit trail purposes | - |
created_at |
datetime |
Timestamp when this configuration record was created | required |
updated_at |
datetime |
Timestamp of the most recent modification to this configuration record. Updated on every write via trigger | required |
notes |
text |
Free-text admin notes about this configuration (e.g., setup instructions, known issues, contact person at external system). Visible only to admins | - |
Database Indexes
idx_integration_configs_org_id
Columns: organization_id
idx_integration_configs_org_connector
Columns: organization_id, connector_type
idx_integration_configs_connector_active
Columns: connector_type, is_active
idx_integration_configs_updated_at
Columns: updated_at
Validation Rules
valid_connector_type_enum
error
Validation failed
valid_organization_id_exists
error
Validation failed
base_api_url_format
error
Validation failed
webhook_endpoint_url_format
error
Validation failed
field_mappings_valid_json
error
Validation failed
feature_flags_valid_json_boolean_values
error
Validation failed
sync_schedule_valid_cron_expressions
error
Validation failed
honorarium_tiers_positive_thresholds
error
Validation failed
auto_approval_thresholds_non_negative
error
Validation failed
updated_at_auto_maintained
error
Validation failed
credentials_ref_max_length
error
Validation failed
Business Rules
one_config_per_connector_type_per_org
Each organization may have at most one integration_configs record per connector_type. Attempting to create a second record for the same (organization_id, connector_type) pair must be rejected. Admins must update the existing record instead
credentials_never_stored_in_plaintext
The credentials_ref field must reference a Supabase Vault secret name or equivalent secure store key. Raw API keys, passwords, OAuth2 client secrets, and Azure AD credentials must never appear in the credentials_ref field itself or in any other column in this table
inactive_config_blocks_sync
When is_active is false, all outbound sync operations for this connector type must be skipped. The Accounting Sync Orchestrator and Member Sync services must check is_active before invoking any external API call
admin_only_write_access
Only users with admin or global_admin roles may create, update, or delete integration_configs records. Supabase RLS policies enforce this at the database level; the Admin Management Service enforces it at the application layer
admin_only_write_access_update
Same admin-only write restriction applies on update and delete operations
org_scoped_read_access
Sync services and repositories may only read integration_configs records belonging to the organization in their execution scope. Cross-organization reads are prohibited by RLS policies
audit_all_config_changes
Every create, update, or delete on integration_configs must produce an immutable audit log entry via the Admin Audit Edge Function, capturing actor, timestamp, connector_type, and a diff of changed fields (excluding credentials_ref value)
feature_flags_gated_by_connector_type
Certain feature_flags keys are only meaningful for specific connector_types (e.g., portal_ownership_overrides is only valid for hlf_dynamics_portal). On save, irrelevant flag keys for the given connector_type should be stripped or ignored
honorarium_config_required_for_blindeforbundet
For connector_type=xledger configs belonging to Blindeforbundet organizations, honorarium_threshold_config must be provided and must define at least two tiers (at 3 and 15 assignments) per the contractual honorarium structure
CRUD Operations
Storage Configuration
Entity Relationships
Every sync attempt against an integration configuration creates an audit log entry for monitoring
An organization may have multiple integration configurations for different external accounting and member systems