Arsitektur Teknis & Skema Data - Unit Peternakan Terpadu (UNT-LAYER)

Dokumen ini memuat spesifikasi arsitektur perangkat lunak, skema basis data relasional PostgreSQL, fungsi tersimpan atomik (Atomic Stored Procedures), kebijakan keamanan (Row Level Security / RLS), serta integrasi sensor kandang pada Unit Peternakan Ayam BUMDes.

1. Arsitektur Komponen Sistem Peternakan#

+-------------------------------------------------------------------------+
| Perangkat IoT & Lapangan Kandang |
| (Sensor ESP32 Suhu/Amonia, Timbangan Bluetooth, Tablet Android ABK) |
+-------------------------------------------------------------------------+
 | (MQTT / REST API)
 v
+-------------------------------------------------------------------------+
| Next.js Server Actions |
| (Zod Validation, Session Auth, Business Policy Guard) |
+-------------------------------------------------------------------------+
 |
 v
+-------------------------------------------------------------------------+
| PostgreSQL Data Engine |
| +-----------------------------------------------------------------+ |
| | Tabel Flok Ayam (livestock_flocks, livestock_daily_records) | |
| | Tabel Pakan & Gudang Telur (feed_logs, egg_inventory) | |
| | Stored Procedure: record_daily_egg_production() | |
| | Otomasi Triggers Jurnal PSAK 69 Suffix .01 | |
| | Row Level Security (RLS) Berbasis Peran (ABK, Mandor, Dokter) | |
| +-----------------------------------------------------------------+ |
+-------------------------------------------------------------------------+

2. Struktur Skema Basis Data Relasional#

2.1. Tabel Flok Populasi Kandang (livestock_flocks)

sql
CREATE TABLE livestock_flocks (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 flock_code VARCHAR(30) UNIQUE NOT NULL, -- FLOK-KDG1-2026A
 house_name VARCHAR(50) NOT NULL, -- Blok Kandang 1
 strain VARCHAR(50) NOT NULL DEFAULT 'ISA Brown',
 arrival_date DATE NOT NULL,
 initial_population INT NOT NULL CHECK (initial_population > 0),
 current_population INT NOT NULL CHECK (current_population >= 0),
 acquisition_cost_per_bird NUMERIC(15,2) NOT NULL, -- Rp 85.000
 estimated_salvage_value NUMERIC(15,2) NOT NULL, -- Rp 27.000
 productive_weeks INT DEFAULT 56,
 status VARCHAR(20) DEFAULT 'ACTIVE', -- 'GROWING', 'ACTIVE', 'CULLED'
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

2.2. Tabel Recording Produksi Harian (livestock_daily_records)

sql
CREATE TABLE livestock_daily_records (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 flock_id UUID NOT NULL REFERENCES livestock_flocks(id),
 record_date DATE NOT NULL,
 eggs_good_count INT NOT NULL DEFAULT 0, -- Jumlah butir telur utuh
 eggs_good_weight_kg NUMERIC(10,2) NOT NULL, -- Berat telur utuh (kg)
 eggs_broken_count INT NOT NULL DEFAULT 0, -- Telur retak/pecah
 feed_consumed_kg NUMERIC(10,2) NOT NULL, -- Pakan yang diberikan (kg)
 mortality_count INT NOT NULL DEFAULT 0, -- Jumlah ayam mati
 culled_count INT NOT NULL DEFAULT 0, -- Jumlah ayam diafkir
 hdp_percentage NUMERIC(5,2) GENERATED ALWAYS AS (
 CASE WHEN (eggs_good_count + eggs_broken_count) > 0 THEN
 ROUND(((eggs_good_count + eggs_broken_count)::numeric / NULLIF(eggs_good_count, 0)) * 100, 2)
 ELSE 0.00 END
 ) STORED,
 fcr_ratio NUMERIC(5,2), -- Dihitung oleh Stored Procedure
 recorded_by VARCHAR(100) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 UNIQUE(flock_id, record_date)
);

2.3. Tabel Gudang & Stok Telur Segar (livestock_egg_inventory)

sql
CREATE TABLE livestock_egg_inventory (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 batch_number VARCHAR(50) UNIQUE NOT NULL, -- BATCH-YYYYMMDD-KDG1
 record_date DATE NOT NULL,
 crates_count INT NOT NULL, -- 1 Krat = 5 Tray (150 Butir)
 loose_trays INT NOT NULL, -- 1 Tray = 30 Butir
 total_weight_kg NUMERIC(10,2) NOT NULL,
 grade VARCHAR(10) NOT NULL DEFAULT 'GRADE_A', -- 'GRADE_A', 'GRADE_B', 'RETAK'
 unit_hpp_per_kg NUMERIC(15,2) NOT NULL,
 status VARCHAR(20) DEFAULT 'IN_STORAGE', -- 'IN_STORAGE', 'DISPATCHED'
 dispatched_to VARCHAR(100), -- 'Unit Kuliner', 'Grosir Pasar'
 created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Fungsi Transaksi Atomik (Atomic Stored Procedure)#

3.1. Pencatatan Panen, Kalkulasi FCR & Pemutakhiran Populasi

sql
CREATE OR REPLACE FUNCTION record_daily_egg_production(
 p_flock_id UUID,
 p_record_date DATE,
 p_eggs_good_count INT,
 p_eggs_weight_kg NUMERIC(10,2),
 p_eggs_broken_count INT,
 p_feed_consumed_kg NUMERIC(10,2),
 p_mortality_count INT,
 p_culled_count INT,
 p_operator_name VARCHAR(100)
) RETURNS JSONB AS **DECLARE v_flock RECORD; v_total_eggs INT; v_hdp NUMERIC(5,2); v_fcr NUMERIC(5,2); v_crates INT; v_trays INT; BEGIN -- 1. Kunci baris flok ayam dengan FOR UPDATE SELECT * INTO v_flock FROM livestock_flocks WHERE id = p_flock_id FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'Data flok ayam tidak ditemukan'; END IF; -- 2. Hitung parameter Hen-Day Production (HDP) v_total_eggs := p_eggs_good_count + p_eggs_broken_count; IF v_flock.current_population > 0 THEN v_hdp := ROUND((v_total_eggs::numeric / v_flock.current_population) * 100, 2); ELSE v_hdp := 0.00; END IF; -- 3. Hitung Rasio Konversi Pakan (FCR) IF p_eggs_weight_kg > 0 THEN v_fcr := ROUND((p_feed_consumed_kg / p_eggs_weight_kg), 2); ELSE v_fcr := 0.00; END IF; -- 4. Masukkan ke tabel recording harian INSERT INTO livestock_daily_records ( flock_id, record_date, eggs_good_count, eggs_good_weight_kg, eggs_broken_count, feed_consumed_kg, mortality_count, culled_count, fcr_ratio, recorded_by ) VALUES ( p_flock_id, p_record_date, p_eggs_good_count, p_eggs_weight_kg, p_eggs_broken_count, p_feed_consumed_kg, p_mortality_count, p_culled_count, v_fcr, p_operator_name ); -- 5. Kurangi populasi ayam hidup jika terdapat kematian atau afkir UPDATE livestock_flocks SET current_population = current_population - (p_mortality_count + p_culled_count), updated_at = NOW() WHERE id = p_flock_id; -- 6. Masukkan hasil panen telur utuh ke gudang (1 Krat = 150 butir, 1 Tray = 30 butir) v_crates := p_eggs_good_count / 150; v_trays := (p_eggs_good_count % 150) / 30; INSERT INTO livestock_egg_inventory ( batch_number, record_date, crates_count, loose_trays, total_weight_kg, grade, unit_hpp_per_kg ) VALUES ( 'BATCH-' || TO_CHAR(p_record_date, 'YYYYMMDD') || '-' || v_flock.flock_code, p_record_date, v_crates, v_trays, p_eggs_weight_kg, 'GRADE_A', 24000.00 ); -- 7. Panggil trigger otomasi jurnal akuntansi PSAK 69 Suffix .01 PERFORM generate_journal_egg_production( p_flock_id, p_eggs_weight_kg, p_feed_consumed_kg, p_mortality_count ); RETURN jsonb_build_object( 'status', 'SUCCESS', 'hdp', v_hdp, 'fcr', v_fcr, 'remaining_population', v_flock.current_population - (p_mortality_count + p_culled_count) ); END;** LANGUAGE plpgsql;

4. Keamanan & Row Level Security (RLS)#

1. Anak Buah Kandang (ABK): Memiliki izin memasukkan data panen harian (INSERT) hanya untuk tanggal hari berjalan (CURRENT_DATE).
2. Kepala Kandang: Memiliki izin mengubah (UPDATE) data panen jika terdapat koreksi penimbangan dalam batas waktu maksimal 24 jam.
3. Dokter Hewan & Manajemen: Memiliki hak akses penuh (Full Audit Read) ke riwayat FCR, mortalitas, dan grafik kesehatan flok tanpa batas waktu.