Skip to content

Data Model

Target Architecture — Final-State Design

This page describes the final-state logical data model of the Integration Platform. Relational aggregates persist in Azure SQL / PostgreSQL via NHibernate; secrets persist only as references to Azure Key Vault; large payloads and request/response bodies persist in Azure Blob. See Storage for the physical store map.

The data model is organised around the aggregate roots. Every table is tenant-partitioned and carries the cross-cutting metadata fields. No table stores secret material — credentials are modelled as references plus rotation metadata, with secret bytes held in Key Vault.

Logical Model

The core relational entities and how they connect:

  • An IntegrationProvider has many ExternalApiClient versions and is the target of many **IntegrationConnection**s.
  • An IntegrationConnection has exactly one active IntegrationCredential and produces many **IntegrationRun**s.
  • A WebhookSubscription produces many outbound WebhookDelivery records; inbound WebhookDelivery records reference their source provider.
  • Both IntegrationRun and WebhookDelivery can produce IntegrationFailure records.

Entity-Relationship Diagram

erDiagram
    IntegrationProvider ||--o{ ExternalApiClient : registers
    IntegrationProvider ||--o{ IntegrationConnection : targets
    IntegrationConnection ||--|| IntegrationCredential : authenticates_with
    IntegrationConnection ||--o{ IntegrationRun : executes
    WebhookSubscription ||--o{ WebhookDelivery : delivers

    IntegrationProvider {
        string providerId PK
        string tenantId
        string key
        string category
        string authModel
        string status
    }
    ExternalApiClient {
        string apiClientId PK
        string providerId FK
        string assemblyName
        string version
        string status
    }
    IntegrationConnection {
        string connectionId PK
        string tenantId
        string providerId FK
        string credentialId FK
        string status
        string health
    }
    IntegrationCredential {
        string credentialId PK
        string tenantId
        string providerId FK
        string keyVaultRef
        string currentVersion
        string status
    }
    WebhookSubscription {
        string subscriptionId PK
        string tenantId
        string eventType
        string endpointUrl
        string signingSecretRef
        bool active
    }
    WebhookDelivery {
        string deliveryId PK
        string tenantId
        string direction
        string subscriptionId FK
        string payloadRef
        string status
        int attempts
    }
Hold "Alt" / "Option" to enable pan & zoom

Credential reference, not secret

IntegrationCredential.keyVaultRef is a pointer (e.g. kv://factory-integration/github-booking?version=2). The secret itself is never represented as a column anywhere in this model. See Security.

Identifiers

Entity Prefix Example
Connection conn- conn-7b3c1f9a
Provider prov- prov-github
Credential cred- cred-github-booking
API client client- client-twilio-v1
Subscription sub- sub-31f8c0a2
Delivery del- del-5e10c3d2
Run run- run-3a6e1d40
Failure fail- fail-9c4b22e1

Retention

Data Retention Rationale
IntegrationConnection, IntegrationProvider, ExternalApiClient Lifetime of the connection/provider + 90 days after retirement Operational record and reuse lineage
IntegrationCredential metadata Lifetime + audit window; secret versions per Key Vault policy Rotation history and audit
IntegrationRun 180 days hot, then archived Traceability and failure analysis
WebhookDelivery metadata 90 days hot, then archived Delivery audit and replay
Raw webhook / request / response bodies (Blob) 30 days, then tiered to cool/archive or purged per policy Replay and forensics, balanced against cost and data minimisation
IntegrationFailure 180 days Retry analytics and escalation audit

Retention is policy-driven per tenant and may be tightened for regulated data classes by Governance, Security & Compliance.

Multi-Tenancy

  • tenantId everywhere. Every table includes tenantId; it is the partition key and a mandatory predicate on every query.
  • No cross-tenant joins. Repositories scope all reads/writes to the caller's tenant; there is no query path that spans tenants.
  • Shared vs tenant providers. Provider and API-client catalogue rows may be global (shared, read-only across tenants) or tenant-owned; connections and credentials are always tenant-owned.
  • Credential isolation. Key Vault references are namespaced per tenant; access policy ensures a tenant can only resolve its own secrets.
  • Blob isolation. Payload containers are partitioned by tenantId; SAS tokens are short-lived and tenant-scoped.