Skip to main content

Source: ocean/docs/SCHEMA_DRIFT_ANALYSIS.md | ✏️ Edit on GitHub

Schema Drift Analysis - Organizations Table

Summary​

Based on my analysis of the codebase, I've identified several schema drift issues between the database schema, GraphQL schema, Edge Functions, and frontend code expectations for the organizations table.

Current Database Schema (Actual)​

The organizations table currently has these columns (confirmed via psql query):

  • trial_end (NOT trial_ends_at)
  • subscription_quantity (NOT seats_included or seats_used)
  • subscription_status (simplified status)
  • stripe_subscription_status (detailed Stripe status)

Historical Issues Found​

1. Column Name Mismatches​

Migration 20250828163000_fix_trial_column_name.sql fixed these issues:

  • Wrong: trial_ends_at → Correct: trial_end
  • Wrong: seats_included/seats_used → Correct: subscription_quantity

2. Code References to Wrong Column Names​

Found in older migrations that were using incorrect column names:

  • 20250819000002_fix_schema_drift_comprehensive.sql - references trial_ends_at, seats_included, seats_used
  • 20250826000001_fix_handle_new_user_error_column.sql - same incorrect references

Current State Analysis​

✅ Correctly Aligned Components​

  1. Database Schema: Uses correct column names (trial_end, subscription_quantity)
  2. GraphQL Resolvers: Query correct columns in base.ts
  3. Edge Functions:
    • handle-stripe-webhook uses subscription_quantity
    • GraphQL mutations use subscription_quantity
  4. Billing Views: Reference trial_end correctly

⚠️ Potential Issues​

  1. TypeScript Types (src/types/supabase.ts):

    • Missing many columns that exist in the actual database
    • Only includes: subscription_status but not stripe_subscription_status
    • Missing: trial_end, subscription_quantity, plan, owner_id, etc.

  2. Frontend Code:

    • No direct references to the problematic column names found
    • Uses GraphQL API which abstracts the database columns

🔍 Key Findings​

  1. Two Status Fields: The database has both:

    • subscription_status: Simplified status ('free', 'trial', 'active', 'canceled', 'past_due')
    • stripe_subscription_status: Detailed Stripe status ('trialing', 'active', 'canceled', etc.)

  2. Migration History: The schema was corrected in migration 20250828163000, but older migrations still contain references to the wrong column names.

  3. GraphQL Abstraction: The GraphQL layer successfully abstracts these database details from the frontend, preventing direct exposure of schema issues.

Additional Schema Drift Issues​

Missing Tables in TypeScript Types​

The TypeScript types file (src/types/supabase.ts) is severely out of date:

  • Database has: 26 tables
  • TypeScript defines: Only 3 tables (organizations, organization_members, provisioning_events)

Missing critical tables include:

  • stripe_products
  • stripe_prices
  • stripe_price_plans
  • subscription_items
  • payment_methods
  • profiles
  • And ~17 other tables

Recommendations​

  1. Regenerate TypeScript Types: The src/types/supabase.ts file needs to be completely regenerated to include:

    • All 26 database tables
    • All columns for the organizations table
    • Proper type definitions for JSONB columns

  2. Use Supabase CLI: Run supabase gen types typescript to regenerate accurate types from the current schema.

  3. Clean Up Old Migrations: While not affecting runtime, the older migrations with incorrect column names could cause confusion.

  4. Consistent Status Usage: Clarify when to use subscription_status vs stripe_subscription_status.

  5. No Frontend Changes Needed: The frontend correctly uses the GraphQL API and doesn't directly reference database columns.

Verification Commands​

To verify the current state:

# Check actual database columns
psql $DATABASE_URL -c "SELECT column_name FROM information_schema.columns WHERE table_name = 'organizations' ORDER BY ordinal_position;"

# Search for any remaining references to old column names
grep -r "seats_included\|seats_used\|trial_ends_at" --include="*.ts" --include="*.tsx" --exclude-dir=node_modules --exclude-dir=migrations_backup

# Verify GraphQL schema expectations
cat supabase/functions/graphql-v2/schema.ts | grep -A20 "type Organization"

Conclusion​

The schema drift has been largely resolved at the database and API levels. The main remaining task is to update the TypeScript type definitions to accurately reflect the current database schema.