---
name: mailforge-data-layer
description: Drizzle-Schema-Workflow, Cumas-CRM-Integration und Tracking-Event-Dedup für MailForge. Nutze diesen Skill **bevor** du `src/lib/db/schema.ts` editierst, eine Drizzle-Migration generierst (`npm run db:generate`), `src/lib/db/defaults.ts` änderst, einen neuen `appSettings`-Key einführst, `src/lib/crm/cumas-client.ts` oder `src/lib/crm/kompass-link.ts` anfasst, an `src/lib/conversions/cumas-poller.ts` editierst, oder eine Stats-/Conversion-Aggregat-Query schreibst. Triggert auch bei "neue Spalte hinzufügen", "Migration vergessen", "appSettings-Default fehlt", "Cumas gibt keine Recipients", "JWT abgelaufen", "doppelte Conversions in Stats", "Pixel und API-Poll zählen beide". Nicht für Worker-Send-Pipeline (→ mailforge-send-pipeline).
---

# MailForge Data-Layer

Wie man **sicher** an Schema, Defaults, CRM-Integration und Tracking-Events arbeitet — ohne Boot-Loops, fehlende Defaults oder doppelte Stats-Zahlen zu produzieren.

## Schema-Change-Workflow

Die fixe Reihenfolge — nicht überspringen. Jeder Schritt deckt einen anderen Failure-Mode ab.

```
1. src/lib/db/schema.ts editieren
        ↓
2. npm run db:generate          (Drizzle schreibt drizzle/XXXX_<name>.sql)
        ↓
3. Migration-File reviewen      (better-sqlite3 kann KEIN DROP COLUMN / ALTER TYPE!)
        ↓
4. (optional) src/lib/db/defaults.ts ergänzen
        ↓
5. (optional) src/lib/db/repositories.ts oder campaign-repo.ts erweitern
        ↓
6. npm run db:migrate           (lokal anwenden)
        ↓
7. (optional) self-heal-Logik prüfen / erweitern
        ↓
8. npx tsx scripts/smoke-test.ts  (verifizieren)
        ↓
9. (deploy: entrypoint.sh ruft db:migrate beim Container-Start)
```

### Wann was tun

| Du änderst… | …dann auch |
|---|---|
| Eine Brand-Spalte | `DEFAULT_BRANDS` in `defaults.ts` ergänzen, sonst boot-fresh-Seed inkonsistent |
| Einen `appSettings`-Key | `DEFAULT_SETTINGS` in `defaults.ts` ergänzen + Typed-Getter/Setter in `repositories.ts` |
| `sectionTemplates` | `scripts/seed-section-templates.ts` ist nicht nur Setup — wird auch im Entrypoint idempotent geseedet |
| `emailQueue` | Worker-Code in `src/worker/index.ts` + `campaign-repo.ts::pickPendingJobs/getJobContext` prüfen |
| `trackingEvents` | Stats-Aggregat-Queries (überall wo `eventType` gefiltert wird) + Conversion-Dedup-Regel beachten |
| Eine FK-Beziehung | `onDelete: 'cascade'` bewusst entscheiden (z.B. `sectionTemplates.brandId` ist cascade) |

### SQLite-Eigenheiten

better-sqlite3 / SQLite kann NICHT, was Postgres-Trainings-Bias erwartet:

- ❌ `DROP COLUMN` direkt → Workaround: `CREATE TABLE _new`, `INSERT INTO _new SELECT…`, `DROP _old`, `ALTER RENAME` (Drizzle generiert das manchmal nicht — Migration **manuell** umschreiben)
- ❌ `ALTER COLUMN TYPE` → gleiches Pattern
- ❌ Concurrent Writes ohne WAL → wir nutzen WAL (`src/lib/db/client.ts`), aber better-sqlite3 ist synchron und serialisiert Writes per default
- ✅ FK-Constraints sind ON (`PRAGMA foreign_keys = ON` in `client.ts`) — Cascade & Restrict funktionieren
- ✅ Single-File-DB unter `data/mailforge.db` (gitignored, Volume-mounted im Container)

### Niemals raw SQL über die Bash

Auch nicht "nur schnell zum Inspizieren". `npm run db:studio` öffnet Drizzle Studio im Browser, ansonsten gibt es überall typisierte Repo-Funktionen. Wenn du eine neue brauchst, in `campaign-repo.ts` oder `repositories.ts` ergänzen — nicht ad-hoc-querien.

## Defaults & Self-Heal

Mehrstufiges Bootstrap-System, das verhindert, dass ein frisch gestartetes MailForge ohne Brands/Settings dasteht.

### `seedIfEmpty()` — initial Bootstrap

`src/lib/db/repositories.ts::seedIfEmpty()` läuft beim ersten Lookup. Bedingung: `brands`-Tabelle leer. Schreibt `DEFAULT_BRANDS` + alle Key-Value-Pairs aus `DEFAULT_SETTINGS`. **Idempotent durch das Empty-Check** — danach passiert nichts mehr.

→ **Konsequenz:** wenn du nach dem ersten Boot einen neuen Default-Wert (z.B. einen neuen `appSettings`-Key) hinzufügst, wird er **nicht** automatisch nachgetragen. Du brauchst zusätzlich eine **self-heal**-Routine.

### `getM365Senders()` — running self-heal

Beispiel-Pattern für Self-Heal in `repositories.ts`:

```typescript
export function getM365Senders(): M365Sender[] {
  // Liest aktuellen Pool aus appSettings
  // Merged DEFAULT_M365_SENDERS idempotent (Match per Email)
  // Schreibt Pool zurück wenn neue Defaults hinzugefügt wurden
}
```

Wenn du in `defaults.ts` einen neuen `M365_SENDER` hinzufügst, taucht er beim nächsten Aufruf automatisch im Pool auf. Dasselbe Pattern funktioniert für andere Settings — wenn ein User-editierbarer KV-Store **garantiert** alle vom Code erwarteten Keys haben muss, eine self-heal-Funktion bauen, die im `read`-Path lazy-merged.

`shopDomainHealDone`-Flag (`repositories.ts:9`) zeigt, wie man eine "einmal pro Prozess"-Heal-Routine baut, ohne sie bei jedem Read auszuführen.

### Wann self-heal nötig ist

| Daten-Typ | self-heal nötig? |
|---|---|
| Brand-Defaults (ITS/KSM/Spartarif) | nein — Brand-Pflege per UI, User entscheidet |
| `appSettings.system_prompt_*` | nein — User pflegt, Fallback in `defaults.ts` reicht |
| `appSettings.m365_senders` (Pool) | **ja** — Code erwartet bestimmte Sender, neue Defaults müssen ankommen |
| `appSettings.conversion_polling` | **ja** — `cumas-poller.ts` braucht Default-Werte |
| `appSettings.segments` / `goals` | optional — wenn UI lazy-creates, nicht nötig |
| `sectionTemplates` | **ja** — `seed-section-templates.ts` läuft im Entrypoint idempotent |

## Cumas-CRM-Integration

`src/lib/crm/cumas-client.ts` ist das einzige File, das mit der Nova/Cumas-API spricht. Drei Endpunkt-Familien: `audience`, `facets`, `schema`, plus `business`/`evb`/`orders` als List-Endpoints.

### Auth & Token-Cache

```typescript
let cachedToken: string | null = null;
let tokenExpiresAt = 0; // unix-seconds

// Refresh wenn weniger als 300s remaining (5min Safety-Margin)
if (!cachedToken || nowSec >= tokenExpiresAt - 300) {
  // POST /api/login_check mit { username, password }
  // cachedToken = data.token; tokenExpiresAt = …
}
```

JWT lebt im Modul-Scope. Beide Prozesse (Next.js + Worker) cachen unabhängig — keine geteilte Cache-Schicht nötig, der Token ist eh stateless.

**Failure-Mode:** Wenn `CRM_USERNAME` / `CRM_PASSWORD` leer → Client liefert **Stub-Empfänger** für UI-Testing. Im Prod dürfen sie nie leer sein, sonst zeigt der Recipient-Picker Mock-Daten ohne Warnung.

### Server-Filter vs Client-Filter

Cumas-Endpoints haben **server-side** Filter (Pagination, Datumsbereiche) und MailForge ergänzt **client-side** Filter:

- `listBusiness(server)` / `listEvbs(server)` / `listOrders(server)` → API-Call
- `applyBusinessClientFilters(records, client)` etc. → in-memory-Filter

Wenn du eine neue Filter-Dimension hinzufügst: prüfen, ob Cumas das serverseitig kann (Performance!), sonst Client-Filter ergänzen. Niemals Client-Filter über > 10k Records laufen lassen ohne Pagination.

### Magic-Link-Builder

`src/lib/crm/kompass-link.ts` baut `{{LOGIN_LINK}}` — ein HMAC-signierter Direkt-Login-Link für Empfänger. Wird **deterministisch im Worker** während der Tag-Substitution eingesetzt (gleiche Reihenfolge wie System-Tags — siehe `mailforge-send-pipeline` Skill). LLM darf `{{LOGIN_LINK}}` als Token im MJML stehen lassen.

## Tracking-Events & Conversion-Dedup

`tracking_events` hat sechs `eventType`s: `open`, `click`, `bounce`, `unsubscribe`, `complaint`, `conversion`. Conversions sind das einzige Event, das aus zwei Quellen kommt — und das ist der Punkt, wo Stats-Aggregate falsch zählen können.

### Zwei Conversion-Pfade

| Pfad | `source` | Wie | Endpoint / File |
|---|---|---|---|
| Pixel-Beacon | `'pixel'` | Thank-You-Page-Beacon mit `?mf_rcp={{RECIPIENT_ID}}` | `/api/track/conversion/route.ts` |
| API-Polling | `'api_poll'` | Worker pollt Cumas-Orders, matched per `recipientId` | `src/lib/conversions/cumas-poller.ts` |

Eine echte Order kann beide Wege triggern: User klickt → Pixel feuert auf Thank-You-Page, später taucht die Order in der Cumas-API auf → Poller schreibt zweites Event.

### Dedup-Regel

In Stats-Aggregat-Queries:

```typescript
// Pseudocode
SELECT COUNT(DISTINCT recipientId) AS conversions
FROM tracking_events
WHERE eventType = 'conversion'
  AND campaignId = ?
// Optional: GROUP BY recipientId, gefolgt von "WHERE row 1"
```

Konkret: **Dedup per `(recipientId, kind)`** — `kind` ist hier z.B. die Order-Art (App-Signup vs. Kauf), die im `eventData`-JSON steckt. Beim Schreiben einer neuen Aggregat-Query: dieselbe Dedup-Regel verwenden, nicht naiv `COUNT(*)`.

### Conversion-Polling-Rate-Limit

`src/lib/conversions/cumas-poller.ts`:

- Settings live in `app_settings.conversion_polling = { intervalMinutes, perSourceLastTickIso }`.
- Default `intervalMinutes: 15`. Min: 1.
- **Pro Tick wird gelesen** → Settings-Änderungen in der UI greifen **ohne** Worker-Restart.
- `lastConversionPollTick` wird **vor** dem `await runConversionPolling()` gesetzt — verhindert Race, wenn der Loop schneller tickt als die Polling-Promise resolved.
- Fire-and-forget: Send-Loop wartet nicht. Fehler im Poller blockieren keinen Send.

Beim Hinzufügen einer neuen Conversion-Quelle (z.B. dritter Webhook):
1. `tracking_events.source` als `enum` erweitern (Schema-Change!)
2. Einen neuen Endpoint / Poll-Funktion bauen
3. Dedup-Regel updaten (jetzt drei Quellen)
4. Stats-UI prüfen, ob die neue Source angezeigt werden soll

## DB-Constraints, FK-Cascade

Aus `schema.ts` zu beachten:

| Beziehung | onDelete |
|---|---|
| `sectionTemplates.brandId → brands.id` | **cascade** — Brand löschen killt alle Templates |
| `campaigns.brandId → brands.id` | meistens `restrict` (manuell prüfen!) |
| `campaignVariants.campaignId → campaigns.id` | cascade (Variant-Lifecycle hängt an Campaign) |
| `emailQueue.campaignId / variantId` | cascade |
| `trackingEvents.recipientId` | restrict — sonst verlieren wir Historie |

Beim Hinzufügen neuer FKs immer explizit `onDelete` setzen, nie auf Default verlassen.

## Migration-Inspection

Migrationen liegen in `drizzle/` mit Sequenz-Prefix: `0000_…sql` bis `0009_…sql` (Stand 2026-05). Jede SQL-Datei reviewen, **bevor** sie zu Git geht:

```bash
# Lokal nach generate:
git diff drizzle/  # Was wurde geschrieben?

# Wenn Drizzle ein DESTRUCTIVE TABLE-RECREATE generiert hat:
# Manuell editieren oder schemas anpassen, damit Drizzle nur ADD COLUMN macht.
```

`drizzle/meta/` enthält den Snapshot-State — nicht händisch editieren.

## Häufige Fehler → Fix

| Symptom | Ursache | Fix |
|---|---|---|
| `no such column 'foo'` beim Start | Migration nicht angewendet | `npm run db:migrate` (lokal) / Server: Container neu starten, entrypoint migrated |
| Settings-Default fehlt nach Update | Kein self-heal-Path | `getM365Senders()`-Pattern adaptieren |
| Recipient-Picker zeigt nur Mock-Daten | `CRM_USERNAME` / `CRM_PASSWORD` leer in `.env` | `.env.local` (dev) bzw. Server-`.env` ergänzen |
| Conversion-Count zu hoch | Pixel + Poll doppelt gezählt | Dedup `(recipientId, kind)` in Aggregat-Query einbauen |
| `getJobContext` returned undefined | `variantId` zeigt auf nicht-existente Variante (Schema-Drift?) | Datenintegrität prüfen, ggf. fix-up-Migration |
| Drizzle generiert riesige TABLE-Recreate-Migration | Default-Wert geändert oder Spaltentyp leicht modifiziert | Migration händisch zu einem `ADD COLUMN DEFAULT…` umschreiben |

## Cross-References

- Wann was im Worker gelesen wird: siehe `mailforge-send-pipeline` Skill.
- `sectionTemplates`-Pflege & `bodyMode`-Branching: siehe `mailforge-section-authoring` Skill.
- System-Prompt-Defaults (`DEFAULT_SYSTEM_PROMPT_GENERATE` etc.) und ihre Pflege: siehe `mailforge-ai-pipeline` Skill.
- Failure-Patterns aus früheren Sessions (z.B. fehlende DB-Backups vor Deploy): siehe `mailforge-learnings` Skill.

## Kritische Files

| Datei | Verantwortung |
|---|---|
| `src/lib/db/schema.ts` | Single Source of Truth für alle Tabellen + Typen |
| `src/lib/db/defaults.ts` | `DEFAULT_BRANDS`, `DEFAULT_SETTINGS`, `DEFAULT_M365_SENDERS`, `DEFAULT_SYSTEM_PROMPT_*`, `DEFAULT_SHOP_DOMAINS_BY_SLUG` |
| `src/lib/db/repositories.ts` | `seedIfEmpty`, `getM365Senders` (self-heal), `getAppSetting/setAppSetting`, Brand-CRUD, Typed-Prompt-Getter/Setter |
| `src/lib/db/campaign-repo.ts` | Campaign/Variant-Lifecycle, Job-Queue, A/B-Logik |
| `src/lib/db/client.ts` | better-sqlite3-Client, WAL + `foreign_keys=ON` |
| `src/lib/db/migrate.ts` | Programmatic Drizzle-Migrator (auch im Entrypoint genutzt) |
| `drizzle/*.sql` + `drizzle/meta/` | Migration-History (commited) |
| `src/lib/crm/cumas-client.ts` | JWT-Cache + Audience/Facets/Schema-Queries |
| `src/lib/crm/kompass-link.ts` | `{{LOGIN_LINK}}`-Magic-Link-Builder (HMAC-signiert) |
| `src/lib/conversions/cumas-poller.ts` | API-Poll-Tick, Per-Source-Cursor in `appSettings.conversion_polling` |
| `src/app/api/track/conversion/route.ts` | Pixel-Beacon-Endpoint (Public via Traefik) |
| `scripts/seed-section-templates.ts` | Idempotenter Section-Seed |
