-- Põllumassiivid (PRIA WFS-st imporditud) CREATE TABLE field_parcels ( id TEXT PRIMARY KEY, pria_id TEXT UNIQUE, geometry BLOB NOT NULL, -- SpatiaLite POLYGON centroid BLOB, -- SpatiaLite POINT area_ha REAL NOT NULL, crop_type TEXT, crop_variety TEXT, soil_type TEXT, cadastre_nr TEXT, owner_id TEXT NOT NULL, sync_status TEXT DEFAULT 'pending', updated_at INTEGER NOT NULL, version INTEGER DEFAULT 1, CONSTRAINT valid_area CHECK (area_ha > 0) ); CREATE INDEX idx_parcels_owner ON field_parcels(owner_id); CREATE INDEX idx_parcels_sync ON field_parcels(sync_status); -- Põllutööde logi (häälsisestusest) CREATE TABLE field_operations ( id TEXT PRIMARY KEY, parcel_id TEXT REFERENCES field_parcels(id), operation_type TEXT NOT NULL, -- spray|fertilize|harvest|sow|till operation_date INTEGER NOT NULL, product_name TEXT, product_amount REAL, product_unit TEXT, -- kg|l|t area_treated_ha REAL, voice_transcript TEXT, whisper_confidence REAL, ner_structured TEXT, -- JSON from EstBERT weather_temp_c REAL, weather_wind_ms REAL, weather_humidity REAL, operator_id TEXT, offline_created INTEGER DEFAULT 1, sync_status TEXT DEFAULT 'pending', synced_at INTEGER, version INTEGER DEFAULT 1 ); CREATE INDEX idx_ops_parcel ON field_operations(parcel_id); CREATE INDEX idx_ops_date ON field_operations(operation_date); CREATE INDEX idx_ops_sync ON field_operations(sync_status); -- Offline kaardiklotsid CREATE TABLE offline_map_tiles ( z INTEGER, x INTEGER, y INTEGER, tile_data BLOB NOT NULL, source TEXT DEFAULT 'maaamet', expires_at INTEGER, PRIMARY KEY (z, x, y, source) ); -- Laohaldus CREATE TABLE inventory ( id TEXT PRIMARY KEY, product_name TEXT NOT NULL, category TEXT, -- fertilizer|pesticide|seed|fuel quantity REAL NOT NULL, unit TEXT NOT NULL, batch_nr TEXT, expiry_date INTEGER, price_eur REAL, updated_at INTEGER, sync_status TEXT DEFAULT 'pending' ); -- Sync järjekord CREATE TABLE sync_queue ( id INTEGER PRIMARY KEY AUTOINCREMENT, table_name TEXT NOT NULL, record_id TEXT NOT NULL, operation TEXT NOT NULL, -- INSERT|UPDATE|DELETE payload TEXT NOT NULL, -- JSON created_at INTEGER NOT NULL, retry_count INTEGER DEFAULT 0, last_error TEXT );

terraform/ ├── environments/ │ ├── dev/ │ │ ├── main.tf │ │ ├── variables.tf │ │ └── terraform.tfvars │ ├── staging/ │ └── prod/ ├── modules/ │ ├── cloud-run/ │ ├── cloud-sql/ │ ├── gke-cluster/ │ ├── networking/ │ ├── iam/ │ └── monitoring/ ├── backend.tf # GCS state locking └── versions.tf

-- Põllumassiivid pilveandmebaasis (PostGIS) CREATE TABLE IF NOT EXISTS field_parcels ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), pria_id TEXT UNIQUE, owner_id UUID NOT NULL REFERENCES users(id), geometry geometry(POLYGON, 3301) NOT NULL, centroid geometry(POINT, 3301) GENERATED ALWAYS AS (ST_Centroid(geometry)) STORED, area_ha NUMERIC(10,4) NOT NULL GENERATED ALWAYS AS ( ST_Area(geometry) / 10000.0 ) STORED, crop_type TEXT, soil_type TEXT, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), version INTEGER DEFAULT 1 ); -- GIST ruumiindeks + CLUSTER CREATE INDEX idx_parcels_geom ON field_parcels USING GIST (geometry); CLUSTER field_parcels USING idx_parcels_geom; -- Ruumipäring näide: kõik massiivid 5km raadiuses SELECT id, pria_id, crop_type, ST_Area(geometry)/10000 AS area_ha FROM field_parcels WHERE ST_DWithin( geometry, ST_Transform( ST_SetSRID(ST_MakePoint(26.0, 58.5), 4326), 3301 ), 5000 -- 5km raadiuses );

// X-tee adapter: REST/JSON → SOAP/XML class XteeAdapter { final String securityServerUrl; final X509Certificate clientCert; Future<PriaResponse> submitFieldJournal( FieldJournalEntry entry ) async { final soapEnvelope = buildSoapEnvelope( service: 'pria-pht', method: 'submitFieldOperation', headers: { 'X-Road-UserId': entry.farmerId, 'X-Road-Id': generateMessageId(), 'X-Road-Service': 'EE/GOV/70000734/pria-pht/v1', }, body: fieldEntryToXml(entry), ); final response = await httpClient.post( Uri.parse(securityServerUrl), headers: { 'Content-Type': 'text/xml; charset=utf-8', 'SOAPAction': 'submitFieldOperation', }, body: soapEnvelope, ); return PriaResponse.fromSoapXml(response.body); } }

// PRIA WFS — põllumassiivide import GET https://kaardirakendus.pria.ee/geoserver/wfs ?service=WFS &version=2.0.0 &request=GetFeature &typeName=pria:field_parcels &srsName=EPSG:3301 &outputFormat=application/json &CQL_FILTER=owner_reg_code='12345678' // Vastus: GeoJSON FeatureCollection { "type": "FeatureCollection", "features": [{ "type": "Feature", "geometry": { "type": "Polygon", "coordinates": [[[6520000,648000], ...]] }, "properties": { "pria_id": "PM-2024-001234", "area_ha": 12.45, "land_use": "P" // P=põld, R=rohumaa } }] }

{ "model_name": "openai/whisper-small", "language": "et", "task": "transcribe", "lora_config": { "r": 16, "lora_alpha": 32, "lora_dropout": 0.05, "target_modules": [ "q_proj", "v_proj", "k_proj", "out_proj" ] }, "training_args": { "learning_rate": 1e-5, "warmup_steps": 500, "max_steps": 5000, "per_device_train_batch_size": 16, "gradient_accumulation_steps": 2, "fp16": true, "evaluation_strategy": "steps", "eval_steps": 500, "save_steps": 1000, "logging_steps": 50 }, "quantization": { "type": "INT8", "calibration_samples": 1000, "export_format": "ONNX" } }