Arsitektur Teknis & Skema Data - Unit Pertanian & Smart Farming (UNT-TANI)

Dokumen ini memuat spesifikasi arsitektur perangkat lunak, skema basis data relasional PostgreSQL, pemetaan poligon geospasial GeoJSON, fungsi atomik (Atomic Stored Procedures), serta integrasi telemetri cuaca pada Unit Pertanian BUMDes.

1. Arsitektur Komponen Sistem Pertanian#

+-------------------------------------------------------------------------+
| Perangkat IoT Lahan & Pemetaan Spasial |
| (Stasiun Cuaca ESP32, Soil Moisture Probe, GPS Surveyor Lahan, Drone) |
+-------------------------------------------------------------------------+
 | (MQTT / REST API / GeoJSON)
 v
+-------------------------------------------------------------------------+
| Next.js Server Actions |
| (Zod Validation, Spatial Boundary Check, Policy Guard) |
+-------------------------------------------------------------------------+
 |
 v
+-------------------------------------------------------------------------+
| PostgreSQL Data Engine |
| +-----------------------------------------------------------------+ |
| | Tabel Petak Lahan (farming_parcels dengan GeoJSON Polygon) | |
| | Tabel Panen & Fertigasi (farming_harvest_logs, fertigation_logs)| |
| | Stored Procedure: absorb_plasma_crop_and_settle_yarnen() | |
| | Otomasi Triggers Jurnal PSAK 69 Suffix .03 | |
| | Row Level Security (RLS) Berbasis Peran (Mandor, PPL, Agronom) | |
| +-----------------------------------------------------------------+ |
+-------------------------------------------------------------------------+

2. Struktur Skema Basis Data Relasional#

2.1. Tabel Petak Lahan Kebun (farming_parcels)

sql
CREATE TABLE farming_parcels (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 parcel_code VARCHAR(30) UNIQUE NOT NULL, -- KEBUN-BLOK-A1
 parcel_name VARCHAR(100) NOT NULL, -- Blok Inti Cabai Rawit A
 commodity VARCHAR(50) NOT NULL, -- 'CABAI_RAWIT', 'JAGUNG_HIBRIDA'
 area_square_meters INT NOT NULL, -- 10.000 m2 (1 Hektar)
 population_count INT NOT NULL, -- 16.000 Pohon
 boundary_geojson JSONB NOT NULL, -- Poligon koordinat batas lahan
 planting_date DATE NOT NULL,
 estimated_first_harvest_date DATE NOT NULL,
 irrigation_type VARCHAR(30) DEFAULT 'DRIP_FERTIGATION',
 status VARCHAR(20) DEFAULT 'TBM', -- 'TBM', 'TM', 'SANITASI'
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

2.2. Tabel Catatan Pemanenan Petik (farming_harvest_logs)

sql
CREATE TABLE farming_harvest_logs (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 parcel_id UUID NOT NULL REFERENCES farming_parcels(id),
 harvest_date DATE NOT NULL,
 picking_cycle_number INT NOT NULL, -- Petikan ke-1, 2, ..., 20
 grade_a_kg NUMERIC(10,2) NOT NULL DEFAULT 0.00,
 grade_b_kg NUMERIC(10,2) NOT NULL DEFAULT 0.00,
 grade_c_rejected_kg NUMERIC(10,2) NOT NULL DEFAULT 0.00,
 total_harvest_kg NUMERIC(10,2) GENERATED ALWAYS AS (grade_a_kg + grade_b_kg) STORED,
 farmgate_price_per_kg NUMERIC(15,2) NOT NULL, -- Nilai wajar saat panen
 weighed_by VARCHAR(100) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

2.3. Tabel Penyerapan Panen Plasma & Pelunasan Yarnen (farming_yarnen_absorptions)

sql
CREATE TABLE farming_yarnen_absorptions (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 farmer_nik VARCHAR(16) NOT NULL,
 farmer_name VARCHAR(150) NOT NULL,
 commodity VARCHAR(50) NOT NULL, -- 'JAGUNG_PIPIL_KERING'
 gross_weight_kg NUMERIC(10,2) NOT NULL,
 moisture_content_pct NUMERIC(5,2) NOT NULL, -- Standar <= 14.00%
 net_weight_kg NUMERIC(10,2) NOT NULL,
 price_per_kg NUMERIC(15,2) NOT NULL, -- Rp 5.000 / kg
 total_gross_payment NUMERIC(15,2) NOT NULL, -- net_weight * price
 loan_settlement_usp NUMERIC(15,2) DEFAULT 0.00,-- Pelunasan pinjaman pokok & bunga
 net_cash_paid_to_farmer NUMERIC(15,2) NOT NULL,-- Sisa uang tunai yang diserahkan
 payment_status VARCHAR(20) DEFAULT 'SETTLED',
 settled_at TIMESTAMPTZ DEFAULT NOW()
);

3. Fungsi Transaksi Atomik (Atomic Stored Procedure)#

3.1. Penyerapan Panen Jagung & Pelunasan Otomatis Pinjaman Yarnen

sql
CREATE OR REPLACE FUNCTION process_crop_absorption_yarnen(
 p_farmer_nik VARCHAR(16),
 p_farmer_name VARCHAR(150),
 p_commodity VARCHAR(50),
 p_weight_kg NUMERIC(10,2),
 p_moisture_pct NUMERIC(5,2),
 p_price_per_kg NUMERIC(15,2),
 p_usp_loan_due NUMERIC(15,2),
 p_operator_name VARCHAR(100)
) RETURNS JSONB AS **DECLARE v_total_value NUMERIC(15,2); v_deduction NUMERIC(15,2); v_net_cash NUMERIC(15,2); v_record_id UUID; BEGIN -- Validasi kadar air tidak boleh di atas 16% untuk bahan baku pakan IF p_moisture_pct > 16.00 THEN RAISE EXCEPTION 'Kadar air jagung terlalu tinggi (%.2f%%). Maksimal 16%% untuk diserap feedmill!', p_moisture_pct; END IF; -- Hitung total nilai kotor pembelian v_total_value := p_weight_kg * p_price_per_kg; -- Tentukan nominal pemotongan hutang Yarnen IF v_total_value >= p_usp_loan_due THEN v_deduction := p_usp_loan_due; v_net_cash := v_total_value - p_usp_loan_due; ELSE v_deduction := v_total_value; v_net_cash := 0.00; END IF; -- Simpan catatan transaksi penyerapan INSERT INTO farming_yarnen_absorptions ( farmer_nik, farmer_name, commodity, gross_weight_kg, moisture_content_pct, net_weight_kg, price_per_kg, total_gross_payment, loan_settlement_usp, net_cash_paid_to_farmer ) VALUES ( p_farmer_nik, p_farmer_name, p_commodity, p_weight_kg, p_moisture_pct, p_weight_kg, p_price_per_kg, v_total_value, v_deduction, v_net_cash ) RETURNING id INTO v_record_id; -- Buat jurnal akuntansi intercompany (Persediaan Debet, Kas Kredit, RK USP Kredit) PERFORM generate_journal_yarnen_absorption( v_record_id, v_total_value, v_deduction, v_net_cash ); RETURN jsonb_build_object( 'status', 'SUCCESS', 'absorption_id', v_record_id, 'total_gross_value', v_total_value, 'loan_deducted', v_deduction, 'net_cash_paid', v_net_cash ); END;** LANGUAGE plpgsql;

4. Keamanan & Row Level Security (RLS)#

1. Mandor Kebun: Memiliki izin memasukkan data panen harian (INSERT) dan log fertigasi petak lahan yang menjadi tanggung jawabnya.
2. Penyuluh Pertanian Lapangan (PPL): Memiliki hak akses mencatat survei kondisi lahan petani plasma dan memvalidasi kelayakan komoditas sebelum panen diserap.
3. Manajer Pertanian & Direksi: Memiliki hak akses penuh (Full Analytics) untuk memantau performa tonase panen, estimasi nilai wajar panen, dan rekapitulasi penyelesaian kredit Yarnen.