1489: Factor/entry join-key normalization¶
Implements the normalization plan Guilbert posted on #1489 (2026-08-24). Written up after the fact alongside the PR, since the work could not wait for his review round; deviations from his plan are listed at the bottom.
Problem¶
Factor resolution compares factor.classification[k] to entry.data[k] by
exact string equality (factor_resolver._build_maps), and the factor upsert
keys on (data_entry_type_id, year, emission_type_id, classification::text).
Nothing guaranteed the two sides agreed on casing or whitespace:
- an entry saved with currency
CHFnever matched a factor stored aschf - re-importing the same factor CSV with different casing inserted a second factor row instead of updating the first
- each module hand-rolled its own
_non_empty/str(v)validators, so the rules drifted per module
Canonical forms¶
Defined once in backend/app/schemas/fields.py and applied symmetrically to
the *FactorCreate/Update and *HandlerCreate/Update DTO of every field that
participates in a factor lookup:
| Alias | Rule | Used for |
|---|---|---|
CurrencyCode |
strip + lower | currency everywhere |
CountryCode |
strip + upper, RoW sentinel kept as-is (case-insensitive) |
travel country codes |
ClassificationKey |
strip only, case carries meaning | names, codes, categories |
OptionalClassificationKey |
strip, blank becomes None |
optional join keys (matches the CSV provider convention) |
IdentifierKey |
spreadsheet numeric forms collapse (1, 1.0, "1.0" all become "1", zero-padded ids untouched) |
researchfacility_id / researchfacility_name |
Closed-vocabulary validators (cabin class, energy type) keep their membership checks but normalize with strip + lower before comparing.
The two choke points the plan missed¶
Typing the DTO fields alone normalizes nothing that persists. Two code paths discarded the validated DTO output:
- Entries:
DataEntryPayloadMixin.unflatten_payload(mode="before") copies the RAW payload intodatabefore field validators run, anddatais whatDataEntryService.createpersists. Fixed with a central mode="after" validator on the mixin that syncs every validated field value back intodata(only for keys already present there, so PATCH semantics survive). - Factors:
base_factor_csv_provider._process_rowhand-builtclassificationfrom the raw CSV row and only used the DTO for type validation. It now copies the validated DTO value intoclassificationfor every classification field, so the canonical form is what lands in the upsert identity.
Pre-existing rows: an audited operator script, no migration¶
The plan on the issue asked for an Alembic data migration that rewrites
factors.classification and merges the duplicates. It was written, then
removed on the lead's decision (2026-09-08) once the dry run against prod and
stage showed it was not needed:
| Platform | Factors scanned | Factors to change | Colliding identities | Entries scanned | Entries to change |
|---|---|---|---|---|---|
| stage | 18,944 | 0 | 0 | 249,496 | 2,147 |
| prod | 18,944 | 0 | 0 | 249,484 | 2,147 |
The 2,147 entries are purchase and equipment rows whose name carries a
surrounding space. name is not a join key for either type (purchases resolve
on the institutional code, equipment on class and sub-class), so no total
moves either way. The dev platform database is empty.
What ships instead is backend/scripts/normalize_join_keys.py: dry run by
default, --apply to rewrite the reported rows by hand, refusal when factor
identities would collide or a value needs a manual decision. The rules are
copies of the DTO aliases, pinned by
tests/unit/schemas/test_join_key_normalization_rules.py and
test_normalized_fields.py, and exercised against Postgres in
tests/integration/services/data_ingestion/test_normalize_join_keys_script_pg.py.
A rewrite hidden in a deploy was exactly what the lead did not want on a
database that goes live on 2026-09-22.
With both DTO families normalizing on write, the compute handlers no longer re-lower entry currency: the stored value is the canonical one, and the audit proves the old rows already are.
Closed vocabularies from the #1489 audit (#2588, #2587)¶
Bundled into the same PR on the lead's decision (2026-09-08):
- Headcount factors:
headcount_category,headcount_classandunitare pinned to the values the shipped factor CSVs carry (HEADCOUNT_*lists inapp/modules/headcount/factors.py), strip + lower first.headcount_subclassis anOptionalClassificationKey. - Plane factors:
categoryis pinned toshort_to_medium_haul/medium_to_long_haul,max_distancemust exceedmin_distance, and a factor upload fails when two factors of one cabin class have overlapping[min, max)bands (check_plane_distance_bands, run by the newBaseFactorHandler.validate_year_factorshook on the year's resulting set inBaseFactorCSVProvider._validate_year_factor_sets, before the commit).get_haul_categorytakes the first band a distance falls into, so overlap would make resolution order-dependent. - Building rooms reference upload:
room_typechecked againstVALID_ROOM_TYPES,room_surface_square_meter >= 0. - External AI factor payloads resolve the provider through
resolve_aiand raiseEmissionTypeResolutionErroron an unknown one (#2587).
Spec decisions from #2591 (lead, 2026-09-08)¶
- Student FTE: no cap. It is the total FTE of all students of the unit, so it only has to be positive. Doc sentence for Martina, no code change.
- Process emissions subcategory: required exactly when the category's
factors carry one (Refrigerants today), optional otherwise. The form
already behaves that way (it skips the required check when a category has
no sub-options). The backend now enforces it where an entry enters:
factor_resolver.unresolved_reasonnames the missing or unknown subkind and lists the accepted ones; the CSV row becomes a row error, the API create a 422. The rule is generic (kind/subkind), so it also covers an equipment class whose factors all carry asub_class. Recompute is not touched: an entry already stored keeps today's behaviour. - Energy combustion unit: the entry DTO carries
unit(required on create, as the doc always said) and it must equal the factor's unit, through the same entry-time check (factor_match_fields = ("unit",)on the handler). The form already fills the unit from the picked fuel.
Tests¶
tests/unit/schemas/test_normalized_fields.py: canonical forms per alias, RoW sentinel, whitespace rejection, normalized-value-reaches-dataregressions, an end-to-end FactorResolver match from a noisy payload, and the script/DTO symmetry pin.tests/unit/services/data_ingestion/test_factor_csv_normalized_identity.py: re-importing the same purchase factor row withCHF/whitespace noise produces the identical classification identity.tests/unit/services/data_ingestion/test_factor_csv_shipped_identity.py: every row of every shipped factor CSV (committed smoke fixture always,backend/INPUT_DATA/*_factors.csvwhen present) keeps the same classification under whitespace/casing/1.0noise, and passes the closed vocabularies.tests/unit/modules/test_headcount_factor_schemas.py,test_professional_travel_schemas.py,test_base_factor_csv_provider.py: the vocabularies, the band check and its wiring.tests/integration/services/data_ingestion/test_normalize_join_keys_script_pg.py: old-style factor and entry rows on real Postgres, the audit reports exactly them,--applyrewrites them into the DTO form and a second audit finds nothing; colliding factor identities makeapplyrefuse and write nothing.tests/integration/services/data_ingestion/test_normalized_entry_pipeline_pg.py: a noisy CSV row (PIC-IT,EUR,1.0) through the real ingest chain stores normalizeddata, resolves the canonical factor and computes the expected kg CO2eq.tests/unit/services/test_factor_resolver.py,test_carbon_report_module_create.py,test_base_csv_provider.py,test_energy_combustion_schemas.py: the entry-time factor check and its two call sites.- Existing suites updated where messages or blank-handling changed
(blank optional codes now store
None, not"").
Deviations from the plan on the issue¶
- The defensive
.lower()in the purchase and external-cloud compute handlers is gone, as the plan asked: the audit against prod and stage shows no entry carries a non-canonical currency (plan 2592). - Added the two choke-point fixes above (data sync validator, provider classification from DTO); without them the typed aliases are cosmetic.
researchfacility_namealso gotIdentifierKey, not justresearchfacility_id: both arrive from spreadsheet exports and both join against the factor classification.
Decisions (lead, 2026-09-08)¶
- Target branch stays
dev. - No data migration and no recompute: the operator script audits, and rewrites only by hand; numbers move at the next natural recompute.