297 lines
11 KiB
PL/PgSQL
297 lines
11 KiB
PL/PgSQL
-- =====================================================================
|
|
-- 0023_fx_rates_and_swap.sql
|
|
--
|
|
-- Two related holes around foreign exchange:
|
|
--
|
|
-- (1) The cashier types an arbitrary `fx_rate_used` on every USD/LBP
|
|
-- transaction. Nothing on the server compares it to the daily
|
|
-- posted rate. A cashier can rate a $100 sale at 1 USD = 90,000 LBP
|
|
-- while the till uses 1 USD = 89,500 LBP and pocket the spread.
|
|
--
|
|
-- (2) `cash_movement_type` has 'fx_swap_in' and 'fx_swap_out' but no
|
|
-- function posts them as a matched pair. A cashier swapping $100
|
|
-- out of the till for 8.95M LBP today does it manually with two
|
|
-- uncoupled cash_movements rows; the sign-guard added in 0019
|
|
-- catches gross sign mistakes but not amount mismatches.
|
|
--
|
|
-- This migration:
|
|
-- * adds `app.fx_rates(shop_id, effective_from, usd_to_lbp_rate,
|
|
-- tolerance_pct)` — append-only history of the shop's posted rate.
|
|
-- * adds `app.compute_fx_window(shop, effective)` returning the
|
|
-- accepted band [low, high] for the currently-active rate.
|
|
-- * adds a constraint trigger on `app.transactions` that, only when
|
|
-- a rate is published for the shop, enforces fx_rate_used falls
|
|
-- within the band whenever both gross_usd and gross_lbp are non-zero
|
|
-- (genuine cross-currency txn) OR for explicit FX swaps.
|
|
-- * adds `app.record_fx_swap(shop, till, p_usd_amount, p_lbp_amount,
|
|
-- p_fx_rate)` which posts both legs atomically and refuses the call
|
|
-- unless |p_usd_amount * fx_rate - p_lbp_amount| <= 1 LBP.
|
|
--
|
|
-- Shops that don't seed `fx_rates` keep working unchanged.
|
|
-- =====================================================================
|
|
|
|
set search_path = app, public;
|
|
|
|
create table if not exists app.fx_rates (
|
|
id uuid primary key default gen_random_uuid(),
|
|
shop_id uuid not null references app.shops(id) on delete cascade,
|
|
effective_from timestamptz not null default now(),
|
|
effective_to timestamptz,
|
|
-- Number of LBP per 1 USD (e.g. 89500).
|
|
usd_to_lbp_rate numeric(14,2) not null check (usd_to_lbp_rate > 0),
|
|
-- Allowed deviation either side of the posted rate, as a percent
|
|
-- (e.g. 1.0 = ±1%). Defaults to 0.5%.
|
|
tolerance_pct numeric(6,3) not null default 0.5
|
|
check (tolerance_pct >= 0 and tolerance_pct <= 25),
|
|
note text,
|
|
created_at timestamptz not null default now(),
|
|
created_by uuid not null references auth.users(id) default auth.uid(),
|
|
check (effective_to is null or effective_to > effective_from)
|
|
);
|
|
|
|
create index if not exists idx_fx_rates_lookup
|
|
on app.fx_rates(shop_id, effective_from desc);
|
|
|
|
alter table app.fx_rates enable row level security;
|
|
alter table app.fx_rates force row level security;
|
|
|
|
drop policy if exists fx_rates_select on app.fx_rates;
|
|
create policy fx_rates_select on app.fx_rates
|
|
for select using (
|
|
app.has_any_role_in_shop(shop_id,
|
|
array['cashier','manager','owner']::app.business_role[])
|
|
);
|
|
|
|
drop policy if exists fx_rates_write on app.fx_rates;
|
|
create policy fx_rates_write on app.fx_rates
|
|
for all using (
|
|
app.has_any_role_in_shop(shop_id,
|
|
array['manager','owner']::app.business_role[])
|
|
) with check (
|
|
app.has_any_role_in_shop(shop_id,
|
|
array['manager','owner']::app.business_role[])
|
|
);
|
|
|
|
-- Append-only on history: only effective_to may be moved forward. Same
|
|
-- pattern as fee_schedule in 0021.
|
|
create or replace function app._fx_rates_immutable()
|
|
returns trigger language plpgsql as $$
|
|
begin
|
|
if tg_op = 'DELETE' then
|
|
raise exception 'fx_rates is append-only';
|
|
end if;
|
|
if (old.shop_id, old.usd_to_lbp_rate, old.tolerance_pct, old.effective_from)
|
|
is distinct from
|
|
(new.shop_id, new.usd_to_lbp_rate, new.tolerance_pct, new.effective_from)
|
|
then
|
|
raise exception 'fx_rates columns are immutable; close the row and insert a new one';
|
|
end if;
|
|
if old.effective_to is not null then
|
|
raise exception 'fx_rates row already closed';
|
|
end if;
|
|
if new.effective_to is null then
|
|
raise exception 'effective_to must be set to close an fx_rates row';
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
drop trigger if exists trg_fx_rates_immutable on app.fx_rates;
|
|
create trigger trg_fx_rates_immutable
|
|
before update or delete on app.fx_rates
|
|
for each row execute function app._fx_rates_immutable();
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Lookup helper: return the active rate band for a shop right now.
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function app.current_fx_band(p_shop uuid)
|
|
returns table (
|
|
rate numeric,
|
|
band_low numeric,
|
|
band_high numeric,
|
|
tolerance_pct numeric,
|
|
rate_id uuid
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
select
|
|
f.usd_to_lbp_rate,
|
|
f.usd_to_lbp_rate * (1 - f.tolerance_pct / 100.0),
|
|
f.usd_to_lbp_rate * (1 + f.tolerance_pct / 100.0),
|
|
f.tolerance_pct,
|
|
f.id
|
|
from app.fx_rates f
|
|
where f.shop_id = p_shop
|
|
and f.effective_from <= now()
|
|
and (f.effective_to is null or f.effective_to > now())
|
|
order by f.effective_from desc
|
|
limit 1;
|
|
$$;
|
|
revoke all on function app.current_fx_band(uuid) from public;
|
|
grant execute on function app.current_fx_band(uuid) to authenticated;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Constraint trigger on app.transactions:
|
|
-- when a posted rate exists for the shop, fx_rate_used must lie within
|
|
-- the band whenever the txn is a genuine USD/LBP cross-currency event.
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function app._txn_fx_rate_check()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
band record;
|
|
begin
|
|
-- Skip non-money / void rows.
|
|
if new.status <> 'completed' then return null; end if;
|
|
if coalesce(new.fx_rate_used, 0) = 0 then return null; end if;
|
|
|
|
-- Only police txns that actually mix the two currencies, or where
|
|
-- the cashier deliberately recorded an fx_rate (e.g. payment in USD,
|
|
-- gross in LBP).
|
|
if not (coalesce(new.gross_usd,0) <> 0 and coalesce(new.gross_lbp,0) <> 0)
|
|
and new.service_code <> 'FX_SWAP'
|
|
then
|
|
-- Some single-currency txns also store the day's rate for
|
|
-- reporting; still validate it against the band so a wildly wrong
|
|
-- value can't slip through.
|
|
null;
|
|
end if;
|
|
|
|
select * into band from app.current_fx_band(new.shop_id);
|
|
if band.rate_id is null then
|
|
return null; -- shop hasn't published a rate yet
|
|
end if;
|
|
|
|
if new.fx_rate_used < band.band_low or new.fx_rate_used > band.band_high then
|
|
raise exception
|
|
'fx_rate_used % outside posted band [%, %] (rate %, tolerance % %%)',
|
|
new.fx_rate_used, band.band_low, band.band_high,
|
|
band.rate, band.tolerance_pct;
|
|
end if;
|
|
return null;
|
|
end;
|
|
$$;
|
|
drop trigger if exists trg_txn_fx_rate_check on app.transactions;
|
|
create constraint trigger trg_txn_fx_rate_check
|
|
after insert on app.transactions
|
|
deferrable initially deferred
|
|
for each row execute function app._txn_fx_rate_check();
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- record_fx_swap: post the two cash_movements legs atomically.
|
|
-- direction:
|
|
-- p_usd_out > 0 means USD leaves the till and LBP comes in
|
|
-- -> fx_swap_out USD, fx_swap_in LBP
|
|
-- p_usd_out < 0 means USD comes into the till and LBP leaves
|
|
-- -> fx_swap_in USD, fx_swap_out LBP
|
|
-- The amounts on both sides must agree to within 1 LBP at p_fx_rate,
|
|
-- and p_fx_rate must lie within the posted band (when one exists).
|
|
-- ---------------------------------------------------------------------
|
|
do $$ begin
|
|
-- Add FX_SWAP service code if not already present, so the txn header
|
|
-- has a real service to attach to (the recorded txn carries no
|
|
-- product detail row).
|
|
if not exists (select 1 from app.services where code = 'FX_SWAP') then
|
|
insert into app.services(code, name, category)
|
|
values ('FX_SWAP', 'Currency Exchange', 'cash_ops');
|
|
end if;
|
|
end $$;
|
|
|
|
create or replace function app.record_fx_swap(
|
|
p_shop uuid,
|
|
p_till uuid,
|
|
p_usd_out numeric, -- + USD leaves till, - USD enters till
|
|
p_lbp_in numeric, -- + LBP enters till when usd_out>0, must be opposite sign
|
|
p_fx_rate numeric,
|
|
p_notes text default null
|
|
) returns uuid
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_shift uuid;
|
|
v_diff numeric;
|
|
v_txn uuid;
|
|
band record;
|
|
begin
|
|
if p_usd_out is null or p_lbp_in is null or p_fx_rate is null or p_fx_rate <= 0 then
|
|
raise exception 'usd_out, lbp_in and positive fx_rate are required';
|
|
end if;
|
|
if p_usd_out = 0 then
|
|
raise exception 'usd_out cannot be 0';
|
|
end if;
|
|
if sign(p_usd_out) = sign(p_lbp_in) then
|
|
raise exception 'usd_out and lbp_in must have opposite signs (one in, one out)';
|
|
end if;
|
|
|
|
-- Sanity: |usd_out| * rate must equal |lbp_in| within 1 LBP.
|
|
v_diff := abs(abs(p_usd_out) * p_fx_rate - abs(p_lbp_in));
|
|
if v_diff > 1 then
|
|
raise exception
|
|
'fx swap mismatch: |usd_out|*rate = % but |lbp_in| = % (diff %)',
|
|
abs(p_usd_out) * p_fx_rate, abs(p_lbp_in), v_diff;
|
|
end if;
|
|
|
|
-- Rate band (only enforced when a rate is published).
|
|
select * into band from app.current_fx_band(p_shop);
|
|
if band.rate_id is not null
|
|
and (p_fx_rate < band.band_low or p_fx_rate > band.band_high)
|
|
then
|
|
raise exception
|
|
'fx_rate % outside posted band [%, %] (rate %, tolerance % %%)',
|
|
p_fx_rate, band.band_low, band.band_high, band.rate, band.tolerance_pct;
|
|
end if;
|
|
|
|
-- Caller must have an open shift on this till.
|
|
select id into v_shift from app.shifts
|
|
where till_id = p_till and shop_id = p_shop
|
|
and user_id = auth.uid() and status = 'open'
|
|
order by opened_at desc limit 1;
|
|
if v_shift is null then
|
|
raise exception 'no open shift for this till';
|
|
end if;
|
|
|
|
-- Create a header txn (gross_usd/lbp = 0 — money does not enter or
|
|
-- leave the shop, just changes currency). The fx_rate is recorded.
|
|
v_txn := app._insert_txn(p_shop, p_till, 'FX_SWAP', 'cash_usd'::app.payment_method,
|
|
0, 0, 0, 0, 0, 0, p_fx_rate,
|
|
null, null, null, null, null, p_notes);
|
|
|
|
-- USD leg.
|
|
insert into app.cash_movements(shift_id, type, currency, amount, ref_txn_id, note)
|
|
values (
|
|
v_shift,
|
|
case when p_usd_out > 0
|
|
then 'fx_swap_out'::app.cash_movement_type
|
|
else 'fx_swap_in'::app.cash_movement_type end,
|
|
'USD'::app.currency_code,
|
|
-p_usd_out, -- p_usd_out is the OUTflow amount
|
|
v_txn,
|
|
'fx swap'
|
|
);
|
|
|
|
-- LBP leg.
|
|
insert into app.cash_movements(shift_id, type, currency, amount, ref_txn_id, note)
|
|
values (
|
|
v_shift,
|
|
case when p_lbp_in > 0
|
|
then 'fx_swap_in'::app.cash_movement_type
|
|
else 'fx_swap_out'::app.cash_movement_type end,
|
|
'LBP'::app.currency_code,
|
|
p_lbp_in,
|
|
v_txn,
|
|
'fx swap'
|
|
);
|
|
|
|
return v_txn;
|
|
end;
|
|
$$;
|
|
revoke all on function app.record_fx_swap(uuid, uuid, numeric, numeric, numeric, text) from public;
|
|
grant execute on function app.record_fx_swap(uuid, uuid, numeric, numeric, numeric, text) to authenticated;
|