67 lines
2.0 KiB
PL/PgSQL
67 lines
2.0 KiB
PL/PgSQL
-- One-shot: run in Supabase Dashboard → SQL Editor **after** schemas/alter_users_rc_bigint.sql
|
|
-- (requires `users.rc` bigint so large balances support small debits/credits).
|
|
-- Atomically debits RC from supply user id 1, credits the buyer, inserts `transactions`.
|
|
-- SECURITY DEFINER avoids RLS/trigger edge cases on direct `users` updates from the API.
|
|
|
|
create or replace function public.purchase_rc(p_buyer_id bigint, p_amount bigint)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_supply bigint := 1;
|
|
begin
|
|
if p_buyer_id is null or p_buyer_id < 1 then
|
|
raise exception 'invalid_buyer';
|
|
end if;
|
|
if p_amount is null or p_amount <= 0 then
|
|
raise exception 'invalid_amount';
|
|
end if;
|
|
|
|
-- Same account: net balance must stay the same; only log for audit.
|
|
if p_buyer_id = v_supply then
|
|
insert into public.transactions ("from", "to", amount, remarks)
|
|
values (v_supply, v_supply, p_amount, 'purchase');
|
|
return (
|
|
select to_jsonb(t)
|
|
from (
|
|
select id, username, cc, rc, created_at, last_logged_at
|
|
from public.users
|
|
where id = p_buyer_id
|
|
) t
|
|
);
|
|
end if;
|
|
|
|
update public.users
|
|
set rc = coalesce(rc, 0) - p_amount
|
|
where id = v_supply
|
|
and coalesce(rc, 0) >= p_amount;
|
|
if not found then
|
|
raise exception 'insufficient_supply';
|
|
end if;
|
|
|
|
update public.users
|
|
set rc = coalesce(rc, 0) + p_amount
|
|
where id = p_buyer_id;
|
|
if not found then
|
|
raise exception 'buyer_not_found';
|
|
end if;
|
|
|
|
insert into public.transactions ("from", "to", amount, remarks)
|
|
values (v_supply, p_buyer_id, p_amount, 'purchase');
|
|
|
|
return (
|
|
select to_jsonb(t)
|
|
from (
|
|
select id, username, cc, rc, created_at, last_logged_at
|
|
from public.users
|
|
where id = p_buyer_id
|
|
) t
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.purchase_rc(bigint, bigint) from public;
|
|
grant execute on function public.purchase_rc(bigint, bigint) to service_role;
|