Deze documentatie wordt actief uitgebreid — kom regelmatig terug.
ASR Data Model
The data model for the Association Register (ASR) — the authoritative store for CTN participants, their legal identities, identifiers, verifications, endpoints, dataspace roles and onboarding state. This document describes the target (SOLL) model: the schema the ASR should converge on, aligned with ADR-00003 — Separate users and participants.
The diagram in this document is large by design. A zoom-friendly, ERD-only companion lives at ctn-asr-data-model-erd.md — use that when you want to pan and zoom the entity relationships without the surrounding prose.
1. Scope and Ownership Boundary
Section titled “1. Scope and Ownership Boundary”The ASR is not the system of record for identities. Per ADR-00003, a strict separation applies: human and machine identities live in Keycloak; participant business data lives in the ASR database. The two are linked by a single cross-reference — the Keycloak Organization ID on the ASR participant record, and the ASR participant ID carried as the Keycloak Organization alias.
| Lives in Keycloak (identity) | Lives in ASR (participants) |
|---|---|
| User credentials, MFA, sessions | Participant record (legal entity registration) |
| Minimal user profile (email, name) | Business identifiers: KVK / KBO / HRB, EUID, LEI, EORI, VAT |
| User-to-participant membership | Verification records and their status |
| Per-participant user roles (admin / member / signatory) via Organization Groups | |
| M2M client credentials (client secret) | Dataspace participation roles (data owner / consumer / provider) |
| Per-participant IdP federation | Onboarding workflow state, evidence, audit trail |
| A reference to each Keycloak M2M client (no secret) |
Consequence for the model: the ASR stores no passwords and no client secrets. Where the ASR needs to point at an identity (the participant owner, an application reviewer, a provisioned M2M client) it stores the Keycloak ID as an opaque string reference, not a foreign key — there is no database-level FK across the system boundary.
2. Meta-field Convention (mandatory)
Section titled “2. Meta-field Convention (mandatory)”Every ASR table uses one audit/meta block. No variants. This replaces the inconsistent mix that existed across earlier prototypes (dt_created, dt_modified, created, created_on, createdAt, dt_updated, submitted_at-as-created, mixed created_by types, etc.).
| Field | Type | Rule |
|---|---|---|
created_at | timestamptz | NOT NULL DEFAULT now(); never written by the application after insert |
updated_at | timestamptz | NOT NULL DEFAULT now(); maintained by an BEFORE UPDATE trigger |
created_by | varchar(100) | Keycloak user ID, or system for automated processes |
updated_by | varchar(100) | Keycloak user ID, or system |
is_deleted | boolean | NOT NULL DEFAULT false; soft delete — all reads filter is_deleted = false |
Exceptions, by design:
audit_recordis an append-only log: it hascreated_atonly (noupdated_at, nois_deleted).- Keycloak entities (
kc_*in the diagram) are external; they are managed by Keycloak and are not subject to this convention. They appear in the diagram only to show the link points.
Domain status fields (e.g. participant.status, verification.status, endpoint.lifecycle_status) are separate from is_deleted and describe business lifecycle, not record deletion.
3. Entity Relationship Diagram
Section titled “3. Entity Relationship Diagram”Solid lines are intra-ASR foreign keys (|| = mandatory, |o = nullable). Dashed lines (..) are logical cross-system links to Keycloak records — resolved by ID at runtime, with no database foreign key.
erDiagram
participant ||--o{ legal_entity : "has"
legal_entity ||--o{ legal_entity_identifier : "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
}
legal_entity_identifier {
uuid identifier_id PK
uuid legal_entity_id FK
varchar identifier_type "KVK|KBO|HRB|HRA|EUID|LEI|EORI|VAT|RSIN|SIREN|SIRET|UID|VESTIGINGSNUMMER|PEPPOL"
varchar identifier_value
varchar registry_name
varchar identifier_scope "ENTITY|ESTABLISHMENT"
varchar validation_status "Pending|Verified|Rejected"
varchar validation_source "KvK|KBO|VIES|GLEIF|Handelsregister|OffeneRegister|recherche-entreprises|Zefix"
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
}
4. Entity Reference
Section titled “4. Entity Reference”ASR (PostgreSQL)
Section titled “ASR (PostgreSQL)”| Entity | Purpose | Key relationships |
|---|---|---|
participant | Root entity: a registered CTN participant. Holds lifecycle status, and the link to its Keycloak Organization. | Parent of legal entities, endpoints, verifications, roles, M2M references |
legal_entity | The verified legal identity (name, address) behind a participant. Usually 1:1 with participant. | FK → participant |
legal_entity_identifier | Business identifiers with validation status and enrichment source. Authoritative here, never in Keycloak. Carries identifier_scope to distinguish entity-level identifiers (KVK, KBO, HRB/HRA, SIREN, UID, EUID, LEI, EORI, VAT, RSIN) from establishment-level ones (vestigingsnummer, SIRET) — see Two-Level Identity Model below. | FK → legal_entity |
contact | Business contact persons (primary / billing / technical). Not authentication users — those live in Keycloak. | FK → legal_entity |
endpoint | Provider API endpoints with lifecycle status (registered → validated → suspended → retired). | FK → participant |
verification | One row per verification check, with status, evidence and handler. Replaces scattered validation_status flags; gives a full auditable trail. | FK → participant |
participant_role | Dataspace participation roles (DataOwner / DataConsumer / DataProvider / ServiceProvider) with validity dates and assurance. Distinct from Keycloak user roles. | FK → participant |
m2m_client_ref | ASR-side reference to a Keycloak M2M client provisioned for the participant: client name, scopes (mirrored for display/audit), status. No secret. | FK → participant; logical link → kc_m2m_client |
onboarding_request | Intake/onboarding request; links to the participant once approved. | FK → participant (nullable); id-ref → kc_user |
audit_record | Append-only change log across all entities (entity type, key, action, actor, payload). | None (standalone) |
Two-Level Identity Model (entity vs establishment)
Section titled “Two-Level Identity Model (entity vs establishment)”(Added 2026-06 — from batch-enrichment learnings; see Identifier Enrichment Architecture.)
Business identifiers live at two scopes that must not be conflated:
- Entity / rechtspersoon — KVK, HRB/HRA, KBO, SIREN, UID (CHE). The carriers of LEI, RSIN, VAT, EUID. One value per legal entity, regardless of how many locations it has.
- Establishment / vestiging — a physical location with its own address but no own LEI/VAT, identified by vestigingsnummer (NL), SIRET (FR), or equivalent.
The current model keeps both on legal_entity_identifier, tagged with identifier_scope (ENTITY | ESTABLISHMENT). DE/CH branches are usually not separately numbered (a Zweigniederlassung refers back to the parent HRB/UID); for those, the establishment is distinguished by address (and, in batch enrichment, a geocoded location code). A future refinement could promote establishments to their own establishment table (legal_entity 1 ──< establishment N) if per-location endpoints/roles are needed.
Keycloak (external — shown for the link only)
Section titled “Keycloak (external — shown for the link only)”| Entity | Purpose |
|---|---|
kc_user | Human or service identity. Owns credentials and minimal profile. |
kc_organization | One Keycloak Organization per ASR participant. Its alias carries the ASR participant_id. |
kc_organization_group | Per-participant user roles (/admin, /signatory) as Organization Groups. |
kc_m2m_client | The M2M client and its secret — the credential the ASR provisions but never stores. |
5. M2M Client Credentials and the Keycloak Link
Section titled “5. M2M Client Credentials and the Keycloak Link”Client credentials are created and held exclusively in Keycloak. The ASR fronts the lifecycle (create / rotate / revoke) via the Keycloak Admin REST API — see Keycloak M2M Authentication — and keeps only a non-secret reference row (m2m_client_ref) for listing, audit and participant scoping.
The links between the two systems, all by opaque ID (no database FK):
participant.keycloak_organization_id⇄kc_organization.org_id(and the reverse via the Organizationalias).m2m_client_ref.keycloak_client_id⇄kc_m2m_client.client_id.participant.owner_user_id,onboarding_request.applicant_user_id/reviewed_by⇄kc_user.user_id.
Never stored in the ASR: client secrets, passwords, MFA state, session/refresh tokens, or business identifiers mirrored as Keycloak attributes (an anti-pattern called out in ADR-00003).
6. Reference DDL
Section titled “6. Reference DDL”A PostgreSQL CREATE TABLE script implementing this model is maintained alongside this document: the SQL DDL shown in full below (jump) (PostgreSQL 15+). It contains the ten ASR tables, the shared updated_at trigger, CHECK constraints for the controlled vocabularies, foreign keys (ON DELETE CASCADE, except onboarding_request which is ON DELETE SET NULL), unique constraints, and indexes — including the partial unique index on participant.keycloak_organization_id and uq_identifier. The Keycloak entities are intentionally absent: they are not created or owned by this database.
This consolidated script is a reference of the target schema. In the implementation repo the schema is applied through Liquibase YAML changesets (TDR-0001, see Database Schema Management — PostgreSQL on Azure); this file is the basis for the baseline changelog, after which every change is its own reviewed changeset rather than an edit to a create-all script.
7. Related Documents
Section titled “7. Related Documents”- ADR-00003 — Separate users and participants
- ADR-00006 — Token Claim Composition
- ADR-00007 — Identity Mutation Proxy
- Association Register — Architecture
- Keycloak M2M Authentication
- Identifier Enrichment Architecture
- Member Onboarding Workflow
- Database Schema Management
- ERD-only companion: ctn-asr-data-model-erd.md
Reference DDL
Section titled “Reference DDL”ctn-asr-schema.sql — full PostgreSQL DDL
-- =============================================================================-- CTN Association Register (ASR) — PostgreSQL schema (SOLL / target state)-- Reference DDL for the data model described in ctn-asr-data-model.md-- Target: PostgreSQL 15+---- Scope: ASR-owned tables only. Human/machine identities and M2M client-- secrets live in Keycloak (see ADR-00003) and are referenced here by opaque-- ID string, with NO database foreign key across the system boundary.---- Meta-field convention (every table except audit_record):-- created_at, updated_at, created_by, updated_by, is_deleted---- NOTE: This is a CONSOLIDATED REFERENCE of the target schema. The ASR applies-- schema through Flyway versioned migrations (src/main/resources/db/migration/,-- V<n>__*.sql) per ctn-database-schema-management-azure.md — not via a single-- create-all script or ORM auto-DDL. Use this file to seed V001__initial_schema.sql.-- =============================================================================
BEGIN;
-- gen_random_uuid() is built into PostgreSQL 13+.-- On older servers, enable it explicitly:-- CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- ------------------------------------------------------------------------------- Shared trigger function: maintain updated_at on every UPDATE-- -----------------------------------------------------------------------------CREATE OR REPLACE FUNCTION asr_set_updated_at()RETURNS trigger AS $BEGIN NEW.updated_at := now(); RETURN NEW;END;$ LANGUAGE plpgsql;
-- ------------------------------------------------------------------------------- participant — root entity; links to its Keycloak Organization-- -----------------------------------------------------------------------------CREATE TABLE participant ( participant_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), keycloak_organization_id varchar(255), owner_user_id varchar(255), status varchar(20) NOT NULL DEFAULT 'Prospective' CONSTRAINT participant_status_check CHECK (status IN ('Prospective','Active','Suspended','Terminated')), metadata jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);COMMENT ON COLUMN participant.keycloak_organization_id IS 'Link to Keycloak Organization (opaque ID, no DB FK; see ADR-00003)';COMMENT ON COLUMN participant.owner_user_id IS 'Keycloak user ID of the initial admin (opaque ID, no DB FK)';
CREATE UNIQUE INDEX uq_participant_kc_org ON participant (keycloak_organization_id) WHERE keycloak_organization_id IS NOT NULL;CREATE INDEX idx_participant_status ON participant (status) WHERE is_deleted = false;
CREATE TRIGGER trg_participant_updated BEFORE UPDATE ON participant FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- legal_entity — verified legal identity behind a participant-- -----------------------------------------------------------------------------CREATE TABLE legal_entity ( legal_entity_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid NOT NULL REFERENCES participant (participant_id) ON DELETE CASCADE, primary_legal_name varchar(255) NOT NULL, address_line1 varchar(255), address_line2 varchar(255), postal_code varchar(20), city varchar(255), province varchar(255), country_code varchar(2), verified boolean NOT NULL DEFAULT false, metadata jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);CREATE INDEX idx_legal_entity_participant ON legal_entity (participant_id);CREATE INDEX idx_legal_entity_name ON legal_entity (primary_legal_name);CREATE INDEX idx_legal_entity_active ON legal_entity (participant_id) WHERE is_deleted = false;
CREATE TRIGGER trg_legal_entity_updated BEFORE UPDATE ON legal_entity FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- legal_entity_identifier — business identifiers (authoritative in ASR)-- -----------------------------------------------------------------------------CREATE TABLE legal_entity_identifier ( identifier_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), legal_entity_id uuid NOT NULL REFERENCES legal_entity (legal_entity_id) ON DELETE CASCADE, identifier_type varchar(20) NOT NULL CONSTRAINT legal_entity_identifier_type_check CHECK (identifier_type IN ('KVK','KBO','HRB','EUID','LEI','EORI','VAT','SIREN','CRN')), identifier_value varchar(100) NOT NULL, registry_name varchar(255), registry_url varchar(500), valid_from timestamptz, valid_to timestamptz, validation_status varchar(20) NOT NULL DEFAULT 'Pending' CONSTRAINT legal_entity_identifier_status_check CHECK (validation_status IN ('Pending','Verified','Rejected')), validation_source varchar(20) CONSTRAINT legal_entity_identifier_source_check CHECK (validation_source IN ('KvK','KBO','VIES','GLEIF','Manual')), validation_date timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false, CONSTRAINT uq_identifier UNIQUE (legal_entity_id, identifier_type, identifier_value));CREATE INDEX idx_identifier_entity ON legal_entity_identifier (legal_entity_id);CREATE INDEX idx_identifier_value ON legal_entity_identifier (identifier_type, identifier_value);
CREATE TRIGGER trg_identifier_updated BEFORE UPDATE ON legal_entity_identifier FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- contact — business contacts (NOT authentication users; those live in Keycloak)-- -----------------------------------------------------------------------------CREATE TABLE contact ( contact_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), legal_entity_id uuid NOT NULL REFERENCES legal_entity (legal_entity_id) ON DELETE CASCADE, contact_type varchar(20) NOT NULL CONSTRAINT contact_type_check CHECK (contact_type IN ('PRIMARY','BILLING','TECHNICAL','LEGAL')), full_name varchar(255) NOT NULL, first_name varchar(100), last_name varchar(100), email varchar(255) NOT NULL, phone varchar(50), mobile varchar(50), job_title varchar(100), is_primary boolean NOT NULL DEFAULT false, is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);COMMENT ON TABLE contact IS 'Business contact persons. Authentication users are managed in Keycloak (ADR-00003).';CREATE INDEX idx_contact_entity ON contact (legal_entity_id);CREATE INDEX idx_contact_email ON contact (email);
CREATE TRIGGER trg_contact_updated BEFORE UPDATE ON contact FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- endpoint — provider API endpoints with lifecycle status-- -----------------------------------------------------------------------------CREATE TABLE endpoint ( endpoint_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid NOT NULL REFERENCES participant (participant_id) ON DELETE CASCADE, endpoint_name varchar(255) NOT NULL, endpoint_url varchar(500), endpoint_description text, data_category varchar(100), endpoint_type varchar(50) NOT NULL DEFAULT 'REST_API', authentication_method varchar(50), lifecycle_status varchar(20) NOT NULL DEFAULT 'Registered' CONSTRAINT endpoint_lifecycle_check CHECK (lifecycle_status IN ('Registered','Validated','Suspended','Retired')), is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);CREATE INDEX idx_endpoint_participant ON endpoint (participant_id);CREATE INDEX idx_endpoint_status ON endpoint (lifecycle_status) WHERE is_deleted = false;
CREATE TRIGGER trg_endpoint_updated BEFORE UPDATE ON endpoint FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- verification — one auditable row per verification check (authoritative in ASR)-- -----------------------------------------------------------------------------CREATE TABLE verification ( verification_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid NOT NULL REFERENCES participant (participant_id) ON DELETE CASCADE, type varchar(40) NOT NULL CONSTRAINT verification_type_check CHECK (type IN ('KvkCheck','KboCheck','LeiCheck','VatCheck','DnsVerification', 'EmailVerification','EHerkenning','OnboardingApproval', 'BusinessRegisterExtract','ConditionsOfUseAcceptance')), category varchar(20) NOT NULL CONSTRAINT verification_category_check CHECK (category IN ('Admin','Automatic','Organization')), status varchar(20) NOT NULL DEFAULT 'Pending' CONSTRAINT verification_status_check CHECK (status IN ('Pending','Approved','Rejected','Revoked')), handled_at timestamptz, handled_by varchar(100), evidence text, reason text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);CREATE INDEX idx_verification_participant ON verification (participant_id);CREATE INDEX idx_verification_status ON verification (status) WHERE is_deleted = false;
CREATE TRIGGER trg_verification_updated BEFORE UPDATE ON verification FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- participant_role — dataspace participation roles ONLY-- (user/auth roles admin/member/signatory live in Keycloak Organization Groups)-- -----------------------------------------------------------------------------CREATE TABLE participant_role ( role_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid NOT NULL REFERENCES participant (participant_id) ON DELETE CASCADE, role varchar(20) NOT NULL CONSTRAINT participant_role_check CHECK (role IN ('DataOwner','DataConsumer','DataProvider','ServiceProvider')), start_date date, end_date date, loa varchar(20), compliancy_verified boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);COMMENT ON TABLE participant_role IS 'Dataspace participation roles only. User/authentication roles live in Keycloak (ADR-00003).';CREATE INDEX idx_participant_role_participant ON participant_role (participant_id);
CREATE TRIGGER trg_participant_role_updated BEFORE UPDATE ON participant_role FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- m2m_client_ref — ASR-side reference to a Keycloak M2M client (NO secret)-- -----------------------------------------------------------------------------CREATE TABLE m2m_client_ref ( m2m_client_ref_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid NOT NULL REFERENCES participant (participant_id) ON DELETE CASCADE, keycloak_client_id varchar(255) NOT NULL, keycloak_organization_id varchar(255), client_name varchar(255) NOT NULL, assigned_scopes text[] NOT NULL DEFAULT '{}', status varchar(20) NOT NULL DEFAULT 'Active' CONSTRAINT m2m_client_ref_status_check CHECK (status IN ('Active','Suspended','Revoked')), last_rotated_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false, CONSTRAINT uq_m2m_client_ref_kc_client UNIQUE (keycloak_client_id));COMMENT ON TABLE m2m_client_ref IS 'Reference to a Keycloak M2M client. The client secret is held ONLY in Keycloak (ADR-00003); never stored here.';COMMENT ON COLUMN m2m_client_ref.assigned_scopes IS 'Mirrored from Keycloak for display/audit; Keycloak remains the source of truth.';CREATE INDEX idx_m2m_client_ref_participant ON m2m_client_ref (participant_id) WHERE is_deleted = false;
CREATE TRIGGER trg_m2m_client_ref_updated BEFORE UPDATE ON m2m_client_ref FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- onboarding_request — intake; links to participant once approved-- -----------------------------------------------------------------------------CREATE TABLE onboarding_request ( request_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), participant_id uuid REFERENCES participant (participant_id) ON DELETE SET NULL, applicant_user_id varchar(255), applicant_email varchar(255) NOT NULL, applicant_name varchar(255) NOT NULL, applicant_job_title varchar(255), applicant_phone varchar(50), legal_name varchar(255) NOT NULL, kvk_number varchar(50), lei varchar(20), company_address text, postal_code varchar(20), city varchar(100), country varchar(100), status varchar(20) NOT NULL DEFAULT 'Submitted' CONSTRAINT onboarding_request_status_check CHECK (status IN ('Submitted','UnderReview','Approved','Rejected')), submitted_at timestamptz NOT NULL DEFAULT now(), reviewed_at timestamptz, reviewed_by varchar(255), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), created_by varchar(100), updated_by varchar(100), is_deleted boolean NOT NULL DEFAULT false);COMMENT ON COLUMN onboarding_request.participant_id IS 'Set when the application is approved and a participant is created.';COMMENT ON COLUMN onboarding_request.reviewed_by IS 'Keycloak user ID of the reviewer (opaque ID, no DB FK).';CREATE INDEX idx_onboarding_request_status ON onboarding_request (status);CREATE INDEX idx_onboarding_request_email ON onboarding_request (applicant_email);
CREATE TRIGGER trg_onboarding_request_updated BEFORE UPDATE ON onboarding_request FOR EACH ROW EXECUTE FUNCTION asr_set_updated_at();
-- ------------------------------------------------------------------------------- audit_record — append-only change log (no updated_at / is_deleted by design)-- -----------------------------------------------------------------------------CREATE TABLE audit_record ( audit_record_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entity_type varchar(100) NOT NULL, entity_key varchar(255) NOT NULL, action varchar(20) NOT NULL CONSTRAINT audit_record_action_check CHECK (action IN ('Added','Modified','Deleted')), performed_by varchar(100), created_at timestamptz NOT NULL DEFAULT now(), payload jsonb);CREATE INDEX idx_audit_record_entity ON audit_record (entity_type, entity_key);CREATE INDEX idx_audit_record_created ON audit_record (created_at);
COMMIT;In samenwerking met
