If you have ever added "track paid sick leave" to a backlog, you have probably written down the rule as one hour accrued for every thirty hours worked and moved on. That constant is wrong often enough to matter, and the way it is wrong is instructive if you build anything that has to encode law.
The rule surface is wider than one constant
Seventeen states and the District of Columbia mandate paid sick-leave accrual, and they do not agree on the denominator:
- 1:30 — the majority, including California, New York, Colorado, New Jersey
- 1:35 — Rhode Island
- 1:37 — DC, and it varies by employer size
- 1:40 — Washington
- 1:52 — Vermont
Caps differ too. Minnesota and Washington set no annual cap at all, while California limits use to 40 hours a year and accrual to 80, which are two separate numbers people routinely collapse into one.
So the first modelling mistake is storing the denominator as a constant in the accrual service. It belongs in data.
The second mistake: no effective date
Legislatures change these numbers, and they change them on a date. If your rules table is a flat lookup keyed on state, then the day a change ships you have silently rewritten history: last year's balances recompute under this year's law, and the numbers your customers reconciled against payroll no longer reproduce.
The fix is boring and worth doing on day one — key rules on (jurisdiction, effective_from) and resolve them per ledger entry, not per employee:
create table sick_leave_rule (
jurisdiction text not null,
effective_from date not null,
hours_worked_per_hour_accrued numeric not null, -- 30, 35, 37, 40, 52
accrual_cap_hours numeric, -- null means uncapped
annual_use_cap_hours numeric,
carryover_allowed boolean not null,
citation text not null,
primary key (jurisdiction, effective_from)
);
The citation column looks like decoration until the first support ticket that says "our lawyer disagrees with your number." Then it is the whole product.
Balances are a fold, not a column
The related design decision: do not store a balance. Store ledger entries — opening balance, each accrual, each use, each adjustment, each carryover expiry — and compute the balance by summing them.
It costs a little at read time and it buys two things you cannot get otherwise. You can answer "why is my balance 12.5 days?" with arithmetic rather than an apology. And a rule change becomes a recomputation you can diff, instead of a migration that overwrites the evidence.
Proration is where the off-by-one lives
A mid-year hire is the classic failure. Someone joins on 15 June on a 20-day annual entitlement. The naive answer is 20 ÷ 2 = 10. The correct answer depends on what you are prorating against: 200 remaining days of the year gives 20 × 200 ÷ 365 = 10.96. Whether you then round, and in which direction, is a policy choice you should expose rather than bury — rounding down by default quietly shorts every employee a fraction of a day, every year.
What I would put in the test suite
- Hire dates on 1 Jan, 29 Feb of a leap year, and 31 Dec
- An employee who moves between two jurisdictions mid-year
- A rule whose effective date falls mid pay period
- Semi-monthly pay periods, which are not two-week periods and break any code that assumes a fixed period length
- Termination mid-accrual-period, with and without payout
None of this is hard. It is just detailed, and detail is exactly what gets skipped when leave tracking is a side feature of a payroll product rather than the product itself.
I work on PTODesk, a flat-priced PTO and leave tracker for small teams, and this rules engine is the part I have rewritten most. If you are building something similar, the two decisions I would make earlier next time are: rules in data with effective dates, and balances as a fold over a ledger. Everything else was recoverable.
Top comments (0)