Session Table

Overview

Login/session records for end users and admins (token material, identity, audit).

Table Definition

Logical Name	Physical Name	Column Name	Data Type	Primary Key	Relation	Unique	Nullable	Default Value	Remarks
Session	session	id	string	◯					varchar(255)
		jti	string				◯
		token_hash	string			◯			varchar(64), NOT NULL
		user_type	enum						session_user_type ('user', 'admin')
		cognito_sub	string
		user_id	number		user:id		◯
		admin_id	number		admin:id		◯
		login_at	datetime
		logout_at	datetime				◯
		ip_address	string				◯
		user_agent	string				◯
		expires_at	datetime				◯
		revoked_at	datetime				◯
		created_at	datetime
		updated_at	datetime
		deleted_at	datetime				◯
		created_by	string
		updated_by	string
		deleted_by	string				◯

Relations

• user_id → user.id (nullable)
• admin_id → admin.id (nullable)
• created_by / updated_by / deleted_by store cognito_sub where applicable (standard audit)

Indexes

• PRIMARY KEY (id)
• UNIQUE INDEX session_token_hash_unique (token_hash)
• INDEX session_user_id_idx (user_id)
• INDEX session_admin_id_idx (admin_id)
• INDEX session_cognito_sub_user_type_idx (cognito_sub, user_type)
• INDEX session_user_type_logout_at_idx (user_type, logout_at)
• INDEX session_user_id_login_at_idx (user_id, login_at)
• INDEX session_admin_id_login_at_idx (admin_id, login_at)
• INDEX session_expires_at_idx (expires_at)
• INDEX session_jti_idx (jti)
• INDEX session_revoked_at_logout_at_idx (revoked_at, logout_at)
• INDEX session_deleted_at_idx (deleted_at)

Notes

• Typically exactly one of user_id or admin_id is set, matching user_type