ARCHIVED: This plan was not delivered. Preserved for context.
📄 PRD 1 — Usage CSV (usage.csv)¶
1. Objective¶
Provide a dataset that tracks the status and progress of each module for every carbon report.
This dataset is intended for:
- monitoring completion
- identifying gaps in data collection
- operational dashboards
THE CSV results should look like this
report_id year unit_id module_name module_stat last_updated
2. Scope¶
- One row per (report_id × module_type_id)
- Covers all modules defined in
ModuleTypeEnum - Includes status and last update timestamp
3. Data Sources¶
carbon_reportscarbon_report_modules
4. Data Model¶
Granularity¶
- 1 row = 1 report × 1 module
Columns¶
| db column | csv Column | Type | Description |
|---|---|---|---|
| id | report_id | integer | carbon_reports.id |
| year | year | string | reporting year (2026) |
| unit_id | unit_institutional_id | string | unit identifier (cost center) |
| module_name | module_name | text | mapped from module_type_id |
| module_status | module_status | text | status of the module (cf *A) |
| last_updated | last_updated | timestamp | last update of the module (cf *B) |
- (*A) status of module is 0,1,2 but MUST display Not Started In Progress, Completed --> create an enum in DB or map to facilitate SQL)
-
(*B) date is "2026-03-25T16:23:54.601649+00:00" in stats.computed_at, so that's a problem we should have a 'last_updated' field as Date not null (currently it's null), and in the csv we should have a csv/excell compatible date format like: 2026-03-25
-
example of carbon_reports table
"year" "unit_id" "id" "last_updated" "stats" "completion_progress" "overall_status"
2025 2011 1 "{""scope1"": 0.0, ""scope2"": 0.0, ""scope3"": 96982.92000000001, ""total"": 96982.92000000001, ""by_emission_type"": {""10000"": 27750.0, ""20000"": 827.3199999999999, ""30000"": 68405.6}, ""computed_at"": ""2026-03-30T07:53:04.029432+00:00"", ""entry_count"": 8}" "0/8" 0
- example of carbon_report_modules table
"module_type_id" "status" "carbon_report_id" "id" "last_updated" "stats"
1 0 125 993 "{""scope1"": 0.0, ""scope2"": 0.0, ""scope3"": 81255.95999999999, ""total"": 81255.95999999999, ""by_emission_type"": {""10000"": 23250.0, ""20000"": 693.16, ""30000"": 57312.79999999999}, ""computed_at"": ""2026-03-25T16:23:54.601649+00:00"", ""entry_count"": 8}"
5. Mapping Logic¶
module_type_id → module_name¶
| ID | Name |
|---|---|
| 1 | headcount |
| 2 | professional_travel |
| 3 | buildings |
| 4 | equipment_electric_consumption |
| 5 | purchase |
| 6 | research_facilities |
| 7 | external_cloud_and_ai |
| 8 | process_emissions |
6. SQL Specification¶
SELECT
cr.id AS report_id,
cr.year,
cr.unit_id,
CASE crm.module_type_id
WHEN 1 THEN 'headcount'
WHEN 2 THEN 'professional_travel'
WHEN 3 THEN 'buildings'
WHEN 4 THEN 'equipment_electric_consumption'
WHEN 5 THEN 'purchase'
WHEN 6 THEN 'research_facilities'
WHEN 7 THEN 'external_cloud_and_ai'
WHEN 8 THEN 'process_emissions'
END AS module_name,
crm.status AS module_status,
crm.last_updated
FROM carbon_reports cr
JOIN carbon_report_modules crm
ON crm.carbon_report_id = cr.id
ORDER BY cr.id, crm.module_type_id;
7. Output¶
- File:
usage.csv - Format: CSV
- Sorted by
(report_id, module_type_id)
8. Constraints¶
- All modules must be included, even if status is NULL
- No aggregation
- No filtering by year unless specified externally
Improvements¶
- Replace
CASEwith amodule_type_mappingtable - don't forget about completion_progress, overall_status
- Add completion percentage per report
- Add derived status (e.g. completed / incomplete)