Cleaned up redundant fields in sys_user.yaml schema based on data analysis and removed duplicate columns.
Before:
- '`Usergroups` text NULL DEFAULT NULL COMMENT ''Comma-separated list of user groups'''
- '`UserGroup` varchar(50) NULL DEFAULT NULL COMMENT ''Primary user group (deprecated - use Usergroups)'''
After:
- '`UserGroup` varchar(255) NULL DEFAULT NULL COMMENT ''Comma-separated list of user groups from def_usergroup'''
Rationale:
Usergroups and UserGroup was redundantUserGroup is the more intuitive name (singular noun for a collection makes sense in database context)Before:
- '`Company` varchar(150) NULL DEFAULT ''Guest'' COMMENT ''Primary company association'''
- '`Companies` text NULL DEFAULT NULL COMMENT ''Comma-separated list of companies'''
- '`UserCompany` varchar(150) NULL DEFAULT NULL COMMENT ''User company assignment'''
- '`Currentcompany` varchar(255) NULL DEFAULT NULL COMMENT ''Currently selected company'''
After:
- '`Companies` text NULL DEFAULT NULL COMMENT ''Comma-separated list of companies'''
- '`Currentcompany` varchar(255) NULL DEFAULT NULL COMMENT ''Currently selected company'''
Rationale:
Company, Companies, UserCompany) was confusingCompanies (plural, TEXT) can hold comma-separated list of all company associationsCurrentcompany tracks which company is currently active in sessionBefore:
- '`Store` varchar(50) NULL DEFAULT NULL COMMENT ''Store identifier if applicable'''
After:
- '`Office` varchar(50) NULL DEFAULT NULL COMMENT ''Office/branch identifier'''
Index Updated:
# Before
'`idx_store`':
- '`Store`'
# After
'`idx_office`':
- '`Office`'
Rationale:
Locations:
local.processing/schema/package.sync/tables/sys_user.yamllocal.processing/schema/package.janee/tables/sys_user.yamlBefore: 43 columns (5 primary + 38 appended)
After: 38 columns (5 primary + 33 appended)
Reduction: 5 columns removed
Location: factory.web/ObjUser.yaml
Updated Queries:
# Query: create_user
# Changed: UserGroups → UserGroup
INSERT INTO sys_user
(User, Package, Password, Uuid, RegisterDate, Level, UserGroup)
VALUES
('{email}', '{package}', '{password}', '{uuid}', NOW(), 5, 'USER')
# Query: create_federated_user
# Changed: UserGroups → UserGroup, usergroups → usergroup parameter
INSERT INTO sys_user
(User, Password, Package, UserGroup, RegisterDate, Name)
VALUES
('{user}', '{password}', '{package}', '{usergroup}', '{registerdate}', '{name}')
notes: |
- UserGroup contains comma-separated group names (e.g., "ADMIN,DEVELOPERS,WEBUI")
- Office replaces Store for branch/office identification
- Companies contains comma-separated list of company codes
# Create new user with cleaned up fields
INSERT INTO sys_user (User, Uuid, Package, Active, Password, Email, RegisterDate, UserGroup, Companies, Office)
VALUES ('john.doe', UUID(), 'homechoice', 1, '~encrypted_pass', 'john@example.com', NOW(), 'USERS,WEBUI', 'HC,FH', 'JHB01');
# Find users in specific office
SELECT * FROM sys_user WHERE Package = 'homechoice' AND Office = 'JHB01';
# Find users with admin group
SELECT * FROM sys_user
WHERE Package = 'homechoice'
AND Active = 1
AND FIND_IN_SET('ADMIN', REPLACE(UserGroup, ' ', '')) > 0;
| Section | Before | After | Change |
|---|---|---|---|
| Primary Columns | 5 | 5 | 0 |
| Authentication | 8 | 8 | 0 |
| Two-Factor Auth | 2 | 2 | 0 |
| Personal Info | 7 | 7 | 0 |
| Authorization | 3 | 2 | -1 (removed Usergroups) |
| Company/Org | 4 | 2 | -2 (removed Company, UserCompany) |
| Profile/Prefs | 4 | 4 | 0 |
| Business/Workflow | 4 | 4 | 0 |
| Session/Tracking | 2 | 2 | 0 |
| Keycloak | 1 | 1 | 0 |
| Legacy | 3 | 3 | 0 |
| Total | 43 | 38 | -5 |
⚠️ Yes - Applications using these fields will need updates:
Usergroups field removed:
Usergroups to use UserGroupCompany and UserCompany fields removed:
Company to use CompaniesUserCompany to use CompaniesStore renamed to Office:
Store to use OfficeStore column-- If upgrading existing database, migrate data before dropping columns
-- Migrate Usergroups to UserGroup (if Usergroups has data)
UPDATE sys_user
SET UserGroup = Usergroups
WHERE Usergroups IS NOT NULL AND Usergroups != '';
-- Migrate Company to Companies (if not already populated)
UPDATE sys_user
SET Companies = CONCAT_WS(',', Company, UserCompany)
WHERE Companies IS NULL OR Companies = '';
-- Rename Store to Office
ALTER TABLE sys_user CHANGE COLUMN `Store` `Office` varchar(50) NULL DEFAULT NULL COMMENT 'Office/branch identifier';
-- Drop redundant columns (AFTER data migration)
ALTER TABLE sys_user DROP COLUMN `Usergroups`;
ALTER TABLE sys_user DROP COLUMN `Company`;
ALTER TABLE sys_user DROP COLUMN `UserCompany`;
✓ sys_user.yaml: Valid YAML
✓ ObjUser.yaml: Valid YAML
✓ Both schema files copied to package.janee
Simplified Schema:
Clearer Semantics:
UserGroup vs Usergroups - one field, one purposeCompanies vs Company/UserCompany - one list, not three fieldsOffice vs Store - better terminologyBetter Data Integrity:
Easier Queries:
If needed, restore original schema:
git checkout HEAD~1 local.processing/schema/package.sync/tables/sys_user.yaml
git checkout HEAD~1 local.processing/schema/package.janee/tables/sys_user.yaml
git checkout HEAD~1 factory.web/ObjUser.yaml
factory.deploy/ObjVirtManager.py:
factory.web/ObjUser_yaml_migration.md:
Database Migration Script: