SQL factor resolution for list sort/filter/pagination
Problem¶
get_submodule_data joins Factor through emission rows
(RollupEmission.primary_factor_id / min(primary_factor_id) agg). Entries
without computed emissions get Factor = NULL in SQL: factor-backed
sort keys (equipment.active_power_w, buildings/cloud/process maps) sort
them last, filters miss them, and the Python resolver fallback fixes only
the displayed page — after pagination. Sort/search/pagination disagree with
what the table shows.
Design¶
One correlated LEFT JOIN LATERAL ... LIMIT 1 per entry row, built
generically from the handler's classification fields — the SQL twin of
FactorResolver, deterministic because the Phase-2 reupload sweep
guarantees a single factor generation per (det, year, classification):
- match:
f.data_entry_type_id = :det AND f.year = data_entries.year AND f.classification->>kind = data.data->>kind - subkind chain (plain handlers): accept exact subkind or subkind-less row;
ORDER BY (subkind matched) DESC→ exact wins, kind-only row is the fallback. - override chain (purchase): accept override-code match or code-less
(average) row;
ORDER BY (code matched) DESC, (code IS NULL) DESC— override-first, then average. Ambiguity resolves to a deterministic pick (display layer; compute/update keep raising loudly viaFactorResolver). - Aliased to the
Factorentity; handlersort_map/filter_mapexpressions referencingFactor.*are adapted onto the alias at query build (ClauseAdapter), so the four modules' maps stay untouched.
Scope of the swap: the buildings branch and the generic non-travel branch
(covers equipment, buildings, external_cloud_and_ai, process_emissions,
purchase, …). Travel and headcount branches keep the emission join — their
kind is derived at compute time (absent from data), their maps don't
reference Factor, and their factor display comes from computed emissions.
The Python row-loop resolver fallback in get_submodule_data becomes dead
and is deleted (the lateral answers for every lateral-covered row; the
remaining branches never benefited from it — kind absent from data).
Display-semantics note (accepted): tables show the current resolution, not "factor used at last compute"; post-Phase-2 these converge on every recalc.
Steps¶
- 1. (shipped as
_resolved_factor_id) correlated scalar subquery instead of LATERAL: works on sqlite (unit tests) AND Postgres, joins the realFactortable so sort/filter maps need no adaptation at all; wired into the buildings and generic non-travel branches; Python fallback deleted. sqlite quirk pinned: correlated ORDER BY is rejected, so the specificity preference is the non-correlatedIS NOT NULL DESC(within the WHERE-filtered candidate set, carrying a subkind/code IS the exact match). - 2. PG integration test (
test_sql_factor_resolution_pg.py): entry WITHOUT emission rows sorts/filters by factor-backed keys and displays the factor; kind-only fallback preference; purchase override preference; entry with emissions unchanged; travel list unaffected. - 3. Retargeted the four resolver-fallback unit tests to seeded-row tests + new subkind-preference and duplicate-determinism pins in
test_data_entry_repo.py(fallback deleted). - 4. Focused verification (changed test files +
tests/unit/repositories), ruff/mypy on touched files; PR stacked on #1719. - 5. Update path stops resolving (user-approved): replace
resolve_factor_if_changedwith a resolver-freeclear_dependent_fields_on_kind_change(subkind + override-code cleared on kind change, nothing else); drop the update-pathpopulate_defaultscall (near no-op today: fills only still-empty fields). - 6. Derived hour defaults (user-approved): equipment formula falls
back to
factor.valuesfor missing usage hours (enable the sketch at equipment/schemas.py:218-221); stop seeding at create/CSV; deletepopulate_defaults+factor_value_fields. Form pre-fill keeps coming from/factors/{det}/classes/{kind}/values. Semantics owned: blank-hours entries track the factor's current defaults ("live default").
Progress log¶
- 2026-07-06: plan written; branch
feat/1661-sql-factor-resolution. - 2026-07-06: steps 1-4 done (scalar-subquery design; 261 repo unit tests + PG test green).
- 2026-07-06: steps 5-6 done — update PATCH does zero factor resolution (pure kind-change clearing); populate_defaults/factor_value_fields deleted; equipment hours are live defaults (ctx wins, factor fallback in formula, coalesce in sort_map, effective values in to_response); CSV ingest row path no longer matches factors at all. Unit 1748 + ruff + mypy green; purchase/sql-resolution/matrix/buildings/sort integration files green.