What is a billing schedule in an ERP contract?

A billing schedule is a table with one row per billable moment of a contract, each holding a due date, a label and a planned amount. Invoices are generated from those rows instead of being cloned from the previous invoice. The schedule is the plan and the invoice is the record that a row was billed.

How should I model a down payment in a billing schedule?

Model it as period 0 in the same table as the regular periods, with its own period type column. That keeps the plan complete, so the rows sum to the contract amount. Select rows by type rather than by index arithmetic, because an index that also carries meaning breaks when the convention changes.

How do I stop an invoice being generated twice for the same period?

Give each schedule row a nullable invoice link with a unique constraint, and always generate from the next row whose link is null. Then claim the row with a conditional update inside the same transaction as the invoice insert, and roll back unless exactly one row changed. The unique constraint alone does not stop two requests racing for one row.

Can a unique column in PostgreSQL be nullable?

Yes. PostgreSQL does not treat two null values as equal in a unique constraint, so any number of rows can hold null at once. The constraint only rejects two rows with the same non-null value, which is exactly what you want for an optional invoice link.

What happens if a contract changes after the first invoice is issued?

Do not delete schedule rows that already point at an invoice, because that orphans the invoice. A safe default is to refuse to regenerate once any row is linked and ask a person to resolve it. A better design cancels only the unbilled tail and generates a new one from the next period.

ERP Contract Billing Schedule Down Payment Without Double Bills

Model a contract as billing-schedule rows, period 0 as the down payment, each with a planned amount and a unique invoice link, so no period is billed twice

Key Takeaway

Model each contract's billing as schedule rows: period 0 for the down payment, periods 1 to N after it, each with a planned amount and a nullable, unique invoice link. Generate every invoice from the next unlinked row and claim that row conditionally in the same transaction. A period then cannot be billed twice, and amounts cannot drift.

The first version of recurring billing in an ERP is usually a copy button. Finance opens last month's invoice, presses generate next, and the system clones it with a new date. It works until a contract has a down payment, an uneven last period, or two people pressing the button within the same minute.

This post is about the design I used in the AP/AR module of an ERP I built, the one described in the ANCoraPRO case study on this site. I read the schema and the proposal and invoice services again before writing. Everything below is a design pattern with generic names and rewritten examples, not client code, and I mark clearly where something is a design choice rather than a measurement.

Why cloning last month's invoice stops working

A cloned invoice has no memory of the contract. It copies an amount, so the contract total is never checked against what has been billed. It copies a date pattern, so a down payment or a shortened final period needs a human to remember the exception. And nothing in the database says that period 3 has already been billed, so the only guard is whoever is looking at the screen.

The fix is to stop treating the invoice as the source of truth for the plan. The plan is the contract's schedule, created once, and invoices are produced from it. That is the step between an approved quotation and cash that the quotation-to-cash workflow post leaves implicit, and it is the data that makes the automation in the next sections possible.

The data model: one row per billable moment

In the ERP I built, a table sits between the contract (called a proposal there) and the invoice. Each row is one billable moment of one contract. The shape below is rewritten with generic names, but the three decisions in it are the real ones: a type column next to the index, a planned amount stored on the row, and a nullable invoice link that is unique.

// Illustrative schema, generic names. One row per billable moment of one contract.
model BillingScheduleRow {
  id            String   @id @default(uuid())
  contractId    String
  periodIndex   Int                 // 0 = down payment, 1..N = regular periods
  periodType    String              // "down_payment" | "period": the meaning lives HERE
  periodLabel   String              // "Down Payment", "Mar 2026"
  dueDate       DateTime
  plannedAmount Decimal  @db.Decimal(15, 2)  // fixed when the schedule is created
  invoiceId     String?  @unique    // null = not billed yet; one invoice, one row
  invoice       Invoice? @relation(fields: [invoiceId], references: [id])

  @@index([contractId])
}

periodIndex orders the rows and periodType says what a row is. Index 0 is the down payment, 1 to N are regular periods. The type is stored separately so that no code has to infer meaning from a magic number.
plannedAmount is fixed at schedule time. The invoice later copies it, so the amount on the invoice is a consequence of the plan, never a fresh calculation someone could get wrong.
invoiceId is nullable and unique. Null means not yet billed. A value means exactly one invoice owns this row, and the database refuses to let one invoice own two rows.

The unique constraint on a nullable column does less than it appears to, and that matters later. PostgreSQL does not treat two null values as equal in a unique constraint, so any number of unbilled rows can coexist. What it forbids is one invoice id appearing on two rows. Prisma expresses the same thing by marking the optional relation scalar as unique, which makes the relation one-to-one.

Period 0: the down payment is a row, not a flag

The tempting model puts a boolean on the invoice, is down payment, and leaves the schedule for the regular periods only. Then the down payment is invisible to the plan: the schedule no longer sums to the contract amount, and every report has to remember to add the special case back.

Making it row zero means the plan is complete in one table, ordered by one column. A comment in the service code I read explains why the type column exists: the link used to be chosen by a rule that tested whether the index equalled zero for a down payment and one otherwise, and it broke whenever the frontend changed its indexing convention. Choosing the target row by periodType removed the dependency on the number.

Store the meaning in a column and the order in another. An index that is both ordering and meaning breaks the first time someone inserts a milestone between periods.

Generating the schedule once, with a remainder rule

A schedule is created when the contract is approved. The generator walks the date range by the billing frequency, counts the periods, splits the amount equally, and makes the last row absorb the rounding remainder. The sketch below is my rewrite with a down payment added in front, since the service I read generates the regular periods and receives a down payment entry with the invoice request.

// Illustrative: build the whole plan once, at approval time.
async function createSchedule(contract: Contract, downPayment = 0) {
  // Idempotent: a re-approval after a rejection must not double the plan.
  const existing = await prisma.billingScheduleRow.count({
    where: { contractId: contract.id },
  });
  if (existing > 0) return;

  const periods = countPeriods(contract.start, contract.end, contract.frequency);
  const remaining = contract.amount - downPayment;
  const base = Math.floor(remaining / periods);

  const rows = [];
  if (downPayment > 0) {
    rows.push({
      contractId: contract.id, periodIndex: 0, periodType: "down_payment",
      periodLabel: "Down Payment", dueDate: contract.start, plannedAmount: downPayment,
    });
  }
  for (let i = 1; i <= periods; i++) {
    rows.push({
      contractId: contract.id, periodIndex: i, periodType: "period",
      periodLabel: labelFor(contract.start, i), dueDate: endOfPeriod(contract.start, i),
      // The last row absorbs the rounding remainder so the plan sums exactly.
      plannedAmount: i === periods ? remaining - base * (periods - 1) : base,
    });
  }
  await prisma.billingScheduleRow.createMany({ data: rows });
}

// 1000 over 3 periods: 333, 333, 334. Never 333, 333, 333 with a 1 unit hole.

Two properties matter more than the arithmetic. The generator is idempotent: it returns early if the contract already has rows, so a re-approval after a rejection cannot double the schedule. And the rows sum to the contract amount by construction, which you can assert with a single query instead of trusting that rounding behaved. Whole currency units and an equal split are my simplification here; a contract with uneven periods needs the planned amounts supplied rather than derived.

Generating an invoice from a row

The invoice step finds the next row that has no invoice and a regular period type, takes its planned amount, label and due date, creates the invoice, and links the row to it. In the code I read, the create and the link run inside one database transaction, so an invoice cannot exist with its row still unclaimed because the link failed halfway.

await prisma.$transaction(async (tx) => {
  // The next regular period nobody has billed. Down payment rows are picked by type.
  const row = await tx.billingScheduleRow.findFirst({
    where: { contractId, invoiceId: null, periodType: "period" },
    orderBy: { periodIndex: "asc" },
  });
  if (!row) throw new Error("Nothing left to bill on this contract");

  const invoice = await tx.invoice.create({
    data: {
      contractId,
      subtotal: row.plannedAmount,   // copied from the plan, never recalculated by hand
      billingPeriod: row.periodLabel,
      dueDate: row.dueDate,
    },
  });

  // Wrong: update by id. Two racing requests both find the same row and the
  // second silently overwrites the first invoice's link.
  // await tx.billingScheduleRow.update({ where: { id: row.id }, data: { invoiceId: invoice.id } });

  // Right: claim it only if it is still free, and insist the claim took effect.
  const claimed = await tx.billingScheduleRow.updateMany({
    where: { id: row.id, invoiceId: null },
    data: { invoiceId: invoice.id },
  });
  if (claimed.count !== 1) throw new Error("Period already billed"); // rolls the invoice back too
});

The transaction boundary is also what keeps the audit trail honest, which is the part of accounts receivable that automating AR in an ERP depends on: one row, one invoice, one history entry written against it.

The version I read finds the row first and then updates it by id. That is correct when one request runs at a time. Two requests in the same instant can both find the same unlinked row. Claim the row with a conditional update instead, shown in the next section.

The failure the schedule prevents

The named failure is double billing a period: two invoices for the same month of the same contract, discovered by the client rather than by finance. I did not see this in production, so I will not dress it up as a war story. It is the failure the design is built to make structurally hard, and the table maps each way it can happen to the guard that stops it.

How it happens	Clone-the-invoice approach	Schedule-row approach
Someone presses generate twice	Two invoices, only a screen check in the way	Second press finds no unlinked row and stops
Down payment exists	A flag on one invoice, plan not summing to total	Row 0 in the plan, totals always reconcile
One invoice linked to two rows	Nothing prevents it	Unique constraint on the invoice link rejects it
Two requests claim one row at once	Both succeed	Needs a conditional claim, see below

The last row is the honest edge. A unique constraint stops one invoice owning two rows, but it does not stop two different invoices racing for one row, because a plain update by id simply overwrites a null. The fix is to make the claim itself conditional and check that it took effect, as in the sketch. Under PostgreSQL's default Read Committed isolation, a second updater waits for the first and then re-evaluates the WHERE clause against the new row version, so the condition on a null invoice link fails and the update affects zero rows. Locking the row first with SELECT FOR UPDATE is the other route, at the cost of holding a lock for the length of the transaction.

Editing a contract after billing has started

The hard cases arrive after the first invoice. When a contract's dates or billing frequency change, the stale schedule has to be rebuilt, and the code I read does that only when no row has been invoiced yet. If any row is linked, it skips the rebuild, logs a warning that says to resolve manually, and tells the caller the regeneration was skipped. The reason is in a comment: deleting an invoiced row would orphan the invoice that points at it.

I consider that a good default and an incomplete answer. It protects the ledger and pushes the real work onto a person. What I would add is an explicit operation that cancels the unbilled tail of a schedule and generates a new tail from the next period, leaving every invoiced row untouched. Two further limits in the code I read: amounts pass through JavaScript numbers on their way to the table, where I would keep Decimal end to end because PostgreSQL's numeric type exists for exactness, and the schedule is only generated automatically for recurring contracts.

Monday checklist

If you are about to build recurring or staged billing, these are the steps in the order I would take them.

Add the schedule table with periodIndex, periodType, plannedAmount and a nullable unique invoice link before you build any generate button.
Model the down payment as row 0 of the same table, and select rows by type, never by index arithmetic.
Generate the schedule from the contract, idempotently, and assert in a test that planned amounts sum to the contract amount.
Create the invoice and claim its row in one transaction, with a conditional update that must affect exactly one row.
Write down what happens when a contract is edited after the first invoice, because that is where manual work hides.

The rule I carry from this: the plan belongs to the contract, the invoice belongs to a row of the plan, and the database, not the screen, should be what says a period is taken.

Sources