209 lines
7.1 KiB
SQL
209 lines
7.1 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "SplitType" AS ENUM ('EQUAL', 'PERCENTAGE', 'EXACT', 'SHARE', 'ADJUSTMENT', 'SETTLEMENT');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Account" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"providerAccountId" TEXT NOT NULL,
|
|
"refresh_token" TEXT,
|
|
"access_token" TEXT,
|
|
"expires_at" INTEGER,
|
|
"token_type" TEXT,
|
|
"scope" TEXT,
|
|
"id_token" TEXT,
|
|
"session_state" TEXT,
|
|
|
|
CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Session" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionToken" TEXT NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"expires" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" SERIAL NOT NULL,
|
|
"name" TEXT,
|
|
"email" TEXT,
|
|
"emailVerified" TIMESTAMP(3),
|
|
"image" TEXT,
|
|
"currency" TEXT NOT NULL DEFAULT 'USD',
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "VerificationToken" (
|
|
"identifier" TEXT NOT NULL,
|
|
"token" TEXT NOT NULL,
|
|
"expires" TIMESTAMP(3) NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Balance" (
|
|
"userId" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL,
|
|
"friendId" INTEGER NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Balance_pkey" PRIMARY KEY ("userId","currency","friendId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Group" (
|
|
"id" SERIAL NOT NULL,
|
|
"publicId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"defaultCurrency" TEXT NOT NULL DEFAULT 'USD',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Group_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "GroupUser" (
|
|
"groupId" INTEGER NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "GroupUser_pkey" PRIMARY KEY ("groupId","userId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "GroupBalance" (
|
|
"groupId" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"firendId" INTEGER NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "GroupBalance_pkey" PRIMARY KEY ("groupId","currency","firendId","userId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Expense" (
|
|
"id" TEXT NOT NULL,
|
|
"paidBy" INTEGER NOT NULL,
|
|
"addedBy" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"category" TEXT NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"splitType" "SplitType" NOT NULL DEFAULT 'EQUAL',
|
|
"expenseDate" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"currency" TEXT NOT NULL,
|
|
"fileKey" TEXT,
|
|
"groupId" INTEGER,
|
|
|
|
CONSTRAINT "Expense_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ExpenseParticipant" (
|
|
"expenseId" TEXT NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "ExpenseParticipant_pkey" PRIMARY KEY ("expenseId","userId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ExpenseNote" (
|
|
"id" TEXT NOT NULL,
|
|
"expenseId" TEXT NOT NULL,
|
|
"note" TEXT NOT NULL,
|
|
"createdById" INTEGER NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ExpenseNote_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Group_publicId_key" ON "Group"("publicId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Expense_groupId_idx" ON "Expense"("groupId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Expense_paidBy_idx" ON "Expense"("paidBy");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Balance" ADD CONSTRAINT "Balance_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Balance" ADD CONSTRAINT "Balance_friendId_fkey" FOREIGN KEY ("friendId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Group" ADD CONSTRAINT "Group_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GroupUser" ADD CONSTRAINT "GroupUser_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GroupUser" ADD CONSTRAINT "GroupUser_groupId_fkey" FOREIGN KEY ("groupId") REFERENCES "Group"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GroupBalance" ADD CONSTRAINT "GroupBalance_groupId_fkey" FOREIGN KEY ("groupId") REFERENCES "Group"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GroupBalance" ADD CONSTRAINT "GroupBalance_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GroupBalance" ADD CONSTRAINT "GroupBalance_firendId_fkey" FOREIGN KEY ("firendId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Expense" ADD CONSTRAINT "Expense_groupId_fkey" FOREIGN KEY ("groupId") REFERENCES "Group"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Expense" ADD CONSTRAINT "Expense_paidBy_fkey" FOREIGN KEY ("paidBy") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Expense" ADD CONSTRAINT "Expense_addedBy_fkey" FOREIGN KEY ("addedBy") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ExpenseParticipant" ADD CONSTRAINT "ExpenseParticipant_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ExpenseParticipant" ADD CONSTRAINT "ExpenseParticipant_expenseId_fkey" FOREIGN KEY ("expenseId") REFERENCES "Expense"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ExpenseNote" ADD CONSTRAINT "ExpenseNote_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ExpenseNote" ADD CONSTRAINT "ExpenseNote_expenseId_fkey" FOREIGN KEY ("expenseId") REFERENCES "Expense"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|