Token Functions¶
Functions for creating, validating, consuming, and expiring short-lived authentication tokens, plus CRUD operations for token type configuration. Tokens are used for email verification, password resets, one-time links, and similar workflows where a time-limited credential is issued and then consumed.
Source: 025_functions_auth_token.sql | Additional sources: 018_functions_public.sql, 019_functions_unsecure.sql
See also: API Key Functions for long-lived service authentication.
Create¶
auth.create_token¶
Creates a new token for a target user. Automatically invalidates all previous valid tokens of the same type for the same user. If the token value already exists and is valid for the same type, raises error 52276 (token already used). Expires any overdue tokens as a side effect.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor creating the token |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_target_user_id |
bigint |
-- | User ID the token is issued for |
_target_user_oid |
text |
-- | External OID of the target user (e.g. AzureAD object ID) |
_user_event_id |
integer |
-- | Related user event ID for audit linkage |
_token_type_code |
text |
-- | Token type code (must exist in const.token_type) |
_token_channel_code |
text |
-- | Delivery channel code (must exist in const.token_channel) |
_token |
text |
-- | The token value (e.g. a generated code or hash) |
_expires_at |
timestamptz |
null |
Explicit expiration timestamp; when null, uses the default_expiration_in_seconds from the token type |
_token_data |
jsonb |
null |
Optional arbitrary JSON payload stored with the token |
Returns: table(___token_id bigint, ___token_uid text, ___expires_at timestamptz)
Permission required: tokens.create_token
Source: 025_functions_auth_token.sql:13
Automatic invalidation
When _target_user_id is not null, all previous valid tokens of the same type for that user are set to 'invalid' before the new token is inserted. This ensures only one active token of each type per user.
Validate¶
auth.validate_token¶
Validates a token by UID, token value, or both. Checks that the token exists, is in 'valid' state, and belongs to the expected user (when _target_user_id is provided). Optionally marks the token as used in a single call. Expires any overdue tokens as a side effect.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_target_user_id |
bigint |
-- | Expected owner of the token; pass null to skip user ownership check |
_token_uid |
text |
-- | The system-generated UID of the token (12-char random string) |
_token |
text |
-- | The token value |
_token_type_code |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context (IP, user-agent, etc.) stored for audit |
_set_as_used |
boolean |
false |
When true, marks the token as used after successful validation |
Returns: table(___token_id bigint, ___token_uid text, ___token_state_code text, ___used_at timestamptz, ___user_id bigint, ___user_oid text, ___token_data jsonb)
Permission required: tokens.validate_token
Source: 025_functions_auth_token.sql:253
Errors raised:
| Error | Condition |
|---|---|
| 52277 | Token not found |
| 52278 | Token is not in 'valid' state (expired, used, or failed) |
| 52279 | Token belongs to a different user than _target_user_id |
public.validate_token¶
Public-schema wrapper for token validation. Functionally identical to auth.validate_token but defined in the public schema. Expires overdue tokens before performing validation (unlike the auth version which expires them after).
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_target_user_id |
bigint |
-- | Expected owner of the token; pass null to skip user ownership check |
_token_uid |
text |
-- | The system-generated UID of the token |
_token |
text |
-- | The token value |
_token_type |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context stored for audit |
_set_as_used |
boolean |
false |
When true, marks the token as used after successful validation |
Returns: table(___token_id bigint, ___token_uid text, ___token_state_code text, ___used_at timestamptz, ___user_id bigint, ___user_oid text, ___token_data jsonb)
Permission required: tokens.validate_token
Source: 018_functions_public.sql:367
Consume (Set as Used)¶
auth.set_token_as_used¶
Marks a valid token as used. Looks up the token by both UID and token value for the given type, then sets token_state_code to 'used' and records the current timestamp in used_at.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_token_uid |
text |
-- | The system-generated UID of the token |
_token |
text |
-- | The token value |
_token_type_code |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context stored for audit |
Returns: table(__token_id bigint, __token_uid text, __token_state_code text, __used_at timestamptz, __user_id bigint, __user_oid text, __token_data jsonb)
Permission required: tokens.set_as_used
Source: 025_functions_auth_token.sql:94
auth.set_token_as_used_by_token¶
Convenience wrapper that looks up the token UID from the token value and type, then delegates to auth.set_token_as_used. Use this when you only have the token value, not the UID.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_token |
text |
-- | The token value |
_token_type |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context stored for audit |
Returns: table(__token_id bigint, __token_uid text, __token_state_code text, __used_at timestamptz, __user_id bigint, __user_oid text, __token_data jsonb)
Permission required: tokens.set_as_used (delegated via auth.set_token_as_used)
Source: 025_functions_auth_token.sql:145
Mark as Failed¶
auth.set_token_as_failed¶
Marks a valid token as failed validation. Sets token_state_code to 'validation_failed' and records the current timestamp in used_at. Used when a token is presented but fails business-level validation (e.g. wrong context, tampered payload).
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_token_uid |
text |
-- | The system-generated UID of the token |
_token |
text |
-- | The token value |
_token_type_code |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context stored for audit |
Returns: table(__token_id bigint, __token_uid text, __token_state_code text, __used_at timestamptz, __user_id bigint, __user_oid text, __token_data jsonb)
Permission required: tokens.set_as_used
Source: 025_functions_auth_token.sql:173
auth.set_token_as_failed_by_token¶
Convenience wrapper that looks up the token UID from the token value and type, then delegates to auth.set_token_as_failed. Use this when you only have the token value, not the UID.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_token |
text |
-- | The token value |
_token_type |
text |
-- | Token type code to match |
_request_context |
jsonb |
-- | Request context stored for audit |
Returns: table(__token_id bigint, __token_uid text, __token_state_code text, __used_at timestamptz, __user_id bigint, __user_oid text, __token_data jsonb)
Permission required: tokens.set_as_used (delegated via auth.set_token_as_failed)
Source: 025_functions_auth_token.sql:225
Expiration¶
unsecure.expire_tokens¶
Batch-expires all tokens that have passed their expires_at timestamp. Sets their token_state_code to 'expired'. Logs a journal entry with the count of expired tokens (only when at least one token was expired). This function runs in the unsecure schema and requires no permission check -- it is called automatically as a side effect of auth.create_token and auth.validate_token.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier recorded as the actor |
Returns: void
Permission required: None (unsecure schema -- called internally)
Source: 019_functions_unsecure.sql:395
No separate expiration job needed
Token expiration is piggy-backed onto regular token operations (create and validate). Every time a user creates or validates a token, overdue tokens across the entire system are expired. This eliminates the need for a separate scheduled cleanup job.
Token Type Configuration¶
These functions manage the const.token_type lookup table. System token types (those with is_system = true) cannot be modified or deleted.
public.get_token_types¶
Returns all token types, ordered by code.
No parameters.
Returns: setof const.token_type -- columns: code text, default_expiration_in_seconds integer, is_system boolean
Permission required: None
Source: 025_functions_auth_token.sql:335
public.create_token_type¶
Creates a new custom token type. The new type is always created with is_system = false.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_code |
text |
-- | Unique code for the new token type |
_default_expiration_in_seconds |
integer |
null |
Default lifetime in seconds for tokens of this type |
_tenant_id |
integer |
1 |
Tenant context for the permission check |
Returns: setof const.token_type (single row)
Permission required: token_configuration.create_token_type
Source: 025_functions_auth_token.sql:344
public.update_token_type¶
Updates the default expiration of an existing custom token type. Cannot modify system token types.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_code |
text |
-- | Code of the token type to update |
_default_expiration_in_seconds |
integer |
null |
New default lifetime in seconds |
_tenant_id |
integer |
1 |
Tenant context for the permission check |
Returns: setof const.token_type (single row)
Permission required: token_configuration.update_token_type
Source: 025_functions_auth_token.sql:375
Errors raised:
| Error | Condition |
|---|---|
| 36001 | Token type with the given code does not exist |
| 36002 | Token type is a system type and cannot be modified |
public.delete_token_type¶
Deletes a custom token type. Cannot delete system token types.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User ID of the actor (used for permission checking) |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_code |
text |
-- | Code of the token type to delete |
_tenant_id |
integer |
1 |
Tenant context for the permission check |
Returns: void
Permission required: token_configuration.delete_token_type
Source: 025_functions_auth_token.sql:422
Errors raised:
| Error | Condition |
|---|---|
| 36001 | Token type with the given code does not exist |
| 36002 | Token type is a system type and cannot be deleted |
Token States¶
Tokens transition through the following states (stored in const.token_state):
| State | Description |
|---|---|
valid |
Active token, not yet used or expired |
used |
Token was successfully consumed via set_token_as_used |
expired |
Token passed its expires_at time and was batch-expired |
invalid |
Token was invalidated (e.g. a newer token of the same type was created for the same user) |
validation_failed |
Token was presented but failed validation via set_token_as_failed |
Journal Event Codes¶
Token functions log the following journal event codes:
| Code | Event | Logged by |
|---|---|---|
| 15001 | Token created | auth.create_token |
| 15002 | Token used / validated | auth.set_token_as_used, auth.validate_token |
| 15003 | Tokens expired (batch) | unsecure.expire_tokens |
| 15004 | Token validation failed | auth.set_token_as_failed |
| 19001 | Token type created | public.create_token_type |
| 19002 | Token type updated | public.update_token_type |
| 19003 | Token type deleted | public.delete_token_type |
Required Permissions Summary¶
| Permission | Used by |
|---|---|
tokens.create_token |
auth.create_token |
tokens.validate_token |
auth.validate_token, public.validate_token |
tokens.set_as_used |
auth.set_token_as_used, auth.set_token_as_failed |
token_configuration.create_token_type |
public.create_token_type |
token_configuration.update_token_type |
public.update_token_type |
token_configuration.delete_token_type |
public.delete_token_type |