Stakeholder Payout Calculation

Status: Shipped (Reports Tier 8, #2102, PRs #2221, #2227, #2240) Code: domains/billing/services/revenue_share/ Related: 03-api-reference/stakeholders-revenue-apis.md documents the LEGACY parts/clinic stakeholder APIs. This describes the billing.* domain model that replaces them.

How much a facility's stakeholders are owed for a month, and why each step is the way it is. Most of the reasoning below exists because the obvious alternative loses money.

The shapeCopy Anchor Link

  collected revenue          payments with status 'completed', by payment_date
- settled refunds            refunds with status 'completed', by processed_at
- facility costs             the entered figure for the month (see below)
- approved commissions       by the invoice's issue_date (see below)
= distributable
- carry-forward in           the previous calculation's unrecovered deficit
= after carry
- platform share             a % of `after carry`, off the TOP
= stakeholder pool           what the stakeholder tree divides

Billing::RevenueShare::FacilityPeriodPl computes everything above the pool and returns a value object without writing anything, so a caller can preview a period and a test can assert the numbers directly. Billing::RevenueShare::CalculationService persists the result and writes the distributions.

Recognition: every input is dated by when the money movedCopy Anchor Link

Two commission paths, both deductedCopy Anchor Link

A facility can run invoice-grained rules, tiered rules, or both, and the P&L deducts both (#2235). They differ in grain and therefore in how a period is matched:

A tiered run already measures work by invoice date — its service selects on billing.invoices.issue_date between the window bounds — so its window is an invoice-month window. The only difference is that it is stored on the row rather than read off a joined invoice.

A straddling window lands whole in the month it starts in. CommissionWorkflowJob#calculate_period_dates produces daily, weekly or monthly windows from the workflow's schedule, and a weekly one crosses a month boundary roughly once a month. That is the scheduled path; POST /billing/commission_workflows/:id/execute takes arbitrary start_date / end_date, validated only as parseable dates, so a manual backfill can produce a window of any length — and six months of commission then lands whole in the month the window opens, quite possibly one already settled. Prefer a run per month when backfilling. It is not split, because calculated_commission is a threshold function of the window's TOTAL revenue and so is not linear in the items — pro-rating would invent precision the number does not have. And it is not dropped for failing to fit, which is the bug #2235 was. One month, deterministically, never twice and never nowhere. A facility that wants its tiered commissions in the month they were earned should run a monthly workflow.

Two REVENUE_GENERATED rules on the same revenue both deduct, and that is not a double-count: such a rule pays a percentage of the facility's total revenue for the window, so two of them are two obligations to two sets of people.

Runs of one rule covering OVERLAPPING dates are refused, because that is one obligation counted twice.

Overlap, not "more than one run". Several runs of one rule in a month is the designed shape for any non-monthly workflow — calculate_period_dates gives a weekly one four or five disjoint windows, each covering different work, and summing those is exactly what the deduction is for. A count-based check brands every daily or weekly tiered facility a duplicate, and since RevenueShareCalculationJob swallows the error per facility, those months would simply stop being produced.

Nor "identical windows". earned_in_period deducts every run whose window opens in the month whatever its end, so a backfill typed 08-01..08-30 beside a scheduled 08-01..08-31 is two full deductions while sharing no bound; so is a daily run sitting inside a monthly one after a schedule_frequency change.

The refusal fires only where it leaves the operator something to do. A pair still calculated is not deducted at all, so refusing there would block a month where nothing is doubled. And once every overlapping run is PAID there is no PAID -> CANCELLED transition to withdraw one — the money genuinely left twice, which makes it a cost genuinely incurred, so the P&L reports it and the mistake to fix is upstream. Refusing would only make the month permanently uncalculable and strand its stakeholders. So: overlapping DEDUCTED runs, at least one still approved. The settlement gate applies the same exclusion, for the same reason.

APPROVED -> CANCELLED is reachable through the API but not the UI. TieredBulkUpdateService::TRANSITIONS allows it; CommissionCalculationsPage renders the Cancel button only while a run is calculated. So the refusal's remedy currently needs POST /billing/tiered_commission_calculations/bulk_update. That is why the gate reports an overlap before the unapproved condition — while both runs are still calculated, cancelling is something an operator can actually do.

Nothing prevents overlapping runs being created: there is no unique index on billing.tiered_commission_calculations beyond its primary key, fetch_all_invoice_items does not exclude already-counted work for REVENUE_GENERATED, and CommissionWorkflow#can_execute? only blocks while a run is RUNNING — so executing a workflow twice over the same dates makes two rows, each carrying the full commission. Harmless while nothing read the table; money once the payout deducts it. FacilityPeriodPl raises DuplicateTieredRuns naming the rule, and the settlement gate reports it before staleness so an operator is not sent to recalculate a month the P&L will refuse. Cancel the overlapping run and the month calculates.

The exclusivity that does exist between SERVICES runs claiming the same invoice item is enforced by idx_tiered_items_services_item_unique where the items are claimed. exclude_already_paid filters on exclusive_claims, not on all ledger rows — claim_items writes a row for everything a run counted, so excluding all of them starved any later SERVICES run of every item (#2261). It also skips cancelled individual commissions, so withdrawing one gives its service back to a tiered SERVICES run, matching what TieredBulkUpdateService already did on its side. It is not a general undo. The individual path does not look at status: CommissionCalculationService excludes per rule (commission_rule_id: rule.id), so a cancelled commission still blocks its own rule forever, though a different rule would be offered the item. Cancel is an undo only for a tiered SERVICES run.

Months starved before that fix are invisible to the DRIFT check, and the report now looks for them separately (#2271). A starved run persisted nothing, so commission_now and commissions_deducted are both wrong in the same direction and stale_reason reports no drift — the drift check calls such a month clean while its practitioners were underpaid and its stakeholders overpaid.

They are found by their ABSENCE instead. rake billing:revenue_share:reconcile prints a STARVED group, fed by Billing::StarvedServicesRunFinder, beside DOUBLE-COUNTED. A starved month also withdraws the report's unqualified all-clear: the verdict drops to "No drift AMONG WHAT WAS CHECKED", because printing "every stored deduction matches" beside a list of starved months is the sentence that let them stay hidden.

The group reports candidates, not findings. A SERVICES rule whose assignees billed nothing that month is indistinguishable in the data from a starved one, so each row needs confirming against what was actually billed.

Re-running the WORKFLOW is still not the recovery: it iterates every rule, so it creates a second REVENUE_GENERATED run over the same window. Run the one rule instead:

POST /billing/commission_workflows/:id/execute
     { start_date:, end_date:, commission_rule_id: <the SERVICES rule> }

commission_rule_id is optional and restricts the run to that rule. The tiered-overlap refusal is scoped to the rules a run will actually execute, so a single-rule run is not refused for an overlap belonging to a rule it is not going to touch. Two guards catch it, at different moments, and the first one is not the P&L's DuplicateTieredRuns: a calculated run is not deducted, so FacilityPeriodPl computes fine and only marks the month provisional. The settlement gate is what speaks up — duplicate_tiered_runs_reason queries calculated as well as the deductible statuses, deliberately, so the operator is told while the extra run is still in the one status the UI offers a Cancel button for. Cancel it and recalculate. Approve it instead and it becomes deductible, DuplicateTieredRuns starts firing on the next P&L computation, and withdrawing it needs the bulk_update API.

Billing::CommissionCalculation.billed_in_period is the single predicate that encodes the basis for the invoice-grained table. FacilityPeriodPl (which computes the deduction), RevenueShareCalculation (whose gate refuses to settle a stale one) both go through it, and the reconciliation rake reads it through the model. The first two previously each wrote the join out — two copies of a rule whose entire history is that it moves, so the next move would have left one of them silently answering on the old one. CommissionCalculation.for_period is deliberately NOT this: it buckets by calculated_at for settlement windows, and a basis change must not touch it.

Commissions: a per-market basisCopy Anchor Link

Billing::CommissionBasis is a tenant setting, because it is an accounting policy rather than a per-facility choice. Both options serve one principle — a commission belongs to the same period as the revenue it was earned on — and differ only in which date stands in for "the revenue's month".

collection_month is named but unimplemented, and asking for it raises rather than falling back. It needs a pro-rating rule for an invoice paid across two months, and a decision about a commission on an invoice not yet paid at all. Reserved rather than omitted for the reason RevenueBasis reserves billed_net: a tenant's basis gets written into a contract, and having nowhere to record it means a migration plus a backfill of assumptions later.

Operationally: August's payout bears August's commissions however late they are approved, so a closed month is not final until they are. That is what the settlement gate enforces — August cannot be settled while work billed in August still carries unapproved commission.

The basis was briefly the commission's own approved_at (#2227). That kept a closed month's figures stable, but the ordinary workflow — close August, approve its commissions in September — put every deduction one month after the revenue, permanently, and the first month bore none at all. See #2239.

A month with no cost figure BLOCKSCopy Anchor Link

FacilityPeriodPl raises MissingCosts rather than treating costs as zero. A zero cost base over-distributes every stakeholder payout for that month, and the error is unrecoverable once the money has gone out.

Costs are entered through billing.facility_period_costs (facility × month, amount plus a free-form category_amounts breakdown that is never reconciled against amount — only amount is subtracted). Entry is idempotent per facility-month: posting for a month that already has a figure replaces it and returns 200 rather than 201. An endpoint that appended would double the cost base and halve every payout for that month.

The UI for this is the Reports app's Facility Costs page — see frontend/docs/features/facility-cost-entry.md.

Platform share comes off the topCopy Anchor Link

It used to be one stakeholder row inside a 100% split. It is now distribution_rules.platform_share, taken before the stake split, so the remaining stakeholders sum to 100% among themselves.

20260831190000 migrates existing configs and re-bases the other percentages over the remainder. It deliberately leaves alone:

• configs whose distribution_rules.method is not percentage — platform_share_percentage returns nil for those, so rebasing would write a value nothing reads while handing the platform's share to the stakeholders;
• a platform row paid a flat share_amount (its percentage is nil);
• an allocation over 100%, which update_columns would otherwise store past the model's own 0..100 validation;
• a platform row with active children, because deactivating it orphans them.

The stakeholder treeCopy Anchor Link

Distribution walks from the roots. A child's percentage is a share of its parent's amount, not of the pool, and the parent's row is written net of what its children take.

Legacy pays the parent its full gross and each child a slice of that same gross, so a 60% parent with a child on half of it distributes 130% of the pool. That is deliberately not ported — it is the same double payment as #2184 in a different table.

Guards, all of which refuse rather than guess:

• OverAllocated — sub-shares exceeding their parent, or roots exceeding the pool. The root check tolerates one paisa per root, because each share rounds to paise independently and a set summing to exactly 100% can still land a fraction over; the real defect it catches overshoots by rupees.
• UnreachableStakeholder — an active stakeholder whose parent is inactive. Its share is a slice of a parent that no longer takes one, so there is no correct amount to pay it and paying it as a root would take from the whole pool.

IdempotencyCopy Anchor Link

POST /calculate for a facility-month replaces any previous calculation, and idx_rev_share_calcs_facility_month_unique enforces one row per facility-month.

Without that, a double-click, an ops re-run after correcting a cost figure, or a Sidekiq retry produced a second calculation and a second full set of payable distributions — the facility paid every stakeholder twice. The legacy job guarded this with destroy_all; the first Tier 8 port dropped it.

Recalculating discards a later month only when this month's carry_forward_out actually changes — that is the only thing a later month depended on. The discarded months come back in meta.superseded_months and the deletion is written to the commission audit trail. Refused outright — AlreadySettled — once the month itself has been approved or has a distribution past pending, and once any discardable later month has.

superseded_months cuts both ways. An empty one is not a statement that the later months are correct; a non-empty one is a list of months you now have to re-run, including clean ones that were deleted as collateral.

Empty: In a profitable chain carry_forward_out is 0 before and after, so nothing is discarded and the later months are not even examined. That is right for the ordinary re-run — a corrected cost figure that leaves the carry-forward alone changes nothing downstream — but it is exactly wrong for a basis change, where each month's own commission deduction moved. When rake billing:revenue_share:reconcile lists several months for a facility, re-run all of them, oldest first; do not stop at the first and assume the rest were superseded.

Carry-forwardCopy Anchor Link

A month whose distributable is negative records the shortfall in carry_forward_out; the next calculation subtracts it as carry_forward_in.

The lookup takes the most recent calculation before this month, not the one exactly a month back: a month with no entered costs legitimately has no calculation, and an exact lookup silently forgave the deficit across that gap.

Months must therefore be calculated in order, which is why an out-of-order run discards the later months rather than double-recovering the same deficit.

total_revenue stores gross collected revenue with refunds counted in deductible_expenses (alongside costs, and broken out in refunds_applied). Netting refunds off revenue made a month whose refunds exceeded its collections fail the total_revenue >= 0 validation, so it could not be recorded at all — and a month with no row carries nothing forward.

The settlement gateCopy Anchor Link

RevenueShareCalculation#settlement_blocked_reason blocks approving or paying a distribution when:

1. the period has not ended — more work can still be billed into it;
2. a tiered rule has runs covering overlapping dates in the period — the same obligation counted twice, so the deduction would double;
3. commission on this period's work is unapproved — it will come off this pool once approved, so the figure is not final; or
4. the stored pool is stale — what the period's approved and paid commissions now sum to no longer matches what this calculation actually deducted; or
5. commission deducted from this pool is unpaid — the stakeholder figure is what remains after commission, so paying it out while the commission is still owed pays a residual the business has not funded.

(2) is checked before (3), and the order is load-bearing: a duplicate run is born calculated, so (3) would fire first — and its remedy, "approve each outstanding row", moves the duplicate out of calculated, the only status the UI offers a Cancel button for. Following the guidance would walk the operator into a one-way door. The message names the rule, because the payout screen has nothing else to identify the extra run by.

Condition (5) clears either way commission is paid: row by row through Commissions::UpdateStatusService, or in a batch through CommissionSettlement#mark_paid!, which flips the approved members it settles (#2236). A settlement still holding an unapproved member refuses to be paid rather than stranding it.

A settlement may span facilities — SettlementService groups by the earner pair and the period, not the facility — so paying one can open the gate at more than one. That is correct: those facilities' commissions really were paid. The single facility_id stamped on such a row is the part that misleads (#2246).

All five are keyed to the invoice month, matching the deduction.

(4) closes the hole the invoice-month basis opens. The ordinary workflow — calculate August while its commissions are unapproved, approve them afterwards — produces a snapshot that deducted nothing, and conditions (1), (3) and (5) all clear once those commissions are approved and paid. Without the staleness check the full un-netted pool was distributable. The fix is to recalculate the month; the message says so.

It is recomputed live. The provisional column is a snapshot written once at calculation time and never revisited; it is still stored and serialized for history, but it is not the gate. The blueprint exposes settleable and settlement_blocked_reason alongside it.

billing.commission_calculations.approved_at is required once a row is APPROVED or PAID (validated on the transition; 20260901120000 backfills older rows). A null one belongs to no period: its cost is deducted from no pool and it blocks no gate.

Who approved and who paidCopy Anchor Link

A distribution records the actor as well as the time of each money step (#2420): approved_by / approved_at on approve, paid_by / paid_at on pay, both pointing at identity.users. RevenueShareDistribution#approve!(user) and #mark_paid!(user) take the acting user and raise ArgumentError without one, so neither step can write an unattributed row.

Each of approve!, mark_paid! and cancel! locks and re-reads its own row inside the transition, the way CommissionSettlement does, so two concurrent requests cannot both pass the status check and overwrite each other's actor — and a caller other than RevenueShareDistributionsController cannot lose that protection by forgetting to lock. The controller wraps them in a lock on the payout MONTH first, which is the order every other writer of those two tables takes.

The detailed blueprint exposes the pair as approved_by / approver_name and paid_by / payer_name, the same shape as the config blueprint's approver. The :normal view and the stakeholder-facing My Statement blueprint do not carry them: who signed a payout off is tenant-finance information, not statement information.

Cancelling is not attributed on the row, and there is no cancelled_by column. Who cancelled is recorded only by the cancelled entry the controller writes to Billing::Commissions::AuditTrail — and that write is deliberately best-effort (AuditTrail.record logs and swallows every failure), so a cancel whose audit write fails leaves no record of who did it anywhere. See Known gaps.

Rows approved or paid before 20260929100000 have null actors; the commission audit trail (Billing::Commissions::AuditTrail, actions approved / paid) is the record for those.

Cancelling a commissionCopy Anchor Link

PATCH /billing/commission_calculations/:id with status: CANCELLED goes through Commissions::UpdateStatusService. A cancelled row leaves DEDUCTIBLE_STATUSES, so cancelling an approved commission changes what its month deducted (#2245):

• Payout still pending — the cancel goes through, the month reads stale on (4), and the gate asks for a recalculation.
• Payout month approved or processed, or any distribution approved or paid, AND the commission is inside that month's stored deduction — refused with a 422 naming the month. Money has moved on a pool netted of the commission, and assert_not_already_settled! would refuse the recalculation that could correct it. "Inside the deduction" means approved_at <= calculated_at of the month: FacilityPeriodPl deducts what is approved when it runs. The month is found through RevenueShareCalculation#commissions_in_period (that is, CommissionCalculation.billed_in_period), so the commission basis is not restated here.
• A commission approved after its month was calculated was never deducted. Cancelling it brings a stale month back into line, so no month blocks it — that is also the repair for an approval made into a settled month.
• A calculated commission was never deducted, so no month blocks its cancel.

On the earner side, a commission in a paid CommissionSettlement is refused. One in a pending or approved settlement is detached, and the settlement's total_commission is recomputed from its approved and paid members. An approved settlement goes back to pending with approved_by/approved_at cleared: its approval was of the old figure, and mark_paid! records a transfer made against it, so it must be approved again at the new one. (PATCH /commission_settlements/:id/approve 500'd on every call until this change; it now stamps the caller as approver.) Bulk cancels audit each settlement they shrink under released_from_settlements. Cancel-and-regenerate is not an option: idx_comm_settlements_earner_period is not partial on status, so a cancelled settlement blocks a new one for the same earner and month. An earner line of a tiered run (tiered_commission_calculation_id set) is refused; cancel the run.

Locks are taken settlement, then payout month, then the row, and the transition is re-checked after the lock. A distribution approve or pay locks the same payout-month row before reading the gate, so the two serialize. SettlementService reads, sums and links its candidate commissions under FOR UPDATE, so a row cancelled meanwhile drops out instead of being linked inside the new total.

The basic-commission path's RevenueDistributionQueue entry is not reversed on cancel. Nothing reads it for money since Tier 8; it is per-item tracing only.

Periods are measured where the facility isCopy Anchor Link

Billing::FacilityPeriodBounds converts a calendar period into the instants it spans using identity.facilities.timezone, and every timestamptz comparison in the payout goes through it — approved_at, processed_at, and whether the period is still open.

Time.zone is UTC (config.time_zone is commented out), so without the conversion anything stamped between 00:00 and 05:30 IST on the 1st landed in the previous month's pool, and a month closed hours late for a facility ahead of UTC and early for one behind. The billing reports already converted, so the payout and the reports disagreed about which month a thing fell in.

A facility with a NULL timezone keeps the previous behaviour deliberately — guessing a region would silently move money.

Collected revenue is unaffected: payments.payment_date is a DATE, not an instant.

Which rule produced a row: the basis markerCopy Anchor Link

billing.revenue_share_calculations.commission_basis records the Billing::CommissionBasis each row was computed under (20260901180000). Every figure on the row — deductible_expenses, distributable_revenue, carry_forward_out — is a snapshot, and the rule that produced them has changed three times:

Nothing on the row said which. Because the carry-forward chains months together, one row computed under a different rule propagates into every month after it, and the same commission can end up deducted from no month (an August invoice approved in September: the stored August row used approval-date and took nothing, and a new September row excludes it as an August invoice) or from two.

NULL means the row predates the column. That is the honest value — the basis genuinely was not recorded and cannot be recovered from the row — so it is deliberately not backfilled to a guess.

The drift reportCopy Anchor Link

bundle exec rake billing:revenue_share:reconcile          # every tenant
TENANT_ID=<uuid> bundle exec rake billing:revenue_share:reconcile

Every table it reads is FORCE RLS, so it walks the tenants, each under its own context (Shared::Tenancy::EstateWalk, shared with billing:starved_services_runs). A TENANT_ID that is not an id, or matches nothing, aborts; one that is set but empty means "every tenant". The run exits 1 when any tenant could not be read — a wrapper keyed on the status must not take such a report for a clean one.

For each closed calculation it asks RevenueShareCalculation#stale_reason — the settlement gate's own question, not a re-implementation of it — and splits what disagrees into two groups. The DEDUCTED and SHOULD BE columns are the two numbers that answer went on: #commissions_deducted and #commission_now.

What it does not check, and says so in its header:

• Still-open months. A month nobody has finished billing into is expected to disagree; the settlement gate makes the same exclusion, in the same order, before it reaches stale_reason. Counted, not listed.
• Tenants on an unimplemented basis. commission_now answers the question for invoice_month; there is no "what it should be" for collection_month, and the re-run the report would prescribe raises UnsupportedBasis anyway. Named under NOT CHECKED.
• Tenants it could not read. A tenant whose scan raised is named under COULD NOT BE READ (the error itself goes to stderr as it happens), counted in the header, and contributes nothing to any group — a scan that failed part-way is discarded whole. The all-clear is qualified and the run exits 1.

It also counts the rows whose commission_basis is NULL, so the share of the report resting on an assumption is visible rather than implied.

Drift here is not only a basis change, and the report says so. Three causes produce the same disagreement:

Re-running fixes all three, but read the direction off the ROW, not the cause. The second shrinks the pool — often to zero, so the month correctly gains no payout rows. The third enlarges it, and gains rows where you were expecting none. The first goes whichever way its own two columns say. The commission_basis column is what tells the causes apart; it is not a filter, and it does not tell you the direction.

Following its instructions literally matters. The payload it prints includes auto_distribute: true, and that is not decoration: recalculating deletes the month's distributions, writes new ones only when that flag is set, and revenue_share_distributions has no create route. Omitting it leaves the month with no payout rows and no way back except another calculate call.

It is necessary, not sufficient — create_distributions also requires stakeholder_pool.positive?. For the commonest row here, one whose deduction grew, a pool driven to zero or negative is an ordinary outcome rather than an edge case; the month then correctly has no distributions and hands the shortfall to the next via carry_forward_out. The symmetric case (DEDUCTED > SHOULD BE) does the opposite and gains rows.

A row it lists as RE-RUNNABLE can still be refused, because settled? inspects only that row — while assert_not_already_settled!(later) also guards the later months whenever the re-run changes the carry-forward. The report says so in its footer.

meta.superseded_months is a work list, not a notification. When the carry-forward changes, supersede_later_calculations! deletes every later month for that facility — including a month that was CLEAN and therefore never appeared in the report. Nothing surfaces that afterwards: the next month re-run simply reads its carry-forward from further back and reports no problem, while the deleted month has no calculation and its stakeholders are never paid. Re-run every month superseded_months names, not only the ones the report listed.

It is a report, not a migration, and that is deliberate. Recalculating automatically would be wrong: supersede_later_calculations! discards a later month only when the carry-forward changes, so re-running an earlier month can leave a later one that already deducted the same commission under the old rule — the same money off two pools. Re-run the affected months in period order, oldest first, per facility.

One active configuration per facilityCopy Anchor Link

A month is calculated under the facility's active configuration, resolved through RevenueShareConfig.currently_effective.first. Two active rows would make that pick, and so the split that pays stakeholders, arbitrary. Three layers keep it to one (#2405, #2419):

currently_effective is also ordered — effective_from DESC, created_at DESC, id DESC — so .first is deterministic even across facilities.

The index's migration (20260929120000) demoted any existing duplicates to inactive before building it, keeping the row in force today, then the latest effective_from, then the newest, then the highest id. Every demotion is printed in the deploy log. Nothing is deleted; to swap which one is active, deactivate the winner and activate the other from the Configurations screen. down drops the index and leaves demoted rows inactive.

Before deploying the indexCopy Anchor Link

On a facility that had duplicates, this migration can change which configuration pays out — it does not only make the existing choice stable. Before the index, the calculation took currently_effective.first with no ORDER BY, so Postgres was free to return any active row and in practice returned them in heap order, which for this append-only table means the oldest was usually picked. The migration keeps the newest in force. So the next calculation for such a facility can split revenue differently from the last one. Check the affected facilities before deploying, not after.

This query lists them and names the row the migration will keep (the first element of ranked):

SELECT facility_id,
       count(*) AS active_rows,
       array_agg(name ORDER BY (effective_from <= CURRENT_DATE
                                AND (effective_to IS NULL OR effective_to >= CURRENT_DATE)) DESC,
                                effective_from DESC, created_at DESC, id DESC) AS ranked,
       array_agg(effective_from ORDER BY effective_from) AS effective_dates
FROM billing.revenue_share_configs
WHERE status = 'active'
GROUP BY facility_id
HAVING count(*) > 1;

Two things about running it:

• It must not be grouped by tenant_id. The index keys on facility_id alone, so it refuses two active rows for one facility even across tenants, while the repair ranks each tenant separately and would leave that pair in place. Grouping by (tenant_id, facility_id) hides exactly the shape that fails the index build and rolls the deploy back.
• Run it as a role that bypasses RLS. billing.revenue_share_configs is FORCE RLS, so an ordinary application role with no app.current_tenant_id set returns zero rows whatever the true state is — it reads as "all clear" when it is not. Either use a BYPASSRLS role or set the tenant context per tenant.

CURRENT_DATE above is the database's date; the migration ranks on the application's Date.current. Where the two time zones straddle midnight the winner can differ from what this query shows.

Also worth knowing before you deploy:

Known gapsCopy Anchor Link

Where the tests areCopy Anchor Link

The gate and P&L tests pin the clock with travel_to. provisional? is Date.current <= period_end, so without a pin the fixtures' August 2026 months change meaning depending on the day the suite runs — they were briefly passing for that reason alone.