Index

public.oauth_device_codes

Description

Pending OAuth 2.0 Device Authorization Grants (RFC 8628). Device code stored hashed; single-use redemption enforced in application code. No deleted_at: rows are hard-deleted on redemption or expiry instead of soft-deleted.

Columns

Name Type Default Nullable Children Parents Comment
client_id uuid false public.oauth_clients Client that initiated the device authorization request.
created_at timestamp with time zone now() false Creation timestamp.
device_code_digest bytea false HMAC digest of the one-time device_code (hashed at rest).
expires_at timestamp with time zone (now() + ‘00:15:00’::interval) false Expiration time for the device code (short-lived; capped at 30 minutes).
interval_seconds integer 5 false Minimum polling interval (seconds) advertised to the client.
jti uuid gen_random_uuid() false Unique identifier for logging/trace.
last_polled_at timestamp with time zone true Timestamp of the previous poll; used to detect too-fast polling (slow_down).
metadata jsonb ‘{}’::jsonb false Free-form JSON for diagnostics (device/ip, etc.).
scopes text[] ‘{}’::text[] false Scopes requested for this device authorization.
status device_code_status ‘pending’::device_code_status false Approval lifecycle: pending, approved, or denied.
updated_at timestamp with time zone now() false Last update timestamp (maintained by trigger).
user_code text false Human-typed code shown on the device (Crockford base32, XXXX-XXXX). Unique among pending rows.
user_id uuid true public.users End-user who approved the grant; NULL until approved.

Constraints

Name Type Definition
device_code_approved_has_user CHECK CHECK (((status <> ‘approved’::device_code_status) OR (user_id IS NOT NULL)))
device_code_expiry_ceiling CHECK CHECK ((expires_at <= (created_at + ‘00:30:00’::interval)))
device_code_interval_positive CHECK CHECK ((interval_seconds > 0))
device_code_user_code_shape CHECK CHECK ((user_code ~ ‘1{4}-[0-9A-HJKMNP-TV-Z]{4}$’::text))
oauth_device_codes_client_id_fkey FOREIGN KEY FOREIGN KEY (client_id) REFERENCES oauth_clients(id) ON DELETE CASCADE
oauth_device_codes_client_id_not_null n NOT NULL client_id
oauth_device_codes_created_at_not_null n NOT NULL created_at
oauth_device_codes_device_code_digest_not_null n NOT NULL device_code_digest
oauth_device_codes_expires_at_not_null n NOT NULL expires_at
oauth_device_codes_interval_seconds_not_null n NOT NULL interval_seconds
oauth_device_codes_jti_not_null n NOT NULL jti
oauth_device_codes_metadata_not_null n NOT NULL metadata
oauth_device_codes_pkey PRIMARY KEY PRIMARY KEY (device_code_digest)
oauth_device_codes_scopes_not_null n NOT NULL scopes
oauth_device_codes_status_not_null n NOT NULL status
oauth_device_codes_updated_at_not_null n NOT NULL updated_at
oauth_device_codes_user_code_not_null n NOT NULL user_code
oauth_device_codes_user_id_fkey FOREIGN KEY FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
scopes_token_chars CHECK CHECK (scopes_all_valid(scopes))

Indexes

Name Definition Comment
idx_oauth_device_codes_client CREATE INDEX idx_oauth_device_codes_client ON public.oauth_device_codes USING btree (client_id) Speeds per-client device code lookups.
idx_oauth_device_codes_expires_at CREATE INDEX idx_oauth_device_codes_expires_at ON public.oauth_device_codes USING btree (expires_at) Speeds eviction/lookup of expiring device codes.
oauth_device_codes_pkey CREATE UNIQUE INDEX oauth_device_codes_pkey ON public.oauth_device_codes USING btree (device_code_digest)
uq_oauth_device_codes_user_code_pending CREATE UNIQUE INDEX uq_oauth_device_codes_user_code_pending ON public.oauth_device_codes USING btree (user_code) WHERE (status = ‘pending’::device_code_status) Ensures user_code is unique among pending device authorization grants.

Triggers

Name Definition
set_timestamp CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.oauth_device_codes FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp()

Relations

er

Generated by tbls

  1. 0-9A-HJKMNP-TV-Z↩︎