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/signupflow. 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:
- Generate Migration:
supabase migration new name_of_your_migration - Add SQL: Open the newly created file in
supabase/migrations/<timestamp>_name.sqland add your DDL statements. - 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.