44 lines
1.7 KiB
SQL
44 lines
1.7 KiB
SQL
-- DropForeignKey
|
|
-- Category.userId is becoming optional -- a category now belongs to
|
|
-- exactly one of a User (Home) or a Project, never both.
|
|
ALTER TABLE "Category" DROP CONSTRAINT "Category_userId_fkey";
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "Category" ALTER COLUMN "userId" DROP NOT NULL;
|
|
ALTER TABLE "Category" ADD COLUMN "projectId" TEXT;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Project" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"ownerId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Project_ownerId_createdAt_idx" ON "Project"("ownerId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Category_projectId_order_idx" ON "Category"("projectId", "order");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Category" ADD CONSTRAINT "Category_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Category" ADD CONSTRAINT "Category_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Project" ADD CONSTRAINT "Project_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- Every existing Category row already has userId set and projectId null,
|
|
-- so this holds immediately with no backfill -- enforce the "exactly one
|
|
-- of userId/projectId" invariant at the database level too, not just in
|
|
-- application code (lib/access.ts).
|
|
ALTER TABLE "Category" ADD CONSTRAINT "Category_owner_xor_project" CHECK (
|
|
("userId" IS NOT NULL AND "projectId" IS NULL) OR
|
|
("userId" IS NULL AND "projectId" IS NOT NULL)
|
|
);
|