Product Management Sync & Migration

Author(s)

• Bishwanath Jana

Last Updated Date

2025-01-08

---

SRS References

• 2.1.2 Product Management System
• 2.1.3 Data Migration & Synchronization

---

Version History

Version	Date	Changes	Author
1.0	2025-01-08	Initial draft for sync & migration feature	Bishwanath Jana

---

Feature Overview

Objective:
Migrate existing flat product data (tblproduct) to new hierarchical structure (tblBaseProduct → tblProductVersion → tblCatalog → tblConfig) and establish real-time bidirectional synchronization to maintain backward compatibility.

Scope:

• One-time migration of all existing product data from tblproduct to new hierarchical tables
• Transparent sync mechanism where new product management operations automatically reflect in tblproduct through modified DAL methods
• Data integrity and consistency validation

Dependencies:

• PostgreSQL database with existing tblproduct structure
• New hierarchical tables: tblBaseProduct, tblProductVersion, tblCatalog, tblConfig, tblConfigPrice
• Existing ProductController with old and new version APIs
• ProductHelper and ProductDAL layers

---

Requirements

Functional Requirements

1. Data Migration: Migrate all existing products from tblproduct to new hierarchical structure
2. Transparent Sync: Any CRUD operation in new system automatically updates tblproduct within the same DAL transaction
3. Backward Compatibility: Old APIs must continue to work without modification
4. Data Integrity: Maintain referential integrity between old and new systems
5. Error Handling: Robust error handling with transaction rollback mechanisms

---

Design Specifications

Data Models

Sync Columns in tblproduct:

ALTER TABLE tblproduct ADD COLUMN ConfigId UUID ;
ALTER TABLE tblproduct ADD COLUMN CatalogId UUID;
ALTER TABLE tblproduct ADD COLUMN VersionId UUID;
ALTER TABLE tblproduct ADD COLUMN BaseProductId UUID;
ALTER TABLE tblproduct ADD COLUMN SyncStatus VARCHAR(20) DEFAULT 'active';

Modified DAL Methods:

DAL Method	Sync Action	Trigger Point
AddConfiguration	Auto-create record in tblproduct	After config insert
UpdateConfigStatus	Update status in tblproduct	After status update
AddPriceOfConfig	Update Amount in tblproduct	After price insert/update
UpdateConfig	Update corresponding tblproduct	After config modification
AddCatalog	Prepare for future sync operations	After catalog creation

Workflow

Migration Workflow:

1. Analyze existing tblproduct data patterns
2. Execute migration stored procedure directly in pgAdmin
3. Validate data integrity and migration completeness
4. Verify hierarchy relationships are correctly established

Sync Workflow:

1. User performs operation in new product management APIs
2. Primary operation executes in hierarchical tables within DAL transaction
3. Automatic sync logic in same DAL method updates tblproduct
4. Single transaction ensures consistency between hierarchical and flat structures
5. No additional API calls or external sync processes required
6. Built-in error handling with transaction rollback on sync failures

---

Development Tasks & Estimates

No	Task Name	Estimate (Hours)	Dependencies	Notes
1	Database Schema Changes	1.5 hours	Database Access	Add sync columns, indexes
2	Migration Stored Procedure	2.5 hours	Schema Changes	Complete data migration SP
3	DAL Sync Methods Integration	4 hours	Migration Complete	Modify existing DAL methods
4	Helper Method for Hierarchy Data	2 hours	DAL Methods	Support method for sync
5	Transaction Management Updates	2 hours	DAL Integration	Ensure atomicity
6	Testing & Validation	2.5 hours	Implementation Complete	Unit & integration tests
7	Documentation & Deployment	0.5 hour	Testing Complete	Documentation & deployment
8	Total	15 hours	-	~1.9 working days

---

Testing & Quality Assurance

Unit Tests

• Migration stored procedure validation
• Sync helper method testing
• Data integrity validation tests
• Error handling scenarios

Integration Tests

• End-to-end migration testing
• Real-time sync validation
• Performance testing for bulk operations
• Rollback and recovery testing

Acceptance Criteria

1. All existing products successfully migrated to new hierarchical structure (tblBaseProduct → tblProductVersion → tblCatalog → tblConfig)
2. New product operations transparently sync to tblproduct without additional API calls
3. Old APIs continue to work without modification
4. Data consistency maintained between old and new systems through atomic transactions

Testing Tools

• Swagger for API testing
• pgAdmin for database validation

---

Deployment Considerations

Configuration Changes

• Database connection strings for new tables
• Logging configuration for sync operations
• Feature toggles for gradual rollout

Rollout Plan

Phase 1: Migration (Maintenance Window)

• Execute migration stored procedure directly in pgAdmin
• Validate data integrity and migration completeness
• Verify sync columns are properly populated

Phase 2: DAL Integration

• Modify existing DAL methods to include sync logic
• Test sync functionality with new product operations
• Validate transaction atomicity

Phase 3: Full Deployment

• Complete rollout to production
• Monitor system health and sync performance
• Validate data consistency

---

Risks & Mitigations

Risk	Impact	Likelihood	Mitigation Strategy
Data Loss During Migration	High	Low	Comprehensive backup, transaction rollback
Sync Performance Issues	Medium	Medium	Async processing, batch operations, monitoring
Data Inconsistency	High	Low	Validation checks, integrity constraints

---

Review & Approval

Technical Review

• Reviewer: Senior Developer / Tech Lead
• Focus: Code quality, performance, architecture

Business Review

• Reviewer: Product Manager / Business Analyst
• Focus: Requirements compliance, user impact

Approval Date

[To be filled after review completion]

---

Notes
(Add any additional notes or considerations related to the feature development here.)