75 lines
2.2 KiB
SQL
75 lines
2.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "AuthorityType" AS ENUM ('Root', 'User', 'RolePolicy');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "KeyPairType" AS ENUM ('UserToken', 'Custom');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Project" (
|
|
"id" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"parentProject" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
|
|
CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"hash" TEXT NOT NULL,
|
|
"rolePolicyId" TEXT NOT NULL,
|
|
"projectId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RolePolicy" (
|
|
"id" TEXT NOT NULL,
|
|
"projectId" TEXT NOT NULL,
|
|
"authority" TEXT NOT NULL,
|
|
"authorityType" "AuthorityType" NOT NULL DEFAULT 'RolePolicy',
|
|
"name" TEXT NOT NULL,
|
|
"policies" TEXT[],
|
|
|
|
CONSTRAINT "RolePolicy_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "KeyPair" (
|
|
"id" TEXT NOT NULL,
|
|
"projectId" TEXT NOT NULL,
|
|
"usage" "KeyPairType" NOT NULL,
|
|
"name" TEXT,
|
|
"encryptedPrivateKey" TEXT NOT NULL,
|
|
"encryptedPublicKey" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "KeyPair_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Project_slug_key" ON "Project"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_id_key" ON "User"("id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_projectId_username_key" ON "User"("projectId", "username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_projectId_email_key" ON "User"("projectId", "email");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_rolePolicyId_fkey" FOREIGN KEY ("rolePolicyId") REFERENCES "RolePolicy"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RolePolicy" ADD CONSTRAINT "RolePolicy_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "KeyPair" ADD CONSTRAINT "KeyPair_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|