# AIR SDA - Dokumentasi Database

PostgreSQL 14+ dengan ekstensi PostGIS 3.x. Skema dirancang untuk skala besar (>100 petugas, multi-wilayah) dengan dukungan multi-tenant, audit trail, dan keamanan baris (Row-Level Security).

## Diagram Entitas Tingkat Tinggi

Organisasi adalah akar multi-tenant. Setiap organisasi memiliki banyak pengguna dengan role berbeda. Daerah Irigasi dan Bangunan terikat ke organisasi. Bangunan terhubung ke jenis (master) dan ke daerah irigasi serta wilayah administratif. Riwayat inspeksi, foto, dan layer GIS terkait ke bangunan atau organisasi. Aktivitas seluruh sistem dicatat di tabel audit terpisah dengan partisi tahunan.

## Daftar Tabel Inti

**organisasi** menyimpan informasi instansi seperti BBWS, PUPR, Dinas SDA, atau kelompok GP3A. Setiap data lain terikat melalui kolom organisasi_id sehingga isolasi antar organisasi dijamin oleh Row-Level Security policy. Tipe organisasi dibatasi pada enum BBWS, PUPR_PROV, PUPR_KAB, DINAS_SDA, GP3A, atau OTHER.

**pengguna** berisi data akun login dengan hash bcrypt pada kolom password_hash. Setiap pengguna terikat ke satu organisasi dan satu role. Field wilayah_kerja bertipe JSONB array berisi ID wilayah yang menjadi scope kerja petugas (misal: petugas lapangan hanya melihat data di kecamatan tertentu).

**role dan permission** membentuk sistem RBAC fleksibel. Role default yang disediakan: SUPERADMIN (level 100), ADMIN_ORG (90), SUPERVISOR (50), OPERATOR (30), PETUGAS_LAPANGAN (10), VIEWER (5). Permission dipetakan ke role melalui tabel pivot role_permission sehingga akses dapat diatur granular per modul (bangunan.create, inspeksi.approve, dan sebagainya).

**sesi_token** menyimpan refresh token aktif dengan hashing SHA-256. Mendukung logout dari satu perangkat (revoke) tanpa mempengaruhi sesi lain, plus tracking IP dan device_info untuk audit.

**wilayah** adalah hirarki Provinsi → Kabupaten → Kecamatan → Desa dengan kode standar BPS. Setiap baris memiliki kolom geom (MULTIPOLYGON) dan centroid (POINT) PostGIS untuk visualisasi peta.

**daerah_irigasi** menyimpan master DI dengan kode unik per organisasi, luas potensial/fungsional/baku, kewenangan (Pusat/Provinsi/Kabupaten/Desa), dan area cakupan sebagai MULTIPOLYGON.

**kategori_bangunan dan jenis_bangunan** adalah master untuk klasifikasi. Kategori utama: BANGUNAN (point), SALURAN (linestring), PETAK (polygon). Setiap kategori punya warna default yang bisa di-override oleh preferensi pengguna.

**bangunan** adalah tabel utama dengan kolom geom bertipe GEOMETRY(GEOMETRY, 4326) yang dapat menyimpan point, linestring, atau polygon. Kolom centroid dihitung otomatis menggunakan GENERATED ALWAYS AS untuk mempercepat query peta. Field atribut_extra bertipe JSONB menampung atribut dinamis (misalnya dari hasil import shapefile dengan field non-standar). Indeks GIST pada geom dan centroid memungkinkan query spasial cepat sampai jutaan baris.

**inspeksi** mencatat riwayat inspeksi dengan workflow approval (DRAFT → SUBMITTED → APPROVED/REJECTED). Trigger update_kondisi_bangunan_dari_inspeksi otomatis memperbarui kondisi terkini bangunan saat status berubah ke APPROVED.

**foto** menyimpan metadata file foto: path lokal/S3, dimensi, GPS location dengan akurasi, EXIF, dan referensi ke bangunan atau inspeksi. Mendukung baik foto pendukung bangunan maupun foto khusus per inspeksi.

**layer_gis** menyimpan layer geospasial yang diupload pengguna: shapefile (.zip), GeoTIFF (.tif), atau referensi ke tile service XYZ/WMS/WMTS/LTS. Field bounds menyimpan extent untuk auto-zoom dan filter wilayah.

**preferensi_pengguna** menyimpan preferensi UI per akun: warna kategori (JSONB), mode warna default, basemap default, bahasa, tema gelap/terang, dan opt-in notifikasi.

**notifikasi** untuk reminder inspeksi yang jatuh tempo, kondisi kritis baru ditemukan, dan approval yang menunggu.

**airsda_audit.activity_log** dipartisi per tahun untuk performa. Setiap aksi penting (CREATE, UPDATE, DELETE, LOGIN, EXPORT) tercatat dengan diff data_lama vs data_baru dalam format JSONB.

## Fitur PostGIS yang Digunakan

Geometri menggunakan SRID 4326 (WGS84) sebagai standar. Untuk perhitungan jarak yang akurat, query menggunakan cast ke geography pada fungsi ST_Distance dan ST_DWithin. Indeks GIST dipasang pada semua kolom geometri. Fungsi cari_bangunan_radius mendemonstrasikan cara mencari bangunan dalam radius tertentu dari titik koordinat dengan optimasi geography.

Generated columns digunakan untuk centroid agar tidak perlu menghitung ulang saat query (otomatis terupdate saat geom berubah). View v_bangunan_lengkap menyediakan tampilan join lengkap untuk konsumsi API tanpa boilerplate query.

## Multi-Tenant Isolation

Row-Level Security diaktifkan pada tabel sensitif (bangunan, daerah_irigasi, inspeksi, foto, layer_gis). Policy memeriksa setting session app.current_organisasi_id terhadap kolom organisasi_id pada setiap baris. Backend wajib menjalankan SET LOCAL app.current_organisasi_id pada setiap transaksi sebelum query agar data terisolasi otomatis bahkan jika ada bug aplikasi.

## Strategi Skalabilitas

Tabel audit_log sudah dipartisi per tahun. Untuk tabel bangunan jika data melebihi 10 juta baris, dapat dipartisi RANGE pada created_at (skema sudah disiapkan, tinggal di-uncomment). Connection pool minimal 2 dan maksimal 20 koneksi mengikuti standar PgBouncer. Untuk skala >100 user concurrent disarankan menambah PgBouncer di depan PostgreSQL untuk multiplexing koneksi.

Indeks yang penting: GIN pada nama (full-text trigram search), GIN pada atribut_extra (query JSONB), GIST pada geom dan centroid (query spasial), partial index pada deleted_at IS NULL untuk mempercepat filter soft-delete.

## Backup dan Recovery

Disarankan menggunakan pg_basebackup untuk full backup mingguan, WAL archiving untuk point-in-time recovery, dan logical backup harian via pg_dump untuk arsip jangka panjang. Snapshot file storage (uploads) terpisah dari database backup. Untuk produksi multi-wilayah, gunakan replikasi streaming ke node read-only di lokasi geografis berbeda.