settled-reach/docs/workshops/commodity-catalog/schema-final.md

Commodity Catalog — Schema Specification

Definitive schema for #804 (systems.db extension), TOML field spec, and validation rules.

Decisions: D-173, D-177, D-178, D-182

---

1. SQL Schema (for #804)

-- =========================================================================
-- Compiled from wiki/economics/commodities.toml
-- =========================================================================

CREATE TABLE commodities (
    commodity_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    tier TEXT NOT NULL CHECK(tier IN (
        'raw', 'intermediate', 'final',
        'service_professional', 'service_luxury'
    )),
    elasticity TEXT NOT NULL CHECK(elasticity IN (
        'perfectly_inelastic', 'inelastic', 'unit_elastic', 'elastic'
    )),
    base_price REAL NOT NULL CHECK(base_price > 0),
    bulk_class TEXT NOT NULL CHECK(bulk_class IN (
        'bulk', 'liquid', 'perishable', 'standard', 'compact',
        'precision', 'oversized', 'non_physical'
    )),
    unit TEXT NOT NULL CHECK(unit IN ('tonnes', 'units', 'contracts')),
    production_ubiquity TEXT NOT NULL CHECK(production_ubiquity IN (
        'ubiquitous', 'common', 'regional', 'concentrated', 'monopolistic'
    )),
    demand_model TEXT NOT NULL CHECK(demand_model IN (
        'market', 'utility', 'compliance'
    )),
    commission_certifiable INTEGER NOT NULL DEFAULT 0,
    compact_contested INTEGER NOT NULL DEFAULT 0,
    shadow_viable INTEGER NOT NULL DEFAULT 0,
    panic_threshold_weeks INTEGER NOT NULL DEFAULT 0 CHECK(panic_threshold_weeks >= 0),
    description TEXT
);

-- =========================================================================
-- Compiled from wiki/economics/production_chains.toml
-- =========================================================================

CREATE TABLE production_chains (
    chain_id TEXT PRIMARY KEY,
    output_commodity TEXT NOT NULL REFERENCES commodities(commodity_id),
    output_quantity REAL NOT NULL DEFAULT 1.0 CHECK(output_quantity > 0),
    location_bound INTEGER NOT NULL DEFAULT 0,
    description TEXT
);

CREATE TABLE chain_inputs (
    chain_id TEXT NOT NULL REFERENCES production_chains(chain_id) ON DELETE CASCADE,
    commodity_id TEXT NOT NULL REFERENCES commodities(commodity_id),
    quantity REAL NOT NULL CHECK(quantity > 0),
    PRIMARY KEY (chain_id, commodity_id)
);

-- =========================================================================
-- Indexes
-- =========================================================================

CREATE INDEX idx_commodities_tier ON commodities(tier);
CREATE INDEX idx_commodities_ubiquity ON commodities(production_ubiquity);
CREATE INDEX idx_commodities_demand ON commodities(demand_model);
CREATE INDEX idx_chains_output ON production_chains(output_commodity);
CREATE INDEX idx_chain_inputs_commodity ON chain_inputs(commodity_id);

Alignment notes for server team

• Every column maps 1:1 from a TOML field. No computed columns.
• Booleans are INTEGER (0/1) — standard SQLite convention, consistent with existing schema (inhabited, asteroid_belt, etc.).
• FK constraints enforce referential integrity at the DB level.
• chain_inputs composite PK: one commodity appears at most once per chain (Leontief fixed-coefficient — one coefficient per input per chain).
• panic_threshold_weeks is integer ≥ 0. Zero means no panic behavior. Non-zero triggers the stockpile-target panic mechanism at runtime when actual stockpile falls below this threshold.

---

2. TOML Field Specification

commodities.toml

Field	Type	Required	Constraint	Source
TOML key	string	yes	[a-z][a-z0-9_]* (snake_case slug)	ID
name	string	yes	non-empty, Title Case	display
tier	enum	yes	raw, intermediate, final, service_professional, service_luxury	D-173
elasticity	enum	yes	perfectly_inelastic, inelastic, unit_elastic, elastic	D-173 (4 classes)
base_price	float	yes	> 0	Tractus equilibrium
bulk_class	enum	yes	bulk, liquid, perishable, standard, compact, precision, oversized, non_physical	transport cost
unit	enum	yes	tonnes, units, contracts	quantity basis
production_ubiquity	enum	yes	ubiquitous, common, regional, concentrated, monopolistic	B-S init guidance
demand_model	enum	yes	market, utility, compliance	B-S demand floors
commission_certifiable	bool	yes	true/false	Gestalt political
compact_contested	bool	yes	true/false	Gestalt political
shadow_viable	bool	yes	true/false	Gestalt political
panic_threshold_weeks	int	yes	≥ 0	B-S panic mechanism
description	string	no	—	flavor text

production_chains.toml

Field	Type	Required	Constraint	Source
TOML key	string	yes	[a-z][a-z0-9_]* (snake_case slug)	chain ID
output	string	yes	must exist in commodities.toml	commodity_id ref
output_quantity	float	no	> 0, default 1.0	yield per cycle
inputs	array	yes	≥ 1 entry, each { commodity, quantity }	recipe
inputs[].commodity	string	yes	must exist in commodities.toml	commodity_id ref
inputs[].quantity	float	yes	> 0	input coefficient
location_bound	bool	no	default false	geographic constraint
description	string	no	—	human-readable recipe

---

3. Bulk Class Transport Multipliers

Class	Multiplier	Gate cost at 8% base	5-hop price	10-hop price	Commodities
bulk	1.2x	9.6%/hop	1.58x	2.50x	ore, stone, timber
liquid	1.3x	10.4%/hop	1.64x	2.69x	water, chemical_feedstock, fusion_fuel
perishable	1.8x	14.4%/hop	1.97x	3.87x	agricultural_produce
standard	1.0x	8.0%/hop	1.47x	2.16x	refined_metals, alloys, food, chemicals, textiles, panels
compact	0.8x	6.4%/hop	1.36x	1.86x	electronics, drive_cores, lattice_substrate, consumer_goods, medical_goods
precision	0.6x	4.8%/hop	1.26x	1.60x	rare_minerals, lattice_grade_material, implant_hardware
oversized	2.0x	16.0%/hop	2.10x	4.41x	heavy_equipment, vehicles, haulers, gate_components, habitat_modules, rail
non_physical	∞	N/A	N/A	N/A	all services

---

4. Validation Rules for make economy-db

Hard failures (build aborts)

#	Rule	Rationale
V-01	Every inputs[].commodity must exist in commodities.toml	Dangling reference
V-02	Every output must exist in commodities.toml	Dangling reference
V-03	Every chain must have ≥ 1 input	Zero-input chain = free production, breaks Leontief
V-04	No commodity may appear as both input and output in the same chain	Self-referential production
V-05	No circular dependencies in the chain graph (topological sort)	Infinite loop in sim
V-06	base_price > 0 for all commodities	Zero/negative prices break tâtonnement
V-07	quantity > 0 for all chain inputs and output_quantity > 0	Dead inputs
V-08	All enum fields match their allowed values	Typo protection
V-09	No duplicate commodity IDs (TOML-enforced, but validate)	Collision
V-10	Services (tier = service_*) must NOT appear as chain inputs or outputs	D-173: services outside production chain
V-11	panic_threshold_weeks >= 0	Schema constraint
V-12	demand_model = "utility" requires elasticity ∈ {perfectly_inelastic, inelastic}	Utility demand can't be highly deferrable
V-13	demand_model = "compliance" requires commission_certifiable = true	Compliance demand requires a certifying authority
V-14	Services must have bulk_class = "non_physical"	Services can't be transported
V-15	Services must have unit = "contracts"	Service unit convention
V-16	Commodity IDs must match pattern [a-z][a-z0-9_]*	Slug format for Rust/SQL

Soft warnings (build succeeds, prints warning)

#	Rule	Rationale
W-01	Every non-service physical commodity SHOULD appear as chain input or chain output	Orphan detection
W-02	Raw tier commodities SHOULD NOT appear as chain outputs	Raws are extracted, not produced
W-03	Final tier commodities SHOULD NOT appear as chain inputs	Finals are end-of-chain
W-04	shadow_viable = true SHOULD have commission_certifiable = true OR compact_contested = true	Shadow markets arise from regulatory friction
W-05	production_ubiquity = "monopolistic" commodities with elasticity = "inelastic" SHOULD be reviewed	Monopoly + inelastic = potential runaway pricing

Coverage warnings (future — gated by corp data)

#	Rule	Rationale
C-01	3+ corporations per major commodity type (D-175)	Simulation density
C-02	1+ corporation per inhabited system > 100K population (D-175)	Coverage threshold

---

5. Catalog Totals

Category	Count	Notes
Raw materials	9	+2 over D-173's ~7: chemical_feedstock (substitution route), lattice_grade_material (east reach anchor)
Intermediate goods	10	+2 over D-173's ~8: fusion_fuel (water→fuel, locked), lattice_substrate (lattice chain)
Final goods	9	+2 over D-173's ~7: freight_haulers (distinct demand function), rail_infrastructure (locked)
Professional services	5	commission_certification (shadow economy mechanism) is the key addition
Luxury services	3	matches D-173
Total	36	D-173 target: ~30. Each addition has locked justification.

Production chains

Tier transition	Count	Substitution routes
Raw → Intermediate	12	2 (chemicals: geological vs biological; panels: timber vs stone)
Intermediate → Final	9	0
Total	21	2

Panic-flagged commodities

Commodity	Threshold	Behavior
water	1 week	Life-or-death hoarding. Demand spikes 3-5x
fusion_fuel	2 weeks	Station emergency reserves. Demand spikes 2-3x
processed_food	2 weeks	Consumer panic buying. Demand spikes 1.5-2x
chemicals	3 weeks	Hospital/industrial stockpiling. Demand spikes 1.5x

All other commodities: panic_threshold_weeks = 0 (no panic behavior).

Key chokepoints

1. lattice_grade_material → lattice_substrate → implant_hardware + gate_components: Geographically sparse, no substitute, feeds the two most politically sensitive finals. Single supply disruption cascades to both neural tech AND gate infrastructure.

2. rare_minerals → electronics / advanced_alloys / drive_cores: Appears in 3 intermediate chains. Electronics is input to 9 of 9 finals. A rare minerals shortage cascades to the entire manufacturing sector.

3. water → fusion_fuel + chemicals + processed_food: Water feeds three intermediate chains (fuel at 8:1, chemicals, food processing). A water shortage simultaneously degrades energy, industrial chemistry, AND food supply. Fuel is the sharpest cascade (8:1 amplification), but the breadth across three chains makes water the single most dangerous raw material disruption. Frontier stations far from ice sources face permanent cost pressure on all three fronts.

Design notes for sim implementation

Berth capacity as transport cost modifier: Station docking/berth capacity is NOT a commodity — renting a berth is a property transaction tied to a specific location. However, berth capacity constrains the transport graph: a station with limited docking has higher effective transport costs (queuing, diversion, wait times). Model this as a per-node modifier on the effective hop cost. Expanding station infrastructure (consuming habitat_modules) reduces the modifier. This creates an investment incentive for station expansion without requiring a tradeable "berth" commodity.

Brand-layer demand: Brand corporations (Calloway, VGV, thrds, Bífröst Marmor, etc.) appear in the commodity simulation as demand nodes — they purchase raws and intermediates at their local market node. Their output (branded goods) is priced through a separate brand/cultural value system, not through the tâtonnement. Brand revenue appears as a GDP income cluster per system. The brand system is a future DLC deliverable built on top of this commodity foundation.

Items removed from catalog during review: commercial_intelligence (corporate behavioral advantage, not a tradeable commodity — what does the buyer receive?), habitation_berths (property transaction at a specific location, not a commodity flow through the gate network). Both are real economic concepts but belong in other systems.

Gate energy transmission (Level 3 — mass + data + energy): Span gates transmit mass, data (the Meridian is a live real-time network — already canon), and energy. Energy-over-gate is a commercial service offered by Gate Corporation — NOT an Assembly policy. Gate Corp is an independent monopoly with its own interests; it is not a government body and not Assembly-controlled. The Commission regulates gate infrastructure, but Gate Corp's energy service is a corporate commercial decision.

Implementation: per-node gate_energy_connected boolean in systems.db. On-grid nodes get reduced fusion_fuel utility demand (~0.3x baseline — still need backup fuel). Industrial chain inputs are unaffected (smelting still burns fuel per the Leontief recipe). Gate Corp charges a Tractus-denominated service fee. MARK_PRIMARY zones default to gate_energy_connected = false — the Compact refused Gate Corp energy dependency as a political choice, building independent fusion capacity instead.

Key dynamics: (1) Gate Corp cutting energy to a dependent node spikes that node's fuel demand — nearby Compact systems with surplus fuel capacity become emergency suppliers. The Compact's political independence becomes an economic asset. (2) Gate Corp energy cutoff is a corporate threat, not a government act — no democratic accountability, which is arguably more frightening. (3) fusion_fuel remains a single commodity; the demand differential emerges from subscription status, not from commodity bifurcation.