b0562a0a12
- Added TicketStatus, TicketPriority, TicketSource enums to schema - Added TicketCategory model (tenant-scoped, unique per tenant by name) - Added Ticket model with lifecycle fields (status, priority, source, resolvedAt, closedAt) - Added reverse relations: Subscriber.tickets, User.createdTickets, TicketCategory.tickets - Applied migration 20260304233528_add_tickets - Added ticket and ticketCategory to TENANT_SCOPED_MODELS with full extension blocks - Seeded 6 default ISP ticket categories in createTenant transaction
74 lines
2.4 KiB
SQL
74 lines
2.4 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "TicketStatus" AS ENUM ('OPEN', 'ASSIGNED', 'RESOLVED', 'CLOSED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TicketPriority" AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'URGENT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TicketSource" AS ENUM ('STAFF', 'SUBSCRIBER');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TicketCategory" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "TicketCategory_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Ticket" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"ticketNumber" TEXT NOT NULL,
|
|
"subject" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"categoryId" TEXT NOT NULL,
|
|
"priority" "TicketPriority" NOT NULL DEFAULT 'MEDIUM',
|
|
"status" "TicketStatus" NOT NULL DEFAULT 'OPEN',
|
|
"source" "TicketSource" NOT NULL DEFAULT 'STAFF',
|
|
"subscriberId" TEXT,
|
|
"createdById" TEXT NOT NULL,
|
|
"resolvedAt" TIMESTAMP(3),
|
|
"closedAt" TIMESTAMP(3),
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Ticket_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TicketCategory_tenantId_idx" ON "TicketCategory"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TicketCategory_tenantId_name_key" ON "TicketCategory"("tenantId", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Ticket_tenantId_idx" ON "Ticket"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Ticket_tenantId_status_idx" ON "Ticket"("tenantId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Ticket_tenantId_categoryId_idx" ON "Ticket"("tenantId", "categoryId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Ticket_subscriberId_idx" ON "Ticket"("subscriberId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Ticket_tenantId_ticketNumber_key" ON "Ticket"("tenantId", "ticketNumber");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Ticket" ADD CONSTRAINT "Ticket_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "TicketCategory"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Ticket" ADD CONSTRAINT "Ticket_subscriberId_fkey" FOREIGN KEY ("subscriberId") REFERENCES "Subscriber"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Ticket" ADD CONSTRAINT "Ticket_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|