124 lines
4.8 KiB
SQL
124 lines
4.8 KiB
SQL
CREATE TABLE IF NOT EXISTS "MediaAsset" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"userId" uuid NOT NULL,
|
|
"chatId" uuid,
|
|
"projectId" uuid,
|
|
"mediaType" varchar(32) NOT NULL,
|
|
"sourceType" varchar(32) NOT NULL,
|
|
"status" varchar(32) DEFAULT 'ready' NOT NULL,
|
|
"originalUrl" text NOT NULL,
|
|
"normalizedUrl" text NOT NULL,
|
|
"previewUrl" text,
|
|
"thumbnailUrl" text,
|
|
"mimeType" varchar(128),
|
|
"width" integer,
|
|
"height" integer,
|
|
"durationMs" integer,
|
|
"displayName" varchar(256),
|
|
"prompt" text,
|
|
"modelId" varchar(128),
|
|
"sourceMessageId" uuid,
|
|
"sourceTable" varchar(64),
|
|
"sourceRowId" varchar(128),
|
|
"sourceNodeId" varchar(128),
|
|
"rootAssetId" uuid,
|
|
"primaryParentAssetId" uuid,
|
|
"summary" jsonb,
|
|
"metadata" jsonb,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "MediaAssetRelation" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"userId" uuid NOT NULL,
|
|
"chatId" uuid,
|
|
"projectId" uuid,
|
|
"fromAssetId" uuid NOT NULL,
|
|
"toAssetId" uuid NOT NULL,
|
|
"relationType" varchar(64) NOT NULL,
|
|
"sourceMessageId" uuid,
|
|
"sourceNodeId" varchar(128),
|
|
"confidence" integer DEFAULT 100 NOT NULL,
|
|
"metadata" jsonb,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAsset" ADD CONSTRAINT "MediaAsset_userId_User_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAsset" ADD CONSTRAINT "MediaAsset_chatId_Chat_id_fk" FOREIGN KEY ("chatId") REFERENCES "public"."Chat"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAsset" ADD CONSTRAINT "MediaAsset_projectId_CanvasProject_id_fk" FOREIGN KEY ("projectId") REFERENCES "public"."CanvasProject"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAsset" ADD CONSTRAINT "MediaAsset_sourceMessageId_Message_v2_id_fk" FOREIGN KEY ("sourceMessageId") REFERENCES "public"."Message_v2"("id") ON DELETE set null ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_userId_User_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_chatId_Chat_id_fk" FOREIGN KEY ("chatId") REFERENCES "public"."Chat"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_projectId_CanvasProject_id_fk" FOREIGN KEY ("projectId") REFERENCES "public"."CanvasProject"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_fromAssetId_MediaAsset_id_fk" FOREIGN KEY ("fromAssetId") REFERENCES "public"."MediaAsset"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_toAssetId_MediaAsset_id_fk" FOREIGN KEY ("toAssetId") REFERENCES "public"."MediaAsset"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "MediaAssetRelation" ADD CONSTRAINT "MediaAssetRelation_sourceMessageId_Message_v2_id_fk" FOREIGN KEY ("sourceMessageId") REFERENCES "public"."Message_v2"("id") ON DELETE set null ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "MediaAsset_user_normalized_uidx"
|
|
ON "MediaAsset"("userId", "normalizedUrl");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "MediaAsset_chat_created_idx"
|
|
ON "MediaAsset"("chatId", "createdAt");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "MediaAsset_project_created_idx"
|
|
ON "MediaAsset"("projectId", "createdAt");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "MediaAsset_source_message_idx"
|
|
ON "MediaAsset"("sourceMessageId");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "MediaAssetRelation_project_type_idx"
|
|
ON "MediaAssetRelation"("projectId", "relationType");
|
|
--> statement-breakpoint
|
|
CREATE INDEX IF NOT EXISTS "MediaAssetRelation_message_type_idx"
|
|
ON "MediaAssetRelation"("sourceMessageId", "relationType");
|