Migration Management

Migration Management

LoanPilot uses Supabase as its backend and database provider. Keeping your local development environment and production instance in sync requires managing SQL migrations effectively.

Prerequisites

Before managing migrations, ensure you have the Supabase CLI installed and have initialized your connection:

# Login to Supabase
supabase login

# Link your project (found in project settings)
supabase link --project-ref your-project-ref

Applying Migrations

When setting up the project for the first time or pulling new changes from the repository, apply the existing migrations to your database instance.

Local Development

To reset your local database to the current schema defined in the supabase/migrations folder:

supabase db reset

Note: This will clear all local data and re-apply all migrations.

Production/Remote Environment

To apply new migrations to your linked remote instance:

supabase db push

Schema Evolution

LoanPilot is designed to be resilient during schema transitions. For example, the Bank Statement Analysis engine includes a fallback mechanism that checks for "enhanced analysis" columns.

If your database schema is behind the codebase, the API will automatically detect missing columns (PostgreSQL error PGRST204) and fall back to a base insertion method to prevent application crashes. To enable full "Enhanced Analysis" features (fraud risk scores, EMI detection, etc.), ensure the latest migrations are applied.

Database Functions and RPCs

Certain application workflows, such as user onboarding, rely on Remote Procedure Calls (RPCs). These are defined in the SQL migrations and must be present for the application to function.

Critical RPCs:

• handle_new_user_signup: Triggered during the /signup flow. It creates the organization, creates the user profile, and links them in a single transaction.

If you encounter a "Failed to complete signup" error, verify that this function exists in your Supabase schema:

-- Example verification query in Supabase SQL Editor
SELECT routine_name 
FROM information_schema.routines 
WHERE routine_type = 'FUNCTION' 
AND routine_name = 'handle_new_user_signup';

Creating New Migrations

If you modify the database schema (e.g., adding fields to loan_applications or bank_statement_analysis), generate a new migration file:

1. Generate Migration:

   supabase migration new name_of_your_migration
   

2. Add SQL: Open the newly created file in supabase/migrations/<timestamp>_name.sql and add your DDL statements.
3. Apply and Test:

   supabase db reset
   

Synchronizing Remote Changes

If you make changes directly through the Supabase Dashboard UI (not recommended for production workflows), you can pull those changes into your local migration history to keep the repository updated:

supabase db pull

This creates a new migration file reflecting the differences between your local files and the remote schema.