Datenmodell

Entity-Relationship-Übersicht

tenants (1) ──┬── profiles (N)           ← User-Tenant-Zuordnung
              ├── tenant_settings (1)     ← Konfiguration
              ├── suppliers (N) ──── green_coffee_lots (N)
              ├── machines (N) ──── maintenance_tasks (N)
              ├── roast_profiles (N) ──── product_variants (N)
              │                     └──── roast_log_entries (N) ──── roasted_barrels (N)
              │                                                       └── packing_positions (N)
              ├── b2b_customers (N) ──── b2b_orders (N) ──── b2b_order_positions (N)
              │                    └──── b2b_invoices (N) ── b2b_invoice_positions (N)
              ├── pricing_agreements (N)
              ├── forecast_positions (N)
              ├── fixed_costs (N)
              └── shipments (N)

Rückverfolgungskette

Die zentrale Kette für EU-VO 178/2002 Compliance:

supplier → green_coffee_lots → roast_log_entries → roasted_barrels → packing_positions → product_variants → b2b_order_positions → b2b_customers

Kein Glied darf NULL sein (FK NOT NULL Constraints).

Kerntabellen

tenants

Wurzel der Multi-Tenancy. Jeder Tenant = eine Rösterei.

Spalte	Typ	Beschreibung
id	UUID (PK)	Tenant-ID
name	TEXT	Rösterei-Name
plan	TEXT	trial / starter / pro / cancelled
trial_ends_at	TIMESTAMPTZ	Ende der Testphase
stripe_customer_id	TEXT	Stripe-Kundennummer
stripe_subscription_id	TEXT	Stripe-Abo-ID
suspended_at	TIMESTAMPTZ	Suspendierungszeitpunkt

green_coffee_lots

Rohkaffee-Bestände.

Spalte	Typ	Beschreibung
id	UUID (PK)
tenant_id	UUID (FK)
lot_number	TEXT	Auto: LOT-NNNN
name	TEXT	Kaffee-Bezeichnung
supplier_id	UUID (FK)
origin_country	TEXT	Herkunftsland
raw_weight_kg	NUMERIC(10,2)	Liefergewicht
purchase_price_per_kg	NUMERIC(10,2)	Einkaufspreis
status	ENUM	Active / Depleted / Blocked
processing	ENUM	Washed / Natural / Honey / ...

Computed: remaining_kg = raw_weight_kg - SUM(roast_log_entries.raw_weight_g)/1000

roast_log_entries

Einzelne Röstungen.

Spalte	Typ	Beschreibung
id	UUID (PK)
roast_display_id	TEXT	Auto: PROFILE-DATE-RNNNN-MACHINE
profile_id	UUID (FK)	Röstprofil
machine_id	UUID (FK)	Röstmaschine
lot_id	UUID (FK, nullable)	Einzelnes Lot (NULL bei Blend)
lot_weights	JSONB	Blend: [{lot_id, weight_g}]
raw_weight_g	INTEGER	Rohgewicht in Gramm
roasted_weight_g	INTEGER	Röstgewicht in Gramm
roast_loss_pct	NUMERIC	Auto-berechnet
quality_check	ENUM	OK / Review / Rejected
barrel_id	UUID (FK)	Zugehörige Tonne
roast_date	DATE	Röstdatum

Blend-Handling: Entweder lot_id ODER lot_weights ist gesetzt (XOR).

roasted_barrels

Tonnen-Sessions.

Spalte	Typ	Beschreibung
id	UUID (PK)
physical_barrel	TEXT	Name der physischen Tonne
profile_id	UUID (FK)	Röstprofil
session_date	DATE	Datum der Session
closed_at	TIMESTAMPTZ	Wann geschlossen

Computed Status:

• Open: closed_at IS NULL
• Resting: closed_at IS NOT NULL AND jetzt < session_date + rest_days
• Ready: Ruhezeit abgelaufen AND available_g > 0
• Empty: available_g = 0

Constraint: UNIQUE(tenant_id, physical_barrel, session_date) — 1 Profil + 1 Tonne + 1 Tag

b2b_invoices

Rechnungen (GoBD-konform).

Spalte	Typ	Beschreibung
id	UUID (PK)
invoice_number	TEXT	NULL bis Finalisierung, dann RE-YYYY-NNN
invoice_status	ENUM	Draft / Finalized / Cancelled
payment_status	ENUM	Open / Paid / Overdue
total_net	NUMERIC(10,2)	Nettobetrag
tax_rate	NUMERIC(5,2)	MwSt-Satz
tax_amount	NUMERIC(10,2)	MwSt-Betrag
total_gross	NUMERIC(10,2)	Bruttobetrag
cancelled_by_invoice_id	UUID (self-ref)	Storno-Kette

Immutabilität: Finalisierte Rechnungen sind unveränderlich. Storno = neue Gutschrift-Rechnung.

Enums

-- Lot-Status
CREATE TYPE lot_status AS ENUM ('Active', 'Depleted', 'Blocked');

-- Aufbereitung
CREATE TYPE processing_method AS ENUM ('Washed', 'Natural', 'Honey', 'Anaerobic', 'WetHulled', 'Other');

-- Röststil
CREATE TYPE roast_style AS ENUM ('Espresso', 'Filter', 'Omni');

-- Qualitätsprüfung
CREATE TYPE quality_check AS ENUM ('OK', 'Review', 'Rejected');

-- Lieferstatus
CREATE TYPE delivery_status AS ENUM ('Pending', 'Shipped', 'Delivered');

-- Rechnungsstatus
CREATE TYPE invoice_status AS ENUM ('Draft', 'Finalized', 'Cancelled');

-- Zahlungsstatus
CREATE TYPE payment_status AS ENUM ('Open', 'Paid', 'Overdue');

-- Zahlungsbedingungen
CREATE TYPE payment_terms AS ENUM ('Prepaid', 'Net14', 'Net30', 'Net60', 'CashOnDelivery');

-- Kundentyp
CREATE TYPE customer_type AS ENUM ('B2B', 'Reseller', 'Gastro', 'Office');

-- Rollen
CREATE TYPE role_type AS ENUM ('admin', 'roester', 'packer', 'buero', 'readonly');

-- Preisbasis
CREATE TYPE price_basis AS ENUM ('PerKg', 'PerUnit', 'Flat');

-- Lieferintervall
CREATE TYPE delivery_interval AS ENUM ('Weekly', 'Biweekly', 'Monthly', 'OnDemand');

-- Versandstatus
CREATE TYPE shipment_status AS ENUM ('LabelCreated', 'InTransit', 'Delivered', 'Undeliverable', 'Cancelled');

Datentyp-Regeln

Datenart	Typ	Niemals
Geldbeträge	NUMERIC(10,2)	float / real
Gewichte	NUMERIC(10,2)	float / real
Prozente	NUMERIC(5,2)	float / real
Zeitstempel	TIMESTAMPTZ	TIMESTAMP (ohne tz)
IDs	UUID	INTEGER / SERIAL

Soft-Delete Pattern

Tabellen mit deleted_at TIMESTAMPTZ:

• green_coffee_lots, profiles, roast_log_entries, product_variants
• packing_positions, roasted_barrels, b2b_orders, pricing_agreements

Aktive Einträge: WHERE deleted_at IS NULL (mit partiellem Index).

2-Tier Delete: Soft Delete (reversibel) → Hard Delete (permanent, nach DSGVO-Prüfung).