Skip to main content

18 — Compliance is data: cohorts, effective dates, and a validator that names names

Read this first: the last lesson showed you a calculator with no dependencies. This one shows where its numbers come from — and the answer is rows, not Java. Statutory contribution and tax tables live in the database, versioned by effective date, and the interesting code is not the arithmetic but the machinery deciding which version applies to a given period, and what happens when a table has a hole in it. As in lesson 16, you read comments, javadoc, and signatures only. Every rate and bracket boundary lives in the business-rules reference; this lesson states none of them on purpose, so that a number changing next January never makes it wrong.

Time: about 30 minutes. Assumes lesson 17.

The rules are rows, not code​

There is no if (salary > X) return Y; anywhere in the contribution path. There are four entities, and they are all the same shape:

SssContributionRate         salary bracket from/to   + effective_date
PhilhealthContributionRate  salary bracket from/to   + effective_date
PagibigContributionRate     salary bracket from/to   + effective_date
WithholdingTaxBracket       taxable income from/to   + effective_date + pay_period

A range, an effective date, and the amounts that apply inside it. What each one contains is agency policy and belongs to the business-rules reference; this lesson is about the two extra columns, because those are the entire versioning design.

pay_period exists because BIR publishes a separate prescribed table per pay period. The TaxPayPeriod enum spells that out: daily, weekly, semi-monthly, monthly, plus the annual schedule used for the year-end adjustment, with every supported pay frequency mapping 1:1 onto its own table. DAILY rows are seeded as reference data but unreachable, since no daily pay frequency exists. Picking the wrong table is not a rounding error — taxing a semi-monthly payslip against the monthly table systematically under-withholds, which is the bug migration V63 fixed.

A table is a stack of dated versions​

When the agencies publish new rates, nothing is overwritten. A new cohort — a set of rows sharing one effective_date — is inserted alongside the old one, and lookups choose between them. The convention is stated once per repository, in the same words. From SssContributionRateRepository:

    // Rate tables are versioned by effective_date: the cohort in force on a given
    // date is the one with the greatest effective_date not after it. Ordered and
    // wrapped in a default method so an accidental overlapping-bracket edit picks a
    // deterministic match instead of Spring Data throwing on a non-unique result.

Two decisions in four lines. The first sentence is the versioning rule: greatest effective date not after the as-of date. Not "the newest cohort" — that one may not have taken effect yet when the period you are computing ended. The subquery expressing this is why these lookups are hand-written @Query methods rather than derived query names (lesson 08).

The second sentence is defensive. The query returns a List ordered by bracket floor, and a default method on the interface takes the first element. If it returned a single entity instead, one overlapping-bracket edit anywhere in the table would make Spring Data throw a non-unique-result exception on some salaries and not others — a failure that depends on who is being paid. Ordering plus first-match turns that into a deterministic, if slightly wrong, answer, while the validator later in this lesson stops the overlap being saved at all.

A second query sits underneath, with a one-line comment that is easy to skim past:

    // Deterministic fallback when the as-of date predates every cohort.

If you back-date a run before the earliest seeded cohort, the primary lookup finds nothing. Rather than fail, the fallback resolves against the earliest cohort — the oldest rates the system knows. Deterministic, documented, and clearly labelled as a fallback rather than a rule.

Two boundary conventions, and why they differ​

Contribution lookups match with SQL between, which is inclusive at both ends. Tax brackets do not, and WithholdingTaxBracketRepository explains why in the only comment in the file:

    // Lower bound exclusive, upper bound inclusive: adjacent brackets share a boundary value
    // […] so a value exactly on that boundary
    // must match exactly one bracket — the lower one. Brackets are versioned by
    // effective_date PER PAY PERIOD: the cohort in force for a table is the one with the
    // greatest effective_date not after the as-of date among rows of that pay period, so a
    // newer cohort that only updates one table never hides the others.

BIR's published tables share boundary values by design: one bracket's ceiling is literally the next bracket's floor. Match both ends inclusively and an income landing exactly on a boundary matches two brackets, and which one wins depends on row order. So the tax query is from < income <= to — strictly above the floor, at most the ceiling — and exactly one bracket can ever match.

The second half of that comment is why the cohort subquery carries pay_period as well as the date. If a new cohort revises only, say, the monthly table, a global "latest cohort" rule would make every other pay period's table vanish — their newest rows would no longer be in the newest cohort. Scoping per pay period lets each table advance on its own timeline.

Predict: a payroll period ends on the last day of the month, and a new contribution cohort's effective date falls three days before that. Two cohorts now straddle the period. Which one is used for the run — and if the new cohort's effective date were exactly the period end date, would it apply?

The new one, and yes. The as-of date is the period end, and the rule is "greatest effective date not after the as-of date", so a cohort that took effect any time on or before the period end is in force for the whole run. There is no proration inside a period: a run resolves its cohort once, from one date, and every payslip in it uses that table. And because the comparison is <=, a cohort effective exactly on the period end date does apply — the date boundary is inclusive even though the tax bracket boundary is exclusive at the bottom.

The pin, and what it cannot do​

Settings carry four optional version pins — one each for tax, SSS, PhilHealth, and Pag-IBIG. The comment on those columns is unusually careful about their status: date-based effective-date selection is authoritative, and a pin merely overrides the as-of date. One static method implements the whole feature:

    /**
     * As-of date for a rate lookup: a table-version pin that parses as a 4-digit year
     * pins the cohort to Dec 31 of that year; otherwise the payroll period end governs.
     */
    static LocalDate resolveAsOf(String versionPin, LocalDate periodEnd) {

Read what the pin is not. It is not a foreign key to a cohort, and it cannot name a cohort that does not exist. It resolves to a date, and that date goes through the same greatest-effective-date-not-after rule as any other lookup — so a pin naming a year with no cohort of its own resolves to whatever was in force at the end of it. Null, blank, or non-numeric means automatic. And resolveAsOf is package-private and static: a pure function of two inputs, unit-testable without a database, exactly the shape lesson 17 argued for.

The incident that produced a validator​

ContributionRateCoverageValidator is a final class with a private constructor and one entry point, and its javadoc opens with a war story instead of a description. A prior incident left the live PhilHealth table with a single bracket covering one narrow band of salaries; every other salary fell through the lookup, and the javadoc's phrase for what happened next is worth memorising — "silently sending every other salary through a wrong hardcoded default." Not a crash. Correct-looking payslips, wrong money, for everyone outside one band.

The class exists so no create, update, or delete on any of the four tables can leave that state behind. Its second paragraph is the design:

 * Tables are versioned by effective date, so validation runs per cohort: every row set
 * sharing one effective date must independently cover the full salary range. Deleting a
 * cohort's last row removes the whole cohort, which is allowed as long as at least one
 * cohort remains.

Per cohort, not per table, and that follows directly from the lookup rule. A run resolves exactly one cohort and never mixes rows across effective dates, so a cohort covering only part of the salary range is a hole for every run that lands on it — even if a neighbouring cohort would have covered it. The validator groups rows by effective date and checks each group independently for overlaps, for gaps between adjacent brackets, for a floor above zero, and for a ceiling below the practical maximum.

The delete rule is the pragmatic exception. Removing a cohort's last remaining row does not create a gap; it retires the cohort entirely, which is allowed as long as one cohort survives. Delete the last row of the last cohort and the validator refuses: a table with no rows covers nothing.

One implementation detail matters more than it looks. The rate services do not validate the current table; they build the ranges the table would have after the change — existing rows, minus the row being edited or deleted, plus the proposed row — and validate that. Validation runs before the write, on the hypothetical result, so an invalid edit is rejected rather than rolled back.

The gate at generation time​

Editing tables is one risk. Running payroll against a table that was already wrong is the other, and the run path has its own check. From PayrollServiceImpl:

    /**
     * Fails fast, naming every affected employee, instead of aborting mid-run on
     * whichever employee happens to iterate first when a salary falls outside every
     * seeded contribution bracket.
     */

validateContributionBracketCoverage resolves the as-of date for each of the three contribution tables through resolveAsOf, walks every active employee trying the primary lookup and the earliest-cohort fallback, and collects a line per miss. Only after the whole sweep does it throw — one exception carrying every failure:

Cannot generate payslips: <employee number> (<name>): no SSS contribution bracket
covers monthly salary <amount>; <employee number> (<name>): no PhilHealth ...

It runs before the payslip-creation loop, not inside it, and that ordering is the point of the javadoc: a check inside the loop aborts on whichever employee iterates first, so an operator fixing one bracket reruns and discovers the next victim, one at a time.

Predict: a gap opens in the PhilHealth table and three employees' salaries fall into it. The run is generated. Do those three get wrong payslips, or does nobody get a payslip?

Nobody gets a payslip. The gate runs ahead of generation inside the transactional generate method, so the throw rolls back everything and the run stays in Draft — never a partial batch where most of the company is paid and three people are missing. The message names all three by employee number and name, so the operator sees the full extent of the problem in one read. Behind that gate, the calculators keep their own orElseThrow naming the salary and the as-of date, as a backstop.

Fail loud, by name​

Every decision in this lesson leans the same way. The lookup falls back deterministically rather than returning null. The overlap wrapper picks a documented row rather than throwing at random. The validator rejects an edit before it lands. The run refuses to generate rather than generating something plausible. And the failure message names people, not row IDs — because for payroll a wrong answer is a wrong bank transfer, a wrong statutory remittance, and a correction run three weeks later. The original incident did not crash, which is precisely why it survived long enough to become a war story in a javadoc. Loud failure is cheap; silent wrongness is not.

Where this shows up in MotorPH​

Recap​

  • Statutory rules are rows, not code. Four entities share one shape — a range plus an effective date — and the values belong to the business-rules reference.
  • Cohorts resolve by greatest effective date not after the as-of date. A run picks one cohort from its period end and uses it for every payslip; tax cohorts resolve per pay period so a partial republication never hides the other tables.
  • Boundaries are decided, not assumed. Tax brackets match from < income <= to because adjacent brackets share a value; contribution lookups use inclusive between plus an ordered first-match wrapper so an accidental overlap degrades deterministically instead of throwing.
  • Coverage is validated per cohort, before the write. The rate services validate the table the change would produce; each cohort must independently cover the whole range.
  • Generation fails loud and by name. The pre-loop gate collects every uncovered employee and throws once, naming all of them, so nobody gets a wrong payslip and one pass fixes the table.

Next: 19 — Withholding and the true-up: why the final cutoff is different.