Building a CRM on Supabase: data model, roles and an audit log

Plenty of growing teams reach the point where a spreadsheet is too loose and an off-the-shelf CRM is too rigid. The sales process has its own stages, the data lives next to the product, and everyone wants one screen that shows a customer's whole story. A small custom CRM on Supabase is often the right answer. It is ordinary Postgres underneath, so the data stays yours and any competent engineer can work on it later.

This guide covers the three decisions that decide whether a home-grown CRM holds up: the data model, the roles, and the audit log.

Start with the data model

Keep the core small. Most B2B CRMs need five tables before anything else.

| Table | What it holds | Key columns | | --- | --- | --- | | organizations | Your own workspace, if you serve more than one team | id, name | | companies | Accounts you sell to | id, org_id, name, domain, owner_id | | contacts | People at those companies | id, org_id, company_id, email, owner_id | | deals | Opportunities with a value and a stage | id, org_id, company_id, stage, amount, owner_id, closed_at | | activities | Calls, emails, notes and tasks | id, org_id, subject_type, subject_id, kind, body, due_at |

A few choices pay off for years.

Put org_id on every row

Even if you only have one team today, an org_id column on every business table makes access rules simple and future-proofs the design. Index it. Almost every query and every policy will filter on it.

Model stages as data, not code

Store pipeline stages in a pipeline_stages table with a name, an order and a flag for won or lost. Sales leaders change stages more often than engineers expect. If stages are rows, changing one is an admin screen, not a deploy.

Keep money and time precise

Use numeric for amounts, with a separate currency column, and timestamptz for every timestamp. Floating-point money and time zone confusion are the two bugs that make finance stop trusting a CRM.

Make activities polymorphic, carefully

A single activities table pointing at a company, contact or deal keeps the timeline easy to build. The trade-off is that Postgres can't enforce a foreign key on subject_id alone. Either add three nullable foreign keys with a check constraint that exactly one is set, or accept the polymorphic pair and validate it in a trigger. We usually choose the three foreign keys because the database enforces them.

Roles: decide who sees what, then let the database enforce it

A CRM leaks quietly. A sales rep exports a list they shouldn't see, or a contractor keeps access after leaving. Supabase's answer is Row Level Security (RLS), which puts the rule in Postgres itself so every client, API call and dashboard query gets the same answer.

Pick a small set of roles

For most teams, three are enough:

• Admin can manage users, stages and settings, and see everything in the organization.
• Manager can see and edit every record in the organization.
• Rep can see shared records and edit the records they own.

Store membership in a memberships table with user_id, org_id and role. Supabase documents a pattern for role-based access control using a custom access token hook. It adds the user's role to their JWT as a custom claim, so policies can read it cheaply.

Write policies that read like the rules

With membership in place, a policy for deals might look like this:

alter table public.deals enable row level security;

create policy "members can read deals in their org"
on public.deals for select
to authenticated
using (
  org_id in (
    select org_id from public.memberships
    where user_id = (select auth.uid())
  )
);

create policy "owners and managers can update deals"
on public.deals for update
to authenticated
using (
  owner_id = (select auth.uid())
  or exists (
    select 1 from public.memberships m
    where m.user_id = (select auth.uid())
      and m.org_id = deals.org_id
      and m.role in ('admin', 'manager')
  )
);

Wrapping auth.uid() in a select follows Supabase's performance advice for RLS. It lets Postgres evaluate the function once per query instead of once per row. Index the columns your policies filter on, here memberships.user_id and deals.org_id.

Keep the service key on the server

The service role key bypasses RLS. It belongs in server code and background jobs, never in the browser. Admin screens should run as the signed-in admin, so the same policies apply to them as to everyone else.

The audit log: who changed what, and when

When a deal's value changes the day before quarter close, someone will ask who did it. An audit log answers that without guesswork. Build it in Postgres so it records every change, including ones made outside your app.

A single audit table

create table audit.log (
  id bigint generated always as identity primary key,
  table_name text not null,
  record_id uuid not null,
  action text not null,
  changed_by uuid,
  changed_at timestamptz not null default now(),
  old_data jsonb,
  new_data jsonb
);

Keep it in a separate schema that the API doesn't expose, and give the application no permission to update or delete rows in it.

A trigger on every business table

A PL/pgSQL trigger function can write one row per insert, update or delete. Postgres gives the function the TG_OP operation and the OLD and NEW records, and to_jsonb() turns them into storable JSON. Record auth.uid() as the person who made the change. For changes made by background jobs with the service key, set a session variable that names the job so the log never shows an empty actor without a reason.

Supabase has also published an open-source extension, supa_audit, that sets up generic table auditing. It's worth evaluating before you write your own. Either way, test the audit trail by changing a record and reading the log back.

Decide on retention

Audit tables grow forever unless you decide otherwise. Write down how long you keep entries, and partition or archive older months if volume calls for it.

What to build next

With the model, roles and audit log in place, the visible features become straightforward:

• A company page that shows contacts, deals and the activity timeline in one view.
• A pipeline board backed by pipeline_stages.
• Saved filters and CSV export, which respect RLS automatically because they query as the user.
• Email logging through your provider's webhooks into activities.
• Simple reports built from SQL views, so the numbers come from one definition.

Build or buy?

A custom CRM makes sense when your process is a competitive advantage, when CRM data needs to sit next to product data, or when per-seat pricing grows faster than the team's value from it. It makes less sense when you need a large marketplace of plug-ins tomorrow. Be honest about who will maintain it. Ordinary Postgres, clear policies and a migration history keep that burden small.

Quick checklist

• org_id on every business table, indexed.
• Stages stored as rows, money as numeric, time as timestamptz.
• RLS enabled on every table, with policies tested as each role.
• Service role key used only on the server.
• Audit trigger on every business table, with an actor recorded for every change.
• A written retention rule for the audit log.

If you're weighing a home-grown CRM or want an existing one reviewed, our CRM and internal tools work covers design through to maintenance. Book a free technical assessment, and we'll map your process to a data model you can own.