DDL

Status: Entwurf · Spec-Kandidat: ja (verbindliche Vorlage für initiale Migration)

Initiales DDL für das mdm-Schema. Indizes, Funktionen und Trigger sind in den jeweiligen Dokumenten ausgegliedert (Indizes, Trigger und Funktionen), hier vollständig zusammengeführt für Migrationszwecke.

Vollständiges DDL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS mdm;

CREATE TYPE mdm.attribute_data_type AS ENUM (
  'string',
  'text',
  'integer',
  'decimal',
  'boolean',
  'date',
  'datetime',
  'enum',
  'multi_enum',
  'reference',
  'multi_reference',
  'money',
  'json'
);

CREATE TYPE mdm.entity_status AS ENUM (
  'draft',
  'active',
  'inactive',
  'archived'
);

CREATE TYPE mdm.delete_policy AS ENUM (
  'restrict',
  'detach',
  'cascade',
  'archive_only'
);

CREATE TYPE mdm.audit_action AS ENUM (
  'insert',
  'update',
  'archive',
  'restore',
  'soft_delete',
  'hard_delete',
  'relate',
  'unrelate',
  'version_create',
  'permission_grant',
  'permission_revoke',
  'role_assign',
  'role_unassign',
  'login',
  'access_denied',
  'job_enqueued',
  'job_done',
  'job_dlq',
  'job_replay',
  'job_discard',
  'external_id_link',
  'external_id_unlink',
  'attachment_uploaded',
  'attachment_scanned',
  'attachment_deleted',
  'attachment_restored',
  'attachment_hard_deleted',
  'email_sent',
  'email_sent_dryrun',
  'email_delivered',
  'email_bounced',
  'email_complained',
  'email_suppressed'
);

CREATE TYPE mdm.principal_type AS ENUM (
  'user',
  'service_account',
  'role',
  'group'
);

CREATE TYPE mdm.permission_effect AS ENUM (
  'allow',
  'deny'
);

CREATE TYPE mdm.permission_action AS ENUM (
  'read',
  'create',
  'update',
  'archive',
  'restore',
  'soft_delete',
  'hard_delete',
  'relate',
  'unrelate',
  'export',
  'manage_metadata',
  'manage_permissions',
  'read_audit'
);

CREATE TYPE mdm.acl_scope AS ENUM (
  'global',
  'tenant',
  'entity_type',
  'entity',
  'relation_type'
);

CREATE TYPE mdm.outbox_status AS ENUM (
  'pending',
  'dispatched',
  'failed',
  'skipped'
);

CREATE TYPE mdm.job_kind AS ENUM (
  'email_send',
  'bulk_import',
  'reindex',
  'outbox_dispatch',
  'cleanup_jobs',
  'virus_scan',
  -- OP-17: Build/Drop von Expression-Indizes auf searchable/sortable-Attributen
  'index_sync',
  -- OP-17: GIN-Catch-all-Backfill für is_external_source_target=true-Typen
  'gin_backfill'
);

CREATE TYPE mdm.job_status AS ENUM (
  'pending',
  'running',
  'done',
  'failed',
  'dlq'
);

-- =====================================================================
-- Mandant (Tenant)
--
-- Pflicht-Säule für Mandantenfähigkeit (FR-203). In V1 kann genau ein
-- Default-Tenant angelegt werden ("public"); alle Tenant-Spalten sind
-- NOT NULL und referenzieren genau diesen. Damit ist später der Schritt
-- zu echtem Multi-Tenancy schemafrei: keine Migration nötig, nur
-- zusätzliche AA_TENANT-Zeilen + Permission-Scope `AA_TENANT`.
-- =====================================================================

CREATE TABLE mdm.AA_TENANT (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT tenant_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT tenant_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

-- Initialer Default-Tenant. Garantiert NOT-NULL-FKs in V1.
INSERT INTO mdm.AA_TENANT (key, name, description)
VALUES ('public', 'Default', 'Default-Mandant in V1, vor Multi-Tenancy-Aktivierung')
ON CONFLICT (key) DO NOTHING;

CREATE TABLE mdm.DDM_ENTITY_TYPE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  key text NOT NULL,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  -- OP-17: markiert Typen, deren Daten aus Quellsystemen oder Bulk-Imports
  -- stammen. Aktiviert den partiellen GIN-Catch-all-Index auf attributes
  -- (Build über AA_JOB.job_kind='gin_backfill'). Auto-Setzung beim ersten
  -- DDM_ENTITY_EXTERNAL_ID- oder DDM_SOURCE_SYSTEM-Mapping; Steward kann
  -- manuell vorab setzen. Demotion (true → false) ist Admin-Migration und
  -- droppt den Catch-all nicht automatisch.
  is_external_source_target boolean NOT NULL DEFAULT false,
  version integer NOT NULL DEFAULT 1,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT entity_type_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT entity_type_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  -- key ist eindeutig je Tenant, nicht global. So bekommen Mandanten
  -- bei Aktivierung ihre eigenen Typen ohne Namenskollision.
  CONSTRAINT entity_type_key_per_tenant_uk
    UNIQUE (tenant_id, key)
);

CREATE TABLE mdm.DDM_ENUM_SET (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT enum_set_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT enum_set_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE TABLE mdm.DDM_ENUM_VALUE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  enum_set_id uuid NOT NULL REFERENCES mdm.DDM_ENUM_SET(id) ON DELETE RESTRICT,
  key text NOT NULL,
  label text NOT NULL,
  description text,
  sort_order integer NOT NULL DEFAULT 100,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT enum_value_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT enum_value_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT enum_value_unique_per_set_uk
    UNIQUE (enum_set_id, key)
);

CREATE TABLE mdm.DDM_RELATION_TYPE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  from_entity_type_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  to_entity_type_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  delete_policy mdm.delete_policy NOT NULL DEFAULT 'restrict',
  is_active boolean NOT NULL DEFAULT true,
  is_bidirectional boolean NOT NULL DEFAULT false,
  -- Kardinalitäten je from-Entity. NULL bei max = unbegrenzt.
  -- min UND max sind Service-Layer-Regeln (OP-13a, revidiert 2026-04-29);
  -- es gibt KEINEN Kardinalitäts-Trigger. Verstöße sind über die Views
  -- mdm.v_relation_min_violations und mdm.v_relation_max_violations sichtbar.
  min_to_per_from integer NOT NULL DEFAULT 0,
  max_to_per_from integer,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT relation_type_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT relation_type_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT relation_type_cardinality_nonneg_chk
    CHECK (min_to_per_from >= 0 AND (max_to_per_from IS NULL OR max_to_per_from >= 1)),
  CONSTRAINT relation_type_cardinality_min_le_max_chk
    CHECK (max_to_per_from IS NULL OR min_to_per_from <= max_to_per_from)
);

CREATE TABLE mdm.DDM_ENTITY_TYPE_ATTRIBUTE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  key text NOT NULL,
  label text NOT NULL,
  description text,
  data_type mdm.attribute_data_type NOT NULL,
  required boolean NOT NULL DEFAULT false,
  unique_per_type boolean NOT NULL DEFAULT false,
  searchable boolean NOT NULL DEFAULT false,
  filterable boolean NOT NULL DEFAULT true,
  sortable boolean NOT NULL DEFAULT false,
  sort_order integer NOT NULL DEFAULT 100,
  default_value jsonb,
  enum_set_id uuid
    REFERENCES mdm.DDM_ENUM_SET(id) ON DELETE RESTRICT,
  reference_entity_type_id uuid
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  relation_type_id uuid
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  min_value numeric,
  max_value numeric,
  regex_pattern text,
  validation_rule jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  -- OP-17: Index-Lifecycle für searchable/sortable. NULL = kein Index nötig
  -- (searchable=false AND sortable=false). Sonst: pending → building → active
  -- bzw. failed; gepflegt vom Worker-Pool über AA_JOB.job_kind='index_sync'.
  index_status text,
  index_built_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT entity_type_attribute_unique_per_type_uk
    UNIQUE (entity_type_id, key),
  CONSTRAINT entity_type_attribute_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT entity_type_attribute_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT entity_type_attribute_validation_rule_is_object_chk
    CHECK (jsonb_typeof(validation_rule) = 'object'),
  CONSTRAINT entity_type_attribute_min_max_chk
    CHECK (min_value IS NULL OR max_value IS NULL OR min_value <= max_value),
  CONSTRAINT entity_type_attribute_enum_chk
    CHECK (
      (
        data_type IN ('enum', 'multi_enum')
        AND enum_set_id IS NOT NULL
      )
      OR data_type NOT IN ('enum', 'multi_enum')
    ),
  CONSTRAINT entity_type_attribute_reference_chk
    CHECK (
      (
        data_type IN ('reference', 'multi_reference')
        AND relation_type_id IS NOT NULL
      )
      OR data_type NOT IN ('reference', 'multi_reference')
    ),
  -- OP-17: zustandsgeführter Index-Lifecycle.
  CONSTRAINT entity_type_attribute_index_status_chk
    CHECK (index_status IS NULL
           OR index_status IN ('pending','building','active','failed'))
);

CREATE TABLE mdm.DDM_ENTITY (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  entity_type_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  code text NOT NULL,
  name text NOT NULL,
  status mdm.entity_status NOT NULL DEFAULT 'draft',
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  search_vector tsvector,
  version integer NOT NULL DEFAULT 1,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  archived_at timestamptz,
  archived_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT entity_attributes_is_object_chk
    CHECK (jsonb_typeof(attributes) = 'object'),
  CONSTRAINT entity_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE TABLE mdm.DDM_ENTITY_RELATION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  relation_type_id uuid NOT NULL
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  from_entity_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  to_entity_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  valid_from timestamptz,
  valid_to timestamptz,
  sort_order integer NOT NULL DEFAULT 100,
  -- Fachliche Eigenschaften der Beziehung (z. B. Rabatt-Prozent,
  -- Verantwortlichkeitsanteil, Vertragsnummer). Strukturell genauso
  -- gepflegt wie entity.attributes, aber pro DDM_RELATION_TYPE definiert
  -- über mdm.DDM_RELATION_TYPE_ATTRIBUTE.
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  -- Technische Felder (Quellsystem-Hash, Sync-Flags). Anwendung darf
  -- attributes und metadata nicht vermischen.
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT entity_relation_from_to_chk
    CHECK (from_entity_id <> to_entity_id),
  CONSTRAINT entity_relation_validity_chk
    CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_from <= valid_to),
  CONSTRAINT entity_relation_attributes_is_object_chk
    CHECK (jsonb_typeof(attributes) = 'object'),
  CONSTRAINT entity_relation_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

-- =====================================================================
-- DDM_RELATION_TYPE_ATTRIBUTE
--
-- Spiegelung von DDM_ENTITY_TYPE_ATTRIBUTE, jedoch für Beziehungen.
-- Ermöglicht Validierung, Suche/Filter und UI-Generierung für
-- entity_relation.attributes.
-- =====================================================================

CREATE TABLE mdm.DDM_RELATION_TYPE_ATTRIBUTE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  relation_type_id uuid NOT NULL
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  key text NOT NULL,
  label text NOT NULL,
  description text,
  data_type mdm.attribute_data_type NOT NULL,
  required boolean NOT NULL DEFAULT false,
  filterable boolean NOT NULL DEFAULT true,
  sortable boolean NOT NULL DEFAULT false,
  sort_order integer NOT NULL DEFAULT 100,
  default_value jsonb,
  enum_set_id uuid REFERENCES mdm.DDM_ENUM_SET(id) ON DELETE RESTRICT,
  min_value numeric,
  max_value numeric,
  regex_pattern text,
  validation_rule jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT relation_type_attribute_unique_per_type_uk
    UNIQUE (relation_type_id, key),
  CONSTRAINT relation_type_attribute_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT relation_type_attribute_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT relation_type_attribute_validation_rule_is_object_chk
    CHECK (jsonb_typeof(validation_rule) = 'object'),
  CONSTRAINT relation_type_attribute_min_max_chk
    CHECK (min_value IS NULL OR max_value IS NULL OR min_value <= max_value),
  CONSTRAINT relation_type_attribute_enum_chk
    CHECK (
      (data_type IN ('enum', 'multi_enum') AND enum_set_id IS NOT NULL)
      OR data_type NOT IN ('enum', 'multi_enum')
    ),
  -- Beziehungs-Attribute können keine Referenzen sein (Reference ist die
  -- Beziehung selbst). Daher reference / multi_reference verboten.
  CONSTRAINT relation_type_attribute_no_reference_chk
    CHECK (data_type NOT IN ('reference', 'multi_reference'))
);

CREATE TABLE mdm.DDM_ENTITY_VERSION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id uuid NOT NULL REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  version_no integer NOT NULL,
  snapshot jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  CONSTRAINT entity_version_snapshot_is_object_chk
    CHECK (jsonb_typeof(snapshot) = 'object'),
  CONSTRAINT entity_version_unique_uk
    UNIQUE (entity_id, version_no)
);

CREATE TABLE mdm.AA_AUDIT_LOG (
  id bigserial PRIMARY KEY,
  tenant_id uuid REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  entity_name text NOT NULL,
  entity_id uuid,
  action mdm.audit_action NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  changed_by text,
  actor_principal_id uuid,
  reason text,
  correlation_id uuid,
  old_data jsonb,
  new_data jsonb,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT audit_log_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

-- =====================================================================
-- Cross-Reference / External-System-Mapping
--
-- Verknüpft eine MDM-Entität mit ihrer Identität in einem Quellsystem.
-- Strict 1:1 pro (entity, source_system). Hard-Delete-Pfad, Historie
-- über AA_AUDIT_LOG (external_id_link / external_id_unlink).
--
-- Inbound-Lookup-Pfad: (source_system, external_id) → entity_id.
-- Outbound-Lookup-Pfad: (entity_id, source_system) → external_id.
-- =====================================================================

CREATE TABLE mdm.DDM_SOURCE_SYSTEM (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  key text NOT NULL,
  name text NOT NULL,
  description text,
  -- 0..100. V1 dokumentiert, von OP-29 (Match/Merge / Survivorship) ausgewertet.
  trust_level smallint NOT NULL DEFAULT 50,
  webhook_config jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT source_system_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT source_system_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT source_system_webhook_config_is_object_chk
    CHECK (jsonb_typeof(webhook_config) = 'object'),
  CONSTRAINT source_system_trust_chk
    CHECK (trust_level BETWEEN 0 AND 100),
  CONSTRAINT source_system_key_per_tenant_uk
    UNIQUE (tenant_id, key)
);

CREATE TABLE mdm.DDM_ENTITY_EXTERNAL_ID (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  entity_id uuid NOT NULL
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  source_system_id uuid NOT NULL
    REFERENCES mdm.DDM_SOURCE_SYSTEM(id) ON DELETE RESTRICT,
  -- Externe ID exakt wie das Quellsystem sie liefert (kein Trim/Lower).
  external_id text NOT NULL,
  -- Confidence-Spalte ist V1 nicht aktiv. Migrationsweg für OP-29:
  --   ALTER TABLE … ADD COLUMN confidence numeric(3,2) NOT NULL DEFAULT 1.00
  --   ADD CONSTRAINT … CHECK (confidence BETWEEN 0.00 AND 1.00);
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  -- Bewusst keine updated_*-/deleted_*-Spalten:
  --   Mappings sind unveränderlich; Re-Mapping = unlink + link.
  --   Hard-Delete-Pfad, Historie steckt in AA_AUDIT_LOG.
  CONSTRAINT entity_external_id_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  -- Eindeutige externe ID je Quellsystem im Tenant (Inbound-Lookup eindeutig).
  CONSTRAINT entity_external_id_per_source_uk
    UNIQUE (tenant_id, source_system_id, external_id),
  -- Strict 1:1: pro Entität max. ein Mapping je Quellsystem.
  CONSTRAINT entity_external_id_per_entity_per_source_uk
    UNIQUE (tenant_id, source_system_id, entity_id)
);

CREATE INDEX ix_entity_external_id_lookup
  ON mdm.DDM_ENTITY_EXTERNAL_ID (source_system_id, external_id);

CREATE INDEX ix_entity_external_id_entity
  ON mdm.DDM_ENTITY_EXTERNAL_ID (entity_id);

-- =====================================================================
-- Anhänge / Dokumente
--
-- Bytes liegen in einem S3-kompatiblen Object-Store. Postgres hält nur
-- Metadaten + storage_uri + virus_scan_status. Upload via Pre-Signed-URL
-- (zwei Phasen: initiate + commit). Versionierung append-only über
-- (entity_id, logical_key, version_no). Permission erbt V1 von Entity.
-- =====================================================================

CREATE TABLE mdm.DDM_ATTACHMENT (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  -- NULLable: erlaubt entkoppelte Anhänge (nur an Job/Email/Case ohne fachliche Entity).
  entity_id uuid REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  logical_key text,
  version_no integer NOT NULL DEFAULT 1,
  is_current boolean NOT NULL DEFAULT true,
  filename text NOT NULL,
  mime text NOT NULL,
  size_bytes bigint NOT NULL,
  -- hex-codierter SHA-256 Hash der Bytes
  sha256 text NOT NULL,
  storage_backend text NOT NULL,
  storage_uri text NOT NULL,
  virus_scan_status text NOT NULL DEFAULT 'pending',
  virus_scan_at timestamptz,
  virus_scan_engine text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT attachment_logical_key_format_chk
    CHECK (logical_key IS NULL OR logical_key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT attachment_size_nonneg_chk
    CHECK (size_bytes >= 0),
  CONSTRAINT attachment_sha256_format_chk
    CHECK (sha256 ~ '^[a-f0-9]{64}$'),
  CONSTRAINT attachment_version_positive_chk
    CHECK (version_no >= 1),
  CONSTRAINT attachment_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT attachment_virus_scan_status_chk
    CHECK (virus_scan_status IN ('pending','clean','infected','skipped','failed')),
  CONSTRAINT attachment_storage_backend_format_chk
    CHECK (storage_backend ~ '^[a-z][a-z0-9_]*$')
);

-- Pro (entity, logical_key) maximal eine aktive aktuelle Version.
CREATE UNIQUE INDEX uq_attachment_slot_current
  ON mdm.DDM_ATTACHMENT (tenant_id, entity_id, logical_key)
  WHERE logical_key IS NOT NULL
    AND deleted_at IS NULL
    AND is_current = true;

-- Versions-Eindeutigkeit innerhalb eines Slots, verhindert Race-Append.
CREATE UNIQUE INDEX uq_attachment_slot_version
  ON mdm.DDM_ATTACHMENT (tenant_id, entity_id, logical_key, version_no)
  WHERE logical_key IS NOT NULL AND deleted_at IS NULL;

-- Listing-Hot-Path je Entity.
CREATE INDEX ix_attachment_entity
  ON mdm.DDM_ATTACHMENT (tenant_id, entity_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Storage-Cleanup / Konsistenzprüfung.
CREATE INDEX ix_attachment_storage_uri
  ON mdm.DDM_ATTACHMENT (storage_uri)
  WHERE deleted_at IS NULL;

-- Worker-Hot-Path für virus_scan-Jobs.
CREATE INDEX ix_attachment_pending_scan
  ON mdm.DDM_ATTACHMENT (virus_scan_status)
  WHERE virus_scan_status = 'pending';

-- Reservierung der zukünftigen Attachment-ID + Pre-Signed-URL-Lebenszeit.
-- Wird durch /attachments:initiate angelegt und von /attachments:commit geleert.
-- Cleanup-Job entfernt abgelaufene, nicht-committete Sessions nach 24 h.
CREATE TABLE mdm.AA_UPLOAD_SESSION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  attachment_uuid uuid NOT NULL,
  entity_id uuid REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  logical_key text,
  filename text NOT NULL,
  mime text NOT NULL,
  size_bytes bigint NOT NULL,
  storage_backend text NOT NULL,
  storage_uri text NOT NULL,
  expires_at timestamptz NOT NULL,
  committed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  CONSTRAINT upload_session_size_nonneg_chk
    CHECK (size_bytes >= 0),
  CONSTRAINT upload_session_logical_key_format_chk
    CHECK (logical_key IS NULL OR logical_key ~ '^[a-z][a-z0-9_]*$')
);

CREATE INDEX ix_upload_session_expiry
  ON mdm.AA_UPLOAD_SESSION (expires_at)
  WHERE committed_at IS NULL;

-- =====================================================================
-- Async Job Queue (interne Hintergrundarbeit)
--
-- Generische Tabelle für Worker-Pool, der über SELECT … FOR UPDATE
-- SKIP LOCKED Jobs claimt. Ergänzt — ersetzt nicht — AA_OUTBOX_EVENT:
-- Outbox = externer Eventbus, AA_JOB = interne Plattform-Pflichtarbeit
-- (Email, Bulk-Import, Reindex, Cleanup).
-- =====================================================================

CREATE TABLE mdm.AA_JOB (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  job_kind mdm.job_kind NOT NULL,
  status mdm.job_status NOT NULL DEFAULT 'pending',
  payload jsonb NOT NULL,
  priority smallint NOT NULL DEFAULT 100,
  available_at timestamptz NOT NULL DEFAULT now(),
  attempts integer NOT NULL DEFAULT 0,
  max_attempts integer NOT NULL DEFAULT 8,
  last_error text,
  claimed_by text,
  claimed_at timestamptz,
  completed_at timestamptz,
  idempotency_key text,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  CONSTRAINT job_payload_is_object_chk
    CHECK (jsonb_typeof(payload) = 'object'),
  CONSTRAINT job_attempts_nonneg_chk
    CHECK (attempts >= 0 AND max_attempts >= 1)
);

-- Eindeutigkeit der Idempotency-Key pro Tenant + Kind (nur wenn gesetzt).
CREATE UNIQUE INDEX job_idempotency_uk
  ON mdm.AA_JOB (tenant_id, job_kind, idempotency_key)
  WHERE idempotency_key IS NOT NULL;

-- Hot-Path-Index für die Worker-Auswahl.
CREATE INDEX ix_job_dispatch
  ON mdm.AA_JOB (status, priority, available_at)
  WHERE status = 'pending';

-- Stale-Claim-Recovery.
CREATE INDEX ix_job_claimed_stale
  ON mdm.AA_JOB (claimed_at)
  WHERE status = 'running';

-- Tenant-/Kind-Sichten für Admin & Reporting.
CREATE INDEX ix_job_tenant_kind
  ON mdm.AA_JOB (tenant_id, job_kind, status);

-- =====================================================================
-- Email-Subsystem
--
-- AA_EMAIL_TEMPLATE — versionierte, mehrsprachige Vorlage je Tenant
-- AA_EMAIL_SUPPRESSION — Sperrliste (Bounce/Complaint/Unsubscribe/manuell)
-- AA_EMAIL_LOG — append-only Versand-Log inkl. Provider-Antwort
--
-- Versand selbst läuft als AA_JOB mit job_kind='email_send'.
-- =====================================================================

CREATE TABLE mdm.AA_EMAIL_TEMPLATE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  key text NOT NULL,
  locale text NOT NULL,
  version integer NOT NULL DEFAULT 1,
  subject text NOT NULL,
  body_text text NOT NULL,
  body_html text,
  -- Liste erlaubter Platzhalter mit Typ-Hints, z. B.
  -- [{"name":"case_code","type":"string","required":true}, …]
  variables jsonb NOT NULL DEFAULT '[]'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT email_template_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  -- BCP-47 vereinfachte Form: 'de', 'en', 'de-CH'
  CONSTRAINT email_template_locale_format_chk
    CHECK (locale ~ '^[a-z]{2}(-[A-Z]{2})?$'),
  CONSTRAINT email_template_variables_is_array_chk
    CHECK (jsonb_typeof(variables) = 'array'),
  CONSTRAINT email_template_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT email_template_uk
    UNIQUE (tenant_id, key, locale, version)
);

CREATE TABLE mdm.AA_EMAIL_SUPPRESSION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  -- normalisiert (lowercase, trim) — Service-Layer-Pflicht
  email text NOT NULL,
  reason text NOT NULL,
  provider_event_id text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  CONSTRAINT email_suppression_reason_chk
    CHECK (reason IN ('bounce','complaint','unsubscribe','manual')),
  CONSTRAINT email_suppression_email_format_chk
    CHECK (position('@' in email) > 1),
  CONSTRAINT email_suppression_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT email_suppression_uk
    UNIQUE (tenant_id, email)
);

CREATE TABLE mdm.AA_EMAIL_LOG (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  -- AA_JOB darf gelöscht werden ohne den Log zu zerreißen.
  job_id uuid REFERENCES mdm.AA_JOB(id) ON DELETE SET NULL,
  to_email text NOT NULL,
  from_email text NOT NULL,
  template_key text,
  template_version integer,
  locale text,
  subject text,
  provider text NOT NULL,
  provider_message_id text,
  status text NOT NULL,
  sent_at timestamptz,
  delivered_at timestamptz,
  bounced_at timestamptz,
  error text,
  attachment_count smallint NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT email_log_status_chk
    CHECK (status IN ('queued','sent','delivered','bounced','failed','suppressed'))
);

CREATE INDEX ix_email_log_tenant_recent
  ON mdm.AA_EMAIL_LOG (tenant_id, created_at DESC);

CREATE INDEX ix_email_log_job
  ON mdm.AA_EMAIL_LOG (job_id)
  WHERE job_id IS NOT NULL;

CREATE INDEX ix_email_log_to_email
  ON mdm.AA_EMAIL_LOG (tenant_id, to_email, created_at DESC);

CREATE INDEX ix_email_log_provider_msg
  ON mdm.AA_EMAIL_LOG (provider, provider_message_id)
  WHERE provider_message_id IS NOT NULL;

-- =====================================================================
-- Outbox (transactional outbox pattern)
--
-- Jede mutierende Operation schreibt in derselben Transaktion einen
-- AA_OUTBOX_EVENT. Ein separater Worker liest pending-Events und stellt
-- sie an externe Konsumenten (Webhooks, Queue) zu.
--
-- Dadurch sind Events at-least-once, in Reihenfolge je entity_id und
-- robust gegen App-Abstürze: kein Event ohne committed DB-Zustand,
-- kein DB-Zustand ohne Event.
-- =====================================================================

CREATE TABLE mdm.AA_OUTBOX_EVENT (
  id bigserial PRIMARY KEY,
  tenant_id uuid REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  -- Stabiler Event-Typ wie 'entity.customer.updated', 'entity_relation.created',
  -- 'permission.granted'. Naming-Konvention: <objekt>.<typ?>.<verb>.
  event_type text NOT NULL,
  -- Schema-Version des payloads, damit Konsumenten breaking-changes erkennen.
  schema_version integer NOT NULL DEFAULT 1,
  -- Aggregat-ID für Reihenfolgegarantien je Entität (entity.id /
  -- entity_relation.id / role.id …). Worker darf events mit derselben
  -- aggregate_id nur in created_at-Reihenfolge zustellen.
  aggregate_type text NOT NULL,
  aggregate_id uuid,
  -- Verbindung zur auslösenden Audit-Zeile. correlation_id koppelt
  -- mehrere outbox-Events einer Service-Operation.
  audit_log_id bigint REFERENCES mdm.AA_AUDIT_LOG(id) ON DELETE RESTRICT,
  correlation_id uuid,
  payload jsonb NOT NULL,
  status mdm.outbox_status NOT NULL DEFAULT 'pending',
  attempts integer NOT NULL DEFAULT 0,
  last_error text,
  available_at timestamptz NOT NULL DEFAULT now(),
  dispatched_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  CONSTRAINT outbox_event_payload_is_object_chk
    CHECK (jsonb_typeof(payload) = 'object'),
  CONSTRAINT outbox_event_attempts_chk
    CHECK (attempts >= 0)
);

-- =====================================================================
-- RBAC + ACL (Identity, Roles, Permissions, Access Control Lists)
--
-- Modell:
--   - AA_APP_USER / AA_SERVICE_ACCOUNT: konkrete Identitäten (OIDC-gemappt).
--   - AA_PRINCIPAL_GROUP: optionale Gruppierung (z. B. aus IdP-Claims).
--   - AA_ROLE: benannte Rolle (z. B. 'administrator', 'editor', 'reader').
--   - AA_PERMISSION: deklarative Aktion auf Scope (DDM_ENTITY_TYPE, DDM_ENTITY, …).
--   - AA_ROLE_PERMISSION: weist Rollen Permissions zu (RBAC).
--   - AA_PRINCIPAL_ROLE: weist Identitäten/Gruppen Rollen zu.
--   - AA_ACL_ENTRY: feingranulare Allow/Deny-Regeln pro principal + scope
--                (überschreibt RBAC-Default, deny gewinnt).
--
-- Konsistenz:
--   - Scope-Felder per Check-Constraint passend zu acl_scope erzwungen.
--   - Soft Delete via deleted_at, partial unique-when-active.
--   - Trigger validieren, dass DDM_ENTITY_TYPE / DDM_ENTITY / DDM_RELATION_TYPE
--     existieren und nicht soft-deleted sind.
-- =====================================================================

CREATE TABLE mdm.AA_APP_USER (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  external_id text NOT NULL,
  issuer text NOT NULL,
  email text,
  display_name text,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT app_user_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT app_user_external_id_per_issuer_uk
    UNIQUE (issuer, external_id)
);

CREATE TABLE mdm.AA_SERVICE_ACCOUNT (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT service_account_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT service_account_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE TABLE mdm.AA_PRINCIPAL_GROUP (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT principal_group_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT principal_group_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE TABLE mdm.AA_PRINCIPAL_GROUP_MEMBER (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  principal_group_id uuid NOT NULL
    REFERENCES mdm.AA_PRINCIPAL_GROUP(id) ON DELETE RESTRICT,
  member_principal_type mdm.principal_type NOT NULL,
  member_principal_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT principal_group_member_kind_chk
    CHECK (member_principal_type IN ('user', 'service_account', 'group'))
);

CREATE TABLE mdm.AA_ROLE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  key text NOT NULL,
  name text NOT NULL,
  description text,
  is_system boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT role_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT role_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  -- key ist eindeutig je Tenant. Default-Set wird beim Tenant-Onboarding
  -- pro Tenant instanziiert (siehe AA_TENANT, mdm.fn_provision_tenant_defaults).
  CONSTRAINT role_key_per_tenant_uk
    UNIQUE (tenant_id, key)
);

CREATE TABLE mdm.AA_PERMISSION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  action mdm.permission_action NOT NULL,
  scope mdm.acl_scope NOT NULL,
  description text,
  is_system boolean NOT NULL DEFAULT false,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  CONSTRAINT permission_key_format_chk
    CHECK (key ~ '^[a-z][a-z0-9_:.]*$'),
  CONSTRAINT permission_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT permission_action_scope_uk
    UNIQUE (action, scope)
);

CREATE TABLE mdm.AA_ROLE_PERMISSION (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  role_id uuid NOT NULL REFERENCES mdm.AA_ROLE(id) ON DELETE RESTRICT,
  permission_id uuid NOT NULL REFERENCES mdm.AA_PERMISSION(id) ON DELETE RESTRICT,
  effect mdm.permission_effect NOT NULL DEFAULT 'allow',
  -- Optionale Scope-Bindung. Bedingung wird per Trigger
  -- gegen permission.scope durchgesetzt:
  --   scope=global         → alle scope_*-Felder NULL
  --   scope=tenant         → scope_tenant_id NOT NULL
  --   scope=entity_type    → scope_entity_type_id NOT NULL
  --   scope=entity         → scope_entity_id NOT NULL
  --   scope=relation_type  → scope_relation_type_id NOT NULL
  scope_tenant_id uuid
    REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  scope_entity_type_id uuid
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  scope_entity_id uuid
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  scope_relation_type_id uuid
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT role_permission_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  -- Höchstens ein Scope-Feld darf gesetzt sein:
  CONSTRAINT role_permission_scope_exclusive_chk
    CHECK (
      (CASE WHEN scope_tenant_id        IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN scope_entity_type_id IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN scope_entity_id      IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN scope_relation_type_id IS NOT NULL THEN 1 ELSE 0 END) <= 1
    )
);

CREATE TABLE mdm.AA_PRINCIPAL_ROLE (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  principal_type mdm.principal_type NOT NULL,
  principal_id uuid NOT NULL,
  role_id uuid NOT NULL REFERENCES mdm.AA_ROLE(id) ON DELETE RESTRICT,
  -- Optionale Scope-Einschränkung der Rollenzuweisung
  -- (z. B. „editor nur für DDM_ENTITY_TYPE=customer", „admin nur für AA_TENANT=acme").
  scope mdm.acl_scope NOT NULL DEFAULT 'global',
  scope_tenant_id uuid
    REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  scope_entity_type_id uuid
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  scope_entity_id uuid
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  scope_relation_type_id uuid
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  valid_from timestamptz,
  valid_to timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT principal_role_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT principal_role_validity_chk
    CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_from <= valid_to),
  CONSTRAINT principal_role_principal_kind_chk
    CHECK (principal_type IN ('user', 'service_account', 'group')),
  CONSTRAINT principal_role_scope_consistency_chk
    CHECK (
      (scope = 'global'        AND scope_tenant_id IS NULL
                               AND scope_entity_type_id IS NULL
                               AND scope_entity_id IS NULL
                               AND scope_relation_type_id IS NULL)
      OR (scope = 'tenant'        AND scope_tenant_id IS NOT NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_entity_id IS NULL
                                  AND scope_relation_type_id IS NULL)
      OR (scope = 'entity_type'   AND scope_entity_type_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_id IS NULL
                                  AND scope_relation_type_id IS NULL)
      OR (scope = 'entity'        AND scope_entity_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_relation_type_id IS NULL)
      OR (scope = 'relation_type' AND scope_relation_type_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_entity_id IS NULL)
    )
);

CREATE TABLE mdm.AA_ACL_ENTRY (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  principal_type mdm.principal_type NOT NULL,
  principal_id uuid NOT NULL,
  action mdm.permission_action NOT NULL,
  effect mdm.permission_effect NOT NULL DEFAULT 'allow',
  scope mdm.acl_scope NOT NULL,
  scope_tenant_id uuid
    REFERENCES mdm.AA_TENANT(id) ON DELETE RESTRICT,
  scope_entity_type_id uuid
    REFERENCES mdm.DDM_ENTITY_TYPE(id) ON DELETE RESTRICT,
  scope_entity_id uuid
    REFERENCES mdm.DDM_ENTITY(id) ON DELETE RESTRICT,
  scope_relation_type_id uuid
    REFERENCES mdm.DDM_RELATION_TYPE(id) ON DELETE RESTRICT,
  -- Optionales Attribut-Level-ACL: bezieht sich auf einen einzelnen
  -- Attribut-Key innerhalb eines DDM_ENTITY_TYPE / DDM_ENTITY (NULL = ganzer Datensatz).
  attribute_key text,
  valid_from timestamptz,
  valid_to timestamptz,
  reason text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text,
  deleted_at timestamptz,
  deleted_by text,
  CONSTRAINT acl_entry_metadata_is_object_chk
    CHECK (jsonb_typeof(metadata) = 'object'),
  CONSTRAINT acl_entry_validity_chk
    CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_from <= valid_to),
  CONSTRAINT acl_entry_attribute_key_format_chk
    CHECK (attribute_key IS NULL OR attribute_key ~ '^[a-z][a-z0-9_]*$'),
  CONSTRAINT acl_entry_scope_consistency_chk
    CHECK (
      (scope = 'global'        AND scope_tenant_id IS NULL
                               AND scope_entity_type_id IS NULL
                               AND scope_entity_id IS NULL
                               AND scope_relation_type_id IS NULL
                               AND attribute_key IS NULL)
      OR (scope = 'tenant'        AND scope_tenant_id IS NOT NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_entity_id IS NULL
                                  AND scope_relation_type_id IS NULL
                                  AND attribute_key IS NULL)
      OR (scope = 'entity_type'   AND scope_entity_type_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_id IS NULL
                                  AND scope_relation_type_id IS NULL)
      OR (scope = 'entity'        AND scope_entity_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_relation_type_id IS NULL)
      OR (scope = 'relation_type' AND scope_relation_type_id IS NOT NULL
                                  AND scope_tenant_id IS NULL
                                  AND scope_entity_type_id IS NULL
                                  AND scope_entity_id IS NULL
                                  AND attribute_key IS NULL)
    )
);

Verwandte Dokumente

• Schema und Konventionen
• Indizes
• Trigger und Funktionen
• Views