ERP Expense Settlement Advance Reconciliation and Input VAT

A cash advance is the one AP document that is not finished when it is paid. Money has left the company, but nobody has said what it bought. Until the employee comes back with receipts, the advance is an open liability on the books, and the ERP has to know that.

This post covers the settlement half of that document in an ERP I built: the data model, the single invariant that keeps the header honest, the approval routing for overspend, and how input VAT is carried on each expense line without ever touching the reconciliation. It is design and data model only. There are no tax rates or amounts here, because the design does not need them.

Settlement is a reconciliation, not a second form

The tempting model is a single request with an amount, and a settlement screen that overwrites it with the real figure. That loses the one thing finance needs, which is the comparison between what was advanced and what was actually spent. I model the advance and its settlement on the same record, but they are two different numbers with two different lifecycles.

The advance amount is fixed at approval and payment. The settled amount is claimed later, backed by expense lines, and reviewed. The gap between them is the whole point: a positive gap is a surplus the employee returns, a negative gap is an overspend the company reimburses. Both need a reviewer, and the second needs a reason.

The data model: one header, many category lines

The schema below is a generic rewrite of the pattern, not the production source. The advance header carries the fixed amount, the claimed settled amount, a reconciliation flag and the optional overspend reason. Each expense line carries its own gross amount, a category, a date, and two tax-related fields that are both nullable.

enum AdvanceStatus {
  draft pending_approval approved paid
  pending_settlement_manager  // overspend: manager reviews first
  pending_settlement          // finance reviews
  settled rejected
}
enum Reconciliation { pending partial settled }

model Advance {
  id              String           @id @default(uuid())
  number          String           @unique
  amount          Decimal          @db.Decimal(15, 2)  // fixed at payment, never overwritten
  status          AdvanceStatus    @default(draft)
  settleBy        DateTime?
  reconciliation  Reconciliation?
  settledAmount   Decimal?         @db.Decimal(15, 2)  // claimed at submit, gross
  overspendReason String?
  returnReference String?          // surplus transfer reference, a string not a ledger entry
  inputVatTotal   Decimal?         @db.Decimal(15, 2)  // informational roll-up of the lines
  expenses        ExpenseLine[]
}

model ExpenseLine {
  id           String   @id @default(uuid())
  advanceId    String
  advance      Advance  @relation(fields: [advanceId], references: [id], onDelete: Cascade)
  lineNumber   Int
  description  String
  amount       Decimal  @db.Decimal(15, 2)  // GROSS, as printed on the receipt
  category     String                       // transport, lodging, meals, supplies, other
  spentOn      DateTime
  inputVat     Decimal? @db.Decimal(15, 2)  // informational: part of amount, not added to it
  taxInvoiceNo String?                      // without it the VAT is not creditable
  @@index([advanceId])
}

Two choices matter here. Money is a fixed-scale decimal column, never a float, because PostgreSQL documents numeric as the type recommended for monetary amounts. And the lines are children of the header with a cascade delete, so a resubmitted settlement replaces its lines wholesale instead of editing them one by one.

The one invariant: the lines are the settled amount

The settled amount on the header and the sum of the expense lines must agree before the settlement can be submitted. A header total that merely sits next to its lines is a number someone typed; a header total that is checked against its lines is a reconciliation. The service rejects a submission when the two differ by more than a tolerance of one currency unit, which absorbs rounding but nothing else. Per-line rules such as a positive amount are declared on a DTO and checked by the NestJS ValidationPipe, so the service only has to enforce the rules that span lines.

async function submitSettlement(advanceId: string, userId: string, dto: SettleDto) {
  const advance = await db.advance.findUnique({ where: { id: advanceId } });
  if (!advance) throw new NotFoundException();
  if (advance.ownerId !== userId) throw new ForbiddenException("Settle your own advance");
  if (advance.status !== "paid") throw new BadRequestException("Pay first, then settle");

  // The invariant: header total must equal the sum of its lines.
  // A CHECK constraint cannot see sibling rows, so this lives here.
  const lineSum = dto.expenses.reduce((acc, e) => acc + Number(e.amount), 0);
  if (Math.abs(lineSum - dto.settledAmount) > ONE_CURRENCY_UNIT) {
    throw new BadRequestException("Lines do not add up to the settled amount");
  }

  const overspend = dto.settledAmount > Number(advance.amount);
  if (overspend && !dto.overspendReason?.trim()) {
    throw new BadRequestException("Say why the advance was exceeded");
  }

  // Informational only. Nothing above reads this value.
  const inputVatTotal = dto.expenses.reduce((acc, e) => acc + (Number(e.inputVat) || 0), 0);

  await db.advance.update({
    where: { id: advanceId },
    data: {
      status: overspend ? "pending_settlement_manager" : "pending_settlement",
      reconciliation: dto.settledAmount >= Number(advance.amount) ? "settled" : "partial",
      settledAmount: dto.settledAmount,
      overspendReason: dto.overspendReason ?? null,
      inputVatTotal: inputVatTotal > 0 ? inputVatTotal : null,
      // One nested write = one transaction: old lines out, new lines in, or neither.
      expenses: { deleteMany: {}, create: dto.expenses.map(toLine) },
    },
  });
}

This cannot be a database constraint. PostgreSQL documents that CHECK constraints cannot reference table data other than the row being written, and a header-versus-children sum is exactly that. So the rule lives in the service, in the same method that decides the next status, and the comment in my code names the risk it closes: without it, a submitter could claim one figure on the header while the lines add up to another, and finance would approve the header with a broken audit trail.

I should be exact about the evidence. That guard came from reasoning about the failure, not from an incident I can report. The reasoning is simple enough to trust, but it is a design decision, not a measurement.

Overspend, underspend and who approves

The comparison between the settled and advanced amounts decides the route. An advance may only be settled once it is in the paid state. Settled above the advance means overspend: a reason is mandatory and the settlement goes to the department manager first. Anything else goes straight to finance. In both cases a rejection sends the advance back to paid with the rejection reason stored, so the employee can resubmit.

The reconciliation flag has three values: pending, partial and settled. In the code it is set at submission, and partial means the settled amount is below the advance, so a surplus is owed back. The service also accepts an optional reference for the surplus return transfer. It stores that reference; it does not model the return as a ledger entry, which I come back to in the limits.

Input VAT: informational on every line, creditable only with an invoice number

Each expense line can carry an input VAT portion and a tax-invoice number. I treat these as two separate facts about the same line. The VAT amount is informational: it says how much of the gross was VAT, for the input-VAT recap. It does not change the line amount, the settled amount or the reconciliation. The invoice number is what decides whether that VAT counts as creditable. No number, no credit.

The header gets a roll-up of the line VAT in the same transaction as the settlement, and the code is explicit that the roll-up never feeds the checks above it. The nested write that replaces the lines runs as one transaction in Prisma, so a failed resubmission cannot leave a header pointing at half of an old set of lines.

// Wrong: settle on net-of-VAT lines. The total now disagrees with the receipts
// and with the cash the employee actually spent.
const settled = lines.reduce((acc, l) => acc + (l.amount - (l.inputVat ?? 0)), 0);

// Right: settle on gross. VAT is a label on a line that is already inside that gross.
const settled = lines.reduce((acc, l) => acc + l.amount, 0);

// Right: the creditable total is a read-time question with two conditions.
const creditable = lines.filter((l) => l.inputVat && l.taxInvoiceNo);
const informationalOnly = lines.filter((l) => l.inputVat && !l.taxInvoiceNo);

The schema comment states the creditable rule, but the header roll-up in my service adds every line, with or without an invoice number. So the header figure is the informational total, and the creditable total has to be computed at read time by requiring the number. I would name the header column accordingly.

Where each fact lives

Most of the design is deciding which table owns which fact. This is the split I settled on, and the reason each fact sits where it does.

Notice that nothing here needs a tax rate. The design only needs to know whether a VAT portion was recorded and whether an invoice number backs it. Which rate applies, and what is creditable under current rules, is a tax-compliance question that belongs in a dated, sourced reference rather than in a schema.

What I would do differently, and the limits

Reading my own service back, these are the places I would tighten, listed as limits rather than hidden.

• The line sum is computed with JavaScript numbers and a tolerance, while the columns are decimals. Summing the lines with a decimal type and requiring an exact match would remove the tolerance.
• The category is a free string with the vocabulary in a comment. A database enum or a lookup table would stop typos.
• The reconciliation flag reads settled at submission, before finance has approved. The status column carries the real state, so the name is misleading and I would rename the flag.
• A surplus return is a stored reference string, not a ledger entry, so nothing reconciles the returned cash against the books.

None of these has been measured against production data. They are findings from reading the code, and the design choice at the centre, lines must equal the header, does not depend on fixing them.

Monday checklist

1. Store the advance amount and the settled amount in two separate columns and never overwrite the first.
2. Use a fixed-scale decimal for every money column, on the header and on the lines.
3. Reject a settlement submission when the line sum and the settled amount differ, in the service, since a CHECK constraint cannot see other rows.
4. Require a reason for overspend, and route overspend to a manager before finance.
5. Put the input VAT portion and the tax-invoice number on the line, both nullable, and keep the VAT out of every reconciliation sum.
6. Compute the creditable total at read time by requiring the invoice number, and replace lines wholesale in one nested write on resubmission.

Related reading on this site

• Accounts payable with multi-level approval in NestJS
• ERP purchase order approval
• ERP e-Faktur tax integration in Indonesia

A settlement is only trustworthy when its lines add up to its header and nothing else is allowed to move that sum. Reconcile gross against the advance, route overspend to a reviewer, and carry VAT as a flag on the line that becomes creditable only when an invoice number backs it.