bot-hermes
639306a26f
Phase 1 of the Plane feature lift into Project E. Schema changes: - Add stateGroupEnum, moduleStatusEnum, linkTypeEnum - Add states table (per-project workflow states with group enum) - Add modules table (project-scoped planning buckets) - Add cycles table (time-boxed sprints) - Add links table (canonical cross-entity mesh) - Drop taskStatusEnum and tasks.status column - Add stateId, moduleId, cycleId FKs to tasks - Drop taskDependencies, noteLinks, noteEntityLinks tables Project creation bootstrap: - Seed 5 default states (Backlog/Todo/In Progress/Done/Cancelled) on new project Minimal API fixes for typecheck: - Remove references to dropped tables/columns - Replace status-based queries with completedAt checks - Stub deprecated dependency/status endpoints for Phase 2 Drizzle migration: 0008_plane-lift-schema.sql (custom, big-bang)
82 lines
5.1 KiB
SQL
82 lines
5.1 KiB
SQL
-- Plane-Lift schema: big-bang break (DB is empty, no data migration)
|
|
-- Adds states, modules, cycles, links tables.
|
|
-- Drops note_links, note_entity_links, task_dependencies.
|
|
-- Drops tasks.status enum column, adds state_id/module_id/cycle_id FKs.
|
|
-- Drops the task_status enum type.
|
|
|
|
-- ── Drop old junction tables ────────────────────────────────────────────────────
|
|
DROP TABLE IF EXISTS "task_dependencies" CASCADE;--> statement-breakpoint
|
|
DROP TABLE IF EXISTS "note_links" CASCADE;--> statement-breakpoint
|
|
DROP TABLE IF EXISTS "note_entity_links" CASCADE;--> statement-breakpoint
|
|
|
|
-- ── Create new enums ───────────────────────────────────────────────────────────
|
|
CREATE TYPE "state_group" AS ENUM ('backlog', 'unstarted', 'started', 'completed', 'cancelled');--> statement-breakpoint
|
|
CREATE TYPE "module_status" AS ENUM ('planned', 'in_progress', 'completed', 'cancelled');--> statement-breakpoint
|
|
CREATE TYPE "link_type" AS ENUM ('relates', 'blocks', 'parent-child', 'created-from');--> statement-breakpoint
|
|
|
|
-- ── Create new tables ──────────────────────────────────────────────────────────
|
|
CREATE TABLE "states" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"color" text,
|
|
"group" "state_group" NOT NULL DEFAULT 'unstarted',
|
|
"project_id" uuid NOT NULL,
|
|
"sort_order" integer DEFAULT 0,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE INDEX "states_project_id_idx" ON "states" ("project_id");--> statement-breakpoint
|
|
CREATE INDEX "states_sort_order_idx" ON "states" ("project_id","sort_order");--> statement-breakpoint
|
|
CREATE TABLE "modules" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text,
|
|
"project_id" uuid NOT NULL,
|
|
"status" "module_status" NOT NULL DEFAULT 'planned',
|
|
"start_date" timestamp with time zone,
|
|
"target_date" timestamp with time zone,
|
|
"sort_order" integer DEFAULT 0,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE INDEX "modules_project_id_idx" ON "modules" ("project_id");--> statement-breakpoint
|
|
CREATE TABLE "cycles" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"project_id" uuid NOT NULL,
|
|
"start_date" timestamp with time zone,
|
|
"end_date" timestamp with time zone,
|
|
"active" boolean DEFAULT false,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE INDEX "cycles_project_id_idx" ON "cycles" ("project_id");--> statement-breakpoint
|
|
CREATE TABLE "links" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"source_type" text NOT NULL,
|
|
"source_id" uuid NOT NULL,
|
|
"target_type" text NOT NULL,
|
|
"target_id" uuid NOT NULL,
|
|
"link_type" "link_type" NOT NULL,
|
|
"direction" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE INDEX "links_source_idx" ON "links" ("source_type","source_id");--> statement-breakpoint
|
|
CREATE INDEX "links_target_idx" ON "links" ("target_type","target_id");--> statement-breakpoint
|
|
|
|
-- ── Modify tasks table ─────────────────────────────────────────────────────────
|
|
ALTER TABLE "tasks" ADD COLUMN "state_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "tasks" ADD COLUMN "module_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "tasks" ADD COLUMN "cycle_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_state_id_states_id_fk" FOREIGN KEY ("state_id") REFERENCES "states"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_module_id_modules_id_fk" FOREIGN KEY ("module_id") REFERENCES "modules"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_cycle_id_cycles_id_fk" FOREIGN KEY ("cycle_id") REFERENCES "cycles"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "tasks_state_id_idx" ON "tasks" ("state_id");--> statement-breakpoint
|
|
CREATE INDEX "tasks_module_id_idx" ON "tasks" ("module_id");--> statement-breakpoint
|
|
CREATE INDEX "tasks_cycle_id_idx" ON "tasks" ("cycle_id");--> statement-breakpoint
|
|
ALTER TABLE "tasks" DROP COLUMN "status";--> statement-breakpoint
|
|
DROP INDEX IF EXISTS "tasks_status_idx";--> statement-breakpoint
|
|
|
|
-- ── Drop old enum type ─────────────────────────────────────────────────────────
|
|
DROP TYPE IF EXISTS "task_status" CASCADE;
|