Enforcing a Freemium Plan Gate in the Database

A freemium plan gate belongs in the database because that's the one place every path to your data passes through. Enforce seat limits with a trigger on the members table, usage limits with a function that checks and increments a monthly meter atomically, and protect the plan column itself so no user can promote their own organization to Pro. The UI then shows upgrade prompts — but the database is what says no. Here's how LetRelay does it, including a race condition worth fixing in any count-then-insert limit.

Why not enforce plans in the app?

Because plans have the same failure mode as authorization. If the seat check lives in the "Invite user" action, then the CSV import, the API, a future admin screen and a direct call with the public API key all need the same check — and one will forget. With Supabase, clients can write to tables directly through the Data API, so an app-only check is advisory. The argument is the same as for row-level security vs app-layer authorization: put the rule where it can't be bypassed.

The three kinds of gate

Gate 1: seat caps with a trigger

create or replace function public.enforce_member_cap()
returns trigger language plpgsql security definer set search_path = public as $$
declare v_cap int; v_count int;
begin
  if tg_op = 'INSERT' or old.organization_id is distinct from new.organization_id then
    v_cap := public.org_seat_cap(new.organization_id);
    select count(*) into v_count from public.profiles
      where organization_id = new.organization_id and id <> new.id;
    if v_count >= v_cap then
      raise exception 'SEAT_CAP' using hint = 'Upgrade to add more people.';
    end if;
  end if;
  return new;
end $$;
 
create trigger trg_member_cap before insert or update on public.profiles
  for each row execute function public.enforce_member_cap();

Details that matter:

• It fires on update too, when a profile moves organizations — otherwise "join another org" bypasses the cap.
• The cap comes from a function (org_seat_cap) that knows the plan, paid seats and any manual override. One place to change pricing.
• A stable error code (SEAT_CAP) with a human hint. The app maps the code to a friendly message and an upgrade button; it doesn't parse English.

The race condition

Count-then-insert has a gap: two invitations accepted at the same moment can both count 4 people, both pass a cap of 5, and leave the organization with 6. LetRelay's current trigger has this gap; for a seat cap, going one over in a rare collision is low-stakes, but it's worth closing. The fix is to serialize inserts per organization before counting:

perform 1 from public.organizations where id = new.organization_id for update;

Locking the organization's row makes concurrent member inserts for the same organization wait their turn; other organizations are unaffected. An advisory lock on the organization id works too. The same pattern protects the rate limiter described in rate limiting without Redis.

Gate 2: usage meters

Monthly usage — AI assists, exports, messages — is a counter per organization per month:

create table public.ai_usage (
  organization_id uuid not null references public.organizations(id) on delete cascade,
  ym   text not null,              -- '2026-09'
  used int  not null default 0,
  primary key (organization_id, ym)
);

And a gate function the AI pipeline calls before spending tokens:

create or replace function public.ai_gate(p_org uuid)
returns boolean language plpgsql security definer set search_path = public as $$
declare v_plan text; v_ym text := to_char(now(), 'YYYY-MM'); v_used int;
begin
  if not (auth.role() = 'service_role'
          or (auth.role() = 'authenticated' and p_org = public.current_user_org())) then
    return false;
  end if;
  select plan into v_plan from public.organizations where id = p_org;
  if v_plan <> 'pro' then
    select used into v_used from public.ai_usage where organization_id = p_org and ym = v_ym;
    if coalesce(v_used, 0) >= 50 then return false; end if;
  end if;
  insert into public.ai_usage (organization_id, ym, used) values (p_org, v_ym, 1)
    on conflict (organization_id, ym) do update set used = public.ai_usage.used + 1;
  return true;
end $$;

Note the first check. An earlier version took an organization id and didn't verify the caller belonged to it — so any user could call it with another organization's id fifty times and use up that organization's monthly AI. A cross-tenant denial of service, from a function that looks harmless. Any security definer function that accepts a tenant id must check the caller's right to that tenant (or be executable only by the service role).

A new month starts a new row, so there's no reset job. The upsert increment is atomic; like the seat cap, the check before it can overshoot by the number of concurrent calls, which is acceptable for a soft meter.

Gate 3: Pro-only features

Feature gates are the simplest: a helper is_pro(org) used in the policies or functions behind the feature, plus a UI that shows an upgrade card instead of a blank page. Two rules:

• Gate the data, not the page. Hiding the Analytics page is UX; the analytics functions refusing Free organizations is the gate.
• Decide what stays free on purpose. In LetRelay, requests, tasks, rooms and booking meetings are free; advanced features are Pro. Free users should get a complete core product, or they won't stay long enough to upgrade.

Protect the plan column

All of this is pointless if an admin can run update organizations set plan = 'pro' through the API. The plan column needs its own guard:

• A before update trigger that raises if plan changes, unless the change comes from the service role (the payment webhook) or a specific, audited function that sets a transaction-local flag first.
• Row-level security allowing admins to edit their organization's name and settings — but the trigger still blocks the plan column.

In LetRelay only three paths can change a plan: the payment provider's webhook (service role), the site owner's console function, and the Supabase dashboard. Everything else raises.

Downgrades without data loss

When an organization drops back to Free:

• Don't delete anything. Over-cap members keep their accounts; the cap only blocks new members.
• Pro-only features become read-only or hidden, with data preserved for when they upgrade again.
• Give a grace period for failed payments before downgrading, and tell admins exactly what will change and when.

Testing plan gates

Write the tests as a Free organization's real users:

1. Invite people up to the cap — succeeds; one more — fails with SEAT_CAP.
2. Accept two invitations concurrently at cap − 1 — exactly one should succeed (this catches the race).
3. Call the AI gate 50 times — succeeds; the 51st — refused.
4. Call the AI gate for another organization — refused, and that organization's meter unchanged.
5. Try to set your own organization's plan to Pro through the API — fails.
6. Call Pro-only functions — refused.

FAQ

Should plan limits be enforced in the frontend or the database?

The database. The frontend should explain limits and offer upgrades, but only server-side enforcement — ideally in database triggers and functions — can't be bypassed by another client.

How do I stop users from changing their own plan?

Guard the plan column with a trigger that rejects changes unless they come from your payment webhook (service role) or an audited internal function.

How do monthly usage limits reset?

Key the counter by organization and month. A new month writes a new row, so no reset job is needed.

What happens to data when a customer downgrades?

Keep it. Block new over-cap usage and hide or freeze Pro-only features, so upgrading again restores everything.

Sources

• PostgreSQL — Trigger Functions (PL/pgSQL)
• PostgreSQL — CREATE FUNCTION
• Supabase — Database functions
• Supabase — Row Level Security
• OWASP — Authorization Cheat Sheet