Your AI app's database is a mess: how to fix the data model

Symptom

A messy data model rarely throws an error. It shows up as numbers that don't add up and records that drift apart.

• The same customer appears twice, once as Sam@example.com and once as sam@example.com.
• Orders store the customer's name as text, so when someone changes their name, their old orders belong to a stranger.
• Deleting a user leaves their projects behind, or fails with a constraint error nobody understands.
• Totals are a penny out.
• Booking times move by an hour when the clocks change.
• A status column contains Active, active and ACTIVE.

The clearest sign comes when you try to add the rule that should have been there from the start, and Postgres refuses because the data already breaks it:

Why it happens

The schema was built one prompt at a time. Each request ("add a bookings page", "let vendors have a profile") creates whatever tables and columns that one screen needs. Nothing steps back to ask how they fit together. Over a few weeks that produces predictable patterns:

• The same thing twice. profiles and users, customers and clients, each half-filled.
• Text instead of a link. orders.customer_name copied in as text rather than orders.customer_id pointing at a customer.
• Rules only in the form. "Email must be unique" is checked in the sign-up form, if anywhere, and not in the database, so a double-click or a second code path skips it.
• Types chosen by guess. Money in real or double precision, times in timestamp without a time zone, statuses as free text.
• No history. Changes made in the Supabase table editor, so there is no record of what changed, and preview and production databases drift apart.

None of it matters with 20 rows of test data. It matters a great deal once people pay you. If you have also hit slow pages, this is usually the same root cause; see why AI-built apps break after the first 100 users.

The fix

Take a backup before you start, and work on a copy of the database first. Then work through these in order. Each step is one migration you can test on its own.

1. Map what you have

List every table and column, then write one sentence per table saying what it is for. If you can't, that table is a candidate for merging or deleting.

select table_name, column_name, data_type, is_nullable
from information_schema.columns
where table_schema = 'public'
order by table_name, ordinal_position;

Then open Supabase's Performance Advisor. Lints such as no_primary_key and unindexed_foreign_keys point at the weakest tables.

2. Put the schema under migrations

Before changing anything, capture the current schema as a migration file and commit it. From then on, every change is a new file in git. Supabase's docs warn that changing the remote database directly makes db push fail with sync errors later.

supabase link
supabase db pull --remote          # capture the current schema as a migration
supabase migration new orders_customer_fk
# write SQL in the new file, then test it locally:
supabase db reset
supabase db push --remote          # older CLI versions use --linked instead

3. Replace copied text with real links

Add the new column alongside the old one, fill it, check what didn't match, and only then enforce it:

-- 1. Add the column, nullable for now
alter table public.orders add column customer_id uuid;
 
-- 2. Fill it from the old text column
update public.orders o
set customer_id = c.id
from public.customers c
where lower(o.customer_email) = lower(c.email);
 
-- 3. Look at what didn't match before going further
select id, customer_email from public.orders where customer_id is null;
 
-- 4. Enforce it, and index it (Postgres doesn't index foreign keys for you)
alter table public.orders
  add constraint orders_customer_id_fkey
  foreign key (customer_id) references public.customers (id)
  on delete restrict not valid;
create index orders_customer_id_idx on public.orders (customer_id);
 
-- 5. In a separate, later migration: check existing rows, then require the link
alter table public.orders validate constraint orders_customer_id_fkey;
alter table public.orders alter column customer_id set not null;

Adding the key as not valid and validating it in a separate migration means the slow check of existing rows runs under a lighter lock, so a busy table keeps taking writes. Choose the delete rule on purpose. restrict stops a customer being deleted while they have orders, which is right for anything financial. cascade deletes the child rows too, which suits things like a user's draft notes. Drop the old customer_email column in a later release, once nothing reads it.

4. Merge duplicates, then make them impossible

Find the duplicates first:

select lower(email) as email, count(*), array_agg(id order by created_at) as ids
from public.customers
group by lower(email)
having count(*) > 1;

For each group, keep the oldest record, point every reference at it, and delete the rest. Then add the rule so it can't happen again. A unique index on lower(email) treats Sam@ and sam@ as the same address:

create unique index customers_email_lower_key on public.customers (lower(email));

Your app will now get Postgres error 23505 (unique_violation) when someone tries to create a duplicate. Catch that code and show a helpful message.

5. Fix the types

Money. The PostgreSQL wiki advises against the money type and suggests numeric or integer instead. I use whole pence in an integer column, named so nobody can misread it:

alter table public.products
  alter column price type integer using round(price * 100)::integer;
alter table public.products rename column price to price_pence;

Renaming the column breaks any code that reads price, so ship the code change in the same release.

Times. A plain timestamp stores a wall-clock time with no zone, so you can't tell what moment it means. Convert to timestamptz, telling Postgres which zone the existing values were in:

alter table public.bookings
  alter column starts_at type timestamptz
  using starts_at at time zone 'Europe/London';

Statuses. Normalise the values, then allow only the ones you mean:

update public.projects set status = lower(trim(status));
alter table public.projects
  add constraint projects_status_check
  check (status in ('draft', 'active', 'archived'));

6. Merge duplicate tables

If you have both users and profiles, keep one profile table keyed on the Supabase user ID, so there is exactly one row per account:

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text,
  created_at timestamptz not null default now()
);

Copy the data across, repoint foreign keys, switch the code, and only then drop the old table. This is the step most likely to touch every screen, so do it last and test it most.

7. Re-check access rules

Every new table needs Row Level Security switched on and policies written, and every new owner column needs an index. It is easy to fix the model and quietly open a hole. The security checklist has the policies to copy.

How to prevent it

• Draw it before you prompt it. Keep a one-page diagram of your tables, and give the AI tool your current schema file with every feature request.
• Migrations only. No more changes in the table editor on production. Every change is a reviewed file in git.
• Rules in the database. Uniqueness, required fields, allowed values and links between tables belong in constraints. The form can check too, for a nicer message, but the database has the final word.
• Boring names. snake_case, _id for links, _pence for money, _at for times. Consistent names make wrong types obvious.
• Re-run the advisors. After each batch of AI changes, check the Performance and Security Advisors for new warnings.

When to get help

Most of this you can do yourself on a small app with a backup and a careful afternoon. Bring someone in when:

• The model is wrong at the root. If the app treats two kinds of user as one, every table is affected. Read whether to refactor or rebuild before starting.
• Paying customers are live. Changing tables under real orders needs a release plan, not just SQL.
• You need teams or organisations. Multi-tenant access touches the model, the policies and auth at once. The auth fix guide covers the sign-in side.

The data model is the second thing I look at in a Rescue Sprint, straight after security, because everything else sits on it. For the full order of work, see the guide to fixing vibe-coded apps, or start with the free health check.