Taking a Lovable, Bolt or Replit app to production: a pre-launch checklist, with an audit script

-- Tables as an AI app builder generated them for a small CRM
create table public.contacts (
  id         bigint generated by default as identity primary key,
  owner_id   uuid not null references auth.users default auth.uid(),
  name       text not null,
  email      text,
  phone      text,
  company    text,
  notes      text,
  created_at timestamptz not null default now()
);

create table public.deals (
  id         bigint generated by default as identity primary key,
  owner_id   uuid not null references auth.users default auth.uid(),
  contact_id bigint references public.contacts,
  title      text not null,
  value      numeric(12, 2),
  stage      text not null default 'new'
);
alter table public.deals enable row level security;
create policy "Enable read access for all users" on public.deals for select using (true);
create policy "Enable insert for authenticated users only" on public.deals for insert to authenticated with check (true);

// Pre-launch audit for a Supabase-backed app: database rules, what the public API really returns, and the browser bundle.
// usage: node audit.mjs <dist-folder>
import fs from 'node:fs';
import path from 'node:path';
import pg from 'pg';

const API = process.env.API_URL;                   // e.g. https://<project>.supabase.co/rest/v1
const ANON = process.env.ANON_KEY;                 // the public key shipped in the app
const USER = { id: process.env.TEST_USER_ID, jwt: process.env.TEST_USER_JWT };   // a normal signed-in test user
const db = new pg.Client({ connectionString: process.env.DATABASE_URL });
let failures = 0;
const report = (ok, msg) => { if (!ok) failures++; console.log(`${ok ? 'PASS' : 'FAIL'}  ${msg}`); };

await db.connect();

// 1. Every table in public must have row-level security switched on
const { rows: tables } = await db.query(`
  select c.relname as table, c.relrowsecurity as rls
    from pg_class c join pg_namespace n on n.oid = c.relnamespace
   where n.nspname = 'public' and c.relkind = 'r' order by 1`);
for (const t of tables) report(t.rls, `${t.table}: row-level security ${t.rls ? 'on' : 'OFF'}`);

// 2. Policies that let everyone through: using (true) on reads, with check (true) on writes
const { rows: open } = await db.query(`
  select tablename, policyname, cmd from pg_policies
   where schemaname = 'public' and (qual = 'true' or with_check = 'true')`);
for (const p of open) report(false, `${p.tablename}: policy "${p.policyname}" (${p.cmd}) allows every row`);
if (!open.length) report(true, 'no policies allow every row');

// 3. Black-box: what the public API returns to a visitor and to an ordinary user
const get = (table, token) => fetch(`${API}/${table}?select=*`, { headers: { apikey: ANON, Authorization: `Bearer ${token}` } })
  .then(async r => (r.ok ? r.json() : []));
for (const { table } of tables) {
  const anonRows = await get(table, ANON);
  report(anonRows.length === 0, `${table}: anonymous visitor reads ${anonRows.length} row(s)`);
  const userRows = await get(table, USER.jwt);
  const others = userRows.filter(r => r.owner_id && r.owner_id !== USER.id).length;
  report(others === 0, `${table}: test user reads ${others} row(s) belonging to other accounts`);
}

// 4. Secrets in the browser bundle
const secretPatterns = [
  ['OpenAI key', /sk-(proj-)?[A-Za-z0-9_-]{20,}/g],
  ['Stripe secret key', /sk_live_[A-Za-z0-9]{20,}/g],
  ['Supabase service_role key', /eyJ[A-Za-z0-9_-]+\.eyJ[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+/g],
];
const files = fs.readdirSync(process.argv[2], { recursive: true }).filter(f => /\.(js|html|map)$/.test(f));
let found = 0;
for (const f of files) {
  const text = fs.readFileSync(path.join(process.argv[2], f), 'utf8');
  for (const [label, re] of secretPatterns) {
    for (const match of text.match(re) ?? []) {
      if (label.startsWith('Supabase')) {
        const claims = JSON.parse(Buffer.from(match.split('.')[1], 'base64url').toString());
        if (claims.role !== 'service_role') continue;   // the anon key is meant to be public
      }
      found++;
      report(false, `${f}: ${label} in browser code (${match.slice(0, 10)}…)`);
    }
  }
}
if (!found) report(true, `no secret keys in ${files.length} bundle file(s)`);

await db.end();
console.log(`\n${failures ? failures + ' problem(s) to fix before launch' : 'all checks passed'}`);
process.exit(failures ? 1 : 0);

-- 1. Switch RLS on for every table
alter table public.contacts enable row level security;

-- 2. Remove the generated "allow everyone" policies
drop policy "Enable read access for all users" on public.deals;
drop policy "Enable insert for authenticated users only" on public.deals;

-- 3. Each account sees and changes only its own rows. Nothing for anon at all.
create policy "own contacts" on public.contacts
  for all to authenticated
  using ((select auth.uid()) = owner_id)
  with check ((select auth.uid()) = owner_id);

create policy "own deals" on public.deals
  for all to authenticated
  using ((select auth.uid()) = owner_id)
  with check ((select auth.uid()) = owner_id
              and (contact_id is null or contact_id in (select id from public.contacts)));  -- only your contacts

-- 4. Policies filter on owner_id, so index it
create index on public.contacts (owner_id);
create index on public.deals (owner_id);