Not legal advice; an engineer's account of the numbers an independent auditor asks for, and where they come from. Your counsel and your auditor have the final word on scope.
An LL144 bias audit is often described as something you buy. In practice you buy the auditor's independence and their opinion. What you supply is data: for every candidate the tool touched, whether they were selected, and which sex and race/ethnicity category they fall into. If that data does not exist in a queryable form, the audit stalls before it starts, and the summary you are required to publish has nothing behind it.
This walkthrough builds the tables and queries that produce it. The worked example is a rubric screener scoring candidates against a firm-owned rubric, the Rubric Screener shape, but the same structure applies to any tool that ranks, scores or classifies applicants. If you have not read the two laws' obligations end to end, start with AI screening under NYC LL144 and Illinois HB 3773 and come back.
Step one: keep demographics away from the scorer
The first design decision is a wall. Self-identification data is collected voluntarily, at application time, and stored in a table the screening pipeline cannot read. The scorer reads the resume and the application fields. It never reads sex, race or ethnicity, and it never reads anything joined to them.
create table candidate_self_id (
candidate_id bigint primary key,
sex text, -- 'female' | 'male' | null
race_ethnicity text, -- EEO-1 style category, or null
declined boolean not null default false,
collected_at timestamptz not null default now()
);
Nulls are not a defect here. Candidates decline, and LL144's audit rules expect the count of applicants in the unknown category to be reported rather than guessed at. Do not infer sex from first names or ethnicity from surnames or zip codes to fill the gaps. Illinois HB 3773 names zip-code proxies explicitly, and a inferred-demographics column is the single most damaging artefact an opposing lawyer could find in your database.
The join between scores and self-ID happens once a year, in the audit export, by an analyst, and nowhere else.
Step two: log one row per scored candidate
The scoring log is the audit's source of truth. One row per candidate per rubric version, with the human decision attached.
create table screening_events (
id bigserial primary key,
candidate_id bigint not null,
job_id bigint not null,
rubric_version text not null, -- 'sales-rn-v3'
scored_at timestamptz not null,
total_score numeric(5,2) not null,
recommendation text not null, -- 'advance' | 'hold' | 'no'
rationale jsonb not null, -- per-criterion evidence and reason
human_actor text, -- who made the stage change
human_decision text, -- 'advanced' | 'rejected' | 'overrode'
decided_at timestamptz,
notice_shown_at timestamptz
);
create index on screening_events (job_id, scored_at);
human_decision is the column that decides what you are measuring. LL144's rules ask for the selection rate: the rate at which candidates in a category move forward. Forward means a person advanced them, not that the model liked them. If those two numbers differ a lot, that is worth knowing on its own.
Step three: selection rates by category
Selection rate is selected divided by scored, per category, over the audit window. Start with sex:
with pool as (
select
e.candidate_id,
coalesce(s.sex, 'unknown') as category,
(e.human_decision = 'advanced') as selected
from screening_events e
left join candidate_self_id s using (candidate_id)
where e.scored_at >= date '2026-01-01'
and e.scored_at < date '2027-01-01'
)
select
category,
count(*) as scored,
count(*) filter (where selected) as selected,
round(
count(*) filter (where selected)::numeric / nullif(count(*), 0),
4
) as selection_rate
from pool
group by category
order by scored desc;
Output looks like this:
| category | scored | selected | selection_rate |
|---|---|---|---|
| male | 812 | 203 | 0.2500 |
| female | 604 | 118 | 0.1954 |
| unknown | 96 | 21 | 0.2188 |
Swap s.sex for s.race_ethnicity for the second table, and use coalesce(s.sex,'unknown') \|\| ' / ' \|\| coalesce(s.race_ethnicity,'unknown') for the intersectional one. The rules ask for sex categories, race/ethnicity categories, and the intersection of the two, so write the query once and pass the grouping expression in.
Step four: impact ratios
The impact ratio is each category's selection rate divided by the highest selection rate in the table. max() over () does it in one pass:
with rates as (
-- the query from step three, without the order by
select category, scored, selected, selection_rate from pool_rates
)
select
category,
scored,
selected,
selection_rate,
round(selection_rate / max(selection_rate) over (), 4) as impact_ratio
from rates
order by impact_ratio;
| category | scored | selected | selection_rate | impact_ratio |
|---|---|---|---|---|
| male | 812 | 203 | 0.2500 | 1.0000 |
| unknown | 96 | 21 | 0.2188 | 0.8752 |
| female | 604 | 118 | 0.1954 | 0.7816 |
An impact ratio below 0.8 is the four-fifths rule of thumb from the federal Uniform Guidelines on Employee Selection Procedures. It is a flag, not a verdict, and it is not what the law prohibits; the audit publishes the ratios, and your counsel and auditor interpret them. But 0.7816 in a nightly dashboard is a conversation you want to have in February, not in the week the auditor arrives.
Two practical notes. Small denominators produce wild ratios: a category with eleven candidates will swing on one decision, so publish the counts next to the rates and treat thin rows as noise. And if the tool scores candidates for jobs outside the city, agree the scope of the pool with your auditor before you compute anything, rather than after.
Step five: run it monthly, not annually
The audit is annual. The query is cheap. Schedule it monthly against a rolling twelve months, write the results to a table, and put the impact ratios on the same internal dashboard as fill rate and gross margin.
create table bias_metrics_history (
computed_on date not null,
window_start date not null,
window_end date not null,
grouping_kind text not null, -- 'sex' | 'race' | 'intersectional'
category text not null,
scored integer not null,
selected integer not null,
selection_rate numeric(6,4),
impact_ratio numeric(6,4),
primary key (computed_on, grouping_kind, category)
);
Keeping the history matters for a reason that is not statistical. When a ratio moves, the first question is what changed: a rubric version, a new client with a different candidate mix, a recruiter who started overriding the tool. rubric_version in the scoring log plus a monthly snapshot lets you answer it. Without them you are looking at one number a year and guessing.
What this does not do
Running these queries yourself is not a bias audit. LL144 requires an independent auditor, someone with no financial interest in the tool or its vendor, and a published summary of results plus the tool's distribution date. Your internal numbers are the input to that work and the early warning between audits. They are also, bluntly, how you find out whether the rubric your recruiters wrote is doing what they think it does.
The other half of the record — inputs, rubric version, per-criterion rationale, the human actor, the notice text and date — has to be captured at scoring time. None of it can be reconstructed in December from a table that only stored a score. Build the log first, the screener second.
If you are scoring candidates today and cannot produce the table in step three, contact us and name your ATS and your screening tool. That is a short piece of work, and it is the cheapest week of engineering on this list.