202 lines
6.6 KiB
SQL
202 lines
6.6 KiB
SQL
|
|
-- CreateEnum
|
|
CREATE TYPE "GroupClaimStatus" AS ENUM ('PENDING_CLAIM', 'CLAIMED', 'RELEASED', 'EXPIRED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ConsentScope" AS ENUM ('INGEST', 'ARCHIVE', 'REPLICATE', 'DISPLAY');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ConsentStatus" AS ENUM ('GRANTED', 'REVOKED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MemberOptOutReason" AS ENUM ('STOP_KEYWORD', 'SELF_PORTAL', 'ADMIN_ACTION');
|
|
|
|
-- AlterEnum
|
|
ALTER TYPE "AccountStatus" ADD VALUE 'PAIRING';
|
|
|
|
-- AlterEnum
|
|
ALTER TYPE "ActorType" ADD VALUE 'MEMBER';
|
|
|
|
-- DropForeignKey
|
|
ALTER TABLE "Account" DROP CONSTRAINT "Account_tenantId_fkey";
|
|
|
|
-- DropForeignKey
|
|
ALTER TABLE "Group" DROP CONSTRAINT "Group_tenantId_fkey";
|
|
|
|
-- DropIndex
|
|
DROP INDEX "Account_tenantId_idx";
|
|
|
|
-- DropIndex
|
|
DROP INDEX "ConsentRecord_groupId_memberJid_consentType_key";
|
|
|
|
-- DropIndex
|
|
DROP INDEX "ConsentRecord_tenantId_idx";
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "Account" DROP COLUMN "tenantId",
|
|
ADD COLUMN "isBot" BOOLEAN NOT NULL DEFAULT true,
|
|
ADD COLUMN "pairingExpiresAt" TIMESTAMP(3),
|
|
ADD COLUMN "pairingToken" TEXT;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "ConsentRecord" DROP COLUMN "consentType",
|
|
DROP COLUMN "grantedAt",
|
|
DROP COLUMN "memberJid",
|
|
ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
ADD COLUMN "effectiveAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
ADD COLUMN "policyVersion" TEXT NOT NULL,
|
|
ADD COLUMN "proofEventId" TEXT NOT NULL,
|
|
ADD COLUMN "retentionDays" INTEGER NOT NULL DEFAULT 90,
|
|
ADD COLUMN "scopes" "ConsentScope"[],
|
|
ADD COLUMN "status" "ConsentStatus" NOT NULL DEFAULT 'GRANTED',
|
|
ADD COLUMN "userId" TEXT NOT NULL;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "Group" ADD COLUMN "claimExpiresAt" TIMESTAMP(3),
|
|
ADD COLUMN "claimStatus" "GroupClaimStatus" NOT NULL DEFAULT 'PENDING_CLAIM',
|
|
ADD COLUMN "claimedAt" TIMESTAMP(3),
|
|
ADD COLUMN "claimedByAdminId" TEXT,
|
|
ALTER COLUMN "tenantId" DROP NOT NULL;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "Message" ADD COLUMN "senderTowerUserId" TEXT;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TenantBot" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "TenantBot_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TowerUser" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"phoneHash" TEXT NOT NULL,
|
|
"jid" TEXT NOT NULL,
|
|
"displayName" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "TowerUser_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TowerSession" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "TowerSession_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MemberOptOut" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"groupId" TEXT,
|
|
"reason" "MemberOptOutReason" NOT NULL,
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MemberOptOut_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TenantBot_accountId_idx" ON "TenantBot"("accountId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TenantBot_tenantId_accountId_key" ON "TenantBot"("tenantId", "accountId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TowerUser_phoneHash_idx" ON "TowerUser"("phoneHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TowerUser_tenantId_idx" ON "TowerUser"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TowerUser_jid_idx" ON "TowerUser"("jid");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TowerUser_tenantId_phoneHash_key" ON "TowerUser"("tenantId", "phoneHash");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TowerSession_tokenHash_key" ON "TowerSession"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TowerSession_userId_idx" ON "TowerSession"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TowerSession_expiresAt_idx" ON "TowerSession"("expiresAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MemberOptOut_tenantId_userId_idx" ON "MemberOptOut"("tenantId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MemberOptOut_groupId_idx" ON "MemberOptOut"("groupId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MemberOptOut_userId_idx" ON "MemberOptOut"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Account_pairingToken_key" ON "Account"("pairingToken");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Account_isBot_idx" ON "Account"("isBot");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Account_status_idx" ON "Account"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ConsentRecord_tenantId_groupId_userId_idx" ON "ConsentRecord"("tenantId", "groupId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ConsentRecord_status_idx" ON "ConsentRecord"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ConsentRecord_userId_idx" ON "ConsentRecord"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Group_claimStatus_idx" ON "Group"("claimStatus");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Message_senderTowerUserId_idx" ON "Message"("senderTowerUserId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TenantBot" ADD CONSTRAINT "TenantBot_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TenantBot" ADD CONSTRAINT "TenantBot_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "Account"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Group" ADD CONSTRAINT "Group_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Group" ADD CONSTRAINT "Group_claimedByAdminId_fkey" FOREIGN KEY ("claimedByAdminId") REFERENCES "Admin"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Message" ADD CONSTRAINT "Message_senderTowerUserId_fkey" FOREIGN KEY ("senderTowerUserId") REFERENCES "TowerUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TowerUser" ADD CONSTRAINT "TowerUser_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TowerSession" ADD CONSTRAINT "TowerSession_userId_fkey" FOREIGN KEY ("userId") REFERENCES "TowerUser"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ConsentRecord" ADD CONSTRAINT "ConsentRecord_userId_fkey" FOREIGN KEY ("userId") REFERENCES "TowerUser"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MemberOptOut" ADD CONSTRAINT "MemberOptOut_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MemberOptOut" ADD CONSTRAINT "MemberOptOut_userId_fkey" FOREIGN KEY ("userId") REFERENCES "TowerUser"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|