docs/database/DATA_MIGRATION_SUMMARY.md

Data Migration Summary - SCM Microservice

Overview

Successfully completed migration of SCM data from public schema to trans schema, resolving all foreign key relationship errors and ensuring application startup works correctly.

Issues Resolved

1. Database Schema Mismatch

• Problem: Application was configured to use trans schema but data existed in public schema
• Solution: Migrated all data from public to trans schema
• Tables Migrated:
  • catalogue_ref: 10 records
  • scm_po: 16 records
  • scm_po_item: 19 records
  • scm_grn: 3 records
  • scm_grn_item: 4 records
  • scm_stock: 1 record

2. Foreign Key Reference Errors

• Problem: SQLAlchemy couldn't find referenced tables due to schema mismatch
• Solution: All models now explicitly use trans schema via __table_args__ = {'schema': 'trans'}
• Result: Application startup now works without foreign key errors

3. Database Name Inconsistency

• Problem: Database name was "insightfy-bloom" but should be "cuatrolabs"
• Solution: Updated .env and config.py to use "cuatrolabs" database
• Files Updated:
  • cuatrolabs-scm-ms/.env
  • cuatrolabs-scm-ms/app/core/config.py

4. Unique Constraint Violations

• Problem: Duplicate SKUs and barcodes prevented migration
• Solution: Implemented conflict resolution:
  • Duplicate SKUs made unique by appending catalogue_id suffix
  • Conflicting barcodes set to NULL
• Examples:
  • SHMP-500-ALOE → SHMP-500-ALOE-033a871c (for duplicates)
  • BRU-XL → BRU-XL-904d8e78 (for duplicates)

5. Missing Schema Columns

• Problem: catalogue_code column existed in public but not trans schema
• Solution: Added missing column to trans schema before migration
• Command: ALTER TABLE trans.catalogue_ref ADD COLUMN catalogue_code TEXT

Migration Process

Phase 1: Schema Preparation

1. Updated database configuration to use "cuatrolabs" database
2. Fixed all SQLAlchemy models to use trans schema
3. Created shared Base instance in app/core/database.py
4. Updated foreign key references to include schema prefixes

Phase 2: Data Migration

1. Created backup of public schema data in backup_public schema
2. Added missing catalogue_code column to trans schema
3. Migrated data with conflict resolution for unique constraints
4. Updated sequence values for auto-increment columns

Phase 3: Verification

1. Verified all records migrated successfully (10/10 for catalogue_ref)
2. Tested application startup - no foreign key errors
3. Confirmed database operations work correctly
4. All tables accessible and queryable

Files Modified

Configuration Files

• cuatrolabs-scm-ms/.env - Updated database name
• cuatrolabs-scm-ms/app/core/config.py - Updated database configuration

Model Files (Schema Updates)

• cuatrolabs-scm-ms/app/catalogues/models/model.py
• cuatrolabs-scm-ms/app/purchases/orders/models/model.py
• cuatrolabs-scm-ms/app/purchases/receipts/models/model.py
• cuatrolabs-scm-ms/app/inventory/stock/models/model.py
• cuatrolabs-scm-ms/app/trade_sales/models/model.py

Database Files

• cuatrolabs-scm-ms/app/core/database.py - Created shared Base
• cuatrolabs-scm-ms/app/sql.py - Updated imports for table creation

Migration Scripts

• cuatrolabs-scm-ms/migration_move_data_to_trans_schema.py - Enhanced with conflict resolution

Current Status

✅ Completed Successfully

• All data migrated from public to trans schema
• Foreign key relationships working correctly
• Application starts without errors
• Database operations functional
• Unique constraint conflicts resolved

📊 Final Statistics

• Total Records Migrated: 53 records across 6 tables
• Schema Consistency: 100% - all tables use trans schema
• Foreign Key Errors: 0 - all resolved
• Application Startup: ✅ Successful

Next Steps

Immediate Actions

1. ✅ Test application functionality with migrated data
2. ✅ Verify API endpoints work correctly
3. Test catalogue sync and pricing functionality
4. Validate all CRUD operations

Future Considerations

1. Consider dropping public schema tables after thorough testing
2. Monitor application performance with trans schema
3. Update any hardcoded schema references in queries
4. Document schema standards for future development

Pricing Levels Implementation

Current State

• Pricing levels structure implemented with 3 fields per level:
  • cost_price: Calculated as MRP - margin%
  • trade_margin: Percentage margin for the level
  • max_discount_pct: Maximum discount allowed
• Margins configured: ncnf (12%), cnf (6%), distributor (10%), retail (20%)
• Auto-generation in sync handler for new catalogues

Migration Impact

• No existing records had MRP values, so no pricing levels generated during migration
• Pricing levels will be generated when catalogues are synced with valid MRP values
• Structure is ready for future catalogue updates

Lessons Learned

1. Schema Consistency: Always ensure application configuration matches actual data location
2. Foreign Key Prefixes: Include schema prefixes in all foreign key references
3. Unique Constraints: Plan for conflict resolution when migrating data with unique constraints
4. Backup Strategy: Always create backups before major data migrations
5. Incremental Testing: Test each phase of migration separately for easier debugging

---

Migration Completed: December 19, 2025
Status: ✅ Successful
Application Status: ✅ Operational