Backfill `tax_rate_id` on Invoice / Supplier-Invoice Lines¶

Date: 2026-06-15

Problem / motivation¶

The add_tax_rate_id_to_details migration (2026-05-17) added a nullable tax_rate_id FK column to invoice_details and supplier_invoice_details. Lines created before that migration—or before the frontend began sending the field—have NULL in that column. The ITBMS worksheet query uses an OUTERJOIN on TaxRate and resolves NULL to the sentinel ITBMS_UNCLASSIFIED, so legacy lines appear in a separate "unclassified" row instead of the correct ITBMS_7 / ITBMS_10 / ITBMS_15 / ITBMS_EXEMPT bucket. This backfill fixes those rows so the worksheet is accurate without requiring a schema change.

In scope¶

One-off script scripts/backfill_tax_rate_id_invoice_details.py that sets tax_rate_id on every invoice_details / supplier_invoice_details row where it is currently NULL.
Unit tests for the infer_tax_rate_code helper.

Out of scope¶

Schema changes (the column already exists).
Re-computing tax_amount or any other amount column.
Any GraphQL surface change.
Retention rates (ISR_RETENTION, ITBMS_RETENTION_50, ITBMS_RETENTION_100) — these cannot appear on normal invoice lines.

Data model changes¶

None — no new alembic revision required.

Inference rule¶

The tax_rate column stores the percentage as a plain number (7, 10, 15, 0), not a decimal fraction (0.07). This matches the formula in BaseAmountDetailInput.tax_amount:

sub_total_amount * self.tax_rate / 100

Mapping:

`detail.tax_rate`	`TaxRateCode`
0	`ITBMS_EXEMPT`
7	`ITBMS_7`
10	`ITBMS_10`
15	`ITBMS_15`
anything else	skip + warn

Once the code is resolved, TaxRateRepository.find_effective(code, invoice_date) locates the matching TaxRate row and its id becomes the new tax_rate_id.

What's being implemented¶

scripts/backfill_tax_rate_id_invoice_details.py — async script following the recalculate_client_balances.py pattern: iterates all tenants, batches reads + bulk-updates per tenant, caches (code, date) → tax_rate_id lookups to avoid N+1 queries.
tests/scripts/test_backfill_tax_rate_id.py — unit tests for the pure infer_tax_rate_code helper.

RBAC¶

No new Path / Resource entries — this is a one-off script, not a new resource.

Background tasks / cron¶

None — script is run manually once post-deploy.

Deploy order¶

Confirm add_tax_rate_id_to_details migration is applied (it was, as of 2026-05-17).

Run the backfill script against prod:

uv run python scripts/backfill_tax_rate_id_invoice_details.py --apply

Verify ITBMS worksheet shows zero rows in ITBMS_UNCLASSIFIED for periods that predate the migration.

Future additions¶

A periodic check query that alerts if new NULL tax_rate_id rows appear (would catch frontend regressions early).

Backfill tax_rate_id on Invoice / Supplier-Invoice Lines¶