Database per Service Migration Planning Prompt

This prompt is for teams that have already committed to a specific service boundary and need a concrete, phased plan to extract a set of tables from a shared database into a service-owned database. The ideal user is a backend engineer or architect who can provide a precise list of source tables, foreign key relationships, and the target service name. The prompt's job is not to debate whether the extraction is a good idea; it assumes the decision is made and focuses entirely on safe, sequenced execution. If you are still evaluating which tables belong to which service, use the Cross-Service Data Ownership Analysis Prompt first.

Before running this prompt, you must have a clear inventory of the tables to migrate, including their foreign key dependencies both within and outside the extraction set. The prompt requires you to specify a target service name, a list of tables, and any known cross-service foreign keys that will need to be broken or replaced with API calls. It will produce a phased migration plan that includes dual-write consistency windows, data synchronization checkpoints, and explicit rollback conditions for each phase. The output is designed to be reviewed by a database administrator and the owning engineering team before any migration step is executed in production.

Do not use this prompt for greenfield schema design, for evaluating whether a service boundary is correct, or for migrations where you cannot tolerate any dual-write period. This prompt assumes a shared database that is currently live and serving traffic; it does not cover offline data warehouse migrations or ETL-only pipelines. If your migration involves regulated data subject to strict consistency guarantees, you must add a human review gate after each phase and validate the plan against your specific compliance requirements before execution.

The prompt below is designed to be pasted directly into your AI harness. It expects you to replace square-bracket placeholders with your specific system context, including the current shared schema, the target service boundaries, and your organization's risk tolerance. The prompt instructs the model to produce a phased migration plan rather than a single-step cutover, which is critical for maintaining data integrity during the transition.

codeCopy
You are a database migration architect specializing in decomposing shared databases into service-owned databases. Your task is to produce a phased migration plan for extracting [TARGET_SERVICE] from the shared database described below.

## Current State
- Shared database schema (DDL or table descriptions): [SHARED_SCHEMA]
- Service to extract: [TARGET_SERVICE]
- Tables currently accessed by [TARGET_SERVICE]: [TARGET_TABLES]
- Other services sharing these tables: [DEPENDENT_SERVICES]
- Total row counts and write throughput per table: [TABLE_METRICS]
- Acceptable downtime window (if any): [DOWNTIME_WINDOW]
- Maximum acceptable dual-write latency: [MAX_LATENCY_MS]

## Constraints
- Risk tolerance level: [RISK_LEVEL: LOW | MEDIUM | HIGH]
- Must preserve referential integrity for: [CRITICAL_FOREIGN_KEYS]
- Rollback must be possible within: [ROLLBACK_WINDOW_HOURS]
- Data consistency validation method: [VALIDATION_METHOD: CHECKSUM | ROW_COUNT | FULL_DIFF | APPLICATION_LEVEL]
- Regulatory or compliance constraints: [COMPLIANCE_REQUIREMENTS]

## Output Schema
Produce a JSON object with this exact structure:
{
  "migration_phases": [
    {
      "phase_number": integer,
      "phase_name": string,
      "objective": string,
      "duration_estimate_hours": integer,
      "steps": [
        {
          "step_number": integer,
          "action": string,
          "sql_or_command": string | null,
          "verification_query": string,
          "expected_result": string,
          "rollback_action": string
        }
      ],
      "foreign_key_impacts": [
        {
          "constraint_name": string,
          "source_table": string,
          "target_table": string,
          "resolution_strategy": "DROP_AND_RECREATE" | "APPLICATION_LEVEL" | "DEFERRED" | "KEEP_IN_SHARED",
          "risk_level": "LOW" | "MEDIUM" | "HIGH",
          "mitigation": string
        }
      ],
      "dual_write_config": {
        "enabled": boolean,
        "tables_in_scope": [string],
        "consistency_window_ms": integer,
        "conflict_resolution": "LAST_WRITE_WINS" | "SOURCE_OF_TRUTH" | "MANUAL" | null,
        "reconciliation_query": string | null
      },
      "data_sync_checkpoint": {
        "sync_method": "FULL_DUMP" | "CDC" | "LOG_REPLAY" | "DUAL_WRITE_BACKFILL",
        "verification_query": string,
        "sync_complete_criteria": string
      },
      "rollback_conditions": [string],
      "go_no_go_criteria": [string]
    }
  ],
  "pre_migration_checks": [string],
  "post_migration_validation": [string],
  "rollback_plan": {
    "trigger_conditions": [string],
    "full_rollback_steps": [string],
    "estimated_rollback_time_minutes": integer,
    "data_loss_risk": "NONE" | "MINIMAL" | "MODERATE" | "HIGH"
  },
  "risk_register": [
    {
      "risk_id": string,
      "description": string,
      "likelihood": "LOW" | "MEDIUM" | "HIGH",
      "impact": "LOW" | "MEDIUM" | "HIGH",
      "mitigation": string,
      "detection_method": string
    }
  ]
}

## Instructions
1. Phase the migration so that no single phase introduces an unrecoverable state.
2. For every foreign key that crosses the extraction boundary, propose a concrete resolution strategy with verification.
3. Include dual-write periods where necessary, specifying the consistency window and conflict resolution approach.
4. Define clear go/no-go criteria at each phase boundary.
5. Ensure the rollback plan is testable and includes data integrity verification after rollback.
6. Flag any table that cannot be safely extracted without application-level changes and recommend those changes.
7. If [RISK_LEVEL] is LOW, prefer simpler approaches even if they take longer. If HIGH, prioritize speed with explicit acceptance of data risk.

To adapt this prompt, replace each square-bracket placeholder with concrete values from your system. The [SHARED_SCHEMA] placeholder should include enough DDL or table descriptions for the model to identify foreign key relationships. If you don't have exact [TABLE_METRICS], provide order-of-magnitude estimates—row counts and write throughput directly affect the feasibility of dump-and-restore versus CDC-based sync strategies. The [RISK_LEVEL] parameter is not cosmetic; it changes the model's trade-off calculus between safety and speed throughout the plan.

Before running this prompt in production, validate the output against your actual schema by diffing the model's foreign key impact list against a database introspection tool. The model may hallucinate constraint names or miss implicit relationships encoded in application logic rather than DDL. Always pair this prompt's output with a human review step that verifies every proposed DROP_AND_RECREATE action against your actual migration tooling and deployment pipeline. For high-risk extractions, run the generated verification queries in a staging environment before executing any phase in production.

This prompt is designed to be called from an application layer that manages the full lifecycle of a migration planning request. The harness should accept a structured input payload containing the current shared schema, the proposed service boundaries, and the target service ownership map. Because the output is a multi-phase migration plan with foreign key impact analysis and rollback conditions, the harness must validate the plan's structural integrity before presenting it to the engineering team. Treat this prompt as a planning engine whose output must pass deterministic checks before it becomes an actionable document.

The implementation should wrap the LLM call in a validation pipeline. First, parse the model output into a typed object with fields for phases (ordered list), foreign_key_impacts (per-phase list of affected relationships), dual_write_windows (service pairs and consistency mode), sync_checkpoints (data validation queries), and rollback_conditions (per-phase safety gates). Validate that every extracted table appears in exactly one service's ownership scope, that no phase references a table before its foreign key dependencies are resolved in prior phases, and that every dual-write window includes both a start condition and a consistency verification query. If validation fails, retry the prompt once with the specific validation errors injected into the [CONSTRAINTS] block. After two failures, escalate to a human reviewer with the partial plan and the error log. Log every attempt, the raw output, the validation result, and the final accepted plan for auditability.

Model choice matters here. Use a model with strong reasoning capabilities and a large context window, as the prompt must ingest the full shared schema, service boundaries, and ownership map in a single request. If the schema exceeds the context window, pre-process it by extracting only table names, columns, foreign keys, and row count estimates, omitting index definitions and storage parameters. For production use, store the accepted migration plan as a versioned artifact in the same repository as the service code, and run the sync checkpoints as automated data validation tests during each extraction phase. Never execute a phase without confirming that its rollback conditions are still valid given the current production state.