-- Star Systems Database Schema — server/data/systems.db -- Source of truth for all structured star system data. -- Authored via tooling pipeline; wiki pages are generated views. -- Shipped with the game — read-only from the server's perspective. -- -- Rebuild: tooling/process-wiki-system-changes --rebuild-db PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; -- Core identity, physical character, political character CREATE TABLE IF NOT EXISTS star_systems ( system_id TEXT PRIMARY KEY, -- GJ catalog number (e.g., "GJ 244A") proper_name TEXT, system_name TEXT, star_type TEXT, spectral_class TEXT, dist_ly REAL, geographic_sector TEXT, geographic_band TEXT, political_zone TEXT, -- Physical character habitable_planet_count INTEGER, inhabited_planet_count INTEGER, asteroid_belt INTEGER, -- boolean 0/1 gas_giant INTEGER, -- boolean 0/1 habitability_profile TEXT, -- Political character earth_alignment TEXT, earth_proximity TEXT, earth_tension TEXT, -- Content notes stability_index INTEGER, system_volatility TEXT, cultural_corridor TEXT, generation_priority TEXT, -- Economics (D-172) currency_zone TEXT DEFAULT 'TRACTUS_PRIMARY', -- TRACTUS_PRIMARY | MARK_PRIMARY | MIXED updated_at TEXT DEFAULT (datetime('now')) ); -- Gate infrastructure — queried independently for pathfinding/topology CREATE TABLE IF NOT EXISTS system_gates ( system_id TEXT PRIMARY KEY REFERENCES star_systems(system_id), horizon_station INTEGER, -- boolean 0/1 aperture_count INTEGER, gate_connections INTEGER, gate_topology TEXT, hop_distance_from_gateway INTEGER, span_gate_network TEXT ); -- Settlement history CREATE TABLE IF NOT EXISTS system_history ( system_id TEXT PRIMARY KEY REFERENCES star_systems(system_id), settlement_wave TEXT, founding_motivation TEXT, founding_culture_primary TEXT, founding_culture_secondary TEXT, cultural_persistence TEXT, religious_status TEXT, religious_generation_count INTEGER ); -- Historical events — proper 1:N child table CREATE TABLE IF NOT EXISTS historical_events ( id INTEGER PRIMARY KEY AUTOINCREMENT, system_id TEXT NOT NULL REFERENCES star_systems(system_id), event_type TEXT NOT NULL, age_years INTEGER, sort_order INTEGER NOT NULL DEFAULT 0 -- 0 = most recent ); -- Economic life CREATE TABLE IF NOT EXISTS system_economy ( system_id TEXT PRIMARY KEY REFERENCES star_systems(system_id), economic_tier INTEGER, population INTEGER, economic_base_primary TEXT, economic_base_secondary TEXT, distribution_index TEXT, imprint_access TEXT ); -- Faction presence CREATE TABLE IF NOT EXISTS system_factions ( system_id TEXT PRIMARY KEY REFERENCES star_systems(system_id), governance_type TEXT, dominant_faction TEXT, primary_fault_line TEXT, secondary_fault_line TEXT, assembly_presence TEXT, commission_presence TEXT, syndic_presence TEXT, syndic_type TEXT, separatist_presence TEXT, separatist_type TEXT, institute_presence TEXT, guardians_presence TEXT ); -- Cultural voice CREATE TABLE IF NOT EXISTS system_culture ( system_id TEXT PRIMARY KEY REFERENCES star_systems(system_id), cultural_register TEXT, cultural_register_secondary TEXT, ambient_anxiety TEXT, local_pride TEXT, atmospheric_tone TEXT, atmospheric_tone_secondary TEXT, active_situation TEXT, silence_threshold TEXT, primary_archetype TEXT, secondary_archetype TEXT, narrative_notable INTEGER -- boolean 0/1 ); -- Authored prose (supply_dependency, faction_notes, silence_topic, -- narrative_hook, calibration_note) lives in wiki markdown files only. -- No DB mirror — read the file if you need the text. -- Celestial bodies — planets, moons, gas giants, asteroid belts, oort clouds -- Naming convention: -- GJ-{n}b/c/d... — planets (innermost first) -- GJ-{n}Ab/c/d... — planets orbiting primary (binary systems) -- GJ-{n}d-1 — first moon of third planet -- GJ-{n}-oort — oort cloud region -- GJ-{n}-belt — asteroid belt CREATE TABLE IF NOT EXISTS bodies ( body_id TEXT PRIMARY KEY, system_id TEXT NOT NULL REFERENCES star_systems(system_id), parent_body_id TEXT REFERENCES bodies(body_id), -- NULL for planets; parent planet for moons -- Classification body_type TEXT NOT NULL, -- planet, moon, gas_giant, asteroid_belt, oort_cloud orbit_index INTEGER, -- innermost = 1 (planets around star, moons around planet) proper_name TEXT, -- "Xin Chengdu", NULL for unnamed -- Physical mass_class TEXT, -- terrestrial, super_earth, ice_giant, gas_giant, dwarf atmosphere TEXT, -- breathable, thin, toxic, none, dense surface_gravity REAL, -- in g (Earth = 1.0), NULL for gas giants orbital_period_days REAL, -- orbital period in Earth days, NULL for oort/belt rotation_period_hours REAL, -- rotation period in Earth hours (Earth = 24), NULL for tidally locked/gas giants planet_class TEXT, -- temperate, arid, frozen, oceanic, volcanic, barren, etc. hydrosphere TEXT, -- ocean, ice, rivers, none, subsurface -- Settlement inhabited INTEGER NOT NULL DEFAULT 0, population INTEGER DEFAULT 0, economic_role TEXT, -- manufacturing, agricultural, extraction, research, transit founding_age_years INTEGER, settlement_pattern TEXT, -- urban_concentrated, dispersed_rural, orbital_only, domed, cave -- Cultural (inherited from system, can override) cultural_corridor TEXT, -- override system corridor if different industrial_corridor TEXT, -- MVG, Gate_Corp, DSMC, Prometheus, Agricultural_Syndic -- Rendering terrain_reference TEXT, -- heightmap path when authored, NULL otherwise screenshot_path TEXT, -- planetary shader screenshot path updated_at TEXT DEFAULT (datetime('now')) ); -- Stations and orbital facilities -- Naming: GJ-{n}b-S1 (first station orbiting planet b), GJ-{n}-oort-S1 (horizon station) CREATE TABLE IF NOT EXISTS stations ( station_id TEXT PRIMARY KEY, system_id TEXT NOT NULL REFERENCES star_systems(system_id), orbits_body_id TEXT REFERENCES bodies(body_id), -- Classification station_type TEXT NOT NULL, -- horizon, commercial, military, research, industrial, agricultural, transit proper_name TEXT, -- "Chengdu Orbital", "Horizon Station" -- Settlement population INTEGER DEFAULT 0, economic_role TEXT, governance_type TEXT, -- can differ from system governance -- Infrastructure docking_class TEXT, -- major, standard, restricted, none has_gate_infrastructure INTEGER DEFAULT 0, district_count INTEGER DEFAULT 1, updated_at TEXT DEFAULT (datetime('now')) ); -- Corporations, combines, and major economic entities -- Source: wiki/corporations/ pages (canonical) CREATE TABLE IF NOT EXISTS corporations ( corp_id TEXT PRIMARY KEY, -- slug from wiki (e.g., "gate-corporation", "vethara") proper_name TEXT NOT NULL, -- display name corp_type TEXT NOT NULL, -- corporation, combine, syndic, institution, independent scope TEXT, -- reach-wide, sector, system, local headquarters_system TEXT REFERENCES star_systems(system_id), headquarters_body TEXT, -- body_id or station_id specialization TEXT, -- primary product/service parent_corp TEXT REFERENCES corporations(corp_id), notes TEXT, -- Economics (D-175) behavioral_archetype TEXT, -- Monopolist|Distributor|Producer|Specialist|Cooperative|Intermediary supply_chain_role TEXT, shadow_economy_access INTEGER DEFAULT 0, updated_at TEXT DEFAULT (datetime('now')) ); -- Gate adjacency graph — bidirectional edges from star-map.json (D-178) -- Transport cost computation: 5–12% per hop (gate edge), 1–3% (orbital edge) CREATE TABLE IF NOT EXISTS gate_links ( from_system_id TEXT NOT NULL REFERENCES star_systems(system_id), to_system_id TEXT NOT NULL REFERENCES star_systems(system_id), PRIMARY KEY (from_system_id, to_system_id) ); -- Commodity catalog — 36 types (D-184) -- Source: wiki/economics/commodities.toml CREATE TABLE IF NOT EXISTS commodities ( commodity_id TEXT PRIMARY KEY, name TEXT NOT NULL, tier TEXT NOT NULL, -- raw|intermediate|final|service_professional|service_luxury elasticity TEXT NOT NULL, -- perfectly_inelastic|inelastic|unit_elastic|elastic base_price REAL NOT NULL, bulk_class TEXT, -- bulk|liquid|perishable|standard|compact|precision|oversized|non_physical unit TEXT, -- tonnes|units|contracts production_ubiquity TEXT, -- ubiquitous|common|regional|concentrated|monopolistic demand_model TEXT, -- market|utility|compliance commission_certifiable INTEGER DEFAULT 0, -- subject to certification in TRACTUS_PRIMARY zones compact_contested INTEGER DEFAULT 0, shadow_viable INTEGER DEFAULT 0, panic_threshold_weeks INTEGER DEFAULT 0, description TEXT, updated_at TEXT DEFAULT (datetime('now')) ); -- Leontief production chain recipes (D-178) -- Source: wiki/economics/production_chains.toml CREATE TABLE IF NOT EXISTS production_chains ( chain_id TEXT PRIMARY KEY, output_commodity_id TEXT NOT NULL REFERENCES commodities(commodity_id), output_quantity REAL NOT NULL DEFAULT 1.0, location_bound INTEGER DEFAULT 0, description TEXT, updated_at TEXT DEFAULT (datetime('now')) ); -- Input requirements per production chain CREATE TABLE IF NOT EXISTS chain_inputs ( chain_id TEXT NOT NULL REFERENCES production_chains(chain_id), input_commodity_id TEXT NOT NULL REFERENCES commodities(commodity_id), quantity REAL NOT NULL, PRIMARY KEY (chain_id, input_commodity_id) ); -- Corporation operating locations (D-175) -- Populated by corporation assignment pipeline, not by initial import CREATE TABLE IF NOT EXISTS corp_presence ( corp_id TEXT NOT NULL REFERENCES corporations(corp_id), location_id TEXT NOT NULL, -- body_id or station_id location_type TEXT NOT NULL, -- body|station primary_operation TEXT, updated_at TEXT DEFAULT (datetime('now')), PRIMARY KEY (corp_id, location_id) ); -- Indexes -- astronomical_id removed: system_id IS the GJ catalog number CREATE INDEX IF NOT EXISTS idx_star_systems_sector ON star_systems(geographic_sector); CREATE INDEX IF NOT EXISTS idx_star_systems_wave ON system_history(settlement_wave); CREATE INDEX IF NOT EXISTS idx_star_systems_topology ON system_gates(gate_topology); CREATE INDEX IF NOT EXISTS idx_star_systems_star_type ON star_systems(star_type); CREATE INDEX IF NOT EXISTS idx_star_systems_volatility ON star_systems(system_volatility); CREATE INDEX IF NOT EXISTS idx_historical_events_system ON historical_events(system_id); CREATE INDEX IF NOT EXISTS idx_system_factions_dominant ON system_factions(dominant_faction); CREATE INDEX IF NOT EXISTS idx_system_economy_tier ON system_economy(economic_tier); CREATE INDEX IF NOT EXISTS idx_system_culture_archetype ON system_culture(primary_archetype); CREATE INDEX IF NOT EXISTS idx_bodies_system ON bodies(system_id); CREATE INDEX IF NOT EXISTS idx_bodies_parent ON bodies(parent_body_id); CREATE INDEX IF NOT EXISTS idx_bodies_type ON bodies(body_type); CREATE INDEX IF NOT EXISTS idx_bodies_inhabited ON bodies(inhabited); CREATE INDEX IF NOT EXISTS idx_stations_system ON stations(system_id); CREATE INDEX IF NOT EXISTS idx_stations_orbits ON stations(orbits_body_id); CREATE INDEX IF NOT EXISTS idx_stations_type ON stations(station_type); CREATE INDEX IF NOT EXISTS idx_corps_system ON corporations(headquarters_system); CREATE INDEX IF NOT EXISTS idx_corps_type ON corporations(corp_type); CREATE INDEX IF NOT EXISTS idx_star_systems_currency ON star_systems(currency_zone); CREATE INDEX IF NOT EXISTS idx_gate_links_from ON gate_links(from_system_id); CREATE INDEX IF NOT EXISTS idx_gate_links_to ON gate_links(to_system_id); CREATE INDEX IF NOT EXISTS idx_commodities_tier ON commodities(tier); CREATE INDEX IF NOT EXISTS idx_production_chains_output ON production_chains(output_commodity_id); CREATE INDEX IF NOT EXISTS idx_chain_inputs_commodity ON chain_inputs(input_commodity_id); CREATE INDEX IF NOT EXISTS idx_corp_presence_corp ON corp_presence(corp_id); CREATE INDEX IF NOT EXISTS idx_corp_presence_location ON corp_presence(location_id);