# Workflow Settings Workflow When an admin changes KYC document requirements: 1\. Config controller detects requiredDocuments changed 2\. Dispatches KycComplianceReEvalJob to Bull queue 3\. Job evaluates all tenant users with verified/pending KYC 4\. For each user, compares their valid documents against new requirements 5\. Users missing required docs get kycPendingDocuments populated 6\. Verified users with missing docs are downgraded to 'pending' 7\. Affected users receive in-app notification + email 8\. Audit log records the batch re-evaluation User Model (80+ columns) ├── fullName ← Used for BOTH personal name AND company name (the problem!) ├── type ← super\_admin | company | user | client ├── createdBy ← Multi-tenancy: 0 for tenants, tenant.id for employees └── \[KYC, auth, SSO, DID fields...] TenantSettings Model (70+ columns) ├── tenantId (FK → User.id) ├── taxId, taxIdType, taxJurisdiction ← Business tax info exists └── \[Feature toggles, prefixes, etc.] └── ❌ NO company identity fields (companyName, legalName, logo) Database Structure Analysis & Recommendations Current State Summary ┌─────────────────┬─────────┬───────────────────────────────────────────────┐ │ Table │ Columns │ Status │ ├─────────────────┼─────────┼───────────────────────────────────────────────┤ │ tenant\_settings │ 85 │ High - needs refactoring │ ├─────────────────┼─────────┼───────────────────────────────────────────────┤ │ users │ 81 │ Medium - well-organized with vault protection │ ├─────────────────┼─────────┼───────────────────────────────────────────────┤ │ kyc\_submissions │ 65 │ OK - complex but justified by workflow │ ├─────────────────┼─────────┼───────────────────────────────────────────────┤ │ invoices/bills │ 35-40 │ OK - document tables are inherently wide │ └─────────────────┴─────────┴───────────────────────────────────────────────┘ \--- Recommendations 1\. TenantSettings Consolidation (Priority: HIGH) This table has grown organically with each new module. I recommend consolidating related fields into JSON objects: Current: 85 columns Target: \~55 columns // BEFORE: 15 individual boolean columns enableKyc, enableCrypto, enableInventory, enableRepairShop, enableFundAccounting, enableEncumbrance, enableMultiCurrency, ... // AFTER: 1 JSON column @column() declare enabledModules: { kyc: boolean crypto: boolean inventory: boolean repairShop: boolean fundAccounting: boolean encumbrance: boolean multiCurrency: boolean costCenters: boolean recurring: boolean budgeting: boolean attachments: boolean projects: boolean electronicInvoicing: boolean } Other consolidation opportunities: ┌───────────────────┬────────────────────┬───────────────────────┐ │ Group │ Current Columns │ Proposed JSON Column │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Module toggles │ 15 booleans │ enabledModules: {} │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Document prefixes │ 7 strings │ documentPrefixes: {} │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Default accounts │ 5 FKs │ defaultAccountIds: {} │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Approval settings │ 6 booleans + 1 int │ approvalSettings: {} │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Repair shop │ 5 mixed │ repairShopConfig: {} │ ├───────────────────┼────────────────────┼───────────────────────┤ │ Crypto │ 3 mixed │ cryptoConfig: {} │ └───────────────────┴────────────────────┴───────────────────────┘ Trade-offs: ┌──────────────────┬─────────────────────────────────────┬──────────────────────────────────────────────────┐ │ Approach │ Pros │ Cons │ ├──────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────┤ │ JSON columns │ Fewer columns, easier to add fields │ Can't index individual fields, no FK constraints │ ├──────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────┤ │ Separate columns │ Indexable, type-safe, FK support │ More migrations, wider tables │ └──────────────────┴─────────────────────────────────────┴──────────────────────────────────────────────────┘ My recommendation: Use JSON for configuration objects (module toggles, prefixes, repair shop settings) but keep FKs as separate columns (defaultAccountIds should stay as individual columns for referential integrity). \--- 2\. Users Table (Priority: MEDIUM) The 81 columns are actually well-organized into logical domains: ┌─────────────────────┬─────────┬────────────────────────────────────────┐ │ Domain │ Columns │ Recommendation │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ Core identity │ 7 │ Keep │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ Auth (passkey/MFA) │ 10 │ Keep - frequently accessed │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ SSO │ 8 │ Consider: Move to user\_sso\_profiles │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ DID/Domain │ 12 │ Consider: Move to user\_domains │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ KYC status │ 7 │ Keep - frequently joined │ ├─────────────────────┼─────────┼────────────────────────────────────────┤ │ Vault-protected PII │ 10 │ Keep - encryption requires co-location │ └─────────────────────┴─────────┴────────────────────────────────────────┘ Recommendation: Extract SSO and DID/Domain fields to separate tables: \-- New table: user\_sso\_profiles CREATE TABLE user\_sso\_profiles ( user\_id INT PRIMARY KEY REFERENCES users(id), provider VARCHAR(50), provider\_id VARCHAR(255), tenant\_id VARCHAR(255), session\_id VARCHAR(255), authenticated\_at TIMESTAMP, provisioned BOOLEAN, first\_login\_at TIMESTAMP, last\_sync\_at TIMESTAMP ); \-- New table: user\_domains CREATE TABLE user\_domains ( user\_id INT PRIMARY KEY REFERENCES users(id), subdomain\_slug VARCHAR(50) UNIQUE, custom\_domain VARCHAR(255), custom\_domain\_verified BOOLEAN, verification\_token VARCHAR(255), verified\_at TIMESTAMP, ssl\_enabled BOOLEAN, ssl\_issued\_at TIMESTAMP, did\_document\_uri VARCHAR(500), did\_document JSONB, did\_document\_updated\_at TIMESTAMP, signing\_key\_public TEXT, signing\_key\_private\_encrypted TEXT, signing\_key\_id VARCHAR(255), signing\_key\_version INT ); Impact: Reduces users table by \~20 columns, improves query performance for common operations that don't need SSO/DID data. \--- 3\. JSON vs Separate Columns Decision Matrix ┌───────────────────────┬─────────────────────────────────────┬──────────────────────────────────────────────────────┐ │ Use Case │ Recommendation │ Reason │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Feature toggles │ JSON │ Rarely queried individually, often loaded as a group │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Configuration objects │ JSON │ Self-contained settings that change together │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Foreign keys │ Separate columns │ Referential integrity, indexing │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Searchable fields │ Separate columns (with blind index) │ Performance │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Audit trail data │ JSON │ Already using JSONB for before/after state │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ API responses │ JSON │ Natural fit (Hacienda responses already use JSONB) │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Status enums │ Separate columns │ Indexable, filterable │ ├───────────────────────┼─────────────────────────────────────┼──────────────────────────────────────────────────────┤ │ Timestamps │ Separate columns │ Range queries, indexing │ └───────────────────────┴─────────────────────────────────────┴──────────────────────────────────────────────────────┘ \--- 4\. Performance Considerations Current Good Practices (Keep): \- Blind indexes for vault-protected PII fields (fullNameBidx, phoneNumberBidx) \- JSONB for API responses (allows GIN indexing if needed) \- Proper FK constraints with ON DELETE CASCADE/SET NULL Recommended Improvements: A. Partial Indexes for Status Columns \-- Only index active users (most queries filter on this) CREATE INDEX idx\_users\_active ON users(id) WHERE is\_active = true; \-- Only index pending KYC submissions CREATE INDEX idx\_kyc\_pending ON kyc\_submissions(user\_id) WHERE status IN ('pending', 'in\_review'); B. Composite Indexes for Common Queries \-- Multi-tenant queries always filter by created\_by + status CREATE INDEX idx\_invoices\_tenant\_status ON invoices(created\_by, status); CREATE INDEX idx\_bills\_tenant\_status ON bills(created\_by, status); C. Consider Table Partitioning for Audit Logs \-- Partition kyc\_audit\_log by month for scalability CREATE TABLE kyc\_audit\_log ( ... ) PARTITION BY RANGE (created\_at); CREATE TABLE kyc\_audit\_log\_2026\_01 PARTITION OF kyc\_audit\_log FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); \--- 5\. Scalability Roadmap ┌─────────────┬──────────────────────────────────────────┬───────────────────────┐ │ Phase │ Action │ Impact │ ├─────────────┼──────────────────────────────────────────┼───────────────────────┤ │ Now │ Add composite indexes on tenant+status │ Query performance │ ├─────────────┼──────────────────────────────────────────┼───────────────────────┤ │ Short-term │ Consolidate tenant\_settings JSON columns │ Schema simplification │ ├─────────────┼──────────────────────────────────────────┼───────────────────────┤ │ Medium-term │ Extract user\_sso\_profiles, user\_domains │ Reduced table width │ ├─────────────┼──────────────────────────────────────────┼───────────────────────┤ │ Long-term │ Partition audit/log tables by date │ Storage scalability │ └─────────────┴──────────────────────────────────────────┴───────────────────────┘ \--- 6\. Implementation Priority 1\. Immediate (this sprint): \- Add missing composite indexes for multi-tenant queries 2\. Next sprint: \- Consolidate tenant\_settings module toggles into enabledModules JSON \- Consolidate tenant\_settings document prefixes into documentPrefixes JSON 3\. Future (when SSO usage increases): \- Extract SSO fields to user\_sso\_profiles \- Extract DID/domain fields to user\_domains \--- Would you like me to implement any of these recommendations? I'd suggest starting with the tenant\_settings JSON consolidation since we just added company identity fields and it's a good time to restructure.