45 lines
1.7 KiB
SQL
45 lines
1.7 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "EmailJobStatus" AS ENUM ('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED');
|
|
|
|
-- CreateTable: log append-only setiap email yang berhasil terkirim.
|
|
-- `idempotencyKey` UNIQUE cegah double-send saat webhook retry / cron rerun.
|
|
CREATE TABLE "EmailSent" (
|
|
"id" TEXT NOT NULL,
|
|
"idempotencyKey" TEXT NOT NULL,
|
|
"to" TEXT NOT NULL,
|
|
"template" TEXT NOT NULL,
|
|
"subject" TEXT NOT NULL,
|
|
"providerMessageId" TEXT,
|
|
"sentAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "EmailSent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "EmailSent_idempotencyKey_key" ON "EmailSent"("idempotencyKey");
|
|
CREATE INDEX "EmailSent_to_sentAt_idx" ON "EmailSent"("to", "sentAt" DESC);
|
|
CREATE INDEX "EmailSent_template_sentAt_idx" ON "EmailSent"("template", "sentAt" DESC);
|
|
|
|
-- CreateTable: retry queue untuk email yang gagal saat sync send.
|
|
-- Cron `/api/cron/process-email-jobs` pick PENDING/FAILED (attempts<5),
|
|
-- exponential backoff (scheduledAt bumped tiap retry).
|
|
CREATE TABLE "EmailJob" (
|
|
"id" TEXT NOT NULL,
|
|
"idempotencyKey" TEXT NOT NULL,
|
|
"to" TEXT NOT NULL,
|
|
"template" TEXT NOT NULL,
|
|
"subject" TEXT NOT NULL,
|
|
"html" TEXT NOT NULL,
|
|
"status" "EmailJobStatus" NOT NULL DEFAULT 'PENDING',
|
|
"attempts" INTEGER NOT NULL DEFAULT 0,
|
|
"scheduledAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"lastAttemptAt" TIMESTAMP(3),
|
|
"lastError" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "EmailJob_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "EmailJob_status_scheduledAt_idx" ON "EmailJob"("status", "scheduledAt");
|
|
CREATE INDEX "EmailJob_idempotencyKey_idx" ON "EmailJob"("idempotencyKey");
|