Blame · Line-by-line history

`0027_sso_oidc.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0027_sso_oidc.sqlBlame40 lines · 1 contributor

edf7c36Claude	1	-- Gluecron migration 0027: OIDC-based enterprise SSO.
	2	--
	3	-- I10 — A single site-wide OIDC provider (Okta / Azure AD / Auth0 / Google
	4	-- Workspace). Identified by an `id = 'default'` singleton row. Admin fills
	5	-- in issuer + endpoint URLs + client credentials. Users then see a
	6	-- "Sign in with SSO" button on /login.
	7	--
	8	-- `sso_user_links` maps a local user to the provider's `sub` claim, so
	9	-- repeat sign-ins find the existing account.
	10
	11	--> statement-breakpoint
	12	CREATE TABLE IF NOT EXISTS "sso_config" (
	13	"id" text PRIMARY KEY,
	14	"enabled" boolean NOT NULL DEFAULT false,
	15	"provider_name" text NOT NULL DEFAULT 'SSO',
	16	"issuer" text,
	17	"authorization_endpoint" text,
	18	"token_endpoint" text,
	19	"userinfo_endpoint" text,
	20	"client_id" text,
	21	"client_secret" text,
	22	"scopes" text NOT NULL DEFAULT 'openid profile email',
	23	"allowed_email_domains" text, -- comma-separated, null = any
	24	"auto_create_users" boolean NOT NULL DEFAULT true,
	25	"created_at" timestamp NOT NULL DEFAULT now(),
	26	"updated_at" timestamp NOT NULL DEFAULT now()
	27	);
	28
	29	--> statement-breakpoint
	30	CREATE TABLE IF NOT EXISTS "sso_user_links" (
	31	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	32	"user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	33	"subject" text NOT NULL UNIQUE,
	34	"email_at_link" text NOT NULL,
	35	"linked_at" timestamp NOT NULL DEFAULT now()
	36	);
	37
	38	--> statement-breakpoint
	39	CREATE INDEX IF NOT EXISTS "sso_user_links_user_id_idx"
	40	ON "sso_user_links" ("user_id");