Invoices Postgres Refuses to Change

Spanish tax law has a sentence aimed at software, not at people. Since 2021, article 29.2.j of the General Tax Law obliges whoever makes, sells or uses an invoicing program to make sure it guarantees "the integrity, conservation, accessibility, legibility, traceability and inalterability of the records, without interpolations, omissions or alterations that leave no note in the systems themselves" (my translation). Real Decreto 1007/2023 turns that into requirements, and the invoicing regulation has said for years that "the numbering of invoices within each series shall be correlative".

So an issued invoice can't change, it can't disappear, and its number has to follow the previous one. In an app, all three sound like a few ifs in the code that issues invoices. That is where I started with Sumalos, my invoicing app for freelancers, and it didn't last long.

The code that issues invoices is one path to the table. There's also the code that edits drafts, the one that records payments, the one that merges duplicate contacts, the worker that sends reminders, the AI assistant that drafts and issues invoices from a chat, every migration I will ever write, and me at a psql prompt on a bad day. The law doesn't care which of them changed the invoice. So the question for this post: how do you make the database itself refuse, so that no path, current or future, can get around it?

A draft is not an invoice yet

The first decision makes everything after it simpler. A draft is a scratch pad: no number, no fixed customer, no fixed totals, and it can be edited or deleted freely. It becomes an invoice at one moment, in one transaction, and from then on only two things can happen to it.

Because drafts have no number, deleting one can't leave a gap. Because issuing is a single transaction, there's no half-issued state for a crash to leave behind. And because the only way out of issued is voided, the state machine is small enough to write in SQL.

The trigger that freezes an issued invoice

Here is the guard as it runs today, trimmed of the long column lists and of an exception for merging duplicate contacts. It fires before every UPDATE and DELETE on invoices, from any connection, whatever code sent the statement:

CREATE OR REPLACE FUNCTION invoices_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.status <> 'draft' THEN
      RAISE EXCEPTION 'invoice % is issued and cannot be deleted', OLD.id USING ERRCODE = 'check_violation';
    END IF;
    RETURN OLD;
  END IF;
  IF OLD.status = 'draft' THEN
    RETURN NEW;
  END IF;
  IF NOT (NEW.status = OLD.status OR (OLD.status = 'issued' AND NEW.status = 'voided')) THEN
    RAISE EXCEPTION 'invoice % cannot go from % to %', OLD.id, OLD.status, NEW.status USING ERRCODE = 'check_violation';
  END IF;
  IF (NEW.organization_id, NEW.kind, NEW.series_id, NEW.number, NEW.full_number, NEW.issuer, NEW.customer,
      -- ...dates, currency, every total, the tax breakdown, notes, template, language...
      NEW.exchange_rate, NEW.language, NEW.customer_reference, NEW.suplidos_total)
     IS DISTINCT FROM
     (OLD.organization_id, OLD.kind, OLD.series_id, OLD.number, OLD.full_number, OLD.issuer, OLD.customer,
      OLD.exchange_rate, OLD.language, OLD.customer_reference, OLD.suplidos_total) THEN
    RAISE EXCEPTION 'invoice % is issued and cannot be changed', OLD.id USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END;
$$;

Three things in there carry the design. A draft returns NEW straight away, so everything about drafts stays cheap. The status can only stay the same or go from issued to voided, so nobody can "un-issue" an invoice to edit it and issue it again. And the frozen columns are compared as one row value with IS DISTINCT FROM, which treats two NULLs as equal, so an update that touches anything on the list fails as a whole.

What's left outside the list is what a business legitimately changes after issuing: internal notes, the reminder settings, the generated PDF and the void fields. Payments live in their own table, so recording one never touches the invoice row. The issuer and the customer are stored as JSON snapshots taken at issue time, which is why a customer can move house without rewriting last year's invoices.

The lines get a guard of their own, because an invoice whose header can't change but whose lines can is not frozen at all:

CREATE FUNCTION invoice_lines_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  target uuid := CASE WHEN TG_OP = 'DELETE' THEN OLD.invoice_id ELSE NEW.invoice_id END;
BEGIN
  IF EXISTS (SELECT 1 FROM invoices WHERE id = target AND status <> 'draft')
     OR (TG_OP = 'UPDATE' AND EXISTS (SELECT 1 FROM invoices WHERE id = OLD.invoice_id AND status <> 'draft')) THEN
    RAISE EXCEPTION 'invoice % is issued: its lines cannot change', target USING ERRCODE = 'check_violation';
  END IF;
  RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$;

The second condition is the subtle one: an UPDATE that moves a line from an issued invoice to a draft would pass a check on NEW.invoice_id alone, so the old parent is checked too.

On top of the triggers, a foreign key does the last bit. invoices.organization_id is ON DELETE RESTRICT, so an organization with issued invoices can't be deleted at all, even by a cascade from somewhere else. The app checks first and says why ("issued invoices must be kept for at least four years"), but the database would refuse anyway.

The comment at the top of the migration describes the split I settled on: "core-api relies on these as a last line of defence; its own checks give the friendly errors." The app should never hit a trigger. When it does, that's a bug, and the bug fails closed.

Numbers without gaps

Correlative numbering sounds like the one thing databases already solve. Postgres has sequences, and a sequence is the wrong tool here, for a reason its own documentation spells out: the value obtained by nextval "is not reclaimed for re-use if the calling transaction later aborts". That's what makes sequences fast under concurrency, and it means any failed issue (a validation error after the number was taken, a lost connection, a deadlock) burns a number forever. For order ids that's fine. For invoice 2026-0042, the gap is the first thing an inspector asks about.

So each series has a counter row, invoice_series.next_number, and the number is taken inside the transaction that issues the invoice, under a row lock:

/** A series' row, made if missing, locked until the end of the transaction. */
async function lockSeriesRow(tx: Tx, organizationId: string, kind: InvoiceKind, code: string) {
  await tx.insert(invoiceSeries).values({ id: Bun.randomUUIDv7(), organizationId, kind, code }).onConflictDoNothing();
  const [series] = await tx
    .select()
    .from(invoiceSeries)
    .where(and(eq(invoiceSeries.organizationId, organizationId), eq(invoiceSeries.code, code)))
    .for("update");

takeNumber then reads next_number, refuses an issue date earlier than the last one in the series (correlative numbers with dates that go backwards are a different red flag), and writes next_number + 1. If anything later in the transaction throws, the increment rolls back with everything else, and the number goes back to being next. The lock is held until commit, so a second issue in the same series waits its turn:

The cost is that issuing is serialized per series. For a freelancer, or a small company issuing a few invoices an hour, that wait is invisible; it would matter for a shop issuing thousands a minute, and that shop wouldn't be using one series.

The lock is the mechanism and two unique indexes are the backstop: one on (series_id, number) and one on (organization_id, full_number). If some future code path forgets the lock, the second writer of a number gets a constraint violation, not a duplicate invoice. The series code comes from the organization's numbering format and the issue date (2026-0001 by default, or 26/001, F2026-0001, a monthly series and a few more), and simplified and rectifying invoices get their own series with a T or R in front, as the regulation asks.

Fixing a mistake without editing anything

A frozen invoice still needs a way to be wrong. The law's answer, and the app's, is another invoice. rectifyInvoice makes a new draft linked to the original, either by differences (its lines copied with the quantities negated to cancel it, or blank for a partial correction) or by substitution (a full restatement that replaces the original). The rectifying invoice is issued like any other: frozen, numbered in the R series, and its own row.

Voiding is for an invoice that should never have been issued. It's the one status change the trigger allows, and the number stays used: the invoice is still there, marked void, with who did it and why. A void is refused once a rectifying invoice has been issued against it, so the two ways out can't be combined into a mess.

Both paths also respect closed accounting periods. That check is in the app's issue and void functions, not in a trigger, and I'll come back to why that matters.

A chain the tax office can check

Freezing rows protects the invoices from the app. It doesn't prove anything to anyone outside, and it can't stop the database's owner. Spain's answer to that is VERI*FACTU: every issued or voided invoice produces a billing record, each record carries a SHA-256 fingerprint that includes the previous record's fingerprint, and the record is sent to the tax agency (AEAT) as it's made. Change or delete an old invoice and every fingerprint after it stops matching what the AEAT already holds.

In Sumalos the record is made in the same transaction that issues the invoice, from the row that was just frozen. The chain head (one per organization and issuer tax ID) is locked the same way the series is, so records join the chain one at a time and each gets the next seq:

await tx.insert(verifactuChains).values({ organizationId, issuerTaxId }).onConflictDoNothing();
const [head] = await tx
  .select()
  .from(verifactuChains)
  .where(and(eq(verifactuChains.organizationId, organizationId), eq(verifactuChains.issuerTaxId, issuerTaxId)))
  .for("update");
 
const [last] = head?.lastRecordId ? await tx.select().from(verifactuRecords).where(eq(verifactuRecords.id, head.lastRecordId)) : [];
const previous = last ? { issuerTaxId: last.issuerTaxId, invoiceNumber: last.invoiceNumber, issueDate: last.issueDate, hash: last.hash } : null;

The fingerprint itself is defined by the AEAT down to the byte: named fields joined as name=value with &, each value trimmed, upper-case hex. Getting one character wrong produces a valid-looking hash the AEAT will reject, so the test suite checks the function against the AEAT's own published examples, a first record, a chained one and a cancellation:

test("chained registration", () => {
  expect(
    registrationHash({
      ...first,
      invoiceNumber: "12345679/G34",
      previousHash: "3C464DAF61ACB827C65FDA19F352A4E3BDC2C640E9E9FC4CC058073F38F12F60",
      generatedAt: "2024-01-01T19:20:35+01:00",
    }),
  ).toBe("F7B94CFD8924EDFF273501B01EE5153E4CE8F259766F88CF6ACB8935802A2B97");
});

(The first two fingerprints in the diagram are the AEAT's own examples, shortened.) The records table gets the same treatment as the invoices: a trigger refuses any DELETE, and any UPDATE to what a record says, its place in the chain or what it amends. The only columns that can change are the AEAT's answer: status, attempts, receipt, error and the submission it went out in. A record the AEAT rejected is never edited; a correction is a new record at the end of the chain that points at the one it amends.

The same pattern now covers Sumalos's own audit log, which has nothing to do with the tax office: each row's hash is SHA-256 of the previous hash and the row's fields, computed by a trigger, and audit_chain_verify walks a chain and returns the first position that doesn't hold.

I should be exact about where VERI*FACTU stands in Sumalos, because "the AEAT can check it" is a strong claim. It's built and switched off: VERIFACTU_ENABLED defaults to false, and with it off, invoices are issued exactly as above but with no records, no QR code and nothing sent. Locally the records go to a simulator that validates every submission against the AEAT's official XSD schemas. On 19 October 2026 I sent nine records of the newer kinds (exports, intra-community supplies, reverse charge, rectifications by substitution and by differences) to the AEAT's test environment, and all nine came back accepted with no errors or warnings. Production needs a few more steps, and the dates when it becomes compulsory have moved more than once: the AEAT's page currently carries a note from 5 October 2026 about another planned extension.

Where the guard is thin

Triggers stop every connection that plays by the rules, which includes all of the app. They don't stop the owner of the table. I know that because one of my own migrations does it on purpose: adding a seq column to existing VERIFACTU records needed a backfill, and the migration wraps it in DISABLE TRIGGER and ENABLE TRIGGER. That's legitimate there (no records existed in production while VERIFACTU was off), and it's also proof that the guard is a rule the app can't break, not a law of physics. That is exactly the gap the hash chain covers: the trigger makes tampering hard, and the chain, once records leave for the AEAT, makes it visible.

The second thin spot is how the frozen columns are listed. The guard names the columns that can't change, which means a column added later is mutable until someone adds it to the list. invoices_guard has been rewritten four times since its first version. Three of those rewrites added columns that arrived later (the template, then the accent colour, then the exchange rate, language, customer reference and pass-through expenses together), one let a contact merge through, and the rectification type got a trigger of its own. Each new column was caught in review, which is not the same as being impossible to miss. The inverse design, listing the few columns that may change and freezing everything else, would fail safe instead. It's the change I'd make if I started again.

The third is the closed-period lock. Issuing or voiding into a period the accountant has closed is refused by assertPeriodOpen in the app, inside the same transaction, but not by a trigger. Today that's fine because both functions go through it. It's the same kind of rule as the others, though, and it belongs in the database for the same reason.

What the database is for here

None of this replaced the checks in the app. The issue function still validates every line, refuses a simplified invoice over 3,000 euros, checks the customer's tax ID and explains each refusal in plain Spanish. Those checks are what people see. The triggers, the locked counter and the unique indexes are what the law sees: a short list of things that are true about every invoice in the table, whichever path wrote it, including the ones I haven't written yet.

That's the trade. A few dozen lines of PL/pgSQL, a lock that makes issuing wait its turn, and a guard I have to remember to extend. In return, "can an issued invoice change?" has an answer I can show an inspector that doesn't depend on reading every line of the application.