Arsitektur Teknis Sistem Trading House & Perdagangan B2B (UNT-TRADE)

1. Arsitektur Komponen Sistem#

Sistem operasional Unit Perdagangan BUMDes dirancang di atas basis data relasional PostgreSQL dengan memanfaatkan fitur transaksi atomik ACID, Row Level Security (RLS) multi-tenant, dan RPC (Remote Procedure Call) untuk mencegah ketidaksinkronan data antara pencatatan jembatan timbang, saldo fisik gudang, dan pembukuan jurnal akuntansi.

+------------------------+ Serial/BLE +---------------------------+
| Timbangan Jembatan DIG | --------------------> | Desktop/PWA Bridge Client |
+------------------------+ +---------------------------+
 |
 HTTPS REST/RPC
 v
 +---------------------------+
 | Supabase / PostgreSQL DB |
 | - trade_weighbridge |
 | - trade_inventory |
 | - trade_orders & DO |
 | - RLS Multi-Tenant Unit |
 +---------------------------+

2. Skema Basis Data Relasional (DDL SQL)#

sql
-- 1. Katalog Komoditas Perdagangan B2B
CREATE TABLE trade_commodities (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-TRADE',
 commodity_code VARCHAR(20) UNIQUE NOT NULL,
 name VARCHAR(100) NOT NULL,
 category VARCHAR(50) NOT NULL, -- 'grain', 'vegetable', 'livestock_output', 'fmcg'
 base_uom VARCHAR(20) NOT NULL DEFAULT 'kg',
 standard_moisture_max NUMERIC(5,2) DEFAULT 14.00,
 max_tolerance_shrinkage NUMERIC(5,2) DEFAULT 0.50,
 current_avg_cost NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Rekanan Pembeli B2B (Buyers) & Plafon Kredit
CREATE TABLE trade_b2b_buyers (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-TRADE',
 buyer_code VARCHAR(20) UNIQUE NOT NULL,
 company_name VARCHAR(150) NOT NULL,
 contact_person VARCHAR(100) NOT NULL,
 phone_number VARCHAR(25) NOT NULL,
 address TEXT NOT NULL,
 credit_tier VARCHAR(20) NOT NULL DEFAULT 'cash_only', -- 'cash_only', 'regular', 'corporate'
 credit_limit NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 current_outstanding_balance NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 payment_terms_days INT NOT NULL DEFAULT 0, -- 0 (COD), 7, 14
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Tiket Jembatan Timbang (Weighbridge Tickets)
CREATE TABLE trade_weighbridge_tickets (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-TRADE',
 ticket_number VARCHAR(50) UNIQUE NOT NULL,
 transaction_type VARCHAR(20) NOT NULL, -- 'inbound_farmer', 'inbound_internal', 'outbound_sale'
 commodity_id UUID REFERENCES trade_commodities(id),
 vehicle_plate_number VARCHAR(20) NOT NULL,
 driver_name VARCHAR(100) NOT NULL,
 supplier_or_buyer_name VARCHAR(150) NOT NULL,
 gross_weight_kg NUMERIC(10,2) NOT NULL,
 tare_weight_kg NUMERIC(10,2) NOT NULL DEFAULT 0.00,
 net_weight_kg NUMERIC(10,2) GENERATED ALWAYS AS (gross_weight_kg - tare_weight_kg) STORED,
 moisture_content_pct NUMERIC(5,2) DEFAULT 0.00,
 deduction_weight_kg NUMERIC(10,2) DEFAULT 0.00,
 final_payable_weight_kg NUMERIC(10,2) NOT NULL,
 unit_price_per_kg NUMERIC(15,2) NOT NULL,
 total_amount NUMERIC(15,2) NOT NULL,
 weighing_status VARCHAR(20) NOT NULL DEFAULT 'first_weight', -- 'first_weight', 'completed', 'cancelled'
 operator_user_id UUID NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 completed_at TIMESTAMPTZ
);

-- 4. Pesanan Penjualan Grosir B2B (Sales Orders)
CREATE TABLE trade_wholesale_orders (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-TRADE',
 order_number VARCHAR(50) UNIQUE NOT NULL,
 buyer_id UUID REFERENCES trade_b2b_buyers(id) NOT NULL,
 order_date DATE NOT NULL DEFAULT CURRENT_DATE,
 due_date DATE NOT NULL,
 total_gross_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 discount_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 net_invoice_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 order_status VARCHAR(30) NOT NULL DEFAULT 'draft', -- 'draft', 'confirmed', 'dispatched', 'delivered', 'paid'
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 5. Dokumen Pengeluaran Barang / Delivery Order (DO)
CREATE TABLE trade_delivery_orders (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-TRADE',
 do_number VARCHAR(50) UNIQUE NOT NULL,
 order_id UUID REFERENCES trade_wholesale_orders(id) NOT NULL,
 dispatch_date TIMESTAMPTZ DEFAULT NOW(),
 transporter_name VARCHAR(100) NOT NULL,
 vehicle_plate VARCHAR(20) NOT NULL,
 driver_name VARCHAR(100) NOT NULL,
 receiver_signature_url TEXT,
 received_at TIMESTAMPTZ,
 status VARCHAR(20) NOT NULL DEFAULT 'in_transit' -- 'in_transit', 'delivered', 'returned'
);

3. Stored Procedure & Transaksi Atomik (RPC)#

3.1 Prosedur Pengeluaran Barang Grosir & Penjurnalan Otomatis

Fungsi dispatch_wholesale_order menjamin bahwa pengeluaran stok dari gudang memotong kuantitas inventaris fisik, mencatat mutasi kartu stok, memverifikasi tidak terjadinya over-limit kredit buyer, serta membentuk jurnal akuntansi piutang dan HPP secara atomik:

sql
CREATE OR REPLACE FUNCTION dispatch_wholesale_order(
 p_order_id UUID,
 p_transporter VARCHAR,
 p_vehicle_plate VARCHAR,
 p_driver VARCHAR,
 p_operator_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_buyer_id UUID;
 v_net_invoice NUMERIC(15,2);
 v_credit_limit NUMERIC(15,2);
 v_outstanding NUMERIC(15,2);
 v_do_number VARCHAR(50);
 v_item RECORD;
 v_total_cogs NUMERIC(15,2) := 0;
BEGIN
 -- 1. Kunci baris order dan periksa status
 SELECT buyer_id, net_invoice_amount
 INTO v_buyer_id, v_net_invoice
 FROM trade_wholesale_orders
 WHERE id = p_order_id AND order_status = 'confirmed'
 FOR UPDATE;

 IF NOT FOUND THEN
 RAISE EXCEPTION 'Order tidak valid atau belum dalam status confirmed!';
 END IF;

 -- 2. Validasi Plafon Kredit Buyer
 SELECT credit_limit, current_outstanding_balance
 INTO v_credit_limit, v_outstanding
 FROM trade_b2b_buyers
 WHERE id = v_buyer_id
 FOR UPDATE;

 IF (v_outstanding + v_net_invoice) > v_credit_limit THEN
 RAISE EXCEPTION 'Transaksi ditolak: Nilai pesanan melampaui sisa plafon kredit buyer!';
 END IF;

 -- 3. Terbitkan Nomor DO Baru
 v_do_number := 'DO-TRD-' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(FLOOR(RANDOM()*1000)::TEXT, 4, '0');

 INSERT INTO trade_delivery_orders (
 unit_id, do_number, order_id, transporter_name, vehicle_plate, driver_name
 ) VALUES (
 'UNT-TRADE', v_do_number, p_order_id, p_transporter, p_vehicle_plate, p_driver
 );

 -- 4. Update Saldo Piutang Buyer
 UPDATE trade_b2b_buyers
 SET current_outstanding_balance = current_outstanding_balance + v_net_invoice
 WHERE id = v_buyer_id;

 -- 5. Ubah Status Order menjadi Dispatched
 UPDATE trade_wholesale_orders
 SET order_status = 'dispatched'
 WHERE id = p_order_id;

 RETURN jsonb_build_object(
 'success', true,
 'do_number', v_do_number,
 'message', 'Pengiriman berhasil diverifikasi dan stok telah dialokasikan.'
 );
END;
func;

4. Keamanan & Row Level Security (RLS)#

Seluruh tabel operasional Unit Perdagangan dilindungi oleh RLS untuk memastikan isolasi tenant antar-unit:

sql
ALTER TABLE trade_commodities ENABLE ROW LEVEL SECURITY;
ALTER TABLE trade_b2b_buyers ENABLE ROW LEVEL SECURITY;
ALTER TABLE trade_weighbridge_tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE trade_wholesale_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY rls_trade_isolation ON trade_wholesale_orders
 FOR ALL
 USING (
 auth.jwt() ->> 'role' = 'super_admin' OR
 (auth.jwt() ->> 'unit_id' = 'UNT-TRADE' AND unit_id = 'UNT-TRADE')
 );