0019_project_files.sql32 lines · main

1	-- 0019_project_files — metadata for S3-compatible object storage.
2	-- The actual object bytes live in MinIO under `projects/<projectId>/<fileId>`.
3	-- The storage service is the only path that mints presigned PUT/GET URLs
4	-- against that prefix, so the unique index on object_key catches any
5	-- code path that tries to register two rows for the same object.
6
7	CREATE TABLE IF NOT EXISTS "project_files" (
8	"id" text PRIMARY KEY NOT NULL,
9	"project_id" text NOT NULL,
10	"name" text NOT NULL,
11	"object_key" text NOT NULL,
12	"content_type" text NOT NULL,
13	"size_bytes" text NOT NULL,
14	"checksum_sha256" text,
15	"uploaded_by" text,
16	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
17	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
18	"deleted_at" timestamp with time zone,
19	CONSTRAINT "project_files_project_id_fk"
20	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE,
21	CONSTRAINT "project_files_uploaded_by_fk"
22	FOREIGN KEY ("uploaded_by") REFERENCES "users"("id") ON DELETE SET NULL
23	);
24
25	-- Per-project list query: hot path is "list non-deleted files for a project".
26	CREATE INDEX IF NOT EXISTS "project_files_project_idx"
27	ON "project_files" USING btree ("project_id")
28	WHERE "deleted_at" IS NULL;
29
30	-- Object keys are unique across the whole bucket.
31	CREATE UNIQUE INDEX IF NOT EXISTS "project_files_object_key_idx"
32	ON "project_files" USING btree ("object_key");