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!