--- title: "Change Data Capture (CDC) and Tenant Migration Tutorial" --- # Change Data Capture (CDC) and Tenant Migration Tutorial **Difficulty:** Advanced --- ## Table of Contents 1. [Introduction](#introduction) 2. [Part 1: Change Data Capture (CDC)](#part-1-change-data-capture-cdc) - [Understanding CDC](#understanding-cdc) - [Viewing CDC Events](#viewing-cdc-events) - [Exporting CDC Data](#exporting-cdc-data) - [CDC Use Cases](#cdc-use-cases) 3. [Part 2: Tenant Migration](#part-2-tenant-migration) - [Understanding Migration](#understanding-migration) - [Migration Workflow](#migration-workflow) - [Monitoring Migration](#monitoring-migration) - [Migration Strategies](#migration-strategies) 4. [Advanced Topics](#advanced-topics) 5. [Troubleshooting](#troubleshooting) 6. [Best Practices](#best-practices) --- ## Introduction This tutorial covers two advanced multi-tenancy features in HeliosDB Nano: 1. **Change Data Capture (CDC):** Automatically tracks all data modifications (INSERT, UPDATE, DELETE) for audit trails, replication, and compliance. 2. **Tenant Migration:** Safely moves tenant data between isolation levels or instances with zero downtime. ### What You'll Learn - How to view and export change events - Understanding CDC event structure - Planning and executing tenant migrations - Monitoring migration progress - Handling migration failures ### Prerequisites - HeliosDB Nano, current release - Completed [RLS Policy Management Tutorial](https://www.heliosdb.com/docs/nano/guides/rls_policy_management_tutorial/) - Understanding of tenant contexts - Familiarity with SQL transactions --- ## Part 1: Change Data Capture (CDC) ### Understanding CDC **Change Data Capture** automatically logs every data modification in a tenant's tables. Each change is recorded with: - **Event ID:** Unique identifier - **Change Type:** INSERT, UPDATE, or DELETE - **Table Name:** Which table was modified - **Row Key:** Primary key of affected row - **Old Values:** Data before change (UPDATE/DELETE) - **New Values:** Data after change (INSERT/UPDATE) - **Tenant ID:** Which tenant made the change - **Timestamp:** When the change occurred - **Transaction ID:** Groups related changes ### Why Use CDC? | Use Case | Description | |----------|-------------| | **Audit Compliance** | Track who changed what and when | | **Data Replication** | Sync changes to analytics databases | | **Event Sourcing** | Reconstruct state at any point in time | | **Migration** | Replay changes during tenant migration | | **Debugging** | Understand data evolution | | **Rollback** | Undo unintended changes | --- ### Viewing CDC Events #### Basic Usage ```sql \tenant cdc-show ``` **Default:** Shows last 10 events for current tenant. **Output:** ``` CDC Events for tenant 'acme-corp': ──────────────────────────────────────────────────────────── Timestamp Type Table Row Key ──────────────────────────────────────────────────────────── 2025-12-12 10:30:15 INSERT customers 1 2025-12-12 10:31:22 UPDATE customers 1 2025-12-12 10:32:45 INSERT orders 101 2025-12-12 10:33:10 UPDATE orders 101 2025-12-12 10:35:00 DELETE orders 101 ──────────────────────────────────────────────────────────── ``` #### Show More Events ```sql \tenant cdc-show 50 ``` Shows last 50 events. #### Show All Events ```sql \tenant cdc-show 1000 ``` Use a large number to see all recorded events (limited by CDC log retention). --- ### Step-by-Step: Tracking Changes #### Step 1: Setup Test Environment ```sql -- Create tenant \tenant create demo-company free -- Create test table CREATE TABLE products ( id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, name TEXT, price DECIMAL, stock INTEGER ); -- Switch to tenant context \tenant use demo-company ``` #### Step 2: Make Some Changes ```sql -- Insert a product INSERT INTO products (id, tenant_id, name, price, stock) VALUES (1, 'demo-company', 'Widget Pro', 99.99, 100); -- Update the product UPDATE products SET price = 89.99 WHERE id = 1; -- Update stock UPDATE products SET stock = 95 WHERE id = 1; -- Delete (if needed) -- DELETE FROM products WHERE id = 1; ``` #### Step 3: View CDC Log ```sql \tenant cdc-show 10 ``` **Expected Output:** ``` CDC Events for tenant 'demo-company': ──────────────────────────────────────────────────────────── Timestamp Type Table Row Key ──────────────────────────────────────────────────────────── 2025-12-12 14:20:15 INSERT products 1 2025-12-12 14:21:30 UPDATE products 1 2025-12-12 14:22:10 UPDATE products 1 ──────────────────────────────────────────────────────────── ``` **Analysis:** - First event: Product creation (INSERT) - Second event: Price change (UPDATE) - Third event: Stock adjustment (UPDATE) --- ### Exporting CDC Data #### Export to JSON ```sql \tenant cdc-export changes.json ``` **Output File:** `changes.json` in current directory **File Structure:** ```json [ { "event_id": 1, "change_type": "Insert", "table_name": "products", "row_key": "1", "old_values": null, "new_values": "{\"id\":1,\"tenant_id\":\"demo-company\",\"name\":\"Widget Pro\",\"price\":99.99,\"stock\":100}", "tenant_id": "550e8400-e29b-41d4-a716-446655440001", "timestamp": "2025-12-12 14:20:15", "transaction_id": 42 }, { "event_id": 2, "change_type": "Update", "table_name": "products", "row_key": "1", "old_values": "{\"id\":1,\"tenant_id\":\"demo-company\",\"name\":\"Widget Pro\",\"price\":99.99,\"stock\":100}", "new_values": "{\"id\":1,\"tenant_id\":\"demo-company\",\"name\":\"Widget Pro\",\"price\":89.99,\"stock\":100}", "tenant_id": "550e8400-e29b-41d4-a716-446655440001", "timestamp": "2025-12-12 14:21:30", "transaction_id": 43 }, { "event_id": 3, "change_type": "Update", "table_name": "products", "row_key": "1", "old_values": "{\"id\":1,\"tenant_id\":\"demo-company\",\"name\":\"Widget Pro\",\"price\":89.99,\"stock\":100}", "new_values": "{\"id\":1,\"tenant_id\":\"demo-company\",\"name\":\"Widget Pro\",\"price\":89.99,\"stock\":95}", "tenant_id": "550e8400-e29b-41d4-a716-446655440001", "timestamp": "2025-12-12 14:22:10", "transaction_id": 44 } ] ``` --- ### CDC Use Cases #### Use Case 1: Audit Trail for Compliance **Scenario:** Financial application needs to track all monetary transactions. **Workflow:** ```sql -- Perform operations \tenant use finance-corp INSERT INTO transactions (id, tenant_id, amount, type) VALUES (1, 'finance-corp', 10000, 'deposit'); UPDATE transactions SET amount = 15000 WHERE id = 1; -- Export audit trail \tenant cdc-export audit_2025_12.json -- Review changes \tenant cdc-show 100 ``` **Compliance Report:** - What: Transaction #1 modified - When: 2025-12-12 14:20:15 - Old Value: $10,000 - New Value: $15,000 - Who: tenant 'finance-corp' --- #### Use Case 2: Data Synchronization **Scenario:** Sync production database changes to analytics warehouse. **Workflow:** ```sql -- Export changes hourly \tenant use analytics-tenant \tenant cdc-export /exports/changes_$(date +%Y%m%d_%H).json -- Process in analytics pipeline # External script: # 1. Read changes.json # 2. Transform to warehouse schema # 3. Apply to analytics DB ``` --- #### Use Case 3: Debugging Data Issues **Scenario:** Customer reports incorrect data. Need to trace how it happened. **Investigation:** ```sql \tenant use customer-corp -- Show recent changes to problematic table \tenant cdc-show 50 -- Look for pattern: -- - What changed? -- - When did it change? -- - Was it bulk update or individual changes? ``` **Example Finding:** ``` 2025-12-12 10:00:00 UPDATE customers 1 (price corrected) 2025-12-12 10:00:01 UPDATE customers 2 (price corrected) 2025-12-12 10:00:02 UPDATE customers 3 (price corrected) ... 2025-12-12 10:00:50 UPDATE customers 50 (price corrected) Conclusion: Bulk update script ran at 10:00 AM ``` --- #### Use Case 4: Rollback Capability **Scenario:** Accidentally deleted critical data. Need to restore. **Recovery Process:** ```sql -- 1. Export CDC to find deleted records \tenant cdc-export deleted_data.json -- 2. Filter for DELETE events in JSON # Look for: "change_type": "Delete" -- 3. Extract old_values from DELETE events -- 4. Reconstruct INSERT statements INSERT INTO customers (id, tenant_id, name, email) VALUES (extracted from old_values); ``` --- #### Use Case 5: Change Tracking Dashboard **Scenario:** Real-time dashboard showing tenant activity. **Implementation:** ```sql -- Export recent events \tenant cdc-export recent_activity.json -- Parse in application # Read JSON # Count by change_type: {"inserts": 50, "updates": 120, "deletes": 5} # Count by table: {"customers": 75, "orders": 100} # Display in UI dashboard ``` --- ## Part 2: Tenant Migration ### Understanding Migration **Tenant Migration** is the process of moving a tenant's data from one location to another while maintaining: - Data integrity - Zero downtime - Consistent state - Rollback capability ### Migration States | State | Description | |-------|-------------| | **Pending** | Migration queued, not started | | **Snapshotting** | Taking initial data snapshot | | **Replicating** | Applying ongoing changes via CDC | | **Verifying** | Validating data consistency | | **Completed** | Migration successful | | **Failed** | Migration encountered error | | **Paused** | Migration temporarily halted | --- ### Migration Workflow #### Phase 1: Pre-Migration Planning **1. Assess Current State** ```sql \tenant info source-tenant -- Check data volume \tenant use source-tenant SELECT COUNT(*) FROM customers; SELECT COUNT(*) FROM orders; -- Check quota usage \tenant quota source-tenant ``` **2. Create Target Tenant** ```sql \tenant create target-tenant pro ``` **3. Verify RLS Policies** ```sql -- List policies on source \tenant rls list customers \tenant rls list orders -- Recreate on target (if needed) \tenant use target-tenant \tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL \tenant rls create orders tenant_isolation tenant_id=current_tenant() ALL ``` --- #### Phase 2: Initiate Migration **Start Migration** ```sql \tenant use source-tenant \tenant migrate-to target-tenant ``` **Expected Output:** ``` Migration initiated successfully ──────────────────────────────────── Source: source-tenant (550e8400-e29b-41d4-a716-446655440001) Target: target-tenant (650e8400-e29b-41d4-a716-446655440002) State: Pending Progress: 0 / 0 changes replicated ──────────────────────────────────── ``` --- #### Phase 3: Monitor Progress **Check Migration State** ```sql \tenant migrate-status source-tenant ``` **Output During Snapshotting:** ``` Migration Status: source-tenant → target-tenant ──────────────────────────────────────────────── State: Snapshotting Started: 2025-12-12 15:00:00 Elapsed: 00:02:30 Progress: 0 / 0 changes replicated Snapshot: In progress... ──────────────────────────────────────────────── ``` **Output During Replication:** ``` Migration Status: source-tenant → target-tenant ──────────────────────────────────────────────── State: Replicating Started: 2025-12-12 15:00:00 Elapsed: 00:15:45 Progress: 1,250 / 1,250 changes replicated (100%) Lag: < 1 second ──────────────────────────────────────────────── ``` **Output After Completion:** ``` Migration Status: source-tenant → target-tenant ──────────────────────────────────────────────── State: Completed Started: 2025-12-12 15:00:00 Completed: 2025-12-12 15:20:30 Duration: 00:20:30 Total Changes: 1,250 replicated Verification: ✓ Passed ──────────────────────────────────────────────── ``` --- #### Phase 4: Verification **1. Compare Row Counts** ```sql -- Source tenant \tenant use source-tenant SELECT COUNT(*) as source_count FROM customers; SELECT COUNT(*) as source_count FROM orders; -- Target tenant \tenant use target-tenant SELECT COUNT(*) as target_count FROM customers; SELECT COUNT(*) as target_count FROM orders; -- Should be equal ``` **2. Spot Check Data** ```sql -- Compare sample records \tenant use source-tenant SELECT * FROM customers WHERE id IN (1, 10, 100); \tenant use target-tenant SELECT * FROM customers WHERE id IN (1, 10, 100); -- Should match exactly ``` **3. Verify Latest Changes** ```sql -- Check CDC on target \tenant use target-tenant \tenant cdc-show 10 -- Should show recent replicated events ``` --- #### Phase 5: Cutover **1. Pause Application Writes** ``` # In your application: # 1. Enable read-only mode for source tenant # 2. Wait for active transactions to complete # 3. Verify no pending writes ``` **2. Final Synchronization** ```sql -- Check status one last time \tenant migrate-status source-tenant -- Should show: State: Completed -- And: Lag: 0 seconds ``` **3. Switch Application** ``` # In your application: # 1. Update tenant_id reference: source-tenant → target-tenant # 2. Enable writes on target # 3. Monitor for errors ``` **4. Cleanup** ```sql -- After confirming success, optionally delete source \tenant delete source-tenant -- Or keep for rollback capability (recommended for 7-30 days) ``` --- ### Migration Strategies #### Strategy 1: Live Migration (Zero Downtime) **Best For:** Production tenants that can't afford downtime **Process:** 1. Start migration while source is active 2. Snapshot initial state 3. Continuously replicate changes via CDC 4. Cutover when lag < 1 second 5. No application downtime **Timeline:** ``` Source Active ████████████████████████████████████████ Migration ───────▶ Snapshot ▶ Replicate ▶ Cutover Target Ready ─────────────────────────────────▶ █████ ``` --- #### Strategy 2: Scheduled Migration (Maintenance Window) **Best For:** Non-critical tenants or planned maintenance **Process:** 1. Schedule maintenance window 2. Pause writes to source 3. Perform migration 4. Verify data 5. Resume on target **Timeline:** ``` Maintenance: 02:00 - 04:00 AM 01:59 Announce maintenance 02:00 Pause writes 02:01 Start migration 02:30 Migration complete 02:35 Verification 02:40 Cutover to target 02:45 Resume writes ``` --- #### Strategy 3: Test-and-Cutover **Best For:** High-risk migrations, large tenants **Process:** 1. Migrate to target 2. Keep source active 3. Test target extensively 4. If issues: rollback to source 5. If success: scheduled cutover **Timeline:** ``` Week 1: Migration Week 2: Parallel testing Week 3: Scheduled cutover ``` --- ### Advanced Migration Scenarios #### Scenario 1: Upgrading Tenant Plan **Objective:** Move tenant from Free to Enterprise plan ```sql -- Check current plan \tenant info budget-tenant -- Output: Plan: Free -- Create enterprise target \tenant create budget-enterprise enterprise -- Migrate data \tenant use budget-tenant \tenant migrate-to budget-enterprise -- Monitor \tenant migrate-status budget-tenant -- After completion, cutover application -- Then delete old tenant \tenant delete budget-tenant ``` --- #### Scenario 2: Multi-Region Migration **Objective:** Move tenant from US region to EU region ```sql -- US Region Database \tenant use us-customer \tenant cdc-export /sync/us-customer-snapshot.json -- Transfer file to EU region # scp /sync/us-customer-snapshot.json eu-server:/sync/ -- EU Region Database \tenant create eu-customer enterprise -- Import from snapshot (conceptual - would need import tool) # heliosdb-import --tenant eu-customer --file /sync/us-customer-snapshot.json -- Continue live replication \tenant use us-customer \tenant migrate-to eu-customer@eu-region ``` --- #### Scenario 3: Splitting Tenant **Objective:** Split large tenant into multiple smaller tenants ```sql -- Original tenant has mixed departments \tenant use megacorp SELECT COUNT(*) FROM employees WHERE department = 'Sales'; -- 500 SELECT COUNT(*) FROM employees WHERE department = 'Engineering'; -- 800 -- Create department-specific tenants \tenant create megacorp-sales pro \tenant create megacorp-eng pro -- Selective migration (conceptual - would need filtering) -- Option 1: Export + filter + import \tenant cdc-export megacorp-full.json # Filter JSON: sales_data.json (department = 'Sales') # Import to megacorp-sales -- Option 2: Manual data copy \tenant clear -- Admin mode INSERT INTO employees SELECT * FROM employees WHERE department = 'Sales' AND tenant_id = 'megacorp' -- Update tenant_id to 'megacorp-sales' ``` --- #### Scenario 4: Consolidating Tenants **Objective:** Merge multiple small tenants into one ```sql -- Small tenants \tenant list -- Output: small-1, small-2, small-3 -- Create consolidated tenant \tenant create consolidated-tenant pro -- Migrate each \tenant use small-1 \tenant migrate-to consolidated-tenant \tenant use small-2 \tenant migrate-to consolidated-tenant \tenant use small-3 \tenant migrate-to consolidated-tenant -- Verify total data \tenant use consolidated-tenant SELECT COUNT(*) FROM customers; -- Should equal: small-1 + small-2 + small-3 ``` --- ## Advanced Topics ### CDC Event Filtering **Problem:** Too many events, want specific changes. **Solution:** Export and filter JSON programmatically. ```python import json # Load CDC export with open('changes.json') as f: events = json.load(f) # Filter: Only DELETE events deletes = [e for e in events if e['change_type'] == 'Delete'] # Filter: Only specific table customer_changes = [e for e in events if e['table_name'] == 'customers'] # Filter: Time range from datetime import datetime start = datetime(2025, 12, 1) end = datetime(2025, 12, 31) monthly_events = [ e for e in events if start <= datetime.fromisoformat(e['timestamp']) <= end ] ``` --- ### Migration Performance Tuning **Slow Migration?** Try these optimizations: **1. Check Network Latency** ```bash # Ping target server ping target-server # Should be < 50ms for good performance ``` **2. Increase Batch Size** (Conceptual - configuration) ```toml [migration] batch_size = 1000 # Default: 100 parallel_workers = 4 # Default: 1 ``` **3. Index Optimization** ```sql -- Ensure indexes exist on target \tenant use target-tenant CREATE INDEX idx_customers_tenant ON customers(tenant_id); CREATE INDEX idx_orders_tenant ON orders(tenant_id); ``` **4. Disable RLS During Migration** (Faster, but risky) ```sql -- Remove RLS temporarily \tenant rls delete target-table policy -- Run migration \tenant migrate-to target -- Re-enable RLS \tenant rls create target-table policy tenant_id=current_tenant() ALL ``` --- ### Rollback Procedures **If Migration Fails:** **1. Check State** ```sql \tenant migrate-status source-tenant -- Output: State: Failed - "Reason: Connection timeout" ``` **2. Verify Source Intact** ```sql \tenant use source-tenant SELECT COUNT(*) FROM customers; -- Should show original count ``` **3. Retry Migration** ```sql -- Fix underlying issue (network, permissions, etc.) -- Retry \tenant migrate-to target-tenant ``` **4. Manual Rollback** ```sql -- If target was partially populated \tenant use target-tenant DELETE FROM customers WHERE tenant_id = 'target-tenant'; DELETE FROM orders WHERE tenant_id = 'target-tenant'; -- Or delete entire target \tenant delete target-tenant ``` --- ## Troubleshooting ### CDC Issues #### Problem: No Events Showing ```sql \tenant cdc-show -- Output: No CDC events found ``` **Causes:** 1. No changes made yet 2. CDC not enabled 3. Wrong tenant context **Solution:** ```sql -- Verify tenant context \tenant current -- Make a test change INSERT INTO customers (id, tenant_id, name) VALUES (999, current_tenant(), 'Test'); -- Check again \tenant cdc-show ``` --- #### Problem: Export Fails ```sql \tenant cdc-export changes.json -- Error: Permission denied ``` **Causes:** 1. No write permission in directory 2. File already exists and is read-only **Solution:** ```bash # Check permissions ls -la changes.json # Remove old file rm changes.json # Or export to different location \tenant cdc-export /tmp/changes.json ``` --- ### Migration Issues #### Problem: Migration Stuck in "Pending" ```sql \tenant migrate-status source -- State: Pending (for 10 minutes) ``` **Causes:** 1. Resource contention 2. Target tenant doesn't exist 3. Migration queue backed up **Solution:** ```sql -- Verify target exists \tenant list | grep target-tenant -- Check system resources \stats -- Cancel and retry # (Conceptual - would need cancel command) ``` --- #### Problem: Migration State "Failed" ```sql \tenant migrate-status source -- State: Failed - "Table schema mismatch" ``` **Causes:** 1. Target missing tables 2. Schema differences 3. RLS policy conflicts **Solution:** ```sql -- Ensure target has same schema \tenant use target-tenant CREATE TABLE customers ( id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, name TEXT, email TEXT ); -- Retry migration \tenant use source-tenant \tenant migrate-to target-tenant ``` --- #### Problem: Data Mismatch After Migration ```sql -- Source: 1000 rows -- Target: 995 rows ``` **Causes:** 1. Migration incomplete 2. RLS filtering on target 3. Ongoing writes during migration **Solution:** ```sql -- Check migration state \tenant migrate-status source -- Should be "Completed", not "Replicating" -- Verify RLS not filtering \tenant use target-tenant \tenant clear -- Admin mode SELECT COUNT(*) FROM customers WHERE tenant_id = 'target-tenant'; -- If mismatch persists, re-run migration ``` --- ## Best Practices ### CDC Best Practices #### 1. Regular Exports ```bash # Daily CDC backup script #!/bin/bash DATE=$(date +%Y%m%d) TENANT="acme-corp" heliosdb-repl <` - [ ] Record row counts per table - [ ] List all RLS policies - [ ] Identify peak usage times (avoid these) - [ ] Notify users of scheduled migration - [ ] Prepare rollback plan --- #### 2. During Migration - [ ] Monitor status every 5 minutes - [ ] Watch for error messages - [ ] Check system resources (\stats) - [ ] Avoid making changes to source - [ ] Keep application in read-only mode --- #### 3. Post-Migration - [ ] Verify row counts match - [ ] Spot-check sample records - [ ] Run application smoke tests - [ ] Monitor performance for 24 hours - [ ] Keep source tenant for 7-30 days (rollback capability) - [ ] Update documentation - [ ] Notify users of completion --- #### 4. Migration Schedule Template ``` T-7 days: Announce migration to users T-3 days: Backup source tenant T-1 day: Final preparation, verify checklist T-0 hours: Begin migration T+1 hour: First verification T+6 hours: Monitor application performance T+24 hours: Full verification, smoke tests T+7 days: Delete source tenant (if successful) ``` --- ## Quick Reference ### CDC Commands | Command | Purpose | |---------|---------| | `\tenant cdc-show [limit]` | View recent changes | | `\tenant cdc-export ` | Export to JSON | ### Migration Commands | Command | Purpose | |---------|---------| | `\tenant migrate-to ` | Start migration | | `\tenant migrate-status [tenant]` | Check progress | ### Migration States ``` Pending → Snapshotting → Replicating → Verifying → Completed ↓ Failed / Paused ``` --- ## Example Workflows ### Complete Migration Workflow ```sql -- 1. Preparation \tenant create new-tenant pro \tenant use old-tenant \tenant cdc-export pre-migration-backup.json -- 2. Initiate \tenant migrate-to new-tenant -- 3. Monitor (run every 5 minutes) \tenant migrate-status old-tenant -- 4. Verify \tenant use old-tenant SELECT COUNT(*) FROM customers; -- Note count \tenant use new-tenant SELECT COUNT(*) FROM customers; -- Should match -- 5. Cutover -- (Update application to use new-tenant) -- 6. Cleanup (after 7-30 days) \tenant delete old-tenant ``` --- ### CDC Audit Workflow ```sql -- Daily audit export \tenant use finance-tenant \tenant cdc-export /audit/finance_$(date +%Y%m%d).json -- Review specific events \tenant cdc-show 100 -- Generate report # External script processes JSON: # - Count changes by type # - Identify high-activity periods # - Flag suspicious patterns ``` --- ## Next Steps - **Practice:** Try the SQL test scripts with CDC enabled - **RLS Integration:** Review [RLS Policy Management Tutorial](https://www.heliosdb.com/docs/nano/guides/rls_policy_management_tutorial/) --- ## Additional Resources - REPL Help: `\h tenants` --- **Need Help?** - Check migration status: `\tenant migrate-status` - View recent changes: `\tenant cdc-show 50` - Export for analysis: `\tenant cdc-export debug.json` **Report Issues:** [GitHub Issues](https://github.com/HeliosDatabase/HeliosDB-Nano/issues)