Supabase Database Migration Engineering Guide

This document is the authoritative operational guide for authoring, reviewing, testing, applying, and rolling back database schema migrations across the Debelu PostgreSQL cluster. Grounded directly in the 97 migration files within [supabase/migrations/](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/supabase/migrations), the launch hardening suite (20260923_launch_hardening/), and the 40+ automated database verification suites in [scripts/](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/scripts), this specification establishes our zero-downtime database deployment lifecycle.

---

1. Migration Topology & Immutability Invariant

graph TD
    Dev[Developer Workstation] -->|1. Write Idempotent SQL| MigFile[supabase/migrations/YYYYMMDD_NNN_name.sql]
    MigFile -->|2. Local Wipe & Replay| SupaReset[supabase db reset]
    SupaReset -->|3. Run Verification Suites| Suite[40+ db-*-checks.mjs Test Harnesses]
    Suite -->|4. Pull Request| CI[GitHub Actions CI: db-flow-tests.sh]
    CI -->|5. Security & RLS Review| Review[Peer & DBA Approval]
    Review -->|6. Production Release| Apply[Supabase CLI db push / Dashboard Execution]

1.1 The Golden Rule: Immutability of Applied Migrations

CAUTION

NEVER EDIT AN APPLIED MIGRATION FILE. Once a migration is committed to the main branch or applied to any shared staging or production environment, its contents become permanently immutable. Modifying historic migration files causes checksum mismatches, corrupts migration tracking tables (supabase_migrations.schema_migrations), breaks local developer resets, and invalidates disaster recovery replays. All schema modifications, bug fixes, or rollbacks must be executed via new forward-only timestamped migrations.

---

2. Chronological Migration Architecture (97+ Migrations)

Debelu's schema evolution is structured across five distinct architectural eras:

timeline
    title Debelu Schema Evolution Timeline
    March 2026 : Foundation Baseline (20260315) : Core Auth, Profiles, Vendor Stores, Catalog & Escrow Base
    May 2026 : Consumer & Commerce Features : Real-Time Messaging, Coupons, Strict Enums, Wallet Payments
    August - September 2026 : Pre-Launch Hardening : RLS Isolation, Checkout Integrity, Paystack Subaccounts & DVA
    Late September 2026 : Production Launch Suite (20260923) : 28-Phase Launch Hardening Suite (Access Control, Ledger, RMA)
    October 2026 : Enterprise Governance & SLAs : Extended RBAC, Maker-Checker, Reconciliations, DSAR & Immutability

2.1 Era 1: Foundation Baseline (March 2026)

• 20260315103649_remote_schema.sql (313 KB baseline schema dump): Installs pgcrypto, uuid-ossp, core Supabase Auth triggers, initial profiles, products, orders, and base RLS policies.
• Post-baseline stabilization (20260315132500 through 20260315234500): Hardens initial audit triggers, sets up personal data export boundaries, and establishes automatic pre-fulfillment refund mechanisms.

2.2 Era 2: Consumer & Marketplace Features (May 2026)

• 20260517170000 to 20260525150000: Upgrades in-app notification preferences, enables Supabase Realtime for buyer-vendor messaging, introduces vendor-specific and product-specific promotional coupon schemas, migrates loose strings to strict PostgreSQL enums, and installs automatic star rating calculation triggers.

2.3 Era 3: Pre-Launch Hardening (August – September 2026)

• 20260831000000 through 20260920170000: Hardens profiles public readability policies, repairs vendor analytics RLS views, introduces checkout integrity checks, and configures Paystack dedicated virtual accounts (DVA) and split subaccounts.

2.4 Era 4: The 28-Phase Launch Hardening Suite (20260923_launch_hardening/)

A dedicated suite applied sequentially to harden production against concurrency race conditions, privilege escalation, and double-spend exploits:

• 01_schema_alignment.sql through 06_marketplace_functions.sql: Aligns foreign keys, enforces multi-campus column isolation, implements wallet ledger triggers, and deploys atomic stock decrement functions.
• 07_visibility_permissions through 16_platform_controls.sql: Configures campus ambassador permissions, dynamic platform maintenance flags, and payout transfer tables.
• 17_order_search through 28_order_read_permissions.sql: Implements WhatsApp delivery receipt tables, native mobile push token registries, and strict staff order read boundaries.

2.5 Era 5: Enterprise Platform Governance & SLAs (October 2026)

Modern compliance and operational control suite (migrations 20261003 to 20261011):

• Maker-Checker & Dual Authorization: 20261003_003_maker_checker.sql, 20261004_001_reconciliation.sql, 20261010001600_reviewed_platform_configuration.sql, 20261011001400_payout_transfer_reconciliation.sql.
• Financial Immutability: 20261011000400_order_fee_snapshots.sql (freezes fee calculations), 20261011001800_reviewed_wallet_refunds.sql (prevents double-refund executions).
• Statutory Data Privacy (NDPA): 20261011001500_reviewed_privacy_erasure_inventory.sql, 20261011001900_scoped_privacy_erasure_execution.sql, 20261011002500_subject_privacy_export_delivery.sql.
• Command Security: 20261011001600_command_table_privilege_hardening.sql (revokes direct SQL write privileges from application roles, forcing execution through audited stored procedures).

---

3. Authoring Standards & Migration Conventions

3.1 File Naming Specification

Migration files must reside in supabase/migrations/ and adhere strictly to sequential timestamp formatting:

YYYYMMDDHHMMSS_brief_descriptive_name.sql
# Example:
20261012143000_campus_hub_capacity_limits.sql

• YYYYMMDD: Target deployment date.
• HHMMSS: Hour, minute, second (ensures natural sorting).
• name: Lowercase words separated by underscores describing the domain modification.

3.2 Idempotency Rules

All migration statements must be replay-safe to prevent deployment aborts on partial executions:

-- 1. Table Creation
CREATE TABLE IF NOT EXISTS public.campus_hub_closures (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    hub_id uuid NOT NULL REFERENCES public.campus_hubs(id),
    closed_at timestamptz NOT NULL,
    reopens_at timestamptz NOT NULL,
    reason text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

-- 2. Column Additions
ALTER TABLE public.products ADD COLUMN IF NOT EXISTS requires_age_verification boolean DEFAULT false;

-- 3. Constraint Additions (Wrapped in DO blocks to avoid duplicate constraint errors)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'check_products_age_verified'
    ) THEN
        ALTER TABLE public.products ADD CONSTRAINT check_products_age_verified 
        CHECK (price > 0);
    END IF;
END $$;

-- 4. Function & Trigger Definitions
CREATE OR REPLACE FUNCTION public.validate_hub_closure()
RETURNS TRIGGER AS $$
BEGIN
    -- Validation logic
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_validate_hub_closure ON public.campus_hub_closures;
CREATE TRIGGER trg_validate_hub_closure
    BEFORE INSERT OR UPDATE ON public.campus_hub_closures
    FOR EACH ROW EXECUTE FUNCTION public.validate_hub_closure();

-- 5. Row-Level Security Policies
ALTER TABLE public.campus_hub_closures ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "campus_hub_closures_read" ON public.campus_hub_closures;
CREATE POLICY "campus_hub_closures_read" ON public.campus_hub_closures
    FOR SELECT TO public USING (true);

3.3 Zero-Downtime Migration Patterns

1. Adding Not-Null Columns: Never add a column with NOT NULL without a default value, as this causes long table locks on large tables. Add the column with a DEFAULT or add it as nullable, backfill existing records in chunks, and alter the column to NOT NULL in a subsequent migration.
2. Dropping Columns or Tables: Never immediately drop a column used by active application code. Follow the Expand-and-Contract pattern:
   • Phase 1: Deploy code that stops writing to and reading from the column.
   • Phase 2: In a subsequent deployment, drop the column from the database schema.
3. Index Creation: For high-volume transaction tables (orders, transactions, messages), always specify non-blocking index generation where supported:

   CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON public.orders(status);

---

4. Verification & Automated Test Harness (40+ Test Suites)

Debelu maintains over 40 automated verification scripts in [scripts/](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/scripts) to validate that migration changes preserve database integrity, RLS isolation, concurrency constraints, and maker-checker invariants.

4.1 Running Database Verifications Locally

# Reset local Supabase database to replay entire migration sequence from scratch
supabase db reset

# Execute the core migration and access control check suite
node scripts/db-access-tests.mjs

# Execute financial ledger and escrow snapshot checks
node scripts/db-finance-checks.mjs
node scripts/db-order-fee-snapshot-checks.mjs
node scripts/db-payout-reconciliation-checks.mjs

# Execute high-concurrency race condition simulations
node scripts/db-native-concurrency-tests.mjs
node scripts/db-native-return-concurrency-tests.mjs

# Execute data privacy and erasure validation
node scripts/db-privacy-erasure-plan-checks.mjs
node scripts/db-privacy-erasure-execution-checks.mjs

4.2 CI/CD Database Verification (db-flow-tests.sh)

The GitHub Actions workflow runs [scripts/db-flow-tests.sh](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/scripts/db-flow-tests.sh) against a temporary, throwaway containerized PostgreSQL instance on every pull request, ensuring that:

1. Every migration applies cleanly without warnings or syntax errors.
2. RLS policies block unauthorized cross-tenant queries.
3. Maker-Checker functions reject single-actor approvals (check_maker_checker_distinct).

---

5. Deployment & Production Rollout Procedure

sequenceDiagram
    autonumber
    participant DBA as Lead DBA / Engineer
    participant Git as GitHub (main branch)
    participant CI as GitHub Actions CI
    participant Prod as Supabase Production Database

    DBA->>Git: Merges approved PR containing new migration
    Git->>CI: Triggers Production Deploy Workflow
    CI->>CI: Executes scripts/db-flow-tests.sh & check-drift.mjs
    Note over CI: All checks pass; creates pre-migration backup snapshot
    CI->>Prod: Executes supabase db push (Applies Pending Migrations)
    Prod-->>CI: Migration applied successfully (Exit Code 0)
    CI->>Prod: Executes post-deploy smoke checks (HealthCheckService)
    CI-->>DBA: Deployment complete; notification sent to #ops-deployments

5.1 Pre-Flight Deployment Checklist

1. Schema Drift Verification: Run node scripts/check-drift.mjs to ensure the local schema definitions match remote production metadata.
2. Point-in-Time Recovery (PITR) Confirmation: Verify via Supabase dashboard that physical WAL archiving is active and healthy.
3. Low-Traffic Window: Schedule schema alterations on transaction-heavy tables (orders, wallets) during campus off-peak hours (02:00 – 05:00 UTC).

---

6. Rollback & Disaster Recovery Strategy

Debelu strictly employs Forward-Only Compensating Migrations rather than down-migrations or destructive database rollbacks:

1. Why Down-Migrations are Prohibited:
   • Reverting a migration that altered data or added columns with populated customer transactions leads to catastrophic data loss.
2. Compensating Migration Workflow:
   • If a newly deployed column or function introduces unexpected regressions, immediately author a new migration (e.g., 20261012150000_revert_feature.sql) that disables the feature, drops the trigger, or restores the previous function definition without dropping historical data columns.
3. Catastrophic Database Corruption Recovery:
   • In the event of severe storage corruption or catastrophic manual error, initiate Point-in-Time Recovery (PITR) to restore the database to the minute immediately preceding the failed migration deployment, as documented in [disaster-recovery-and-bcp.md](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/docs/operations/disaster-recovery-and-bcp.md).

---

7. Document Revision History

Revision	Date	Lead Author	Scope of Changes	Status
1.0.0	2026-10-05	Principal Data Architect	Initial enterprise migration specification covering chronological eras (97+ migrations), idempotency rules, zero-downtime patterns, and automated test harnesses.	Active Living Standard