Skip to main content

Source: ocean/docs/adr/029-migration-numbering-standardization.md | ✏️ Edit on GitHub

ADR-029: Migration Numbering Standardization and CI/CD Fix

Status​

Accepted

Date​

2025-08-28

Context​

The CI/CD pipeline for database migrations was failing due to several issues:

  1. Migration Numbering Inconsistency: The remote database had a legacy migration 20250814 without a timestamp suffix, while local migrations used the standardized format YYYYMMDDHHMMSS_description.sql. This mismatch prevented the Supabase CLI from pushing new migrations.

  2. Deno Lockfile Version Incompatibility: The graphql-v2 Edge Function contained a deno.lock file with version 5, which was incompatible with the Deno version in the CI environment, causing Edge Function deployments to fail.

  3. Outdated Supabase CLI: The local development environment was using Supabase CLI v2.34.3, which had less robust handling of migration history discrepancies compared to newer versions.

These issues blocked all database migrations and Edge Function deployments since August 22, 2025, preventing critical updates from reaching production.

Decision​

We implemented the following fixes:

  1. Migration History Repair:

    • Used supabase migration repair --status reverted 20250814 to mark the legacy migration as reverted
    • This removed the blocking migration from the remote history without affecting the actual database schema
    • The timestamped migrations (20250814000001, etc.) contain the actual schema changes

  2. Remove Deno Lockfile:

    • Deleted supabase/functions/graphql-v2/deno.lock
    • Lockfiles will be regenerated during deployment with the appropriate version for the CI environment

  3. Update Supabase CLI:

    • Upgraded from v2.34.3 to v2.39.2 for improved migration handling and bug fixes

Consequences​

Positive​

  • CI/CD pipeline now successfully deploys database migrations and Edge Functions
  • All 13 pending migrations were successfully applied to production
  • Migration history is now consistent between local and remote environments
  • Future deployments will work without manual intervention
  • Clear pattern established for migration naming: YYYYMMDDHHMMSS_description.sql

Negative​

  • The legacy migration 20250814 is marked as reverted in the history, which could be confusing when reviewing migration logs
  • Deno lockfiles are not committed, potentially leading to minor dependency version differences between environments (mitigated by Deno's default behavior of using latest compatible versions)

Neutral​

  • Developers must ensure they're using Supabase CLI v2.39.2 or later for local development
  • All new migrations must follow the timestamp naming convention

Implementation Notes​

Migration Naming Convention​

All migrations must follow this pattern:

YYYYMMDDHHMMSS_descriptive_name.sql

Example:

20250814000001_add_provisioning_events_table.sql

Handling Future Discrepancies​

If migration history discrepancies occur again:

  1. Use supabase migration list to identify mismatches
  2. Use supabase migration repair --status [applied|reverted] <version> to fix history
  3. Never manually edit the supabase_migrations.schema_migrations table

CI/CD Workflow​

The GitHub Actions workflow (deploy-supabase.yml) already handles remote-only migrations by:

  1. Detecting missing local migrations
  2. Creating placeholder files
  3. Repairing migration history
  4. Proceeding with deployment

References​