Arsitektur Teknis: Unit Potensi Desa, Lahan & Crowdfunding (UNT-POTENSI)

Dokumen ini mendokumentasikan skema database relasional, fungsi prosedur atomik, dan arsitektur keamanan untuk mengelola inventarisasi spasial lahan kas desa serta transaksi urun dana saham partisipasi warga.

1. Skema Basis Data Relasional (PostgreSQL DDL)#

1.1 Tabel Petak Lahan Kas Desa (village_land_parcels)

sql
CREATE TABLE IF NOT EXISTS public.village_land_parcels (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 parcel_code VARCHAR(50) NOT NULL UNIQUE, -- Contoh: 'LHN-BLG-001'
 name VARCHAR(150) NOT NULL,
 area_sqm NUMERIC(12,2) NOT NULL, -- Luas dalam m2
 area_ha NUMERIC(8,4) GENERATED ALWAYS AS (area_sqm / 10000.0) STORED,
 boundary_geojson JSONB NOT NULL, -- Poligon koordinat spasial WGS84
 soil_class VARCHAR(50) DEFAULT 'Kelas A Irigasi Teknis',
 status VARCHAR(30) DEFAULT 'available', -- 'available', 'leased', 'reserved', 'dispute'
 base_annual_rental_rate NUMERIC(15,2) NOT NULL,
 current_tenant_name VARCHAR(150),
 current_lease_start DATE,
 current_lease_end DATE,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

1.2 Tabel Kontrak Sewa Lahan (land_lease_contracts)

sql
CREATE TABLE IF NOT EXISTS public.land_lease_contracts (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 contract_number VARCHAR(100) NOT NULL UNIQUE, -- 'KTR-LHN/2026/08/012'
 parcel_id UUID NOT NULL REFERENCES public.village_land_parcels(id),
 farmer_nik VARCHAR(16) NOT NULL,
 farmer_name VARCHAR(150) NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 total_amount NUMERIC(15,2) NOT NULL,
 payment_status VARCHAR(30) DEFAULT 'unpaid', -- 'unpaid', 'partially_paid', 'paid'
 payment_method VARCHAR(50) DEFAULT 'cash', -- 'cash', 'transfer', 'yarnen'
 signed_contract_url TEXT,
 created_by UUID REFERENCES auth.users(id),
 created_at TIMESTAMPTZ DEFAULT NOW()
);

1.3 Tabel Kampanye Crowdfunding Proyek (crowdfunding_campaigns)

sql
CREATE TABLE IF NOT EXISTS public.crowdfunding_campaigns (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 campaign_code VARCHAR(50) NOT NULL UNIQUE, -- 'CF-2026-GH-01'
 title VARCHAR(200) NOT NULL,
 target_amount NUMERIC(15,2) NOT NULL, -- Contoh: 50.000.000
 price_per_share NUMERIC(15,2) NOT NULL DEFAULT 500000.00,
 total_shares INT GENERATED ALWAYS AS ((target_amount / price_per_share)::INT) STORED,
 collected_amount NUMERIC(15,2) DEFAULT 0.00,
 shares_sold INT DEFAULT 0,
 soft_cap_amount NUMERIC(15,2) NOT NULL, -- Target minimal (misal 80% = 40.000.000)
 status VARCHAR(30) DEFAULT 'draft', -- 'draft', 'active', 'funded', 'executing', 'completed', 'cancelled'
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 target_unit_id VARCHAR(50) NOT NULL, -- '03_Pertanian', '18_Unit_Transportasi'
 prospectus_pdf_url TEXT,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

1.4 Tabel Pembelian Saham Warga (crowdfunding_investments)

sql
CREATE TABLE IF NOT EXISTS public.crowdfunding_investments (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 campaign_id UUID NOT NULL REFERENCES public.crowdfunding_campaigns(id),
 investor_nik VARCHAR(16) NOT NULL,
 investor_name VARCHAR(150) NOT NULL,
 investor_wallet_id UUID, -- Terkoneksi ke Portal Warga
 share_count INT NOT NULL CHECK (share_count > 0),
 total_amount NUMERIC(15,2) NOT NULL,
 escrow_payment_ref VARCHAR(100) NOT NULL,
 payment_status VARCHAR(30) DEFAULT 'verified', -- 'pending', 'verified', 'refunded'
 certificate_number VARCHAR(100) UNIQUE,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

2. Prosedur Tersimpan Atomik (Stored Procedures)#

2.1 Eksekusi Investasi Saham Warga (process_crowdfunding_investment)

sql
CREATE OR REPLACE FUNCTION public.process_crowdfunding_investment(
 p_tenant_id UUID,
 p_campaign_id UUID,
 p_investor_nik VARCHAR(16),
 p_investor_name VARCHAR(150),
 p_share_count INT,
 p_escrow_ref VARCHAR(100)
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_campaign RECORD;
 v_total_cost NUMERIC(15,2);
 v_new_collected NUMERIC(15,2);
 v_cert_no VARCHAR(100);
BEGIN
 -- 1. Validasi Kampanye Aktif
 SELECT * INTO v_campaign 
 FROM public.crowdfunding_campaigns 
 WHERE id = p_campaign_id AND tenant_id = p_tenant_id AND status = 'active'
 FOR UPDATE;

 IF NOT FOUND THEN
 RAISE EXCEPTION 'Kampanye crowdfunding tidak ditemukan atau belum aktif.';
 END IF;

 -- 2. Validasi Kuota Lembar Saham Tersedia
 IF (v_campaign.shares_sold + p_share_count) > v_campaign.total_shares THEN
 RAISE EXCEPTION 'Jumlah lembar saham melebihi kuota sisa yang tersedia.';
 END IF;

 v_total_cost := p_share_count * v_campaign.price_per_share;
 v_new_collected := v_campaign.collected_amount + v_total_cost;
 v_cert_no := 'SRT-SHM/' || v_campaign.campaign_code || '/' || LPAD((v_campaign.shares_sold + 1)::TEXT, 4, '0');

 -- 3. Catat Investasi Saham
 INSERT INTO public.crowdfunding_investments (
 tenant_id, campaign_id, investor_nik, investor_name, 
 share_count, total_amount, escrow_payment_ref, certificate_number
 ) VALUES (
 p_tenant_id, p_campaign_id, p_investor_nik, p_investor_name, 
 p_share_count, v_total_cost, p_escrow_ref, v_cert_no
 );

 -- 4. Update Saldo Kampanye
 UPDATE public.crowdfunding_campaigns
 SET 
 collected_amount = v_new_collected,
 shares_sold = shares_sold + p_share_count,
 status = CASE 
 WHEN v_new_collected >= target_amount THEN 'funded' 
 ELSE status 
 END,
 updated_at = NOW()
 WHERE id = p_campaign_id;

 RETURN jsonb_build_object(
 'success', true,
 'certificate_number', v_cert_no,
 'total_invested', v_total_cost,
 'new_shares_sold', v_campaign.shares_sold + p_share_count
 );
END;
func;

3. Kebijakan Keamanan Row Level Security (RLS)#

1. Akses Data Spasial Lahan (village_land_parcels):

• Seluruh warga dan publik dapat membaca (SELECT) koordinat poligon batas lahan yang berstatus available atau leased.
• Modifikasi (INSERT, UPDATE, DELETE) dibatasi hanya untuk staf Unit Potensi Desa terotentikasi.

2. Kerahasiaan Data Investasi Saham (crowdfunding_investments):

• Investor warga hanya dapat melihat data investasi miliknya sendiri berdasarkan pencocokan NIK pengguna yang sedang login.
• Manajemen BUMDes memegang hak akses agregat untuk keperluan audit dan pembagian dividen.