82 lines
2.6 KiB
SQL
82 lines
2.6 KiB
SQL
CREATE TYPE "Role" AS ENUM ('ADMIN', 'FINANCE', 'MEMBER');
|
|
CREATE TYPE "ApprovalType" AS ENUM ('CHAIR_A', 'CHAIR_B', 'FINANCE');
|
|
CREATE TYPE "ApprovalStatus" AS ENUM ('PENDING', 'APPROVED');
|
|
|
|
CREATE TABLE "working_groups" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"total_budget" DECIMAL(10,2) NOT NULL,
|
|
"color_code" TEXT NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "working_groups_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "users" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password_hash" TEXT NOT NULL,
|
|
"role" "Role" NOT NULL,
|
|
"approval_preference" "ApprovalType",
|
|
"working_group_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "expenses" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"amount" DECIMAL(10,2) NOT NULL,
|
|
"creator_id" TEXT NOT NULL,
|
|
"ag_id" TEXT NOT NULL,
|
|
"approval_status" "ApprovalStatus" NOT NULL DEFAULT 'PENDING',
|
|
"paid_at" TIMESTAMP(3),
|
|
"documented_at" TIMESTAMP(3),
|
|
"proof_url" TEXT,
|
|
"notes" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "expenses_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "approvals" (
|
|
"id" TEXT NOT NULL,
|
|
"expense_id" TEXT NOT NULL,
|
|
"user_id" TEXT NOT NULL,
|
|
"approval_type" "ApprovalType" NOT NULL,
|
|
"timestamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "approvals_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "working_groups_name_key" ON "working_groups"("name");
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
CREATE UNIQUE INDEX "approvals_expense_id_approval_type_key" ON "approvals"("expense_id", "approval_type");
|
|
|
|
ALTER TABLE "users"
|
|
ADD CONSTRAINT "users_working_group_id_fkey"
|
|
FOREIGN KEY ("working_group_id") REFERENCES "working_groups"("id")
|
|
ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "expenses"
|
|
ADD CONSTRAINT "expenses_creator_id_fkey"
|
|
FOREIGN KEY ("creator_id") REFERENCES "users"("id")
|
|
ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "expenses"
|
|
ADD CONSTRAINT "expenses_ag_id_fkey"
|
|
FOREIGN KEY ("ag_id") REFERENCES "working_groups"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "approvals"
|
|
ADD CONSTRAINT "approvals_expense_id_fkey"
|
|
FOREIGN KEY ("expense_id") REFERENCES "expenses"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "approvals"
|
|
ADD CONSTRAINT "approvals_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|