0050_project_storage_grants.sql31 lines · main

1	-- 0050_project_storage_grants — M5 S3 sprint (cross-project storage sharing).
2	--
3	-- A GRANTER project explicitly shares one file (by file id) OR a whole path
4	-- prefix with a GRANTEE project. The grantee can then mint a download URL for
5	-- exactly the granted resource — nothing else. This is the ONLY sanctioned
6	-- exception to strict cross-project storage isolation.
7	--
8	-- Strict-deny by construction: access is allowed ONLY when a matching row
9	-- exists with revoked_at IS NULL. Revoke sets revoked_at (never deletes) so the
10	-- row can be re-activated on a fresh grant and the unique index stays intact.
11	-- Lands on the control DB (Postgres). Additive + idempotent.
12	CREATE TABLE IF NOT EXISTS "project_storage_grants" (
13	"id" text PRIMARY KEY NOT NULL,
14	"granter_project_id" text NOT NULL,
15	"grantee_project_id" text NOT NULL,
16	"resource" text NOT NULL,
17	"is_prefix" boolean DEFAULT false NOT NULL,
18	"created_by" text,
19	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
20	"revoked_at" timestamp with time zone
21	);
22	--> statement-breakpoint
23	DO $$ BEGIN
24	ALTER TABLE "project_storage_grants" ADD CONSTRAINT "project_storage_grants_granter_project_id_projects_id_fk" FOREIGN KEY ("granter_project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;
25	EXCEPTION
26	WHEN duplicate_object THEN null;
27	END $$;
28	--> statement-breakpoint
29	CREATE UNIQUE INDEX IF NOT EXISTS "project_storage_grants_unique_idx" ON "project_storage_grants" ("granter_project_id","grantee_project_id","resource");
30	--> statement-breakpoint
31	CREATE INDEX IF NOT EXISTS "project_storage_grants_grantee_idx" ON "project_storage_grants" ("grantee_project_id");