Skip to main content

Source: ocean/docs/adr/020-api-database-schema-synchronization.md | ✏️ Edit on GitHub

ADR-020: API-Database Schema Synchronization

Date: 2024-08-20

Status​

Accepted

Context​

We experienced significant production issues due to schema drift between our GraphQL API expectations and the actual database schema. The API was querying for columns that didn't exist (provisioning_status, subscription_status) or had different names (stripe_subscription_status), causing 500 errors in our GraphQL endpoints.

This drift occurred because:

  1. API code was updated without corresponding database migrations
  2. No automated validation between GraphQL schema and database schema
  3. Column naming inconsistencies between different parts of the codebase

Decision​

We will implement the following practices to prevent API-database schema drift:

  1. Atomic Changes: API changes and database migrations MUST be committed together
  2. Schema Validation: Add automated tests that validate GraphQL schema against database schema
  3. Naming Conventions: Use consistent column naming across all layers
  4. JSONB for Evolving Data: Use JSONB columns for data structures that may evolve (like provisioning_status)
  5. Migration Testing: All migrations must be tested locally before deployment

Implementation Details​

  1. For new API fields:

    • First create the database migration
    • Then update the API to use the new fields
    • Deploy both together

  2. For field renames:

    • Create a migration that adds the new column
    • Create a sync trigger to keep both columns in sync during transition
    • Update API to use new name
    • After verification, drop the old column

  3. For complex status tracking:

    -- Use JSONB for flexible status tracking
    provisioning_status JSONB DEFAULT jsonb_build_object(
      'stripe', jsonb_build_object('status', 'pending', 'customerId', null),
      'neon', jsonb_build_object('status', 'pending', 'tenantId', null),
      'overall', 'pending'
    )

Consequences​

Positive​

  • Prevents runtime errors from schema mismatches
  • Makes schema evolution more predictable
  • Reduces debugging time for production issues
  • Improves API reliability

Negative​

  • Requires more coordination between API and database changes
  • May slow down development slightly
  • Requires additional testing infrastructure

Mitigation​

  • Create tooling to generate TypeScript types from database schema
  • Add pre-deployment checks that validate schema compatibility
  • Use feature flags for gradual rollouts of schema changes