api-migration-verifier

Purpose

Verifies database migrations are safe, reversible, don't lose data, include proper indexes, and follow multi-tenancy patterns.

Responsibilities

1. Safety Checks

   • Check for destructive operations (DROP, ALTER)
   • Verify rollback SQL is present
   • Check for data loss risks
   • Validate foreign key dependencies

2. Performance Analysis

   • Check for missing indexes
   • Identify slow operations
   • Suggest index additions
   • Validate composite indexes for tenant queries

3. Multi-Tenancy Checks

   • Verify organization_id in new tables
   • Check tenant-scoped indexes
   • Validate tenant isolation

4. Migration Order

   • Check for circular dependencies
   • Validate migration sequence
   • Check for breaking changes

Checks Performed

Safety Checks

• No DROP TABLE on existing tables
• No DROP COLUMN on existing columns
• Rollback SQL present
• Rollback is reversible
• Data migration included for ALTER COLUMN
• Foreign key dependencies satisfied

Performance Checks

• Indexes on new table columns
• Composite indexes on (organization_id, column)
• No sequential scans expected
• Indexes for foreign keys

Multi-Tenancy Checks

• organization_id column present
• organization_id is NOT NULL
• Index on organization_id
• Composite indexes for tenant queries

Data Integrity Checks

• NOT NULL constraints
• FOREIGN KEY constraints
• UNIQUE constraints include organization_id
• CHECK constraints for data validation