prisma/migrations/202610180004_ticket_events/migration.sql

BEGIN;
-- CreateEnum
CREATE TYPE "TicketEventKind" AS ENUM ('CREATED', 'STATUS_CHANGED', 'ASSIGNED', 'UNASSIGNED', 'REPLIED', 'ASSIGNEE_RELEASED', 'HISTORY_STARTED');

-- CreateTable
CREATE TABLE "TicketEvent" (
    "id" UUID NOT NULL,
    "ticketId" UUID NOT NULL,
    "teamId" UUID NOT NULL,
    "version" INTEGER NOT NULL,
    "kind" "TicketEventKind" NOT NULL,
    "actorUserId" TEXT,
    "actorName" TEXT NOT NULL,
    "actorRole" "MemberRole",
    "fromStatus" "TicketStatus",
    "toStatus" "TicketStatus",
    "fromAssigneeName" TEXT,
    "toAssigneeName" TEXT,
    "reason" VARCHAR(1000),
    "replyId" UUID,
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "TicketEvent_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "TicketEvent_teamId_ticketId_version_idx" ON "TicketEvent"("teamId", "ticketId", "version");

-- CreateIndex
CREATE UNIQUE INDEX "TicketEvent_ticketId_version_key" ON "TicketEvent"("ticketId", "version");

-- AddForeignKey
ALTER TABLE "TicketEvent" ADD CONSTRAINT "TicketEvent_ticketId_teamId_fkey" FOREIGN KEY ("ticketId", "teamId") REFERENCES "Ticket"("id", "teamId") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "TicketEvent" ADD CONSTRAINT "TicketEvent_actorUserId_fkey" FOREIGN KEY ("actorUserId") REFERENCES "user"("id") ON DELETE SET NULL ON UPDATE CASCADE;


-- 仅记录上线时的现状，不虚构旧工单曾经经历的操作。后续版本从此快照继续追加。
INSERT INTO "TicketEvent" ("id", "ticketId", "teamId", "version", "kind", "actorName", "toStatus", "toAssigneeName", "reason")
SELECT gen_random_uuid(), t."id", t."teamId", t."version", 'HISTORY_STARTED', '系统', t."status", u."name", '操作记录功能启用时的状态快照，之前的操作未追溯'
FROM "Ticket" t
LEFT JOIN "Membership" m ON m."id" = t."assigneeMembershipId"
LEFT JOIN "user" u ON u."id" = m."userId";

COMMIT;