Database: trader
Package: FULLHOUSE
Total Tables: 525 tables
Schema Type: Legacy retail POS system with multi-store architecture
users - Main User Table(US_STORE_ID, US_ID)US_STORE_ID - Store identifierUS_ID - User ID (unique per store)US_USERNAME - Login usernameUS_PASSWORD - Password (max 10 chars)US_ID_NUMBER - ID numberUS_NAME - Full nameUS_GLOBAL_ID - Global user ID (cross-store)user_passwords - Password History(LP_STORE_ID, LP_USER_ID, LP_ID)LP_STORE_ID - Store identifierLP_USER_ID - User IDLP_ID - History entry IDLP_DATE - Change dateLP_PASSWORD - Previous passwordsecurity_groups - User Groups/Roles(SG_STORE_ID, SG_ID)SG_STORE_ID - Store identifierSG_ID - Group IDSG_NAME - Group name (e.g., "ADMINISTRATORS", "BR CLERK")SG_GLOBAL_ID - Global group IDSample Groups:
security_user_groups - User-Group Mapping (M:N)(UG_STORE_ID, UG_USER_ID, UG_GROUP_ID)UG_STORE_ID - Store identifierUG_USER_ID - User IDUG_GROUP_ID - Group IDUG_USER_GLOBAL_ID - Global user IDUG_GROUP_GLOBAL_ID - Global group IDsecurity_points - 542 records (security access points)security_user_points - 83,054 records (user access permissions)security_headers - 43 records (security configuration headers)security_exceptions - 96 records (security exceptions)security_settings - 24 records (security settings)sys_user - System User TableFile: local.processing/schema/package.sync/tables/sys_user.yaml
(User, Package)User - Username (char 150)Uuid - UUID (char 35)Package - Package name (char 255)Active - Active statusPassword - Encrypted password (text)UserGroups - Comma-separated groups (mediumtext)Email, Name, Firstnames, etc.sys_user_history - User HistoryFile: local.processing/schema/package.sync/tables/sys_user_history.yaml
(User, Package)User - UsernamePackage - Package namePassword - Password snapshotChangetime - Change timestampPasswordmessage - Change type (REGISTERED, Activated, Login, etc.)def_usergroup - User Group DefinitionsFile: local.processing/schema/package.sync/tables/def_usergroup.yaml
(usergroup, package)usergroup - Group name (char 255)package - Package name (char 255)description - Group description (TEXT)access_level - Access level identifier (VARCHAR 50)active - Active status (char 5)Standard Groups Defined (13 groups):
| Aspect | Current Database (trader) | Schema Files (sys_user) |
|---|---|---|
| Multi-tenancy | Multi-store (STORE_ID) | Multi-package (Package) |
| User ID | Numeric (US_ID) | String (User) |
| Primary Key | (STORE_ID, USER_ID) | (User, Package) |
| Password Length | 10 chars (varchar) | Unlimited (text, encrypted) |
| Groups Storage | Separate M:N table | Comma-separated in user record |
| UUID Support | Limited (US_GLOBAL_ID) | Full UUID (Uuid column) |
| Package Isolation | No | Yes (Package column) |
| Feature | Current Database | Schema Files |
|---|---|---|
| Table | security_groups | def_usergroup |
| Primary Key | (SG_STORE_ID, SG_ID) | (usergroup, package) |
| Group Name | SG_NAME (varchar 50) | usergroup (char 255) |
| Description | No description field | description (TEXT) |
| Access Level | No access level | access_level (VARCHAR 50) |
| Standard Groups | Ad-hoc per store | 13 standard groups defined |
| User Assignment | security_user_groups (M:N) | UserGroups column (comma-separated) |
| Feature | Current Database | Schema Files |
|---|---|---|
| Table | user_passwords | sys_user_history |
| Primary Key | (LP_STORE_ID, LP_USER_ID, LP_ID) | (User, Package) - compound |
| History Type | Password only | All changes (login, activation, etc.) |
| Message/Reason | No | PasswordMessage field |
The ObjUser.py class expects the sys_user schema, but the current database uses the users table schema.
Options:
Keep Both Schemas: Maintain separate classes for each schema
ObjUser - For sys_user (new systems)ObjTraderUser - For users table (legacy trader system)Migrate Database: Create sys_user tables alongside existing users table
Abstract Base Class: Create common interface
The ObjVirtManager expects sys_user table for Keycloak user provisioning:
provision_keycloak_users() reads from sys_user WHERE Package = current_packageusers table without Package columnSolutions:
sys_user table in trader databaseCREATE VIEW sys_user AS SELECT ... FROM usersThe standard groups defined in def_usergroup.yaml don't exist in current database:
security_groups has ad-hoc groups per storeRecommendation:
def_usergroup tableseed_usergroups.py-- Create sys_user, sys_user_history, def_usergroup
-- Run from schema yaml files
-- Tables coexist with legacy users table
# Populate def_usergroup with standard groups
python factory.web/seed_usergroups.py seed --all
-- Migrate users → sys_user
INSERT INTO sys_user (User, Package, Password, Active, Name, ...)
SELECT US_USERNAME, 'FULLHOUSE', US_PASSWORD,
CASE WHEN US_STATUS = 1 THEN 'Y' ELSE 'N' END,
US_NAME, ...
FROM users;
-- Map security_groups → UserGroups
UPDATE sys_user u
SET UserGroups = (
SELECT GROUP_CONCAT(sg.SG_NAME)
FROM security_user_groups sug
JOIN security_groups sg ON sg.SG_ID = sug.UG_GROUP_ID
WHERE sug.UG_USER_ID = (SELECT US_ID FROM users WHERE US_USERNAME = u.User)
);
# Update ObjUser to use sys_user table
# Update ObjVirtManager to read from sys_user
# Update authentication to check sys_user first, fallback to users
Short Term (Immediate):
Medium Term (1-2 sprints):
Long Term (3-6 months):