0016_org_invitations.sql26 lines · main

1	-- org_invitations — pending invites to a team org. Mirrors the
2	-- project_invitations table shape but scoped to an org id and
3	-- carrying an org role (owner/admin/developer/viewer) instead of
4	-- a project-member role.
5
6	CREATE TABLE IF NOT EXISTS "org_invitations" (
7	"id" text PRIMARY KEY NOT NULL,
8	"org_id" text NOT NULL REFERENCES "organizations"("id") ON DELETE CASCADE,
9	"email" text NOT NULL,
10	"role" text NOT NULL DEFAULT 'developer',
11	"token_hash" text NOT NULL,
12	"invited_by" text REFERENCES "users"("id"),
13	"expires_at" timestamp with time zone NOT NULL,
14	"accepted_at" timestamp with time zone,
15	"revoked_at" timestamp with time zone,
16	"created_at" timestamp with time zone DEFAULT now() NOT NULL
17	);
18
19	-- One pending invite per (org, email) at a time. A second invite to the
20	-- same address replaces the prior one (handled at the service layer with
21	-- an INSERT … ON CONFLICT … DO UPDATE).
22	CREATE UNIQUE INDEX IF NOT EXISTS "org_invitations_org_email_idx"
23	ON "org_invitations" USING btree ("org_id", "email");
24
25	CREATE UNIQUE INDEX IF NOT EXISTS "org_invitations_token_idx"
26	ON "org_invitations" USING btree ("token_hash");