Integration
Data Entity
Description
Configuration and state record for an external system integration — Xledger (accounting), HLF Dynamics Portal, Cornerstone, and Consio. Stores API credentials, endpoint configuration, sync schedule, enabled status, and sync history per organization. Each organization may have different integrations enabled.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key. Stable identifier for this integration configuration record. | PKrequiredunique |
organization_id |
uuid |
Foreign key referencing the organization that owns this integration. Integration configurations are scoped per organization — different organizations may have different integrations enabled or configured differently. | required |
integration_type |
enum |
Identifies which external system this record configures. Drives which adapter implementation handles sync. Together with organization_id, forms a unique business key — each organization can have at most one configuration per integration type. | required |
config_json |
json |
Encrypted JSON blob containing adapter-specific configuration: API base URL, client ID, client secret (AES-256-GCM encrypted at rest), OAuth2 token cache, sync schedule (cron expression), and any integration-specific options such as Xledger company code or Dynamics environment identifier. Structure varies by integration_type and is validated against a per-type JSON schema on write. | required |
enabled |
boolean |
Whether this integration is currently active. Disabled integrations are skipped by the sync scheduler and cannot be manually triggered. Defaults to false — must be explicitly enabled by an Organization Administrator after configuration. | required |
status |
enum |
Current operational state of the integration. Updated by the sync engine after each run and by admin actions. 'pending' means configured but not yet synced. 'active' means last sync succeeded. 'syncing' means a sync job is currently in progress. 'error' means the last sync failed. 'inactive' means the integration has been disabled after being active. | required |
last_synced_at |
datetime |
UTC timestamp of the most recent successful sync completion. Null if the integration has never successfully synced. Used by the scheduler to determine whether a scheduled sync is overdue. | - |
last_sync_error |
text |
Human-readable error message from the most recent failed sync attempt. Null when the last sync succeeded. Displayed in the Integration Settings Screen to help administrators diagnose configuration issues. | - |
last_sync_attempt_at |
datetime |
UTC timestamp of the most recent sync attempt, regardless of success or failure. Used alongside last_synced_at to detect stuck syncs (attempt_at significantly newer than synced_at with status 'syncing'). | - |
sync_count |
integer |
Running count of successful sync completions since the integration was first configured. Used for operational monitoring and surfaced in the sync history view. | required |
created_by |
uuid |
Foreign key referencing the user (Organization Administrator or Global Administrator) who created this integration configuration. Preserved for audit purposes even if the user is later deactivated. | required |
created_at |
datetime |
UTC timestamp of when this integration configuration record was first created. | required |
updated_at |
datetime |
UTC timestamp of the most recent update to any field on this record. Updated automatically on every write via a trigger. | required |
Database Indexes
idx_integration_org_type
Columns: organization_id, integration_type
idx_integration_organization_id
Columns: organization_id
idx_integration_status
Columns: status
idx_integration_enabled_last_synced
Columns: enabled, last_synced_at
Validation Rules
organization_id_references_existing_org
error
Validation failed
integration_type_is_valid_enum
error
Validation failed
integration_type_valid_on_update
error
Validation failed
config_json_not_empty
error
Validation failed
config_json_not_empty_on_update
error
Validation failed
sync_count_non_negative
error
Validation failed
last_synced_at_not_future
error
Validation failed
warn_on_error_status_with_long_duration
warning
Validation failed
Business Rules
one_integration_type_per_organization
Each organization may have at most one configuration record per integration_type. Attempting to create a second Xledger configuration for the same organization must be rejected. The unique index on (organization_id, integration_type) enforces this at the database level; the service layer should surface a meaningful error.
credentials_encrypted_at_rest
Any API credentials, client secrets, or OAuth2 tokens stored inside config_json must be encrypted using AES-256-GCM before persistence. The Encryption Service must be invoked on every write that touches config_json. Raw credential strings must never appear in the database.
credentials_encrypted_on_update
When config_json is updated (e.g. rotating an API key), the new value must be re-encrypted before persistence. Partial updates that merge with existing config must decrypt-merge-re-encrypt atomically.
disabled_integration_cannot_sync
When enabled = false, the sync scheduler and any manual trigger must skip this integration. The External API Integration Service must check enabled status before invoking any adapter. Status should transition to 'inactive' when disabled.
only_admins_can_enable_disable
Only Organization Administrators (scoped to their organization) or Global Administrators may change the enabled flag or modify config_json. Coordinators and Peer Mentors must be denied access at the API layer. Auth Middleware enforces role checks on the integration configuration endpoints.
only_admins_can_enable_disable_update
Role enforcement on updates: same administrator-only gate applies when toggling enabled or modifying config_json on an existing record.
status_tracks_sync_outcome
After every sync attempt the status must be updated atomically: 'active' on success (and last_synced_at + sync_count incremented), 'error' on failure (and last_sync_error populated), 'syncing' at job start. A stuck 'syncing' record (last_sync_attempt_at older than 30 minutes) must be reset to 'error' by the scheduler.
organization_tenant_isolation
All reads and writes against this table must be scoped to the authenticated user's organization_id. Cross-organization access to integration configuration — including credentials — is prohibited. Multi-Tenancy Infrastructure injects tenant context; Integration Repository must include WHERE organization_id = :orgId on all queries.
config_schema_validated_per_type
The shape of config_json differs per integration_type. Xledger requires company_code and api_key; HLF Dynamics requires tenant_id, client_id, client_secret, and environment; Cornerstone and Consio have their own schemas. The service layer must validate config_json against the type-specific schema before persisting.
config_schema_validated_per_type_on_update
Re-validate config_json shape on every update, including partial PATCH operations, to prevent schema drift from accumulating across sequential partial updates.
CRUD Operations
Storage Configuration
Entity Relationships
An organization can have multiple external system integrations (accounting, portals) configured independently