Blame · Line-by-line history

`0094_enterprise_sso.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.

0094_enterprise_sso.sqlBlame47 lines · 1 contributor

Normal view Raw

9c888c3Claude	1	-- Migration 0077: Enterprise SSO (SAML 2.0 + OIDC per-org) and SCIM user provisioning
	2	-- Org-level SSO configs (distinct from site-wide sso_config / Block I10)
	3
	4	CREATE TABLE IF NOT EXISTS org_sso_configs (
	5	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	6	org_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
	7	provider text NOT NULL DEFAULT 'saml', -- 'saml' \| 'oidc'
	8	-- SAML fields
	9	idp_entity_id text,
	10	idp_sso_url text,
	11	idp_certificate text, -- PEM cert from IdP
	12	sp_entity_id text, -- our entity ID (computed)
	13	-- OIDC fields
	14	oidc_client_id text,
	15	oidc_client_secret text,
	16	oidc_discovery_url text,
	17	-- Common
	18	domain_hint text, -- e.g. "acme.com" — auto-routes users from this domain to SSO
	19	attribute_mapping jsonb DEFAULT '{"email":"email","name":"name","username":"preferred_username"}',
	20	enabled boolean NOT NULL DEFAULT false,
	21	created_at timestamp DEFAULT now(),
	22	updated_at timestamp DEFAULT now(),
	23	UNIQUE(org_id)
	24	);
	25
	26	CREATE INDEX IF NOT EXISTS org_sso_configs_domain ON org_sso_configs(domain_hint) WHERE domain_hint IS NOT NULL;
	27
	28	CREATE TABLE IF NOT EXISTS scim_tokens (
	29	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	30	org_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
	31	token_hash text NOT NULL UNIQUE, -- SHA-256 of the token
	32	created_by uuid NOT NULL REFERENCES users(id),
	33	created_at timestamp DEFAULT now(),
	34	last_used_at timestamp
	35	);
	36
	37	CREATE TABLE IF NOT EXISTS org_sso_sessions (
	38	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	39	user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	40	org_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
	41	idp_session_id text,
	42	created_at timestamp DEFAULT now(),
	43	expires_at timestamp NOT NULL
	44	);
	45
	46	CREATE INDEX IF NOT EXISTS org_sso_sessions_user ON org_sso_sessions(user_id);
	47	CREATE INDEX IF NOT EXISTS org_sso_sessions_expires ON org_sso_sessions(expires_at);