Schema View SQL Schema Learn about IO Matrix

Eight tables: industry and factor are shared reference tables (industry definitions, 721 environmental factors); sector and sector_industry map industries into the 21 BEA sector categories used by the interstate tables; trade and trade_factor hold international flows and their environmental coefficients per country/flow_type; interstate and interstate_factor hold US state-to-state (BEA) flows. Each database covers a single year, so these tables carry no year column. Each table's box shows column names and types.

A ninth table, region, supports loading more than one country into the same database: each country gets a small block_index, assigned the first time it's loaded, so trade_id stays unique across countries (not just within one country's own files) — see the team repo's PLAN-merge.md for the full design. trade.country has a real foreign key to region.country — every region row is created before any trade row referencing that country is inserted. It's created automatically (CREATE TABLE IF NOT EXISTS) the next time trade data is loaded into a database, so it may not exist yet in a database that hasn't had a load run since this table was added — it's not backfilled by any separate migration step.

The diagram's columns come from two sources: a hardcoded static schema (in this page's JS and mirrored in the Rust API) used as an instant preview, and the live database schema (queried from Postgres information_schema.columns) once the API responds. The two can drift out of sync — that's why a column can briefly appear then disappear (as happened with a leftover id column already removed from the live tables but not yet from the static fallbacks).

Indexes

Indexes aren't arbitrary — each one mirrors a real join or filter the app performs:

• Composite lookup indexes on (trade_id, country, flow_type) for trade and trade_factor — this is the exact compound key used to join those tables, since trade_id alone repeats across countries/flow types.
• Filter-column indexes on country, region1/region2 (trade), state1/state2 (interstate — US state codes, not Exiobase regions), and flow_type — these back the country/region/state/flow-type selections made in this page's UI (year selects which per-year database to query, not a column filter).
• Foreign-key indexes on factor_id (both trade_factor and interstate_factor → factor) and interstate_id (interstate_factor → interstate). interstate itself keeps trade_id — interstate_id's embedded industry is only a broad category, not the industry1/industry2 pair, so trade_id is the only path back to the originating international flow (its exact industry pair, amount, country, flow_type).
• Natural-key dedup constraints: trade on (region1, region2, industry1, industry2) — the true identity of a flow, so the same flow pulled from two different country-perspective CSV runs (e.g. "imports to US" and "exports from Canada" both capturing the same CA→US leg) collapses into one row instead of duplicating. interstate on (state1, state2, industry1, industry2) — including same-state (state1 == state2) rows, which represent genuine intrastate flow, not duplicates.
• No bigserial id anywhere now: each table uses its natural key as the real PRIMARY KEY directly — trade: region1, region2, industry1, industry2; trade_factor: trade_id, country, flow_type, factor_id; interstate: interstate_id alone; interstate_factor: (interstate_id, factor_id).

Loading asynchronously

The diagram used to wait on a fetch to the Rust API (/api/db/industry-schema) before drawing anything, so a slow or unresponsive database made the whole page feel stalled. Now it draws immediately using the static schema, then fetches the live schema in the background and re-renders — adding real row counts and the ⋮ per-table menu once that data arrives.