Make issued invoices immutable in PostgreSQL

Make issued rows insert-only in two layers. A row trigger that raises on UPDATE and DELETE turns accidental edits into errors, but privileges are the boundary: the application role owns nothing and gets only SELECT and INSERT. Corrections are new credit notes linked to the original, and the adjusted total is derived.

CREATE ROLE app LOGIN;  -- the application role; owns no table

CREATE TABLE documents (  -- reduced to the columns used here
  id          uuid   PRIMARY KEY,
  kind        text   NOT NULL CHECK (kind IN ('invoice', 'credit_note')),
  corrects_id uuid   REFERENCES documents (id),
  total_minor bigint NOT NULL CHECK (total_minor >= 0),
  CHECK ((kind = 'invoice') = (corrects_id IS NULL))
);

CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION '% on % is not allowed', TG_OP, TG_TABLE_NAME; END $$;

CREATE TRIGGER no_change BEFORE UPDATE OR DELETE ON documents
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

GRANT SELECT, INSERT ON documents TO app;  -- no UPDATE, DELETE or TRUNCATE
-- repeat both for document_lines, rule_sets and document_events

-- A correction is a credit_note row whose corrects_id is the original's id,
-- with the original's amounts copied. The adjusted total is derived:
SELECT o.total_minor - COALESCE(sum(c.total_minor), 0) AS adjusted_minor
FROM documents o
LEFT JOIN documents c ON c.corrects_id = o.id
WHERE o.id = $1
GROUP BY o.id;

#Why does it work?

• The trigger fires on every UPDATE and DELETE from any code path. It is a guard rail: the table owner can disable or drop it, and row triggers do not fire on TRUNCATE.
• Privileges are the boundary. With no UPDATE, DELETE or TRUNCATE privilege, PostgreSQL rejects the statement with permission denied before any trigger runs, and a non-owner cannot disable the trigger.
• A full reversal copies the original's stored amounts instead of recomputing them. A partial credit defaults to the original's rule set, so tax reverses at the rate charged; check your market's rules.
• Status changes (sent, paid, submitted to a gateway) go into an append-only document_events table.

#When should you not use it?

• Drafts, quotes and carts: keep them editable and out of the guarded tables, and freeze at issue.
• Migrations cannot backfill history without disabling the guard: add nullable columns with a documented meaning for legacy rows, and fix a frozen bug forward.
• Credit notes only reduce. A correction that raises the amount is a new invoice or a debit note, depending on the market.