nextjs-elysia-allaos/docs/checklist-phase1-database.md

Phase 1: Database Schema Design - Checklist

✅ Overview

Convert all database schemas from integer IDs to UUID, implement multi-tenant branch support, dual customer codes, contact visibility, and multi-currency quotations.

📋 Completed Tasks

Schema Files

• Create src/database/schema/branches.ts
  • Define branches table with UUID primary key
  • Add code, name, isActive fields
  • Create indexes for code and isActive
• Update src/database/schema/customers.ts
  • Convert ID to UUID
  • Add branchId foreign key
  • Add crmCustomerCode (auto-generated, unique)
  • Add erpCustomerCode (manual, nullable, unique)
  • Update creditLimit to numeric type
  • Convert createdBy/updatedBy to UUID
  • Add performance indexes
• Update src/database/schema/customerContacts.ts
  • Convert ID to UUID
  • Add branchId foreign key
  • Add isPublic field (default: false)
  • Convert createdBy/updatedBy to UUID
  • Add visibility indexes
• Create src/database/schema/contact-shares.ts
  • Define contact shares table
  • Add contactId, sharedWithUserId, sharedBy fields
  • Add unique constraint on (contactId, sharedWithUserId)
  • Create indexes for performance
• Update src/database/schema/quotations.ts
  • Convert ID to UUID
  • Add branchId foreign key
  • Add revisionNo and parentQuotationId
  • Add currencyCode, exchangeRate, baseCurrencyAmount
  • Remove uniqueness constraint from code
  • Convert all user references to UUID
  • Update all child tables (followups, attachments, topics, etc.)
  • Add performance indexes
• Create src/database/schema/quotation-contacts.ts
  • Define quotation contacts snapshot table
  • Add immutable snapshot fields
  • Create indexes
• Update src/database/schema/index.ts
  • Export all new and updated schemas

Migration Script

• Create drizzle/migrations/0001_crm_refactor_uuid_multi_branch.sql
  • Phase 1: Create branches table
  • Phase 2-7: Prepare core tables for UUID
  • Phase 4: Create contact shares table
  • Phase 8: Prepare additional quotation tables
  • Phase 9: Backfill all data
  • Phase 10-15: Swap columns (integer → UUID)
  • Phase 16: Create quotation contacts snapshot
  • Phase 17: Create performance indexes
  • Add verification queries

🎯 Key Features Implemented

1. Multi-Tenant Architecture

• ✅ Branch-based data isolation
• ✅ Branch table with alla and onvalla
• ✅ All business tables have branchId

2. UUID Conversion

• ✅ All primary keys converted to UUID
• ✅ All foreign keys converted to UUID
• ✅ Safe migration with column swapping

3. Dual Customer Codes

• ✅ crmCustomerCode: Auto-generated, unique
• ✅ erpCustomerCode: Manual, nullable, unique
• ✅ Support for CRM → ERP sync flow

4. Contact Visibility

• ✅ Private by default (isPublic: false)
• ✅ Contact sharing mechanism
• ✅ Creator ownership tracking
• ✅ Visibility indexes for performance

5. Multi-Currency Quotations

• ✅ Manual exchange rate entry
• ✅ Base currency amount (THB)
• ✅ Same code, multiple currency versions
• ✅ Historical rate capture

6. Revision System

• ✅ Revision number tracking
• ✅ Parent quotation reference
• ✅ Revision history support

7. Historical Integrity

• ✅ Immutable contact snapshots
• ✅ Quotation retains full contact access
• ✅ No retroactive permission loss

📊 Migration Statistics

Tables Modified: 10

1. ms_branches (NEW)
2. ms_customers
3. ms_customer_contacts
4. ms_customer_contact_shares (NEW)
5. tr_quotations
6. tr_quotations_items
7. tr_quotations_customers
8. tr_quotations_followups
9. tr_quotations_attachments
10. tr_quotations_topics
11. tr_quotations_topic_items
12. ms_quotations_template_versions
13. ms_quotations_template_mappings
14. ms_quotations_template_table_columns
15. tr_quotation_contacts (NEW)

Indexes Created: 20+

• Branch indexes: 2
• Customer indexes: 4
• Contact indexes: 4
• Quotation indexes: 6
• Other indexes: 4+

⚠️ Important Notes

Before Running Migration

1. Backup database - This is a destructive migration
2. Test on staging - Never run directly on production first
3. Prepare rollback - Have a rollback plan ready

Migration Execution

# Run migration
psql -U your_user -d your_database -f drizzle/migrations/0001_crm_refactor_uuid_multi_branch.sql

# Verify
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM ms_branches;"
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM ms_customers WHERE branch_id IS NULL;"
psql -U your_user -d your_database -c "SELECT COUNT(*) FROM tr_quotations WHERE branch_id IS NULL;"

Data Safety

• ✅ Uses transactions (BEGIN/COMMIT)
• ✅ IF NOT EXISTS checks throughout
• ✅ Safe column swapping without data loss
• ✅ Backfills existing data to 'alla' branch
• ✅ Preserves all existing relationships

🔍 Verification Steps

After migration, verify:

-- 1. Check branches
SELECT * FROM ms_branches;

-- 2. Check customer UUIDs
SELECT id, code, crm_customer_code, branch_id
FROM ms_customers
LIMIT 10;

-- 3. Check contact visibility
SELECT id, customer_id, created_by, is_public
FROM ms_customer_contacts
LIMIT 10;

-- 4. Check quotation multi-currency
SELECT id, code, currency_code, exchange_rate, branch_id
FROM tr_quotations
LIMIT 10;

-- 5. Check no NULL branchIds
SELECT 'customers' as table_name, COUNT(*) as null_count
FROM ms_customers WHERE branch_id IS NULL
UNION ALL
SELECT 'quotations', COUNT(*)
FROM tr_quotations WHERE branch_id IS NULL
UNION ALL
SELECT 'contacts', COUNT(*)
FROM ms_customer_contacts WHERE branch_id IS NULL;

🚀 Next Phase

Phase 2: Branch Middleware (ElysiaJS)

• Create branch validation middleware
• Implement Keycloak group mapping
• Add error handling for unauthorized access

📝 Known Issues Resolved

• ✅ Fixed integer/UUID type mismatches in all tables
• ✅ Removed self-reference circular dependency in quotations
• ✅ Ensured all foreign keys use correct types

✨ Success Criteria

• All tables use UUID primary keys
• All foreign keys are UUID
• Branch isolation implemented
• Dual customer codes supported
• Contact visibility system in place
• Multi-currency quotations ready
• Revision tracking enabled
• Migration script tested and verified
• Performance indexes created
• Historical integrity ensured

---

Phase 1 Status: ✅ COMPLETED Completion Date: 2026-04-23 Next Phase: Phase 2 - Branch Middleware