ASR Data Model — ERD

The ASR target (SOLL) entity-relationship diagram, on its own for easy zooming and panning. For entity descriptions, the Keycloak ownership boundary, and the meta-field convention, see ctn-asr-data-model.md.

Solid lines are intra-ASR foreign keys (|| mandatory, |o nullable). Dashed lines (..) are logical cross-system links to Keycloak records (resolved by ID, no DB FK).

erDiagram
    participant ||--o{ legal_entity : "has"
    legal_entity ||--o{ attribute : "has"
    legal_entity ||--o{ contact : "has"
    participant ||--o{ endpoint : "registers"
    participant ||--o{ verification : "verified by"
    participant ||--o{ participant_role : "plays (dataspace roles)"
    participant ||--o{ m2m_client_ref : "owns"
    participant |o--o{ onboarding_request : "results in"

    kc_organization ||--o{ kc_user : "members"
    kc_organization ||--o{ kc_organization_group : "has (admin/signatory)"
    kc_organization ||--o{ kc_m2m_client : "has"

    participant ||..|| kc_organization : "org-id link (no DB FK)"
    m2m_client_ref ||..|| kc_m2m_client : "client-id link (no DB FK)"
    participant }o..o| kc_user : "owner / initial admin (id ref)"
    onboarding_request }o..o| kc_user : "applicant / reviewed_by (id ref)"

    participant {
        uuid participant_id PK
        varchar keycloak_organization_id UK "link to Keycloak Org (no FK)"
        varchar owner_user_id "Keycloak user id (id ref)"
        varchar status "Prospective|Active|Suspended"
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    legal_entity {
        uuid legal_entity_id PK
        uuid participant_id FK
        varchar primary_legal_name
        varchar address_line1
        varchar postal_code
        varchar city
        varchar country_code
        boolean verified
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    attribute {
        uuid attribute_id PK
        uuid legal_entity_id FK
        varchar identifier_type "KVK|KBO|HRB|EUID|LEI|EORI|VAT"
        varchar identifier_value
        varchar registry_name
        varchar validation_status "Pending|Verified|Rejected"
        varchar validation_source "KvK|KBO|VIES|GLEIF"
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    contact {
        uuid contact_id PK
        uuid legal_entity_id FK
        varchar contact_type "PRIMARY|BILLING|TECHNICAL"
        varchar full_name
        varchar email
        varchar phone
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    endpoint {
        uuid endpoint_id PK
        uuid participant_id FK
        varchar endpoint_name
        varchar endpoint_url
        varchar data_category
        varchar endpoint_type
        varchar lifecycle_status "Registered|Validated|Suspended|Retired"
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    verification {
        uuid verification_id PK
        uuid participant_id FK
        varchar type "KvkCheck|LeiCheck|VatCheck|DnsVerification|EmailVerification|EHerkenning|OnboardingApproval"
        varchar category "Admin|Automatic|Organization"
        varchar status "Pending|Approved|Rejected|Revoked"
        timestamptz handled_at
        varchar handled_by
        text evidence
        text reason
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    participant_role {
        uuid role_id PK
        uuid participant_id FK
        varchar role "DataOwner|DataConsumer|DataProvider|ServiceProvider"
        date start_date
        date end_date
        varchar loa
        boolean compliancy_verified
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    m2m_client_ref {
        uuid m2m_client_ref_id PK
        uuid participant_id FK
        varchar keycloak_client_id UK "link to Keycloak client (no FK)"
        varchar keycloak_organization_id "link"
        varchar client_name
        text assigned_scopes "array, mirrored from Keycloak"
        varchar status "Active|Suspended|Revoked"
        timestamptz last_rotated_at
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    onboarding_request {
        uuid request_id PK
        uuid participant_id FK "nullable, set on approval"
        varchar applicant_user_id "Keycloak user id (id ref)"
        varchar applicant_email
        varchar applicant_name
        varchar legal_name
        varchar kvk_number
        varchar status "Submitted|UnderReview|Approved|Rejected"
        timestamptz submitted_at
        timestamptz reviewed_at
        varchar reviewed_by "Keycloak user id (id ref)"
        timestamptz created_at
        timestamptz updated_at
        varchar created_by
        varchar updated_by
        boolean is_deleted
    }
    audit_record {
        uuid audit_record_id PK
        varchar entity_type
        varchar entity_key
        varchar action "Added|Modified|Deleted"
        varchar performed_by
        timestamptz created_at
        jsonb payload
    }
    kc_user {
        varchar user_id PK
        varchar email
        varchar given_name
        varchar family_name
    }
    kc_organization {
        varchar org_id PK
        varchar alias "carries participant_id"
        varchar name
    }
    kc_organization_group {
        varchar group_id PK
        varchar org_id FK
        varchar path "/admin, /signatory"
    }
    kc_m2m_client {
        varchar client_id PK
        varchar org_id FK
        varchar client_name
        varchar secret "stored in Keycloak only"
        text scopes
    }