# custom-esg-indicators-tracing A Strapi 5 plugin for managing ESG (Environmental, Social, Governance) indicator data. It provides a complete data model for companies, indicators, measurement units, SDGs, time periods, and the associations between them. It also exposes a content-api for reading indicators and submitting values, with support for bulk Excel import and an auto-calculation formula engine. --- ## Overview The plugin models the full lifecycle of ESG data collection: 1. **Registry** — define *companies* and *indicator units* (units of measurement). 2. **Indicators** — define *indicators* (stable code + versioned parameters including SDG links, goal types, and optional auto-calculation formulas). Each indicator can optionally be applied globally to all companies via the `applyGlobally` flag. 3. **Associations** — link indicators to companies (`indicator-company`) for specific time ranges, then record actual measured values (`indicator-company-period-value`) per reporting *period*. 4. **SDG** — manage Sustainable Development Goal items and their sub-categories. --- ## Directory structure ``` custom-esg-indicators-tracing/ ├── package.json ├── README.md ├── admin/ │ ├── jsconfig.json │ └── src/ │ ├── index.js # Plugin registration + bootstrap injections + registerTrads │ ├── pluginId.js # PLUGIN_ID = 'custom-esg-indicators-tracing' │ ├── components/ │ │ ├── PluginIcon.jsx # Earth icon from @strapi/icons │ │ ├── Initializer.jsx # Sets plugin as ready │ │ ├── ErrorBoundary.jsx # Prevents admin crash on component errors │ │ ├── IndicatorCompanyHandler.jsx # (disabled) Edit-view injection for indicator-company │ │ └── IndicatorApplyGloballyModalWarning.jsx # Dialog warning when applyGlobally toggles │ ├── pages/ │ │ ├── App.jsx # Root router (HomePage + error fallback) │ │ └── HomePage.jsx # 3 SectionCards → Content Manager │ ├── shared/ │ │ └── constants.js # All content-type UIDs + type helpers │ ├── translations/ │ │ ├── en.json │ │ ├── it.json │ │ └── es.json │ └── utils/ │ └── getTranslation.js # Namespaces a key: `${PLUGIN_ID}.${id}` └── server/ ├── jsconfig.json └── src/ ├── index.js # Aggregates all server exports ├── register.js # Registers esg.indicator-version-item + document middlewares ├── bootstrap.js # No-op ├── destroy.js # No-op ├── config/ │ └── index.js # Empty config ├── content-types/ │ ├── index.js │ ├── company/schema.json │ ├── indicator/schema.json │ ├── indicator-company/schema.json │ ├── indicator-company-period-value/schema.json │ ├── indicator-period/schema.json │ ├── indicator-unit/schema.json │ ├── indicator-version/indicator-version-item.json # Component schema (not a content-type) │ ├── sdg/schema.json │ └── sdg-category/schema.json ├── controllers/ │ ├── index.js │ ├── company.js # getCompanies (respects user.companies filter) │ ├── indicator.js # getIndicators · valueUpdate · importValues · importPreview · importCommit │ └── period.js # getPeriods ├── services/ │ ├── index.js │ ├── company.js # Admin users see all; regular users see only assigned companies │ ├── indicator.js # Core business logic (~873 lines) │ └── period.js # Simple findMany sorted by sortOrder + startDate ├── routes/ │ ├── index.js │ ├── admin.js # Empty │ └── content-api.js # 7 authenticated routes ├── middlewares/ │ ├── index.js │ └── content-manager-validation-middleware/ │ └── index.js # Formula validation + applyGlobally post-save propagation ├── policies/ │ └── index.js # Empty └── shared/ └── constants.js # All content-type UIDs (mirrors admin/src/shared/constants.js) ``` --- ## Content-types All content-types are hidden from the Content-Type Builder (`content-type-builder.visible: false`). Visibility in the Content Manager is controlled per-schema. ### `plugin::custom-esg-indicators-tracing.company` Registry of companies tracked for ESG reporting. | Field | Type | Notes | |---------------|---------|-----------------------------------------------------------| | `slug` | uid | Auto-generated from `completeName` in `register.js`. Unique. | | `completeName`| string | Required. 2–200 chars. | | `shortName` | string | Optional. Up to 80 chars. | | `address` | string | Optional. | | `city` | string | Optional. | | `district` | string | Optional. | | `postalCode` | string | Optional. | | `country` | string | ISO 2-letter code. | | `latitude` | decimal | −90 to 90. | | `longitude` | decimal | −180 to 180. | | `sector` | string | Optional. | | `description` | text | Optional. | | `website` | string | Validated with `^(https?://).+$`. | | `email` | email | Optional. | | `phone` | string | Validated with `^\+?[0-9 ()\-]{6,20}$`. | i18n-enabled (none of the fields are localised — slug generation handles diacritics via NFD normalisation). ### `plugin::custom-esg-indicators-tracing.indicator-unit` Unit of measurement for indicators (e.g. `tCO2eq`, `MWh`, `%`). | Field | Type | Notes | |---------------|--------|-------------------| | `name` | string | Required. Unique. | | `description` | text | Optional. | ### `plugin::custom-esg-indicators-tracing.indicator` An ESG metric definition. Contains a stable numeric+string code, core attributes, and a repeatable `versions` component that stores time-bounded parameters. | Field | Type | Notes | |----------------|-----------|--------------------------------------------------------------| | `code` | string | Required. Unique (e.g. `E01`). | | `indicatorId` | integer | Required. Unique numeric identifier. | | `name` | string | Required. | | `abbreviation` | string | Optional. | | `unit` | relation | oneToOne → `indicator-unit`. Required. | | `isPositive` | boolean | Whether a higher value is considered better. | | `description` | text | Optional. | | `applyGlobally`| boolean | When toggled, the post-save middleware creates/disables `indicator-company` associations for **all** companies. | | `versions` | component | Repeatable `esg.indicator-version-item`. | #### Component: `esg.indicator-version-item` Registered programmatically in `register.js`. Represents a time-bounded set of parameters for one indicator version. | Field | Type | Notes | |------------------------------|-------------|--------------------------------------------------------------| | `periodFrom` | date | Required. Start of the validity window. | | `periodTo` | date | Optional. End of the validity window. | | `sdg` | relation | oneToMany → `sdg`. SDG goals linked to this version. | | `goalType` | enumeration | `NONE`, `FIXED`, `RELATIVE`, `THRESHOLD`. | | `minimumThreshold` | decimal | Visible when `goalType = THRESHOLD`. | | `maximumThreshold` | decimal | Visible when `goalType = THRESHOLD`. | | `fixedValue` | decimal | Visible when `goalType = FIXED`. | | `fixedText` | string | Visible when `goalType = FIXED`. | | `relativePercentage` | decimal | Visible when `goalType = RELATIVE`. | | `weight` | enumeration | `NONE`, `APPLY`, `NON_APPLY`. | | `notes` | text | Optional. | | `enableAutoCalculationFormula`| boolean | Enables formula field. | | `autoCalculationFormula` | string | Excel-like formula referencing other indicator codes (e.g. `E2/E13`, `(E20*1000)/E136`). Validated server-side by the validation middleware. | | `periodLabel` | string | Auto-set by `register.js` middleware. Read-only in CM. | ### `plugin::custom-esg-indicators-tracing.indicator-company` Association between an indicator and a company for a specific validity period. | Field | Type | Notes | |--------------|----------|----------------------------------------------| | `pairKey` | string | Auto-generated: `{company.name} - {indicator.code} - {periodFrom} - {periodTo}`. Unique. | | `company` | relation | manyToOne → `company`. Required. | | `indicator` | relation | manyToOne → `indicator`. Required. | | `periodFrom` | date | Required. | | `periodTo` | date | Optional. | | `isDisabled` | boolean | Soft-delete flag. | ### `plugin::custom-esg-indicators-tracing.indicator-company-period-value` Actual measured value for a specific (indicator-company, period) combination. | Field | Type | Notes | |--------------------|----------|-------------------------------------------------| | `pairKey` | string | Auto-generated composite key. Unique. | | `indicatorCompany` | relation | manyToOne → `indicator-company`. Required. | | `indicatorPeriod` | relation | manyToOne → `indicator-period`. Required. | | `value` | decimal | Required. Min: 0. | | `isDisabled` | boolean | Soft-delete flag. | ### `plugin::custom-esg-indicators-tracing.indicator-period` A named reporting period used to select the correct indicator versions. | Field | Type | Notes | |-------------|---------|----------------------------------------------------------| | `name` | string | Required. E.g. `Q1 2025`, `FY 2025`. | | `startDate` | date | Required. | | `endDate` | date | Required. | | `sortOrder` | integer | Used for sorting in the UI. Default: 0. | ### `plugin::custom-esg-indicators-tracing.sdg` A UN Sustainable Development Goal. | Field | Type | Notes | |----------------|----------|----------------------------------------------------| | `code` | string | Required. Unique. E.g. `SDG-01`. | | `title` | string | Required. | | `description` | text | Optional. | | `icon` | media | Single image. | | `displayLabel` | string | Auto-generated by `register.js`: `{code} - {title}`.| | `categories` | relation | oneToMany → `sdg-category` (inversedBy: `sdg`). | ### `plugin::custom-esg-indicators-tracing.sdg-category` A sub-category of a Sustainable Development Goal. | Field | Type | Notes | |---------------|----------|---------------------------------| | `name` | string | Required. | | `description` | text | Optional. | | `sdg` | relation | manyToOne → `sdg` (inversedBy: `categories`). | --- ## Server-side automation (`register.js`) The `register.js` lifecycle hook does two things: ### 1. Programmatic component registration The `esg.indicator-version-item` component is registered directly via `strapi.get('components').add(...)` because Strapi does not auto-scan component directories inside plugins. ### 2. Document middleware (auto-computed fields) A `strapi.documents.use(...)` middleware intercepts write operations (`create`, `createMany`, `update`, `updateMany`) and enriches data before persistence: | Entity | Auto-computed field | Logic | |--------------------|---------------------|------------------------------------------------------------------| | `company` | `slug` | NFD-normalised lowercase slug from `completeName`. Appends `-N` on collision. | | `sdg` | `displayLabel` | `{code} - {title}` | | `indicator` | `periodLabel` | `DD/MM/YYYY - DD/MM/YYYY` on each version item. | | `indicator-company`| `pairKey` | `{company.completeName} - {indicator.code} [ - periodFrom - periodTo]` | | `job-request` (KNIME) | `identifier`, `pairKey` | Set in the KNIME plugin's own `register.js`. | --- ## Content manager validation middleware The `content-manager-validation-middleware` must be explicitly listed in the global Strapi middleware stack in `config/middlewares.js`: ```js // config/middlewares.js module.exports = [ // ... other middlewares ... 'plugin::custom-esg-indicators-tracing.content-manager-validation-middleware', // ... ]; ``` Once active, it intercepts `POST`/`PUT` requests to the Content Manager edit view for the `indicator` content-type and: 1. **Pre-save formula validation**: For each `versions` item where `enableAutoCalculationFormula = true`, extracts referenced indicator codes from the formula (regex `[A-Z][0-9]+`) and verifies they exist in the database. Returns a `ValidationError` (HTTP 400) with per-field paths if any code is missing. 2. **Post-save `applyGlobally` propagation**: After a successful save, if `applyGlobally` was toggled: - **→ `true`**: Creates a new `indicator-company` association (or re-enables an existing disabled one) for every company in the database. - **→ `false`**: Sets `isDisabled = true` on all existing `indicator-company` records for that indicator. --- ## API routes All routes require authentication (`auth: {}`). Base path: `/api/custom-esg-indicators-tracing/`. | Method | Path | Handler | Description | |--------|---------------------------------------|----------------------------|--------------------------------------------------| | GET | `/periods` | `period.getPeriods` | Returns all periods sorted by `sortOrder` + `startDate`. | | GET | `/companies` | `company.getCompanies` | Returns companies accessible to the current user.| | GET | `/indicators` | `indicator.getIndicators` | Returns paginated indicators for a period+company.| | POST | `/indicators/value/update` | `indicator.valueUpdate` | Create or update a single indicator value. | | POST | `/indicators/value/import` | `indicator.importValues` | Legacy single-step Excel import (deprecated). | | POST | `/indicators/value/import/preview` | `indicator.importPreview` | Parse and validate an Excel file without writing.| | POST | `/indicators/value/import/commit` | `indicator.importCommit` | Write previously previewed values to the database.| ### `GET /indicators` query parameters | Param | Required | Description | |------------|----------|--------------------------------------------------------------------| | `period` | ✅ | `documentId` of the indicator period. | | `companyId`| optional | Filter by company `documentId`. | | `page` | optional | Page number (default: 1). | | `pageSize` | optional | Items per page (default: 25). | | `sdgCodes` | optional | Comma-separated SDG codes to filter by. | | `search` | optional | Full-text search on indicator `name`, `code`, `abbreviation`. | --- ## Service: `indicator` The main service (`server/src/services/indicator.js`, ~873 lines) implements: ### `getIndicators({ period, companyId, user, page, pageSize, sdgCodes, search })` 1. Resolves the period's `startDate`/`endDate`. 2. Queries `indicator-company` records filtered by company access rights and period overlap. 3. For each association, selects the active `indicator-version-item` whose `periodFrom`/`periodTo` overlaps the requested period (`_pickActiveVersion`). 4. Joins the corresponding `indicator-company-period-value` (if any). 5. **Auto-calculation**: If a version has `enableAutoCalculationFormula = true`, evaluates the formula by recursively resolving referenced indicator codes from the current result set. 6. Returns paginated results with full indicator metadata. ### `createValue({ indicatorCompanyId, value, period, user })` Creates or updates an `indicator-company-period-value` record. Enforces company-level access control. ### `importPreview({ file, period, companyId, user })` Parses an `.xlsx` file using the **xlsx** library. Validates rows against existing indicator codes and company access rights. Returns a preview array and a list of validation errors — **no data is written**. ### `importCommit({ file, period, companyId, user, confirmedCodes })` Re-parses the same file and writes values for the rows whose codes are in `confirmedCodes`. Rows with errors are skipped. --- ## Admin UI ### HomePage Three `SectionCard` components navigate to the Strapi Content Manager: | Section | Rows | |------------|----------------------------------------------------------------------| | Registry | Company (create/list), Indicator Unit (create/list) | | Indicators | Indicator (create/list), Indicator Company (create/list), Indicator Period (create/list) | | SDG | SDG (create/list), SDG Category (create/list) | ### Injected components (bootstrap) Two components are injected into the Content Manager **edit view** (`right-links` slot) via `cm.injectComponent`: | Component | Status | Purpose | |--------------------------------------|----------|----------------------------------------------------------------------| | `IndicatorCompanyHandler` | Disabled | Was intended to sync goal/weight fields in the edit view. Body currently commented out. | | `IndicatorApplyGloballyModalWarning` | Active | Shows a `Dialog` warning whenever the `applyGlobally` boolean is toggled on an `indicator` record, before the user saves. | Both components are wrapped in an `ErrorBoundary` to prevent crashes from propagating to the rest of the admin. ### Translations Translations are loaded via `registerTrads` in `admin/src/index.js`. All three locales are supported (35 keys each): | File | Locale | |-----------|---------| | `en.json` | English | | `it.json` | Italian | | `es.json` | Spanish | Translation key prefix: `custom-esg-indicators-tracing.*` --- ## Development ```bash # Build the plugin (required before running Strapi) npm run build # Watch mode (auto-rebuild on change) npm run watch ``` Run together with the main Strapi app: ```bash # From Backend/ npm run develop:all ``` Set `PLUGINS_DEBUG=1` to enable verbose console logging in `admin/src/index.js`. --- ## Known issues / TODOs | Area | Issue | |-----------------------------|----------------------------------------------------------------------------------| | `entityService` | Most server-side code uses `strapi.entityService` (Strapi v4 API). Consider migrating to `strapi.documents` for full Strapi v5 compatibility. | | `IndicatorCompanyHandler` | The component body is entirely commented out. Determine whether it can be removed or reactivated. | | In-memory pagination | `getIndicators` loads all `indicator-company` records and paginates in memory. For large datasets, replace with DB-level pagination. | | Excel import (legacy route) | `POST /indicators/value/import` is a legacy single-step route. Prefer the preview/commit two-step flow. | | `indicator-company-period-value` | Not exposed in the HomePage (no create/list buttons). Add if direct CM access is needed. |