281 lines
11 KiB
Plaintext
281 lines
11 KiB
Plaintext
================================================================================
|
|
QR MASTER - SQL-BEFEHLE ZUM UMSETZUNGSPLAN VOM 27.07.2026
|
|
Gehoert zu: PLAN_CRO_UND_RETENTION_2026-07-27.md
|
|
================================================================================
|
|
|
|
POLICY (aus CLAUDE.md): Keine Prisma-Migrationen. Alle Schemaaenderungen werden
|
|
als rohes SQL direkt gegen die laufende PostgreSQL-Instanz ausgefuehrt. Danach
|
|
prisma/schema.prisma von Hand angleichen und "npx prisma generate" laufen
|
|
lassen - niemals "npx prisma migrate".
|
|
|
|
AUSFUEHREN ueber:
|
|
npm run docker:db
|
|
oder einzeln:
|
|
docker-compose exec db psql -U postgres -d qrmaster -c "<statement>"
|
|
|
|
REIHENFOLGE: Block 0 -> 1 -> 2 -> 3 -> 4 -> 5 -> 6. Alle Bloecke sind Pflicht.
|
|
Block 5 (prisma generate) darf nicht vergessen werden.
|
|
|
|
|
|
================================================================================
|
|
BLOCK 0 - VORHER PRUEFEN (liest nur, aendert nichts)
|
|
================================================================================
|
|
|
|
-- 0.1 Wie viele FREE-Nutzer bekommen durch die Umstellung auf "nur ACTIVE
|
|
-- zaehlen" rueckwirkend Slots frei? Das ist eine Lockerung, nimmt also
|
|
-- niemandem etwas weg - aber die Zahl sollte man kennen.
|
|
SELECT COUNT(DISTINCT u.id) AS betroffene_free_nutzer
|
|
FROM "User" u
|
|
JOIN "QRCode" q ON q."userId" = u.id
|
|
WHERE u.plan = 'FREE'
|
|
AND q.type = 'DYNAMIC'
|
|
AND q.status = 'PAUSED';
|
|
|
|
-- 0.2 Wie viele Nutzer wuerden die neue "erster Scan"-Mail beim ersten
|
|
-- Cron-Lauf bekommen? WICHTIG: ohne Block 3.2 ginge sie an ALLE
|
|
-- Bestandsnutzer mit Scan-Historie, auch an solche, deren erster Scan
|
|
-- Monate zurueckliegt. Das waere kein Anlass mehr, sondern eine
|
|
-- Massenmail unter deinem Namen.
|
|
SELECT COUNT(*) AS wuerden_mail_bekommen
|
|
FROM "User"
|
|
WHERE "firstScanAt" IS NOT NULL;
|
|
|
|
-- 0.3 Verteilung der Plaene - Kontext fuer alles Weitere.
|
|
SELECT plan, COUNT(*) AS nutzer
|
|
FROM "User"
|
|
GROUP BY plan
|
|
ORDER BY nutzer DESC;
|
|
|
|
-- 0.4 Wie viele FREE-Nutzer sitzen aktuell am Limit? Das ist die Zielgruppe
|
|
-- des neuen Limit-Modals aus Phase 1 und der Limit-Mail aus Phase 5.1.
|
|
SELECT COUNT(*) AS free_nutzer_am_limit
|
|
FROM (
|
|
SELECT u.id
|
|
FROM "User" u
|
|
JOIN "QRCode" q ON q."userId" = u.id
|
|
WHERE u.plan = 'FREE' AND q.type = 'DYNAMIC' AND q.status = 'ACTIVE'
|
|
GROUP BY u.id
|
|
HAVING COUNT(q.id) >= 3
|
|
) t;
|
|
|
|
|
|
================================================================================
|
|
BLOCK 1 - INDEX FUER DIE NEUE LIMIT-QUERY (Phase 1.1)
|
|
================================================================================
|
|
|
|
-- Die Zaehl-Query in src/app/(main)/api/qrs/route.ts bekommt zusaetzlich
|
|
-- status = 'ACTIVE'. Dieser Index deckt die neue Bedingung ab.
|
|
-- Dieselbe Aenderung gilt fuer src/app/(main)/api/user/stats/route.ts.
|
|
|
|
CREATE INDEX IF NOT EXISTS "QRCode_userId_type_status_idx"
|
|
ON "QRCode" ("userId", "type", "status");
|
|
|
|
|
|
================================================================================
|
|
BLOCK 2 - MARKER-SPALTEN FUER DIE NEUEN RETENTION-MAILS (Phase 5)
|
|
================================================================================
|
|
|
|
-- limitReachedNudgeSentAt: Marker fuer die verhaltensbasierte Limit-Mail.
|
|
-- Ersetzt die kalenderbasierte Tag-7-Mail, die heute auch an Nutzer geht,
|
|
-- die das Limit gar nicht erreicht haben.
|
|
-- firstScanNudgeSentAt: Marker fuer die neue "erster Scan"-Mail.
|
|
-- Die Erkennung selbst braucht nichts Neues - User."firstScanAt" existiert
|
|
-- bereits und wird in src/app/(main)/r/[slug]/route.ts gesetzt.
|
|
|
|
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "limitReachedNudgeSentAt" TIMESTAMP(3);
|
|
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "firstScanNudgeSentAt" TIMESTAMP(3);
|
|
|
|
-- Index fuer den Cron-Job, der Nutzer mit erstem Scan ohne Versandmarker sucht.
|
|
CREATE INDEX IF NOT EXISTS "User_firstScanAt_firstScanNudgeSentAt_idx"
|
|
ON "User" ("firstScanAt", "firstScanNudgeSentAt");
|
|
|
|
|
|
================================================================================
|
|
BLOCK 3 - BESTANDSDATEN VORBEREITEN (unbedingt VOR dem ersten Cron-Lauf)
|
|
================================================================================
|
|
|
|
-- 3.1 Nutzer, die schon am Limit sitzen, haben die Limit-Mail nie bekommen
|
|
-- koennen - es gab sie nicht. Ob sie sie nachtraeglich bekommen sollen,
|
|
-- ist eine Entscheidung:
|
|
--
|
|
-- Variante A - sie sollen die Mail bekommen: dieses Statement NICHT
|
|
-- ausfuehren. Der erste Cron-Lauf schickt sie an alle, die am Limit sind.
|
|
-- Bei vielen Bestandsnutzern ist das ein Versand-Peak.
|
|
--
|
|
-- Variante B - nur Neufaelle ab jetzt: Statement ausfuehren, dann bekommen
|
|
-- bestehende Limit-Faelle keine Mail und die Sequenz startet sauber.
|
|
|
|
-- Variante B (auskommentiert - bewusst entscheiden und dann aktivieren):
|
|
-- UPDATE "User" u
|
|
-- SET "limitReachedNudgeSentAt" = now()
|
|
-- WHERE u.plan = 'FREE'
|
|
-- AND (SELECT COUNT(*) FROM "QRCode" q
|
|
-- WHERE q."userId" = u.id AND q.type = 'DYNAMIC' AND q.status = 'ACTIVE') >= 3;
|
|
|
|
-- 3.2 PFLICHT: Bestandsnutzer, deren erster Scan laenger als 7 Tage her ist,
|
|
-- als "bereits benachrichtigt" markieren. Ohne dieses Statement geht die
|
|
-- "erster Scan"-Mail beim ersten Lauf an die gesamte Bestandsbasis - mit
|
|
-- einem Anlass, der Monate zurueckliegt.
|
|
UPDATE "User"
|
|
SET "firstScanNudgeSentAt" = now()
|
|
WHERE "firstScanAt" IS NOT NULL
|
|
AND "firstScanAt" < now() - interval '7 days';
|
|
|
|
-- 3.3 Kontrolle nach 3.2 - sollte eine kleine, plausible Zahl sein.
|
|
SELECT COUNT(*) AS offene_erster_scan_mails
|
|
FROM "User"
|
|
WHERE "firstScanAt" IS NOT NULL
|
|
AND "firstScanNudgeSentAt" IS NULL;
|
|
|
|
|
|
================================================================================
|
|
BLOCK 4 - PFLICHT: DESIGN-VORLAGEN
|
|
================================================================================
|
|
|
|
-- Nicht mehr optional: die Design-Presets sind gebaut. Ohne diese Tabelle
|
|
-- laufen GET/POST/DELETE /api/design-presets und die Preset-Auswahl im
|
|
-- Bulk-Flow in einen Prisma-Fehler.
|
|
--
|
|
-- Die Formen selbst brauchen weiterhin keine Schemaaenderung: QRCode."style"
|
|
-- ist JSON, moduleShape, eyeFrameShape, eyeBallShape, gradientMode und
|
|
-- gradientTo liegen dort ohne Migration drin.
|
|
|
|
CREATE TABLE IF NOT EXISTS "QRDesignPreset" (
|
|
"id" TEXT PRIMARY KEY,
|
|
"userId" TEXT NOT NULL REFERENCES "User"("id") ON DELETE CASCADE,
|
|
"name" TEXT NOT NULL,
|
|
"style" JSONB NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT now(),
|
|
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "QRDesignPreset_userId_idx"
|
|
ON "QRDesignPreset" ("userId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "QRDesignPreset_userId_name_key"
|
|
ON "QRDesignPreset" ("userId", "name");
|
|
|
|
|
|
================================================================================
|
|
BLOCK 5 - PRISMA-SCHEMA VON HAND ANGLEICHEN (kein SQL, aber Pflichtschritt)
|
|
================================================================================
|
|
|
|
In prisma/schema.prisma, model User, im Block "// Retention email tracking"
|
|
ergaenzen:
|
|
|
|
limitReachedNudgeSentAt DateTime?
|
|
firstScanNudgeSentAt DateTime?
|
|
|
|
In model QRCode ergaenzen:
|
|
|
|
@@index([userId, type, status])
|
|
|
|
Ausserdem (Block 4 ist Pflicht) - beides ist im Repo bereits eingetragen,
|
|
diese Angabe dient nur der Kontrolle:
|
|
|
|
model QRDesignPreset {
|
|
id String @id @default(cuid())
|
|
userId String
|
|
name String
|
|
style Json
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([userId, name])
|
|
@@index([userId])
|
|
}
|
|
|
|
und in model User die Gegenseite:
|
|
|
|
designPresets QRDesignPreset[]
|
|
|
|
Danach:
|
|
|
|
npx prisma generate
|
|
|
|
NICHT "npx prisma migrate" - das wuerde gegen die Policy in CLAUDE.md verstossen.
|
|
|
|
|
|
================================================================================
|
|
BLOCK 6 - VERIFIKATION NACH DEM DEPLOY
|
|
================================================================================
|
|
|
|
-- 6.1 Sind alle neuen Spalten da?
|
|
SELECT column_name, data_type
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'User'
|
|
AND column_name IN ('limitReachedNudgeSentAt', 'firstScanNudgeSentAt',
|
|
'activationNudgeSentAt', 'upgradeNudgeSentAt',
|
|
'thirtyDayNudgeSentAt', 'firstScanAt')
|
|
ORDER BY column_name;
|
|
|
|
-- 6.2 Sind alle neuen Indizes da?
|
|
SELECT indexname
|
|
FROM pg_indexes
|
|
WHERE tablename IN ('User', 'QRCode', 'QRDesignPreset')
|
|
AND indexname IN ('QRCode_userId_type_status_idx',
|
|
'User_firstScanAt_firstScanNudgeSentAt_idx',
|
|
'QRDesignPreset_userId_idx',
|
|
'QRDesignPreset_userId_name_key')
|
|
ORDER BY indexname;
|
|
|
|
-- 6.3 Nutzt die neue Limit-Query den Index? Sollte "Index Scan" oder
|
|
-- "Index Only Scan" zeigen, keinen "Seq Scan".
|
|
EXPLAIN ANALYZE
|
|
SELECT COUNT(*) FROM "QRCode"
|
|
WHERE "userId" = (SELECT id FROM "User" LIMIT 1)
|
|
AND type = 'DYNAMIC'
|
|
AND status = 'ACTIVE';
|
|
|
|
-- 6.4 Wie viele Mails stehen im naechsten Cron-Lauf an? Vor dem ersten
|
|
-- scharfen Lauf pruefen, damit es keine Ueberraschung gibt.
|
|
SELECT
|
|
(SELECT COUNT(*) FROM "User"
|
|
WHERE "firstScanAt" IS NOT NULL AND "firstScanNudgeSentAt" IS NULL)
|
|
AS erster_scan_mails,
|
|
(SELECT COUNT(*) FROM "User" u WHERE u.plan = 'FREE'
|
|
AND u."limitReachedNudgeSentAt" IS NULL
|
|
AND (SELECT COUNT(*) FROM "QRCode" q
|
|
WHERE q."userId" = u.id AND q.type = 'DYNAMIC' AND q.status = 'ACTIVE') >= 3)
|
|
AS limit_mails,
|
|
(SELECT COUNT(*) FROM "User"
|
|
WHERE "activationNudgeSentAt" IS NULL
|
|
AND "createdAt" < now() - interval '3 days')
|
|
AS aktivierungs_mails;
|
|
|
|
|
|
================================================================================
|
|
ROLLBACK - falls etwas zurueckgedreht werden muss
|
|
================================================================================
|
|
|
|
-- Die Spalten sind additiv und nullable, ein Rollback ist normalerweise nicht
|
|
-- noetig. Falls doch: Datenverlust bei den Versandmarkern beachten - danach
|
|
-- koennten Nutzer Mails ein zweites Mal bekommen.
|
|
|
|
-- ALTER TABLE "User" DROP COLUMN IF EXISTS "limitReachedNudgeSentAt";
|
|
-- ALTER TABLE "User" DROP COLUMN IF EXISTS "firstScanNudgeSentAt";
|
|
-- DROP INDEX IF EXISTS "User_firstScanAt_firstScanNudgeSentAt_idx";
|
|
-- DROP INDEX IF EXISTS "QRCode_userId_type_status_idx";
|
|
-- DROP TABLE IF EXISTS "QRDesignPreset";
|
|
|
|
|
|
================================================================================
|
|
ZUSAMMENFASSUNG
|
|
================================================================================
|
|
|
|
Pflicht: 2 Spalten, 2 Indizes, 1 UPDATE fuer Bestandsdaten (Block 3.2)
|
|
Pflicht: 1 Tabelle mit 2 Indizes (Design-Presets, Block 4)
|
|
Formen: brauchen kein SQL - QRCode."style" ist bereits JSON
|
|
Phasen 1-4: brauchen kein SQL ausser dem Index aus Block 1
|
|
|
|
NACH dem SQL zwingend: npx prisma generate
|
|
Ohne generate kennt der Prisma-Client das Modell QRDesignPreset nicht und
|
|
/api/design-presets wirft zur Laufzeit.
|
|
|
|
Der einzige Schritt mit echtem Risiko ist Block 3.2. Wird er vergessen, geht
|
|
die "erster Scan"-Mail an die gesamte Bestandsbasis.
|
|
|
|
================================================================================
|