50 lines
2.2 KiB
SQL
50 lines
2.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "public"."MarketingMilestoneStatus" AS ENUM ('pending', 'in_progress', 'completed');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingMilestone" (
|
|
"id" TEXT NOT NULL,
|
|
"initiativeId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL DEFAULT '',
|
|
"dueDate" TIMESTAMP(3) NOT NULL,
|
|
"status" "public"."MarketingMilestoneStatus" NOT NULL DEFAULT 'pending',
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdById" TEXT,
|
|
"createdByName" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "MarketingMilestone_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingMilestoneCheckpoint" (
|
|
"id" TEXT NOT NULL,
|
|
"milestoneId" TEXT NOT NULL,
|
|
"note" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdByName" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingMilestoneCheckpoint_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingMilestone_initiativeId_dueDate_status_idx" ON "public"."MarketingMilestone"("initiativeId", "dueDate", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingMilestoneCheckpoint_milestoneId_createdAt_idx" ON "public"."MarketingMilestoneCheckpoint"("milestoneId", "createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingMilestone" ADD CONSTRAINT "MarketingMilestone_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingMilestone" ADD CONSTRAINT "MarketingMilestone_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingMilestoneCheckpoint" ADD CONSTRAINT "MarketingMilestoneCheckpoint_milestoneId_fkey" FOREIGN KEY ("milestoneId") REFERENCES "public"."MarketingMilestone"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingMilestoneCheckpoint" ADD CONSTRAINT "MarketingMilestoneCheckpoint_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|