An append-only tip ledger

Before tips were part of Bookatu, a client who wanted to tip their stylist did it in cash or on the card terminal, and nothing in the books said a tip had been taken, how much, or who it went to. A business split its tips from memory, and nobody could check the split afterwards.

When we added tips at checkout, the money part was the easy bit. The harder part was the record: who got what, under which rule, who changed it later and why, and whether last month's payroll still adds up after someone fixes a mistake today. This post walks through the design we ended up with.

Where the tip lives

The first decision was that a tip is not part of the sale. It sits in its own column on the sale row, next to the total.

    /** The tip taken with the sale (drizzle/0106), beside `totalCents` and
     *  never inside it: the client paid total + tip. Not taxed. Who got it is
     *  in tip_allocations. 0 when there was none. */
    tipCents: integer("tip_cents").notNull().default(0),

Putting the tip inside the total would have been one less column, and it would have changed the meaning of a number that the receipt, the booking's payment on the ledger, the tax report and the commission engine all read. Keeping it beside the total means none of them needed to learn anything. One integration test checks out two identical bookings, one with a $7.65 tip and one without, at a business that adds 13% HST at checkout. The two sale rows are identical apart from tip_cents, tax included.

Tips are off until the business switches on "Accept tips at checkout", because a tip changes what a client pays. The prompt offers preset percentages, 10%, 15% and 20% by default, stored in hundredths of a percent, at most four of them and none above 50%. A preset is a percentage of the booking's price after its discount and before any tax added at the till, rounded to the cent with half a cent going up. A tip above $10,000 is refused. As the code puts it, past that it is a slip of the finger.

Splitting a tip to the cent

A sale can involve several people. One person did the colour, another did the blow-dry, and a third sold the client a bottle of shampoo. The business chooses how a tip is split when nobody types the amounts in:

• service_share, the default: by each person's share of the services, so a booking one person did sends them the whole tip;
• equal: equally between everyone on the sale;
• line_share: by each person's share of the whole sale, products included.

Each rule turns the sale's lines into a weight per person. Under service_share, product lines are skipped. If every weight comes out at zero, for example on a sale of free lines, everyone on it gets an equal weight instead. The weights then go through one function.

export function splitTip(tipCents: number, weights: Array<{ staffId: string; weight: number }>): TipShare[] {
  const tip = Math.round(tipCents);
  const total = weights.reduce((s, w) => s + Math.max(0, Math.round(w.weight)), 0);
  if (tip <= 0 || total <= 0) return [];
  const T = BigInt(total);
  const parts = weights.map((w, i) => {
    const exact = BigInt(tip) * BigInt(Math.max(0, Math.round(w.weight)));
    return { i, staffId: w.staffId, weight: w.weight, cents: Number(exact / T), rem: exact % T };
  });
  let left = tip - parts.reduce((s, p) => s + p.cents, 0);
  const byLoss = [...parts].sort((a, b) => (a.rem !== b.rem ? (a.rem > b.rem ? -1 : 1) : b.weight - a.weight || a.i - b.i));
  for (const p of byLoss) {
    if (left <= 0) break;
    p.cents += 1;
    left -= 1;
  }
  return parts.filter((p) => p.cents > 0).map((p) => ({ staffId: p.staffId, amountCents: p.cents }));
}

This is the largest remainder method. Each share is worked out exactly, as whole cents times whole weights, and rounded down. Fewer cents are left over than there are people, and they go one at a time to the shares that lost the most in rounding. Ties go to the larger share, and then to whoever comes first on the sale. A $7.65 tip split equally between two people is $3.83 and $3.82. A $10.00 tip by share of the sale, where the exact shares are $6.7105 and $3.2895, rounds down to $6.71 and $3.28, and the spare cent goes to the second share because it lost more.

A seeded property test runs two thousand random splits of up to five people and checks two things on each one: the shares add up to the tip exactly, and no share is a cent or more away from its exact value.

Amounts typed at the till for one sale skip the rule entirely. They must be whole cents, nobody gets a negative amount, every person must be on the business's team, and the amounts must add up to the tip exactly. Anything else is refused rather than rounded into shape.

Taken with the sale, or not at all

The checkout reads and checks the posted tip before it writes anything. Amounts typed at the till are checked inside the checkout's transaction, against the team and the people on the booking. A rule-based split can only be worked out once the sale's lines are known, so it happens inside the transaction too. If the rule finds nobody to give the tip to, such as a booking with no staff member on it, the checkout throws and the whole sale rolls back. The till then asks who the tip is for.

      if (tip > 0 && !shares) {
        shares = allocateTip(tip, tipRule, [
          ...draft.lines.flatMap((l) => lineShares(l).map((s) => ({ kind: l.kind, staffId: s.staffId, totalCents: s.totalCents }))),
          ...sold.soldLines.map((l) => ({ kind: "product" as const, staffId: seller?.id ?? null, totalCents: l.totalCents })),
        ]);
        if (shares.length === 0) throw new TipRefused();
      }
      // ...
      // Who got the tip, with the sale: the record (lib/tip-allocations.ts).
      if (tip > 0 && shares) {
        await writeTipAllocations(tx, { orgId: org.id, saleId: sale.id, shares, rule: entered ? "manual" : tipRule, by: input.createdBy ?? null });
      }

Writing the allocation rows in the same transaction as the sale means there is never a sale with a tip on it and no record of who got the tip.

A table that refuses to be edited

tip_allocations has one row per person per tip. Each row stores the person and their name at the time, the amount, the rule, the login that took it and that login's name at the time, and a timestamp. Names are copied in on purpose. If someone is renamed or removed later, the record still says who it was.

The value rules live in the migration, because the raw SQL is the authority on the table's shape.

  -- A tip given is a positive amount; an adjustment moves money either way,
  -- and a row of nothing says nothing.
  constraint tip_allocations_amount check (amount_cents <> 0 and (rule = 'adjustment' or amount_cents > 0)),
  -- An adjustment's rows share its id and say why; a split at checkout has neither.
  constraint tip_allocations_adjustment check (
    ((rule = 'adjustment') = (adjustment_id is not null))
    and (rule <> 'adjustment' or length(btrim(coalesce(reason, ''))) > 0)
  )

Append-only is enforced by a trigger, not by convention. The obvious trigger, one that raises an exception on any update or delete, would have broken two things we needed to keep working. A row's staff_id and allocated_by are foreign keys with ON DELETE SET NULL, and Postgres carries out that action as an UPDATE on this table. A row's sale and business are foreign keys with ON DELETE CASCADE, which is a DELETE. A blanket refusal would have stopped anyone from deleting a staff member or a login who had ever received or taken a tip, and blocked a business's data from ever being removed.

The answer was pg_trigger_depth(). A foreign key's own action runs inside the system's referential trigger, so our trigger fires at depth 2 or more. A statement sent to the table directly fires at depth 1.

create or replace function bookatu.tip_allocations_append_only() returns trigger
language plpgsql as $$
begin
  if tg_op = 'DELETE' then
    if pg_trigger_depth() < 2 then
      raise exception 'tip allocations are a record: they are never deleted (record an adjustment instead)';
    end if;
    return old;
  end if;
  -- ON DELETE SET NULL letting go of a deleted person or login; nothing else.
  if pg_trigger_depth() >= 2
     and (new.staff_id is null or new.staff_id is not distinct from old.staff_id)
     and (new.allocated_by is null or new.allocated_by is not distinct from old.allocated_by)
     and (new.id, new.org_id, new.sale_id, new.staff_name, new.amount_cents, new.rule, new.adjustment_id, new.reason, new.allocated_by_name, new.created_at)
         is not distinct from
         (old.id, old.org_id, old.sale_id, old.staff_name, old.amount_cents, old.rule, old.adjustment_id, old.reason, old.allocated_by_name, old.created_at) then
    return new;
  end if;
  raise exception 'tip allocations are a record: they are never changed (record an adjustment instead)';
end
$$;

Even at depth 2, the only update allowed is one that clears a link and leaves every other column exactly as it was. The link goes and the name stays. An integration test takes a real tip at checkout and then tries three raw statements against the table: changing the amount, moving the row to another person, and deleting it. All three come back with the database's own refusal, and the original row is still there.

Adjustments are new rows

Tip splits do get changed. The till said one person did the blow-dry and it was actually someone else. The owner opens the sale's tip page, types the amounts it should have been, gives a reason, and saves. The code that handles this works out the difference and writes only that.

    const next = new Map(check.shares.map((s) => [s.staffId, s.amountCents]));
    const moves: TipShare[] = [];
    for (const staffId of new Set([...held.keys(), ...next.keys()])) {
      const delta = (next.get(staffId) ?? 0) - (held.get(staffId) ?? 0);
      if (delta !== 0) moves.push({ staffId, amountCents: delta });
    }
    if (moves.length === 0) return { ok: false as const, error: "unchanged" as const };
    const adjustmentId = `tadj_${nanoid(16)}`;
    await writeTipAllocations(tx, { orgId: org.id, saleId: sale.id, shares: moves, rule: "adjustment", by: input.by, adjustmentId, reason });

Here held is each person's current share, the sum of all their rows so far. The new amounts must be money, must go only to people who are or have been on the business's team (a correction can be for someone who has since left), and must add up to the tip. A reason is required, and something has to actually change. The sale row is locked with SELECT ... FOR UPDATE while this runs, so two people adjusting the same tip at once are queued instead of both applying their change to the same starting split.

The test for it is the best description of the result. In the test, Sam got the whole $7.65 at checkout. An admin then moves $2.65 to Anna with the reason "Anna did the blow-dry". The table ends up with three rows: Sam's original 765 cents under service_share, untouched, then Anna plus 265 and Sam minus 265, both marked adjustment, sharing one adjustment id, with the reason and the admin's login on them. The sale's tip_cents is still 765. Only who has it changed.

Every row is dated when it was written, and a person's tips for a period are the sum of their rows dated in that period. So an adjustment counts in the period when it was made, not the period of the original sale. If last month's payroll has already gone out, its figures stay as they were, and the correction appears in this month as a plus for one person and a minus for the other. Staff earnings already handles refunds this way, and we wanted the two to behave the same.

How commission and payroll read it

The commission engine builds a list of earning lines from visits, sale lines, refunds and voids. Tips join that list as lines of their own, with a sales figure of zero, a commission of zero, and the amount in a separate tip field. The totals function handles them before anything else.

function addLine(t: EarningsTotals, l: EarningLine): void {
  if (l.source === "tip") {
    t.tipsCents += l.tipCents;
    return;
  }

A tip line carries no sales and a commission fixed at zero, and the totals function returns before it reaches the commission sums, so no commission rule, tiered or flat, can pay commission on a tip. A test checks that a stylist's 40% commission on a $51.00 visit is the same whatever the client tipped. An adjustment row comes through as a line labelled "Tip adjusted", negative for the person who gave money up, so a period that contains only an adjustment still shows who gained and who lost.

The payroll export works out each employee's total as commission plus goal bonus plus tips, minus any booth rent owed. Tips get their own column in the generic CSV, their own Tips column in the Wagepoint file, and their own earnings code (the business's own, typed into the download form) in the ADP paydata file. A payroll provider can then treat tips differently from commission.

Sales tax never touches a tip, because the tip is outside the total that tax is worked on. When a day is posted to QuickBooks, the till's tips are part of the money taken and are credited to a "Tips for the team" account with no tax code. The account picker describes it as a liability, because the business owes that money to its staff.

A tip is never taxed, never commissioned, and never part of the sale's total.

The audit trail

The table is itself most of the audit trail: who got each share, under which rule, who recorded it, and when. A sale's tip page shows the split made at checkout and then each adjustment, oldest first, with its reason and who made it. A CSV download gives every row in a date range with its date, time, receipt, booking, person, amount, rule, whether it was a checkout or an adjustment, reason, who recorded it, and how the sale was paid.

On top of that, the business's activity log gets two events. Taking a tip records "Tip taken" with the amount and the split made at checkout. An adjustment records "Tip split changed" with each person's plus or minus, read back from that adjustment's own rows. The free-text reason stays on the tip record and is deliberately not copied into the activity log, because a reason can say anything, about anyone. Changing a split requires an owner or admin role with both the Manage and the revenue permission, under the business's own role settings.

What we would do again

• Keep new money beside existing totals, not inside them. Every reader of the old number stayed correct without being touched.
• Make the database enforce append-only, and remember that foreign key actions are writes too. pg_trigger_depth() let us refuse application writes while still allowing SET NULL and CASCADE.
• Model a correction as rows that add up to zero. The tip stays what was taken, the history stays readable, and nothing already paid gets restated.