Designing POS Cash-Session Reconciliation in an ERP

When I built the JID Carwash ERP for an Indonesian carwash SME, the single feature that generated the most owner anxiety was not payroll or inventory. It was the cash drawer. Every shift, a cashier handles a stack of physical banknotes, and at the end of the day the owner wants one honest answer: does the cash in the drawer match what the system says it should be? Getting that answer reliably is the entire job of the cash-session module.

The temptation is to store a single running cash balance and adjust it on every transaction. I learned the hard way that this is the wrong model. A running balance hides history, cannot answer who was on shift when a discrepancy appeared, and turns a concurrency bug into lost money. Instead I modelled the cash session as an explicit, immutable-once-closed record, and I made the cash book a derived view rather than a table anyone writes to.

The real cashier workflow at the carwash

Before writing a single Prisma model I sat at the counter and watched. The carwash runs two shifts a day across a couple of branches. A shift begins when the cashier counts the drawer and records the opening float, usually around Rp 500.000 in small notes for change. Through the day cash comes in from wash orders and leaves for small expenses such as buying detergent or paying a kasbon, an employee cash advance. At shift end the cashier counts the drawer again, hands over to the next person, and the money above the float is deposited.

• Open: cashier physically counts the drawer and records the opening float for the shift.
• During: cash-in from paid cash sales, cash-out for petty expenses and kasbon disbursements, each logged as its own movement.
• Close: cashier counts the drawer again, the system shows expected cash, and the difference becomes the recorded variance.
• Handover: everything above the float is deposited and the next shift opens a fresh session.

Modelling a cash session, not a balance

The core entity is a CashSession scoped to a branch, an employee, and a status. Money is stored as integer Indonesian Rupiah throughout the codebase, never as a floating-point number, because IDR has no sub-unit in daily use and floats silently corrupt sums. The opening and counted-closing amounts live directly on the session; everything that happens in between is a stream of cash movements that reference the session.

model CashSession {
  id             String        @id @default(cuid())
  branchId       String        // every query scopes by this
  openedById     String
  closedById     String?
  status         SessionStatus @default(OPEN)
  openingFloat   Int           // integer IDR, e.g. 500000
  countedClosing Int?          // what the cashier physically counted
  expectedClosing Int?         // computed at close, snapshotted
  variance       Int?          // countedClosing - expectedClosing
  openedAt       DateTime      @default(now())
  closedAt       DateTime?

  branch    Branch         @relation(fields: [branchId], references: [id])
  movements CashMovement[]

  @@index([branchId, status])
}

enum SessionStatus { OPEN CLOSED SUSPENDED }

model CashMovement {
  id         String       @id @default(cuid())
  sessionId  String
  branchId   String
  type       MovementType // CASH_IN | CASH_OUT
  source     String       // 'order' | 'expense' | 'kasbon'
  refId      String?      // order/expense id for traceability
  amount     Int          // always positive integer IDR
  createdAt  DateTime     @default(now())

  session CashSession @relation(fields: [sessionId], references: [id])
  @@index([sessionId])
}

enum MovementType { CASH_IN CASH_OUT }

The cash book is a derived view, never a written table

Owners wanted a running cash book, called the buku kas, showing every entry chronologically. The naive approach is a CashLedgerEntry table that you insert into on every transaction. I deliberately did not build that. Instead CashLedgerEntry is a derived union view assembled at read time from the real source tables: cash movements, settled payments, and expenses. There is no write path to the ledger, so it can never drift from the transactions it summarises.

This mirrors a rule I applied everywhere in the ERP: caches and summaries must have a single source of truth. Product stock quantity is a cache too; the truth is the sum of stock movements. If a derived number and its source disagree, the source wins and the derived value is simply recomputed. That principle removes an entire category of reconciliation bug where a summary silently lies.

Expected vs counted: the variance formula

At close, the service sums the movements for the session and computes expected cash. The formula is the industry-standard one: opening float plus total cash-in minus total cash-out. The cashier enters the physically counted amount, and variance is counted minus expected. A positive variance is an overage, a negative one is a shortage, matching the accounting concept of cash over and short.

// cash-session.service.ts (close flow, simplified)
async closeSession(sessionId: string, countedClosing: number, actor: Actor) {
  const session = await this.prisma.cashSession.findFirstOrThrow({
    where: { id: sessionId, branchId: actor.branchId, status: 'OPEN' },
    include: { movements: true },
  });

  const cashIn  = session.movements
    .filter(m => m.type === 'CASH_IN')
    .reduce((s, m) => s + m.amount, 0);
  const cashOut = session.movements
    .filter(m => m.type === 'CASH_OUT')
    .reduce((s, m) => s + m.amount, 0);

  const expectedClosing = session.openingFloat + cashIn - cashOut;
  const variance = countedClosing - expectedClosing; // + over, - short

  return this.prisma.cashSession.update({
    where: { id: session.id },
    data: {
      status: 'CLOSED',
      closedById: actor.userId,
      countedClosing,
      expectedClosing,   // snapshot, so later edits can't rewrite history
      variance,
      closedAt: new Date(),
    },
  });
}

Note that expected closing is snapshotted onto the row at close time. I do not recompute it later from live movements, because a backdated correction to some order should never silently change what a shift was reconciled against months ago. The snapshot freezes the reconciliation as it stood when the human counted the drawer.

Variance handling and who can close

Not every variance is fraud, and the system must not treat it as such. The carwash target is well under one percent of cash sales; industry guidance puts a well-run drawer under 0.1 percent. I built a two-tier policy: small variances within a configurable threshold close automatically with a logged note, while anything beyond it requires a manager to review and approve the close. A single small shortage is documented without accusation; the module surfaces patterns across shifts, because a repeated small shortage is a stronger signal than one large one.

The audit trail that makes it defensible

Every open, movement, and close writes to an append-only AuditLog. Rows are never updated or deleted, personally identifiable information is redacted on write, and each entry records who did what, when, and against which session and branch. When the owner asks why branch B was short Rp 40.000 on a Tuesday, the answer is a query, not a guess. The audit log is what turns a pile of cash into an accountable process the owner actually trusts.

The payoff of this design is that reconciliation stopped being an argument. The numbers are derived from immutable movements, the variance is frozen at close, permissions decide who can sign off, and the audit log remembers everything. That is the difference between software that tracks cash and software an owner is willing to bet the business on.