Skip to main content

Source: ocean/docs/adr/019-schema-drift-prevention.md | ✏️ Edit on GitHub

ADR-019: Schema Drift Prevention

Status: Accepted​

Context​

We discovered that our GraphQL API code expects database columns (neon_tenant_id, neon_database_host, neon_database_ready) that don't exist in the actual database schema. This caused signup failures and represents a dangerous schema drift between our API expectations and database reality.

Problem​

  1. Schema Drift: The API was updated to use new columns without corresponding database migrations
  2. Missing Columns: GraphQL resolvers reference non-existent columns causing runtime errors
  3. Deployment Failures: Migrations reference tables/columns that don't exist (e.g., usage_records table, invoice.number column)
  4. No Validation: No process to ensure API changes match database schema

Decision​

Implement a multi-layered approach to prevent schema drift:

1. TypeScript Database Types​

  • Generate TypeScript types from the actual database schema using Supabase CLI
  • API code must use these generated types, ensuring compile-time safety
  • Run supabase gen types typescript after each migration

2. Migration Testing​

  • All migrations must be tested locally before committing
  • Use supabase db reset to ensure migrations apply cleanly
  • Add pre-commit hook to validate migrations

3. API-Database Contract Testing​

  • Create integration tests that verify API expectations match database schema
  • Test all GraphQL resolvers against actual database structure
  • Run these tests in CI/CD pipeline

4. Schema Documentation​

  • Document all table structures in /docs/database-schema.md
  • Update documentation with every schema change
  • Include expected columns in GraphQL resolver comments

5. Code Review Process​

  • Any PR that changes database queries must include:
    • Corresponding migration if adding/changing columns
    • Updated TypeScript types
    • Integration tests
    • Documentation updates

Implementation Steps​

  1. Immediate Fix (Done):

    • Created migration 20250818_add_missing_neon_columns.sql to add missing columns
    • Fixed billing views migration to remove non-existent column references

  2. Generate Types:

    supabase gen types typescript --local > src/types/database.ts

  3. Update API Code:

    • Import and use generated database types
    • Fix any TypeScript errors from mismatched columns

  4. Add Validation:

    • Create tests/schema-validation.test.ts to verify API-DB alignment
    • Add to pre-commit and CI/CD pipelines

  5. Documentation:

    • Create comprehensive schema documentation
    • Add comments to all GraphQL resolvers about expected columns

Consequences​

Positive​

  • Compile-time safety for database operations
  • Early detection of schema mismatches
  • Clearer documentation of database structure
  • Reduced runtime errors from missing columns

Negative​

  • Additional development overhead
  • Must regenerate types after each migration
  • More complex PR review process

Lessons Learned​

  1. Never update API code without corresponding migrations
  2. Always test migrations against production-like data
  3. Use TypeScript's type system to enforce schema contracts
  4. Document database expectations in code

References​