-- ============================================================ -- EIGIS Core Tables: Projects, Field Trips, Personnel -- Normalized schema supporting multiple observations per trip, -- multiple samples per observation, GIS integration, -- mobile data collection, and future expansion. -- ============================================================ -- ----------------------------------------------------------- -- 1. PROJECTS - Top-level organizational unit -- ----------------------------------------------------------- CREATE TABLE projects ( project_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), project_code VARCHAR(50) NOT NULL UNIQUE, -- e.g. EIGIS-GIE-2026-001 project_name VARCHAR(255) NOT NULL, description TEXT, client_name VARCHAR(255), client_email VARCHAR(255), receptionist_email VARCHAR(255), region VARCHAR(100), start_date DATE NOT NULL, end_date DATE, status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('planning','active','paused','completed','cancelled')), boundary_geom GEOMETRY(Polygon, 4326), -- Project AOI boundary created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_projects_boundary ON projects USING GIST(boundary_geom); CREATE INDEX idx_projects_status ON projects(status); CREATE INDEX idx_projects_code ON projects(project_code); -- ----------------------------------------------------------- -- 2. PERSONNEL - Team members and data loggers -- ----------------------------------------------------------- CREATE TABLE personnel ( personnel_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), personnel_code VARCHAR(20) UNIQUE, -- e.g. TH-001 full_name VARCHAR(255) NOT NULL, email VARCHAR(255), phone VARCHAR(50), role VARCHAR(50) CHECK (role IN ('geologist','engineer','technician','manager','surveyor','student')), organization VARCHAR(255) DEFAULT 'GIE', certification VARCHAR(100), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMPTZ DEFAULT NOW() ); -- ----------------------------------------------------------- -- 3. FIELD TRIPS - Organized data collection campaigns -- ----------------------------------------------------------- CREATE TABLE field_trips ( field_trip_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), project_id UUID NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE, trip_code VARCHAR(50) NOT NULL, -- e.g. FT-2026-001 trip_date DATE NOT NULL, leader_id UUID REFERENCES personnel(personnel_id), team_members UUID[], -- Array of personnel IDs weather_condition VARCHAR(50), vehicle_info VARCHAR(100), area_visited VARCHAR(255), route_geom GEOMETRY(LineString, 4326), -- GPS track of the day notes TEXT, sync_status VARCHAR(20) DEFAULT 'pending' CHECK (sync_status IN ('pending','synced','conflict','error')), device_id VARCHAR(100), -- Mobile device identifier created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(project_id, trip_code) ); CREATE INDEX idx_field_trips_project ON field_trips(project_id); CREATE INDEX idx_field_trips_date ON field_trips(trip_date); CREATE INDEX idx_field_trips_route ON field_trips USING GIST(route_geom); CREATE INDEX idx_field_trips_sync ON field_trips(sync_status); -- ----------------------------------------------------------- -- 4. OBSERVATIONS - Core spatial data unit -- Each field trip has multiple observation points -- ----------------------------------------------------------- CREATE TABLE observations ( observation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), field_trip_id UUID NOT NULL REFERENCES field_trips(field_trip_id) ON DELETE CASCADE, site_id VARCHAR(50) NOT NULL, -- e.g. TP-001-A, EXP-047 observation_type VARCHAR(50) NOT NULL CHECK (observation_type IN ( 'test_pit','gully','slope_cut','river_valley','landslide', 'quarry_face','road_cut','natural_exposure','geothermal_spring', 'fumarole','hot_spring','geyser','mineral_deposit','other' )), exposure_type VARCHAR(50), geom GEOMETRY(Point, 4326) NOT NULL, -- Primary spatial location elevation_m DECIMAL(10,2), easting DECIMAL(12,2), northing DECIMAL(12,2), utm_zone VARCHAR(5) DEFAULT '37N', epsg_code INTEGER DEFAULT 4326, exposure_length_m DECIMAL(8,2), exposure_height_m DECIMAL(8,2), groundwater_level_m DECIMAL(8,2), weather_condition VARCHAR(50), excavation_method VARCHAR(50), accessibility VARCHAR(20) CHECK (accessibility IN ('good','fair','poor')), admin_unit VARCHAR(255), -- Auto-populated from GIS watershed VARCHAR(255), -- Auto-populated from DEM geological_formation VARCHAR(255), -- Auto-populated from Geo Map landslide_inventory_ref VARCHAR(100), -- Auto from Geohazard DB remarks TEXT, observation_date TIMESTAMPTZ DEFAULT NOW(), logger_id UUID REFERENCES personnel(personnel_id), status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','saved','submitted','reviewed','approved')), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(field_trip_id, site_id) ); -- Critical spatial indexes for low-latency rendering CREATE INDEX idx_observations_geom ON observations USING GIST(geom); CREATE INDEX idx_observations_geom_3d ON observations USING GIST(geom) WHERE elevation_m IS NOT NULL; CREATE INDEX idx_observations_type ON observations(observation_type); CREATE INDEX idx_observations_trip ON observations(field_trip_id); CREATE INDEX idx_observations_status ON observations(status); CREATE INDEX idx_observations_site ON observations(site_id); CREATE INDEX idx_observations_composite ON observations USING GIST(geom, observation_type); -- ----------------------------------------------------------- -- 5. PHOTO DOCUMENTATION - Linked to any observation -- ----------------------------------------------------------- CREATE TABLE photos ( photo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, photo_type VARCHAR(50) CHECK (photo_type IN ( 'exposure_overview','slope_photo','discontinuity_set', 'sample_photo','structural_feature','geothermal_feature', 'general','panoramic' )), file_path VARCHAR(500) NOT NULL, -- MinIO/S3 path file_size_kb INTEGER, mime_type VARCHAR(50), geom GEOMETRY(Point, 4326), -- Geotagged location azimuth_deg DECIMAL(5,2), -- Camera direction caption VARCHAR(500), taken_at TIMESTAMPTZ DEFAULT NOW(), synced BOOLEAN DEFAULT FALSE, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_photos_observation ON photos(observation_id); CREATE INDEX idx_photos_geom ON photos USING GIST(geom); CREATE INDEX idx_photos_type ON photos(photo_type); -- ----------------------------------------------------------- -- 6. AUDIT LOG - Track all changes for data integrity -- ----------------------------------------------------------- CREATE TABLE audit_log ( audit_id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100) NOT NULL, record_id UUID NOT NULL, action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')), old_data JSONB, new_data JSONB, changed_by VARCHAR(255), changed_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_audit_table ON audit_log(table_name); CREATE INDEX idx_audit_record ON audit_log(record_id); CREATE INDEX idx_audit_time ON audit_log(changed_at);