121 lines
4.2 KiB
SQL
121 lines
4.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "HouseRole" AS ENUM ('OWNER', 'ADMIN', 'MEMBER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "InvitationStatus" AS ENUM ('PENDING', 'ACCEPTED', 'REJECTED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RequestStatus" AS ENUM ('PENDING', 'ACCEPTED', 'REJECTED', 'CANCELLED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "House" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"creatorId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "House_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HouseMembership" (
|
|
"id" TEXT NOT NULL,
|
|
"houseId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"role" "HouseRole" NOT NULL DEFAULT 'MEMBER',
|
|
"joinedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "HouseMembership_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HouseInvitation" (
|
|
"id" TEXT NOT NULL,
|
|
"houseId" TEXT NOT NULL,
|
|
"inviterId" TEXT NOT NULL,
|
|
"inviteeId" TEXT NOT NULL,
|
|
"status" "InvitationStatus" NOT NULL DEFAULT 'PENDING',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "HouseInvitation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HouseRequest" (
|
|
"id" TEXT NOT NULL,
|
|
"houseId" TEXT NOT NULL,
|
|
"requesterId" TEXT NOT NULL,
|
|
"status" "RequestStatus" NOT NULL DEFAULT 'PENDING',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "HouseRequest_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "House_creatorId_idx" ON "House"("creatorId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "House_name_idx" ON "House"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "HouseMembership_houseId_userId_key" ON "HouseMembership"("houseId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseMembership_houseId_idx" ON "HouseMembership"("houseId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseMembership_userId_idx" ON "HouseMembership"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "HouseInvitation_houseId_inviteeId_key" ON "HouseInvitation"("houseId", "inviteeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseInvitation_houseId_idx" ON "HouseInvitation"("houseId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseInvitation_inviteeId_idx" ON "HouseInvitation"("inviteeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseInvitation_status_idx" ON "HouseInvitation"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "HouseRequest_houseId_requesterId_key" ON "HouseRequest"("houseId", "requesterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseRequest_houseId_idx" ON "HouseRequest"("houseId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseRequest_requesterId_idx" ON "HouseRequest"("requesterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "HouseRequest_status_idx" ON "HouseRequest"("status");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "House" ADD CONSTRAINT "House_creatorId_fkey" FOREIGN KEY ("creatorId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseMembership" ADD CONSTRAINT "HouseMembership_houseId_fkey" FOREIGN KEY ("houseId") REFERENCES "House"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseMembership" ADD CONSTRAINT "HouseMembership_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseInvitation" ADD CONSTRAINT "HouseInvitation_houseId_fkey" FOREIGN KEY ("houseId") REFERENCES "House"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseInvitation" ADD CONSTRAINT "HouseInvitation_inviterId_fkey" FOREIGN KEY ("inviterId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseInvitation" ADD CONSTRAINT "HouseInvitation_inviteeId_fkey" FOREIGN KEY ("inviteeId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseRequest" ADD CONSTRAINT "HouseRequest_houseId_fkey" FOREIGN KEY ("houseId") REFERENCES "House"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HouseRequest" ADD CONSTRAINT "HouseRequest_requesterId_fkey" FOREIGN KEY ("requesterId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|