🚀 Leveling Up with Supabase RPC — My “Sell Honey” Transaction Journey

#supabase #postgres #nextjs #webdev

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!