222 lines
6.9 KiB
SQL
222 lines
6.9 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "MailProvider" AS ENUM ('GMAIL', 'GMX', 'WEBDE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "JobStatus" AS ENUM ('QUEUED', 'RUNNING', 'SUCCEEDED', 'FAILED', 'CANCELED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RuleActionType" AS ENUM ('MOVE', 'DELETE', 'ARCHIVE', 'LABEL');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RuleConditionType" AS ENUM ('HEADER', 'SUBJECT', 'FROM', 'LIST_UNSUBSCRIBE', 'LIST_ID');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tenant" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Tenant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MailboxAccount" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"provider" "MailProvider" NOT NULL,
|
|
"imapHost" TEXT NOT NULL,
|
|
"imapPort" INTEGER NOT NULL,
|
|
"imapTLS" BOOLEAN NOT NULL,
|
|
"smtpHost" TEXT,
|
|
"smtpPort" INTEGER,
|
|
"smtpTLS" BOOLEAN,
|
|
"oauthToken" TEXT,
|
|
"appPassword" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "MailboxAccount_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MailboxFolder" (
|
|
"id" TEXT NOT NULL,
|
|
"mailboxAccountId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"remoteId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "MailboxFolder_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MailItem" (
|
|
"id" TEXT NOT NULL,
|
|
"folderId" TEXT NOT NULL,
|
|
"messageId" TEXT NOT NULL,
|
|
"subject" TEXT,
|
|
"from" TEXT,
|
|
"receivedAt" TIMESTAMP(3),
|
|
"listId" TEXT,
|
|
"listUnsubscribe" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "MailItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Rule" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Rule_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RuleCondition" (
|
|
"id" TEXT NOT NULL,
|
|
"ruleId" TEXT NOT NULL,
|
|
"type" "RuleConditionType" NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "RuleCondition_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RuleAction" (
|
|
"id" TEXT NOT NULL,
|
|
"ruleId" TEXT NOT NULL,
|
|
"type" "RuleActionType" NOT NULL,
|
|
"target" TEXT,
|
|
|
|
CONSTRAINT "RuleAction_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CleanupJob" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"mailboxAccountId" TEXT NOT NULL,
|
|
"status" "JobStatus" NOT NULL DEFAULT 'QUEUED',
|
|
"startedAt" TIMESTAMP(3),
|
|
"finishedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "CleanupJob_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UnsubscribeAttempt" (
|
|
"id" TEXT NOT NULL,
|
|
"jobId" TEXT NOT NULL,
|
|
"mailItemId" TEXT,
|
|
"method" TEXT NOT NULL,
|
|
"target" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "UnsubscribeAttempt_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CleanupJobEvent" (
|
|
"id" TEXT NOT NULL,
|
|
"jobId" TEXT NOT NULL,
|
|
"level" TEXT NOT NULL,
|
|
"message" TEXT NOT NULL,
|
|
"progress" INTEGER,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "CleanupJobEvent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MailboxAccount_tenantId_idx" ON "MailboxAccount"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MailboxFolder_mailboxAccountId_idx" ON "MailboxFolder"("mailboxAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MailItem_folderId_idx" ON "MailItem"("folderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MailItem_messageId_idx" ON "MailItem"("messageId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Rule_tenantId_idx" ON "Rule"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RuleCondition_ruleId_idx" ON "RuleCondition"("ruleId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RuleAction_ruleId_idx" ON "RuleAction"("ruleId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CleanupJob_tenantId_idx" ON "CleanupJob"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CleanupJob_mailboxAccountId_idx" ON "CleanupJob"("mailboxAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "UnsubscribeAttempt_jobId_idx" ON "UnsubscribeAttempt"("jobId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CleanupJobEvent_jobId_idx" ON "CleanupJobEvent"("jobId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MailboxAccount" ADD CONSTRAINT "MailboxAccount_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MailboxFolder" ADD CONSTRAINT "MailboxFolder_mailboxAccountId_fkey" FOREIGN KEY ("mailboxAccountId") REFERENCES "MailboxAccount"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MailItem" ADD CONSTRAINT "MailItem_folderId_fkey" FOREIGN KEY ("folderId") REFERENCES "MailboxFolder"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Rule" ADD CONSTRAINT "Rule_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RuleCondition" ADD CONSTRAINT "RuleCondition_ruleId_fkey" FOREIGN KEY ("ruleId") REFERENCES "Rule"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RuleAction" ADD CONSTRAINT "RuleAction_ruleId_fkey" FOREIGN KEY ("ruleId") REFERENCES "Rule"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CleanupJob" ADD CONSTRAINT "CleanupJob_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CleanupJob" ADD CONSTRAINT "CleanupJob_mailboxAccountId_fkey" FOREIGN KEY ("mailboxAccountId") REFERENCES "MailboxAccount"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "UnsubscribeAttempt" ADD CONSTRAINT "UnsubscribeAttempt_jobId_fkey" FOREIGN KEY ("jobId") REFERENCES "CleanupJob"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CleanupJobEvent" ADD CONSTRAINT "CleanupJobEvent_jobId_fkey" FOREIGN KEY ("jobId") REFERENCES "CleanupJob"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|