Fast ecoinvent LCA lookup with CockroachDB and Brightway

This repository demonstrates and measures a hybrid SQL-to-Brightway calculation workflow. It contains standard Python scripts and integration tests; it does not yet include a web dashboard, API server, or persistent calculation worker.

Why ecoinvent is decoupled

A live dashboard should not scan a local Brightway database or rebuild its calculation state for every request. This project therefore separates the ecoinvent workflow into two cooperating parts:

Dashboard/API request
        |
        v
CockroachDB: indexed catalogue, stable UUIDs, mappings, provenance
        |
        v
Local calculation worker: Brightway matrices, LCI and LCIA

CockroachDB is the searchable and shareable catalogue. It resolves inputs to exact ecoSpold activity and reference-product UUIDs. Brightway remains local to the calculation environment and performs the matrix-based inventory and impact assessment. Large licensed matrices therefore do not need to be transferred over the network for every calculation.

This separation provides:

• one indexed catalogue that multiple applications can query;
• stable UUID-based mappings instead of repeated fuzzy activity searches;
• a small, controlled interface between an application and licensed local Brightway data;
• the option to keep calculation workers and matrices loaded between requests;
• independent scaling of SQL, application, and calculation resources; and
• auditable mappings that fail when SQL and Brightway metadata disagree.

An optimized local batch pipeline can calculate an inventory once before applying multiple LCIA methods. This repository preserves that numerical principle while adding an indexed SQL boundary for activity discovery and identity resolution.

CockroachDB does not make Brightway's matrix calculations intrinsically faster. The production benefit comes from removing catalogue scans, batching SQL lookups, keeping calculation workers warm, and avoiding repeated initialization.

The SQL representation is both useful and lossless:

• activity_datasets, exchanges, and parameters expose the main LCI data;
• master_data_entries exposes ecoinvent MasterData records as JSONB;
• filename_activity_lookup mirrors the supplied filename lookup; and
• source_documents stores each original file compressed with zlib, allowing the licensed source to be reconstructed.

Deploy the database to CockroachDB

Prerequisites

You need:

• Python 3.10 or newer;
• a licensed ecoSpold2 ecoinvent export;
• a CockroachDB cluster and SQL user; and
• the CockroachDB connection string.

The ecoinvent data is licensed. Keep the CockroachDB organization, cluster, users, backups, and connection string private, and confirm that your licence permits storage in a managed cloud service.

This public repository does not contain ecoSpold source files, exchanges, inventory matrices, characterization data, or a database export. The included test mapping contains dataset-documentation identifiers and metadata only. The checked-in JSON result is a static, aggregated calculation report and does not expose the underlying licensed inventory.

Data provider attribution: the example calculation uses the ecoinvent 3.12 cutoff database provided by the ecoinvent Association. Review the ecoinvent EULA before using the importer or calculations. Do not expose calculated results dynamically to third parties or deploy this code as a public calculation service unless the applicable ecoinvent licence explicitly permits software integration and distribution.

1. Install the SQL tooling

Create a virtual environment and install the database-import dependencies:

py -3.12 -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

requirements.txt contains the packages needed to parse the ecoSpold files and communicate with CockroachDB. Brightway dependencies are installed separately in the calculation environment.

2. Configure the private connection string

Copy .env.example to .env, then replace the placeholder with the connection string supplied by CockroachDB:

Copy-Item .env.example .env
notepad .env

3. Inspect the source export

Use stats to check the source and estimate its size before uploading:

python -m ecoinvent_sql stats `
  --source "C:\path\to\ecoinvent_ecoSpold02"

Use --no-compression for a faster count-only scan.

4. Create the database and schema

python -m ecoinvent_sql init

This creates the ecoinvent database and applies schema.sql.

5. Import ecoinvent

python -m ecoinvent_sql import `
  --source "C:\path\to\ecoinvent_ecoSpold02"

The importer commits new activity datasets in bounded transactions. It hashes every file and skips unchanged documents, so the command can safely resume an interrupted import.

Before a full upload, run a small real-data smoke test:

python -m ecoinvent_sql import `
  --source "C:\path\to\ecoinvent_ecoSpold02" `
  --limit 3 `
  --progress-every 1

Useful options:

--no-raw          omit compressed XML/CSV (smaller, but not lossless)
--force           reload files even when their SHA-256 hash is unchanged
--limit N         import only the first N source files
--batch-size N    new datasets per COPY transaction (default 100)
--stop-on-error   stop at the first malformed or rejected document
--progress-every  progress interval in files (default 100)

6. Verify the deployment

python -m ecoinvent_sql verify `
  --source "C:\path\to\ecoinvent_ecoSpold02"

The command compares the cloud activity count with the local source and prints sample activities and row counts for the imported tables.

Example SQL queries:

SELECT *
FROM ecoinvent.activities
WHERE activity_uuid = '00000000-0000-0000-0000-000000000000'::UUID;

SELECT activity_name, location, reference_product_name, unit_name
FROM activity_datasets
WHERE activity_name ILIKE '%search term%'
ORDER BY location;

SELECT e.exchange_name, e.amount, e.unit_name, e.exchange_type
FROM activity_datasets AS a
JOIN exchanges AS e USING (dataset_id)
WHERE a.activity_name = 'activity name'
  AND a.location = 'location code'
ORDER BY e.exchange_type, e.exchange_name;

Set explicit cluster resource limits, monitor storage and request usage during the initial import, and review the compressed-source estimate before choosing whether to use --no-raw.

Set up the local Brightway calculation environment

Use the Python or Conda environment that owns the local Brightway project and ecoinvent database. Install the calculation dependencies into that environment:

conda activate your-brightway-environment
conda install -c conda-forge pypardiso
python -m pip install -r requirements-brightway.txt

requirements-brightway.txt includes the SQL requirements plus bw2data and bw2calc. The project assumes that the licensed ecoinvent database and the required LCIA methods have already been imported into Brightway.

On Windows, keep the Conda environment activated when running calculations. Starting its python.exe without activation can omit the DLL paths required by the MKL/PARDISO sparse solver.

The command-line defaults are:

Brightway project:  e312bio-cutoff
Brightway database: ecoinvent-3.12-cutoff

Use --brightway-project and --brightway-database to select different local names. List available climate-change methods with:

python -m ecoinvent_sql methods --contains "climate change"

CLA farm upstream integration test

The integration test is a reusable example of the complete batch pipeline. It reads a CSV manifest containing input labels, exact activity and reference-product UUIDs, expected metadata, and calculation amounts. The included sample contains six agricultural inputs, but another compatible CSV can be supplied with --inputs.

Each sample demand is 1 in the selected ecoinvent activity's native reference unit. This means that a kilogram-based activity is evaluated at 1 kg, while an energy-based activity is evaluated at 1 MJ. The values are independent unit tests and must not be summed as a farm footprint when their functional units differ.

Run the complete integration test from the activated Brightway environment:

python -m tests.cla_upstream_pipeline

To run another compatible manifest and choose a different report location:

python -m tests.cla_upstream_pipeline `
  --inputs "C:\path\to\upstream_inputs.csv" `
  --output "C:\path\to\lca_results.json"

The pipeline:

1. loads and validates the input manifest;
2. resolves all UUID pairs in one indexed CockroachDB query;
3. verifies the SQL metadata;
4. maps the UUID pairs directly to local Brightway nodes;
5. calculates LCI and LCIA for each distinct demand; and
6. writes the scores, method metadata, and stage timings to JSON.

The default output is tests/outputs/cla_upstream_lca_results.json. The pipeline creates one LCA object. A distinct input requires a distinct inventory, so later inputs recalculate the inventory while reusing the loaded matrices and solver setup.

The recorded runtime should be interpreted with the test environment below:

Component	Tested configuration
CPU	12th Gen Intel Core i7-12700H, 14 physical cores and 20 logical processors
RAM	31.68 GiB installed (nominally 32 GB)
Storage	WD PC SN810 SDCPNRY-1T00-1006, 954 GiB NVMe
Operating system	Microsoft Windows 11 Home, 64-bit, version 10.0.26200 (build 26200)
Python	3.11.14 in Conda environment env_bw25
Brightway	bw2data 4.0.dev56, bw2calc 2.0.dev22, bw2io 0.9.dev38
Numerical stack	numpy 1.26.4, scipy 1.17.1, pypardiso 0.4.7
SQL driver	psycopg 3.3.4
CockroachDB database	ecoinvent
Brightway project	e312bio-cutoff
Brightway database	ecoinvent-3.12-cutoff
LCIA method	ecoinvent 3.12, IPCC 2013 climate change, GWP100
Test workload	Six distinct activity demands, each set to 1 in its native reference unit

One cold development run recorded:

Pipeline stage	Runtime
Load input manifest	0.002 s
Open CockroachDB connection	0.570 s
Resolve all UUID pairs in one SQL query	0.039 s
SQL retry wait	0.000 s
Load Brightway project and database	4.875 s
Resolve local Brightway nodes	0.004 s
First LCI and LCIA	1.546 s
Remaining LCI and LCIA calculations	0.210 s
Write JSON output	0.001 s
End to end	7.259 s

These timings are workload- and environment-specific. In production, connection pooling removes most connection setup and a resident worker removes the Brightway cold-load cost from normal application requests.

Select another LCIA method

List the exact methods installed in the local Brightway project:

python -m ecoinvent_sql methods --contains "climate change"

Pass a complete method tuple joined with |:

python -m tests.cla_upstream_pipeline `
  --method "database|method family|impact category|method name"

The selected method is validated before calculation and recorded with its score unit in the JSON report. An unavailable or misspelled method fails explicitly instead of falling back silently.

Temporary CockroachDB failures

Connection-class errors, serialization failures, administrative restarts, and temporary connection refusal are retried up to three times by default. The delay starts at 0.5 seconds and doubles after each failure. Each retry is reported on standard error, and the JSON timings record the number of attempts and total retry delay.

Configure the bounded retry policy when needed:

python -m tests.cla_upstream_pipeline `
  --sql-max-attempts 5 `
  --sql-retry-base-seconds 1

After the final attempt, the error identifies the failed SQL phase and suggests checking cluster availability, DNS or network access, the IP allowlist, SSL settings, and the configured database URL. Data-quality failures and permanent SQL errors, including authentication failures, are not retried.

Hybrid service usage

Search the SQL catalogue for activities:

python -m ecoinvent_sql activities "search term" `
  --location "location code" `
  --unit "reference unit" `
  --limit 10

Calculate a demand by passing its SQL activity and reference-product UUIDs:

python -m ecoinvent_sql lca `
  ACTIVITY_UUID `
  --reference-product-uuid REFERENCE_PRODUCT_UUID `
  --amount 1 `
  --method "database|method family|impact category|method name"

For application code, HybridLCAService in ecoinvent_sql.brightway exposes the same two operations:

• search_activities(...) queries CockroachDB; and
• calculate(...) closes the cloud connection before running the CPU-heavy local Brightway calculation.

Keep one service instance per calculation worker. It reuses Brightway's constructed matrices and sparse-solver factorization across requests. Brightway calculation access is serialized inside a worker because its active project and numerical solver are process-global.

The identity bridge is:

(activity UUID, reference-product UUID) -> Brightway activity code

Before calculating, it verifies the Brightway node's name, product, location, and unit against the SQL record. A mismatch fails explicitly instead of silently selecting a similarly named activity.

Dashboard improvements

The following improvements require a persistent dashboard or service runtime and cannot be implemented or meaningfully validated in the current standalone Python scripts:

• separate the web/API process from dedicated Brightway calculation workers;
• keep Brightway workers resident and warm between independent user requests;
• run a controlled multi-process worker pool instead of a single batch process;
• maintain and validate a CockroachDB connection pool across requests;
• route long calculations through an asynchronous job queue with status, cancellation, and timeout handling;
• add authentication, authorization, rate limits, and per-user calculation limits;
• manage deployment secrets through the hosting platform and restrict CockroachDB access through private networking or an explicit allowlist;
• expose health checks for the API, worker, SQL catalogue, Brightway project, database version, and installed LCIA methods;
• collect request-level latency, queue-time, error-rate, and worker-utilization metrics; and
• perform concurrent-user load testing in the intended deployment region.

The current scripts focus on calculation correctness, dynamic inputs and methods, batch efficiency, identity validation, and reproducible runtime measurements.