Our legacy subscription system allowed multiple active contracts for the same customer with overlapping date ranges. We’re migrating 8,000+ subscription contracts to CloudSuite, but the staging table enforces unique key constraints that reject overlapping active periods for the same customer-product combination. The error we’re getting:
ERROR: Duplicate key violation on SUBSCRIPTION_STAGING
Key: CUSTOMER_ID + PRODUCT_ID + STATUS='ACTIVE'
Conflict: Contract SC-4521 overlaps with SC-4198
Manual fixes aren’t feasible for this volume. Our legacy contracts legitimately have overlaps - customers could have multiple subscriptions to the same product with different terms. How do others handle this? Do we need to merge overlapping contracts or can we adjust the staging table constraints?
Be careful with date consolidation. If you’re just merging contracts, you might lose important audit trail information about when each subscription was actually purchased. Consider using CloudSuite’s contract history feature to preserve this data.
In our legacy system, customers could purchase the same product multiple times with different billing cycles or discount structures. For example, a customer might have an annual subscription that auto-renewed, then purchase an additional quarterly subscription during a promotion. Both would be active simultaneously.
Let me provide a comprehensive solution addressing all three challenges you’re facing.
Legacy Contracts with Overlapping Dates: Your legacy system’s flexibility created a data structure that doesn’t map directly to CloudSuite’s model. The key is understanding that CloudSuite uses a hierarchical contract structure: Contract Header > Contract Lines > Pricing Tiers. What you had as separate contracts should become lines within a single contract. Here’s the transformation approach:
- Identify all overlapping contract sets using the SQL query shared earlier
- For each customer-product combination with overlaps, designate the earliest contract as the “master” contract
- Convert subsequent overlapping contracts into additional contract lines under the master
- Preserve original contract numbers in a custom field (CONTRACT_LINE_LEGACY_REF) for audit purposes
- Adjust start/end dates so lines don’t overlap but maintain continuous coverage
Staging Table Enforces Unique Keys: The constraint exists because CloudSuite’s subscription engine processes renewals, billing, and usage tracking based on unique active subscriptions. Attempting to bypass this will cause downstream issues in billing cycles. Your migration script needs to:
// Pseudocode - Overlap resolution logic:
1. Query legacy system for customer-product groups with COUNT(*) > 1
2. For each group, sort contracts by start_date ASC
3. Create master contract header from earliest record
4. For each subsequent contract in group:
a. Create contract line with unique line_number
b. Map original contract fields to line-level attributes
c. Set line start_date = MAX(previous_line_end_date + 1, original_start_date)
d. Preserve original dates in custom fields for reference
5. Calculate consolidated end_date as MAX of all original end_dates
6. Set master contract status based on current date vs end_date
Manual Fixes Not Feasible for Large Data Sets: Absolutely correct. With 8,000+ contracts, automation is essential. Build a transformation layer that runs before staging table load:
Phase 1 - Analysis (run first, validate results):
- Extract all subscription data to temporary tables
- Run overlap detection queries
- Generate consolidation mapping tables
- Review mapping with business users (sample 100 records)
Phase 2 - Transformation:
- Execute consolidation logic via batch processing
- Handle edge cases (gaps between contracts, status mismatches)
- Generate consolidated contract headers and lines
- Validate transformed data against business rules
Phase 3 - Staging Load:
- Load contract headers to staging table
- Load contract lines to line staging table
- Run CloudSuite’s staging validation
- Review and fix any remaining errors
Critical considerations:
- Preserve billing history by linking to original contract numbers
- Handle pro-rated amounts for mid-period consolidations
- Update customer communications with new contract references
- Plan for a parallel run period where both systems maintain contract data
For your 8,000 contracts, expect 15-20% to have overlaps based on typical patterns. Budget 1 week for script development, 3 days for testing with sample data, and 2 days for full migration execution. Use batch sizes of 500 contracts to allow for incremental validation and rollback if needed.
The staging table constraint is actually protecting your data integrity. CloudSuite’s subscription model doesn’t support true overlaps for the same customer-product-status combination because it would create ambiguity in billing and renewal processing. You’ll need to restructure your data before migration. Can you share more details about why your legacy system allowed overlaps? That might help us find the right approach.
Here’s a SQL snippet to find your overlaps:
SELECT customer_id, product_id, COUNT(*) as overlap_count
FROM legacy_subscriptions
WHERE status = 'ACTIVE'
GROUP BY customer_id, product_id
HAVING COUNT(*) > 1;
This will show you exactly which customer-product combinations have multiple active contracts.
That’s a common pattern in older systems. CloudSuite handles this differently using subscription lines and add-ons rather than separate contracts. You should consolidate overlapping contracts into a single contract with multiple lines. Each line can have different terms, billing cycles, and pricing. This maintains the business logic while conforming to the staging table constraints. Your migration script will need to identify overlaps and create the consolidated structure.
I’d recommend a pre-migration analysis query to identify all overlap scenarios. Group by customer and product, then check for date range intersections. This will give you a clear picture of how many contracts need consolidation. You might find that 80% of your overlaps fall into just 2-3 patterns, which makes scripting the consolidation much easier.