Arsitektur Teknis Sistem Manajemen Aset & Sewa Alsintan (UNT-ASET)

1. Diagram Relasi Entitas (ERD) Modul Manajemen Aset#

+---------------------------+ +---------------------------+
| assets_registry | | assets_categories |
| (Katalog Master 100% Aset)|<-------| (Tanah, Alsintan, Mesin) |
+---------------------------+ +---------------------------+
 | |
 | +-----------------------+
 v v
+-------------------------------+ +-------------------------------+
| assets_depreciation_schedules | | assets_rentals |
| (Jadwal Penyusutan Bulanan) | | (Transaksi Sewa Petani & SPK) |
+-------------------------------+ +-------------------------------+
 | |
 v v
+-------------------------------+ +-------------------------------+
| assets_maintenance_logs | | assets_location_history |
| (Riwayat Servis & Hour Meter) | | (Mutasi Ruang & Pengguna Unit)|
+-------------------------------+ +-------------------------------+

2. Skema Basis Data Relasional (DDL SQL)#

sql
-- 1. Master Katalog Aset Tetap Desa
CREATE TABLE assets_registry (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-ASET',
 asset_code VARCHAR(50) UNIQUE NOT NULL, -- Contoh: AST-ALS-2026-004
 name VARCHAR(150) NOT NULL,
 category VARCHAR(50) NOT NULL, -- 'land', 'building', 'machinery', 'heavy_equipment', 'vehicle', 'office_equipment'
 acquisition_date DATE NOT NULL,
 acquisition_cost NUMERIC(15,2) NOT NULL,
 salvage_value NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 useful_life_months INTEGER NOT NULL, -- Contoh: 96 bulan (8 tahun)
 current_book_value NUMERIC(15,2) NOT NULL,
 condition VARCHAR(30) NOT NULL DEFAULT 'good', -- 'good', 'minor_damage', 'heavy_damage', 'scrapped'
 assigned_unit VARCHAR(50) NOT NULL, -- Entitas pemegang fisik (misal UNT-TANI, UNT-TPS3R, UNT-ASET)
 is_rentable BOOLEAN DEFAULT FALSE, -- Dapat disewakan ke umum / unit lain
 current_hour_meter NUMERIC(10,2) DEFAULT 0.00,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Jadwal & Eksekusi Penyusutan Bulanan
CREATE TABLE assets_depreciation_schedules (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 asset_id UUID REFERENCES assets_registry(id) ON DELETE CASCADE NOT NULL,
 period_month INTEGER NOT NULL,
 period_year INTEGER NOT NULL,
 monthly_depreciation NUMERIC(15,2) NOT NULL,
 accumulated_depreciation NUMERIC(15,2) NOT NULL,
 net_book_value NUMERIC(15,2) NOT NULL,
 is_posted BOOLEAN DEFAULT FALSE,
 posted_at TIMESTAMPTZ,
 UNIQUE(asset_id, period_month, period_year)
);

-- 3. Transaksi Penyewaan Alsintan & Pemakaian Internal
CREATE TABLE assets_rentals (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-ASET',
 rental_number VARCHAR(50) UNIQUE NOT NULL, -- Contoh: RNT-ALS-2026-089
 asset_id UUID REFERENCES assets_registry(id) NOT NULL,
 renter_type VARCHAR(30) NOT NULL, -- 'external_citizen', 'farmer_group', 'internal_unit'
 renter_identifier VARCHAR(100) NOT NULL, -- NIK warga atau Kode Unit (UNT-TANI)
 renter_name VARCHAR(150) NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 work_area_hectares NUMERIC(6,2), -- Luas lahan bajak sawah (opsional)
 rate_per_unit NUMERIC(15,2) NOT NULL, -- Tarif per hektar atau per hari
 total_cost NUMERIC(15,2) NOT NULL,
 payment_status VARCHAR(30) NOT NULL DEFAULT 'pending', -- 'pending', 'down_payment', 'settled'
 rk_transaction_id UUID, -- Tautan RK jika pemakaian internal
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Log Perawatan & Servis Berkala
CREATE TABLE assets_maintenance_logs (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 asset_id UUID REFERENCES assets_registry(id) ON DELETE CASCADE NOT NULL,
 maintenance_date DATE NOT NULL,
 hour_meter_reading NUMERIC(10,2) NOT NULL,
 service_type VARCHAR(50) NOT NULL, -- 'routine_service', 'breakdown_repair', 'overhaul'
 total_cost NUMERIC(15,2) NOT NULL,
 parts_replaced TEXT,
 mechanic_name VARCHAR(100) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Stored Procedure Perhitungan Penyusutan Bulanan Otomatis#

Fungsi calculate_and_post_monthly_depreciation menghitung depresiasi garis lurus seluruh aset aktif dan mencatat nilai buku baru secara atomik:

sql
CREATE OR REPLACE FUNCTION calculate_and_post_monthly_depreciation(
 p_period_month INTEGER,
 p_period_year INTEGER
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_asset RECORD;
 v_monthly_depr NUMERIC(15,2);
 v_accum_depr NUMERIC(15,2);
 v_new_book_value NUMERIC(15,2);
 v_total_posted_depr NUMERIC(15,2) := 0;
 v_count INTEGER := 0;
BEGIN
 FOR v_asset IN (
 SELECT id, acquisition_cost, salvage_value, useful_life_months, current_book_value
 FROM assets_registry
 WHERE category NOT IN ('land') 
 AND condition != 'scrapped'
 AND current_book_value > salvage_value
 )
 LOOP
 -- Hitung penyusutan garis lurus bulanan
 v_monthly_depr := (v_asset.acquisition_cost - v_asset.salvage_value) / v_asset.useful_life_months;

 -- Pastikan nilai buku tidak turun di bawah nilai residu
 IF (v_asset.current_book_value - v_monthly_depr) < v_asset.salvage_value THEN
 v_monthly_depr := v_asset.current_book_value - v_asset.salvage_value;
 END IF;

 IF v_monthly_depr > 0 THEN
 v_new_book_value := v_asset.current_book_value - v_monthly_depr;
 v_accum_depr := v_asset.acquisition_cost - v_new_book_value;

 -- Simpan ke jadwal penyusutan
 INSERT INTO assets_depreciation_schedules (
 asset_id, period_month, period_year, monthly_depreciation, 
 accumulated_depreciation, net_book_value, is_posted, posted_at
 )
 VALUES (
 v_asset.id, p_period_month, p_period_year, v_monthly_depr,
 v_accum_depr, v_new_book_value, TRUE, NOW()
 )
 ON CONFLICT (asset_id, period_month, period_year) DO UPDATE
 SET monthly_depreciation = EXCLUDED.monthly_depreciation,
 net_book_value = EXCLUDED.net_book_value,
 posted_at = NOW();

 -- Update nilai buku aset
 UPDATE assets_registry
 SET current_book_value = v_new_book_value,
 updated_at = NOW()
 WHERE id = v_asset.id;

 v_total_posted_depr := v_total_posted_depr + v_monthly_depr;
 v_count := v_count + 1;
 END IF;
 END LOOP;

 RETURN jsonb_build_object(
 'success', true,
 'assets_processed', v_count,
 'total_depreciation_amount', v_total_posted_depr,
 'period', format('%s-%s', p_period_year, p_period_month)
 );
END;
func;

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

sql
ALTER TABLE assets_registry ENABLE ROW LEVEL SECURITY;
ALTER TABLE assets_rentals ENABLE ROW LEVEL SECURITY;
ALTER TABLE assets_maintenance_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY rls_assets_access ON assets_registry
 FOR ALL
 USING (
 auth.jwt() ->> 'role' IN ('super_admin', 'direktur', 'admin_keuangan') OR
 auth.jwt() ->> 'unit_id' = 'UNT-ASET'
 );