39 lines
1.6 KiB
SQL
39 lines
1.6 KiB
SQL
-- Moon Base v0.3 — recurring tasks / reminders
|
|
|
|
CREATE TYPE "TaskCategory" AS ENUM ('GARDEN', 'HOME', 'EQUIPMENT', 'OTHER');
|
|
CREATE TYPE "RecurrenceType" AS ENUM ('ONCE', 'CUSTOM', 'MONTHLY', 'YEARLY');
|
|
|
|
CREATE TABLE "Task" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"notes" TEXT,
|
|
"category" "TaskCategory" NOT NULL DEFAULT 'GARDEN',
|
|
"plantId" TEXT,
|
|
-- Recurrence
|
|
"recurrence" "RecurrenceType" NOT NULL DEFAULT 'YEARLY',
|
|
"intervalDays" INTEGER, -- for CUSTOM: every N days
|
|
"month" INTEGER, -- 1-12: for YEARLY (which month) or MONTHLY (ignored)
|
|
-- Scheduling
|
|
"nextDue" TIMESTAMP(3) NOT NULL,
|
|
"lastDone" TIMESTAMP(3),
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Task_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "Task_plantId_fkey" FOREIGN KEY ("plantId") REFERENCES "Plant"("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
CREATE INDEX "Task_nextDue_idx" ON "Task"("nextDue");
|
|
CREATE INDEX "Task_plantId_idx" ON "Task"("plantId");
|
|
CREATE INDEX "Task_active_idx" ON "Task"("active");
|
|
|
|
CREATE TABLE "TaskCompletion" (
|
|
"id" TEXT NOT NULL,
|
|
"taskId" TEXT NOT NULL,
|
|
"doneAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"notes" TEXT,
|
|
"actorId" TEXT,
|
|
CONSTRAINT "TaskCompletion_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "TaskCompletion_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "Task"("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
CREATE INDEX "TaskCompletion_taskId_idx" ON "TaskCompletion"("taskId");
|