2a70441eaa
VVT: Master library tables (7 catalogs), 500+ seed entries, process templates with instantiation, library API endpoints + 18 tests. Loeschfristen: Baseline catalog, compliance checks, profiling engine, HTML document generator, MkDocs documentation. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
106 lines
3.7 KiB
PL/PgSQL
106 lines
3.7 KiB
PL/PgSQL
-- Migration 064: VVT Master Libraries — 8 global reference tables
|
|
-- These are shared across all tenants (no tenant_id).
|
|
|
|
BEGIN;
|
|
|
|
-- 1. Data Subjects (Betroffenenkategorien)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_data_subjects (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
label_de VARCHAR(200) NOT NULL,
|
|
description_de TEXT,
|
|
art9_relevant BOOLEAN DEFAULT FALSE,
|
|
typical_for JSONB DEFAULT '[]'::jsonb,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 2. Data Categories (Datenkategorien — hierarchisch)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_data_categories (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
parent_id VARCHAR(50) REFERENCES vvt_lib_data_categories(id) ON DELETE SET NULL,
|
|
label_de VARCHAR(200) NOT NULL,
|
|
description_de TEXT,
|
|
is_art9 BOOLEAN DEFAULT FALSE,
|
|
is_art10 BOOLEAN DEFAULT FALSE,
|
|
risk_weight INTEGER DEFAULT 1 CHECK (risk_weight BETWEEN 1 AND 5),
|
|
default_retention_rule VARCHAR(50),
|
|
default_legal_basis VARCHAR(50),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_vvt_lib_data_categories_parent ON vvt_lib_data_categories(parent_id);
|
|
|
|
-- 3. Recipients (Empfaengerkategorien)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_recipients (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('INTERNAL', 'PROCESSOR', 'CONTROLLER', 'AUTHORITY')),
|
|
label_de VARCHAR(200) NOT NULL,
|
|
description_de TEXT,
|
|
is_third_country BOOLEAN DEFAULT FALSE,
|
|
country VARCHAR(5),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 4. Legal Bases (Rechtsgrundlagen)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_legal_bases (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
article VARCHAR(50) NOT NULL,
|
|
type VARCHAR(30) NOT NULL CHECK (type IN ('CONSENT', 'CONTRACT', 'LEGAL_OBLIGATION', 'VITAL_INTEREST', 'PUBLIC_TASK', 'LEGITIMATE_INTEREST', 'ART9', 'NATIONAL')),
|
|
label_de VARCHAR(300) NOT NULL,
|
|
description_de TEXT,
|
|
is_art9 BOOLEAN DEFAULT FALSE,
|
|
typical_national_law VARCHAR(100),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 5. Retention Rules (Aufbewahrungsfristen)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_retention_rules (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
label_de VARCHAR(300) NOT NULL,
|
|
description_de TEXT,
|
|
legal_basis VARCHAR(200),
|
|
duration INTEGER NOT NULL,
|
|
duration_unit VARCHAR(10) NOT NULL CHECK (duration_unit IN ('DAYS', 'MONTHS', 'YEARS')),
|
|
start_event VARCHAR(200),
|
|
deletion_procedure VARCHAR(500),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 6. Transfer Mechanisms (Uebermittlungsmechanismen)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_transfer_mechanisms (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
label_de VARCHAR(300) NOT NULL,
|
|
description_de TEXT,
|
|
article VARCHAR(50),
|
|
requires_tia BOOLEAN DEFAULT FALSE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 7. Purposes (Verarbeitungszwecke)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_purposes (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
label_de VARCHAR(300) NOT NULL,
|
|
description_de TEXT,
|
|
typical_legal_basis VARCHAR(50),
|
|
typical_for JSONB DEFAULT '[]'::jsonb,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 8. TOMs (Technisch-Organisatorische Massnahmen)
|
|
CREATE TABLE IF NOT EXISTS vvt_lib_toms (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
category VARCHAR(30) NOT NULL CHECK (category IN ('accessControl', 'confidentiality', 'integrity', 'availability', 'separation')),
|
|
label_de VARCHAR(300) NOT NULL,
|
|
description_de TEXT,
|
|
art32_reference VARCHAR(100),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMIT;
|