Lovable's Supabase Schema Problems: What to Fix Before You Scale

The Lovable app feels fast when you launch it. Ten users sign up, a few dozen rows land in the database, every page loads in under a second, and the dashboard you shipped on a Tuesday afternoon looks like a real product. Three months later a Product Hunt feature or a single viral post drops ten thousand new users into the same tables and the site begins to crawl. Profile queries that used to return in eight milliseconds now take one and a half seconds. The account deletion flow throws a foreign key constraint violation and silently rolls back. An admin page that aggregates user activity times out. Someone reports that two accounts share an email address. You open Supabase, look at the SQL editor, and realise you have no idea what any of the generated schema actually does.

This is the rhythm of every Lovable takeover we handle. The code is fine. The design is fine. The database is the bottleneck, and the bottleneck was built in at generation time before the first user signed up. Lovable is excellent at producing a working schema. It is not opinionated about the hundred small decisions that separate a schema that survives growth from one that collapses at the ten thousand row mark. This piece walks through the five schema fixes we apply on every Lovable hardening engagement, shows the EXPLAIN ANALYZE output that proves each one matters, and explains the pattern behind each decision so you can audit your own project before scale catches you off guard.

Why Lovable Schemas Break at Ten Thousand Rows

Postgres is a forgiving database for small tables. A table with fifty rows will return any query in under a millisecond regardless of how you wrote it, because the planner chooses a sequential scan and the whole table fits in a single page of memory. That same planner behaviour is what hides schema problems in development. You test your Lovable app with thirty seeded rows, everything feels instant, you ship. Prod fills up. The planner still chooses a sequential scan because no index exists to choose instead. At ten thousand rows the seq scan is still fast enough that no one notices. At one hundred thousand rows it is not. The curve is not linear, it is a cliff, and the cliff is usually the first foreign key query that runs without an index.

The planner reveals this in one command. Run explain analyze select * from posts where user_id = 'some-uuid'; against a Lovable table and look for the phrase Seq Scan on posts. If you see it, that query is scanning every row in the table to find the matching user. At ten thousand rows the actual time reads about eight milliseconds. At one hundred thousand rows the same query reads one hundred and eighty milliseconds. At a million rows it is one to three seconds and the app freezes visibly. Postgres does not automatically index foreign key columns. It indexes only the primary key and any column you explicitly declare unique. Lovable declares foreign keys but never declares the matching index, and this single omission is the number one reason a Lovable app slows down as it grows. Every other fix in this piece matters, but this one is the difference between an app that scales and an app that gets rebuilt at the hundred user mark.

Fix One, Index Every Foreign Key and Every Where Clause Column

The first fix we run on any Lovable takeover is to list every foreign key column in the schema and create a btree index on each one. You can pull the list with a query against the pg_constraint and information_schema.key_column_usage tables, or simply read through the Lovable migration files and note every line that contains references. For each user_id, project_id, post_id, organization_id column that appears as a foreign key, write a migration that adds an index with a predictable name. The pattern we use is create index if not exists posts_user_id_idx on posts(user_id); applied across every table. Idempotent with if not exists so the migration is safe to rerun. Named with the table and column so the index shows up predictably in Supabase's dashboard.

Foreign keys are not the only columns that need indexes. Any column that appears regularly in a where clause, order by, or join condition deserves the same treatment. Status columns on posts, stripe customer id on billing records, slug columns on content, created at when it drives feed ordering. The test for whether a column needs an index is to run the production query through explain analyze and look at the plan. If Postgres chooses a seq scan and the table is growing, index it. If Postgres chooses an index scan already and the query is under one millisecond, leave it alone. Over indexing is a real cost on write heavy tables because every insert has to update every index, but in practice Lovable schemas are so under indexed that the write cost is negligible compared to the read speedup. Expect a five to fifty times speedup on any filtered query after the foreign key indexes land. EXPLAIN ANALYZE before and after is the proof we include in the deliverable so the founder can see the difference in milliseconds rather than trust us on feel.

Fix Two, Convert Every JSON Column to JSONB and Index It

Postgres has two JSON types that look identical in the schema viewer and behave very differently at query time. The json type stores the column as raw text, preserves whitespace and key order exactly as you wrote it, and reparses the text every single time you read into it. The jsonb type stores the column as a parsed binary tree, loses whitespace, sorts keys internally, and supports GIN indexing with operator class queries. Lovable alternates between both types based on which prompt template ran, and in almost every case the column should have been jsonb and was not.

The performance gap shows up the moment you query into the column. A query like select * from users where settings->>'theme' = 'dark' runs a sequential scan regardless of index on a json column because Postgres has no way to index inside raw text. On a jsonb column you can build a GIN index with create index users_settings_gin on users using gin (settings jsonb_path_ops); and rewrite the query as select * from users where settings @> '{"theme":"dark"}';. On a five hundred thousand row users table with a two kilobyte settings blob per row we see the json version run in about one and a half seconds and the jsonb version with the GIN index return in six milliseconds. The migration to convert is straightforward, alter table users alter column settings type jsonb using settings::jsonb;, but it needs a brief table lock on large tables, so we schedule it during low traffic windows and wrap it in a transaction. The moment it ships, every settings read in the application gets faster and stays fast as the table grows.

Fix Three, Set Cascade Rules on Every Foreign Key

Foreign keys without cascade rules are time bombs that go off the first time a user tries to delete their account. Postgres defaults to ON DELETE NO ACTION, which in practice behaves like RESTRICT and refuses to delete a parent row if any child row references it. Lovable declares foreign keys with the default, so every relationship in the database is implicitly set to block deletions. The account deletion flow in a Lovable app looks like it works, because the UI shows a success toast, but the underlying transaction rolls back silently when it hits the first child table, and the user row stays in the database forever. Support tickets about ghost accounts and users who cannot re register with the same email trace back to this single default.

The fix is to decide, for each foreign key, what should happen when the parent disappears. For data that the parent owns outright, like comments on a post, the cascade rule is on delete cascade. When the post goes, the comments go with it. For optional references where the child should survive without the parent, like a post with a featured image that can be removed, the rule is on delete set null. The image reference becomes null and the post keeps existing. For strict integrity cases where deletion should be blocked, like a billing record tied to a completed invoice, on delete restrict is correct and explicit. The migration pattern is alter table comments drop constraint comments_post_id_fkey, add constraint comments_post_id_fkey foreign key (post_id) references posts(id) on delete cascade; applied across every foreign key. Once the rules are set, the account deletion flow completes cleanly, cleanup jobs work, and GDPR data deletion requests stop turning into manual SQL triage.

Fix Four, Add the Unique Constraints Lovable Skips

Lovable writes application logic that assumes data is unique without ever declaring uniqueness at the database level. The signup form checks whether an email exists before inserting, then inserts. Two concurrent signups with the same email pass the check a millisecond apart and both insert successfully because there is no unique constraint on the email column to catch them. The slug generator on a content page runs a select and an insert in two statements instead of an atomic operation, so a race condition produces duplicate slugs. A project_members join table with columns for user id and project id has no composite unique constraint, so a user clicks the add button twice and ends up as two members of the same project, which breaks role based permission logic downstream.

The fix is a one line migration per constraint, but the audit is the harder part. List every column in the schema that the application treats as unique and add an explicit constraint for each one. Email on the users table, alter table users add constraint users_email_unique unique (email);. Slug on the posts table, same pattern. Composite unique on join tables, alter table project_members add constraint project_members_user_project_unique unique (user_id, project_id);. Stripe customer ID on the billing table so the webhook handler can run idempotent upserts with a proper conflict target. Once the constraints are declared, the database enforces them under concurrency and Lovable's race prone insert logic becomes safe, because the second insert fails with a constraint violation the application can catch and handle. Idempotent upserts with on conflict clauses suddenly work because the conflict target exists. A twenty minute migration prevents a class of bugs that would otherwise take hours to debug in production.

Fix Five, Triggers for Updated At and Check Constraints for Enums

The final fix is the one that looks cosmetic until it breaks a caching layer or a sync job. Lovable generates tables with a created_at timestamptz default now() and often an updated_at timestamptz default now() column, but it never wires up a trigger to keep updated_at current on row updates. The column gets set at insert time and then stays frozen forever. Any code that depends on updated_at for cache invalidation, webhook delivery, incremental sync to a search index, or audit logging reads a value that is effectively insert time and misses every real update. The symptom is cache staleness or search indexes that never catch up, and the cause is the missing trigger.

The pattern we drop in on every takeover is a single reusable trigger function plus one trigger per mutable table. The function is create or replace function set_updated_at() returns trigger as $$ begin new.updated_at = now(); return new; end; $$ language plpgsql; and each trigger is create trigger posts_updated_at before update on posts for each row execute function set_updated_at();. Apply it to every table with an updated_at column. While we are in the schema we also add check constraints to every enum style column that Lovable stored as plain text. A status column with values like draft, published, and archived deserves check (status in ('draft','published','archived')) so the database rejects typos and invalid values at write time rather than allowing them through and surfacing them as broken UI three weeks later. The same applies to role columns on membership tables and any other small fixed vocabulary stored as text. The constraints are cheap to add, they run at write time only, and they turn entire classes of application bug into loud and immediate database errors.

The Row Level Security Gap on Join Tables

One schema issue sits outside the five fixes because it is strictly a security problem rather than a scale problem, and it is worth its own heading. Lovable enables row level security on the main tables it generates, users and profiles and posts, because the Supabase template tells it to. RLS on join tables, the ones that wire users to projects or teams or organizations, is inconsistent. We find it missing entirely on some projects and enabled but policyless on others. Enabled without a policy means every authenticated query against that table returns zero rows, which founders patch by disabling RLS, at which point the table is readable by any authenticated user regardless of membership. Neither state is correct.

The correct state is RLS on, with a select policy that checks membership. For a project_members table, the policy reads something like create policy "members read own memberships" on project_members for select using (auth.uid() = user_id); and a corresponding insert policy restricts who can add members. Every join table in the schema needs the same treatment. We audit these as part of the hardening engagement because a missing policy on a join table is one of the most common data leakage patterns in Lovable projects and it rarely shows up in surface level security reviews.

What the WitsCode Lovable Schema Hardening Engagement Looks Like

If you have read this far and recognised your own database in any of the five patterns, the next step is either to run the migrations yourself using the SQL samples above or to hand the audit to us. Our Lovable schema hardening engagement is a one time fixed scope review that covers everything in this piece and delivers a single migration file your team can apply in Supabase. The deliverable includes a complete index audit with EXPLAIN ANALYZE output from before and after each index so you can see the speedup in milliseconds, a full pass over every JSON column to convert and index where appropriate, cascade rule decisions documented per foreign key so you know why each one was chosen, the unique constraint and check constraint set, the updated at trigger applied across every mutable table, and a row level security audit on main and join tables with policies written where missing. We ship the migration, walk your team through applying it in staging, and verify the production cutover. Most projects ship inside a week. Book a Lovable schema review at witscode.com and have the engagement scoped against your actual schema rather than a generic template. →