ESG System

Documented design

• Operating facilities (the operational unit) belong to a business unit, an industry sector and an activity type through required foreign keys. The organizational structure is flat: there is no parent column, recursive query or tree encoding.
• A data entry belongs to one facility and one reporting period, and has a status. Its values are one row per entry and indicator, with a unique index on that pair, a unit and a JSON snapshot of the indicator at entry time.
• The indicator catalogue is organized as pillar, topic, indicator group and indicator. Indicators carry input and standard units, a data cycle and an input type (manual or formula), and link to activity types.
• Targets are indicator, unit, value and an effective date. Emission coefficients are versioned: a metric value belongs to an emission version, with partial unique indexes that ignore soft-deleted rows.
• Every entity shares a base with soft delete and creator, updater and deleter audit columns. A database check constraint caps attachment size.
• Schema synchronization is switched off in the data-source configuration, and changes ship as 22 migrations: an initial schema, mostly hand-written SQL migrations, and index changes.
• The pillar-to-indicator grid shown to users is assembled in the use case as display headers; it is not a database hierarchy.

Documented design

• A separate identity service issues tokens and holds users, roles, actions and resources. A permission key is a resource and an action, and a user's roles are resolved from the database, with a short-lived cache and explicit invalidation.
• The main service does not verify tokens itself: it asks the identity service to validate each token.
• A permission guard accepts a list of keys with OR semantics, or nested lists for AND. Permissions cover areas such as data entry, indicators, targets, coefficients, staff, settings and approval.
• Facilities are assigned to a user as a list of ids. Every permission a user's roles grant applies to all of that user's facilities; there is no per-role facility difference.
• Row-level scoping is applied to the data-entry list (a facility filter in the query, and an empty list returns nothing) and checked per record in create, update, detail, history, duplicate, delete, recalculate and the approval paths.
• Facility dropdowns are limited to facilities where the user holds the view permission. Approval permissions are checked against the entry's facility.

Documented design

• Master-data import (indicators, topics, targets, groups, facilities) reads the first sheet with SheetJS, maps columns by position or header, checks required fields and duplicates within the file, and resolves codes with batched lookups. Valid rows are written in one transaction, so the batch is all-or-nothing. The indicator import stops at the first validation error.
• Entry import reads a fixed template: a facility code and period at the top, then indicator values and extra values. It collects every error across the sections and returns them together, each with a section, row, column, field and message.
• The entry import writes nothing: it returns a preview, and the user saves through the normal workflow. It rejects a target entry that is not a draft.
• Legacy history import is limited to admins, runs as a background job with a job id that is polled, and keeps job state in Redis with an expiry. Rows are skipped, with a count and capped samples per reason, for unknown facilities or indicators, formula indicators, data-cycle mismatches and non-numeric values.
• Within a file, a repeated key keeps the last row, except for indicators that record an execution date, whose values are summed. An entry that already exists for a facility and period is skipped.
• Each legacy entry is written in steps (create, bulk upsert of values, formula values, status update). A bulk upsert is one transaction, but the steps are not wrapped in one. A later run finds entries left incomplete by an interrupted run, soft-deletes them and reports them.
• A command-line variant runs as a dry run by default. Supporting scripts verify imported values against the source and restore values from it. A value-scale fix is opt-in.
• Prometheus counters record import duration, rows and failed rows.

Documented design

• A data entry moves through seven states: DRAFT, PENDING_APPROVAL, APPROVED, MANAGER_REJECTED, ADMIN_REJECTED, EDIT_REQUESTED and EDIT_APPROVED. There is no separate publish state.
• Approval is single-level: a user with the manager approval permission approves or rejects a pending entry, and one with the admin approval permission can reject an approved entry. A rejection needs a reason and the affected indicators.
• A complete form moves from draft to pending approval automatically. An approved entry can have an edit requested, which an approver accepts, optionally narrowing the indicators, or rejects with a reason.
• A rejected or edit-approved entry is corrected by duplicating it into a new draft, where editing is limited to the affected indicators.
• Every transition writes a history record with an order, operator, operation, reason and status, and rejections and edit requests have their own records.
• Notifications cover creation, reminders, overdue warnings, pending approval, rejections and edit-request outcomes. They go in-app through an external queue service and by email through SMTP or a mail queue.
• Recipients are resolved by asking the identity service who holds a permission for a facility. A scheduled job reads per-setting day and hour values and notifies facilities with no entry or only a draft.
• The recipient lookup returns an empty list if the identity service call fails, and notification dispatch runs after the database write and only logs failures.

Documented design

• The report export loads approved entries for a period range, filters them by the user's export permission per facility, loads their values with a lean query, and groups indicators by data table into sheets.
• Each sheet has one row per facility and period, with a column per indicator. The workbook is built by a worker thread so the main event loop stays free.
• The report is a detail pivot, not a hierarchical roll-up: no SQL aggregation by business unit or sector was found. Year-to-date accumulation exists only inside the formula engine.
• Other exports (indicators, targets and master data) build workbooks in memory with SheetJS. A Prometheus histogram records report export duration.
• Report results are not cached.

Documented design

• About 2,000 lines outside specs, in a lexer, parser, resolver and evaluator, with no use of eval() or the Function constructor in the source.
• A domain-specific language: HS[] looks up emission factors, CT[] references an indicator of the same or an earlier period (with offsets), LK[] accumulates from the start of the year, and IF[] supports conditions. NCT, SUM_TNBC, DAY_TNBC and DAY_IN_MONTH are also documented.
• Formulas run in dependency waves (buildFormulaWaves), so a formula that reads another formula runs after its inputs.
• Lookups for referenced indicators are preloaded and cached per facility, which removed a per-formula query pattern.
• 6 spec files in the engine folder, plus specs for wave building and batch evaluation, cover the engine, resolver and caches.