0051_project_storage_share_links.sql31 lines · main

1	-- 0051_project_storage_share_links — M5 S3 sprint (tokenized public share-links).
2	--
3	-- The public-link half of M5. A file OWNER mints a signed public link — a URL
4	-- carrying a cryptographically-random token that ANYONE can open for a LIMITED
5	-- TIME, with NO project/auth needed — and can revoke it at will. Unlike a grant
6	-- (project→project), this exposes ONE file to the open internet.
7	--
8	-- Strict-deny by construction: a link resolves to its file ONLY when a row
9	-- matches the exact token AND revoked_at IS NULL AND expires_at > now(). Revoke
10	-- sets revoked_at (never deletes). The token is the only bearer credential and
11	-- is never logged. Lands on the control DB (Postgres). Additive + idempotent.
12	CREATE TABLE IF NOT EXISTS "project_storage_share_links" (
13	"id" text PRIMARY KEY NOT NULL,
14	"project_id" text NOT NULL,
15	"file_id" text NOT NULL,
16	"token" text NOT NULL,
17	"expires_at" timestamp with time zone 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_share_links" ADD CONSTRAINT "project_storage_share_links_project_id_projects_id_fk" FOREIGN KEY ("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_share_links_token_idx" ON "project_storage_share_links" ("token");
30	--> statement-breakpoint
31	CREATE INDEX IF NOT EXISTS "project_storage_share_links_project_idx" ON "project_storage_share_links" ("project_id");