# Database Schema Reference

Mailforge nutzt **Drizzle ORM** mit zwei unterstützten Backends:
- **SQLite** (`better-sqlite3`) — Default für Dev/CI
- **MySQL** (`mysql2`) — empfohlen für Production neben Laravel

Beide haben dasselbe Schema; nur Spalten-Datentypen unterscheiden sich (`INTEGER` vs. `INT`, JSON-Storage etc.). Wähle das Backend in `.env.local` via `DB_DRIVER=sqlite|mysql`.

## ERD (vereinfacht)

```
┌──────────────┐                                                 ┌──────────────┐
│  workspaces  │ ◄────────────┐                          ┌──────►│  templates   │
└──────────────┘              │                          │       └──────────────┘
       ▲                      │                          │              ▲
       │                      │                          │              │
       │                      ▼                          │              │
┌──────────────┐       ┌──────────────────┐       ┌──────────────┐       │
│  campaigns   │──────►│ campaign_variants│       │   sections    │      │
│              │       └──────────────────┘       │  _templates   │      │
└──────────────┘              ▲                   └──────────────┘       │
       │                      │                          ▲               │
       │                      │                          │               │
       ├─────────────────┐    │                          │               │
       │                 │    │                          │               │
       ▼                 ▼    │                          │               │
┌────────────┐   ┌──────────────────┐                    │               │
│ recipients │──►│   email_queue    │                    │               │
└────────────┘   └──────────────────┘                    │               │
       │                 │                               │               │
       │                 │                               │               │
       ▼                 ▼                               │               │
┌─────────────────────────────────┐               ┌──────────────┐       │
│       tracking_events           │               │   trigger    │       │
│ (open/click/bounce/conversion)  │               │  _templates  │       │
└─────────────────────────────────┘               └──────────────┘       │
                                                         │               │
┌──────────────┐    ┌──────────────┐    ┌──────────────┐ │  ┌──────────┐ │
│ app_settings │    │   users      │    │   assets     │ │  │ trigger  │ │
│  (key-value) │    │              │    │ (images etc.)│ │  │  _logs   │ │
└──────────────┘    └──────────────┘    └──────────────┘ │  └──────────┘ │
                                                         └────────────────┘
```

Vollständige Schema-Definition: `src/lib/db/schema.ts`

## Tabellen-Referenz

### Core

#### `workspaces`
Mandanten-Discriminator. Jede Workspace hat ihr eigenes Branding, eigene Recipients, eigene Campaigns.

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | UUID |
| `slug` | text unique | URL-safe identifier (z.B. "main", "newsletter-de") |
| `name` | text | Display-Name |
| `primary_color` | text | Brand-Hauptfarbe (Hex) |
| `secondary_color` | text? | Zweitfarbe (Hex) |
| `logo_url` | text? | Absolute URL zum Logo |
| `sender_domain` | text | DKIM-relevante Sender-Domain |
| `sender_name` | text | Display-Name in From-Header |
| `sender_email` | text | Default-From-Adresse |
| `reply_to_email` | text | Reply-To-Adresse |
| `footer_text` | text | Plain-Text Footer (Impressum-Kurzform) |
| `brand_voice` | text | Tone-Description für AI-Pipeline |
| `wording_style` | text? | Wording-Hinweise (z.B. "Du-Form") |
| `design_style` | text? | Visual-Style-Hinweise |
| `imprint_url` | text | Vollständiger Impressum-Link |
| `privacy_url` | text | Privacy-Policy-Link |
| `brand_kit_json` | JSON | Erweiterte Branding-Config (header/footer-Blöcke etc.) |
| `attribution_model` | enum | last-click / first-click / linear / time-decay |
| `attribution_lookback_days` | int | Conversion-Lookback-Window |

#### `users`
Bei JWT-Auth (Phase E) wird `users.id` mit der external `userId` aus dem JWT synchronisiert. Aktuell rein lokal.

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | UUID |
| `email` | text unique | |
| `name` | text? | |
| `role` | enum | admin / editor |

#### `app_settings`
Key-Value-JSON-Store. Wichtige Keys:
- `system_prompt_generate` / `_suggest` / `_image` — System-Prompts für AI-Pipeline
- `openrouter_api_key` — verschlüsselt (envelope `{_enc: "base64..."}`)
- `fal_api_key` — verschlüsselt
- `send_provider_config` — verschlüsselte JSON-Config für aktiven SendProvider
- `crm_adapter_config` — verschlüsselte JSON-Config für aktiven CrmAdapter
- `conversion_polling` — `{ intervalMinutes: 15, recipientIdField: "..." }`

Verschlüsselung: AES-256-GCM mit `APP_ENCRYPTION_KEY` (64-hex env-var). Plain-Werte werden roh gespeichert, secrets wrapped als `{_enc: "..."}`.

### Content

#### `campaigns`
Eine Marketing-Mail (oder Trigger-Container). Hauptentität.

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | |
| `workspace_id` | text FK → workspaces.id | |
| `template_id` | text? FK → templates.id | Optional: aus Template gestartet |
| `created_by_id` | text FK → users.id | |
| `name` | text | |
| `briefing_json` | JSON | User-Briefing (Briefing-Tab-State) |
| `tiptap_json` | JSON | TipTap-Editor-State |
| `strategy_json` | JSON? | Vom LLM generierte Strategy |
| `kind` | enum | marketing / trigger_container |
| `status` | enum | draft / building / review / scheduled / sending / sent / failed / cancelled |
| `ab_enabled` | bool | A/B-Test aktiv? |
| `ab_winner_hours` | int | Nach wie vielen Stunden Champion-Promotion |
| `utm_defaults_json` | JSON? | UTM-Defaults zum Append an alle Links |
| `spam_report_json` | JSON? | Letzter Spam-Check |

#### `campaign_variants`
A/B-Test-Varianten (Pattern C: Full-Template).

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | |
| `campaign_id` | text FK → campaigns.id (CASCADE) | |
| `label` | text | "A", "B", "C" |
| `subject` | text | |
| `preheader` | text | |
| `mjml` | text? | Variant-MJML-Source |
| `html` | text? | Gerendertes HTML |
| `html_text` | text? | Plain-Text-Fallback |
| `is_winner` | bool | |
| `sent_count` / `open_count` / `click_count` | int | |

#### `templates`, `section_templates`
Wiederverwendbare Vorlagen pro Workspace.

#### `recipients`
Empfänger einer Campaign (oder Trigger-Single-Recipient).

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | |
| `campaign_id` | text FK → campaigns.id | |
| `variant_id` | text? FK → campaign_variants.id | A/B-Zuordnung |
| `crm_id` | text? | External CRM ID (für Conversion-Polling-Match) |
| `email` | text | |
| `first_name` / `last_name` | text? | |
| `custom_data_json` | JSON? | Beliebige Felder vom CRM für Template-Tag-Substitution |

### Delivery

#### `email_queue`
Worker-Job-Queue.

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | |
| `campaign_id` / `recipient_id` / `variant_id` | FKs | |
| `status` | enum | pending / sending / sent / failed / bounced / skipped / champion_held |
| `scheduled_at` | timestamp | |
| `attempts` | int | |
| `last_error` | text? | |
| `message_id` | text? | Provider-zurückgegebene ID (für Tracking-Korrelation) |

#### `tracking_events`
Open/Click/Conversion/Bounce-Events.

| Spalte | Typ | Beschreibung |
|---|---|---|
| `id` | text PK | |
| `campaign_id` / `recipient_id` / `variant_id` / `message_id` | FKs | |
| `event_type` | enum | delivered / open / click / bounce / unsubscribe / complaint / conversion |
| `link_url` | text? | Bei click-Events |
| `is_mpp` | bool? | Apple Mail Privacy Protection Flag |
| `source` | enum? | pixel / api_poll (für conversion-Events) |
| `occurred_at` | timestamp | |

### Optional Modules

- `assets` — Hero-Images + Uploads (gehört zu `heroImage`-Modul)
- `behavior_events` — Onsite-Behavior für Multi-Touch-Attribution (gehört zu `stats`)
- `unsubscribes` — Globale Abmeldungen
- `trigger_templates` + `trigger_logs` — Transactional-API (gehört zu `triggerTemplates`)
- `sessions` + `magic_links` — Magic-Link-Auth (legacy, wird in Phase E durch JWT ersetzt)

## DDL-Files

Auto-generiert via `npm run db:export-ddl`. Stand jetzt manuell pflegen — wird in Phase D automatisiert.

- `docs/ddl/sqlite.sql` — CREATE TABLE für SQLite
- `docs/ddl/mysql.sql` — CREATE TABLE für MySQL

Wenn du eine **bestehende MySQL-Datenbank** hast und nur Mailforge-Tabellen einfügen willst, kannst du `docs/ddl/mysql.sql` direkt in deine DB importieren statt Drizzle-Migrations zu nutzen. Die Tabellen sind mit Prefix `mf_` benannt (via `DB_TABLE_PREFIX`).

## Field-Mapping zwischen SQLite und MySQL

| Drizzle-Type | SQLite | MySQL |
|---|---|---|
| `text()` | TEXT | VARCHAR(255) oder TEXT |
| `integer({ mode: 'timestamp_ms' })` | INTEGER (ms epoch) | BIGINT (ms epoch) |
| `integer({ mode: 'boolean' })` | INTEGER 0/1 | TINYINT 0/1 |
| `text({ mode: 'json' })` | TEXT (JSON-string) | JSON column |
| `text({ enum: [...] })` | TEXT (kein Constraint) | ENUM oder VARCHAR mit CHECK |

Drizzle abstrahiert das — du schreibst denselben Schema-Code, das richtige SQL kommt raus.

## Tabellen-Präfix

In Shared-DB-Setups (Laravel + Mailforge auf derselben MySQL-Instanz) verwende:

```bash
DB_TABLE_PREFIX=mf_
```

Daraus werden:
- `workspaces` → `mf_workspaces`
- `campaigns` → `mf_campaigns`
- usw.

Laravel-Tabellen bleiben unberührt. Mailforge referenziert NIE Laravel-Tabellen direkt — Cross-System-Daten (User, Contacts) gehen IMMER über JWT-`ctx` (User-ID) und den CrmAdapter (Contacts).

## Migrations

Migrations werden pro Driver getrennt generiert:
- `drizzle/sqlite/` — generiert via `npm run db:generate:sqlite`
- `drizzle/mysql/` — generiert via `npm run db:generate:mysql`
- `npm run db:generate` (ohne Suffix) liest `DB_DRIVER` aus env und wählt den passenden

Drizzle-Config-Files:
- `drizzle.sqlite.config.ts` — verweist auf `src/lib/db/schema.ts` (vollständig)
- `drizzle.mysql.config.ts` — verweist auf `src/core/db/schema/mysql/` (**STUB** in Phase D-Lite, siehe `src/core/db/schema/mysql/README.md` für Port-Checklist)

### Driver-Selector zur Laufzeit

`src/core/db/client.ts` liest `DB_DRIVER` und baut den passenden Client. Existierender Code in `src/lib/db/*-repo.ts` importiert weiterhin `db` aus `src/lib/db/client.ts` (Bridge-File) — die Migration auf den neuen Pfad geschieht inkrementell in Phase G.

```typescript
import { getDriverInfo, getDb } from '@/core/db/client';

console.log(getDriverInfo());
// { driver: "sqlite", prefix: "", details: "file: ./data/mailforge.db" }
```

### Phase D-Lite vs. Phase D-Full

**Phase D-Lite (aktuell, ausgeliefert):**
- Driver-Selector funktioniert
- SQLite läuft produktiv
- MySQL-Setup wirft mit klarem Fehler "Schema not yet implemented"
- Drizzle-Config-Files für beide Drivers
- README mit Port-Checklist im `mysql/`-Verzeichnis

**Phase D-Full (Follow-up):**
- MySQL-Schema in `src/core/db/schema/mysql/*.ts` portieren (siehe Port-Checklist)
- DDL-Export-Script (`npm run db:export-ddl`) generiert `docs/ddl/{sqlite,mysql}.sql`
- CI testet beide Drivers

Wer MySQL **jetzt** braucht, hat zwei Optionen:
1. Schema-Port manuell durchführen (~1-2 Tage Arbeit, siehe Port-Checklist)
2. SQLite-Schema händisch in MySQL-DDL übersetzen und direkt importieren (kein Drizzle-Migration-Track-Record, aber funktionsfähig)
