Building a production CRM with Supabase + Sheets

The problem: spreadsheets don't scale for a sales team

The NEET admissions team had been running their lead pipeline entirely out of a shared Google Sheet. Ten counsellors, all editing simultaneously, no lead assignment, no status history, no audit trail. Leads were getting called by three different people, updates were overwriting each other, and there was no way to see which counsellors were actually converting. The Sheet wasn't the problem — the counsellors knew it well and weren't going to abandon it — but it needed a proper database behind it.

The brief was specific: build a CRM web app where new leads appear in real time, counsellors can be assigned leads, statuses can be updated with notes, and everything syncs back to the Google Sheet so the management team can keep using their existing reporting dashboards. Supabase was the obvious choice — it gave us PostgreSQL, real-time subscriptions, RLS, and a client library that works directly from the browser, all without managing a backend server.

The best tool for a non-technical team isn't always the most powerful one — it's the one that meets them where they already work. The CRM had to be the source of truth without asking anyone to abandon Google Sheets.

Schema design: leads, assignments, and a status log

The database has three core tables. leads holds the raw lead data — name, phone, NEET score, preferred colleges — mirrored from the Sheet. assignments is a join table between leads and counsellors, with a timestamp and a status field (new, contacted, follow_up, converted, lost). status_log records every status change with a timestamp and a notes field, giving a full history of every lead interaction.

-- leads table
create table leads (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  phone       text unique not null,
  neet_score  int,
  city        text,
  source      text,
  sheet_row   int,  -- row index in the Google Sheet for sync
  created_at  timestamptz default now()
);

-- assignments
create table assignments (
  id            uuid primary key default gen_random_uuid(),
  lead_id       uuid references leads(id) on delete cascade,
  counsellor_id uuid references auth.users(id),
  status        text default 'new',
  assigned_at   timestamptz default now()
);

-- status history
create table status_log (
  id            uuid primary key default gen_random_uuid(),
  assignment_id uuid references assignments(id) on delete cascade,
  status        text not null,
  notes         text,
  changed_by    uuid references auth.users(id),
  changed_at    timestamptz default now()
);

Row-level security: counsellors see only their leads

Each counsellor should only see leads assigned to them. This is enforced at the database level with RLS, not in application code — which means it holds even if there's a bug in the frontend. The policy on assignments checks that the counsellor_id matches the authenticated user's ID. Managers get a separate role with a policy that grants full read access.

-- Enable RLS
alter table assignments enable row level security;

-- Counsellors see only their own assignments
create policy "counsellors_own_assignments" on assignments
  for select using (counsellor_id = auth.uid());

-- Managers see all (via a custom claim set on login)
create policy "managers_all_assignments" on assignments
  for select using (
    (auth.jwt() -> 'app_metadata' ->> 'role') = 'manager'
  );

The critical thing I got wrong on the first pass: I set up RLS on assignments but forgot to set REPLICA IDENTITY FULL on the table. Supabase Realtime uses PostgreSQL's logical replication, and without REPLICA IDENTITY FULL, the Realtime payload for UPDATE and DELETE events only includes the new row values — not the old ones. This meant that when a counsellor updated a lead's status, the Realtime event received by other clients didn't contain enough information to identify which row had changed, causing the UI to silently fail to update. One ALTER TABLE assignments REPLICA IDENTITY FULL; fixed it.

Realtime subscriptions: new leads appear instantly

The front-end subscribes to the leads table on mount. When a new lead is inserted — either by the sync script or manually — every connected client receives the event and adds the lead to the top of the list without a page refresh. The subscription filters by the counsellor's assigned leads using the RLS context, so each counsellor only receives events for their own queue.

const channel = supabase
  .channel('leads-realtime')
  .on(
    'postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'leads' },
    (payload) => {
      // New lead arrived — prepend to list
      setLeads(prev => [payload.new, ...prev]);
    }
  )
  .on(
    'postgres_changes',
    { event: 'UPDATE', schema: 'public', table: 'assignments' },
    (payload) => {
      // Status changed — update in place
      setAssignments(prev =>
        prev.map(a => a.id === payload.new.id ? payload.new : a)
      );
    }
  )
  .subscribe();

// Clean up on unmount
return () => supabase.removeChannel(channel);

Google Sheets sync: two-way without conflict

The sync runs as a Cloudflare Worker on a cron trigger every five minutes. It reads new rows from the Sheet using the Google Sheets API (checking a synced column set to FALSE for unprocessed rows), upserts them into Supabase, and marks the rows as synced. In the reverse direction, it reads status updates from status_log created since the last sync run and writes the latest status back to a dedicated column in the Sheet.

// Cloudflare Worker — runs every 5 minutes via cron
export default {
  async scheduled(event, env, ctx) {
    const sheets = await getSheetData(env.SHEETS_API_KEY, env.SHEET_ID);
    const newRows = sheets.filter(row => row.synced === 'FALSE');

    for (const row of newRows) {
      const { error } = await supabase
        .from('leads')
        .upsert({
          phone:      row.phone,
          name:       row.name,
          neet_score: parseInt(row.neet_score),
          city:       row.city,
          sheet_row:  row._rowIndex
        }, { onConflict: 'phone' });

      if (!error) await markRowSynced(row._rowIndex, env);
    }
  }
};

The onConflict: 'phone' upsert is important. Phone number is the natural deduplication key — if a lead submits a form twice, the second entry updates rather than duplicates. The sheet_row integer stored on each lead record is what allows the reverse sync to write status updates back to the exact right row in the Sheet without doing a slow search-by-phone on every run.

Cross-device reliability: what breaks and why

The counsellors use a mix of laptops and phones. Mobile browsers aggressively suspend background tabs, which closes the WebSocket that powers the Realtime subscription. The fix is a visibility change listener that re-subscribes when the tab comes back to the foreground, plus a refetch of the full lead list to fill any gaps from while the subscription was suspended.

useEffect(() => {
  const handleVisibility = () => {
    if (document.visibilityState === 'visible') {
      // Re-subscribe and refetch to cover any missed events
      channel.subscribe();
      fetchLeads();
    }
  };
  document.addEventListener('visibilitychange', handleVisibility);
  return () => document.removeEventListener('visibilitychange', handleVisibility);
}, []);

The other cross-device issue was optimistic updates. When a counsellor marks a lead as contacted on their phone, the UI should feel instant — not wait for the database round-trip. I update the local state immediately, then fire the Supabase update. If the update fails (rare, but happens on poor mobile connections), the state rolls back and shows an error. This pattern made the app feel native-fast even on 4G connections.

What I'd change if starting today

The sync worker polling every five minutes introduces up to five minutes of lag between a Sheet submission and the lead appearing in the CRM. A better architecture would use a Google Apps Script trigger on the Sheet's onEdit event to call a Cloudflare Worker endpoint directly, reducing that lag to under ten seconds. I've built this pattern since on other projects — the polling version was a faster initial build but the webhook version is production-correct. The other change: I'd set REPLICA IDENTITY FULL from day one and document it explicitly, rather than discovering it through a baffling Realtime debugging session at 11pm.

---

If you're building a CRM, lead management system, or any Supabase-backed application and want a second pair of eyes on the architecture — or need to move fast and get it right — get in touch.