117 lines
3.5 KiB
SQL
117 lines
3.5 KiB
SQL
-- CreateSchema
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tenant" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Tenant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"fullName" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"refreshTokenId" TEXT,
|
|
"refreshTokenHash" TEXT,
|
|
"refreshTokenExpiresAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Role" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Role_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Permission" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Permission_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UserRole" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"roleId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "UserRole_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RolePermission" (
|
|
"id" TEXT NOT NULL,
|
|
"roleId" TEXT NOT NULL,
|
|
"permissionId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "RolePermission_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Tenant_slug_key" ON "Tenant"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_refreshTokenId_key" ON "User"("refreshTokenId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_tenantId_idx" ON "User"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_tenantId_email_key" ON "User"("tenantId", "email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Role_tenantId_idx" ON "Role"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Role_tenantId_name_key" ON "Role"("tenantId", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Permission_key_key" ON "Permission"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "UserRole_userId_roleId_key" ON "UserRole"("userId", "roleId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "RolePermission_roleId_permissionId_key" ON "RolePermission"("roleId", "permissionId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Role" ADD CONSTRAINT "Role_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "UserRole" ADD CONSTRAINT "UserRole_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "UserRole" ADD CONSTRAINT "UserRole_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RolePermission" ADD CONSTRAINT "RolePermission_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RolePermission" ADD CONSTRAINT "RolePermission_permissionId_fkey" FOREIGN KEY ("permissionId") REFERENCES "Permission"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|