Blame · Line-by-line history

`0007_oauth_provider.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.

0007_oauth_provider.sqlBlame78 lines · 1 contributor

Normal view Raw

bfdb5e7Claude	1	-- Gluecron migration 0007: Block B6 — OAuth 2.0 provider.
	2	--
	3	-- Tables:
	4	-- oauth_apps — third-party apps registered by developers
	5	-- oauth_authorizations — short-lived authorization codes (single-use)
	6	-- oauth_access_tokens — long-lived bearer tokens + refresh tokens
	7	--
	8	-- All secrets / code / token values are stored as SHA-256 hex hashes.
	9	-- Only the plaintext `clientSecret` is shown to the developer once at
	10	-- creation; the plaintext auth code and tokens are returned to the client
	11	-- in the OAuth response and never persisted.
	12
	13	--> statement-breakpoint
	14	CREATE TABLE IF NOT EXISTS "oauth_apps" (
	15	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	16	"owner_id" uuid NOT NULL,
	17	"name" text NOT NULL,
	18	"client_id" text NOT NULL UNIQUE,
	19	"client_secret_hash" text NOT NULL,
	20	"client_secret_prefix" text NOT NULL,
	21	"redirect_uris" text NOT NULL,
	22	"homepage_url" text,
	23	"description" text,
	24	"confidential" boolean DEFAULT true NOT NULL,
	25	"revoked_at" timestamp,
	26	"created_at" timestamp DEFAULT now() NOT NULL,
	27	"updated_at" timestamp DEFAULT now() NOT NULL,
	28	CONSTRAINT "oauth_apps_owner_fk" FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE cascade
	29	);
	30
	31	--> statement-breakpoint
	32	CREATE INDEX IF NOT EXISTS "oauth_apps_owner" ON "oauth_apps" ("owner_id");
	33
	34	--> statement-breakpoint
	35	CREATE TABLE IF NOT EXISTS "oauth_authorizations" (
	36	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	37	"app_id" uuid NOT NULL,
	38	"user_id" uuid NOT NULL,
	39	"code_hash" text NOT NULL UNIQUE,
	40	"redirect_uri" text NOT NULL,
	41	"scopes" text NOT NULL DEFAULT '',
	42	"code_challenge" text,
	43	"code_challenge_method" text,
	44	"expires_at" timestamp NOT NULL,
	45	"used_at" timestamp,
	46	"created_at" timestamp DEFAULT now() NOT NULL,
	47	CONSTRAINT "oauth_authorizations_app_fk" FOREIGN KEY ("app_id") REFERENCES "oauth_apps"("id") ON DELETE cascade,
	48	CONSTRAINT "oauth_authorizations_user_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade
	49	);
	50
	51	--> statement-breakpoint
	52	CREATE INDEX IF NOT EXISTS "oauth_authorizations_expires" ON "oauth_authorizations" ("expires_at");
	53
	54	--> statement-breakpoint
	55	CREATE TABLE IF NOT EXISTS "oauth_access_tokens" (
	56	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	57	"app_id" uuid NOT NULL,
	58	"user_id" uuid NOT NULL,
	59	"access_token_hash" text NOT NULL UNIQUE,
	60	"refresh_token_hash" text UNIQUE,
	61	"scopes" text NOT NULL DEFAULT '',
	62	"expires_at" timestamp NOT NULL,
	63	"refresh_expires_at" timestamp,
	64	"revoked_at" timestamp,
	65	"last_used_at" timestamp,
	66	"created_at" timestamp DEFAULT now() NOT NULL,
	67	CONSTRAINT "oauth_access_tokens_app_fk" FOREIGN KEY ("app_id") REFERENCES "oauth_apps"("id") ON DELETE cascade,
	68	CONSTRAINT "oauth_access_tokens_user_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade
	69	);
	70
	71	--> statement-breakpoint
	72	CREATE INDEX IF NOT EXISTS "oauth_access_tokens_user" ON "oauth_access_tokens" ("user_id");
	73
	74	--> statement-breakpoint
	75	CREATE INDEX IF NOT EXISTS "oauth_access_tokens_app" ON "oauth_access_tokens" ("app_id");
	76
	77	--> statement-breakpoint
	78	CREATE INDEX IF NOT EXISTS "oauth_access_tokens_expires" ON "oauth_access_tokens" ("expires_at");