user_sessions

Bearer-token session store for authenticated access

SQL Identity Security

user_sessions

Purpose

Stores active and historical bearer-token sessions. This is the persistence layer behind login, logout, token validation, forced session revocation, and last-used tracking.

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`user_id`	bigint unsigned	No	Owning user
`token_hash`	char(64)	No	SHA-256 hash of validator/token
`created_at`	datetime(3)	No	Session creation time
`expires_at`	datetime(3)	No	Expiry timestamp
`revoked_at`	datetime(3)	Yes	Manual or security revocation
`last_used_at`	datetime(3)	Yes	Most recent token use
`user_agent`	varchar(255)	Yes	Client user-agent snapshot
`ip_address`	varchar(45)	Yes	IPv4/IPv6-compatible IP field

Keys and indexes

PRIMARY KEY (id)
UNIQUE KEY uq_token_hash (token_hash)
KEY ix_user_sessions_user (user_id, expires_at)

Foreign keys

fk_user_sessions_user: user_sessions.user_id → users.id ON DELETE CASCADE

Session lifecycle

Login
 └── create row
      ├── validate on each request
      ├── update last_used_at
      ├── revoke on logout/reset/security event
      └── expire by time

Used by

Login endpoint inserts new sessions
Auth guards validate session hash and expiry
Logout revokes current session
Password reset revokes all active sessions for the user

Design notes

Token hashes are stored, not raw tokens
Revocation and expiry are separate concepts
The schema supports device/session-level metadata via user agent and IP
This table uses hard cascade delete when a user is deleted

Risks

High sensitivity: compromise here affects active access control
Last-used updates can increase write volume on authenticated traffic
No explicit index on revoked_at, so filtering patterns matter

Recommended future improvements

Consider session/device naming if user-facing session management is added
Consider archiving or pruning old revoked/expired sessions
Add clearer operational policy for retention of IP and user-agent metadata