Arsitektur Teknis Sistem Billing PAMSIMAS & Akuakultur Kolam (UNT-AIR / UNT-FISH)

1. Diagram Relasi Entitas (ERD) Sistem Air & Kolam#

+------------------------------------+ +------------------------------------+
| pamsimas_subscribers | | aquaculture_ponds |
| (350 Pelanggan SR & Nomor Meter) | | (12 Kolam Terpal Bulat D4) |
+------------------------------------+ +------------------------------------+
 | |
 v v
+------------------------------------+ +------------------------------------+
| pamsimas_readings | | aquaculture_cycles |
| (Angka Stand Meter & Foto Bukti) | | (Siklus Tebar & Pakan Feedmill) |
+------------------------------------+ +------------------------------------+
 | |
 v v
+------------------------------------+ +------------------------------------+
| pamsimas_billings | | aquaculture_harvests |
| (Kalkulasi Tagihan Tarif 3 Blok) | | (Panen Nila, Bobot & Jurnal HPP) |
+------------------------------------+ +------------------------------------+

2. Skema Basis Data Relasional (DDL SQL)#

sql
-- 1. Master Pelanggan Sambungan Rumah (SR) PAMSIMAS
CREATE TABLE pamsimas_subscribers (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-AIR',
 customer_number VARCHAR(50) UNIQUE NOT NULL, -- Contoh: SR-04-0125
 full_name VARCHAR(150) NOT NULL,
 nik CHAR(16) NOT NULL,
 phone_number VARCHAR(20) NOT NULL,
 hamlet_zone VARCHAR(50) NOT NULL, -- Dusun A, B, C, D
 meter_serial_number VARCHAR(100) NOT NULL,
 qr_payload VARCHAR(100) UNIQUE NOT NULL,
 installation_date DATE NOT NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'active', -- 'active', 'locked', 'terminated'
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Pencatatan Meteran Air Bulanan (Stand Meter)
CREATE TABLE pamsimas_readings (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 subscriber_id UUID REFERENCES pamsimas_subscribers(id) NOT NULL,
 reading_month INTEGER NOT NULL, -- 1 s/d 12
 reading_year INTEGER NOT NULL, -- 2026
 previous_meter_value NUMERIC(10,2) NOT NULL,
 current_meter_value NUMERIC(10,2) NOT NULL,
 consumption_m3 NUMERIC(10,2) NOT NULL,
 is_anomaly BOOLEAN DEFAULT FALSE, -- True jika lonjakan > 50% atau 0 m3
 anomaly_notes TEXT,
 proof_image_url TEXT, -- URL foto dial meteran
 recorded_by VARCHAR(100) NOT NULL,
 recorded_at TIMESTAMPTZ DEFAULT NOW(),
 UNIQUE(subscriber_id, reading_month, reading_year)
);

-- 3. Tagihan Rekening Air Bulanan (Billings)
CREATE TABLE pamsimas_billings (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 reading_id UUID REFERENCES pamsimas_readings(id) NOT NULL,
 subscriber_id UUID REFERENCES pamsimas_subscribers(id) NOT NULL,
 billing_number VARCHAR(50) UNIQUE NOT NULL, -- Contoh: INV-AIR-2026-03-0125
 period_month INTEGER NOT NULL,
 period_year INTEGER NOT NULL,
 base_administrative_fee NUMERIC(15,2) NOT NULL DEFAULT 10000.00, -- Abodemen
 usage_fee NUMERIC(15,2) NOT NULL, -- Hasil kalkulasi tarif progresif 3 blok
 penalty_fee NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 total_amount NUMERIC(15,2) NOT NULL,
 payment_status VARCHAR(30) NOT NULL DEFAULT 'unpaid', -- 'unpaid', 'paid', 'overdue'
 payment_method VARCHAR(50), -- 'cash_counter', 'portal_warga_qris'
 paid_at TIMESTAMPTZ,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Master Kolam Terpal Bioflok
CREATE TABLE aquaculture_ponds (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-FISH',
 pond_code VARCHAR(50) UNIQUE NOT NULL, -- Contoh: KLM-BIO-01 s/d 12
 diameter_meters NUMERIC(4,2) NOT NULL DEFAULT 4.00,
 water_volume_m3 NUMERIC(6,2) NOT NULL DEFAULT 12.00,
 status VARCHAR(30) NOT NULL DEFAULT 'active', -- 'preparation', 'active', 'harvesting', 'cleaning'
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 5. Siklus Pemeliharaan Bioflok Ikan Nila
CREATE TABLE aquaculture_cycles (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 pond_id UUID REFERENCES aquaculture_ponds(id) NOT NULL,
 cycle_number VARCHAR(50) UNIQUE NOT NULL, -- Contoh: CYC-NILA-2026-01
 fingerling_count INTEGER NOT NULL, -- Jumlah benih (misal 1.200 ekor)
 initial_biomass_kg NUMERIC(8,2) NOT NULL,
 stocking_date DATE NOT NULL,
 estimated_harvest_date DATE NOT NULL,
 total_feed_consumed_kg NUMERIC(8,2) DEFAULT 0.00,
 current_status VARCHAR(30) DEFAULT 'ongoing', -- 'ongoing', 'harvested', 'failed'
 created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Stored Procedure Kalkulasi Tarif Progresif & Tagihan Otomatis#

Fungsi record_meter_reading_and_generate_bill menghitung pemakaian air secara atomik menggunakan struktur tarif 3 blok progresif:

sql
CREATE OR REPLACE FUNCTION record_meter_reading_and_generate_bill(
 p_subscriber_id UUID,
 p_current_meter_value NUMERIC,
 p_reading_month INTEGER,
 p_reading_year INTEGER,
 p_recorded_by VARCHAR,
 p_proof_image_url TEXT
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_prev_reading NUMERIC(10,2) := 0.00;
 v_consumption NUMERIC(10,2);
 v_base_fee NUMERIC(15,2) := 10000.00; -- Abodemen Rp 10.000
 v_usage_fee NUMERIC(15,2) := 0.00;
 v_total_bill NUMERIC(15,2);
 v_is_anomaly BOOLEAN := FALSE;
 v_anomaly_note TEXT := NULL;
 v_reading_id UUID;
 v_bill_id UUID;
 v_bill_number VARCHAR(50);
 v_cust_number VARCHAR(50);
BEGIN
 -- Ambil stand meter bulan sebelumnya
 SELECT COALESCE(current_meter_value, 0.00) INTO v_prev_reading
 FROM pamsimas_readings
 WHERE subscriber_id = p_subscriber_id
 ORDER BY reading_year DESC, reading_month DESC
 LIMIT 1;

 -- Hitung kubikasi pemakaian
 v_consumption := p_current_meter_value - v_prev_reading;
 IF v_consumption < 0 THEN
 RAISE EXCEPTION 'Angka stand meter baru tidak boleh lebih kecil dari bulan lalu!';
 END IF;

 -- Deteksi Anomali
 IF v_consumption = 0 THEN
 v_is_anomaly := TRUE;
 v_anomaly_note := 'Konsumsi 0 m3: Kemungkinan meteran macet atau rumah kosong';
 ELSIF v_consumption > 30 THEN
 v_is_anomaly := TRUE;
 v_anomaly_note := 'Konsumsi melonjak tinggi (>30 m3): Waspada kebocoran pipa';
 END IF;

 -- Kalkulasi Tarif Progresif 3 Blok:
 -- Blok I (0-10 m3) @ Rp 2.000
 -- Blok II (11-20 m3) @ Rp 2.500
 -- Blok III (>20 m3) @ Rp 3.500
 IF v_consumption <= 10 THEN
 v_usage_fee := v_consumption * 2000;
 ELSIF v_consumption <= 20 THEN
 v_usage_fee := (10 * 2000) + ((v_consumption - 10) * 2500);
 ELSE
 v_usage_fee := (10 * 2000) + (10 * 2500) + ((v_consumption - 20) * 3500);
 END IF;

 v_total_bill := v_base_fee + v_usage_fee;

 -- Simpan Pembacaan Meteran
 INSERT INTO pamsimas_readings (
 subscriber_id, reading_month, reading_year, previous_meter_value,
 current_meter_value, consumption_m3, is_anomaly, anomaly_notes,
 proof_image_url, recorded_by
 )
 VALUES (
 p_subscriber_id, p_reading_month, p_reading_year, v_prev_reading,
 p_current_meter_value, v_consumption, v_is_anomaly, v_anomaly_note,
 p_proof_image_url, p_recorded_by
 )
 RETURNING id INTO v_reading_id;

 -- Ambil Nomor Pelanggan
 SELECT customer_number INTO v_cust_number FROM pamsimas_subscribers WHERE id = p_subscriber_id;
 v_bill_number := format('INV-AIR-%s-%s-%s', p_reading_year, lpad(p_reading_month::text, 2, '0'), v_cust_number);

 -- Terbitkan Tagihan Rekening Air
 INSERT INTO pamsimas_billings (
 reading_id, subscriber_id, billing_number, period_month, period_year,
 base_administrative_fee, usage_fee, total_amount, payment_status
 )
 VALUES (
 v_reading_id, p_subscriber_id, v_bill_number, p_reading_month, p_reading_year,
 v_base_fee, v_usage_fee, v_total_bill, 'unpaid'
 )
 RETURNING id INTO v_bill_id;

 RETURN jsonb_build_object(
 'success', true,
 'billing_id', v_bill_id,
 'billing_number', v_bill_number,
 'consumption_m3', v_consumption,
 'total_amount', v_total_bill,
 'is_anomaly', v_is_anomaly
 );
END;
func;

4. Keamanan & Kebijakan Row Level Security (RLS)#

sql
ALTER TABLE pamsimas_subscribers ENABLE ROW LEVEL SECURITY;
ALTER TABLE pamsimas_readings ENABLE ROW LEVEL SECURITY;
ALTER TABLE pamsimas_billings ENABLE ROW LEVEL SECURITY;

CREATE POLICY rls_pamsimas_access ON pamsimas_billings
 FOR ALL
 USING (
 auth.jwt() ->> 'role' IN ('super_admin', 'direktur', 'admin_keuangan') OR
 auth.jwt() ->> 'unit_id' IN ('UNT-AIR', 'UNT-FISH') OR
 auth.jwt() ->> 'sub' = (SELECT nik FROM pamsimas_subscribers WHERE id = pamsimas_billings.subscriber_id)
 );