Jakob Wennberg
a447b29210
* fix(invoices): remaining_amount can no longer be inserted as 0 on an unpaid invoice remaining_amount is NOT NULL DEFAULT 0 and every payment surface (payment dialog, bank match, Stripe sync, agent mark-paid) reads it as the customer's open balance. Four writers omitted it, so their invoices looked settled: the dialog rejected every payment as an overpayment and the bank match saw nothing to clear. Prod carried 337 such open invoices on 2026-08-17 (backfilled the same day, snapshot in _backfill_remaining_20260817). - Migration 20260817191708: BEFORE INSERT trigger invoices_derive_remaining_amount. When remaining_amount is NULL/0 on a real invoice (document_type invoice, not a credit note) with total > 0 and a status that still owes money, it becomes total - paid_amount - deduction_total (>= 0). The ROT/RUT share is a 1513 receivable on Skatteverket, never the customer's, exactly as buildInvoiceWriteData computes it. INSERT only: settlement code owns updates and legitimately writes 0 when paid in full. - pg-real test: derivation, explicit value respected, paid/prior/deduction arithmetic, drafts + overdue, paid/cancelled keep 0, credit notes and proformas untouched, never negative. - Writers fixed as well: proforma -> invoice conversion (dashboard route and MCP commitConvertInvoice), MCP commitCreateInvoice, sandbox seed. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> * fix(sandbox): every row in the seed invoice batch carries remaining_amount + paid_amount PostgREST normalises a bulk insert to the union of keys, so a row that omits a column the others set arrives as NULL, not as the default. Keep the draft row on the same contract as the rest of the batch (CodeRabbit). Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> --------- Co-authored-by: Jakob Wennberg <311770904+jakobwennberg-oss@users.noreply.github.com> Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
48 lines
2.2 KiB
PL/PgSQL
48 lines
2.2 KiB
PL/PgSQL
-- invoices.remaining_amount insert guard.
|
|
--
|
|
-- remaining_amount is NOT NULL DEFAULT 0 (20260323120001). Every payment
|
|
-- surface (payment dialog, bank match, Stripe sync, agent mark-paid) treats it
|
|
-- as the customer's open balance, so a writer that omits it leaves an unpaid
|
|
-- invoice looking settled: the dialog rejects any payment as an overpayment
|
|
-- and the bank match sees nothing to clear. On 2026-08-17 prod carried 337
|
|
-- such open invoices (proforma conversion, MCP create_invoice, sandbox seed,
|
|
-- older imports); they were backfilled the same day. This trigger keeps the
|
|
-- default from ever meaning "0 kr open" again on a fresh unpaid invoice.
|
|
--
|
|
-- Rule (BEFORE INSERT only; updates are owned by the settlement code, which
|
|
-- legitimately writes 0 when an invoice is paid in full):
|
|
-- when remaining_amount is NULL or 0
|
|
-- and the row is a real invoice (document_type invoice, not a credit note)
|
|
-- with total > 0 and a status that still owes money,
|
|
-- derive remaining_amount = total - paid_amount - deduction_total (>= 0).
|
|
-- The ROT/RUT deduction is a receivable on Skatteverket (1513), never the
|
|
-- customer's to pay, so it is excluded exactly as buildInvoiceWriteData does.
|
|
|
|
CREATE OR REPLACE FUNCTION public.invoices_derive_remaining_amount()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF COALESCE(NEW.remaining_amount, 0) = 0
|
|
AND NEW.credited_invoice_id IS NULL
|
|
AND COALESCE(NEW.document_type, 'invoice') = 'invoice'
|
|
AND COALESCE(NEW.total, 0) > 0
|
|
AND COALESCE(NEW.status, 'draft') NOT IN ('paid', 'cancelled', 'credited')
|
|
THEN
|
|
NEW.remaining_amount := GREATEST(
|
|
0,
|
|
ROUND((NEW.total - COALESCE(NEW.paid_amount, 0) - COALESCE(NEW.deduction_total, 0))::numeric, 2)
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
COMMENT ON FUNCTION public.invoices_derive_remaining_amount() IS
|
|
'BEFORE INSERT guard: an unpaid real invoice inserted with remaining_amount NULL/0 gets total - paid_amount - deduction_total, so the NOT NULL DEFAULT 0 can never read as "settled".';
|
|
|
|
DROP TRIGGER IF EXISTS invoices_derive_remaining_amount ON public.invoices;
|
|
CREATE TRIGGER invoices_derive_remaining_amount
|
|
BEFORE INSERT ON public.invoices
|
|
FOR EACH ROW EXECUTE FUNCTION public.invoices_derive_remaining_amount();
|