168 lines
11 KiB
MySQL
168 lines
11 KiB
MySQL
|
CREATE TABLE "object_assignees" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"object_id" uuid NOT NULL,
|
||
|
|
"user_id" uuid NOT NULL,
|
||
|
|
"role" varchar(50) DEFAULT 'assignee' NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "objects" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"type" varchar(50) NOT NULL,
|
||
|
|
"parent_id" uuid,
|
||
|
|
"title" varchar(500) DEFAULT '' NOT NULL,
|
||
|
|
"icon" text,
|
||
|
|
"cover_image" text,
|
||
|
|
"description" text,
|
||
|
|
"content" jsonb,
|
||
|
|
"status" varchar(50),
|
||
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
||
|
|
"template_id" uuid,
|
||
|
|
"workspace_id" uuid,
|
||
|
|
"created_by" uuid,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"archived_at" timestamp with time zone
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "workspace_members" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"workspace_id" uuid NOT NULL,
|
||
|
|
"user_id" uuid NOT NULL,
|
||
|
|
"role" varchar(50) DEFAULT 'member' NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "property_definitions" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"workspace_id" uuid NOT NULL,
|
||
|
|
"name" varchar(255) NOT NULL,
|
||
|
|
"field_type" varchar(50) NOT NULL,
|
||
|
|
"config" jsonb,
|
||
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "property_values" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"object_id" uuid NOT NULL,
|
||
|
|
"property_def_id" uuid NOT NULL,
|
||
|
|
"value" jsonb,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "views" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"object_id" uuid NOT NULL,
|
||
|
|
"view_type" varchar(50) NOT NULL,
|
||
|
|
"config" jsonb,
|
||
|
|
"name" varchar(255) NOT NULL,
|
||
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "accounts" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"user_id" uuid NOT NULL,
|
||
|
|
"type" varchar(255) NOT NULL,
|
||
|
|
"provider" varchar(255) NOT NULL,
|
||
|
|
"provider_account_id" varchar(255) NOT NULL,
|
||
|
|
"refresh_token" text,
|
||
|
|
"access_token" text,
|
||
|
|
"expires_at" integer,
|
||
|
|
"token_type" varchar(255),
|
||
|
|
"scope" varchar(255),
|
||
|
|
"id_token" text,
|
||
|
|
"session_state" varchar(255)
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "sessions" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"session_token" varchar(255) NOT NULL,
|
||
|
|
"user_id" uuid NOT NULL,
|
||
|
|
"expires" timestamp with time zone NOT NULL,
|
||
|
|
CONSTRAINT "sessions_session_token_unique" UNIQUE("session_token")
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "users" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"email" varchar(255) NOT NULL,
|
||
|
|
"name" varchar(255),
|
||
|
|
"avatar_url" text,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
CONSTRAINT "users_email_unique" UNIQUE("email")
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "verification_tokens" (
|
||
|
|
"identifier" varchar(255) NOT NULL,
|
||
|
|
"token" varchar(255) NOT NULL,
|
||
|
|
"expires" timestamp with time zone NOT NULL,
|
||
|
|
CONSTRAINT "verification_tokens_identifier_token_pk" PRIMARY KEY("identifier","token")
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "object_relations" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"source_id" uuid NOT NULL,
|
||
|
|
"target_id" uuid NOT NULL,
|
||
|
|
"relation_type" varchar(50) NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "templates" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"workspace_id" uuid NOT NULL,
|
||
|
|
"name" varchar(255) NOT NULL,
|
||
|
|
"target_type" varchar(50) NOT NULL,
|
||
|
|
"schema" jsonb,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
ALTER TABLE "object_assignees" ADD CONSTRAINT "object_assignees_object_id_objects_id_fk" FOREIGN KEY ("object_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "object_assignees" ADD CONSTRAINT "object_assignees_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "objects" ADD CONSTRAINT "objects_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "objects" ADD CONSTRAINT "objects_parent_id_objects_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."objects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "objects" ADD CONSTRAINT "objects_workspace_id_objects_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "objects" ADD CONSTRAINT "objects_template_id_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."templates"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_workspace_id_objects_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "property_definitions" ADD CONSTRAINT "property_definitions_workspace_id_objects_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "property_values" ADD CONSTRAINT "property_values_object_id_objects_id_fk" FOREIGN KEY ("object_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "property_values" ADD CONSTRAINT "property_values_property_def_id_property_definitions_id_fk" FOREIGN KEY ("property_def_id") REFERENCES "public"."property_definitions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "views" ADD CONSTRAINT "views_object_id_objects_id_fk" FOREIGN KEY ("object_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "object_relations" ADD CONSTRAINT "object_relations_source_id_objects_id_fk" FOREIGN KEY ("source_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "object_relations" ADD CONSTRAINT "object_relations_target_id_objects_id_fk" FOREIGN KEY ("target_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "templates" ADD CONSTRAINT "templates_workspace_id_objects_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "object_assignees_object_id_user_id_unique" ON "object_assignees" USING btree ("object_id","user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "object_assignees_object_id_idx" ON "object_assignees" USING btree ("object_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "object_assignees_user_id_idx" ON "object_assignees" USING btree ("user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_parent_id_idx" ON "objects" USING btree ("parent_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_type_idx" ON "objects" USING btree ("type");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_workspace_id_idx" ON "objects" USING btree ("workspace_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_template_id_idx" ON "objects" USING btree ("template_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_created_by_idx" ON "objects" USING btree ("created_by");--> statement-breakpoint
|
||
|
|
CREATE INDEX "objects_type_workspace_id_idx" ON "objects" USING btree ("type","workspace_id");--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "workspace_members_workspace_id_user_id_unique" ON "workspace_members" USING btree ("workspace_id","user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "workspace_members_workspace_id_idx" ON "workspace_members" USING btree ("workspace_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "workspace_members_user_id_idx" ON "workspace_members" USING btree ("user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "property_definitions_workspace_id_idx" ON "property_definitions" USING btree ("workspace_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "property_definitions_workspace_id_name_idx" ON "property_definitions" USING btree ("workspace_id","name");--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "property_values_object_id_property_def_id_unique" ON "property_values" USING btree ("object_id","property_def_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "property_values_object_id_idx" ON "property_values" USING btree ("object_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "property_values_property_def_id_idx" ON "property_values" USING btree ("property_def_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "views_object_id_idx" ON "views" USING btree ("object_id");--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "accounts_provider_provider_account_id_unique" ON "accounts" USING btree ("provider","provider_account_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "accounts_user_id_idx" ON "accounts" USING btree ("user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "sessions_user_id_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "users_email_idx" ON "users" USING btree ("email");--> statement-breakpoint
|
||
|
|
CREATE INDEX "object_relations_source_id_idx" ON "object_relations" USING btree ("source_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "object_relations_target_id_idx" ON "object_relations" USING btree ("target_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "object_relations_relation_type_idx" ON "object_relations" USING btree ("relation_type");--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "object_relations_source_target_type_unique" ON "object_relations" USING btree ("source_id","target_id","relation_type");--> statement-breakpoint
|
||
|
|
CREATE INDEX "templates_workspace_id_idx" ON "templates" USING btree ("workspace_id");
|