ESG & Sustainability - ProKure Database Documentation

1. Overview

The ESG & Sustainability module enables organizations to track, measure, and report on environmental impact of their procurement activities. This is critical for BRSR (Business Responsibility and Sustainability Reporting) compliance in India and global sustainability reporting frameworks.

Scope 3

Emissions Tracking

ESG

Supplier Scoring

Carbon

Footprint Calc

7

Tables

                 Key Capabilities
                Scope 3 Emissions: Track indirect emissions from purchased goods, transportation, and upstream activities
Supplier ESG Scores: Rate vendors on Environmental, Social, and Governance parameters
Carbon Footprint: Calculate product-level and order-level carbon footprint
Sustainability Certifications: Track ISO 14001, B Corp, Fair Trade, and other certifications
ESG Targets: Set and track progress towards sustainability goals
BRSR/CDP Reporting: Generate reports for regulatory compliance

            

2. GHG Protocol Emission Scopes

The Greenhouse Gas Protocol classifies emissions into three scopes. ProKure focuses primarily on Scope 3 (supply chain emissions) which typically represents 70-90% of an organization's total carbon footprint.

Scope 1 - Direct

Emissions from owned/controlled sources

Company vehicles
On-site fuel combustion
Manufacturing processes
Refrigerant leaks

Scope 2 - Indirect Energy

Emissions from purchased energy

Purchased electricity
Purchased steam/heat
Purchased cooling
Grid electricity

Scope 3 - Value Chain

All other indirect emissions (ProKure focus)

Purchased goods & services
Transportation & logistics
Business travel
Waste disposal

Scope 3 Categories Tracked in ProKure
======================================

Category 1: Purchased Goods & Services
  └── Tracked via: PO line items × product carbon footprint

Category 4: Upstream Transportation
  └── Tracked via: ASN/GRN shipment data × distance × mode

Category 5: Waste Generated
  └── Tracked via: QC rejects × material type

Category 6: Business Travel
  └── Tracked via: Travel requests × distance × mode

Category 7: Employee Commuting
  └── Tracked via: Employee survey data (optional)

Category 9: Downstream Transportation
  └── Tracked via: Outbound shipments (if applicable)

3. Database Schema

The ESG module uses a dedicated esg schema in PostgreSQL with tables for supplier scoring, emissions tracking, certifications, and targets.

esg.supplier_esg_scores

Composite ESG scores for vendors based on assessments, certifications, and performance data.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
vendor_id UUID FK NOT NULL UNIQUE
vendor_name VARCHAR(200) — DENORM
vendor_code VARCHAR(50) — DENORM
overall_esg_score DECIMAL(5,2) NOT NULL — 0-100 composite
environmental_score DECIMAL(5,2) NOT NULL — 0-100
social_score DECIMAL(5,2) NOT NULL — 0-100
governance_score DECIMAL(5,2) NOT NULL — 0-100
environmental_breakdown JSONB — {"carbon": 85, "water": 70, "waste": 90, "biodiversity": 75}
social_breakdown JSONB — {"labor": 80, "safety": 95, "diversity": 70, "community": 85}
governance_breakdown JSONB — {"ethics": 90, "transparency": 85, "compliance": 95}
risk_level VARCHAR(10) NOT NULL — LOW, MEDIUM, HIGH, CRITICAL
data_quality_score DECIMAL(5,2) — confidence in the score
last_assessment_date DATE
next_assessment_due DATE
assessment_frequency VARCHAR(20) — ANNUAL, BIANNUAL, QUARTERLY
certifications_count INTEGER DEFAULT 0 — DENORM
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ

Indexes: (tenant_id, overall_esg_score), (vendor_id), (risk_level), (next_assessment_due)

esg.carbon_emissions

Aggregated carbon emissions by period, scope, and category for reporting.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
reporting_period_start DATE NOT NULL
reporting_period_end DATE NOT NULL
scope VARCHAR(10) NOT NULL — SCOPE_1, SCOPE_2, SCOPE_3
category VARCHAR(50) — GHG Protocol category (e.g., CAT_1_PURCHASED_GOODS)
category_name VARCHAR(200) — DENORM: human-readable name
emission_source VARCHAR(100) — specific source within category
co2e_tonnes DECIMAL(18,6) NOT NULL — CO2 equivalent in metric tonnes
co2_tonnes DECIMAL(18,6) — CO2 only
ch4_tonnes DECIMAL(18,6) — Methane
n2o_tonnes DECIMAL(18,6) — Nitrous oxide
other_ghg_tonnes DECIMAL(18,6) — HFCs, PFCs, SF6, NF3
activity_data DECIMAL(18,4) — quantity of activity (e.g., kWh, km, kg)
activity_unit VARCHAR(20) — unit of activity
emission_factor DECIMAL(18,6) — kg CO2e per unit
emission_factor_source VARCHAR(100) — IPCC, DEFRA, EPA, etc.
data_quality VARCHAR(20) — PRIMARY, SECONDARY, ESTIMATED
verification_status VARCHAR(20) — UNVERIFIED, INTERNAL, THIRD_PARTY
project_id UUID FK — if attributable to a project
department_id UUID FK — if attributable to a department
calculated_at TIMESTAMPTZ DEFAULT NOW()
notes TEXT

Indexes: (tenant_id, reporting_period_start, scope), (category), (project_id)

esg.sustainability_certifications

Vendor sustainability certifications and their validity.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
vendor_id UUID FK NOT NULL
vendor_name VARCHAR(200) — DENORM
certification_type VARCHAR(50) NOT NULL — ISO_14001, ISO_45001, B_CORP, FAIR_TRADE, FSC, LEED, etc.
certification_name VARCHAR(200) NOT NULL
certification_body VARCHAR(200) — issuing organization
certificate_number VARCHAR(100)
issue_date DATE NOT NULL
expiry_date DATE NOT NULL
scope_description TEXT — what the cert covers
verification_url VARCHAR(500) — link to verify
document_id UUID FK — uploaded certificate
status VARCHAR(20) NOT NULL — ACTIVE, EXPIRED, SUSPENDED, REVOKED
verified_by UUID FK
verified_at TIMESTAMPTZ
esg_category VARCHAR(20) — ENVIRONMENTAL, SOCIAL, GOVERNANCE
score_impact DECIMAL(5,2) — points added to ESG score
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ

Indexes: (tenant_id, vendor_id), (certification_type), (expiry_date, status)

esg.esg_assessments

Detailed ESG assessment records for vendors with questionnaire responses.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
vendor_id UUID FK NOT NULL
vendor_name VARCHAR(200) — DENORM
assessment_year INTEGER NOT NULL
assessment_type VARCHAR(30) NOT NULL — SELF_ASSESSMENT, ON_SITE_AUDIT, THIRD_PARTY, DOCUMENT_REVIEW
questionnaire_version VARCHAR(20)
questionnaire_responses JSONB NOT NULL — full Q&A responses
environmental_responses JSONB — subset for E
social_responses JSONB — subset for S
governance_responses JSONB — subset for G
calculated_scores JSONB NOT NULL — {"overall": 78, "e": 82, "s": 75, "g": 80}
findings TEXT
improvement_areas JSONB — list of areas needing improvement
corrective_actions JSONB — required actions
assessor_id UUID FK
assessor_name VARCHAR(150) — DENORM
assessment_date DATE NOT NULL
status VARCHAR(20) NOT NULL — DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED
approved_by UUID FK
approved_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ

Indexes: (tenant_id, vendor_id, assessment_year), (status), (assessment_date)

esg.scope3_tracking

Detailed Scope 3 emissions at transaction level (PO, shipment, travel).

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
scope3_category VARCHAR(30) NOT NULL — CAT_1 through CAT_15
category_name VARCHAR(100) — DENORM
source_type VARCHAR(30) NOT NULL — PO_ITEM, SHIPMENT, TRAVEL, WASTE, ASSET
source_id UUID NOT NULL
source_display_number VARCHAR(50) — DENORM: e.g., "PO-2026-00145"
vendor_id UUID FK
vendor_name VARCHAR(200) — DENORM
material_id UUID FK
material_name VARCHAR(200) — DENORM
activity_quantity DECIMAL(18,4) NOT NULL
activity_unit VARCHAR(20) NOT NULL
emission_factor_id UUID FK
emission_factor_value DECIMAL(18,6) NOT NULL
emission_factor_unit VARCHAR(30) — kg CO2e per activity unit
co2e_kg DECIMAL(18,6) NOT NULL — calculated emissions in kg
calculation_method VARCHAR(30) — SPEND_BASED, ACTIVITY_BASED, SUPPLIER_SPECIFIC
data_source VARCHAR(50) — where the data came from
transaction_date DATE NOT NULL
fiscal_year INTEGER NOT NULL
fiscal_quarter INTEGER
project_id UUID FK
department_id UUID FK
created_at TIMESTAMPTZ DEFAULT NOW()

Indexes: (tenant_id, scope3_category, fiscal_year), (source_type, source_id), (vendor_id), (transaction_date)

esg.esg_targets

Sustainability targets and progress tracking for net-zero goals.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
target_name VARCHAR(200) NOT NULL
target_description TEXT
target_type VARCHAR(30) NOT NULL — EMISSION_REDUCTION, RENEWABLE_ENERGY, WASTE_REDUCTION, WATER_REDUCTION, SUPPLIER_ESG
scope VARCHAR(10) — SCOPE_1, SCOPE_2, SCOPE_3, ALL
metric VARCHAR(50) NOT NULL — CO2E_TONNES, PERCENTAGE, COUNT
baseline_year INTEGER NOT NULL
baseline_value DECIMAL(18,4) NOT NULL
target_year INTEGER NOT NULL
target_value DECIMAL(18,4) NOT NULL
target_reduction_pct DECIMAL(5,2) — % reduction from baseline
current_value DECIMAL(18,4)
current_year INTEGER
progress_pct DECIMAL(5,2) — % progress towards target
on_track BOOLEAN — is progress sufficient
sbti_aligned BOOLEAN DEFAULT FALSE — Science Based Targets initiative
sbti_target_type VARCHAR(30) — 1.5C, WELL_BELOW_2C, 2C
status VARCHAR(20) NOT NULL — ACTIVE, ACHIEVED, MISSED, REVISED
owner_id UUID FK
owner_name VARCHAR(150) — DENORM
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ

Indexes: (tenant_id, target_type, status), (target_year), (scope)

esg.product_carbon_footprints

Carbon footprint data for products/materials used in Scope 3 calculations.

id UUID PK DEFAULT gen_random_uuid()
tenant_id UUID FK NOT NULL
material_id UUID FK NOT NULL
material_name VARCHAR(200) — DENORM
material_code VARCHAR(50) — DENORM
category_id UUID FK
category_name VARCHAR(200) — DENORM
co2e_per_unit DECIMAL(18,6) NOT NULL — kg CO2e per UOM
unit_of_measure VARCHAR(20) NOT NULL
emission_factor_source VARCHAR(100) NOT NULL — ECOINVENT, DEFRA, EPA, SUPPLIER_PROVIDED, ESTIMATED
emission_factor_year INTEGER
lifecycle_stage VARCHAR(30) — CRADLE_TO_GATE, CRADLE_TO_GRAVE, GATE_TO_GATE
includes_transport BOOLEAN DEFAULT FALSE
includes_packaging BOOLEAN DEFAULT FALSE
data_quality_rating VARCHAR(10) — HIGH, MEDIUM, LOW, ESTIMATE
supplier_specific BOOLEAN DEFAULT FALSE
vendor_id UUID FK — if supplier-specific
valid_from DATE NOT NULL
valid_until DATE
metadata JSONB — additional LCA data
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ

Indexes: (tenant_id, material_id), (category_id), (emission_factor_source), (valid_from)

4. Carbon Calculation Flow

ProKure calculates Scope 3 emissions automatically when POs are released or GRNs are recorded.

Carbon Footprint Calculation Flow
==================================

PO Released / GRN Created
         |
         v
For each line item:
    |
    +-- Get material_id from PO/GRN item
    |
    +-- Lookup product_carbon_footprints
    |       |
    |       +-- If supplier-specific exists --> Use vendor-specific factor
    |       |
    |       +-- Else --> Use category average factor
    |       |
    |       +-- If none --> Use spend-based estimation
    |
    +-- Calculate: quantity × co2e_per_unit = emissions_kg
    |
    +-- Store in scope3_tracking (CAT_1: Purchased Goods)
    |
    v
If shipment data available:
    |
    +-- Get distance and transport mode from ASN/GRN
    |
    +-- Apply transport emission factor (kg CO2e per tonne-km)
    |
    +-- Store in scope3_tracking (CAT_4: Upstream Transport)
    |
    v
Aggregate to carbon_emissions (monthly/quarterly)

Calculation Methods

Method	Accuracy	Data Required	Use Case
Supplier-Specific	HIGH	Vendor-provided LCA data	Strategic suppliers with ESG maturity
Activity-Based	MEDIUM	Product quantities, emission factors	Standard products with known factors
Spend-Based	LOW	Purchase amount, industry averages	Services, low-spend categories

5. Supplier ESG Scoring Model

Vendors are scored on Environmental, Social, and Governance factors using a weighted model.

ESG Score Calculation

Overall ESG Score = (E × 0.40) + (S × 0.35) + (G × 0.25)

Where:
  E = Environmental Score (0-100)
      = (Carbon × 0.30) + (Water × 0.25) + (Waste × 0.25) + (Biodiversity × 0.20)

  S = Social Score (0-100)
      = (Labor × 0.35) + (Safety × 0.30) + (Diversity × 0.20) + (Community × 0.15)

  G = Governance Score (0-100)
      = (Ethics × 0.35) + (Transparency × 0.35) + (Compliance × 0.30)

Certification Bonuses:
  ISO 14001:        +5 to Environmental
  ISO 45001:        +5 to Social (Safety)
  B Corp:           +10 to Overall
  Fair Trade:       +5 to Social

Risk Classification

ESG Score	Risk Level	Action Required
80-100	LOW	Preferred supplier, promote
60-79	MEDIUM	Standard monitoring, annual review
40-59	HIGH	Improvement plan required
0-39	CRITICAL	Consider phase-out, remediation needed

6. MongoDB Audit Collection

MongoDB collection for ESG calculation audit logs and carbon tracking history.

esg_audit_logs / carbon_calculation_logs

Audit trail for all ESG score changes and carbon calculations.

_id ObjectId PK
tenant_id UUID indexed
log_type String — ESG_SCORE_CHANGE, CARBON_CALC, CERTIFICATION_ADDED, TARGET_UPDATE
entity_type String — VENDOR, PRODUCT, PO, PROJECT
entity_id UUID indexed
entity_name String
previous_value Object — before state
new_value Object — after state
change_reason String
calculation_details Object — full calculation breakdown
emission_factors_used Array — list of factors applied
data_sources Array — where data came from
user_id UUID
user_name String
timestamp Date indexed
ttl_expiry Date — 7-year retention for compliance

7. API Endpoints

RESTful API endpoints for ESG management.

Method	Endpoint	Description
GET	/api/esg/vendors/{id}/score	Get vendor ESG score
GET	/api/esg/vendors	List vendors with ESG scores
POST	/api/esg/assessments	Create ESG assessment
GET	/api/esg/certifications	List certifications
POST	/api/esg/certifications	Add certification
GET	/api/esg/carbon/summary	Get carbon emissions summary
GET	/api/esg/carbon/by-scope	Emissions by scope
GET	/api/esg/carbon/by-category	Scope 3 by category
GET	/api/esg/targets	List sustainability targets
POST	/api/esg/targets	Create target
GET	/api/esg/reports/brsr	Generate BRSR report
GET	/api/esg/reports/cdp	Generate CDP response
GET	/api/esg/products/{id}/carbon	Get product carbon footprint
POST	/api/esg/products/carbon	Set product carbon footprint

8. Business Rules

Core business rules for ESG and sustainability management.

                 BR-ESG-001
                All new vendors must complete ESG self-assessment questionnaire during onboarding

                 BR-ESG-002
                Critical risk vendors (ESG score < 40) require management approval for new POs

                 BR-ESG-003
                Expiring certifications trigger alerts 60 days before expiry

                 BR-ESG-004
                Carbon emissions must be calculated for all POs above configurable threshold

                 BR-ESG-005
                Strategic suppliers (>5% of spend) require annual on-site ESG audits

                 BR-ESG-006
                ESG scores impact vendor recommendation ranking (+10% weight for high ESG vendors)

                 BR-ESG-007
                Quarterly ESG dashboard review required for management

                 BR-ESG-008
                Product carbon footprint must use supplier-specific data when available (data quality priority)

9. Background Jobs

Scheduled jobs for ESG data processing and reporting.

Job	Schedule	Action
Calculate PO Carbon	On PO Release (event)	Calculate Scope 3 emissions for PO items
Aggregate Emissions	Daily (2 AM)	Rollup scope3_tracking to carbon_emissions
Recalculate ESG Scores	Monthly (1st)	Recalculate all vendor ESG scores
Certification Expiry Check	Daily (8 AM)	Alert on expiring certifications
ESG Assessment Reminder	Monthly (15th)	Remind vendors due for assessment
Update Target Progress	Weekly (Monday)	Calculate progress towards targets
Generate BRSR Report	Quarterly	Prepare BRSR section data
Sync Emission Factors	Quarterly	Update emission factor database

ESG & Sustainability Logic