Your free plan says "up to 10". Where does that 10 actually live?
In most apps I have read, it lives in the interface: a button that disables itself at ten, a count in a store, maybe a check in the route handler that writes the row. That is a suggestion, not a limit. A second tab, a page left open since yesterday, a direct call to the API with a token from devtools, and the eleventh row is in the table.
I put mine in Postgres. This is what that took, and the three things I got wrong on the way.
The setup
The product is a release tracker: you follow the technologies in your stack and it tells you what shipped and whether upgrading needs work. The Free plan follows up to 10.
Follows are the one thing a signed in developer writes, and row level security already limits every row to their own user id, so the browser talks to the database directly. There is no route of mine in the middle:
const { error } = follow
? await follows.insert({ user_id: developerId, technology_id: technologyId })
: await follows.delete().eq("technology_id", technologyId);
That is a deliberate choice, and it is what makes the rest of this possible: if the database refuses, the refusal arrives in the mutation that asked, not in a log I will read next week.
The rule, in the database
create function public.enforce_follow_rules()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
followed integer;
begin
if (select retired from public.technologies where id = new.technology_id) then
raise exception 'A retired Technology cannot be followed'
using errcode = 'FRTRD';
end if;
perform pg_advisory_xact_lock(hashtextextended(new.user_id::text, 0));
if coalesce((select plan from public.plans where user_id = new.user_id), 'free') = 'free' then
select count(*) into followed from public.follows where user_id = new.user_id;
if followed >= 10 then
raise exception 'The Free Plan follows at most 10 Technologies'
using errcode = 'FLIMT';
end if;
end if;
return new;
end;
$$;
create trigger enforce_follow_rules
before insert on public.follows
for each row execute function public.enforce_follow_rules();
Three details in there are worth more than the rest.
The advisory lock. Without it the whole thing is theatre. Two tabs inserting at the same moment both run the count, both read nine, both pass. pg_advisory_xact_lock on a hash of the user id serialises the decision per developer and releases at the end of the transaction. It is exactly the "added from another session" case the feature is supposed to survive, and it is the one I would have shipped broken if I had not written a test that inserts a follow from outside the browser first.
security definer. The trigger applies to the service role too. That is what I want, and it also means my own test fixtures cannot set up a followed retired technology directly: they have to follow first and retire after, which is the only way the case appears in real life anyway.
The plan check. The limit is for free only. What a paid plan allows is not decided yet, and the trigger is the place where that decision will land.
Refusals should be codes, not sentences
The interesting part is not raising the error, it is getting it to the row that has to explain itself.
I gave each rule an SQLSTATE of my own: FLIMT for the limit, FRTRD for a retired technology. PostgREST carries the code to the browser in the error object, so the page never parses a message:
const BY_CODE: Record<string, FollowRejection> = { FLIMT: "limit", FRTRD: "retired" };
export function followRejectionFor(code: string | undefined): FollowRejection {
return (code && BY_CODE[code]) || "unknown";
}
Anything I did not plan for, a dropped connection or an expired session, becomes unknown, which still has a remedy to offer ("try again") instead of failing silently. Two rules, three outcomes, and the wording of the database error stays where it belongs: in the console.
Pick codes in the XX000 custom space and write them down somewhere the next person will look. Mine are in the migration, in a comment right above the function.
The bug the first real refusal found
The UI follows optimistically: the row flips, the count moves, and if the database says no the previous state goes back. I had built that with TanStack Query, with one useMutation inside the follow toggle component.
It worked perfectly until the first refusal arrived, and then the refusal was invisible.
One mutation per row means the error lives in the component that was pressed. The row that has to show "this technology is retired" is a sibling; it never sees it. Worse, the count in the top bar is computed somewhere else again, so three parts of the same page can each hold a different idea of what just happened.
The fix is dull and it is the whole lesson: one mutation for the page, in the provider, exposed through context.
const value: Follows = {
count: following.size,
atLimit: following.size >= FREE_FOLLOW_LIMIT,
isFollowing: (technologyId) => following.has(technologyId),
toggle: (technologyId) => mutation.mutate({ technologyId, follow: !following.has(technologyId) }),
isPending: (technologyId) => mutation.isPending && mutation.variables?.technologyId === technologyId,
rejectionOn: (technologyId) => (technologyId === rejected ? rejection : null),
};
The count, the block at ten and the refusal now come from one place, so they cannot drift apart.
What the interface still owes you
Enforcing the rule in the database does not excuse the UI from anticipating it. At ten of ten the unpressed buttons get aria-disabled="true" and point at a note that says what to do, and they stay in the tab order. A real disabled would take them out of it, and a keyboard user would meet a control that has silently vanished and no explanation of why.
There is a nice side effect. Playwright refuses to click a control marked aria-disabled, so the test has to say click({ force: true }). That line is the proof that the block is there: if the attribute went missing, the forced click would go through and the assertion after it would fail.
The number 10 is now written in two places, FREE_FOLLOW_LIMIT in the code and the trigger in the migration. That duplication bothers me a little, and it is the correct trade: the one in the migration is the one that decides, the one in the code is how the page explains itself before asking.
Was it worth it
The trigger is 25 lines. It ships with the schema, it applies to every client that will ever exist, including the ones I have not written yet, and it turned a plan limit from a claim into a fact.
What it does not do is tell your users anything. That is still the interface's job, and the trigger only makes it easier, because now the page reacts to a rule instead of pretending to be one.
