Skip to content

ESG Screening — Technical Protocol

This file is loaded at the start of every Claude session that touches the engineering stack. It is the technical companion to the session primer in the ESG screening project (which lives in Claude's project knowledge, not in this repo). The primer covers why and what is this; this file covers how.

Methodology spec versioning: * v0.4 behaviour — ESG_Screening_Framework_v0.4.xlsx (in data/seed/) is the spec. If anything here contradicts the workbook for v0.4 rules, the workbook wins. * v0.5 two-stage architecture — the spec is docs/design/DESIGN_NOTE_v2_two_stage.md plus migrations 003/004/005. A v0.5 workbook has not been produced yet; the design note and migrations are the authoritative record until one is. When those three contradict each other, ask Rob — don't pick a winner.

Scope: CC-on-VM only

This file is the protocol for Claude Code sessions running on the esg-screening-01 VM. Chat sessions on claude.ai follow the project instructions on the claude.ai project, not this file. The two are deliberately split: chat sessions can't reach the VM or the repo, so stack checks, file writes, migrations, and commits are CC-on-VM-only. Chat sessions produce artefacts (methodology, decisions, interpretive work on the workbook) and hand them over via #esg-screening.

A future CC-on-VM session reading this file should not apply rules from here to a chat context, or assume chat-session conventions apply on the VM. When in doubt about which lane a piece of work belongs in: if it touches the running app, the DB, the repo, or anything on the filesystem, it's CC-on-VM. Everything else is chat.

What this is, mechanically

A Node.js application that runs on esg-screening-01 (Azure VM, West Europe, Ubuntu 24.04). Every Sunday at 02:00 UTC, node-cron triggers a two-stage scoring run across all institutions in scope, then writes the result to a Google Sheet via the googleapis client. Internal users reach the application's web UI at https://esg-screen.org via Cloudflare Tunnel + Access (OTP gate, 5-address allow-list).

v0.5 architecture

Two-stage scoring model

Stage 1 — ESG screen, universal. Runs for every active institution regardless of sector. Input: company name + signal sources. Output: ESG score (0–100), confidence (0.0–1.0), peer-relative ranking within GICS sub-industry (falling back up the ladder), and universe-relative ranking.

Stage 2 — Combined screen, financials only. Runs only for institutions where institution_type = 'financial'. Input: Stage 1 ESG score + credit score + returns score. Output: composite score, propagated confidence, peer-relative ranking within financials. Stage 2 does not apply to non-financials even if they hold bonds — the triangulation thesis (ESG vs credit vs returns) is only coherent for banks.

GICS classification

Four code columns on institution plus a gics_classification lookup table:

gics_sector_code         CHAR(2)   -- e.g. '40' Financials
gics_industry_group_code CHAR(4)   -- e.g. '4010' Banks
gics_industry_code       CHAR(6)   -- e.g. '401010' Banks
gics_sub_industry_code   CHAR(8)   -- e.g. '40101010' Diversified Banks

Codes stored as TEXT (not INTEGER) to preserve leading zeros and simplify joins. The gics_classification table holds label text for each code and a parent_code FK for hierarchy traversal.

Rule applicability

rule.applicable_sectors (TEXT, added by migration 003) controls which institutions a rule applies to:

  • 'ALL' — universal; evaluated for every institution
  • '40' — financials only (default for all pre-v0.5 rules E1–E7, S1–S6, G1–G7)
  • '10,15,20,25,30,35,45,50,55,60' — all non-financials
  • A single code like '20' — sector-specific (e.g. Industrials only)

The rule evaluator filters: WHERE applicable_sectors = 'ALL' OR institution.gics_sector_code IN (split(applicable_sectors, ',')).

Rule ID conventions

Pattern Applies to Example
E1–E7, S1–S6, G1–G7 Financials only (applicable_sectors = '40') E1 Financed emissions
UN-{pillar}{n} All sectors (applicable_sectors = 'ALL') UN-G1 Sustainability reporting
NF-{pillar}{n} All non-financials (comma list of non-FI sector codes) NF-E1 Operational emissions
NF-{pillar}{n}-{SECTOR} Specific sector(s) NF-E5-IND Enabled emissions (Industrials)

Where {pillar} is E/S/G and {SECTOR} is a short sector label matching applicable_sectors.

Confidence model

Confidence is a real number 0.0–1.0 carried at every score level and propagated upward:

  • signal.confidence — set by the scraper. 1.0 = clean hit, 0.5 = ambiguous, 0.0 = no signal. Distinct from boolean_value (whether the rule passed).
  • score_sub_criterion.confidence — coverage-weighted: (signals_with_evidence / signals_applicable) * mean(signal.confidence).
  • score_pillar.confidence — weight-weighted mean of sub-criterion confidences within pillar.
  • score_stage1_esg.confidence — weighted mean of E/S/G pillar confidences.
  • score_stage2_composite.confidence — weight-weighted minimum of Stage 1 ESG / credit / returns confidences; absent components drop their weight and reduce output confidence proportionally.

Propagation rule implemented at src/scoring/confidence.js (to be created).

Peer group ladder

Evaluator walks up the GICS hierarchy until a peer group meets the minimum-n threshold stored in config:

Level Config key Default min-n
Sub-industry (8-char) peer_ladder_min_n_sub_industry 10
Industry (6-char) peer_ladder_min_n_industry 15
Industry group (4-char) peer_ladder_min_n_industry_group 25
Sector (2-char) — always used as fallback

score_stage1_esg.peer_group_label records which GICS level was actually used. peer_distribution holds per-run distribution snapshots (n, min, p10–p90, max, mean, stddev) keyed by (run_id, gics_level, gics_code, score_type) so historical rankings are reproducible.

Universe definition

Universe membership for a given scrape_run = institutions in that run where score_stage1_esg.confidence >= config.universe_confidence_threshold (default 0.5). score_stage1_esg.in_universe (bool) is set at scoring time. The threshold is a known placeholder; calibrate once real confidence distributions emerge from the pilot.

Score tables

Table Scope Replaces
score_stage1_esg All institutions score_composite (dropped in migration 003)
score_stage2_composite Financials only New in v0.5
score_pillar All institutions New in v0.5 (pillar-level rollup)
peer_distribution Per-run snapshots New in v0.5

Stack

Layer Choice
Runtime Node.js 22.x
Process mgr PM2 7.x
HTTP Express (TBD — keep it boring)
DB SQLite (via better-sqlite3)
Scheduling node-cron
Sheets out googleapis
Scraping undici (fetch), cheerio (HTML), pdf-parse (PDFs) — TBD per source
Edge / auth Cloudflare Tunnel + Access (external, not in repo)

Why SQLite: single-VM deployment, weekly write cadence, dataset measured in tens of thousands of rows even at 1,000 institutions. Postgres would be overkill and add an operational surface that doesn't earn its keep at this scale.

Repo layout

esg-screening/
├── .claude/
│   └── CLAUDE.md              this file
├── docs/
│   └── design/
│       └── DESIGN_NOTE_v2_two_stage.md   v0.5 architecture spec
├── src/
│   ├── server.js              Express app entry point
│   ├── db/
│   │   ├── migrations/        SQL migration files, numbered
│   │   └── migrate.js         migration runner
│   ├── scrapers/              one file per signal source
│   ├── scoring/               rule engine, blends, confidence
│   ├── sheets/                Google Sheets writer
│   ├── scheduler/             cron registration
│   └── lib/                   shared utilities
├── data/
│   └── esg.db                 SQLite database (gitignored)
├── ecosystem.config.cjs       PM2 process file
├── package.json
├── README.md
└── .gitignore

Database

Schema lives in src/db/migrations/. The initial migration (001_init.sql) creates the full v0.1 schema; subsequent changes are additive migrations, never edits to old ones. Migrations are applied in order at app start; the applied list is tracked in a schema_migrations table.

Conventions for reading the workbook into shape: * Reference tables (peer_group, signal_source, rule, exclusion, return_profile, blend_weight) are seeded from migration 002. * v0.5 additions — GICS classification, non-financial rules, pilot institutions — are seeded in migrations 003/004/005. * The 10 illustrative institutions from the workbook's D.1 tab are not seeded into production. They're useful for local dev / smoke tests, and live in data/seed/dev-fixtures/ for that purpose only. * signal, score_*, controversy, alert, scrape_run are operational tables, populated by the app.

Backups: nightly cron writes a gzipped SQLite copy to /var/backups/esg/, keeping 14 days. The DB file itself is small enough that the cost is trivial.

Scheduler

One Sunday job, registered with node-cron: 0 2 * * 0 UTC. The job:

  1. Opens a new scrape_run row, status running.
  2. For each active institution: for each applicable rule's signal sources (filtered by rule.applicable_sectors), calls the appropriate scraper. Records every attempted extraction in signal, even failures (with extraction_ok=0 and an error_message).
  3. Computes sub-criterion scores → pillar scores → Stage 1 ESG score per institution. Writes score_sub_criterion, score_pillar, score_stage1_esg. For financial institutions, additionally computes Stage 2 composite and writes score_stage2_composite. Computes peer_distribution snapshots for all active GICS levels.
  4. Detects new controversies and exclusion hits; writes them.
  5. Generates alerts: controversies, score changes ≥5 points, scrape failures.
  6. Writes the dashboard view of the latest run to the configured Google Sheet.
  7. Posts a digest to #esg-screening summarising the run.
  8. Closes the scrape_run row, status complete (or partial if any sources failed).

Idempotency: re-running the same Sunday's job should produce a new scrape_run row, not overwrite the previous one. History is queryable by run_id. Re-runs are common during development; production should be a single run per Sunday.

Scoring rules — what's in the DB vs what's in code

The rule catalogue (rule_id, sub_rule_id, points, signal source, rule_weight, confidence_tier, applicable_sectors) lives in the rule table, seeded from the workbook (v0.4 rules) and migration 004 (v0.5 non-financial rules).

The rule evaluation logic — i.e. "given a raw signal value, does sub-rule E1.3 pass?" — lives in code, in src/scoring/rules/. One file per rule, exporting an evaluate(signal) → boolean function. This split keeps the methodology (auditable, version-controlled) separate from the implementation (testable, version-controlled in git).

Aggregation rules (E7, S6, G5, G7 — "start at 5, deduct N per X in window") are implemented per-rule rather than as a generic aggregator. The savings from a generic implementation aren't worth the abstraction tax at four instances.

Confidence

Confidence is a real number 0.0–1.0 (not a percentage). See the full propagation model in the v0.5 architecture section above. src/scoring/confidence.js implements the aggregation functions (file to be created).

The old v0.1 model (integer 0–100, 100 * sources_ok / sources_total) was replaced by migration 003. Do not use the old model.

Authentication

There is no app-level auth. Cloudflare Access sits in front of every request. The app trusts the Cf-Access-Authenticated-User-Email header that Cloudflare injects on every authenticated request — and rejects any request that lacks it.

Practical implication: never expose the app on 0.0.0.0:3000 directly, even briefly. The tunnel binds to localhost:3000 and that's the only ingress.

Secrets

Secret Lives Notes
GitHub PAT (deploy / push) /root/.esg-gh-token (root only, 600) Short-lived during repo setup; shredded after use
Google Sheets service account /etc/esg/google-sa.json (root, 600) App reads via GOOGLE_APPLICATION_CREDENTIALS env var
Slack webhook (digest channel) /etc/esg/slack-webhook (root, 600) Posted to #esg-screening
Cloudflare API token (admin) not on the VM Lives in 1Password; only Rob needs it

No secrets in the repo, ever. No .env.example with placeholder values that look real. .gitignore covers .env, *.sqlite, *.db, data/*.db, /etc/esg/.

Operational checks

Before any change that affects the running app, run the stack health check (documented in the session primer). At minimum that means:

  • pm2 status — process should be online with no recent restarts
  • curl -I http://localhost:3000/health — should return 200
  • systemctl status cloudflared — should be active (running)
  • Most recent scrape_run row — status and finished_at look sane

If any of these are off, surface it before making changes.

Push-and-deploy

After any push, confirm on origin before declaring done:

git log origin/main -1 --format="%H %s"

Local-only commits have shipped stale handoffs before. Match the SHA in the output against the commit just made.

Two repos:

Repo GitHub Deploys to
App McMillanGrubb/esg-screening —
Ops site McMillanGrubb/esg-screen-ops ops.esg-screen.org via Cloudflare Pages (ADR-0006, ADR-0020)

Changes to CLAUDE.md, ADRs, or the universe page require a follow-on sync and separate esg-screen-ops commit (sync-claudemd-to-ops.js, sync-adr-to-ops.js, generate-universe-md.js). Pages renders committed markdown — no build-time fetch.

Pages deploy verification — run from the esg-screen-ops directory after pushing:

node scripts/verify-pages-deploy.js          # auto-detects ops origin/main HEAD
node scripts/verify-pages-deploy.js <sha>   # explicit SHA if needed

Without an explicit SHA the script reads the ops origin/main HEAD. Default failure mode: passing the esg-screening SHA instead of the ops SHA. Most cycles touch esg-screening first; the ops SHA is the second one committed. Symptoms: script reports "No deployment found for <sha>" and times out. Fix: re-run without a SHA argument, or pass the ops SHA explicitly.

Exit 0 = status=success confirmed; exit 1 = timeout or failure. Polls every 15 s, 5-min hard timeout.

Seed-backed scrapers

Some signal sources load their data from a manually-maintained JSON file in data/seed/ rather than making live HTTP requests. The is_seed_backed column on signal_source (added by migration 006) flags these.

Current seed-backed sources:

source_id Seed file Refresh cadence
NZBA-MEMBERS data/seed/nzba-members.json Quarterly or when NZBA announces material membership changes

The NZBA member list loads dynamically via a WordPress AJAX plugin on the UNEP FI website — no member data appears in the static HTML. Rather than maintaining a brittle AJAX scrape, the list is versioned in the seed file with retrieved_at and _source_method metadata. Update the file and bump retrieved_at when refreshing.

Confidence semantics for seed-backed scrapers: 1.0 on both found and confidently-not-found (the seed list is treated as authoritative either way). This is the same as a live scraper — the seed-backed nature is not visible at the signal level, only at the source level via is_seed_backed.

What's deliberately not here yet

  • Returns dimension implementation. The workbook keeps it indicative (Decision #17); v0 reads from the seeded return_profile table and maps each institution by its profile. No per-institution returns modelling.
  • Programmatic detection of fossil-fuel / weapons red flags. Methodology note: "for v0.4 they remain manual." Stored on institution; analyst sets at intake.
  • Multi-user features beyond the allow-list. There are 5 users; nobody needs per-user dashboards in v0.
  • Anything web-dashboard-y beyond what the Sheets output provides. Decision #24 — Google Sheets via API is the v0 frontend.
  • Materiality-varied pillar weights by sector. blend_weight has no institution_type scoping column; non-financials currently use the bank weights (E=0.40, S=0.30, G=0.30) as a v0.5 default. v0.6 work.
  • src/scoring/confidence.js — confidence propagation functions described in v0.5 architecture; to be implemented before the first live run.
  • signal_source rename: SBTI-DASHBOARD and SBTI-CORPORATE should both become SBTI-VALIDATED and SBTI-VALIDATED-CORP (or similar) in a future migration. The original spec called for a CSV source; it's actually an Excel file. The source ID names are now misleading but a rename requires updating all referencing rule rows — deferred to avoid mid-session churn.

What's separate from Vextor

ESG screening is not Vextor — different VM, repo, secrets, channel, DNS. Full separation rationale in the session primer.

Cycle-summary phrasing conventions

When a rule's applicable_sectors filter excludes institutions, phrase as: "rule X filters out institutions A, B, C (sector N) — applicable_sectors is 'M,...'" rather than "institutions A, B, C are outside rule X's scope." The latter is ambiguous between (a) institutions not in the universe and (b) institutions in the universe but outside the rule's sector filter. The former is unambiguous about institutions being in-universe.

When in doubt

  • For v0.4 rules and methodology: the workbook (ESG_Screening_Framework_v0.4.xlsx) is the spec.
  • For v0.5 two-stage architecture: docs/design/DESIGN_NOTE_v2_two_stage.md and migrations 003/004/005 are the spec (no v0.5 workbook yet).
  • #esg-screening is the live operational record.
  • This file is the technical protocol.
  • If those contradict, ask Rob — don't pick a winner.