Safe Database Migration Prompt
Design and review production database migrations with evidence-based rollout, data protection, rollback or recovery controls, and concrete verification gates.
Plan and assess the database migration described below using Codex. Inputs - Migration goal: [Migration goal] - Database platform and version: [Database platform and version] - Current schema and migration artifacts: [Current schema and migration artifacts] - Application and deployment context: [Application and deployment context] - Data profile and workload: [Data profile and workload] - Operational constraints: [Operational constraints] - Success criteria: [Success criteria] - Available evidence and commands: [Available evidence and commands] - Authorization scope: [Authorization scope] Input requirements Treat the database platform and version, current schema or migration artifacts, deployment context, operational constraints, and authorization scope as blocking prerequisites when their absence could change DDL behavior, lock risk, compatibility, or recovery strategy. Ask focused clarification questions before recommending execution if any of these are materially unknown. Useful non-blocking context includes representative query plans, table sizes, row-growth rates, peak traffic periods, replica topology and lag, maintenance windows, backup history, observability links, prior migration timings, and staging results. If optional evidence is unavailable, continue with a bounded review, preserve the unknown explicitly, and state what must be measured before approval. Codex access and action boundaries - Inspect only the files, repository context, diffs, logs, schemas, configuration, and command output actually provided or accessible in the current Codex session. - You may propose patches and, when explicitly permitted by the authorization scope, edit files or run non-destructive commands in an isolated development or test environment. - Do not connect to, modify, migrate, restart, deploy to, or run write operations against production or any shared database unless explicit authorization and environment access are both supplied. Planning permission is not execution permission. - Never expose credentials, connection strings, secrets, or unnecessary personal data. Prefer schema metadata, aggregates, redacted samples, and synthetic fixtures. - Require human approval before destructive DDL, irreversible data transformations, production backfills, traffic switching, failover, restore operations, or deployment. - Do not claim that a migration was run, tested, approved, deployed, rolled back, or verified unless the corresponding action occurred and its evidence is present. Otherwise label it proposed, not run, unavailable, blocked, or unverified. Evidence rules For every important conclusion, distinguish among supplied fact, inspected observation, command-produced evidence, assumption, hypothesis, unknown, and conflict. Cite the relevant file path, migration name, schema object, log excerpt, command, or metric when available. Do not invent row counts, lock duration, query plans, backup validity, replication behavior, test results, or database capabilities. When evidence conflicts, show the conflict and explain what must resolve it. Migration analysis and design 1. Establish the change boundary. - Identify affected tables, columns, indexes, constraints, triggers, views, stored routines, foreign keys, jobs, application models, queries, caches, analytics consumers, and replicas. - Classify the work as schema DDL, data migration or backfill, application compatibility change, or a coordinated combination. - For Laravel projects, inspect relevant migration classes, models, casts, validation, query usage, queue workers, scheduler jobs, deployment scripts, and framework or database-version constraints. 2. Inspect the current and target states. - Reconstruct the current schema from authoritative artifacts where possible and describe the intended target schema. - Detect drift between migration history, schema dumps, ORM definitions, and observed database metadata. Do not silently choose a source of truth when they disagree. - Identify nullability, defaults, collation, character set, precision, generated values, identity or sequence behavior, uniqueness, referential integrity, and existing invalid or duplicate data that could block the change. 3. Analyze engine-specific operational risk. - Determine whether each DDL operation is transactional, metadata-only, table-rewriting, lock-acquiring, online, concurrent, or version-dependent for the named database. - Assess lock modes and duration, transaction-log or WAL growth, disk headroom, temporary space, replication lag, replica compatibility, connection-pool pressure, long-running transactions, statement timeouts, deadlocks, and impact on hot queries. - Flag unsafe assumptions about operations such as adding a non-null column, changing a type, building or validating an index, adding a foreign key, renaming or dropping an object, or rewriting a large table. 4. Choose the safest migration pattern. - Prefer expand-and-contract when old and new application versions may overlap: add compatible structures, deploy compatible code, backfill in bounded batches, validate, switch reads or writes, observe, and remove legacy structures only in a later approved release. - Separate schema changes, application releases, backfills, and cleanup when combining them would enlarge the failure domain. - Define batch keys, batch size assumptions, ordering, throttling, retry behavior, idempotency, resume checkpoints, transaction boundaries, and pause criteria for backfills. - Address dual-write consistency, race conditions, stale workers, queued jobs, cache behavior, and mixed-version deployments where applicable. - Compare credible alternatives, including downtime, online DDL, shadow-table approaches, phased constraints, or application-level compatibility. Explain safety, complexity, duration, and cost trade-offs. 5. Design recovery rather than assuming a down migration is safe. - State whether reversal is logically and operationally safe after new writes begin. - Select the appropriate response for each failure point: pause and resume, roll back application code while retaining expanded schema, run a compensating forward migration, restore from a tested backup, or fail over under an approved runbook. - Define recovery point and recovery time expectations, backup or snapshot prerequisites, restore-test evidence, data reconciliation needs, and the point after which rollback becomes destructive or impractical. - Never present a destructive down method as sufficient recovery for dropped or transformed data without validated restoration evidence. 6. Build rollout controls. - Provide ordered pre-deployment, deployment, backfill, cutover, observation, and cleanup stages with owners or approval points. - Include CI/CD gates appropriate to the artifacts: migration linting or static checks, clean-database application, upgrade from a representative prior schema, application tests against the target database version, backward-compatibility checks, and prevention of automatic destructive production execution. - Define canary or staged rollout options, maintenance-window needs, concurrency controls, feature flags, timeout settings, monitoring coverage, and communications or incident escalation requirements. 7. Define stop conditions. Stop and require human review if the target environment is ambiguous; production authority is absent; the current schema cannot be established; destructive or lossy behavior is unexplained; backup restoration is required but unvalidated; disk capacity is insufficient or unknown for a likely rewrite; lock, lag, error-rate, latency, or data-integrity thresholds are breached; mixed application versions are incompatible; or observed results materially differ from the plan. 8. Verify with expected and actual evidence. - Specify preflight queries or commands, expected observations, acceptance thresholds, and the evidence to retain. - Verify schema shape and migration-history consistency; row counts or scoped reconciliation totals; null, duplicate, orphan, checksum, or domain invariants; index presence and validity; critical query plans and latency; application read/write behavior; queue and job compatibility; error rates; locks; database load; replication lag; and backfill checkpoint completeness as relevant. - Include tests for fresh installation and upgrade paths when both are supported. - Record actual observations only from supplied or executed evidence. If commands were not run, leave actual results as not run and do not infer acceptance. - Treat cleanup or destructive contraction as a separate decision after an observation period and explicit approval. Required deliverable Produce the following task-specific report: A. Decision brief State the recommended migration pattern, risk rating with rationale, execution readiness status, blocking issues, required approvals, and the smallest safe next action. B. Evidence and uncertainty register Use columns: ID; statement; classification; source or command; confidence; conflict or limitation; effect on decision. C. Change inventory Use columns: object or component; current state; target state; operation; application dependency; compatibility window; data-loss potential. D. Operation risk matrix Use columns: migration step; database behavior; expected lock or rewrite; workload impact; replica or storage impact; reversibility; mitigation; evidence needed. E. Ordered migration runbook For every phase, give prerequisites, exact artifact or proposed command, environment, permitted actor, expected observation, timeout or threshold, stop condition, recovery action, and retained evidence. Clearly distinguish proposed commands from commands actually executed. Avoid production credentials and use explicit environment checks before any runnable command. F. Backfill specification When applicable, document selection predicate, stable batch key, batch sizing, transaction scope, idempotency, concurrency, throttling, checkpoints, retries, data reconciliation, completion rule, and pause or abort thresholds. If no backfill is needed, explain why. G. Rollback and recovery matrix Use columns: failure stage; symptom; safe response; data consequences; required backup or checkpoint; authorization; validation after recovery. Identify the point of no safe rollback. H. CI/CD and release gates List each gate, where it runs, pass condition, evidence produced, failure behavior, and whether it blocks merge, deployment, cutover, or cleanup. I. Verification ledger Use columns: check; method or command; expected result; actual result; evidence reference; status; owner. Allowed statuses are passed with evidence, failed, not run, blocked, or not applicable with rationale. J. Unresolved decisions and handoff List open questions, responsible decision-maker, required evidence, deadline or sequencing dependency, residual risks accepted by whom, and the next authorized action. Do not convert unknowns into reassuring assumptions. A migration is ready only when blocking evidence exists, required gates pass, recovery is credible, and the authorized human decision-maker approves execution.