Fassung: ad-terms-2026-10-20-v2

Export-Feldkatalog

Bezugsstand: AusbildungsDesk12.4.0, Schema87. Statisch aus dem bestehenden Exportformat abgeleitet, keine Kundendatensätze. Ein fremdes System muss Formate und fachliche Beziehungen passend übernehmen; vollständige Funktionsgleichheit wird nicht zugesagt.

Lesen ohne falsche Importannahmen

Jede data/<relation>.jsonl enthält je Zeile ein JSON-Objekt aus PostgreSQL to_jsonb(item); die Exportquelle nimmt die jeweils vorhandenen Spalten der freigegebenen Relation mit, kein davon unabhängiges API-Feldmapping. Schlüssel und Verbundbezüge erhalten, nicht Tabellen allein nach Personennamen verbinden. Reihenfolge der Zeilen ist keine Fachsortierung oder Wiedereinspielreihenfolge. Text-IDs nicht pauschal nach UUID casten oder kürzen.

Es gibt keinen live abgefragten information_schema-Befund. „NULL zulässig“ berücksichtigt explizite Spaltendeklarationen/NOT-NULL-Änderungen und deklarierte Primärschlüssel. Zusätzliche CHECKs/Trigger können Werte weiter einschränken. Eine angezeigte Ausgangsdefinition kann durch den direkt darunter vollständig angegebenen ALTER-Verlauf geändert sein; aus dem Ausgangscheck keine aktuelle zulässige Wertemenge ableiten.

Beziehungen: REFERENCES-Belege zeigen tatsächlich deklarierte Zielspalten, keine bloß nach *_id geratenen Joins. Constraints werden einschließlich DROP/ADD in Migrationsreihenfolge gezeigt, nicht als kumulativ gleichzeitig gültig behauptet. Automatisch vergebene Constraintnamen und bedingte/dynamische Änderungen sind ohne Datenbanklauf nicht abschließend aufgelöst; die betroffenen Fälle sind unten konkret benannt. Ein Bezug auf eine nicht exportierte Authrelation erlaubt weder Loginrekonstruktion noch Zugriff auf deren Geheimnisse.

Fachsemantik, Einheit, Statuswirkung und innere JSON-Struktur werden nur dort erklärt, wo die vorgegebenen Quellen sie verlässlich tragen. Unklare Felder tragen einen expliziten Hinweis statt erfundener Fachdefinition. Dies ist kein vollständiges Importhandbuch aller Fremdsysteme und keine API-/Schnittstellenfreigabe.

Container, Manifest und Inspektor

• .adtx: 8Bytes ADTXEX01,12Bytes IV, AES-256-GCM-Chiffretext,

16Bytes Tag; verschlüsselt ist gzip-tar, kein ZIP.

• Erlaubte Nutzdateien: manifest.json, data/<relation>.jsonl, files/<id>.

Originalname/MIME/Größe und SHA-256 stehen im Manifest; Datei-ID nicht als Originalnamen interpretieren. Kein README oder SQL-Schema im vorhandenen Archiv.

• Manifest: format=ausbildungsdesk-tenant-exit-export, version=1,

createdAt, releaseVersion, tenantId, correlationId, reasonCode, migrations, relations[], files[]. Relationsobjekte: name,path,rows,bytes,sha256; Dateiobjekte: id,path,name,mime,size,sha256. Dieses externe Quelldokument ist nicht Bestandteil eines bereits erzeugten Kundendownloads.

• releaseVersion und Migrationsanzahl sind keine enthaltenen vollständigen

Git-/SQL-Hashes. Bei Übergabe passenden Quell-/Schema-/Werkzeugstand getrennt zuordnen; dieser Katalog bindet ihn über die obigen Hashes.

• Der bestehende Inspektor benötigt Archiv, separaten Schlüssel und leeres

restriktives Ziel. Er prüft GCM, erlaubte Pfade, striktes Manifest und Hashes. Er baut keinen Fremdsystemimport und prüft nicht jedes Fachfeld auf vollständige Rekonstruierbarkeit. Keine Ausgabe eines Schlüssels/echter Pfade hier.

• Sicherheitsgrenzen laut Quellkonstanten: höchstens10Millionen Zeilen/64GiB

pro Relation sowie64GiB Archiv; kein Leistungsversprechen für diese Größen.

Präzise nicht vollständig statisch aufgelöste Stellen

• capacity_windows, Migration0050: altes Zeitbereichs-CHECK wird anhand eines

Katalogtreffers dynamisch entfernt. Das neue benannte CHECK erlaubt period_end IS NULL OR period_start<=period_end; NULL bedeutet „bis auf Weiteres“. Der entfernte automatisch vergebene Name wird nicht erfunden.

• school_schedule_rules, Migration0073: bedingt wird

school_rule_calendar_tenant_fk FOREIGN KEY(tenant_id,school_calendar_id) REFERENCES school_calendars(tenant_id,id) angelegt. Als deklarierte bedingte Beziehung dokumentiert, nicht frisch im Livekatalog nachgewiesen.

• JSON-Innenstrukturen: bei38 der41 JSON-Spalten wird hier keine vollständige

Fachstruktur aus einem bloßen SQL-Typ/äußeren CHECK abgeleitet. Das ist jeweils unmittelbar am Feld markiert. Auch die unten erklärten Kalender-/Zusatzfeldstrukturen ersetzen keine Gesamtdokumentation aller Fach-/Triggerregeln.

• Polymorphe Entity-/Scope-/Dokumentreferenzen ohne REFERENCES und fachliche

Zeit-/Statuswirkungen sind ohne weitere belegte Zuordnung nicht als Fremdschlüssel oder vollständige Prozessregel beschrieben.

• Die aktuelle wirksame Gesamtmenge benannter/automatisch vergebener Checks/FKs

ist nicht per DB introspektiert; der lesbare Deklarations-/Änderungsverlauf ersetzt keine Live-Constraintprüfung.

Zwei durch vorhandene SQL-Trigger belegte JSON-Strukturen

Migration0073 begrenzt school_calendars.schools_json auf höchstens100 eindeutige, nicht leere Schulbindungsstrings (je1–150Zeichen). periods_json ist ein Array von höchstens200Objekten mit genau label,kind,start,end; label1–120Zeichen, kind holiday|day_off|school_day, start/end als Datum YYYY-MM-DD innerhalb der Kalendergrenzen. Unterschiedliche Nichtferien-Ausnahmen dürfen sich nach dem Trigger nicht widersprechen. Quellenbeleg: 0073. Nicht daraus ableiten, dass jede JSON-Spalte denselben Aufbau hat.

Zusatzfeld-Wertformen, vorhandener Guard0072

apprentice_field_values.value_json wird mit der zugehörigen apprentice_field_definitions.field_type interpretiert: boolean als JSON-Boolean, text als String1–200Zeichen, number als JSON-Zahl mit Betrag höchstens10^12, date als Kalenderdatumsstring, validity als genaues Objekt aus on/validUntil (Datum, gültig-bis nicht vor durchgeführt-am), select als Optionsstring, multiselect als duplikatfreies Array aus den exportierten Optionswerten. JSON-null ist bei optionaler Definition zulässig; SQL NULL und fehlende Definition sind etwas anderes. Quelle: Migration0072, bestehender ad_additional_field_guard, kein neuer Lauf. Diese konkrete Interpretation ist damit erklärt; aus den anderen offenen Feinsemantiken keine Rohwertsperre ableiten.

Relationsregister

• tenants · 6Felder

• businesses · 9Felder

• business_addresses · 10Felder

• business_contacts · 11Felder

• users · 6Felder

• tenant_memberships · 7Felder

• membership_business_access · 6Felder

• membership_business_scopes · 4Felder

• training_professions · 6Felder

• business_training_professions · 7Felder

• profession_import_aliases · 6Felder

• org_units · 11Felder

• org_hierarchy · 6Felder

• apprentices · 24Felder

• apprentice_profiles · 4Felder

• employee_profiles · 15Felder

• employee_department_assignments · 8Felder

• assessment_tenant_policy · 3Felder

• person_business_assignment_events · 15Felder

• assessment_apprentice_policy · 4Felder

• domain_audit · 11Felder

• domain_audit_context · 9Felder

• tenant_entitlements · 8Felder

• membership_expiry · 4Felder

• tenant_membership_scopes · 10Felder

• scoped_delegations · 11Felder

• delegation_permissions · 3Felder

• delegation_apprentices · 4Felder

• referral_accounts · 9Felder

• referrals · 17Felder

• training_interruptions · 14Felder

• interruption_decisions · 8Felder

• school_schedule_rules · 16Felder

• school_day_exceptions · 10Felder

• school_calendars · 15Felder

• school_calendar_versions · 6Felder

• availability_blocks · 14Felder

• capacity_windows · 17Felder

• capacity_window_versions · 8Felder

• plan_years · 5Felder

• plan_days · 9Felder

• plan_revisions · 9Felder

• capacity_requests · 10Felder

• capacity_decisions · 7Felder

• capacity_consumptions · 5Felder

• plan_templates · 6Felder

• plan_template_versions · 10Felder

• plan_template_steps · 9Felder

• plan_template_step_alternatives · 7Felder

• plan_template_publications · 8Felder

• curriculum_items · 14Felder

• curriculum_versions · 7Felder

• objective_progress · 14Felder

• objective_progress_versions · 7Felder

• department_objective_rules · 5Felder

• department_objective_rule_versions · 9Felder

• department_completions · 12Felder

• department_completion_events · 8Felder

• objective_department_origins · 8Felder

• learning_modules · 16Felder

• learning_module_professions · 4Felder

• learning_module_training_years · 4Felder

• learning_module_objectives · 7Felder

• learning_module_versions · 8Felder

• learning_progress · 24Felder

• learning_progress_versions · 8Felder

• objective_module_origins · 8Felder

• learning_support_needs · 16Felder

• learning_support_need_events · 8Felder

• apprentice_private_notes · 11Felder

• apprentice_probation · 17Felder

• probation_schedule_policies · 9Felder

• training_conversations · 14Felder

• training_conversation_versions · 8Felder

• apprentice_field_definitions · 18Felder

• apprentice_field_values · 9Felder

• apprentice_field_definition_versions · 7Felder

• apprentice_field_value_versions · 9Felder

• tenant_feedback_policies · 5Felder

• tenant_feedback_policy_events · 6Felder

• apprentice_feedback · 11Felder

• reportbook_policies · 11Felder

• training_reports · 10Felder

• training_report_versions · 8Felder

• training_report_events · 9Felder

• training_report_version_files · 8Felder

• training_assessments · 9Felder

• training_assessment_versions · 8Felder

• training_assessment_events · 9Felder

• assessment_templates · 9Felder

• assessment_template_versions · 10Felder

• assessment_template_events · 9Felder

• certificate_templates · 9Felder

• certificate_template_versions · 8Felder

• certificate_template_events · 9Felder

• training_certificates · 12Felder

• training_certificate_versions · 9Felder

• training_certificate_events · 9Felder

• training_grades · 10Felder

• training_grade_versions · 14Felder

• training_grade_events · 9Felder

• operational_tasks · 26Felder

• operational_task_events · 14Felder

• assessment_schedule_policies · 9Felder

• assessment_schedule_policy_events · 10Felder

• planning_notification_policies · 5Felder

• onboarding_process_templates · 24Felder

• onboarding_process_template_professions · 7Felder

• onboarding_process_template_events · 10Felder

• runtime_files · 13Felder

• file_acknowledgements · 5Felder

• mandatory_trainings · 10Felder

• mandatory_training_professions · 4Felder

• mandatory_training_org_units · 4Felder

• mandatory_training_versions · 7Felder

• mandatory_training_records · 22Felder

• mandatory_training_record_versions · 8Felder

• person_imports · 10Felder

• examination_events · 14Felder

• examination_event_versions · 8Felder

• examination_reminders · 7Felder

• tenant_reminder_policies · 6Felder

• retention_operations · 7Felder

• retention_policy_versions · 13Felder

• retention_cases · 11Felder

• retention_case_events · 8Felder

• retention_legal_holds · 7Felder

• retention_legal_hold_releases · 7Felder

• restore_erasure_tombstones · 13Felder

• erasure_execution_receipts · 14Felder

• tenant_exit_export_receipts · 11Felder

tenants

Exportpfad: data/tenants.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD CONSTRAINT tenants_access_status_check CHECK(status IN ('pending','active','suspended','cancelled','expired')) — 0005_provisioning.sql

businesses

Exportpfad: data/businesses.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0021_multi_business_foundation.sql

• UNIQUE(tenant_id,code) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id) REFERENCES public.tenants(id) — 0021_multi_business_foundation.sql

• CHECK(updated_at>=created_at) — 0021_multi_business_foundation.sql

• CHECK(NOT is_default OR id='default') — 0021_multi_business_foundation.sql

business-addresses

Exportpfad: data/business_addresses.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

business-contacts

Exportpfad: data/business_contacts.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• CHECK(email IS NOT NULL OR phone IS NOT NULL) — 0021_multi_business_foundation.sql

• CHECK(updated_at>=created_at) — 0021_multi_business_foundation.sql

users

Exportpfad: data/users.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD CONSTRAINT users_platform_user_id_fkey FOREIGN KEY(platform_user_id) REFERENCES public."user"(id) ON DELETE SET NULL — 0002_authentication.sql

Exportselektion: nur Benutzer, deren ID in einer Mitgliedschaft des angeforderten Mandanten vorkommt. platform_user_id kann auf nicht exportierte Authidentität verweisen; kein Loginmaterial.

tenant-memberships

Exportpfad: data/tenant_memberships.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,id) — 0001_identity_directory.sql

• UNIQUE(tenant_id,user_id) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD CONSTRAINT tenant_memberships_tenant_id_user_id_unique UNIQUE(tenant_id,id,user_id) — 0015_kobico_provisioning_api.sql

• DROP CONSTRAINT tenant_memberships_role_check — 0016_tenant_administration.sql

• ADD CONSTRAINT tenant_memberships_role_check CHECK(role IN('tenant_owner','tenant_admin','training_manager','trainer','training_officer', 'training_coordinator','trainee','works_council','hr_readonly','auditor')) — 0016_tenant_administration.sql

• DROP CONSTRAINT tenant_memberships_status_check — 0016_tenant_administration.sql

• ADD CONSTRAINT tenant_memberships_status_check CHECK(status IN('active','inactive')) — 0016_tenant_administration.sql

• DROP CONSTRAINT tenant_memberships_role_check — 0017_role_preview_demo.sql

• ADD CONSTRAINT tenant_memberships_role_check CHECK(role IN('tenant_owner','tenant_admin','training_manager','trainer','training_officer', 'training_coordinator','trainee','works_council','hr_readonly','auditor','demo_presenter')) — 0017_role_preview_demo.sql

membership-business-access

Exportpfad: data/membership_business_access.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,membership_id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,membership_id) REFERENCES public.tenant_memberships(tenant_id,id) ON DELETE CASCADE — 0021_multi_business_foundation.sql

• CHECK(updated_at>=created_at) — 0021_multi_business_foundation.sql

membership-business-scopes

Exportpfad: data/membership_business_scopes.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,membership_id,business_id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,membership_id) REFERENCES public.membership_business_access(tenant_id,membership_id) ON DELETE CASCADE — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

training-professions

Exportpfad: data/training_professions.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,code) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN status text NOT NULL DEFAULT 'active' CHECK(status IN('active','inactive','archived')) — 0064_directory_training_lifecycle.sql

business-training-professions

Exportpfad: data/business_training_professions.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,profession_code) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0021_multi_business_foundation.sql

• CHECK(updated_at>=created_at) — 0021_multi_business_foundation.sql

profession-import-aliases

Exportpfad: data/profession_import_aliases.jsonl. Ausgangsquelle: 0045_profession_import_resolution.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,alias_key) — 0045_profession_import_resolution.sql

• FOREIGN KEY(tenant_id) REFERENCES public.tenants(id) — 0045_profession_import_resolution.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0045_profession_import_resolution.sql

• FOREIGN KEY(created_by) REFERENCES public.users(id) — 0045_profession_import_resolution.sql

• CHECK(alias_key=lower(regexp_replace(btrim(alias_label),'[[:space:]]+',' ','g'))) — 0045_profession_import_resolution.sql

org-units

Exportpfad: data/org_units.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,id) — 0001_identity_directory.sql

• UNIQUE(tenant_id,code) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT org_units_tenant_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT org_units_tenant_business_code_unique UNIQUE(tenant_id,business_id,code) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT org_units_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT org_units_tenant_id_code_key — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT org_units_status_check — 0064_directory_training_lifecycle.sql

• ADD CONSTRAINT org_units_status_check CHECK(status IN('active','inactive','archived')) — 0064_directory_training_lifecycle.sql

• ADD COLUMN IF NOT EXISTS calendar_color text CHECK (calendar_color IS NULL OR calendar_color ~ '^#[0-9a-f]{6}$') — 0075_calendar_colors.sql

• ADD COLUMN IF NOT EXISTS calendar_color_revision integer NOT NULL DEFAULT 0 CHECK (calendar_color_revision >= 0) — 0075_calendar_colors.sql

org-hierarchy

Exportpfad: data/org_hierarchy.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,org_unit_id) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,parent_id) REFERENCES public.org_units(tenant_id,id) — 0001_identity_directory.sql

• CHECK(parent_id IS NULL OR parent_id<>org_unit_id) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT org_hierarchy_unit_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT org_hierarchy_parent_business_fkey FOREIGN KEY(tenant_id,business_id,parent_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

apprentices

Exportpfad: data/apprentices.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,id) — 0001_identity_directory.sql

• UNIQUE(tenant_id,personnel_number) — 0001_identity_directory.sql

• CHECK(training_start<=training_end) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,target_org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT apprentices_tenant_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT apprentices_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT apprentices_target_org_business_fkey FOREIGN KEY(tenant_id,business_id,target_org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD CONSTRAINT apprentices_business_profession_fkey FOREIGN KEY(tenant_id,business_id,profession_code) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD COLUMN business_assignment_event_id text — 0030_person_business_transfer.sql

• ALTER COLUMN business_assignment_event_id SET NOT NULL — 0030_person_business_transfer.sql

• ADD CONSTRAINT apprentices_business_assignment_event_fkey FOREIGN KEY(tenant_id,business_assignment_event_id,id) REFERENCES public.person_business_assignment_events(tenant_id,id,apprentice_id) DEFERRABLE INITIALLY DEFERRED — 0030_person_business_transfer.sql

• ADD COLUMN revision integer NOT NULL DEFAULT 1 CHECK(revision>0) — 0031_manual_people.sql

• ADD COLUMN regular_training_end date — 0031_manual_people.sql

• ADD COLUMN personnel_number_generated boolean NOT NULL DEFAULT false — 0031_manual_people.sql

• ALTER COLUMN regular_training_end SET NOT NULL — 0031_manual_people.sql

• ADD CONSTRAINT apprentices_regular_training_period_check CHECK(training_start<=regular_training_end) — 0031_manual_people.sql

• DROP CONSTRAINT apprentices_status_check — 0031_manual_people.sql

• ADD CONSTRAINT apprentices_status_check CHECK(status IN('active','inactive','archived')) — 0031_manual_people.sql

• ADD COLUMN training_outcome text NOT NULL DEFAULT 'in_training' CHECK(training_outcome IN('in_training','completed','completed_early','withdrawn')) — 0064_directory_training_lifecycle.sql

• ADD COLUMN actual_training_end date — 0064_directory_training_lifecycle.sql

• ADD COLUMN withdrawal_reason text CHECK(withdrawal_reason IN('probation_company','probation_apprentice', 'agreement','resignation','dismissal','business_change','other')) — 0064_directory_training_lifecycle.sql

• ADD COLUMN withdrawal_note text NOT NULL DEFAULT '' CHECK(length(withdrawal_note)<=1000) — 0064_directory_training_lifecycle.sql

• ADD CONSTRAINT apprentices_training_lifecycle_check CHECK( (training_outcome='in_training' AND actual_training_end IS NULL AND withdrawal_reason IS NULL AND withdrawal_note='') OR (training_outcome<>'in_training' AND actual_training_end IS NOT NULL AND actual_training_end>=training_start AND status<>'active' AND ((training_outcome='withdrawn' AND withdrawal_reason IS NOT NULL) OR (training_outcome IN('completed','completed_early') AND withdrawal_reason IS NULL AND withdrawal_note='')) AND (training_outcome<>'completed_early' OR actual_training_end<regular_training_end) AND (training_outcome<>'completed' OR actual_training_end>=regular_training_end))) — 0064_directory_training_lifecycle.sql

• ADD COLUMN role_test_allowed boolean NOT NULL DEFAULT false — 0066_controlled_role_test.sql

apprentice-profiles

Exportpfad: data/apprentice_profiles.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,apprentice_id) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0001_identity_directory.sql

employee-profiles

Exportpfad: data/employee_profiles.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0001_identity_directory.sql

• UNIQUE(tenant_id,personnel_number) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,department_id) REFERENCES public.org_units(tenant_id,id) — 0001_identity_directory.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• DROP CONSTRAINT employee_profiles_intended_role_check — 0016_tenant_administration.sql

• ADD CONSTRAINT employee_profiles_intended_role_check CHECK(intended_role IN('tenant_owner','tenant_admin','training_manager','trainer','training_officer', 'training_coordinator','works_council','hr_readonly','auditor')) — 0016_tenant_administration.sql

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT employee_profiles_tenant_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT employee_profiles_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT employee_profiles_department_business_fkey FOREIGN KEY(tenant_id,business_id,department_id) REFERENCES public.org_units(tenant_id,business_id,id) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD COLUMN business_assignment_event_id text — 0030_person_business_transfer.sql

• ALTER COLUMN business_assignment_event_id SET NOT NULL — 0030_person_business_transfer.sql

• ADD CONSTRAINT employee_profiles_business_assignment_event_fkey FOREIGN KEY(tenant_id,business_assignment_event_id,id) REFERENCES public.person_business_assignment_events(tenant_id,id,employee_id) DEFERRABLE INITIALLY DEFERRED — 0030_person_business_transfer.sql

• ADD COLUMN personnel_number_generated boolean NOT NULL DEFAULT false — 0031_manual_people.sql

• DROP CONSTRAINT employee_profiles_status_check — 0031_manual_people.sql

• ADD CONSTRAINT employee_profiles_status_check CHECK(status IN('active','inactive','archived')) — 0031_manual_people.sql

employee-department-assignments

Exportpfad: data/employee_department_assignments.jsonl. Ausgangsquelle: 0034_employee_department_assignments.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,employee_id,org_unit_id,assigned_at) — 0034_employee_department_assignments.sql

• FOREIGN KEY(tenant_id,business_id,employee_id) REFERENCES public.employee_profiles(tenant_id,business_id,id) — 0034_employee_department_assignments.sql

• FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0034_employee_department_assignments.sql

• CHECK((ended_at IS NULL AND ended_by IS NULL) OR (ended_at IS NOT NULL AND ended_by IS NOT NULL AND assigned_at<=ended_at)) — 0034_employee_department_assignments.sql

assessment-tenant-policy

Exportpfad: data/assessment_tenant_policy.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

person-business-assignment-events

Exportpfad: data/person_business_assignment_events.jsonl. Ausgangsquelle: 0021_multi_business_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,employee_id) REFERENCES public.employee_profiles(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,from_business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,to_business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• FOREIGN KEY(tenant_id,actor_membership_id,actor_user_id) REFERENCES public.tenant_memberships(tenant_id,id,user_id) — 0021_multi_business_foundation.sql

• CHECK( (person_kind='apprentice' AND apprentice_id IS NOT NULL AND employee_id IS NULL) OR (person_kind='employee' AND apprentice_id IS NULL AND employee_id IS NOT NULL) ) — 0021_multi_business_foundation.sql

• CHECK(from_business_id IS NULL OR from_business_id<>to_business_id) — 0021_multi_business_foundation.sql

• CHECK( (source IN('migration','single_business_default') AND actor_user_id IS NULL AND actor_membership_id IS NULL AND audit_id IS NULL) OR (source NOT IN('migration','single_business_default') AND actor_user_id IS NOT NULL AND actor_membership_id IS NOT NULL AND audit_id IS NOT NULL) ) — 0021_multi_business_foundation.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD CONSTRAINT person_business_assignment_audit_fkey FOREIGN KEY(tenant_id,audit_id,actor_user_id,created_at,to_business_id) REFERENCES public.domain_audit(tenant_id,id,actor_id,created_at,business_id) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD CONSTRAINT person_business_assignment_apprentice_identity_unique UNIQUE(tenant_id,id,apprentice_id) — 0030_person_business_transfer.sql

• ADD CONSTRAINT person_business_assignment_employee_identity_unique UNIQUE(tenant_id,id,employee_id) — 0030_person_business_transfer.sql

assessment-apprentice-policy

Exportpfad: data/assessment_apprentice_policy.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,apprentice_id) — 0001_identity_directory.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0001_identity_directory.sql

domain-audit

Exportpfad: data/domain_audit.jsonl. Ausgangsquelle: 0001_identity_directory.sql.

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD CONSTRAINT domain_audit_tenant_id_id_actor_created_unique UNIQUE(tenant_id,id,actor_id,created_at) — 0018_audit_foundation.sql

• ADD CONSTRAINT domain_audit_detail_no_secrets CHECK(public.ad_audit_detail_is_safe(detail_json)) — 0018_audit_foundation.sql

• ADD COLUMN business_id text — 0021_multi_business_foundation.sql

• ADD CONSTRAINT domain_audit_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT domain_audit_business_identity_unique UNIQUE(tenant_id,id,actor_id,created_at,business_id) — 0021_multi_business_foundation.sql

domain-audit-context

Exportpfad: data/domain_audit_context.jsonl. Ausgangsquelle: 0018_audit_foundation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• FOREIGN KEY(tenant_id,audit_id,accountable_user_id,created_at) REFERENCES public.domain_audit(tenant_id,id,actor_id,created_at) — 0018_audit_foundation.sql

• FOREIGN KEY(tenant_id,accountable_membership_id,accountable_user_id) REFERENCES public.tenant_memberships(tenant_id,id,user_id) — 0018_audit_foundation.sql

tenant-entitlements

Exportpfad: data/tenant_entitlements.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,feature_key) — 0004_authorization.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN usage_value integer CHECK(usage_value IS NULL OR usage_value>=0) — 0086_active_apprentice_entitlement.sql

membership-expiry

Exportpfad: data/membership_expiry.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,membership_id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,membership_id) REFERENCES public.tenant_memberships(tenant_id,id) ON DELETE CASCADE — 0004_authorization.sql

• CHECK(num_nonnulls(expires_on,expires_at)=1) — 0004_authorization.sql

tenant-membership-scopes

Exportpfad: data/tenant_membership_scopes.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• FOREIGN KEY(tenant_id,membership_id) REFERENCES public.tenant_memberships(tenant_id,id) ON DELETE CASCADE — 0004_authorization.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0004_authorization.sql

• CHECK((scope_type='org_unit' AND org_unit_id IS NOT NULL AND apprentice_id IS NULL) OR (scope_type='apprentice' AND apprentice_id IS NOT NULL AND org_unit_id IS NULL)) — 0004_authorization.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT tenant_membership_scopes_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT tenant_membership_scopes_org_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD CONSTRAINT tenant_membership_scopes_apprentice_business_fkey FOREIGN KEY(tenant_id,business_id,apprentice_id) REFERENCES public.apprentices(tenant_id,business_id,id) DEFERRABLE INITIALLY DEFERRED — 0021_multi_business_foundation.sql

• ADD COLUMN profession_code text — 0052_trainer_profession_scope.sql

• DROP CONSTRAINT tenant_membership_scopes_scope_type_check — 0052_trainer_profession_scope.sql

• DROP CONSTRAINT tenant_membership_scopes_check — 0052_trainer_profession_scope.sql

• ADD CONSTRAINT tenant_membership_scopes_scope_type_check CHECK(scope_type IN ('org_unit','apprentice','profession')) — 0052_trainer_profession_scope.sql

• ADD CONSTRAINT tenant_membership_scopes_target_check CHECK( (scope_type='org_unit' AND org_unit_id IS NOT NULL AND apprentice_id IS NULL AND profession_code IS NULL) OR (scope_type='apprentice' AND apprentice_id IS NOT NULL AND org_unit_id IS NULL AND profession_code IS NULL) OR (scope_type='profession' AND profession_code IS NOT NULL AND org_unit_id IS NULL AND apprentice_id IS NULL) ) — 0052_trainer_profession_scope.sql

• ADD CONSTRAINT tenant_membership_scopes_profession_business_fkey FOREIGN KEY(tenant_id,business_id,profession_code) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) DEFERRABLE INITIALLY DEFERRED — 0052_trainer_profession_scope.sql

scoped-delegations

Exportpfad: data/scoped_delegations.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,represented_membership_id) REFERENCES public.tenant_memberships(tenant_id,id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,delegate_membership_id) REFERENCES public.tenant_memberships(tenant_id,id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,created_by) REFERENCES public.tenant_memberships(tenant_id,user_id) — 0004_authorization.sql

• CHECK(represented_membership_id<>delegate_membership_id) — 0004_authorization.sql

• CHECK(starts_on<=ends_on) — 0004_authorization.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT scoped_delegations_tenant_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT scoped_delegations_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

delegation-permissions

Exportpfad: data/delegation_permissions.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,delegation_id,permission) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,delegation_id) REFERENCES public.scoped_delegations(tenant_id,id) ON DELETE CASCADE — 0004_authorization.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• DROP CONSTRAINT delegation_permissions_permission_check — 0058_certificate_delegation_permissions.sql

• ADD CONSTRAINT delegation_permissions_permission_check CHECK(permission IN ( 'people.read','planning.read','planning.write','learning.read','learning.record','learning.confirm', 'report.read','report.review','assessment.read','assessment.write','assessment.review', 'certificate.read','certificate.write','certificate.review','safety.read','safety.record','absence.write' )) — 0058_certificate_delegation_permissions.sql

delegation-apprentices

Exportpfad: data/delegation_apprentices.jsonl. Ausgangsquelle: 0004_authorization.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,delegation_id,apprentice_id) — 0004_authorization.sql

• FOREIGN KEY(tenant_id,delegation_id) REFERENCES public.scoped_delegations(tenant_id,id) ON DELETE CASCADE — 0004_authorization.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0004_authorization.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT delegation_apprentices_delegation_business_fkey FOREIGN KEY(tenant_id,business_id,delegation_id) REFERENCES public.scoped_delegations(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

referral-accounts

Exportpfad: data/referral_accounts.jsonl. Ausgangsquelle: 0027_referral_program.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,id) — 0027_referral_program.sql

• FOREIGN KEY(tenant_id,user_id) REFERENCES public.tenant_memberships(tenant_id,user_id) — 0027_referral_program.sql

• CHECK((is_active AND deactivated_at IS NULL) OR (NOT is_active AND deactivated_at IS NOT NULL)) — 0027_referral_program.sql

referrals

Exportpfad: data/referrals.jsonl. Ausgangsquelle: 0027_referral_program.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• UNIQUE(tenant_id,id) — 0027_referral_program.sql

• FOREIGN KEY(tenant_id,referral_account_id) REFERENCES public.referral_accounts(tenant_id,id) — 0027_referral_program.sql

• CHECK((commission_amount_cents IS NULL)=(commission_currency IS NULL) AND (commission_amount_cents IS NULL)=(commission_type IS NULL)) — 0027_referral_program.sql

• CHECK(((status='paid' OR payout_status='paid') AND paid_at IS NOT NULL) OR (status<>'paid' AND payout_status<>'paid' AND paid_at IS NULL)) — 0027_referral_program.sql

training-interruptions

Exportpfad: data/training_interruptions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0006_planning_calendar.sql

• CHECK(starts_on<=ends_on) — 0006_planning_calendar.sql

• CHECK((credited IS NULL AND decided_by IS NULL AND decided_at IS NULL) OR (credited IS NOT NULL AND decided_by IS NOT NULL AND decided_at IS NOT NULL)) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT training_interruptions_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT training_interruptions_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD COLUMN cancelled_at timestamptz — 0056_training_interruption_lifecycle.sql

• ADD COLUMN cancellation_reason text — 0056_training_interruption_lifecycle.sql

• ADD COLUMN cancelled_by text REFERENCES public.users(id) — 0056_training_interruption_lifecycle.sql

• ADD CONSTRAINT training_interruptions_cancellation_complete CHECK( (cancelled_at IS NULL AND cancellation_reason IS NULL AND cancelled_by IS NULL) OR (cancelled_at IS NOT NULL AND length(btrim(cancellation_reason))>0 AND cancelled_by IS NOT NULL) ) — 0056_training_interruption_lifecycle.sql

interruption-decisions

Exportpfad: data/interruption_decisions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,interruption_id,revision) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,interruption_id) REFERENCES public.training_interruptions(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT interruption_decisions_business_root_fkey FOREIGN KEY(tenant_id,business_id,interruption_id) REFERENCES public.training_interruptions(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

school-schedule-rules

Exportpfad: data/school_schedule_rules.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0006_planning_calendar.sql

• CHECK(valid_from<=valid_to) — 0006_planning_calendar.sql

• CHECK((model='block') OR (weekdays_json IS NOT NULL AND jsonb_array_length(weekdays_json) BETWEEN 1 AND 7)) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT school_schedule_rules_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT school_schedule_rules_business_profession_fkey FOREIGN KEY(tenant_id,business_id,profession_code) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT school_schedule_rules_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD COLUMN IF NOT EXISTS respect_holidays boolean NOT NULL DEFAULT false — 0073_regional_school_calendars.sql

• ADD COLUMN IF NOT EXISTS school_calendar_id text — 0073_regional_school_calendars.sql

Bedingte/dynamische Stelle: 0073_regional_school_calendars.sql; konkrete Einordnung oben, keine Livebestätigung.

school-day-exceptions

Exportpfad: data/school_day_exceptions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• UNIQUE(tenant_id,apprentice_id,day) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT school_day_exceptions_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT school_day_exceptions_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

school-calendars

Exportpfad: data/school_calendars.jsonl. Ausgangsquelle: 0073_regional_school_calendars.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0073_regional_school_calendars.sql

• CHECK(starts_on<=ends_on AND ends_on-starts_on<=740) — 0073_regional_school_calendars.sql

Bedingte/dynamische Stelle: 0073_regional_school_calendars.sql; konkrete Einordnung oben, keine Livebestätigung.

school-calendar-versions

Exportpfad: data/school_calendar_versions.jsonl. Ausgangsquelle: 0073_regional_school_calendars.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id,revision) — 0073_regional_school_calendars.sql

• FOREIGN KEY(tenant_id,id) REFERENCES public.school_calendars(tenant_id,id) — 0073_regional_school_calendars.sql

availability-blocks

Exportpfad: data/availability_blocks.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0006_planning_calendar.sql

• CHECK(period_start<=period_end) — 0006_planning_calendar.sql

• CHECK((cancelled_at IS NULL AND cancellation_reason IS NULL AND cancelled_by IS NULL) OR (cancelled_at IS NOT NULL AND cancellation_reason IS NOT NULL AND length(btrim(cancellation_reason))>0 AND cancelled_by IS NOT NULL)) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT availability_blocks_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT availability_blocks_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT availability_blocks_type_check — 0037_absence_release_kind.sql

• ADD CONSTRAINT availability_blocks_type_check CHECK(type IN('vacation','sick','other','release')) — 0037_absence_release_kind.sql

capacity-windows

Exportpfad: data/capacity_windows.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,profession_reference) REFERENCES public.training_professions(tenant_id,code) — 0006_planning_calendar.sql

• CHECK(profession_code='*' OR length(btrim(profession_code))>0) — 0006_planning_calendar.sql

• CHECK(period_start<=period_end) — 0006_planning_calendar.sql

• CHECK((semantics='legacy-unresolved') OR (available_supervisors IS NOT NULL AND supervisor_capacity=COALESCE(supervision_ratio*available_supervisors,0))) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_windows_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_windows_org_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_windows_profession_business_fkey FOREIGN KEY(tenant_id,business_id,profession_reference) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_windows_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ALTER COLUMN period_end DROP NOT NULL — 0050_open_capacity_windows.sql

• ADD CONSTRAINT capacity_windows_period_check CHECK(period_end IS NULL OR period_start<=period_end) — 0050_open_capacity_windows.sql

Bedingte/dynamische Stelle: 0050_open_capacity_windows.sql; konkrete Einordnung oben, keine Livebestätigung.

capacity-window-versions

Exportpfad: data/capacity_window_versions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,window_id,revision) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,window_id) REFERENCES public.capacity_windows(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_window_versions_business_root_fkey FOREIGN KEY(tenant_id,business_id,window_id) REFERENCES public.capacity_windows(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

plan-years

Exportpfad: data/plan_years.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,start_year) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_years_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_years_business_year_unique UNIQUE(tenant_id,business_id,start_year) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT plan_years_pkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT plan_years_business_year_unique — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT plan_years_pkey PRIMARY KEY(tenant_id,business_id,start_year) — 0022_multi_business_structural_activation.sql

plan-days

Exportpfad: data/plan_days.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• UNIQUE(tenant_id,apprentice_id,day) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,start_year) REFERENCES public.plan_years(tenant_id,start_year) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_days_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_days_org_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_days_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_days_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT plan_days_business_year_fkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT plan_days_tenant_id_start_year_fkey — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT plan_days_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0022_multi_business_structural_activation.sql

• ADD COLUMN module_progress_id text — 0069_learning_module_plan_origin.sql

• ADD CONSTRAINT plan_days_module_person_origin FOREIGN KEY(tenant_id,module_progress_id,apprentice_id) REFERENCES public.learning_progress(tenant_id,id,apprentice_id) — 0069_learning_module_plan_origin.sql

plan-revisions

Exportpfad: data/plan_revisions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,start_year,revision) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,start_year) REFERENCES public.plan_years(tenant_id,start_year) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_revisions_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_revisions_business_revision_unique UNIQUE(tenant_id,business_id,start_year,revision) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT plan_revisions_business_year_fkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT plan_revisions_tenant_id_start_year_fkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT plan_revisions_pkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT plan_revisions_business_revision_unique — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT plan_revisions_pkey PRIMARY KEY(tenant_id,business_id,start_year,revision) — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT plan_revisions_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0022_multi_business_structural_activation.sql

capacity-requests

Exportpfad: data/capacity_requests.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,start_year) REFERENCES public.plan_years(tenant_id,start_year) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_requests_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_requests_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT capacity_requests_business_year_fkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT capacity_requests_tenant_id_start_year_fkey — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT capacity_requests_business_year_fkey FOREIGN KEY(tenant_id,business_id,start_year) REFERENCES public.plan_years(tenant_id,business_id,start_year) — 0022_multi_business_structural_activation.sql

capacity-decisions

Exportpfad: data/capacity_decisions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,request_id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,request_id) REFERENCES public.capacity_requests(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_decisions_business_request_fkey FOREIGN KEY(tenant_id,business_id,request_id) REFERENCES public.capacity_requests(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

capacity-consumptions

Exportpfad: data/capacity_consumptions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,request_id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,request_id) REFERENCES public.capacity_requests(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,start_year,plan_revision) REFERENCES public.plan_revisions(tenant_id,start_year,revision) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_consumptions_business_request_fkey FOREIGN KEY(tenant_id,business_id,request_id) REFERENCES public.capacity_requests(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT capacity_consumptions_business_revision_fkey FOREIGN KEY(tenant_id,business_id,start_year,plan_revision) REFERENCES public.plan_revisions(tenant_id,business_id,start_year,revision) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT capacity_consumptions_business_revision_fkey — 0022_multi_business_structural_activation.sql

• DROP CONSTRAINT capacity_consumptions_tenant_id_start_year_plan_revision_fkey — 0022_multi_business_structural_activation.sql

• ADD CONSTRAINT capacity_consumptions_business_revision_fkey FOREIGN KEY(tenant_id,business_id,start_year,plan_revision) REFERENCES public.plan_revisions(tenant_id,business_id,start_year,revision) — 0022_multi_business_structural_activation.sql

plan-templates

Exportpfad: data/plan_templates.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_templates_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_templates_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• DROP CONSTRAINT plan_templates_status_check — 0036_plan_template_release_lifecycle.sql

• ADD COLUMN published_version integer — 0036_plan_template_release_lifecycle.sql

• ADD CONSTRAINT plan_templates_status_check CHECK(status IN('draft','active','archived')) — 0036_plan_template_release_lifecycle.sql

• ADD CONSTRAINT plan_templates_published_revision_check CHECK(published_version IS NULL OR published_version BETWEEN 1 AND revision) — 0036_plan_template_release_lifecycle.sql

plan-template-versions

Exportpfad: data/plan_template_versions.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,template_id,version) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,template_id) REFERENCES public.plan_templates(tenant_id,id) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_versions_business_version_unique UNIQUE(tenant_id,business_id,template_id,version) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_versions_business_template_fkey FOREIGN KEY(tenant_id,business_id,template_id) REFERENCES public.plan_templates(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_versions_business_profession_fkey FOREIGN KEY(tenant_id,business_id,profession_code) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) — 0021_multi_business_foundation.sql

plan-template-steps

Exportpfad: data/plan_template_steps.jsonl. Ausgangsquelle: 0006_planning_calendar.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,template_id,version,ordinal) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,template_id,version) REFERENCES public.plan_template_versions(tenant_id,template_id,version) — 0006_planning_calendar.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0006_planning_calendar.sql

• CHECK(start_week+week_count<=53) — 0006_planning_calendar.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_steps_business_version_fkey FOREIGN KEY(tenant_id,business_id,template_id,version) REFERENCES public.plan_template_versions(tenant_id,business_id,template_id,version) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_steps_org_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT plan_template_steps_business_ordinal_unique UNIQUE(tenant_id,business_id,template_id,version,ordinal) — 0035_plan_template_alternative_stations.sql

plan-template-step-alternatives

Exportpfad: data/plan_template_step_alternatives.jsonl. Ausgangsquelle: 0035_plan_template_alternative_stations.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,template_id,version,ordinal,alternative_org_unit_id) — 0035_plan_template_alternative_stations.sql

• UNIQUE(tenant_id,business_id,template_id,version,ordinal,priority) — 0035_plan_template_alternative_stations.sql

• FOREIGN KEY(tenant_id,business_id,template_id,version,ordinal) REFERENCES public.plan_template_steps(tenant_id,business_id,template_id,version,ordinal) — 0035_plan_template_alternative_stations.sql

• FOREIGN KEY(tenant_id,business_id,alternative_org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0035_plan_template_alternative_stations.sql

plan-template-publications

Exportpfad: data/plan_template_publications.jsonl. Ausgangsquelle: 0036_plan_template_release_lifecycle.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,template_id,version) — 0036_plan_template_release_lifecycle.sql

• FOREIGN KEY(tenant_id,business_id,template_id) REFERENCES public.plan_templates(tenant_id,business_id,id) — 0036_plan_template_release_lifecycle.sql

• FOREIGN KEY(tenant_id,business_id,template_id,version) REFERENCES public.plan_template_versions(tenant_id,business_id,template_id,version) — 0036_plan_template_release_lifecycle.sql

curriculum-items

Exportpfad: data/curriculum_items.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• UNIQUE(tenant_id,profession_code,code) — 0007_learning.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0007_learning.sql

• CHECK(description IS NULL OR length(btrim(description))>0) — 0007_learning.sql

curriculum-versions

Exportpfad: data/curriculum_versions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,objective_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,objective_id) REFERENCES public.curriculum_items(tenant_id,id) — 0007_learning.sql

objective-progress

Exportpfad: data/objective_progress.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• UNIQUE(tenant_id,apprentice_id,curriculum_item_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,curriculum_item_id) REFERENCES public.curriculum_items(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,curriculum_item_id,curriculum_revision) REFERENCES public.curriculum_versions(tenant_id,objective_id,revision) — 0007_learning.sql

• CHECK((status='planned' AND performed_by IS NULL AND performed_at IS NULL AND confirmed_by IS NULL AND confirmed_at IS NULL) OR (status='performed' AND performed_by IS NOT NULL AND performed_at IS NOT NULL AND confirmed_by IS NULL AND confirmed_at IS NULL) OR (status='confirmed' AND performed_by IS NOT NULL AND performed_at IS NOT NULL AND confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_progress_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_progress_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

objective-progress-versions

Exportpfad: data/objective_progress_versions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,progress_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,progress_id) REFERENCES public.objective_progress(tenant_id,id) — 0007_learning.sql

• CHECK((actor_id IS NULL)=(created_at IS NULL)) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_progress_versions_business_root_fkey FOREIGN KEY(tenant_id,business_id,progress_id) REFERENCES public.objective_progress(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

department-objective-rules

Exportpfad: data/department_objective_rules.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,department_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,department_id) REFERENCES public.org_units(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_objective_rules_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_objective_rules_org_business_fkey FOREIGN KEY(tenant_id,business_id,department_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_objective_rules_business_department_unique UNIQUE(tenant_id,business_id,department_id) — 0021_multi_business_foundation.sql

department-objective-rule-versions

Exportpfad: data/department_objective_rule_versions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,department_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,department_id) REFERENCES public.department_objective_rules(tenant_id,department_id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_rule_versions_business_root_fkey FOREIGN KEY(tenant_id,business_id,department_id) REFERENCES public.department_objective_rules(tenant_id,business_id,department_id) — 0021_multi_business_foundation.sql

department-completions

Exportpfad: data/department_completions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• UNIQUE(tenant_id,id,department_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,department_id) REFERENCES public.org_units(tenant_id,id) — 0007_learning.sql

• CHECK(starts_on<=ends_on) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_completions_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_completions_org_business_fkey FOREIGN KEY(tenant_id,business_id,department_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_completions_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_completions_business_department_unique UNIQUE(tenant_id,business_id,id,department_id) — 0021_multi_business_foundation.sql

department-completion-events

Exportpfad: data/department_completion_events.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,completion_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,completion_id) REFERENCES public.department_completions(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT department_completion_events_business_root_fkey FOREIGN KEY(tenant_id,business_id,completion_id) REFERENCES public.department_completions(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

objective-department-origins

Exportpfad: data/objective_department_origins.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,progress_id,completion_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,progress_id) REFERENCES public.objective_progress(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,completion_id,department_id) REFERENCES public.department_completions(tenant_id,id,department_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,department_id,rule_revision) REFERENCES public.department_objective_rule_versions(tenant_id,department_id,revision) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_department_origins_business_progress_fkey FOREIGN KEY(tenant_id,business_id,progress_id) REFERENCES public.objective_progress(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_department_origins_business_completion_fkey FOREIGN KEY(tenant_id,business_id,completion_id,department_id) REFERENCES public.department_completions(tenant_id,business_id,id,department_id) — 0021_multi_business_foundation.sql

learning-modules

Exportpfad: data/learning_modules.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,org_unit_id) REFERENCES public.org_units(tenant_id,id) — 0007_learning.sql

• CHECK(description IS NULL OR length(btrim(description))>0) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR (kind IN('workshop','course','practice') AND status IN('active','archived'))) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_modules_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_modules_org_business_fkey FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_modules_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD COLUMN IF NOT EXISTS calendar_color text CHECK (calendar_color IS NULL OR calendar_color ~ '^#[0-9a-f]{6}$') — 0075_calendar_colors.sql

• ADD COLUMN IF NOT EXISTS calendar_color_revision integer NOT NULL DEFAULT 0 CHECK (calendar_color_revision >= 0) — 0075_calendar_colors.sql

learning-module-professions

Exportpfad: data/learning_module_professions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,module_id,profession_code) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id) REFERENCES public.learning_modules(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,profession_code) REFERENCES public.training_professions(tenant_id,code) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_professions_business_module_fkey FOREIGN KEY(tenant_id,business_id,module_id) REFERENCES public.learning_modules(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_professions_business_profession_fkey FOREIGN KEY(tenant_id,business_id,profession_code) REFERENCES public.business_training_professions(tenant_id,business_id,profession_code) — 0021_multi_business_foundation.sql

learning-module-training-years

Exportpfad: data/learning_module_training_years.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,module_id,training_year) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id) REFERENCES public.learning_modules(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_years_business_module_fkey FOREIGN KEY(tenant_id,business_id,module_id) REFERENCES public.learning_modules(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

learning-module-objectives

Exportpfad: data/learning_module_objectives.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• UNIQUE(tenant_id,module_id,curriculum_item_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id) REFERENCES public.learning_modules(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,curriculum_item_id) REFERENCES public.curriculum_items(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_objectives_business_module_fkey FOREIGN KEY(tenant_id,business_id,module_id) REFERENCES public.learning_modules(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

learning-module-versions

Exportpfad: data/learning_module_versions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,module_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id) REFERENCES public.learning_modules(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_versions_business_version_unique UNIQUE(tenant_id,business_id,module_id,revision) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_module_versions_business_root_fkey FOREIGN KEY(tenant_id,business_id,module_id) REFERENCES public.learning_modules(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

learning-progress

Exportpfad: data/learning_progress.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0007_learning.sql

• UNIQUE(tenant_id,id,module_id,module_revision) — 0007_learning.sql

• UNIQUE(tenant_id,apprentice_id,module_id,cycle) — 0007_learning.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id) REFERENCES public.learning_modules(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_id,module_revision) REFERENCES public.learning_module_versions(tenant_id,module_id,revision) — 0007_learning.sql

• CHECK(starts_on IS NULL OR due_on IS NULL OR starts_on<=due_on) — 0007_learning.sql

• CHECK(completed_on IS NULL OR starts_on IS NULL OR starts_on<=completed_on) — 0007_learning.sql

• CHECK(completed_on IS NULL OR due_on IS NULL OR completed_on<=due_on) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR status IN('planned','in_progress','completed','cancelled')) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR module_revision IS NOT NULL) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR completed_at IS NULL) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR status<>'completed' OR (completed_on IS NOT NULL AND completed_by IS NOT NULL AND proof IS NOT NULL)) — 0007_learning.sql

• CHECK(semantics='legacy-unresolved' OR completed_on IS NULL OR status IN('completed','cancelled')) — 0007_learning.sql

• CHECK((completed_on IS NULL)=(completed_by IS NULL)) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_progress_business_fkey FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_progress_business_id_unique UNIQUE(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_progress_business_module_unique UNIQUE(tenant_id,business_id,id,module_id,module_revision) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_progress_business_module_fkey FOREIGN KEY(tenant_id,business_id,module_id) REFERENCES public.learning_modules(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD COLUMN schedule_id text — 0069_learning_module_plan_origin.sql

• ADD COLUMN schedule_json jsonb — 0069_learning_module_plan_origin.sql

• ADD CONSTRAINT learning_progress_schedule_pair CHECK( (schedule_id IS NULL AND schedule_json IS NULL) OR (schedule_id IS NOT NULL AND length(schedule_id) BETWEEN 1 AND 256 AND schedule_json IS NOT NULL AND jsonb_typeof(schedule_json)='object')) — 0069_learning_module_plan_origin.sql

• ADD CONSTRAINT learning_progress_person_origin UNIQUE(tenant_id,id,apprentice_id) — 0069_learning_module_plan_origin.sql

learning-progress-versions

Exportpfad: data/learning_progress_versions.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,progress_id,revision) — 0007_learning.sql

• FOREIGN KEY(tenant_id,progress_id) REFERENCES public.learning_progress(tenant_id,id) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT learning_progress_versions_business_root_fkey FOREIGN KEY(tenant_id,business_id,progress_id) REFERENCES public.learning_progress(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

objective-module-origins

Exportpfad: data/objective_module_origins.jsonl. Ausgangsquelle: 0007_learning.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,progress_id,module_progress_id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,progress_id) REFERENCES public.objective_progress(tenant_id,id) — 0007_learning.sql

• FOREIGN KEY(tenant_id,module_progress_id,module_id,module_revision) REFERENCES public.learning_progress(tenant_id,id,module_id,module_revision) — 0007_learning.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN business_id text NOT NULL DEFAULT 'default' — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_module_origins_business_objective_fkey FOREIGN KEY(tenant_id,business_id,progress_id) REFERENCES public.objective_progress(tenant_id,business_id,id) — 0021_multi_business_foundation.sql

• ADD CONSTRAINT objective_module_origins_business_module_fkey FOREIGN KEY(tenant_id,business_id,module_progress_id,module_id,module_revision) REFERENCES public.learning_progress(tenant_id,business_id,id,module_id,module_revision) — 0021_multi_business_foundation.sql

learning-support-needs

Exportpfad: data/learning_support_needs.jsonl. Ausgangsquelle: 0032_learning_support_needs.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0032_learning_support_needs.sql

• UNIQUE(tenant_id,business_id,id) — 0032_learning_support_needs.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0032_learning_support_needs.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0032_learning_support_needs.sql

• FOREIGN KEY(tenant_id,business_id,org_unit_id) REFERENCES public.org_units(tenant_id,business_id,id) — 0032_learning_support_needs.sql

• FOREIGN KEY(tenant_id,objective_progress_id) REFERENCES public.objective_progress(tenant_id,id) — 0032_learning_support_needs.sql

• CHECK((status='resolved')=(outcome IS NOT NULL)) — 0032_learning_support_needs.sql

• CHECK(created_at<=updated_at) — 0032_learning_support_needs.sql

learning-support-need-events

Exportpfad: data/learning_support_need_events.jsonl. Ausgangsquelle: 0032_learning_support_needs.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,support_need_id,revision) — 0032_learning_support_needs.sql

• FOREIGN KEY(tenant_id,support_need_id) REFERENCES public.learning_support_needs(tenant_id,id) — 0032_learning_support_needs.sql

apprentice-private-notes

Exportpfad: data/apprentice_private_notes.jsonl. Ausgangsquelle: 0061_private_apprentice_notes.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0061_private_apprentice_notes.sql

• FOREIGN KEY(tenant_id,business_id,apprentice_id) REFERENCES public.apprentices(tenant_id,business_id,id) — 0061_private_apprentice_notes.sql

• CHECK(updated_at>=created_at) — 0061_private_apprentice_notes.sql

Besonderer Übergabeschutz: Vollarchiv enthält private Notizinhalte. Eine gewöhnliche Personen-/Ausbilderberechtigung genügt nicht automatisch für Vollarchivempfang; Rechts-/Weisungs-/Umfangsprüfung vor Übergabe.

apprentice-probation

Exportpfad: data/apprentice_probation.jsonl. Ausgangsquelle: 0070_training_conversations.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,apprentice_id) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,responsible_membership_id) REFERENCES public.tenant_memberships(tenant_id,id) — 0070_training_conversations.sql

• CHECK((start_on IS NULL AND end_on IS NULL) OR (start_on IS NOT NULL AND end_on IS NOT NULL AND end_on>=start_on)) — 0070_training_conversations.sql

• CHECK(num_nonnulls(responsible_membership_id,responsible_role)=1) — 0070_training_conversations.sql

• CHECK(updated_at>=created_at) — 0070_training_conversations.sql

Spätere ALTER-Deklarationen in Migrationsreihenfolge:

• ADD COLUMN decision_by date — 0079_probation_preparation.sql

• ADD COLUMN conversation_on date — 0079_probation_preparation.sql

• ADD COLUMN decision_lead_days integer CHECK(decision_lead_days BETWEEN 0 AND 90) — 0079_probation_preparation.sql

• ADD COLUMN reminder_days_before integer CHECK(reminder_days_before BETWEEN 0 AND 90) — 0079_probation_preparation.sql

• ADD COLUMN exception_review text NOT NULL DEFAULT '' CHECK(length(exception_review)<=2000) — 0079_probation_preparation.sql

• ADD CONSTRAINT probation_optional_dates_require_end CHECK(end_on IS NOT NULL OR (decision_by IS NULL AND conversation_on IS NULL)) — 0079_probation_preparation.sql

probation-schedule-policies

Exportpfad: data/probation_schedule_policies.jsonl. Ausgangsquelle: 0079_probation_preparation.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id) — 0079_probation_preparation.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0079_probation_preparation.sql

training-conversations

Exportpfad: data/training_conversations.jsonl. Ausgangsquelle: 0070_training_conversations.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,id) — 0070_training_conversations.sql

• UNIQUE(tenant_id,id) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0070_training_conversations.sql

• CHECK(updated_at>=created_at) — 0070_training_conversations.sql

• CHECK(held OR shared_summary='') — 0070_training_conversations.sql

training-conversation-versions

Exportpfad: data/training_conversation_versions.jsonl. Ausgangsquelle: 0070_training_conversations.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,business_id,conversation_id,revision) — 0070_training_conversations.sql

• FOREIGN KEY(tenant_id,business_id,conversation_id) REFERENCES public.training_conversations(tenant_id,business_id,id) — 0070_training_conversations.sql

apprentice-field-definitions

Exportpfad: data/apprentice_field_definitions.jsonl. Ausgangsquelle: 0072_additional_apprentice_fields.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,id) — 0072_additional_apprentice_fields.sql

• UNIQUE(tenant_id,label) — 0072_additional_apprentice_fields.sql

• CHECK(updated_at>=created_at) — 0072_additional_apprentice_fields.sql

• CHECK(field_type='validity' OR reminder_days IS NULL) — 0072_additional_apprentice_fields.sql

• CHECK((field_type IN('select','multiselect') AND jsonb_array_length(options_json)>0) OR (field_type NOT IN('select','multiselect') AND options_json='[]'::jsonb)) — 0072_additional_apprentice_fields.sql

apprentice-field-values

Exportpfad: data/apprentice_field_values.jsonl. Ausgangsquelle: 0072_additional_apprentice_fields.sql.

Ausgangs-Schlüssel/Checks (spätere Änderungen beachten):

• PRIMARY KEY(tenant_id,apprentice_id,field_id) — 0072_additional_apprentice_fields.sql

• FOREIGN KEY(tenant_id,apprentice_id) REFERENCES public.apprentices(tenant_id,id) — 0072_additional_apprentice_fields.sql

• FOREIGN KEY(tenant_id,business_id) REFERENCES public.businesses(tenant_id,id) — 0072_additional_apprentice_fields.sql