Database migrations

As Supapower uses PGlite, which is a PostgreSQL database, you could easily copy your database migration queries from Supabase to the client and it will work, but it’s not recommended without slightly modifying them.

The key difference with client databases and backend databases is that you’ll eventually end up with many different versions of the client application. Either because of caches, or because users doesn’t always immediately update their app when a new version is available.

This places higher demands on backward and forward compatibility and there are some tricks below you can use to ease maintenance and avoiding database errors.

Schema recommendations

Here are some recommendations when writing your migrations to make the most out of Supapower and to avoid any schema or data issues.

Recommendations are for either the client (C ) or the server (S ) or both.

• C make all columns nullable, except the primary key
  • if you’re really sure some other columns will never ever be missing in the future (like created_at) you can keep them non-nullable as well
  • but if a column is non-nullable but set by a trigger in the backend (e.g. updated_at) it is easier to keep it nullable in the client schema
• S give every column you add later a default, or make it nullable
  • an older client creating a row only sends the columns it knows about, so a new NOT NULL column without a default fails its insert with 23502
  • 23502 counts as unrecoverable, so the change is discarded rather than retried: every client still on the previous version silently stops being able to create rows
• C remove all foreign key constraints
  • keep the columns, but remove the constraints as we don’t know/can’t guarantee in which order rows will be synced
• C S prefer soft deletes over DELETE queries, i.e. use a deleted_at column or similar and filter all queries with it
  • this recommendation is for the Supabase migrations as well because realtime events for deleted rows are not sent by default (see Delete events limitation)
  • even with full replication and delete events are received via the realtime engine they will only sync to online users, this means that rows deleted by other users while you are offline will still be in your client’s database
  • bump updated_at in the same statement that sets deleted_at, otherwise the deletion never reaches a client that is using cursor: an incremental download only asks for rows whose timestamp moved, so a soft delete that leaves updated_at alone is invisible to every client that was offline when it happened
• C S give every synced table an updated_at column maintained by a trigger
  • a trigger rather than application code, so that no write path can forget it - one missed update is a row that silently stops syncing to offline clients
  • it is what cursor needs to turn the full download on every start into an incremental one
• S bump updated_at on every row whose visibility changes, not just every row whose contents change

  • granting somebody access to a project, moving a document between teams, accepting an invitation: the rows themselves are untouched, so their timestamps do not move, so a client using cursor never asks for them and never learns they exist

  • realtime does not cover the gap either, since it only delivers rows that actually change

  • the fix belongs in the statement that changes the access, alongside the membership row itself:

    UPDATE documents SET updated_at = now() WHERE project_id = new.project_id;

  • it is worth doing from a trigger on the membership table so that no code path granting access can forget, and worth keeping in mind when writing row-level security policies: whatever a policy reads, a change to it has to move the timestamp of every row the policy decides about

• C S use uuid’s as primary keys
  • as the primary key is shared between the client and server databases and can be created at any end they shouldn’t be able to collide, which is why a sequence number won’t work
• C S have a single primary key in every table that you want to sync
  • support for compound primary keys in Supapower has not been implemented yet
  • from my experience even when you want compound primary keys it’s usually better to have a single PK with a unique key for the compound keys instead

Conclusion: relax your client database schema, have an updated_at column in every table (updated by triggers), use soft deletes and always use single primary keys as uuid columns.