Distributed Review Index and Aggregation Engine Migration Plan
Plan a zero-downtime database migration and sharded search index re-architecture for high-scale review querying.
Deploy this template when your commerce application outgrows its legacy database and needs to migrate review queries and rating aggregates to a distributed search cluster. It provides a blueprint for dual-writing, backfilling, consistency auditing, and cutover.
Role: Lead Database & Search Infrastructure Engineer.
Context
- Source System: {{source_database}}
- Target Search Cluster: {{target_search_cluster}}
- Total Active SKUs: {{catalog_sku_count}}
- Historical Review Records: {{historical_review_count}}
- Peak Write Throughput: {{write_throughput_peak}}
- Data Consistency Requirement: {{consistency_requirement}}
Task
Formulate a production engineering migration plan to re-architect and cut over product review indexing and real-time rating aggregations from {{source_database}} to {{target_search_cluster}} without service downtime or aggregate drift.
Method
- Profile the read/write query distributions across {{catalog_sku_count}} to identify hot-shard risks and aggregation bottlenecks.
- Design index mapping and document sharding topology in {{target_search_cluster}} optimized for faceted filtering, verified-buyer weighting, and review sorting.
- Architect a dual-write and change data capture (CDC) pipeline to mirror active writes during migration at {{write_throughput_peak}}.
- Design a chunked historical backfill mechanism with adaptive rate throttling to prevent resource exhaustion on {{source_database}}.
- Establish automated reconciliation and continuous checksum verification scripts to validate {{consistency_requirement}}.
- Construct real-time aggregate computation workers (bayesian averages, star-distribution buckets) with caching layers.
- Formulate a blue-green shadow querying strategy to validate latency and relevance metrics before production routing.
- Build an operational rollback and cutover execution runbook including telemetry alert thresholds.
Constraints
- Migration MUST maintain zero query downtime and zero lost customer review submissions.
- Aggregate recalculation jobs MUST NOT perform full-table scans during active traffic.
- Dual-write drift must be detected and corrected via automatic self-healing jobs.
- All shard allocation strategies must accommodate a minimum 3x organic growth projection.
Output format
- Section 1: Target Index Schema & Sharding Topology Specification
- Section 2: Dual-Write and Change Data Capture (CDC) Pipeline Architecture
- Section 3: Backfill Orchestration & Throttling Parameters (batch sizes, worker pools)
- Section 4: Data Consistency Verification & Automated Reconciliation Protocol
- Section 5: Step-by-Step Cutover, Shadowing, and Rollback Runbook
Self-review
- Are hot-partition mitigation strategies documented for high-review SKUs?
- Does the backfill throttling plan prevent replica lag on {{source_database}}?
- Are the reconciliation mathematical guarantees adequate for {{consistency_requirement}}?
Explicit role, a named task, and discrete steps the model can follow.
Background, inputs and variables the model needs before it starts.
Hard boundaries — what the model must and must not do.
A named, field-level shape for the response.
Ordered work items that force analysis before an answer.
Length and structure that travel across frontier models.
Signal density — instruction weight without padding.
Documented variables so the scaffold adapts to new inputs.
Quality bar, assumptions and behaviour when inputs are thin.
How much real usage the template has behind it.