-- ============================================================ -- EIGIS Theme 3: Geothermal Features -- Hot springs, fumaroles, geothermal gradients, wells -- Supports future expansion: geochemistry, geophysics, drilling -- ============================================================ -- ----------------------------------------------------------- -- 1. GEOTHERMAL MANIFESTATIONS -- ----------------------------------------------------------- CREATE TABLE geothermal_manifestations ( manifestation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, manifestation_type VARCHAR(50) NOT NULL CHECK (manifestation_type IN ( 'hot_spring','warm_spring','fumarole','steaming_ground', 'mud_pot','geyser','sinter_deposit','travertine', 'altered_ground','hydrothermal_breccia','volcanic_vent', 'mineral_spring','other' )), surface_temp_c DECIMAL(6,2), -- Surface temperature °C discharge_rate_lps DECIMAL(8,3), -- Liters per second ph_value DECIMAL(4,2), electrical_conductivity_us DECIMAL(8,2), -- µS/cm total_dissolved_solids DECIMAL(8,2), -- mg/L fluid_color VARCHAR(50), odor VARCHAR(50), deposit_type VARCHAR(100), -- Sinter, travertine, etc. alteration_zone VARCHAR(100), alteration_intensity VARCHAR(30) CHECK (alteration_intensity IN ('none','slight','moderate','intense','pervasive')), alteration_minerals TEXT[], structural_control VARCHAR(100), -- Fault-controlled, fracture, etc. host_rock VARCHAR(100), geom GEOMETRY(Point, 4326) NOT NULL, elevation_m DECIMAL(10,2), catchment_area VARCHAR(100), usage_current VARCHAR(100), -- Bathing, irrigation, none, etc. usage_potential VARCHAR(100), temperature_measurement_depth_m DECIMAL(6,2), notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_geotherm_obs ON geothermal_manifestations(observation_id); CREATE INDEX idx_geotherm_geom ON geothermal_manifestations USING GIST(geom); CREATE INDEX idx_geotherm_type ON geothermal_manifestations(manifestation_type); CREATE INDEX idx_geotherm_temp ON geothermal_manifestations(surface_temp_c); -- ----------------------------------------------------------- -- 2. GEOTHERMAL GRADIENT STATIONS -- ----------------------------------------------------------- CREATE TABLE geothermal_gradients ( gradient_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID REFERENCES observations(observation_id) ON DELETE CASCADE, station_code VARCHAR(50) NOT NULL, depth_top_m DECIMAL(8,2) NOT NULL, depth_bottom_m DECIMAL(8,2) NOT NULL, temp_top_c DECIMAL(6,2), temp_bottom_c DECIMAL(6,2), gradient_c_per_km DECIMAL(8,2), -- °C/km conductivity_wmk DECIMAL(8,4), -- W/m·K heat_flow_mwm2 DECIMAL(8,4), -- mW/m² measurement_method VARCHAR(50), geom GEOMETRY(Point, 4326), notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_gradient_obs ON geothermal_gradients(observation_id); CREATE INDEX idx_gradient_geom ON geothermal_gradients USING GIST(geom); CREATE INDEX idx_gradient_value ON geothermal_gradients(gradient_c_per_km); -- ----------------------------------------------------------- -- 3. SAMPLING TABLE - Unified across all themes -- Multiple samples per observation, linked to any sub-entity -- ----------------------------------------------------------- CREATE TABLE samples ( sample_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, sample_code VARCHAR(50) NOT NULL, -- S-01, GTH-01, etc. sample_type VARCHAR(30) NOT NULL CHECK (sample_type IN ( 'disturbed_soil','undisturbed_soil','bulk_soil', 'rock_core','rock_chip','water','gas', 'geothermal_fluid','geothermal_gas','alteration_mineral', 'surface_sediment','vegetation','other' )), depth_m DECIMAL(6,2), horizon_ref VARCHAR(20), -- Reference to soil horizon weight_kg DECIMAL(6,2), volume_cm3 DECIMAL(8,2), sample_condition VARCHAR(30) CHECK (sample_condition IN ('good','fair','poor','compromised')), storage_method VARCHAR(50), -- Bag, core box, bottle, etc. tests_requested TEXT[], -- Array of requested tests lab_id VARCHAR(50), -- Laboratory reference lab_status VARCHAR(20) DEFAULT 'pending' CHECK (lab_status IN ('pending','in_progress','completed','failed')), lab_results JSONB, -- Flexible results storage geom GEOMETRY(Point, 4326), collected_at TIMESTAMPTZ DEFAULT NOW(), collected_by UUID REFERENCES personnel(personnel_id), notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(observation_id, sample_code) ); CREATE INDEX idx_samples_obs ON samples(observation_id); CREATE INDEX idx_samples_geom ON samples USING GIST(geom); CREATE INDEX idx_samples_type ON samples(sample_type); CREATE INDEX idx_samples_lab_status ON samples(lab_status); CREATE INDEX idx_samples_code ON samples(sample_code); -- ----------------------------------------------------------- -- 4. GEOCHEMISTRY RESULTS (Future expansion table) -- ----------------------------------------------------------- CREATE TABLE geochemistry_results ( geochem_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), sample_id UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE, analysis_type VARCHAR(50) NOT NULL CHECK (analysis_type IN ( 'xrf','xrd','icp_ms','icp_aes','aas','ic','gc_ms', 'isotope_ratio','major_oxides','trace_elements','rare_earth', 'stable_isotope','radiometric','fluid_inclusion','other' )), laboratory VARCHAR(100), method_code VARCHAR(30), detection_limit DECIMAL(12,6), results JSONB NOT NULL, -- Key-value pairs of elements/compounds unit VARCHAR(20) DEFAULT 'ppm', quality_flag VARCHAR(20) CHECK (quality_flag IN ('valid','suspect','invalid','below_detection')), analyzed_at DATE, reported_at DATE, notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_geochem_sample ON geochemistry_results(sample_id); CREATE INDEX idx_geochem_type ON geochemistry_results(analysis_type); CREATE INDEX idx_geochem_results ON geochemistry_results USING GIN(results); -- ----------------------------------------------------------- -- 5. GEOPHYSICAL SURVEYS (Future expansion table) -- ----------------------------------------------------------- CREATE TABLE geophysical_surveys ( survey_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL, project_id UUID NOT NULL REFERENCES projects(project_id), survey_type VARCHAR(50) NOT NULL CHECK (survey_type IN ( 'magnetic','gravity','seismic_refraction','seismic_reflection', 'electrical_resistivity','induced_polarization','spm', 'magnetotelluric','ground_penetrating_radar','self_potential', 'thermal_infrared','aeromagnetic','other' )), line_id VARCHAR(50), station_spacing_m DECIMAL(8,2), data_points INTEGER, depth_investigation_m DECIMAL(8,2), instrument VARCHAR(100), parameters JSONB, results_summary TEXT, data_file_path VARCHAR(500), -- S3/MinIO path geom GEOMETRY(MultiPoint, 4326), -- Survey stations line_geom GEOMETRY(LineString, 4326), -- Survey line date_conducted DATE, conducted_by UUID REFERENCES personnel(personnel_id), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_geophys_obs ON geophysical_surveys(observation_id); CREATE INDEX idx_geophys_project ON geophysical_surveys(project_id); CREATE INDEX idx_geophys_geom ON geophysical_surveys USING GIST(geom); CREATE INDEX idx_geophys_line ON geophysical_surveys USING GIST(line_geom); -- ----------------------------------------------------------- -- 6. DRILLING DATA (Future expansion table) -- ----------------------------------------------------------- CREATE TABLE drilling_data ( drill_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), project_id UUID NOT NULL REFERENCES projects(project_id), observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL, borehole_code VARCHAR(50) NOT NULL, bh_type VARCHAR(30) CHECK (bh_type IN ('exploratory','production','injection','monitoring','temperature_gradient','core_hole')), collar_easting DECIMAL(12,2), collar_northing DECIMAL(12,2), collar_elevation DECIMAL(10,2), total_depth_m DECIMAL(8,2), diameter_mm DECIMAL(6,1), inclination_deg DECIMAL(5,1), azimuth_deg DECIMAL(5,1), water_table_m DECIMAL(6,2), circulation_loss VARCHAR(50), lithology_log JSONB, -- Array of {from, to, description} geom GEOMETRY(Point, 4326) NOT NULL, trajectory_geom GEOMETRY(LineString, 4326), -- 3D borehole path start_date DATE, end_date DATE, contractor VARCHAR(100), notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(project_id, borehole_code) ); CREATE INDEX idx_drill_project ON drilling_data(project_id); CREATE INDEX idx_drill_geom ON drilling_data USING GIST(geom); CREATE INDEX idx_drill_traj ON drilling_data USING GIST(trajectory_geom); -- ----------------------------------------------------------- -- 7. PETROGRAPHY (Future expansion table) -- ----------------------------------------------------------- CREATE TABLE petrography ( petro_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), sample_id UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE, thin_section_id VARCHAR(50), rock_name VARCHAR(100), modal_mineralogy JSONB, -- {quartz: 35, feldspar: 45, ...} texture_type VARCHAR(50), grain_size_mm DECIMAL(6,3), fabric VARCHAR(50), alteration TEXT, micro_structures TEXT, photo_path VARCHAR(500), analyzed_by UUID REFERENCES personnel(personnel_id), analyzed_at DATE, notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_petro_sample ON petrography(sample_id);