91 lines
3.2 KiB
SQL
91 lines
3.2 KiB
SQL
-- =====================================================================
|
|
-- DB invariants: run against a closed E2E shift to verify SQL-side rules.
|
|
-- Pass shift id via: psql ... -v shift_id="'<uuid>'"
|
|
-- =====================================================================
|
|
\set ON_ERROR_STOP on
|
|
\timing off
|
|
|
|
\echo
|
|
\echo === shift under test ===
|
|
select id, status, opening_usd, expected_close_usd, declared_close_usd,
|
|
variance_usd, opening_lbp, expected_close_lbp, variance_lbp
|
|
from app.shifts where id = :shift_id;
|
|
|
|
\echo
|
|
\echo === INV1: every completed REPAIR txn has >=1 cash_movements row ===
|
|
select t.id, t.service_code, t.status
|
|
from app.transactions t
|
|
where t.shift_id = :shift_id
|
|
and t.status = 'completed'
|
|
and t.service_code = 'REPAIR'
|
|
and not exists (select 1 from app.cash_movements cm where cm.ref_txn_id = t.id);
|
|
\echo (expect 0 rows)
|
|
|
|
\echo
|
|
\echo === INV2: voided txns have net-zero cash (per currency) ===
|
|
with v as (
|
|
select id from app.transactions
|
|
where shift_id = :shift_id and status = 'voided'
|
|
)
|
|
select cm.ref_txn_id, cm.currency, sum(cm.amount) as net
|
|
from app.cash_movements cm
|
|
join v on v.id = cm.ref_txn_id
|
|
group by cm.ref_txn_id, cm.currency
|
|
having sum(cm.amount) <> 0;
|
|
\echo (expect 0 rows)
|
|
|
|
\echo
|
|
\echo === INV3: opening + Σ cash_movements(post-open) = expected_close ===
|
|
with s as (select * from app.shifts where id = :shift_id),
|
|
mv_usd as (
|
|
select coalesce(sum(amount),0) as total
|
|
from app.cash_movements
|
|
where shift_id = :shift_id and currency = 'USD' and type <> 'opening_float'
|
|
),
|
|
mv_lbp as (
|
|
select coalesce(sum(amount),0) as total
|
|
from app.cash_movements
|
|
where shift_id = :shift_id and currency = 'LBP' and type <> 'opening_float'
|
|
)
|
|
select s.opening_usd, mv_usd.total as movements_usd,
|
|
(s.opening_usd + mv_usd.total) as computed_usd,
|
|
s.expected_close_usd,
|
|
(s.opening_usd + mv_usd.total = s.expected_close_usd) as usd_ok,
|
|
s.opening_lbp, mv_lbp.total as movements_lbp,
|
|
(s.opening_lbp + mv_lbp.total) as computed_lbp,
|
|
s.expected_close_lbp,
|
|
(s.opening_lbp + mv_lbp.total = s.expected_close_lbp) as lbp_ok
|
|
from s, mv_usd, mv_lbp;
|
|
\echo (expect usd_ok = t AND lbp_ok = t)
|
|
|
|
\echo
|
|
\echo === INV4: variance = declared - expected ===
|
|
select id,
|
|
(declared_close_usd - expected_close_usd) as computed_var_usd,
|
|
variance_usd,
|
|
(declared_close_usd - expected_close_usd) = variance_usd as usd_ok,
|
|
(declared_close_lbp - expected_close_lbp) as computed_var_lbp,
|
|
variance_lbp,
|
|
(declared_close_lbp - expected_close_lbp) = variance_lbp as lbp_ok
|
|
from app.shifts where id = :shift_id;
|
|
\echo (expect usd_ok = t AND lbp_ok = t)
|
|
|
|
\echo
|
|
\echo === INV5: cash_movements sign rule never violated (whole DB) ===
|
|
select id, shift_id, type, currency, amount
|
|
from app.cash_movements
|
|
where (type in ('sale_in','fx_swap_in','opening_float') and amount <= 0)
|
|
or (type in ('payout_out','drop_to_safe','bank_deposit','expense','fx_swap_out') and amount >= 0);
|
|
\echo (expect 0 rows)
|
|
|
|
\echo
|
|
\echo === INV6: cash_movements.ref_txn_id always resolves ===
|
|
select cm.id, cm.ref_txn_id
|
|
from app.cash_movements cm
|
|
where cm.ref_txn_id is not null
|
|
and not exists (select 1 from app.transactions t where t.id = cm.ref_txn_id);
|
|
\echo (expect 0 rows)
|
|
|
|
\echo
|
|
\echo === DONE ===
|