Date: 2026-02-07
Updated By: Claude Code
Updated the YAML schema definitions for sys_user and created new sys_usersession table schema with comprehensive documentation, metadata, and usage examples.
Locations:
local.processing/schema/package.sync/tables/sys_user.yamllocal.processing/schema/package.janee/tables/sys_user.yamlChanges:
Locations:
local.processing/schema/package.sync/tables/sys_usersession.yamllocal.processing/schema/package.janee/tables/sys_usersession.yamlNew Table: Complete session management system
PRIMARY KEY (User, Package)
| Column | Type | Description |
|---|---|---|
| User | varchar(150) | Username/login identifier |
| Uuid | char(36) | UUID for user |
| Package | varchar(100) | Package/tenant identifier |
| Active | tinyint(1) | Active status: 0=inactive, 1=active |
| Store | varchar(50) | Store identifier if applicable |
idx_active_user ON (Active, User)
idx_active_uuid ON (Active, Uuid)
idx_uuid ON (Uuid)
idx_email ON (Email)
idx_package ON (Package)
idx_store ON (Store)
metadata:
version: "2.0"
last_updated: "2026-02-07"
purpose: Multi-tenant user authentication and management
related_tables:
- sys_user_history
- sys_user_session
- def_usergroup
- sys_user_preferences
keycloak_integration: true
encryption:
fields:
- Password
- loginpassword
method: AES encryption with ~ prefix
Documents how Usergroups field should contain comma-separated values from def_usergroup table:
# Create new user
INSERT INTO sys_user (User, Uuid, Package, Active, Password, Email, RegisterDate, Usergroups)
VALUES ('john.doe', UUID(), 'homechoice', 1, '~encrypted_pass', 'john@example.com', NOW(), 'USERS,WEBUI');
# Find active users in package
SELECT * FROM sys_user WHERE Package = 'homechoice' AND Active = 1;
# Update last login
UPDATE sys_user SET LastLoginDate = NOW(), LastInteraction = NOW()
WHERE User = 'john.doe' AND Package = 'homechoice';
PRIMARY KEY (SessionId)
| Column | Type | Description |
|---|---|---|
| SessionId | char(64) | Unique session identifier |
| User | varchar(150) | Username from sys_user |
| Package | varchar(100) | Package/tenant identifier |
| SessionToken | varchar(512) | JWT or session token |
| SessionType | varchar(20) | web, api, mobile, console |
| IpAddress | varchar(45) | Client IP address (IPv4/IPv6) |
| UserAgent | text | Client user agent string |
| CreatedAt | datetime | Session creation timestamp |
| LastActivity | datetime | Last activity (auto-update) |
| ExpiresAt | datetime | Session expiration |
| IsActive | tinyint(1) | Active status |
| DeviceId | varchar(100) | Device identifier |
| DeviceName | varchar(255) | Device name/description |
| Location | varchar(255) | Geographic location |
| LoginMethod | varchar(50) | password, otp, sso, api_key |
| RefreshToken | varchar(512) | Refresh token for renewal |
| RefreshTokenExpiresAt | datetime | Refresh token expiration |
| InvalidatedAt | datetime | Invalidation timestamp |
| InvalidatedBy | varchar(150) | User who invalidated |
| InvalidatedReason | varchar(255) | Reason for invalidation |
idx_user_package ON (User, Package)
idx_user_active ON (User, IsActive)
idx_expires ON (ExpiresAt, IsActive)
idx_token ON (SessionToken(255))
idx_ip ON (IpAddress)
idx_created ON (CreatedAt)
idx_package ON (Package)
idx_device ON (DeviceId)
session_lifetime:
web: 8 hours
api: 30 days
mobile: 90 days
console: 12 hours
max_concurrent_sessions:
web: 3
api: 10
mobile: 5
console: 1
cleanup:
expired_retention_days: 7
inactive_retention_days: 30
token_rotation:
enabled: true
rotation_interval: 1 hour
refresh_token_lifetime: 30 days
The schema includes 8 pre-defined SQL queries:
create_session - Create new sessionget_active_session - Get session by IDget_user_sessions - Get all user sessionsupdate_activity - Update last activityinvalidate_session - Invalidate single sessioninvalidate_user_sessions - Invalidate all user sessionscleanup_expired - Clean up old sessionscount_concurrent - Count concurrent sessions# Create new session
INSERT INTO sys_usersession
(SessionId, User, Package, SessionType, IpAddress, UserAgent, CreatedAt, ExpiresAt, LoginMethod)
VALUES
(UUID(), 'john.doe', 'homechoice', 'web', '192.168.1.100', 'Mozilla/5.0...', NOW(), DATE_ADD(NOW(), INTERVAL 8 HOUR), 'password');
# Find active sessions for user
SELECT * FROM sys_usersession
WHERE User = 'john.doe' AND Package = 'homechoice' AND IsActive = 1 AND ExpiresAt > NOW();
# Invalidate session (logout)
UPDATE sys_usersession
SET IsActive = 0, InvalidatedAt = NOW(), InvalidatedReason = 'User logout'
WHERE SessionId = '...' AND IsActive = 1;
# Clean up expired sessions
DELETE FROM sys_usersession
WHERE ExpiresAt < DATE_SUB(NOW(), INTERVAL 7 DAY) AND IsActive = 0;
# Create tables from schema
python factory.core/ObjData.py create-tables sys_user
python factory.core/ObjData.py create-tables sys_usersession
# Seed standard user groups
python factory.web/seed_usergroups.py seed --all
# Create test user
python -c "
from factory.web.ObjUser import User
u = User()
u.Create('test@example.com', '~encrypted_pass', 'homechoice')
"
from ObjData import ObjData
db = ObjData()
# Create session
sql = db.load_yaml_query('sys_usersession', 'create_session',
session_id='test-session-123',
user='test@example.com',
package='homechoice',
session_type='web',
ip_address='127.0.0.1',
user_agent='Test/1.0',
expires_at='2026-02-08 00:00:00',
login_method='password',
session_token='jwt.token.here'
)
db.sql_execute(sql)
# Get active sessions
sql = db.load_yaml_query('sys_usersession', 'get_user_sessions',
user='test@example.com',
package='homechoice'
)
sessions = db.sql_get_array(sql)
print(f"Active sessions: {len(sessions)}")