# SupplierPulse — Datenbankschema
## Stand: 2026-04-29
## Supabase Projekt: `${SUPABASE_PROJECT_REF}` (operator-managed; placeholder used here for handover)

> **Quelle:** Dieses Dokument ist aus den SQL-Dateien des Repositories abgeleitet (siehe `MIGRATIONS.md`).
> Es wurde **nicht** gegen eine produktive Datenbank introspectiert. RLS-Spalten mit `?` müssen
> vom Vendor gegen den Live-Stand verifiziert werden — siehe `docs/foundation/rls-audit-2026-04-21.md`
> für den letzten verifizierten RLS-Stand.
>
> **Phase-1-RLS-Cleanup (PR #35, Commit `95b02b7`)** ist auf der Supabase-Instanz angewendet:
> - `supabase/migrations/supabase-migration-rls-phase1-cleanup.sql`
> - `supabase/migrations/supabase-migration-rls-phase1a-cleanup.sql`
> - `supabase/migrations/supabase-migration-rls-phase1b-user-profiles.sql` (`up_own`-Policy umgeschrieben — Selbst-Join-Rekursion behoben)

---

## 1. Tabellen-Übersicht

> Zeilenzahlen sind Schätzwerte (aus `pg_stat_user_tables`). Für aktuelle Werte: Phase-1-Audit ausführen.

| # | Tabelle | Beschreibung | RLS | Modul |
|---|---------|-------------|-----|-------|
| 1 | `projects` | Besuche / Lieferantenprojekte | ✅ | Core |
| 2 | `process_steps` | Stationen / Prozessschritte je Projekt | ✅ | Core |
| 3 | `cycle_measurements` | Zykluszeitmessungen je Station | ✅ | Core |
| 4 | `shift_outputs` | Stündliche Schichtleistung | ✅ | Core |
| 5 | `workshop_actions` | Verbesserungsmaßnahmen | ✅ | Core |
| 6 | `qaf_uploads` | QAF-Dokument-Uploads | ✅ | Core |
| 7 | `project_type_assignments` | M2M: Projekt ↔ Projekttyp | ✅ | Core |
| 8 | `project_suppliers` | M2M: Projekt ↔ Lieferant | ✅ | Core |
| 9 | `project_types` | Lookup: Projekttypen | ? | Core |
| 10 | `project_statuses` | Lookup: Projektstatus-Codes | ? | Core |
| 11 | `project_responsibilities` | Lookup: Zuständigkeiten | ? | Core |
| 12 | `project_consultants` | M2M: Projekt ↔ Berater (Zuordnung) | ? | Core |
| 13 | `consultants` | Berater-Profile | ? | Planning |
| 14 | `appointment_types` | Kalender-Termintypen (EI, EE, SC …) | ? | Planning |
| 15 | `planning_projects` | Kalender-Projekte (für Einsatzplanung) | ? | Planning |
| 16 | `assignments` | Kalender-Einträge je Berater | ? | Planning |
| 17 | `assessment_main_categories` | Assessment-Hauptkategorien | ? | Assessment |
| 18 | `assessment_sub_categories` | Assessment-Unterkategorien | ? | Assessment |
| 19 | `assessment_questions` | Assessment-Fragen | ? | Assessment |
| 20 | `assessments` | Assessment-Sitzungen | ? | Assessment |
| 21 | `assessment_responses` | Antworten je Frage und Sitzung | ? | Assessment |
| 22 | `documents` | Repository-Dokumente | ? | Repository |
| 23 | `document_metadata` | KI-angereicherte Dokument-Metadaten | ? | Repository |
| 24 | `document_suppliers` | M2M: Dokument ↔ Lieferant | ? | Repository |
| 25 | `document_departments` | M2M: Dokument ↔ Abteilung | ? | Repository |
| 26 | `master_data_import_jobs` | Import-Aufträge für Stammdaten | ? | Repository |
| 27 | `supplier_master_data` | Lieferanten-Stammdaten | ? | Stammdaten |
| 28 | `department_master_data` | Abteilungs-Stammdaten | ? | Stammdaten |
| 29 | `master_data_types` | Stammdaten-Typ-Definitionen | ? | Stammdaten |
| 30 | `master_data_values` | Stammdaten-Werte (u.a. Platzhaltertexte) | ? | Stammdaten |
| 31 | `master_data_audit_log` | Audit-Log für Stammdaten-Änderungen | ? | Stammdaten |
| 32 | `master_data_suggestions` | KI-vorgeschlagene Stammdaten | ? | Stammdaten |
| 33 | `roles` | Systemrollen | ? | Auth |
| 34 | `permissions` | Systemberechtigungen | ? | Auth |
| 35 | `role_permissions` | M2M: Rolle ↔ Berechtigung | ? | Auth |
| 36 | `user_profiles` | Erweiterte Nutzerprofile | ? | Auth |
| 37 | `role_view_configs` | Rollenbasierte UI-Konfiguration | ? | Auth |
| 38 | `user_audit_log` | Audit-Log für Nutzerverwaltung | ? | Auth |
| 39 | `email_queue` | E-Mail-Versandwarteschlange | ? | System |
| 40 | `holiday_calendars` | Feiertagskalender (Land/Region) | ? | System |
| 41 | `holiday_entries` | Einzelne Feiertagseinträge | ? | System |
| 42 | `user_holiday_preferences` | Nutzer-Feiertagskalender-Präferenzen | ? | System |
| 43 | `lsc_shifts` | LSC Schichtkonfigurationen | ? | LSC |
| 44 | `lsc_shift_hours` | LSC stündliche Ist-/Plan-Leistung | ✅ | LSC |
| 45 | `lsc_measures` | LSC Verbesserungsmaßnahmen | ? | LSC |
| 46 | `oee_records` | OEE-Datensätze je Woche/Linie | ? | OEE |
| 47 | `oee_loss_categories` | OEE-Verlustaufteilung | ? | OEE |
| 48 | `value_stream_maps` | Wertstrom-Diagramme (JSON-basiert) | ? | VSM |
| 49 | `assignment_audit_log` | Audit-Log für Planungsänderungen | ? | Planning |

**Legende:** ✅ = RLS bestätigt aktiv | ? = RLS-Status im Audit prüfen

---

## 2. Spalten-Details pro Tabelle

### projects
| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|-------------|
| id | uuid | NO | gen_random_uuid() | PK |
| user_id | uuid | NO | — | FK → auth.users |
| supplier_name | text | NO | — | Lieferantenname (legacy, ersetzt durch supplier_id) |
| plant_location | text | YES | — | Werksstandort |
| product_name | text | YES | — | Produktbezeichnung |
| visit_date | date | YES | CURRENT_DATE | Besuchsdatum |
| customer_takt_time_sec | numeric(8,2) | YES | — | Kundentakt in Sekunden |
| planned_oee | numeric(5,2) | YES | 85.0 | Geplante OEE % |
| target_cycle_time_sec | numeric(8,2) | YES | — | Ziel-Zykluszeit |
| notes | text | YES | — | Notizen |
| estimated_savings_eur | numeric(12,2) | YES | — | Geschätzte Einsparung EUR |
| actual_savings_eur | numeric(12,2) | YES | — | Tatsächliche Einsparung EUR |
| parent_project_id | uuid | YES | — | FK → projects (Sub-Projekte) |
| sub_project_suffix | text | YES | — | Kürzel für Sub-Projekt |
| created_at | timestamptz | YES | NOW() | — |
| updated_at | timestamptz | YES | NOW() | — |

### process_steps
| Spalte | Typ | Nullable | Default |
|--------|-----|----------|---------|
| id | uuid | NO | gen_random_uuid() |
| project_id | uuid | NO | — | FK → projects |
| step_number | integer | NO | — |
| station_name | text | NO | — |
| description | text | YES | — |
| planned_cycle_time_sec | numeric(8,2) | YES | — |
| is_manual | boolean | YES | false |
| operator_count | integer | YES | 1 |
| area_name | text | YES | — |
| sort_order | integer | YES | 0 |
| created_at | timestamptz | YES | NOW() |

### cycle_measurements
| Spalte | Typ | Nullable | Default |
|--------|-----|----------|---------|
| id | uuid | NO | gen_random_uuid() |
| process_step_id | uuid | NO | — | FK → process_steps |
| cycle_number | integer | NO | — |
| cycle_time_sec | numeric(8,2) | NO | — |
| is_outlier | boolean | YES | false |
| notes | text | YES | — |
| time_type | text | YES | — | CHECK: prozesszeit/maschinenzeit/ruestzeit/wartezeit/manuelle_zeit/wertschoepfend/nicht_wertschoepfend |
| machine_time_sec | numeric | YES | — |
| manual_time_sec | numeric | YES | — |
| measured_at | timestamptz | YES | NOW() |

### assignments
| Spalte | Typ | Nullable | Default |
|--------|-----|----------|---------|
| id | uuid | NO | gen_random_uuid() |
| date | date | NO | — |
| start_time | time | YES | — |
| end_time | time | YES | — |
| consultant_id | uuid | NO | — | FK → consultants |
| appointment_type_id | uuid | NO | — | FK → appointment_types |
| project_id | uuid | YES | — | FK → planning_projects |
| origin_project_id | uuid | YES | — | FK → projects (für Projektanlage-Termine) |
| supplier_name | text | YES | — |
| location_label | text | YES | — |
| description | text | YES | — |
| work_mode | text | NO | 'onsite' | CHECK: homeoffice/onsite/remote |
| status | text | NO | 'fixed' | CHECK: fixed/tentative/critical/cancelled |
| is_all_day | boolean | NO | true |
| requires_travel | boolean | YES | false |
| source | text | NO | 'manual' |
| is_demo | boolean | YES | — |
| created_by | uuid | YES | — | FK → auth.users |
| updated_by | uuid | YES | — | FK → auth.users |
| created_at | timestamptz | NO | NOW() |
| updated_at | timestamptz | NO | NOW() |

### user_profiles
| Spalte | Typ | Nullable | Default |
|--------|-----|----------|---------|
| id | uuid | NO | gen_random_uuid() |
| auth_user_id | uuid | NO | — | FK → auth.users (UNIQUE) |
| first_name | text | NO | — |
| last_name | text | NO | — |
| display_name | text | NO | — |
| email | text | NO | — |
| email_verified | boolean | YES | false |
| department_id | uuid | YES | — | FK → department_master_data |
| role_id | uuid | NO | — | FK → roles |
| account_status | text | NO | 'invited' | CHECK: invited/pending_registration/active/inactive/locked/deactivated |
| must_change_password | boolean | YES | true |
| initial_password_issued_at | timestamptz | YES | — |
| avatar_url | text | YES | — |
| created_by | uuid | YES | — | FK → auth.users |
| last_login_at | timestamptz | YES | — |
| deactivated_at | timestamptz | YES | — |
| deleted_at | timestamptz | YES | — |
| created_at | timestamptz | YES | NOW() |
| updated_at | timestamptz | YES | NOW() |

---

## 3. Beziehungen (ERD als Text)

```
auth.users
  ├── projects.user_id (CASCADE)
  ├── consultants.auth_user_id (SET NULL)
  ├── user_profiles.auth_user_id (CASCADE, UNIQUE)
  ├── assessments.created_by (SET NULL)
  ├── documents.uploaded_by (SET NULL)
  ├── oee_records.created_by (SET NULL)
  ├── value_stream_maps.created_by
  ├── assignments.created_by / updated_by (SET NULL)
  └── user_audit_log.actor_id (SET NULL)

projects
  ├── process_steps.project_id (CASCADE)
  │     └── cycle_measurements.process_step_id (CASCADE)
  ├── shift_outputs.project_id (CASCADE)
  ├── workshop_actions.project_id (CASCADE)
  ├── qaf_uploads.project_id (CASCADE)
  ├── project_type_assignments.project_id (CASCADE)
  ├── project_suppliers.project_id (CASCADE)
  ├── assessments.project_id (SET NULL)
  ├── documents.project_id (SET NULL)
  ├── lsc_shifts.project_id (CASCADE)
  ├── lsc_shift_hours.project_id (CASCADE)
  ├── lsc_measures.project_id (CASCADE)
  ├── oee_records.project_id (SET NULL)
  ├── value_stream_maps.project_id (SET NULL)
  ├── assignments.origin_project_id (CASCADE)  [MO-27]
  └── projects.parent_project_id (SET NULL, self-ref)

supplier_master_data
  ├── project_suppliers.supplier_id (CASCADE)
  ├── document_suppliers.supplier_id (CASCADE)
  ├── oee_records.supplier_id (SET NULL)
  └── planning_projects.supplier_id

consultants
  └── assignments.consultant_id (CASCADE)

appointment_types
  └── assignments.appointment_type_id (RESTRICT)

planning_projects
  └── assignments.project_id (SET NULL)

assessment_main_categories
  ├── assessment_sub_categories.main_category_id (CASCADE)
  └── assessment_questions.main_category_id (CASCADE)

assessment_sub_categories
  └── assessment_questions.sub_category_id (CASCADE)

assessments
  └── assessment_responses.assessment_id (CASCADE)

assessment_questions
  └── assessment_responses.question_id (CASCADE)

documents
  ├── document_metadata.document_id (CASCADE)
  ├── document_suppliers.document_id (CASCADE)
  ├── document_departments.document_id (CASCADE)
  └── master_data_import_jobs.file_document_id (SET NULL)

department_master_data
  ├── document_departments.department_id (CASCADE)
  └── user_profiles.department_id (SET NULL)

roles
  ├── role_permissions.role_id (CASCADE)
  └── user_profiles.role_id

permissions
  └── role_permissions.permission_id (CASCADE)

user_profiles
  └── user_audit_log.target_user_id (SET NULL)

oee_records
  └── oee_loss_categories.oee_record_id (CASCADE)

master_data_types
  └── master_data_values.type_id
```

---

## 4. Indexes

### Bestätigte Indexes (aus supabase/migrations/supabase-migration-subprojects.sql)
| Tabelle | Indexname | Spalten | Typ |
|---------|-----------|---------|-----|
| projects | idx_projects_parent | parent_project_id | btree |
| project_type_assignments | idx_pta_project | project_id | btree |
| project_type_assignments | idx_pta_type | project_type_code | btree |
| project_suppliers | idx_ps_project | project_id | btree |
| project_suppliers | idx_ps_supplier | supplier_id | btree |
| assignments | idx_assignments_origin_project_id | origin_project_id | btree |

### Implizite Indexes (durch UNIQUE/PK Constraints)
| Tabelle | Spalte(n) | Grund |
|---------|-----------|-------|
| supplier_master_data | supplier_number | UNIQUE |
| user_profiles | auth_user_id | UNIQUE |
| project_type_assignments | (project_id, project_type_code) | UNIQUE |
| project_suppliers | (project_id, supplier_id) | UNIQUE |
| lsc_shift_hours | (project_id, shift_date, shift_label, hour_start) | UNIQUE |
| lsc_shifts | (project_id, area_name, shift_number) | UNIQUE |
| roles | code | UNIQUE |
| permissions | code | UNIQUE |
| role_permissions | (role_id, permission_id) | UNIQUE |
| appointment_types | code | UNIQUE |
| planning_projects | code | UNIQUE |
| oee_records | (line_name, calendar_week, year) | UNIQUE |

### Empfohlene Indexes (aus supabase/migrations/supabase-migration-cleanup.sql)
→ Siehe `supabase/migrations/supabase-migration-cleanup.sql` für vollständige CREATE INDEX Statements.

---

## 5. RLS Policies

### Bestätigte Policies
| Tabelle | Policy | Operation | Bedingung |
|---------|--------|-----------|-----------|
| projects | "Users see own projects" | ALL | `auth.uid() = user_id` |
| process_steps | "Users see own steps" | ALL | `project_id IN (SELECT id FROM projects WHERE user_id = auth.uid())` |
| cycle_measurements | "Users see own measurements" | ALL | via process_steps → projects join |
| shift_outputs | "Users see own shift outputs" | ALL | `project_id IN (SELECT id FROM projects WHERE user_id = auth.uid())` |
| workshop_actions | "Users see own actions" | ALL | `project_id IN (SELECT id FROM projects WHERE user_id = auth.uid())` |
| qaf_uploads | "Users see own QAFs" | ALL | `project_id IN (SELECT id FROM projects WHERE user_id = auth.uid())` |
| project_type_assignments | pta_select / pta_insert / pta_delete | SELECT/INSERT/DELETE | `TO authenticated USING (true)` ⚠️ zu breit |
| project_suppliers | ps_select / ps_insert / ps_delete | SELECT/INSERT/DELETE | `TO authenticated USING (true)` ⚠️ zu breit |
| lsc_shift_hours | "Authenticated users manage lsc_shift_hours" | ALL | `auth.uid() IS NOT NULL` ⚠️ zu breit |

### ⚠️ RLS-Probleme
1. **`project_type_assignments`** / **`project_suppliers`**: Jeder authentifizierte Nutzer kann fremde Verknüpfungen lesen/schreiben/löschen → sollte auf Projektbesitzer beschränkt werden.
2. **`lsc_shift_hours`**: Jeder Auth-Nutzer sieht alle Stunden-Daten aller Projekte → project_id-Prüfung fehlt.
3. Alle Tabellen ohne dokumentierte Policy (? in Tabellen-Übersicht) → müssen per Audit verifiziert werden.

---

## 6. Sequences

| Name | Verwendung |
|------|-----------|
| `user_audit_log_id_seq` | PK-Sequence für `user_audit_log.id` (GENERATED ALWAYS AS IDENTITY) |
| `master_data_audit_log_id_seq` | PK-Sequence für `master_data_audit_log.id` |
| `assignment_audit_log_id_seq` | PK-Sequence für `assignment_audit_log.id` |

Alle anderen Tabellen verwenden `gen_random_uuid()` — keine Sequences nötig.

---

## 7. Trigger

| Trigger | Tabelle | Timing | Event | Funktion |
|---------|---------|--------|-------|----------|
| trg_lsc_shift_hours_updated_at | lsc_shift_hours | BEFORE | UPDATE | `update_lsc_shift_hours_updated_at()` |

Weitere `updated_at`-Trigger wahrscheinlich auf: `projects`, `assignments`, `assessments`, `oee_records`, `lsc_shifts`, `lsc_measures`, `documents`, `user_profiles`, `supplier_master_data`, `department_master_data` — per Audit verifizieren.

---

## 8. Storage Buckets

| Bucket | Typ | Verwendung |
|--------|-----|-----------|
| `documents` | private | Repository-Datei-Uploads (PDF, XLSX, PPTX, Bilder) |

---

## 9. Bekannte Einschränkungen & TODOs

| # | Problem | Schwere | Status |
|---|---------|---------|--------|
| 1 | `projects.supplier_name` ist Legacy-Textfeld — Stammdaten-Referenz über `project_suppliers` | Medium | Migriert (beide existieren) |
| 2 | `project_type_assignments` / `project_suppliers` RLS zu breit (USING true) | High | Offen — Fix in cleanup-migration |
| 3 | `lsc_shift_hours` RLS zu breit (kein project_id-Check) | High | Offen — Fix in cleanup-migration |
| 4 | Fehlende Indexes auf FK-Spalten (details in cleanup-migration) | Medium | Fix in cleanup-migration |
| 5 | `assessments.project_id` könnte zu `projects` gehören oder zu `planning_projects` — Semantik unklar | Low | Dokumentiert |
| 6 | `planning_projects.supplier_id` FK ohne dokumentierten ON DELETE | Low | Prüfen |
| 7 | `assignment_audit_log` Schema nicht vollständig in .sql-Dateien — nur aus TypeScript-Typ abgeleitet | Medium | SQL-File erstellen |

---

## 10. Technischer Schuldenstand

Letzter SQL-basierter Refresh: **2026-04-29** (handover/foundation-readiness branch).
Letzter live-introspektierter Audit: **2026-04-05** (operator) + Phase-1-RLS Cleanup-Pilots **2026-04-21 … 2026-04-28**.

Empfohlene nächste Schritte (Vendor):
1. Diese Datei gegen die Azure-PostgreSQL-Replik introspektieren (Spalten-für-Spalten-Vergleich).
2. RLS-Spalten mit `?` aus dem letzten Live-Audit auffüllen — Quelle: `docs/foundation/rls-audit-2026-04-21.md`.
3. Replacement-Policies für `project_type_assignments`, `project_suppliers`, `lsc_shift_hours` (`supabase/rls/supabase-rls-test-*.sql`) prüfen und ggf. anwenden — siehe `MIGRATIONS.md` Abschnitt 5.
4. Translation der Policies nach Azure-PG dokumentieren — siehe `docs/foundation/rls-azure-translation.md` und `scripts/rls-azure-translate.mjs`.