Database Tables
audit
Old versions of the rows in users and sessions
Table "public.audit"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------
id | text | | not null | | extended | | | ID as in the source table (users or sessions)
timestamp | timestamp with time zone | | not null | now() | plain | | | When the row was changed
kind | audit_kind | | not null | | plain | | | The kind of table modification
type | resource_type | | not null | | plain | | | Which table the modification refers to, either "User" or "OidcSession"
data | jsonb | | | | extended | | | The data from the new contents after the change. Saved as a JSON blob, the schema depends on the table the modification refers to
Indexes:
"audit_pkey" PRIMARY KEY, btree (id, "timestamp")
Access method: heap
jwks
Keys for JWT encoding
Table "public.jwks"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------------------------------------------
id | integer | | not null | generated always as identity | plain | | | Key ID, generated as consecutive integers
created | timestamp with time zone | | not null | now() | plain | | | When the key was created
stop_issue | timestamp with time zone | | not null | now() + '1 day'::interval | plain | | | When we should stop issuing JWTs using this key
expiry | timestamp with time zone | | not null | now() + '2 days'::interval | plain | | | When this key expires
private_key | bytea | | not null | | extended | | | The key itself
Access method: heap
sessions
User login sessions
Table "public.sessions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-----------------------------------------------
id | integer | | not null | generated always as identity | plain | | | Session ID, generated as consecutive integers
created | timestamp with time zone | | not null | now() | plain | | | When the session was created
user_id | text | | not null | | extended | | | ID of the user this session belongs to
scope | text | | not null | | extended | | | OAuth scope for the session
refresh_token | text | | | | extended | | | Token for refreshing the session
nonce | text | | | | extended | | | OpenID nonce
code | text | | | | extended | | | OpenID authorization code
code_challenge | text | | | | extended | | | OpenID PKCE code challenge
upstream_id_token | text | | | | extended | | | The ID token from the upstream IdP
Foreign-key constraints:
"sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
Triggers:
add_audit_session_delete AFTER DELETE ON sessions FOR EACH ROW EXECUTE FUNCTION audit_session_delete()
add_audit_session_insert AFTER INSERT ON sessions FOR EACH ROW EXECUTE FUNCTION audit_session_insert()
sessions_sync_trigger AFTER INSERT OR DELETE OR UPDATE ON sessions FOR EACH STATEMENT EXECUTE FUNCTION send_resource_notification()
Access method: heap
users
Most recent info about users
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+--------------------------+-----------+----------+---------+----------+-------------+--------------+--------------------------------------------
id | text | | not null | | extended | | | String uniquely identifying the user
created | timestamp with time zone | | not null | now() | plain | | | When the user was created
modified | timestamp with time zone | | not null | | plain | | | When the user was last modified
data | jsonb | | not null | | extended | | | Arbitrary JSON data from the SCIM upstream
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "sessions" CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
Triggers:
add_audit_user_delete AFTER DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_user_delete()
add_audit_user_insert AFTER INSERT ON users FOR EACH ROW EXECUTE FUNCTION audit_user_insert()
add_audit_user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_user_update()
add_update_user_modified BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_user_modified()
check_audit_timestamps_trigger AFTER INSERT OR UPDATE ON users DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION check_audit_timestamps()
users_sync_trigger AFTER INSERT OR DELETE OR UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION send_resource_notification()
Access method: heap