payroll/docs/data-model.md

Payroll data model

The one entity that matters

Everything hangs off a contract: a standing instruction to pay one employee a fixed amount, at a fixed cadence, starting on a fixed date, a fixed number of times.

contract
  ├─ who    employee_id  →  employee_wallet   (where the money lands)
  ├─ from   source_wallet                     (where the money leaves)
  ├─ how much  amount + currency              (the agreed instruction)
  └─ when   start_date + frequency + total_periods

There is no separate "employee" table. An employee is an LNbits account; duplicating account rows here would immediately drift from core. The only thing copied out of core is employee_username, and that is display-only — a label so a payroll history still reads sensibly after an account is renamed or deleted. Authorisation and lookup always go through employee_id.

Schedule position

A contract stores periods_done, not a next_run_at timestamp.

Every payday is computed as occurrence(start_date, frequency, n) for period index n, so the n-th payday depends only on the start date. Two consequences worth the trade:

• No drift. Incrementally advancing a stored date accumulates error — one late tick and every subsequent payday shifts. Recomputing from the anchor cannot drift.
• Month-end behaves. A contract anchored on 31 Jan pays 28 Feb, then 31 Mar — because March is computed from January, not from February. Advancing month-by-month would have pinned it to the 28th forever.

periods_done counts periods consumed, which is paid or deliberately skipped — not periods successfully paid. A failed payout leaves it alone, which is exactly what makes the next tick retry the same payday instead of quietly dropping it. "How many actually paid" is a question for the payout ledger, not for this counter.

Money: instruction vs. fact

	lives on	means
amount + currency	contract	the agreed instruction — "€800 a month"
the sat figure paid	payout record	the settled fact for one period

A fiat contract is converted to sats once per period, at payout time, by LNbits' own invoice pricing. Whatever comes back is recorded as-is and is canonical for that period. Nothing downstream recomputes it from amount × rate: FX moves between quote and settlement, and rounding accumulates over a year of paydays.

Dates are civil dates

start_date is a YYYY-MM-DD string, not a DB date or timestamp. A payday is a calendar fact ("the 31st"), not an instant. Storing it as a timestamp invites a timezone normalisation to move a payday across a month boundary, which for a monthly contract silently changes which month gets paid.

Lifecycle

        ┌──────────┐
        │  active  │──────── periods exhausted ───────▶ completed
        └────┬─────┘
             │  ▲
       pause │  │ resume
             ▼  │
        ┌──────────┐
        │  paused  │
        └──────────┘
             │
             └──────────── cancel ─────────────────────▶ cancelled

completed and cancelled are terminal. paused keeps its schedule position: resuming does not backfill the paydays that passed while paused, because a pause is a decision not to pay them.

Tables

payroll.contracts in ext_payroll (SQLite) or the payroll Postgres schema. Indexed on status (the scheduler's hot path: "every contract still eligible to pay") and on employee_id (the employee-facing view).