Change Data Capture (CDC) and Tenant Migration Tutorial¶
Version: 3.2.0 Last Updated: December 12, 2025 Difficulty: Advanced
Table of Contents¶
- Introduction
- Part 1: Change Data Capture (CDC)
- Understanding CDC
- Viewing CDC Events
- Exporting CDC Data
- CDC Use Cases
- Part 2: Tenant Migration
- Understanding Migration
- Migration Workflow
- Monitoring Migration
- Migration Strategies
- Advanced Topics
- Troubleshooting
- Best Practices
Introduction¶
This tutorial covers two advanced multi-tenancy features in HeliosDB-Lite:
-
Change Data Capture (CDC): Automatically tracks all data modifications (INSERT, UPDATE, DELETE) for audit trails, replication, and compliance.
-
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-Lite v3.2.0 or later
- Completed 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¶
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¶
Shows last 50 events.
Show All Events¶
Use a large number to see all recorded events (limited by CDC log retention).
Step-by-Step: Tracking Changes¶
Step 1: Setup Test Environment¶
-- 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¶
-- 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¶
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¶
Output File: changes.json in current directory
File Structure:
[
{
"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:
-- 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:
-- 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:
\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:
-- 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:
-- 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
\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
3. Verify RLS Policies
-- 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
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 Status
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
-- 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
-- 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
-- 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
-- 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
-- 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:
Advanced Migration Scenarios¶
Scenario 1: Upgrading Tenant Plan¶
Objective: Move tenant from Free to Enterprise plan
-- 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
-- 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
-- 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
-- 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.
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
2. Increase Batch Size (Conceptual - configuration)
3. Index Optimization
-- 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)
-- 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 Status
2. Verify Source Intact
3. Retry Migration
4. Manual Rollback
-- 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¶
Causes: 1. No changes made yet 2. CDC not enabled 3. Wrong tenant context
Solution:
-- 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¶
Causes: 1. No write permission in directory 2. File already exists and is read-only
Solution:
# 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"¶
Causes: 1. Resource contention 2. Target tenant doesn't exist 3. Migration queue backed up
Solution:
-- Verify target exists
\tenant list | grep target-tenant
-- Check system resources
\stats
-- Cancel and retry
# (Conceptual - would need cancel command)
Problem: Migration State "Failed"¶
Causes: 1. Target missing tables 2. Schema differences 3. RLS policy conflicts
Solution:
-- 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¶
Causes: 1. Migration incomplete 2. RLS filtering on target 3. Ongoing writes during migration
Solution:
-- 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¶
# Daily CDC backup script
#!/bin/bash
DATE=$(date +%Y%m%d)
TENANT="acme-corp"
heliosdb-repl <<EOF
\tenant use $TENANT
\tenant cdc-export /backups/cdc_${TENANT}_${DATE}.json
\q
EOF
2. Monitor CDC Growth¶
-- Check CDC log size
\tenant use my-tenant
\tenant cdc-show 99999
-- Count events in output
-- If too large, consider archiving old events
\tenant cdc-export archive_2025_q1.json
-- (Then purge old events - conceptual, would need purge command)
3. Include CDC in Disaster Recovery¶
Recovery Plan: 1. Restore last database backup 2. Import CDC exports to replay changes 3. Verify data integrity
Migration Best Practices¶
1. Pre-Migration Checklist¶
- [ ] Backup source tenant data
- [ ] Export CDC log:
\tenant cdc-export pre-migration.json - [ ] Document current schema:
\d <tables> - [ ] 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 <file> |
Export to JSON |
Migration Commands¶
| Command | Purpose |
|---|---|
\tenant migrate-to <target> |
Start migration |
\tenant migrate-status [tenant] |
Check progress |
Migration States¶
Example Workflows¶
Complete Migration Workflow¶
-- 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¶
-- 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
- Learn More: Read the Multi-Tenancy Implementation Report
- RLS Integration: Review RLS Policy Management Tutorial
Additional Resources¶
- Multi-Tenancy Test Coverage
- Quick Test Guide
- 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