122 lines
4.6 KiB
PL/PgSQL
122 lines
4.6 KiB
PL/PgSQL
-- =====================================================================
|
|
-- Migration 0019 — Cash-movement sign guard + fix record_cash_drop.
|
|
--
|
|
-- Two bugs in 0015 + 0002:
|
|
--
|
|
-- (a) record_cash_drop in 0015 inserted into app.cash_movements using
|
|
-- column names that do not exist (`movement_type`, `external_ref`)
|
|
-- and an enum value that doesn't exist (`safe_drop`). The real
|
|
-- schema is `type` / `note` and the enum value is `drop_to_safe`.
|
|
-- Worse, it inserted the drop amount as POSITIVE, which would make
|
|
-- `expected_close_usd` go UP when cash physically left the till.
|
|
--
|
|
-- (b) app.cash_movements has no constraint that the sign of `amount`
|
|
-- matches the movement `type`. A buggy or malicious insert with
|
|
-- type='drop_to_safe' amount=+1000 would silently increase the
|
|
-- expected drawer balance.
|
|
--
|
|
-- This migration:
|
|
-- 1. Adds a BEFORE INSERT trigger on app.cash_movements enforcing the
|
|
-- sign-vs-type rule.
|
|
-- 2. Replaces app.record_cash_drop with a correct implementation
|
|
-- using the real columns and a negative sign.
|
|
--
|
|
-- Threat-model rows: 7, 14, 22, 25.
|
|
-- =====================================================================
|
|
|
|
-- =====================================================================
|
|
-- Sign-vs-type guard
|
|
-- =====================================================================
|
|
create or replace function app._cash_mov_sign_check()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
-- + cash into till
|
|
if new.type in ('opening_float','sale_in','fx_swap_in') then
|
|
if new.amount <= 0 then
|
|
raise exception 'cash_movements.type=% must have positive amount (got %)',
|
|
new.type, new.amount;
|
|
end if;
|
|
-- - cash out of till
|
|
elsif new.type in ('payout_out','drop_to_safe','bank_deposit',
|
|
'expense','fx_swap_out') then
|
|
if new.amount >= 0 then
|
|
raise exception 'cash_movements.type=% must have negative amount (got %)',
|
|
new.type, new.amount;
|
|
end if;
|
|
-- 'adjustment' is the only type that may legitimately go either way
|
|
-- (manager-approved correction). It must still be non-zero (already
|
|
-- enforced by the table CHECK).
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists trg_cash_mov_sign_check on app.cash_movements;
|
|
create trigger trg_cash_mov_sign_check
|
|
before insert on app.cash_movements
|
|
for each row execute function app._cash_mov_sign_check();
|
|
|
|
-- =====================================================================
|
|
-- Replace record_cash_drop with a correct implementation.
|
|
-- Drops are negative cash_movement rows of type 'drop_to_safe'.
|
|
-- =====================================================================
|
|
create or replace function app.record_cash_drop(
|
|
p_shift_id uuid,
|
|
p_drop_usd numeric,
|
|
p_drop_lbp numeric,
|
|
p_notes text default null
|
|
) returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_shop uuid;
|
|
v_status app.shift_status;
|
|
v_user uuid;
|
|
begin
|
|
if p_drop_usd is null or p_drop_lbp is null
|
|
or p_drop_usd < 0 or p_drop_lbp < 0 then
|
|
raise exception 'drop amounts must be non-negative numbers';
|
|
end if;
|
|
if coalesce(p_drop_usd,0) = 0 and coalesce(p_drop_lbp,0) = 0 then
|
|
raise exception 'must drop > 0 in at least one currency';
|
|
end if;
|
|
|
|
select shop_id, status, user_id
|
|
into v_shop, v_status, v_user
|
|
from app.shifts
|
|
where id = p_shift_id;
|
|
if v_shop is null then
|
|
raise exception 'shift % not found', p_shift_id;
|
|
end if;
|
|
if v_status <> 'open' then
|
|
raise exception 'shift must be open to record a drop (got %)', v_status;
|
|
end if;
|
|
if v_user <> auth.uid() and not app.has_role_in_shop(v_shop, 'manager') then
|
|
raise exception 'only the shift owner or a manager may record a drop';
|
|
end if;
|
|
|
|
if p_drop_usd > 0 then
|
|
insert into app.cash_movements(shift_id, type, currency, amount, note)
|
|
values (p_shift_id, 'drop_to_safe', 'USD', -p_drop_usd,
|
|
coalesce(p_notes, 'mid-day safe drop'));
|
|
end if;
|
|
if p_drop_lbp > 0 then
|
|
insert into app.cash_movements(shift_id, type, currency, amount, note)
|
|
values (p_shift_id, 'drop_to_safe', 'LBP', -p_drop_lbp,
|
|
coalesce(p_notes, 'mid-day safe drop'));
|
|
end if;
|
|
|
|
perform app.log_auth_event('safe_drop_recorded', v_shop, null,
|
|
jsonb_build_object('shift_id', p_shift_id,
|
|
'usd', p_drop_usd, 'lbp', p_drop_lbp));
|
|
end;
|
|
$$;
|
|
revoke all on function app.record_cash_drop(uuid, numeric, numeric, text) from public;
|
|
grant execute on function app.record_cash_drop(uuid, numeric, numeric, text) to authenticated;
|
|
|
|
-- End migration 0019 ----------------------------------------------------
|