This content originally appeared on DEV Community and was authored by Egor
This week, I implemented a new feature for our beekeeping management platform 
:
When a beekeeper sells honey, the system must deduct the sold amount from the batch table and record the sale in a separate sold_honey table — atomically.
At first, I planned to try doing this with two Supabase SDK calls:
await supabase.from('honey_batches').update(...);
await supabase.from('sold_honey').insert(...);
But then I realized the problem —
What if the first succeeds but the second fails? 
That would leave my data inconsistent (a nightmare for anything involving quantities or money).
So I thought a lot and finally discovered a better way: Supabase RPC (Remote Procedure Call).
I wrote a Postgres function (sell_honey()) that:
- Locks the batch row (
FOR UPDATE) to prevent race conditions - Validates available stock
- Deducts weight from the honey batch
- Inserts a record into
sold_honey - Runs all of this in one transaction — if anything fails, it rolls back automatically
The full Supabase RPC function is as below:
create or replace function public.sell_honey(
p_honey_batch_id bigint,
p_batch_reference_id text,
p_weight_sold numeric,
p_price_kilo numeric,
p_buyer text
)
returns table(sold_id bigint, remaining_weight text)
language plpgsql
as $$
declare
v_current_weight numeric;
v_remaining numeric;
begin
if p_weight_sold is null or p_weight_sold <= 0 then
raise exception 'weight_sold must be a positive number';
end if;
-- Lock the batch row to prevent concurrent modifications
select
coalesce(nullif(regexp_replace(h.weight, '[^0-9\.]', '', 'g'), ''), '0')::numeric
into v_current_weight
from public.honey_batches h
where h.id = p_honey_batch_id
for update;
if v_current_weight is null then
raise exception 'Batch with id % not found', p_honey_batch_id;
end if;
if v_current_weight < p_weight_sold then
raise exception 'Insufficient honey in batch. Available: % kg', v_current_weight;
end if;
-- compute remaining
v_remaining := v_current_weight - p_weight_sold;
-- Update honey_batches.weight (store as text to match existing schema)
update public.honey_batches
set weight = trim(to_char(v_remaining, 'FM9999999990.00'))
where id = p_honey_batch_id;
-- Insert sold record
insert into public.sold_honey (
honey_batch_id,
batch_reference_id,
weight_sold,
price_kilo,
buyer,
created_at
)
values (
p_honey_batch_id,
p_batch_reference_id,
p_weight_sold,
p_price_kilo,
p_buyer,
now()
)
returning id into sold_id;
-- Return sold id and remaining weight (as text)
remaining_weight := trim(to_char(v_remaining, 'FM9999999990.00'));
return next;
end;
$$;
Then I integrated it in my Next.js frontend:
await supabase.rpc('sell_honey', {
p_batch_id: id,
p_weight_sold: 5.2,
p_price_kilo: 18.0,
p_buyer: 'John Bee',
});
Supabase executes the function securely on Postgres — no partial updates, no data corruption.
The cherry on top?
I learned how Row Level Security (RLS) interacts with RPCs — and how to safely use security definer so the function runs with elevated privileges but still restricts direct table writes.
Lesson learned:
Supabase RPCs are an underrated superpower.
They turn your database into a reliable logic layer — atomic, consistent, and secure.
Curious if others have used Supabase RPCs or security definer for production logic?
Would love to hear your experiences.
Thanks for kind reading!
This content originally appeared on DEV Community and was authored by Egor