Database Migration Versioning Expert

Database Migration & Versioning Expert

English | Bahasa Indonesia

English

Description

A specialized skill focused strictly on the lifecycle of database schemas in production environments. While database-orm-expert handles queries and types, this skill covers the DevOps aspect of databases: zero-downtime schema migrations, backward-compatible release cycles, stateful data backfills, rollback mechanisms, and schema versioning strategies.

Trigger Conditions

• When modifying an existing production database schema.
• When planning a deployment that involves database changes (blue-green, canary).
• When resolving merge conflicts in migration files (e.g., Prisma, Drizzle, Alembic).
• When designing backfill scripts for massive data migrations.

Core Architectural Guidelines

1. Zero-Downtime Migration Pattern (Expand and Contract)

Never make breaking changes in a single deployment. Use the "Expand and Contract" pattern (Parallel Change):

• Phase 1 (Expand): Add the new schema element (column, table) without removing the old one. Deploy the database change.
• Phase 2 (Migrate): Update application code to write to both old and new elements, and read from the new element (with fallback). Deploy code.
• Phase 3 (Backfill): Run a background script to backfill data from the old element to the new element for older records.
• Phase 4 (Contract): Remove the old application code that writes to the old element. Deploy code.
• Phase 5 (Cleanup): Drop the old schema element from the database.

2. Backward Compatibility Rules

• Never DROP or RENAME a column/table in active use. Create a new one, migrate data, then drop the old one later.
• Avoid changing constraints on existing data without carefully verifying that all data complies.
• Add DEFAULT values to new NOT NULL columns, or make them nullable first, backfill, then enforce NOT NULL.

3. Migration Mechanics

• Always use version-controlled, immutable migration scripts (e.g., 20260814_add_user_status.sql).
• Never modify an already-applied migration file. If a mistake was made, create a new forward-migration to fix it.
• Idempotency: Write scripts that can be safely run multiple times (e.g., CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS).

4. Safe Data Backfilling

For large tables (millions of rows), running UPDATE table SET new_col = old_col will lock the table and cause downtime.

• Chunking: Perform updates in batches using LIMIT and sleep intervals to avoid locking the database.
• Background Jobs: Use dedicated queues (like BullMQ or Inngest) to orchestrate massive backfills.

Orchestration & Integration

• Enhances database-orm-expert with production-grade migration strategies.
• Complements ci-cd-devops-architect for automated migration deployment steps.
• Integrates with supabase-migration for Supabase-specific PostgreSQL migration workflows.

Bahasa Indonesia

Deskripsi

Skill khusus yang berfokus ketat pada siklus hidup skema database di lingkungan produksi. Sementara database-orm-expert menangani query dan tipe, skill ini mencakup aspek DevOps database: migrasi skema tanpa downtime (zero-downtime), rilis backward-compatible, skrip backfill data stateful, mekanisme rollback, dan strategi pembuatan versi skema.

Kondisi Pemicu

• Saat memodifikasi skema database produksi yang sudah ada.
• Saat merencanakan deployment yang melibatkan perubahan database (blue-green, canary).
• Saat merancang skrip backfill untuk tabel berukuran besar (jutaan baris).

Panduan Arsitektur Inti

1. Pola Zero-Downtime (Expand and Contract)

Jangan pernah melakukan perubahan yang merusak (breaking change) dalam satu deployment.

• Tambahkan kolom baru tanpa menghapus yang lama.
• Ubah aplikasi untuk menulis ke keduanya dan membaca dari yang baru.
• Lakukan migrasi data lama (backfill).
• Hapus penggunaan kolom lama dari kode aplikasi.
• Hapus kolom lama dari database di migrasi berikutnya.

2. Aturan Kompatibilitas Mundur (Backward Compatibility)

• Jangan pernah melakukan DROP atau RENAME pada kolom yang sedang aktif digunakan. Buat yang baru, pindahkan data, baru hapus yang lama di siklus rilis berikutnya.
• Kolom NOT NULL baru harus selalu memiliki nilai DEFAULT, atau jadikan nullable terlebih dahulu sebelum memaksakan constraint.

3. Keamanan Skrip Migrasi

• Gunakan file migrasi yang immutable (tidak boleh diubah setelah di-deploy). Jika ada bug, buat file migrasi baru untuk memperbaikinya (forward-fix).
• Skrip harus sebisa mungkin bersifat idempoten.
• Untuk tabel besar, lakukan backfill data secara bertahap (chunking/batching) agar tidak terjadi table lock yang menyebabkan downtime aplikasi.

Integrasi Orkestrasi

• Memperkuat database-orm-expert dengan strategi deployment yang aman.
• Melengkapi ci-cd-devops-architect dalam alur CI/CD untuk otomatisasi migrasi.
• Terintegrasi dengan supabase-migration untuk ekosistem spesifik Supabase.