Arsitektur Teknis Sistem Produksi Pakan Ternak Feedmill (UNT-FEED)

1. Diagram Relasi Entitas (ERD) Modul Pabrik Pakan#

Sistem informasi manufaktur pakan BUMDes dirancang untuk melacak pergerakan bahan baku sejak penimbangan bahan mentah, pengolahan adonan resep, hingga pengemasan pelet jadi:

+---------------------------+ +---------------------------+
| feedmill_raw_materials | | feedmill_recipes |
| (Jagung, Maggot, Premix) | | (Master Formula & Nutrisi)|
+---------------------------+ +---------------------------+
 | |
 +------------------+-----------------+
 |
 v
 +-------------------------------+
 | feedmill_recipe_items |
 | (Rasio Komposisi Bahan BOM) |
 +-------------------------------+
 |
 v
 +-------------------------------+
 | feedmill_production_batches |
 | (Surat Perintah Kerja Batch) |
 +-------------------------------+
 |
 +---------------+---------------+
 | |
 v v
+-------------------------------+ +-------------------------------+
| feedmill_batch_qc_logs | | feedmill_finished_products |
| (Uji Kadar Air, PDI & Protein)| | (Gudang Pakan Pelet Kemasan) |
+-------------------------------+ +-------------------------------+

2. Skema Basis Data Relasional (DDL SQL)#

sql
-- 1. Master Bahan Baku Mentah Pabrik Pakan
CREATE TABLE feedmill_raw_materials (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-FEED',
 material_code VARCHAR(30) UNIQUE NOT NULL,
 name VARCHAR(100) NOT NULL,
 category VARCHAR(50) NOT NULL, -- 'energy_source', 'protein_source', 'premix_mineral'
 current_stock_kg NUMERIC(12,2) NOT NULL DEFAULT 0.00,
 avg_cost_per_kg NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 crude_protein_pct NUMERIC(5,2) NOT NULL DEFAULT 0.00,
 moisture_limit_pct NUMERIC(5,2) NOT NULL DEFAULT 14.00,
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Master Resep & Formula Pakan (BOM Header)
CREATE TABLE feedmill_recipes (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-FEED',
 recipe_code VARCHAR(30) UNIQUE NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 target_protein_pct NUMERIC(5,2) NOT NULL,
 target_moisture_pct NUMERIC(5,2) NOT NULL DEFAULT 12.00,
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Detail Komposisi Formula Pakan (BOM Line Items)
CREATE TABLE feedmill_recipe_items (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 recipe_id UUID REFERENCES feedmill_recipes(id) ON DELETE CASCADE NOT NULL,
 raw_material_id UUID REFERENCES feedmill_raw_materials(id) NOT NULL,
 percentage_ratio NUMERIC(6,3) NOT NULL, -- Misal: 0.500 (50%)
 kg_per_ton NUMERIC(8,2) NOT NULL, -- Misal: 500.00 kg
 UNIQUE(recipe_id, raw_material_id)
);

-- 4. Batch Eksekusi Produksi (Work Orders)
CREATE TABLE feedmill_production_batches (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-FEED',
 batch_number VARCHAR(50) UNIQUE NOT NULL,
 recipe_id UUID REFERENCES feedmill_recipes(id) NOT NULL,
 target_batch_kg NUMERIC(12,2) NOT NULL, -- Misal 2.000 kg
 actual_output_kg NUMERIC(12,2) DEFAULT 0.00,
 total_raw_material_cost NUMERIC(15,2) DEFAULT 0.00,
 conversion_cost_applied NUMERIC(15,2) DEFAULT 0.00, -- BTKL + BOP
 total_manufactured_cogs NUMERIC(15,2) DEFAULT 0.00,
 cogs_per_kg NUMERIC(15,2) DEFAULT 0.00,
 status VARCHAR(30) NOT NULL DEFAULT 'in_progress', -- 'in_progress', 'completed', 'qc_rejected'
 executed_by UUID NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 completed_at TIMESTAMPTZ
);

-- 5. Log Pengujian Mutu Laboratorium (QC Testing)
CREATE TABLE feedmill_batch_qc_logs (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 batch_id UUID REFERENCES feedmill_production_batches(id) ON DELETE CASCADE NOT NULL,
 tested_moisture_pct NUMERIC(5,2) NOT NULL,
 tested_pdi_pct NUMERIC(5,2) NOT NULL,
 tested_protein_pct NUMERIC(5,2) NOT NULL,
 qc_status VARCHAR(20) NOT NULL, -- 'passed', 'failed'
 qc_notes TEXT,
 tested_at TIMESTAMPTZ DEFAULT NOW()
);

3. Stored Procedure Eksekusi Produksi Batch Atomik#

Fungsi execute_feedmill_production_batch mengotomatisasi pemotongan bahan baku sesuai formula, membebankan biaya konversi, dan menghitung HPP per kg secara atomik:

sql
CREATE OR REPLACE FUNCTION execute_feedmill_production_batch(
 p_batch_id UUID,
 p_actual_output_kg NUMERIC,
 p_conversion_cost NUMERIC
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_batch RECORD;
 v_item RECORD;
 v_item_usage_kg NUMERIC;
 v_item_cost NUMERIC;
 v_total_material_cost NUMERIC := 0;
 v_cogs_per_kg NUMERIC;
BEGIN
 -- 1. Kunci data batch produksi
 SELECT * INTO v_batch
 FROM feedmill_production_batches
 WHERE id = p_batch_id AND status = 'in_progress'
 FOR UPDATE;

 IF NOT FOUND THEN
 RAISE EXCEPTION 'Batch produksi tidak ditemukan atau sudah diselesaikan sebelumnya!';
 END IF;

 -- 2. Potong stok bahan baku berdasarkan persentase BOM
 FOR v_item IN (
 SELECT ri.raw_material_id, ri.percentage_ratio, rm.avg_cost_per_kg, rm.current_stock_kg, rm.name
 FROM feedmill_recipe_items ri
 JOIN feedmill_raw_materials rm ON ri.raw_material_id = rm.id
 WHERE ri.recipe_id = v_batch.recipe_id
 )
 LOOP
 v_item_usage_kg := (v_batch.target_batch_kg * v_item.percentage_ratio);

 IF v_item.current_stock_kg < v_item_usage_kg THEN
 RAISE EXCEPTION 'Stok bahan baku % tidak mencukupi (Tersedia: % kg, Dibutuhkan: % kg)!',
 v_item.name, v_item.current_stock_kg, v_item_usage_kg;
 END IF;

 v_item_cost := v_item_usage_kg * v_item.avg_cost_per_kg;
 v_total_material_cost := v_total_material_cost + v_item_cost;

 -- Kurangi saldo stok bahan baku
 UPDATE feedmill_raw_materials
 SET current_stock_kg = current_stock_kg - v_item_usage_kg,
 updated_at = NOW()
 WHERE id = v_item.raw_material_id;
 END LOOP;

 -- 3. Kalkulasi HPP Total & HPP per Kg
 v_cogs_per_kg := (v_total_material_cost + p_conversion_cost) / p_actual_output_kg;

 -- 4. Perbarui status batch menjadi selesai
 UPDATE feedmill_production_batches
 SET actual_output_kg = p_actual_output_kg,
 total_raw_material_cost = v_total_material_cost,
 conversion_cost_applied = p_conversion_cost,
 total_manufactured_cogs = (v_total_material_cost + p_conversion_cost),
 cogs_per_kg = v_cogs_per_kg,
 status = 'completed',
 completed_at = NOW()
 WHERE id = p_batch_id;

 RETURN jsonb_build_object(
 'success', true,
 'batch_number', v_batch.batch_number,
 'actual_output_kg', p_actual_output_kg,
 'cogs_per_kg', v_cogs_per_kg,
 'message', 'Eksekusi batch produksi pakan berhasil dituntaskan.'
 );
END;
func;

4. Keamanan & Row Level Security (RLS)#

Akses data formula dan stok feedmill diisolasi menggunakan kebijakan RLS:

sql
ALTER TABLE feedmill_raw_materials ENABLE ROW LEVEL SECURITY;
ALTER TABLE feedmill_recipes ENABLE ROW LEVEL SECURITY;
ALTER TABLE feedmill_production_batches ENABLE ROW LEVEL SECURITY;

CREATE POLICY rls_feedmill_isolation ON feedmill_production_batches
 FOR ALL
 USING (
 auth.jwt() ->> 'role' = 'super_admin' OR
 (auth.jwt() ->> 'unit_id' = 'UNT-FEED' AND unit_id = 'UNT-FEED')
 );