6a6e74562c
- Add goals feature (models, migrations, API, web pages) - Add reserved/centralized wallet balance service - Add wallet detail page and overview components - Add new UI components (progress, multi-select, FAB) - Remove stray empty -H/-d files from working tree
88 lines
2.9 KiB
SQL
Executable File
88 lines
2.9 KiB
SQL
Executable File
-- CreateTable
|
|
CREATE TABLE "public"."Goal" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"teamId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"targetAmount" DECIMAL(18,2) NOT NULL,
|
|
"currentAmount" DECIMAL(18,2) NOT NULL DEFAULT 0,
|
|
"currency" TEXT NOT NULL DEFAULT 'IDR',
|
|
"targetDate" TIMESTAMP(3),
|
|
"imageUrl" TEXT,
|
|
"category" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"completedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Goal_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."GoalAllocation" (
|
|
"id" TEXT NOT NULL,
|
|
"goalId" TEXT NOT NULL,
|
|
"walletId" TEXT NOT NULL,
|
|
"amount" DECIMAL(18,2) NOT NULL,
|
|
"currency" TEXT NOT NULL,
|
|
"exchangeRate" DECIMAL(18,6),
|
|
"amountInGoalCurrency" DECIMAL(18,2) NOT NULL,
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdBy" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "GoalAllocation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."GoalMilestone" (
|
|
"id" TEXT NOT NULL,
|
|
"goalId" TEXT NOT NULL,
|
|
"percentage" INTEGER NOT NULL,
|
|
"targetAmount" DECIMAL(18,2) NOT NULL,
|
|
"achievedAt" TIMESTAMP(3),
|
|
"notifiedAt" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "GoalMilestone_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Goal_userId_idx" ON "public"."Goal"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Goal_status_idx" ON "public"."Goal"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Goal_teamId_idx" ON "public"."Goal"("teamId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GoalAllocation_goalId_idx" ON "public"."GoalAllocation"("goalId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GoalAllocation_walletId_idx" ON "public"."GoalAllocation"("walletId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GoalAllocation_createdAt_idx" ON "public"."GoalAllocation"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GoalMilestone_goalId_idx" ON "public"."GoalMilestone"("goalId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GoalMilestone_achievedAt_idx" ON "public"."GoalMilestone"("achievedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "GoalMilestone_goalId_percentage_key" ON "public"."GoalMilestone"("goalId", "percentage");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."Goal" ADD CONSTRAINT "Goal_userId_fkey" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."GoalAllocation" ADD CONSTRAINT "GoalAllocation_goalId_fkey" FOREIGN KEY ("goalId") REFERENCES "public"."Goal"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."GoalAllocation" ADD CONSTRAINT "GoalAllocation_walletId_fkey" FOREIGN KEY ("walletId") REFERENCES "public"."Wallet"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."GoalMilestone" ADD CONSTRAINT "GoalMilestone_goalId_fkey" FOREIGN KEY ("goalId") REFERENCES "public"."Goal"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|