Migrating data from local storage to Supabase DB

One of my clients initially built a web app that stored all its data in the browser's local storage. After completing the MVP, they decided to transition to a more scalable solution by moving the data to Supabase and using a proper database for storage.

The app already had a few active users. So we couldn't simply switch to Supabase without data migration. We first needed to migrate existing data of those users from local storage to Supabase DB to ensure a smooth transition without data loss.

Here's how the architecture looks.

In the current app, user authentication was not needed as all the data was stored in the user's browser (local storage). But to start storing it in a DB we have to add authentication. In this case, we will use Supabase authentication.

Let's see what the requirements are for this migration:

1. Migrate data from local storage to Supabase DB
   • There should be no loss of data and no data corruption
2. Data should be stored in DB instead of local storage after the migration
3. User's data should be behind authentication

Let's get started.

Identification

The first step is to identify if a data migration is needed or not. Users who open the app can be first-time users or existing users. We need to migrate data only for existing users. New users will not have any data and their usage data can straightaway go to Supabase DB.

Here, we look for a few keys in local storage. If they are present, it indicates that this is an existing user and we have to migrate their data. If it is a new user, we don't have to do any migration. They can sign up/sign in and start using the app.

Authentication

Once we have identified that a migration is needed, the first thing we have to do is to force the user to sign in. Without authentication, we cannot do the migration.

We also have to lock the app out so that they don't add any new data to the local storage (by using the app). This ensures we can migrate the data consistently and without loss of data.

Once a user logs in, the migration process is kick-started.

Migration start

Once the user is logged in, the migration process starts.

We copy over the data from local storage to a separate table local_storage_data in the DB. The data is stored as JSON. This will be the source of truth from now and we don't have to worry about data loss.

Core migration

We add a database trigger, which triggers when a new row is added to local_storage_data table. The trigger in turn calls a database function.

We want to do the migration within a transaction, so that we can rollback when something goes wrong in the process. With Supabase, the only way to do it is by creating a database function.

This database function goes through each item in the local storage data (which is relevant to the app) and copies over the data to the respective tables in the database.

Migration complete

If there are no errors, we mark the migration as complete and clear the user's local storage. In local_storage_data table, the column is_migration_complete is set to true.

If migration fails, it can be tried again by manually invoking a different database function. It does the same thing, but this function has to be manually called.

We also verify the data automatically to ensure that the data is copied over correctly and it is not corrupted. This is a straightforward comparison of data.

Once the migration is complete, the user will be unlocked and can start using the app as usual. We don't want the user to use the app before the migration is completed.

When the migration is in process, they are locked out and a modal shows a message: Migration in progress. Contact support <email_id> if migration is stuck for more than 5 minutes

Conclusion

Migrating data from local storage to a proper database like Supabase requires careful planning and execution to ensure data integrity. The key steps we followed were:

1. Identifying existing users who need data migration
2. Implementing authentication before migration
3. Safely copying local storage data to a temporary table
4. Using database triggers and functions to migrate data within transactions
5. Verifying data integrity and completing the migration

This approach ensures zero data loss while transitioning from client-side storage to a more scalable database solution. The process is automated but includes safeguards like locking out users during migration and providing support contact information if issues arise.

By following these steps, we successfully migrated user data while maintaining application functionality and providing a seamless experience for both existing and new users.