Implementation Plan: Reuse EmbodiedEnergyWorkflow logic for CSV/bulk building ingestion¶
Issue: #878 — the title/body is unrelated (Results-page feedback), the actual report is buried in a comment thread from
@martina-gallato: "le calcul des constructions et rénovations ne s'effectue pas lors de l'ingestion des CSV, mais uniquement lorsque je saisis des données manuellement" — i.e. this exact gap, plus an explicit call that old data will not be retroactively recalculated, only new ingests going forward. That framing (no backfill promised to the reporter) should be revisited before merge against the "DB persists, backfills apply" project default — flag it to the maintainers rather than silently deciding either way.Prior attempt: PR #2121 (open,
BenBotros, CI green, no tests) already fixes the symptom — will not be merged (decided below in §5), but its structural decisions are worth reusing. See §0 for what it got right and what this plan changes; §5 for landing this as a fresh implementation against currentdev.Touches recalculation/pipeline internals (
backend/app/workflows/,backend/app/services/data_ingestion/,backend/app/tasks/) — perguardrails.md, do not implement without a written plan reviewed by both maintainers. This file is that plan; get it reviewed before writing code. Target branch:fix/pipeline-debug, notdev, per project convention for pipeline-chain work.
0. What PR #2121 already got right (and wrong)¶
Read in full (gh pr diff 2121, 4 files, +116/-11, no test files). It:
- Adds
DERIVED_DATA_ENTRY_TYPES = {building: [building_embodied_energy]}(app/models/module_type.py) and folds it into_delete_existing_entries_for_module_per_year'svalid_entry_typesso a det-pinned building re-upload deletes stale derived rows along with the stale rooms — full delete-then-recreate, not a diff. This is simpler than this plan's earlier diff-and-sync draft and fits the file's existing "per-year CSV is a complete export" precedent (base_csv_provider.py:786-801). Worth keeping. - Runs the derivation synchronously, inline, at the tail of
_finalize_and_commit(_create_embodied_energy_companions) — not as a new chained job. Since derived rows don't need priced emissions to exist (that comes from the recalc chain right after), and the row count is bounded by the CSV itself, there's no real need for a separate async step. Worth keeping — it avoids adding a new job type, a newDedupConfig, and a migration, which is a much smaller, lower-risk diff for something touching pipeline internals. - Fetches the just-inserted
buildingrows back via a newDataEntryRepository.list_by_creator_and_type(created_by_id, type)(data_entry_repo.py) — necessary becausebulk_copy(used for the main batch insert) never populates.id(app/repositories/data_entry_repo.py:104-134), and the derived entry'sdata.data_entry_idFK-link needs the real id. Worth keeping. - Prefetches the whole
BuildingRoomtable once (BuildingRoomRepository.list_rooms()) instead of one query per row — already avoids the ~20k-row N+1 for the CSV path itself. Worth keeping (independently,BuildingRoomModuleHandler.pre_compute's existing per-rowget_roomcall for the interactive recalc path still has the N+1 — out of scope for #878, flagged as a possible follow-up in §2.2, not bundled into this fix). - Fans out a parallel sibling
emission_recalc(building_embodied_energy)job next toemission_recalc(building)(_chain_emission_recalc_for_data_ingest,app/tasks/ingestion_tasks.py:492-...) instead of chaining it sequentially after. Safe specifically because derived rows already exist (created synchronously, step above) by the time the async chain fires — no ordering dependency between the two siblings. Worth keeping — this plan's earlier draft over-built a 3-deep sequential chain to solve an ordering problem that doesn't exist once creation is synchronous. - What it gets wrong — the actual point of this plan:
_embodied_energy_companion(a new@staticmethodonBaseCSVProvider) reimplements thebuilding→building_embodied_energytransform from scratch instead of callingEmbodiedEnergyWorkflow. Two independent implementations of the same derivation will drift — the guardrail this whole plan exists to uphold. Concretely it already has drifted: it resolvesroom_surface_square_metervia the room-table lookup and falls back toparent.data.get("room_surface_square_meter")— dead code for CSV rows (that key is never present, per §1's diagnosis) copied over from a misreading of the CRUD path's original (also-buggy)_make_building_embodied_energy_data. It also puts building-specific logic (BuildingRoomRepositoryimport, building-only branch) insideBaseCSVProvider, the generic base every module's CSV ingestion goes through — a layering smell (mirrors, don't invent: buildings-specific logic belongs behindEmbodiedEnergyWorkflow/a buildings service, not in the shared CSV base). And it ships with zero tests, against the "every bug fix ships with a regression test" guardrail. - Verdict: reuse §0's structural decisions (delete-then-recreate,
synchronous inline, parallel recalc sibling,
list_by_creator_and_type), replace_embodied_energy_companionwith a call into the shared, correctedEmbodiedEnergyWorkflowresolver from §2.1, and move the buildings-specific orchestration out ofBaseCSVProviderintoEmbodiedEnergyWorkflowitself (called from one small, generic hook in_finalize_and_commit—DERIVED_DATA_ENTRY_TYPES-driven, not buildings-hardcoded, so the base class stays module-agnostic). Add the missing tests.
1. Diagnosis¶
EmbodiedEnergyWorkflow.post_create/post_update/post_delete
(backend/app/workflows/embodied_energy.py) derives a
building_embodied_energy DataEntry whenever a building (room) entry is
created/updated/deleted — but it is wired in only at the single-item CRUD
API layer (backend/app/api/v1/carbon_report_module.py:892,987,1043, called
right after CarbonReportModuleWorkflow.create/update/delete). The CSV bulk
path (backend/app/services/data_ingestion/base_csv_provider.py,
ModulePerYearCSVProvider) never references it — confirmed by grep and by
the test suite's own docstring
(backend/tests/integration/services/data_ingestion/test_buildings_csv_pg.py:20-23):
"building_embodied_energy — derived: rows are created post-hoc by
EmbodiedEnergyWorkflow.post_create... No CSV ingest path." So today, a
building room CSV upload produces zero embodied-energy rows; only rooms
created one at a time through the UI form get one.
A second, deeper bug surfaced during investigation and must be fixed as
part of the same change, not worked around:
EmbodiedEnergyWorkflow._make_building_embodied_energy_data reads
room_surface_square_meter straight off the source building entry's
.data dict. That field is not a declared field of
BuildingRoomHandlerCreate (backend/app/modules/buildings/data_entries.py:57-62
has only building_name, room_name, room_type,
room_allocation_ratio, note) — it is a real, resolved-from-reference-data
value (BuildingRoomModuleHandler.pre_compute,
backend/app/modules/buildings/handlers.py:72-109, calls
BuildingRoomService.get_room(room_name=...)), never persisted on the
building row (per the "don't store derived values" invariant, and
confirmed: DataEntryEmissionService's ctx merge never writes pre_compute
results back to data_entry.data,
backend/app/services/data_entry_emission_service.py:484-495). The only
reason the single-item CRUD path "works" today is that the frontend's room
picker smuggles the value through as an extra, undeclared payload field
(frontend/src/composables/useBuildingRoomDynamicOptions.ts:138,
form['room_surface_square_meter'] = match.room_surface_square_meter), which
DataEntryPayloadMixin.unflatten_payload
(backend/app/schemas/data_entry.py:37-48) happily carries into .data
unfiltered. CSV ingestion filters incoming columns to each handler's
declared DTO fields
(base_csv_provider.py:1227-1231, filtered_row = {k: v for k, v in
row.items() if k in expected_columns...}), so even a CSV with an extra
room_surface_square_meter column would never reach .data. Simply wiring
CSV rows into the existing helper unchanged would silently no-op forever
(every row hits the if room_surface_square_meter is None: return None
guard) — a second silent fallback stacked on the first. The shared logic
must resolve the surface itself via BuildingRoomService, the same call
pre_compute already makes, instead of trusting an upstream caller to have
passed it in.
2. Recommended approach¶
Core principle: one shared "how do I build a building_embodied_energy
payload from a building entry" function, invoked by two orchestration
shapes — synchronous single-item (existing) and async bulk-diff (new) —
mirroring the codebase's existing sync-vs-bulk split for emissions
themselves (DataEntryEmissionService.upsert_by_data_entry for single-item
vs EmissionRecalculationWorkflow for bulk/recalc). This is not new
duplication; it's the same shape the rest of the pipeline already uses.
2.1 Fix and extract the shared derivation logic (embodied_energy.py)¶
In backend/app/workflows/embodied_energy.py, replace
_make_building_embodied_energy_data with a session-aware version that
resolves the room surface itself:
async def _resolve_building_embodied_energy_data(
room_cache: dict[str, BuildingRoom] | None,
session: AsyncSession,
data_entry_id: int,
data: dict,
) -> dict | None:
building_name = data.get("building_name")
room_name = data.get("room_name")
if not building_name or not room_name:
return None
if room_cache is not None:
room = room_cache.get(room_name)
else:
room = await BuildingRoomService(session).get_room(room_name=room_name)
if room is None or room.room_surface_square_meter is None:
return None
return {
"data_entry_id": data_entry_id,
"building_name": building_name,
"room_surface_square_meter": room.room_surface_square_meter,
}
Make it a module-level function (or @staticmethod) so both the workflow
and the new bulk step import the exact same code — not a copy.
EmbodiedEnergyWorkflow._post_create_building/_post_update_building call
it (with room_cache=None, so it falls back to the direct get_room call —
no cache needed for a single room) in place of the old
.data.get("room_surface_square_meter") read.
_get_embodied_energy_entry_id (lines 174-185) is already pure
session/id-based lookup logic — reused as-is by the bulk step too (or given
a bulk sibling, see §2.2).
Once this ships, remove the now-redundant frontend autofill
(useBuildingRoomDynamicOptions.ts lines 44, 57, 138 setting
room_surface_square_meter on the form) — the backend resolves it, the
field was never meant to be client-supplied (guardrail: backend is the
single source of truth for every transform).
2.2 Bulk derived-entry creation — reuse #2121's placement, not its transform¶
Add EmbodiedEnergyWorkflow.create_derived_entries_for(parent_entries:
list[DataEntry]) -> int, taking the just-inserted building DataEntry
rows (already fetched with real ids — see below) and bulk-inserting their
building_embodied_energy derived entries:
- No diff/reconcile logic needed. #2121's delete-then-recreate strategy
(§0) means every call to this method only ever sees new parent rows —
cleanup of stale derived rows already happened via
_delete_existing_entries_for_module_per_year'sDERIVED_DATA_ENTRY_TYPESinclusion (kept as-is from #2121). So this is a straight map-and-bulk-insert, not a create/update/delete reconciliation — simpler than this plan's earlier draft. - Performance — no per-row
BuildingRoomService.get_roomcall. With ~20k rooms, one query perbuildingrow is exactly the ingest failure mode to avoid — #2121 already solved this for the CSV path with oneBuildingRoomRepository.list_rooms()call up front (building_room_service.py:29-38/building_room_repo.py:43-56— the whole reference table is the cache, no per-slice filtering needed). Keep that: build{room_name: BuildingRoom}once, pass it into_resolve_building_embodied_energy_data(§2.1) asroom_cacheso the shared resolver does an in-memory lookup instead of a query per row — same function, same fix, now used by both the interactive and bulk paths instead of #2121's bespokesurface_by_roomdict. (Optional follow-up, not bundled into #878:BuildingRoomModuleHandler. pre_computestill does oneget_roomcall per entry on everybuildingrecalc job, CSV-triggered or not — the sameprefetch_slicebulk-cache patternProfessionalTravelPlaneModuleHandleralready uses (professional_travel/handlers.py:127-164, wired throughEmissionRecalculationWorkflowatemission_recalculation.py:125) would fix that too, but it's a separate pre-existing N+1, unrelated to the CSV-ingest gap this issue reports — keep this fix minimal and self contained, file it separately if worth doing.) EmbodiedEnergyWorkflow(session).create_derived_entries_for(parents): builds the room cache, calls_resolve_building_embodied_energy_dataper parent, drops theNones (missing room/no ref-data — same "skip, don't default" semantic as the interactive path), and bulk-inserts viaDataEntryService.bulk_copy(data_entry_service.py:312-335— matches the file's existing bulk-insert idiom; no ids needed back from this insert, sobulk_copy's known id gap is a non-issue here).
2.3 Wire it into base_csv_provider.py (generic hook, not buildings-specific)¶
Keep #2121's placement (synchronous, tail of _finalize_and_commit) and its
emission_recalc fan-out change, but drive both off
DERIVED_DATA_ENTRY_TYPES instead of hardcoding "buildings" into the base
class, so BaseCSVProvider stays module-agnostic (only buildings has an
entry in that map today, but the base class shouldn't need to know that):
base_csv_provider.py,_finalize_and_commit: where #2121 added_create_embodied_energy_companions, instead add a small generic_create_derived_entriesthat — for eachderived_typeinDERIVED_DATA_ENTRY_TYPES.get(pinned_det, [])— fetches this job's newly written parent rows viaDataEntryRepository.list_by_creator_and_type(#2121's addition, kept as-is:created_by_id=self.job_id, data_entry_type_id=pinned_det) and delegates to the type-specific deriver. For now there's exactly one deriver (EmbodiedEnergyWorkflow.create_derived_entries_for, buildings only), so this can be a one-entrydict[DataEntryTypeEnum, Callable]map or a singleif derived_type == DataEntryTypeEnum.building_embodied_energybranch — whichever reads simpler; don't build a plugin registry for one caller (YAGNI).ingestion_tasks.py,_chain_emission_recalc_for_data_ingest: keep #2121's fan-out of a parallelemission_recalcsibling perDERIVED_DATA_ENTRY_TYPES.get(pinned, [])entry — already generic (reads the map, not a buildings check), no change needed there beyond what #2121 already did.
This only touches the buildings module in practice (the only key in
DERIVED_DATA_ENTRY_TYPES today) but doesn't hardcode that into the base
CSV provider — a second derived module type is a map entry + deriver
function, not a new branch in BaseCSVProvider.
2.4 Keep the single-item plumbing as-is¶
EmbodiedEnergyWorkflow.post_create/post_update/post_delete and their call
sites in carbon_report_module.py stay as the synchronous path — a user
creating one room in the UI still sees the derived embodied-energy entry
immediately in the same response, exactly like upsert_by_data_entry
computes that single entry's emissions synchronously while bulk CSV rows
wait for the async emission_recalc job. Two invocation shapes calling the
one shared _resolve_building_embodied_energy_data is the established
sync/bulk split, not competing implementations — no backward-compat
violation.
3. Critical files¶
Smaller footprint than the earlier job-chaining draft — no new job type, no
DedupConfig, no migration:
backend/app/workflows/embodied_energy.py— fix + extract_resolve_building_embodied_energy_data(§2.1, nowroom_cache-aware), addcreate_derived_entries_for(§2.2).backend/app/services/data_ingestion/base_csv_provider.py— replace #2121's_create_embodied_energy_companions/_embodied_energy_companionwith the generic_create_derived_entrieshook (§2.3) that calls intoEmbodiedEnergyWorkflow; drop theBuildingRoomRepositoryimport (no longer needed here — buildings-specific lookups move behind the workflow).backend/app/tasks/ingestion_tasks.py— keep #2121'sDERIVED_DATA_ENTRY_TYPESfan-out change in_chain_emission_recalc_for_data_ingestas-is.backend/app/models/module_type.py— keep #2121'sDERIVED_DATA_ENTRY_TYPESmap and its use in_delete_existing_entries_for_module_per_year'svalid_entry_typesas-is.backend/app/repositories/data_entry_repo.py— keep #2121'slist_by_creator_and_typeas-is.frontend/src/composables/useBuildingRoomDynamicOptions.ts— drop the now-redundantroom_surface_square_meterautofill once the backend resolves it itself.backend/tests/integration/services/data_ingestion/test_buildings_csv_pg.py— update the docstring (no longer "no CSV ingest path"); add the coverage listed in §4 (missing from #2121).
4. Verification¶
- New unit tests for
_resolve_building_embodied_energy_data(ref-data hit / miss / missingroom_name,room_cachehit vsNone-cache DB fallback) andcreate_derived_entries_for(bulk-insert shape, skip-on-missing semantics) — mirrorstests/unit/services/data_ingestion/test_module_per_year_csv_provider.py's style. None of this exists in #2121 — it shipped with zero tests. - Regression test pinning the room-lookup query count for a CSV ingest of N
buildingrows: assert onelist_rooms()call regardless of N, not O(N) — the concrete "20k rooms must not mean 20k queries" contract from this conversation. - Extend
test_buildings_csv_pg.py's existingdispatch_csv_and_waitchain-wiring test to assertbuilding_embodied_energyrows exist after a building CSV ingest with correctkg_co2eq; add a re-dispatch (reupload) case asserting stale derived rows are replaced, not duplicated (exercises #2121's delete-then-recreate path end to end). - Regression test for the frontend-smuggling bug: a
buildingentry created via a raw API payload (noroom_surface_square_meterfield, as a non-JS client would send) must now also get a correct derived entry — today it silently wouldn't, on either path. - Run
uv run pyteston the touched files locally;make ci(ruff + ty + vue-tsc) before pushing, per guardrails — notmake test-cov/full suite unless asked.
5. Path to landing¶
Decision: #2121 will not be merged. Reimplement from current dev
fresh — a new branch off dev (per the fix/pipeline-debug convention for
pipeline-chain work), not a checkout or rebase of #2121's branch. #2121
stays useful as prior art (§0 credits the structural decisions worth
repeating) but the code lands as new commits against current dev, so it
picks up whatever's landed there since #2121 was opened (2026-08-14) instead
of inheriting its branch point. Comment on #2121 linking this plan and
closing it in favor of the new PR, so BenBotros isn't left wondering why
their green PR went stale — referencing both #878 and #2121 from the new
PR's description.
Before writing code: diff current dev against #2121's four touched files
(module_type.py, data_entry_repo.py, base_csv_provider.py,
ingestion_tasks.py) to confirm none of them drifted since 2026-08-14 in a
way that changes §2's design (e.g. another PR touching
_delete_existing_entries_for_module_per_year or
_chain_emission_recalc_for_data_ingest in the meantime).