153 lines
6.8 KiB
SQL
153 lines
6.8 KiB
SQL
-- =====================================================================
|
||
-- DOGFATHER × VanVan Supporter-Abo — Datenbankschema
|
||
-- Setzt "Dogfather_VanVan_Supporter_Abo.odt" um (Abschnitt 28 empfiehlt
|
||
-- diese Struktur; hier leicht angepasst an die Konventionen des restlichen
|
||
-- Workers: TEXT-IDs, ISO-Zeitstempel, Wiederverwendung von audit_log statt
|
||
-- eines eigenen Änderungsprotokolls).
|
||
--
|
||
-- Bewusst eine EIGENE, von der internen Team-Zugangsverwaltung (users/
|
||
-- roles/sessions) komplett GETRENNTE Nutzerwelt — Supporter sind
|
||
-- öffentliche Fans/Kund:innen, keine Teammitglieder mit Rechten.
|
||
-- =====================================================================
|
||
|
||
-- Öffentliche Supporter-Accounts. Nur Name, TikTok-Name, E-Mail bei
|
||
-- Registrierung (siehe Abschnitt 5/30 — Datensparsamkeit).
|
||
CREATE TABLE IF NOT EXISTS supporter_users (
|
||
id TEXT PRIMARY KEY,
|
||
display_name TEXT NOT NULL,
|
||
tiktok_username TEXT, -- ohne "@", normalisiert
|
||
email TEXT NOT NULL UNIQUE,
|
||
email_verified INTEGER NOT NULL DEFAULT 0,
|
||
pending_email TEXT, -- neue E-Mail, bis sie bestätigt wird (Abschnitt 25)
|
||
status TEXT NOT NULL DEFAULT 'active', -- active | deleted
|
||
-- Versanddaten werden ERST bei der ersten Prämien-Einlösung abgefragt
|
||
-- (Abschnitt 17/30), deshalb hier bewusst alles NULL-fähig.
|
||
ship_name TEXT,
|
||
ship_street TEXT,
|
||
ship_zip TEXT,
|
||
ship_city TEXT,
|
||
ship_country TEXT,
|
||
ship_save_address INTEGER NOT NULL DEFAULT 0,
|
||
created_at TEXT NOT NULL,
|
||
last_login_at TEXT
|
||
);
|
||
|
||
-- Einmalcodes für E-Mail-Bestätigung UND Magic-Link-Login (Abschnitt 5/116).
|
||
CREATE TABLE IF NOT EXISTS supporter_auth_codes (
|
||
id TEXT PRIMARY KEY,
|
||
supporter_id TEXT NOT NULL REFERENCES supporter_users(id),
|
||
purpose TEXT NOT NULL, -- verify_email | login
|
||
code_hash TEXT NOT NULL,
|
||
expires_at TEXT NOT NULL,
|
||
used_at TEXT,
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
-- Sessions für den öffentlichen Supporter-Bereich — eigenes, von der
|
||
-- internen Team-Session-Tabelle getrenntes Token-System.
|
||
CREATE TABLE IF NOT EXISTS supporter_sessions (
|
||
token_hash TEXT PRIMARY KEY,
|
||
supporter_id TEXT NOT NULL REFERENCES supporter_users(id),
|
||
created_at TEXT NOT NULL,
|
||
last_active_at TEXT NOT NULL,
|
||
expires_at TEXT NOT NULL
|
||
);
|
||
|
||
-- Eine (aktuell: genau eine) Abo-Stufe pro Supporter.
|
||
CREATE TABLE IF NOT EXISTS supporter_subscriptions (
|
||
id TEXT PRIMARY KEY,
|
||
supporter_id TEXT NOT NULL REFERENCES supporter_users(id),
|
||
paypal_subscription_id TEXT UNIQUE, -- erst nach PayPal-Freigabe gesetzt
|
||
paypal_plan_id TEXT,
|
||
status TEXT NOT NULL DEFAULT 'pending', -- pending | active | suspended | cancelled | expired
|
||
started_at TEXT,
|
||
next_payment_at TEXT,
|
||
paid_period_end_at TEXT, -- bis wann Zugang nach Kündigung bleibt
|
||
cancelled_at TEXT,
|
||
-- Fortschritt (Abschnitt 11-13): Gesamtzahl erfolgreich angerechneter
|
||
-- Monate bleibt für IMMER erhalten (auch über Kündigung/Reaktivierung
|
||
-- hinweg, Abschnitt 23) — daraus werden Zyklus/Meilenstein/Kollektion
|
||
-- rein rechnerisch abgeleitet (siehe lib/supporter-cycle.js), NICHT
|
||
-- redundant gespeichert, damit nichts auseinanderlaufen kann.
|
||
total_paid_months INTEGER NOT NULL DEFAULT 0,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL
|
||
);
|
||
|
||
-- Jede EINZELNE erfolgreiche/fehlgeschlagene PayPal-Zahlung. Idempotenz
|
||
-- über paypal_event_id: jedes Webhook-Ereignis wird nur einmal verarbeitet
|
||
-- (Abschnitt 8, "doppelt empfangenes PayPal-Ereignis: keine doppelte
|
||
-- Anrechnung").
|
||
CREATE TABLE IF NOT EXISTS supporter_payments (
|
||
id TEXT PRIMARY KEY,
|
||
supporter_id TEXT NOT NULL REFERENCES supporter_users(id),
|
||
subscription_id TEXT NOT NULL REFERENCES supporter_subscriptions(id),
|
||
paypal_transaction_id TEXT,
|
||
paypal_event_id TEXT UNIQUE,
|
||
amount TEXT,
|
||
currency TEXT DEFAULT 'EUR',
|
||
payment_status TEXT NOT NULL, -- completed | failed | refunded | reversed
|
||
is_sandbox INTEGER NOT NULL DEFAULT 0, -- Testzahlungen zählen NIE als echter Monat
|
||
counted_for_progress INTEGER NOT NULL DEFAULT 0,
|
||
paid_at TEXT NOT NULL,
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
-- Supporter-Kollektionen (Abschnitt 14) — jeder abgeschlossene 30-Monats-
|
||
-- Zyklus bekommt ein neues Design. Zyklus-Nummer 1 = Kollektion 01 usw.
|
||
-- collection_number ist der 1-basierte Zyklus-Index (global, nicht pro
|
||
-- Supporter — alle Supporter im selben Zyklus teilen sich EIN Design).
|
||
CREATE TABLE IF NOT EXISTS supporter_collections (
|
||
id TEXT PRIMARY KEY,
|
||
collection_number INTEGER NOT NULL UNIQUE,
|
||
name TEXT NOT NULL,
|
||
theme TEXT,
|
||
description TEXT,
|
||
cover_image TEXT,
|
||
small_teddy_design TEXT,
|
||
medium_teddy_design TEXT,
|
||
large_teddy_design TEXT,
|
||
mug_design TEXT,
|
||
card_design TEXT,
|
||
hoodie_design TEXT,
|
||
available_hoodie_sizes TEXT DEFAULT '["S","M","L","XL","XXL"]', -- JSON-Array
|
||
status TEXT NOT NULL DEFAULT 'draft', -- draft | published
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
-- Eine Zeile pro freigeschaltetem Meilenstein/Prämie eines Supporters.
|
||
-- milestone_month ∈ {6,12,18,24,30}, cycle_number = welcher 30er-Zyklus.
|
||
CREATE TABLE IF NOT EXISTS supporter_premiums (
|
||
id TEXT PRIMARY KEY,
|
||
supporter_id TEXT NOT NULL REFERENCES supporter_users(id),
|
||
collection_id TEXT NOT NULL REFERENCES supporter_collections(id),
|
||
cycle_number INTEGER NOT NULL,
|
||
milestone_month INTEGER NOT NULL, -- 6 | 12 | 18 | 24 | 30
|
||
premium_type TEXT NOT NULL, -- teddy_klein | teddy_mittel | teddy_gross | tasse_karte | hoodie
|
||
unlocked_at TEXT NOT NULL,
|
||
redeem_status TEXT NOT NULL DEFAULT 'verfuegbar',
|
||
-- verfuegbar | eingeloest | wird_geprueft | wird_vorbereitet
|
||
-- versandbereit | versendet | zugestellt | erhalten | problem | ersatz_wird_vorbereitet
|
||
redeemed_at TEXT,
|
||
hoodie_size TEXT,
|
||
hoodie_variant TEXT,
|
||
ship_name TEXT, ship_street TEXT, ship_zip TEXT, ship_city TEXT, ship_country TEXT,
|
||
tracking_number TEXT,
|
||
shipped_at TEXT,
|
||
delivered_at TEXT,
|
||
admin_note TEXT,
|
||
UNIQUE (supporter_id, cycle_number, milestone_month)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_supp_sub_supporter ON supporter_subscriptions(supporter_id);
|
||
CREATE INDEX IF NOT EXISTS idx_supp_pay_supporter ON supporter_payments(supporter_id);
|
||
CREATE INDEX IF NOT EXISTS idx_supp_pay_sub ON supporter_payments(subscription_id);
|
||
CREATE INDEX IF NOT EXISTS idx_supp_premiums_supporter ON supporter_premiums(supporter_id);
|
||
CREATE INDEX IF NOT EXISTS idx_supp_sessions_supporter ON supporter_sessions(supporter_id);
|
||
CREATE INDEX IF NOT EXISTS idx_supp_codes_supporter ON supporter_auth_codes(supporter_id);
|
||
|
||
-- Kollektion 01 direkt mit anlegen, damit das System sofort nutzbar ist —
|
||
-- Designs/Bilder können jederzeit im Admin-Bereich nachgepflegt werden.
|
||
INSERT OR IGNORE INTO supporter_collections (id, collection_number, name, theme, description, status, created_at)
|
||
VALUES ('collection-01', 1, 'Der Anfang', 'Babyblau & Silber', 'Die allererste Supporter-Kollektion von Dogfather × VanVan.', 'published', datetime('now'));
|