69 行
3.1 KiB
SQL
69 行
3.1 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "ProjectCommunicationChannel" AS ENUM ('wechat', 'phone', 'meeting', 'wecom', 'email', 'onsite', 'other');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ProjectClosureChecklistStatus" AS ENUM ('pending', 'done', 'waived');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "project_communication_logs" (
|
|
"id" UUID NOT NULL,
|
|
"project_id" UUID NOT NULL,
|
|
"happened_at" TIMESTAMP(3) NOT NULL,
|
|
"channel" "ProjectCommunicationChannel" NOT NULL,
|
|
"external_contact_name" TEXT,
|
|
"external_org_name" TEXT,
|
|
"summary" TEXT NOT NULL,
|
|
"next_action" TEXT,
|
|
"next_follow_up_date" DATE,
|
|
"created_by" UUID NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "project_communication_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "project_closure_checklist_items" (
|
|
"id" UUID NOT NULL,
|
|
"project_id" UUID NOT NULL,
|
|
"item_key" VARCHAR(64) NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"status" "ProjectClosureChecklistStatus" NOT NULL DEFAULT 'pending',
|
|
"note" TEXT,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 100,
|
|
"completed_at" TIMESTAMP(3),
|
|
"completed_by" UUID,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "project_closure_checklist_items_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "idx_project_communication_logs_project_happened" ON "project_communication_logs"("project_id", "happened_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "idx_project_communication_logs_followup" ON "project_communication_logs"("project_id", "next_follow_up_date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "idx_project_communication_logs_creator_created" ON "project_communication_logs"("created_by", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "project_closure_checklists_project_key_ux" ON "project_closure_checklist_items"("project_id", "item_key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "idx_project_closure_checklists_project_status" ON "project_closure_checklist_items"("project_id", "status", "sort_order");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "idx_project_closure_checklists_completed" ON "project_closure_checklist_items"("completed_by", "completed_at");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "project_communication_logs" ADD CONSTRAINT "project_communication_logs_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "project_communication_logs" ADD CONSTRAINT "project_communication_logs_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "project_closure_checklist_items" ADD CONSTRAINT "project_closure_checklist_items_project_id_fkey" FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "project_closure_checklist_items" ADD CONSTRAINT "project_closure_checklist_items_completed_by_fkey" FOREIGN KEY ("completed_by") REFERENCES "employees"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|