Arsitektur Teknis & Skema Data - Unit Simpan Pinjam Syariah (UNT-USPPS)

Dokumen ini memuat arsitektur teknis perangkat lunak, skema basis data relasional PostgreSQL, fungsi tersimpan atomik (Atomic Stored Procedures), kebijakan isolasi data (Row Level Security / RLS), serta mekanisme otomatisasi pembukuan syariah.

1. Arsitektur Komponen Sistem#

+-------------------------------------------------------------------------+
| Frontend UI & PWA Mobile |
| (Next.js 14, React Hook Form, Tailwind CSS, Lucide Icons) |
+-------------------------------------------------------------------------+
 |
 v
+-------------------------------------------------------------------------+
| Next.js Server Actions |
| (Zod Validation, Session Auth, Business Policy Guard) |
+-------------------------------------------------------------------------+
 |
 v
+-------------------------------------------------------------------------+
| PostgreSQL Data Engine |
| +-----------------------------------------------------------------+ |
| | Tabel Khusus Syariah (uspps_members, uspps_contracts, dll) | |
| | Atomic Stored Procedures (Pencairan Murabahah, Angsuran, Ta'zir)| |
| | Triggers & Otomasi Jurnal Pembukuan PSAK 102 | |
| | Row Level Security (RLS) Berbasis Peran (Teller, Analis, DPS) | |
| +-----------------------------------------------------------------+ |
+-------------------------------------------------------------------------+

2. Struktur Skema Basis Data Relasional#

2.1. Tabel Rekening Wadiah Anggota (uspps_accounts)

sql
CREATE TABLE uspps_accounts (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 cif_number VARCHAR(30) UNIQUE NOT NULL, -- USPPS-MBR-YYYY-XXXXX
 full_name VARCHAR(150) NOT NULL,
 nik VARCHAR(16) UNIQUE NOT NULL,
 phone_number VARCHAR(20) NOT NULL,
 address TEXT NOT NULL,
 wadiah_balance NUMERIC(15,2) DEFAULT 0.00 CHECK (wadiah_balance >= 0.00),
 is_active BOOLEAN DEFAULT true,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

2.2. Tabel Kontrak Pembiayaan Murabahah (uspps_financings)

sql
CREATE TABLE uspps_financings (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 contract_number VARCHAR(50) UNIQUE NOT NULL, -- MRB-YYYYMM-XXXX
 account_id UUID NOT NULL REFERENCES uspps_accounts(id),
 item_description TEXT NOT NULL, -- Objek Komoditas Riil (Traktor, Pakan, dll)
 cost_price NUMERIC(15,2) NOT NULL, -- Harga Perolehan (Harga Pokok)
 margin_amount NUMERIC(15,2) NOT NULL, -- Margin Keuntungan Kesepakatan
 selling_price NUMERIC(15,2) NOT NULL, -- Harga Jual = Pokok + Margin
 down_payment NUMERIC(15,2) DEFAULT 0.00, -- Uang Muka (Urbun)
 outstanding_principal NUMERIC(15,2) NOT NULL,-- Sisa Pokok Belum Lunas
 outstanding_margin NUMERIC(15,2) NOT NULL, -- Sisa Margin Belum Terealisasi
 tenor_months INT NOT NULL CHECK (tenor_months > 0),
 financing_type VARCHAR(20) DEFAULT 'REGULER',-- 'REGULER' atau 'YARNEN'
 collectibility INT DEFAULT 1 CHECK (collectibility BETWEEN 1 AND 5),
 status VARCHAR(20) DEFAULT 'ACTIVE', -- 'DRAFT', 'ACTIVE', 'PAID_OFF', 'RESTRUCTURED'
 dps_approved_by VARCHAR(100),
 dps_approval_date TIMESTAMPTZ,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

2.3. Tabel Rincian Angsuran & Denda Ta'zir (uspps_installments)

sql
CREATE TABLE uspps_installments (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 financing_id UUID NOT NULL REFERENCES uspps_financings(id) ON DELETE CASCADE,
 installment_number INT NOT NULL,
 due_date DATE NOT NULL,
 principal_portion NUMERIC(15,2) NOT NULL,
 margin_portion NUMERIC(15,2) NOT NULL,
 total_amount NUMERIC(15,2) NOT NULL,
 paid_principal NUMERIC(15,2) DEFAULT 0.00,
 paid_margin NUMERIC(15,2) DEFAULT 0.00,
 paid_tazir NUMERIC(15,2) DEFAULT 0.00, -- Denda Keterlambatan Sosial
 paid_at TIMESTAMPTZ,
 is_settled BOOLEAN DEFAULT false,
 collector_id VARCHAR(50)
);

2.4. Tabel Titipan Dana Kebajikan / Ta'zir (uspps_social_funds)

sql
CREATE TABLE uspps_social_funds (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 source_type VARCHAR(50) NOT NULL, -- 'TAZIR_PENALTY', 'INFAQ', 'SEDEKAH'
 reference_id UUID, -- ID installment atau transaksi eksternal
 amount NUMERIC(15,2) NOT NULL,
 direction VARCHAR(10) NOT NULL, -- 'IN' (Penerimaan) atau 'OUT' (Penyaluran)
 recipient_description TEXT, -- 'Bantuan Sembako Fakir RT 02'
 authorized_by VARCHAR(100) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Fungsi Transaksi Atomik (Atomic Stored Procedures)#

3.1. Pembayaran Angsuran Murabahah & Realisasi Margin

Untuk menjamin integritas pembukuan ganda dan menghindari anomali data saat konkurensi:

sql
CREATE OR REPLACE FUNCTION record_murabahah_payment(
 p_financing_id UUID,
 p_installment_id UUID,
 p_paid_amount NUMERIC(15,2),
 p_tazir_amount NUMERIC(15,2),
 p_payment_method VARCHAR(20),
 p_collector_id VARCHAR(50)
) RETURNS JSONB AS **DECLARE v_principal_portion NUMERIC(15,2); v_margin_portion NUMERIC(15,2); v_financing_rec RECORD; BEGIN -- Kunci baris kontrak dengan FOR UPDATE SELECT * INTO v_financing_rec FROM uspps_financings WHERE id = p_financing_id FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'Kontrak pembiayaan tidak ditemukan'; END IF; -- Ambil porsi pokok dan margin dari jadwal angsuran SELECT principal_portion, margin_portion INTO v_principal_portion, v_margin_portion FROM uspps_installments WHERE id = p_installment_id AND is_settled = false FOR UPDATE; -- Perbarui status angsuran UPDATE uspps_installments SET paid_principal = v_principal_portion, paid_margin = v_margin_portion, paid_tazir = p_tazir_amount, paid_at = NOW(), is_settled = true, collector_id = p_collector_id WHERE id = p_installment_id; -- Kurangi baki pokok dan margin pada kontrak utama UPDATE uspps_financings SET outstanding_principal = outstanding_principal - v_principal_portion, outstanding_margin = outstanding_margin - v_margin_portion, status = CASE WHEN (outstanding_principal - v_principal_portion) <= 0 THEN 'PAID_OFF' ELSE status END WHERE id = p_financing_id; -- Catat penerimaan denda ta'zir jika ada (ke pos dana sosial kebajikan) IF p_tazir_amount > 0 THEN INSERT INTO uspps_social_funds(source_type, reference_id, amount, direction, authorized_by) VALUES ('TAZIR_PENALTY', p_installment_id, p_tazir_amount, 'IN', p_collector_id); END IF; -- Buat entri jurnal akuntansi PSAK 102 secara otomatis PERFORM generate_journal_murabahah_installment( p_financing_id, v_principal_portion, v_margin_portion, p_tazir_amount, p_payment_method ); RETURN jsonb_build_object('status', 'SUCCESS', 'message', 'Angsuran berhasil dibukukan'); END;** LANGUAGE plpgsql;

4. Keamanan & Row Level Security (RLS)#

1. Teller & Kolektor: Hanya memiliki izin membaca dan menulis transaksi harian pada unit kerja masing-masing.
2. Dewan Pengawas Syariah (DPS): Memiliki hak akses khusus (Read-Only Auditor) ke seluruh kontrak pembiayaan, mutasi dana ta'zir, dan buku besar syariah untuk keperluan audit kepatuhan syariah.
3. Isolasi Unit Usaha: Data transaksi syariah terisolasi dengan filter unit_code = 'UNT-USPPS' sehingga tidak dapat dimodifikasi oleh staf unit usaha lain.