381 lines
16 KiB
SQL
381 lines
16 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "SubscriptionPlan" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"priceMonthly" DECIMAL NOT NULL,
|
|
"messageQuota" INTEGER NOT NULL,
|
|
"seatQuota" INTEGER NOT NULL,
|
|
"broadcastQuota" INTEGER NOT NULL,
|
|
"featuresJson" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tenant" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"companyName" TEXT NOT NULL,
|
|
"timezone" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'TRIAL',
|
|
"planId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Tenant_planId_fkey" FOREIGN KEY ("planId") REFERENCES "SubscriptionPlan" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Role" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"permissionsJson" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Role_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"fullName" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"roleId" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'INVITED',
|
|
"avatarUrl" TEXT,
|
|
"lastLoginAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "User_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "User_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Channel" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelName" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"wabaId" TEXT,
|
|
"phoneNumberId" TEXT,
|
|
"displayPhoneNumber" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"webhookStatus" TEXT,
|
|
"lastSyncAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Channel_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Contact" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelId" TEXT,
|
|
"externalRef" TEXT,
|
|
"fullName" TEXT NOT NULL,
|
|
"phoneNumber" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"avatarUrl" TEXT,
|
|
"countryCode" TEXT,
|
|
"optInStatus" TEXT NOT NULL DEFAULT 'UNKNOWN',
|
|
"lastInteractionAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Contact_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Contact_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tag" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"color" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Tag_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ContactTag" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"contactId" TEXT NOT NULL,
|
|
"tagId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "ContactTag_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "Contact" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ContactTag_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "Tag" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Conversation" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"contactId" TEXT NOT NULL,
|
|
"subject" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'OPEN',
|
|
"priority" TEXT NOT NULL DEFAULT 'NORMAL',
|
|
"assignedUserId" TEXT,
|
|
"firstMessageAt" TIMESTAMP(3),
|
|
"lastMessageAt" TIMESTAMP(3),
|
|
"lastInboundAt" TIMESTAMP(3),
|
|
"lastOutboundAt" TIMESTAMP(3),
|
|
"resolvedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Conversation_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Conversation_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Conversation_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "Contact" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Conversation_assignedUserId_fkey" FOREIGN KEY ("assignedUserId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Message" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"conversationId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"contactId" TEXT,
|
|
"direction" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL DEFAULT 'TEXT',
|
|
"providerMessageId" TEXT,
|
|
"replyToMessageId" TEXT,
|
|
"contentText" TEXT,
|
|
"mediaUrl" TEXT,
|
|
"mimeType" TEXT,
|
|
"deliveryStatus" TEXT NOT NULL DEFAULT 'QUEUED',
|
|
"failedReason" TEXT,
|
|
"sentByUserId" TEXT,
|
|
"sentAt" TIMESTAMP(3),
|
|
"deliveredAt" TIMESTAMP(3),
|
|
"readAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "Message_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Message_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Message_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Message_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "Contact" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT "Message_sentByUserId_fkey" FOREIGN KEY ("sentByUserId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ConversationNote" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"conversationId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ConversationNote_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ConversationNote_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ConversationNote_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ConversationTag" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"conversationId" TEXT NOT NULL,
|
|
"tagId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "ConversationTag_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ConversationTag_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "Tag" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ConversationActivity" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"conversationId" TEXT NOT NULL,
|
|
"actorUserId" TEXT,
|
|
"activityType" TEXT NOT NULL,
|
|
"metadataJson" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "ConversationActivity_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ConversationActivity_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "ConversationActivity_actorUserId_fkey" FOREIGN KEY ("actorUserId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ContactSegment" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"rulesJson" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ContactSegment_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SegmentMember" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"segmentId" TEXT NOT NULL,
|
|
"contactId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "SegmentMember_segmentId_fkey" FOREIGN KEY ("segmentId") REFERENCES "ContactSegment" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "SegmentMember_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "Contact" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MessageTemplate" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"category" TEXT NOT NULL,
|
|
"languageCode" TEXT NOT NULL,
|
|
"templateType" TEXT,
|
|
"bodyText" TEXT NOT NULL,
|
|
"headerType" TEXT,
|
|
"footerText" TEXT,
|
|
"buttonsJson" JSONB,
|
|
"providerTemplateId" TEXT,
|
|
"approvalStatus" TEXT NOT NULL DEFAULT 'DRAFT',
|
|
"rejectedReason" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "MessageTemplate_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "MessageTemplate_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BroadcastCampaign" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"templateId" TEXT NOT NULL,
|
|
"createdByUserId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"campaignType" TEXT NOT NULL DEFAULT 'BROADCAST',
|
|
"audienceType" TEXT NOT NULL,
|
|
"segmentId" TEXT,
|
|
"scheduledAt" TIMESTAMP(3),
|
|
"startedAt" TIMESTAMP(3),
|
|
"finishedAt" TIMESTAMP(3),
|
|
"status" TEXT NOT NULL DEFAULT 'DRAFT',
|
|
"totalRecipients" INTEGER NOT NULL DEFAULT 0,
|
|
"totalSent" INTEGER NOT NULL DEFAULT 0,
|
|
"totalDelivered" INTEGER NOT NULL DEFAULT 0,
|
|
"totalRead" INTEGER NOT NULL DEFAULT 0,
|
|
"totalFailed" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "BroadcastCampaign_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BroadcastCampaign_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BroadcastCampaign_templateId_fkey" FOREIGN KEY ("templateId") REFERENCES "MessageTemplate" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BroadcastCampaign_createdByUserId_fkey" FOREIGN KEY ("createdByUserId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BroadcastCampaign_segmentId_fkey" FOREIGN KEY ("segmentId") REFERENCES "ContactSegment" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CampaignRecipient" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"campaignId" TEXT NOT NULL,
|
|
"contactId" TEXT NOT NULL,
|
|
"phoneNumber" TEXT NOT NULL,
|
|
"sendStatus" TEXT NOT NULL DEFAULT 'QUEUED',
|
|
"failureReason" TEXT,
|
|
"providerMessageId" TEXT,
|
|
"sentAt" TIMESTAMP(3),
|
|
"deliveredAt" TIMESTAMP(3),
|
|
"readAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "CampaignRecipient_campaignId_fkey" FOREIGN KEY ("campaignId") REFERENCES "BroadcastCampaign" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "CampaignRecipient_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "Contact" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AuditLog" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"actorUserId" TEXT,
|
|
"entityType" TEXT NOT NULL,
|
|
"entityId" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"metadataJson" JSONB,
|
|
"ipAddress" TEXT,
|
|
"userAgent" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "AuditLog_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "AuditLog_actorUserId_fkey" FOREIGN KEY ("actorUserId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WebhookEvent" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"channelId" TEXT,
|
|
"eventType" TEXT NOT NULL,
|
|
"providerEventId" TEXT,
|
|
"payloadJson" JSONB NOT NULL,
|
|
"processStatus" TEXT NOT NULL,
|
|
"failedReason" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"processedAt" TIMESTAMP(3),
|
|
CONSTRAINT "WebhookEvent_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "WebhookEvent_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "Channel" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UsageMetric" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"metricDate" TIMESTAMP(3) NOT NULL,
|
|
"inboundMessages" INTEGER NOT NULL DEFAULT 0,
|
|
"outboundMessages" INTEGER NOT NULL DEFAULT 0,
|
|
"activeContacts" INTEGER NOT NULL DEFAULT 0,
|
|
"activeAgents" INTEGER NOT NULL DEFAULT 0,
|
|
"broadcastSent" INTEGER NOT NULL DEFAULT 0,
|
|
"storageUsedMb" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "UsageMetric_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BillingInvoice" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"tenantId" TEXT NOT NULL,
|
|
"planId" TEXT NOT NULL,
|
|
"invoiceNumber" TEXT NOT NULL,
|
|
"periodStart" TIMESTAMP(3) NOT NULL,
|
|
"periodEnd" TIMESTAMP(3) NOT NULL,
|
|
"subtotal" DECIMAL NOT NULL,
|
|
"taxAmount" DECIMAL NOT NULL,
|
|
"totalAmount" DECIMAL NOT NULL,
|
|
"paymentStatus" TEXT NOT NULL DEFAULT 'UNPAID',
|
|
"dueDate" TIMESTAMP(3) NOT NULL,
|
|
"paidAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "BillingInvoice_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BillingInvoice_planId_fkey" FOREIGN KEY ("planId") REFERENCES "SubscriptionPlan" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "SubscriptionPlan_code_key" ON "SubscriptionPlan"("code");
|
|
CREATE UNIQUE INDEX "Tenant_slug_key" ON "Tenant"("slug");
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
CREATE UNIQUE INDEX "Contact_tenantId_phoneNumber_key" ON "Contact"("tenantId", "phoneNumber");
|
|
CREATE UNIQUE INDEX "Tag_tenantId_name_key" ON "Tag"("tenantId", "name");
|
|
CREATE UNIQUE INDEX "ContactTag_contactId_tagId_key" ON "ContactTag"("contactId", "tagId");
|
|
CREATE INDEX "Conversation_tenantId_status_idx" ON "Conversation"("tenantId", "status");
|
|
CREATE INDEX "Conversation_tenantId_assignedUserId_idx" ON "Conversation"("tenantId", "assignedUserId");
|
|
CREATE INDEX "Conversation_tenantId_lastMessageAt_idx" ON "Conversation"("tenantId", "lastMessageAt");
|
|
CREATE UNIQUE INDEX "Message_providerMessageId_key" ON "Message"("providerMessageId");
|
|
CREATE UNIQUE INDEX "ConversationTag_conversationId_tagId_key" ON "ConversationTag"("conversationId", "tagId");
|
|
CREATE UNIQUE INDEX "SegmentMember_segmentId_contactId_key" ON "SegmentMember"("segmentId", "contactId");
|
|
CREATE UNIQUE INDEX "UsageMetric_tenantId_metricDate_key" ON "UsageMetric"("tenantId", "metricDate");
|
|
CREATE UNIQUE INDEX "BillingInvoice_invoiceNumber_key" ON "BillingInvoice"("invoiceNumber");
|