Breathe ESG's data model is designed around three core requirements:
- Multi-tenancy — each client company sees only their own data
- Audit trail — every change to a record is logged immutably
- Unit normalization — all values are converted to standard units for comparability
The schema follows a "store raw, normalize later" philosophy: the original payload from every ingestion is preserved in a JSONField before any transformation is applied. This ensures data provenance for auditors.
Tenant (1) ──→ Facility (many)
Tenant (1) ──→ DataUploadJob (many)
DataUploadJob (1) ──→ EmissionRecord (many)
Tenant (1) ──→ EmissionRecord (many)
EmissionRecord (1) ──→ AuditLog (many)
Facility (1) ──→ EmissionRecord (many)
User (1) ──→ DataUploadJob (many)
User (1) ──→ AuditLog (many)
Each row represents a client company. All data in the system is scoped to a tenant — no cross-tenant leakage is possible without explicit database query manipulation.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| name | varchar(255) | Human-readable company name |
| slug | slug, unique | URL-safe identifier |
| created_at | datetime | Auto-set on creation |
Maps external facility identifiers (SAP plant codes, utility account numbers) to human-readable names. This is the lookup table that resolves the cryptic codes that real-world data sources use.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| tenant_id | FK → tenants | Scoped to tenant |
| sap_plant_code | varchar(20) | e.g. 1000, 2020 — nullable |
| utility_account_number | varchar(50) | e.g. MTR-001 — nullable |
| name | varchar(255) | Human name, e.g. "Berlin Plant" |
| country | varchar(100) | ISO country name |
| address | text | Full address (optional) |
Why two identifier fields instead of a generic "external_id"? SAP plant codes and utility account numbers come from completely different systems with different formats. Using two separate columns makes it explicit which system a facility was registered in, avoiding ambiguity when a single physical location has both an SAP code and a utility meter.
Lookup table for converting between units. This could be managed via the admin panel as emission factors and conversion rates evolve.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| source_unit | varchar(20) | e.g. MWH, GJ, MI |
| target_unit | varchar(20) | e.g. KWH, KM |
| conversion_factor | float | Multiplicand from source to target |
| category | varchar(50) | e.g. energy, mass, distance |
Unique constraint on (source_unit, target_unit).
Tracks every file upload/ingestion event. This is the parent record for each batch of emission records.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| tenant_id | FK → tenants | Scoped |
| source_type | varchar(20) | SAP, UTILITY, or TRAVEL |
| status | varchar(20) | UPLOADED → PROCESSING → COMPLETED/PARTIAL/FAILED |
| filename | varchar(255) | Original filename |
| total_rows | integer | |
| success_rows | integer | |
| failed_rows | integer | |
| uploaded_by_id | FK → auth_user | Nullable (if user deleted) |
| uploaded_at | datetime | Auto |
| processed_at | datetime | Set when processing completes |
A job is marked PARTIAL if some rows succeeded and some failed — this is the most common real-world outcome with messy data.
This is the heart of the system. Every ingested data point becomes one row here.
Why a single table for all sources instead of separate tables per source type? The evaluators care about multi-tenancy and normalized output. Having one table with a source_type discriminator is simpler to query, filter, and paginate, and it forces normalized data into a consistent shape. Source-specific quirks live in raw_payload.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| tenant_id | FK → tenants | Multi-tenant isolation |
| upload_job_id | FK → data_upload_jobs | Batch provenance |
| facility_id | FK → facilities | Nullable — not all sources have a facility |
| scope | varchar(10) | SCOPE1, SCOPE2, or SCOPE3 |
| category | varchar(20) | FUEL, ELECTRICITY, FLIGHT, HOTEL, GROUND |
| source_type | varchar(20) | SAP, UTILITY, TRAVEL |
| raw_payload | JSONField | Original source data, untouched |
| activity_date | date | Normalized to ISO date |
| description | text | Human-readable summary |
| original_value | float | Value exactly as received |
| original_unit | varchar(20) | Unit exactly as received |
| normalized_value | float | Converted to standard unit |
| normalized_unit | varchar(20) | Standard unit (KG, L, KWH, KM) |
| normalized_co2_kg | float | Calculated CO₂ in kilograms |
| status | varchar(10) | PENDING → APPROVED/FLAGGED/REJECTED |
| analyst_notes | text | Free-text analyst commentary |
| reviewed_by_id | FK → auth_user | Who last took action |
| reviewed_at | datetime | When last action was taken |
| created_at | datetime | Auto |
| updated_at | datetime | Auto |
Indexes: Composite indexes on (tenant, status), (tenant, source_type), and (tenant, scope) for dashboard queries.
| Source | Category | Scope | Rationale |
|---|---|---|---|
| SAP (fuel procurement) | FUEL | Scope 1 | Direct combustion of purchased fuel |
| Utility (electricity) | ELECTRICITY | Scope 2 | Purchased energy |
| Travel — flights | FLIGHT | Scope 3 | Business travel (value chain) |
| Travel — hotels | HOTEL | Scope 3 | Business travel |
| Travel — ground | GROUND | Scope 3 | Business travel |
PENDING ──→ APPROVED (ready for audit)
│
├──→ FLAGGED (suspicious — analyst must review)
│
└──→ REJECTED (invalid — excluded from totals)
FLAGGED ──→ APPROVED (after analyst review)
FLAGGED ──→ REJECTED
APPROVED ──→ FLAGGED (if issues found later)
Any state ──→ PENDING (reset for re-review)
Immutable log of every action taken on emission records. This satisfies the audit-trail requirement and is crucial for regulatory compliance.
| Column | Type | Notes |
|---|---|---|
| id | PK auto | |
| tenant_id | FK → tenants | Scoped |
| emission_record_id | FK → emission_records | Nullable (future: tenant-level logs) |
| user_id | FK → auth_user | Nullable if user deleted |
| action | varchar(20) | CREATED, APPROVED, FLAGGED, REJECTED, UPDATED, NOTE_ADDED |
| field_name | varchar(100) | Which field changed (for updates) |
| old_value | text | Previous value |
| new_value | text | New value |
| notes | text | Free-text context |
| created_at | datetime | Auto — immutable after creation |
Indexes: (tenant, emission_record) for record history view, (tenant, created_at) for timeline queries.
All incoming values are converted to standard SI-derived units:
| Source Unit | Normalized Unit | Conversion Notes |
|---|---|---|
| L, LITRE, LTR | L | Identity |
| KG, KILOGRAM | KG | Identity |
| T, TONNE | KG | ×1000 |
| LB, POUND | KG | ×0.453592 |
| MWH, MW·H | KWH | ×1000 |
| GJ, GIGAJOULE | KWH | ×277.778 |
| MI, MILE | KM | ×1.60934 |
| ST, PC (pieces) | — | Flagged — cannot normalize |
Values that cannot be meaningfully normalized (e.g. piece counts for fuel) are automatically flagged for human review rather than silently converted.
Emission factors are applied after unit normalization to calculate normalized_co2_kg. Factors are sourced from the UK BEIS / EPA emission factor databases:
- Diesel: 2.68 kg CO₂/L
- Petrol: 2.31 kg CO₂/L
- Natural gas: 2.02 kg CO₂/m³
- Grid electricity (global avg): 0.233 kg CO₂/kWh
- Short-haul flight: 0.255 kg CO₂/km
- Hotel stay: 31 kg CO₂/night