Migrating the schema without breaking the apps already out there

A user asked us for the usual thing, the kind that sounds like nothing: "can I have my profile and my daughter's separate?"

Netflix has had the "Who's watching?" screen for years. It's a picker with two avatars. Half a day of work.

And underneath, it's changing the primary key of the table where all the user state lives, with apps already installed on the living room TVs of people who don't know you.

What was there, and why it didn't cut it

In LoverCast, user state — favorites, continue watching, channel order, hidden categories, the IPTV provider — is stored locally on the device and synced to a table in the cloud: user_preferences, one row per user. The primary key was user_id. One user, one row, all their data.

Profiles break that in the most basic way possible: if you and your daughter have different "Continue watching" lists, one row per user no longer fits. You need one row per profile. The PK goes from user_id to profile_id.

On the web this is an afternoon. You change the schema, deploy, and the user's browser swallows the new version the next time they log in. Nobody's running yesterday's web.

On Android there's no such thing. The APK someone installed eight months ago is still there, turning on every night, talking to your database with eight-month-old code. You can publish a new version. You can't force anyone to install it.

Why the easy case is deceiving

The trap is that the change doesn't break anything until someone uses it.

You add the profiles table, add a profile_id column to user_preferences, and fill in the "Main" profile for each user. You deploy. Everything keeps working. Old clients don't even notice: there's still exactly one row per user, and one extra column never hurt anyone.

That's migration 0012, and it's additive and reversible on purpose. It doesn't change the PK, doesn't make the column NOT NULL, doesn't touch any existing values. It's the expand phase of the classic expand-contract: first you add the new thing without removing the old one, and you live in the ambiguity for as long as it takes.

The bomb goes off the day someone creates their second profile. That's when a second row shows up with the same user_id, and the eight-month-old APK — which asks for its preferences assuming it'll get one row and only one — finds two. It breaks. And it breaks on the device of someone who didn't do anything weird: they just didn't update.

So the real problem isn't changing the PK. It's that the old app has an assumption baked in, and that assumption is about to stop holding.

The compatibility view

The way out is elegant and not widely known: if old clients ask for user_preferences expecting one row, give them exactly that — it just won't be the table anymore.

The real table gets renamed to profile_preferences (one row per profile, PK profile_id, which is what we wanted). And user_preferences is reborn as a view that returns only the row for the default profile:

-- Renombras la tabla real...
alter table public.user_preferences rename to profile_preferences;

-- ...y el nombre viejo pasa a ser una vista del perfil por defecto de cada usuario.
-- security_invoker: la RLS se aplica con el rol de quien consulta, no con el del dueño.
create or replace view public.user_preferences
  with (security_invoker = true) as
  select pp.*
  from public.profile_preferences pp
  where pp.profile_id = (
    select p.id from public.profiles p
    where p.user_id = pp.user_id and p.is_default
    limit 1
  );

The old client keeps doing select ... from user_preferences, keeps getting one row, and that row is the one for their main profile. It notices nothing. And since it's a view over a single table with a WHERE, Postgres makes it auto-updatable: UPDATEs and DELETEs also flow through to the real table, for free.

INSERT doesn't, because a new user logging in with an old app has no profile to link to yet. That's covered by an INSTEAD OF INSERT trigger that creates the "Main" profile on the fly and puts the row where it belongs.

This far, it's the textbook plan. Three migrations: 0012 expands, 0013 cuts over the PK, 0014 renames and sets up the view. 0013 and 0014 go together in the same window, and you can't stay halfway: 0013 alone already allows multiple rows per user, but without the view protecting the old clients.

Where the real difficulty was

And now the part I'm writing this for, because it's the one that doesn't show up in articles about expand-contract.

The view buys you reads. Writes you have to check client by client.

The old web writes fine: it does a plain UPDATE, and the UPDATE flows through the auto-updatable view without a hitch.

Old Android and Android TV don't write. Nothing. And not because of a bug on our end:

• They save with an upsert (Prefer: merge-duplicates from PostgREST), which is the sensible thing to do when you don't know whether the user's row already exists.
• PostgREST implements the upsert with an ON CONFLICT, and ON CONFLICT requires a unique constraint to resolve the conflict against.
• A view can't have unique constraints. It's not that we don't have one: it's that it's not possible.
• Result: HTTP 400. The old client reads its main profile perfectly and doesn't save a single favorite.

Read-only. Silent until you try to save something.

There's a second loss, easier to see coming but just as real: realtime doesn't work on views. Postgres's publication carries tables, not views. So old clients also lose live sync between devices — they keep the pull-on-startup behavior, which is what existed before realtime was a thing, but the TV stops learning what you do on your phone until you restart it.

Add it up: the compatibility view, which on paper was "old clients won't notice," in practice is "old clients read, don't write, and lose realtime." It's still way better than breaking them. But calling it "zero breakage" would be a lie, and that's the kind of lie you believe yourself before anyone else does.

What was decided and what was dropped

Knowing that, the decision was to accept the temporary read-only state. LoverCast is a small product: we update our own devices, publish the new version on the store, and stragglers resume saving as soon as they update. The cost is bounded and the benefit — having profiles — is worth it.

I'm writing this with the plan finished and the migrations written, but before applying them to production. I'm telling the design and the decisions, not a result: if something we didn't see shows up when we run it, that'll be material for another post.

What was dropped, and is noted down in case some day there's a user base that justifies it, is the dual-table redesign: leave the default profile in a real table still called user_preferences and put only the extra profiles in profile_preferences. There, old clients write without any issue, because they're talking to an actual table, with its own unique constraints and its own spot in the realtime publication. It's more complex and duplicates the write paths. At this scale it doesn't pay off. At another scale, it would.

That's the honest decision: it's not that the view is the correct solution, it's that it's the solution proportionate to the size of the problem. And it's written down in the deployment doc, with names attached, so that in two years nobody has to reconstruct why.

The detail that makes the order matter

There's one loose end left: the new client has to work both against a backend with the migration applied and one without it. Because if someone updates the app before you've touched the database, the new app starts asking for a table that doesn't exist.

This is solved by letting the client pick the table on its own:

// Con perfil activo, el backend tiene el cutover → tabla real `profile_preferences`.
// Sin perfil activo, backend legacy → `user_preferences` (que en prod es la vista).
// Así el MISMO build sirve contra un backend con o sin la migración aplicada.
private val tableUrl get() =
    "$supabaseUrl/rest/v1/" + if (activeProfileId() != null) "profile_preferences" else "user_preferences"

Four lines worth an entire on-call night. An update that lands before the migration doesn't explode: it degrades to legacy and keeps working like it always did.

Even so, the deployment order is schema first, apps after. That's non-negotiable and it's on the first line of the doc, in bold, because it's the kind of thing you forget exactly on the day you're in a hurry.

What we're taking away

The client you don't control sets the pace. You can't force an update, so your schema has to withstand two versions of your own code talking to it at the same time. Expand-contract isn't bureaucratic ceremony: it's what lets you live in the middle for weeks without it showing.

A compatibility view is not a mirror. It's a different object that looks alike. It reads the same, writes almost the same, and doesn't do realtime. Before trusting it with your old clients, test the writes of each one, not just the reads — ours broke because of how PostgREST implements the upsert, three layers below where we were looking.

"Zero breakage" is almost never zero. It's "small, known breakage, accepted on purpose." The difference between those two phrases is whether you've measured it or you're just telling yourself a story. Write down in the deployment doc exactly what old clients lose and for how long; if you're embarrassed to write it down, the decision wasn't as good as you thought.

When this reaches someone's TV, none of it will show. "Who's watching?" will pop up, they'll pick their face, and their show will be right there. Everything above will have happened underneath, and that's exactly the point.

---

LoverCast is in production and you can download it. If you're in the middle of a similar migration and you're not fully sure about it, tell us.