Data Model & Persistence Architecture (HUB-DATA-001)
| Field | Value |
|---|---|
| Document ID | HUB-DATA-001 |
| Pillar | Data |
| Standard Alignment | DAMA-DMBOK Data Modeling & Persistence Standard |
| Status | Active / Synchronized with Codebase |
| Last Updated | 2026-11-21 |
| Source of Truth | supabase/schema_complete.sql, supabase/migrations/20261024_secure_auth.sql through supabase/migrations/20261130080000_kerapian_kamar_review.sql, scripts/generate-master-sql.mjs, src/store/portal.js, src/lib/supabase.js, src/lib/offlineQueue.js, src/lib/attendancePhotoCache.js, src/lib/operasionalService.js, src/lib/perizinanService.js, src/lib/poldikService.js, src/lib/pengasuhReportsService.js, src/views/HelpCenterView.vue |
1. Schema Overview & Domain Topology
Section titled “1. Schema Overview & Domain Topology”Soedirman Hub partitions PostgreSQL storage across three schemas, totaling 67 tables and 1 security-barrier view across supabase/schema_complete.sql and 39 sequential SQL migrations:
public(36 tables + 1 view): Core domain entities, masked cohort directory (public.portal_serdik_directory), JSONB operational tables, Corps 17-unit & Pengasuh 21-instructor reporting tables, notifications, and command feeds.portal_private(30 tables): Hidden internal schema completely revoked fromPUBLIC,anon, andauthenticatedroles—storing session token hashes, rate-limiting buckets, OIDC links, confirmation tokens, onboarding setup progress, delegated scoped grants, IB retention policies, daily self-condition rollouts/consent/entries, mentoring cases, Info Digital intake/evidence, and room cleanliness (kerapian_kamar) workspaces.core_cache(1 table): P3MDI Core Directory synchronization cursor state (core_cache.sync_state) plus triggercore_owned_fieldsprotecting Core-mastered profile columns onpublic.serdik.
For privacy controls, column-level revocations, and RLS policies governing these objects, see SECURITY_AND_PRIVACY.md. For RPC signatures manipulating them, see API_AND_INTERFACES.md.
2. Core Entity Relationship Diagram
Section titled “2. Core Entity Relationship Diagram”erDiagram
SERDIK ||--o{ ABSENSI_APEL : "records"
SERDIK ||--o{ KORPS_PROKER : "owns PIC"
SERDIK ||--o{ DAILY_CONDITION_CONSENT : "grants"
DAILY_CONDITION_CONSENT ||--o{ DAILY_CONDITION_ENTRIES : "submits"
SERDIK ||--o{ KAMAR_MEMBERS : "assigned to"
SERDIK ||--o{ KAMAR_APPOINTMENTS : "appointed commander"
KAMAR_ROOMS ||--o{ KAMAR_MEMBERS : "houses"
KAMAR_ROOMS ||--o{ KAMAR_APPOINTMENTS : "commanded by"
KAMAR_ROOMS ||--o{ KAMAR_SUBMISSIONS : "daily photo slots"
KAMAR_SUBMISSIONS ||--o| KAMAR_SUBMISSION_MEDIA : "stores url"
KAMAR_ROOMS ||--o{ KAMAR_EXEMPTIONS : "exempts"
KAMAR_ROOMS ||--o{ KAMAR_REVIEWS : "verdict"
KORPS_BIDANG ||--o{ KORPS_REPORTS : "daily unit report"
KORPS_BIDANG ||--o{ KORPS_PROKER : "owns work program"
KORPS_BIDANG ||--o{ PENGASUH_KORPS_NOTES : "receives supervision notes"
KORPS_PROKER ||--o{ KORPS_PROKER_PROGRESS : "tracks realizations"
SESSIONS ||--o| SSO_CONFIRMATIONS : "re-auth gate"
PENDAMPINGAN_CASES ||--o{ PENDAMPINGAN_NOTES : "tracks follow-ups"
INFO_DIGITAL_REPORTS ||--o| INFO_DIGITAL_SCREENSHOTS : "EXIF-stripped bytea"
INFO_DIGITAL_REPORTS ||--o{ INFO_DIGITAL_ACTIONS : "audit & status actions"
3. Authoritative Data Dictionary
Section titled “3. Authoritative Data Dictionary”3.1 public Schema (36 Tables + 1 View)
Section titled “3.1 public Schema (36 Tables + 1 View)”Roster, Auth-Backed Profiles & Masked View
Section titled “Roster, Auth-Backed Profiles & Masked View”| Object | Primary Key / Unique | Key Columns & Types | Constraints / Notes |
|---|---|---|---|
public.serdik |
id (UUID PK), nosis (TEXT UK), core_serdik_id (UUID UK) |
nama, jenis_kelamin, asal_institusi, grup, elemen, tempat_lahir, tanggal_lahir (DATE), alamat, telepon, email, role, pin_hash, must_change_pin, core_version, core_is_active |
Plaintext pin dropped (20261024). SELECT on telepon, email, alamat, tanggal_lahir revoked from anon/authenticated (20261116_session_boundaries). Trigger core_owned_fields blocks non-sync edits to Core fields when core_serdik_id IS NOT NULL (20261128). serdik_role_check limits role to 11 recognized values. |
public.portal_serdik_directory (View) |
— | Exposes public.serdik safe fields + birthday (MM-DD) + conditional tanggal_lahir, alamat, email, telepon, core_serdik_id, core_version |
Built with security_barrier = true, security_invoker = false (20261121_restore_serdik_directory_view, updated 20261128). Masks DOB/address/email outside self/super_admin/komandan_batch/kabid_sdm; masks telepon outside command/element scope. |
public.pengasuh |
id (TEXT PK), nrp (TEXT UK) |
nama, pangkat, jabatan, matra, satker, grup, elemen, role, pin_hash, must_change_pin, aliases (TEXT[] used in portal_login) |
Plaintext pin dropped (20261024). Default role 'pengasuh'. |
Attendance, Schedule & Batch Feeds
Section titled “Attendance, Schedule & Batch Feeds”| Table | Key(s) | Key Columns & Constraints | Lifecycle / Notes |
|---|---|---|---|
public.absensi_apel |
id (UUID PK), UNIQUE(tanggal, sesi, nosis) |
tanggal (DATE), sesi (pagi|malam), grup, elemen, nosis (FK serdik.nosis CASCADE), status (hadir|sakit|izin|dinas_luar), keterangan, diinput_oleh, sampai, first_submitted_at, status_recorded_at, updated_at |
Trigger stamp_attendance_update preserves first_submitted_at and tracks status changes. Composite index (tanggal, updated_at). |
public.absensi_lock |
id (UUID PK), UNIQUE(tanggal, sesi, grup) |
tanggal, sesi (pagi|malam), grup, locked (BOOLEAN), verified_by, verified_at |
Enforces post-verification lock evaluated by portal_private.is_absensi_locked(). |
public.rundown_harian |
tanggal (DATE PK) |
tipe_hari, outfit, catatan, items (JSONB array), updated_by, updated_at |
Batch daily agenda & OOTD template. |
public.notulensi_apel |
id (TEXT PK) |
tanggal, judul, kategori, scope, lokasi, dibuat_oleh, pembuat_nama, pembuat_elemen, isi, created_at |
Apel & instructor briefing minutes. |
public.sosiometri |
id (TEXT PK) |
tanggal, nosis_target, nosis_pelapor, jenis (positif), judul, deskripsi, bukti_foto_url, created_at |
Positive peer appreciation entries; gated via fail-closed portal_private.can_view_sosiometri(). |
public.events |
id (TEXT PK) |
judul, deskripsi, kategori, waktu_mulai, lokasi, dibuat_oleh, peserta (JSONB) |
Historical batch calendar events table. |
public.dokumentasi |
id (TEXT PK) |
judul, tanggal, kategori, foto_url, uploaded_by, uploader_nama |
Drive photo metadata catalog; SELECT gated to penerangan, komandan_batch, super_admin (20261027). |
public.saran |
id (TEXT PK) |
tanggal, kategori, judul, isi, is_anonim, pengirim_nosis, pengirim_nama, status, tanggapan_lemusdik, upvotes (JSONB) |
WBS & Lemusdik aspiration submissions. Frontend uses is_anonymous/pesan/pengirim/tanggapan mapping safely alongside strict RLS isolation for anonymous reports. |
public.commander_intents |
id (TEXT PK) |
tanggal, judul (1..200), minggu, tahap, prioritas (Krusial|Standar), ringkasan (<=2000), poin_arahan (JSONB array), catatan_khusus (<=1000), pemberi, jabatan, dibuat_oleh, diperbarui_oleh, updated_at |
Trigger stamp_commander_intent stamps author/timestamp server-side (20261025). |
public.rabuan_sessions |
id (TEXT PK) |
tanggal, minggu, judul, narasumber, lokasi, dresscode, ringkasan, poin (JSONB array), image, dibuat_oleh, diperbarui_oleh, updated_at |
Trigger stamp_rabuan_session enforces author stamps (20261028, image added 20261030). |
JSONB Operational Persistence & Notifications (20261116_operational_persistence.sql)
Section titled “JSONB Operational Persistence & Notifications (20261116_operational_persistence.sql)”Ten tables sharing id TEXT PRIMARY KEY, owner_code, owner_type, payload JSONB (plus public.notifications), guarded by BEFORE UPDATE trigger stamp_operational_update:
public.pam_reports— Security/lost-found/facility payload (<= 256 KB). Policy20261130060000strictly excludespayload->>'tipe' = 'info_digital'.public.paket_orders— Generated stored columnsgrup,elemen,statusfrompayload; triggersverify_package_transition¬ify_package_statusmanage courier receipt -> picked-up state transitions.public.live_attendance_photos— Payload<= 1.5 MB; generated columnstaken_on(^\d{4}-\d{2}-\d{2}$),session_key(1..32), andmetadata(payload - 'photoUrl' - 'gps' - 'locationName'after20261130030000). Primary key format strictly verified astaken_on || '__' || session_key || '__' || owner_code.20261130030000attachesvalid_photo_gps(payload)(NOT VALIDcheck constraint preserving historical rows).public.apel_summaries— Generatedtanggal,sesi,grup.public.rabuan_articles— Student-written Wednesday summaries & review metadata (<= 256 KB).public.kelas_notes— Classroom learning notes (<= 256 KB).public.calendar_events— Cohort calendar entries (<= 256 KB).public.calendar_day_notes— Day-level annotations (<= 256 KB).public.audit_logs— Operational client audit log payloads (<= 8 KB).public.notifications— Composite PK(id, recipient_code, recipient_type), server-written via triggers/portal_refresh_daily_notifications(), client onlySELECT&UPDATE (read_at).
Hierarchical Corps & Pengasuh Governance (20261118–20261124, 20261130070000)
Section titled “Hierarchical Corps & Pengasuh Governance (20261118–20261124, 20261130070000)”| Table | Key(s) | Key Columns & Constraints | Notes |
|---|---|---|---|
public.korps_bidang |
id (SMALLINT PK 1..17) |
nama (1..100), kasi (TEXT[]), kabid (TEXT) |
Constraint kabid IS NULL OR NOT kabid = ANY(kasi). Units 1–9 are functional divisions; 10–17 are BPI, Lemusdik, Polisi Serdik, Pembinaan Karakter, and Grup 1–4. |
public.korps_bpi_reviewers |
nosis (TEXT PK) |
nosis |
Seeded BPI final approvers. |
public.korps_reports |
PK (tanggal, bidang) |
bidang_nama, status (draft|kabid_pending|bpi_pending|returned|final), kegiatan, kendala, tindak_lanjut, overrides (JSONB), kekuatan (JSONB), submitted_at, lampiran (JSONB max 20 evidence refs), created_by, submitted_by, checked_by, finalized_by, finalized_at, version, events (JSONB) |
Optimistic locking via version. Deadline checked at 21:30 WIB (korps_deadline_time()). |
public.korps_proker |
id (BIGINT IDENTITY PK) |
bidang (FK), judul, satuan, target (> 0), periode (sekali|mingguan|bulanan), pic_nosis (FK serdik.nosis), tenggat, version, events |
Divisional & scoped work programs. |
public.korps_proker_progress |
PK (proker, mulai) |
realisasi (>= 0), target (> 0), version |
Period-based progress tracking. |
public.pengasuh_reports |
PK (tanggal, unit_id) (20261130070000) |
unit_nama, tingkat (elemen|grup|kasi|batch), grup, elemen, status (draft|dangrup_pending|danbatch_pending|returned|final), skor_kesiapan (1..5), kondisi_disiplin, kesehatan_mental_fisik, kesiapan_akademik_latihan, temuan_barak_apel, arahan_tindak_lanjut, serdik_perhatian (JSONB array), submitted_by/at, checked_by, finalized_by/at, version, events |
Hierarchical daily supervision reports across 21 TNI instructors (Elemen -> Grup -> Kasi -> Batch). |
public.pengasuh_korps_notes |
id (TEXT PK) |
tanggal, bidang (FK korps_bidang.id), pengasuh_code, pengasuh_nama, pengasuh_jabatan, prioritas (Atensi|Penting|Apresiasi), isi (1..1500) |
Supervisory directives pushed to 17 Corps units. |
Legacy / Dual-Persisted SQL Tables (20261116_perizinan_ib.sql, 20261116_poldik_operasional.sql)
Section titled “Legacy / Dual-Persisted SQL Tables (20261116_perizinan_ib.sql, 20261116_poldik_operasional.sql)”public.izin_keluar(idUUID PK, 3-tiercurrent_tierdanel|dangrup|kabid_sdm|selesai,status7 values) andpublic.izin_bermalam_checkins(idUUID PK, nullablelatitude/longitude/foto_selfie_url+evidence_removed_atadded in20261130020000,slot_number1..4,status_verifikasi). Note: FrontendperizinanService.jspersistsizin_keluarinlocalStorage(p3md_perizinan_data_v1), whereas IB check-ins and 168-hour evidence purging run live throughportal_ib_*SECURITY DEFINERRPCs (20261130020000).public.catatan_disiplin,public.proker_korps,public.notulensi_pic_tasks,public.pengumuman_korps,public.gform_trackers: Initial schema created in20261116_poldik_operasional.sql, while active frontend read/write modules (poldikService.js,operasionalService.js) maintainlocalStoragestate keys (p3md_poldik_disiplin_v1,p3md_operasional_*_v1).
3.2 portal_private & core_cache Schemas (31 Internal Tables)
Section titled “3.2 portal_private & core_cache Schemas (31 Internal Tables)”| Schema & Table | Purpose & Keys | Constraints / Lifecycle |
|---|---|---|
portal_private.sessions |
Auth sessions (token_hash SHA-256 PK, user_code, user_type, expires_at, preview_role) |
12-hour TTL; invalidated on logout, PIN change, or SSO session revoke. |
portal_private.login_attempts |
Brute-force throttling (user_code bucket key PK, failed_count, locked_until) |
Tracks both IP bucket (50 failures) and user_type:code|ip (5 failures) with 15-minute lockouts. |
portal_private.p3mdi_identities |
OIDC binding (PK (issuer, subject), UNIQUE (issuer, user_type, user_code), provider_pk, provider_username, linked_at) |
Enforces 1:1 link between Authentik account & Hub identity. |
portal_private.sso_audit |
Identity mutation log (id IDENTITY PK, actor_code, action 7 types, via, target_pk, target_code, detail JSONB) |
Written via portal_sso_audit_log; excludes secrets/passwords. |
portal_private.account_setup |
First-login onboarding progress (PK (user_type, user_code), requires_sso, pin_saved, password_saved, email_saved, completed_at) |
Guards guided onboarding progression cleanly without resetting prior stages. |
portal_private.sso_confirmations |
Fresh re-auth gate (token_hash FK sessions PK, provider_pk, expires_at) |
5-minute TTL enforced before sensitive SSO mutations. |
portal_private.scoped_grants |
Delegated time-bounded authority (id UUID PK, subject, capability, scope_type, scope_key, issuer, delegated, starts_at < ends_at, revoked_at) |
Enforces subject <> issuer, non-overlapping validity windows, and revocation tracking. |
portal_private.ib_periods, ib_retention_settings, ib_retention_history |
IB weekend/period windows, singleton enabled toggle (default false), and retention execution audit |
Drives 7-day (168h) evidence scrub after ends_at (pg_cron or manual run). |
portal_private.daily_condition_rollout, daily_condition_consent, daily_condition_entries |
Self-check-in rollout (Grup 2), versioned consent (1|2, peer), daily entries (PK (nosis, tanggal, slot), fisik/psikologis 1..5, catatan only when score <= 2) |
Self-only privacy model; scores & notes never exposed via directory or general boards. |
portal_private.pendampingan_cases, pendampingan_notes, pendampingan_deletion_audit |
Mentoring cases (id UUID PK, creation_key idempotency, revision, subject_id, awal, tingkat, kebutuhan, tindakan, selesai), chronological notes (PK (case_id, id)), deletion log |
Partial unique index limits 1 active (selesai IS NULL) case per subject_id. |
portal_private.info_digital_paminfo, verified_handles, quota, reports, screenshots, actions |
Designated Paminfo scopes, normalized social handles, 5/day WIB reporter quota, incident reports (status baru|ditinjau|selesai|ditolak), EXIF-stripped JPEG bytea (<= 2.1 MB), idempotent action log (PK (report_id, operation_id)) |
Reporter identity is never stored alongside info_digital_reports (quota stores reporter_nosis independently per wib_day without report FK). |
portal_private.kamar_rooms, kamar_members, kamar_appointments, kamar_submissions, kamar_submission_media, kamar_exemptions, kamar_reviews, kamar_operation_receipts |
Room roster (incl. mixed-element rooms), Komandan Kamar appointments, 3 daily photo slots (kamar_mandi, tempat_tidur, pintu_rak_sepatu, 14-day expires_at), media URLs, exemptions, verdicts (belum_ditinjau|sesuai|perlu_perbaikan, revision), and idempotency receipts (PK (actor_code, operation_id)) |
Mixed-element visibility evaluated via room occupant intersection across elements. |
core_cache.sync_state |
Core synchronization cursor (app PK 'hub', cursor, last_success_at, last_count) |
Advanced monotonically by portal_core_cache_apply. |
4. Core State Machines
Section titled “4. Core State Machines”4.1 3-Tier Izin Keluar Approval & Return Lifecycle
Section titled “4.1 3-Tier Izin Keluar Approval & Return Lifecycle”stateDiagram-v2
[*] --> menunggu_danel : "Submitted (current_tier = danel)"
menunggu_danel --> menunggu_dangrup : "Danel Approves (tier = dangrup)"
menunggu_danel --> ditolak : "Danel Rejects (+ reason)"
menunggu_dangrup --> menunggu_kabid_sdm : "Dangrup Approves (tier = kabid_sdm)"
menunggu_dangrup --> ditolak : "Dangrup Rejects (+ reason)"
menunggu_kabid_sdm --> disetujui : "Kabid SDM / Admin Approves (tier = selesai)"
menunggu_kabid_sdm --> ditolak : "Kabid SDM Rejects (+ reason)"
disetujui --> kembali : "Lapor Kembali (+ waktu_kembali_aktual)"
disetujui --> lewat : "Past waktu_kembali_rencana"
4.2 17-Unit Laporan Korps Hierarchical Review Flow (20261118 + 20261121)
Section titled “4.2 17-Unit Laporan Korps Hierarchical Review Flow (20261118 + 20261121)”stateDiagram-v2
[*] --> draft : "Kasi / Maker Saves Draft"
draft --> kabid_pending : "submit (+ submitted_at vs 21:30 WIB)"
kabid_pending --> bpi_pending : "Kabid / Checker Approves"
kabid_pending --> returned : "Kabid Returns with Note"
bpi_pending --> final : "BPI / Danbatch / Admin Finalizes"
bpi_pending --> returned : "BPI Returns with Note"
returned --> kabid_pending : "Maker Revises & Resubmits"
(Similarly, public.pengasuh_reports follows draft -> dangrup_pending -> danbatch_pending -> final, with returned loopback at either approval tier.)
4.3 Guided First-Login Account Setup (20261126_first_login_setup.sql)
Section titled “4.3 Guided First-Login Account Setup (20261126_first_login_setup.sql)”- Account enters
must_change_pin = true-> router locks navigation to/ganti-pin(ForcePinView->AccountSetupModal). portal_setup_pinupdates bcrypt PIN hash (must_change_pinremainstrueuntil finish), settingpin_saved = true.- If
requires_sso = true, user must sequentially completestep: 'password'(12–128 chars via Authentik Core API) ->password_saved = true, thenstep: 'email'->email_saved = true. portal_setup_finishvalidates all required flags (pin_saved, pluspassword_saved&email_savedif SSO-linked) and flipsmust_change_pin = falseatomically, unlockingportal_private.current_session().
5. Client-Side Browser Persistence Inventory
Section titled “5. Client-Side Browser Persistence Inventory”| Storage Engine | Key / Identifier | Contents & Retention / Cleanup Policy |
|---|---|---|
localStorage |
p3md_portal_vue_state_v6 |
Cached non-PII cohort feeds, attendance range watermarks (attendanceLoadedRange), notification counts; photo payloads & PII explicitly stripped via stripPhotoPayloads(). Wiped on logout. Legacy keys (p3md_portal_vue_state_v5, p3md_login_attempts_v1) auto-purged. |
localStorage |
p3md_portal_session_v1 |
{ nosis, role, realRole, loginAt, token } with 12h TTL (SESSION_TTL_MS). Cleared on expiry or logout. |
localStorage |
soedirman-outbox-v1:<scope>:<uuid> |
Queued offline mutations (offlineQueue.js, max 8 retries). Wiped on logout (purgeBrowserCaches()). |
localStorage |
p3md_operasional_*_v1, p3md_perizinan_data_v1, p3md_poldik_disiplin_v1, p3md_pengasuh_reports_v1, p3md_pengasuh_korps_notes_v1 |
Local module stores for Ops, Perizinan (izin_keluar), Poldik, and Pengasuh fallback drafts. Cleared on logout where listed in USER_CACHE_KEYS. |
localStorage |
p3md_gmaps_api_key, portal_sidebar_collapsed, p3md_help_center_tickets_v1 |
User-entered Maps API key (included in USER_CACHE_KEYS), sidebar UI preference, Help Center local tickets. |
sessionStorage |
soedirman_demo_mode_v1, soedirman_admin_role_preview_v1, soedirman_chunk_reload, p3mdi-sso-transaction, p3mdi-sso-link-callback, p3mdi-sso-security-callback |
Demo persona state, Super Admin role preview (realRole/role), single-shot Vite chunk reload guard, ephemeral PKCE S256 verifier/state/nonce transactions (10-min window, removed immediately on read). |
IndexedDB |
DB soedirman-attendance-photos (v1), store sessions |
Attendance selfie/photo blobs under granular ::photo::<session>::<nosis> keys + lightweight ::index metadata map. Purged on logout (clearAttendancePhotos()). |
6. Ordered Migration Index (All 39 Migrations)
Section titled “6. Ordered Migration Index (All 39 Migrations)”Every SQL file in supabase/migrations/ is idempotent where intended and must be applied chronologically:
| # | Migration File | Summary of Schema / Policy Changes |
|---|---|---|
| 1 | 20261024_secure_auth.sql |
Enables pgcrypto; drops plaintext pin; adds pin_hash + must_change_pin; creates portal_private.sessions & login_attempts; adds portal_login, portal_logout, portal_me, portal_change_pin, portal_admin_set_pin; enables RLS across base tables. |
| 2 | 20261025_komandan_batch.sql |
Adds komandan_batch to serdik_role_check; creates public.commander_intents + stamp_commander_intent trigger + RLS. |
| 3 | 20261026_defect_fixes.sql |
Drops legacy broken policies referencing removed portal_sessions; aligns rundown_harian, notulensi_apel, saran policies. |
| 4 | 20261026_merge_penerangan.sql |
Merges dokumentasi role into penerangan and tightens serdik_role_check. |
| 5 | 20261027_dokumentasi_akses.sql |
Restricts public.dokumentasi SELECT to penerangan, komandan_batch, super_admin; DELETE to super_admin or own uploader. |
| 6 | 20261028_learning_rabuan.sql |
Creates public.rabuan_sessions + stamp_rabuan_session trigger + RLS. |
| 7 | 20261029_absensi_pribadi.sql |
Restricts absensi_apel SELECT (can_view_all_absensi() or own nosis), enforces is_absensi_locked(), adds portal_my_absensi_summary(). |
| 8 | 20261030_learning_rabuan_image.sql |
Adds image column & grants to public.rabuan_sessions. |
| 9 | 20261115_security_rls_hardening.sql |
Hardens fail-closed can_view_sosiometri() & is_absensi_window_open() (Danel 05:00-08:00 morning / 19:00-22:00 evening WIB). |
| 10 | 20261116_attendance_sync.sql |
Adds stamp_attendance_update trigger & (tanggal, updated_at) index on absensi_apel. |
| 11 | 20261116_attendance_workflow.sql |
Adds sampai, first_submitted_at, status_recorded_at to absensi_apel; adds portal_attendance_exemptions, portal_correct_attendance, portal_my_absensi_streak. |
| 12 | 20261116_operational_persistence.sql |
Adds 9 JSONB operational tables + public.notifications, package state triggers (verify_package_transition, notify_package_status), and rundown_harian DELETE RLS. |
| 13 | 20261116_perizinan_ib.sql |
Creates public.izin_keluar and public.izin_bermalam_checkins + initial RLS policies. |
| 14 | 20261116_poldik_operasional.sql |
Creates catatan_disiplin, proker_korps, notulensi_pic_tasks, pengumuman_korps, gform_trackers tables. |
| 15 | 20261116_session_boundaries.sql |
Splits session_identity() from current_session() (requires NOT must_change_pin); adds IP (cf-connecting-ip) + account rate limits (50/IP, 5/account+IP); adds portal_admin_unlock_login; revokes direct PII SELECT on serdik; creates portal_serdik_directory. |
| 16 | 20261117_daily_notifications.sql |
Refreshes live_attendance_photos.metadata projection & adds portal_refresh_daily_notifications(). |
| 17 | 20261117_live_photo_review_scope.sql |
Adds can_review_scoped_photo() so komandan_grup / komandan_elemen can SELECT scoped live photos. |
| 18 | 20261117_p3mdi_sso.sql |
Creates portal_private.p3mdi_identities & portal_login_p3mdi (service_role). |
| 19 | 20261118_korps_reports.sql |
Creates korps_bidang (seeds 1–9), korps_bpi_reviewers, korps_reports, korps_proker, korps_proker_progress + workspace/action RPCs. |
| 20 | 20261119_korps_batch_report.sql |
Seeds korps_bidang 10–17 & adds portal_korps_batch_report. |
| 21 | 20261120_admin_role_switch.sql |
Adds sessions.preview_role, effective_role(), and portal_switch_role(). |
| 22 | 20261121_korps_review_flow.sql |
Adds korps_reports.submitted_at, korps_deadline_time() (21:30), portal_korps_review_queue(), daily Korps notifications. |
| 23 | 20261121_restore_serdik_directory_view.sql |
Restores portal_serdik_directory with explicit security_barrier = true, security_invoker = false & owner alignment (remediating HTTP 42501 sync outage). |
| 24 | 20261122_korps_last_report.sql |
Includes last_report carry-over field in portal_korps_workspace. |
| 25 | 20261123_korps_batch_matrix.sql |
Adds portal_korps_batch_matrix(p_from, p_to). |
| 26 | 20261124_korps_sparkline_evidence.sql |
Adds portal_korps_strength_history & korps_reports.lampiran (max 20 evidence refs). |
| 27 | 20261125_sso_identity_management.sql |
Extends p3mdi_identities (provider_pk, provider_username, linked_at); creates portal_private.sso_audit; adds self-link/unlink/audit RPCs. |
| 28 | 20261126_first_login_setup.sql |
Creates portal_private.account_setup; adds guided onboarding RPCs (portal_setup_*); blocks portal_change_pin while must_change_pin. |
| 29 | 20261127_sso_account_management.sql |
Creates portal_private.sso_confirmations (5m TTL); adds confirmation, directory, binding, reset-setup, session revocation, and username sync RPCs. |
| 30 | 20261128_core_member_cache.sql |
Adds core_serdik_id, core_version, core_is_active to serdik; creates core_cache schema & protect_serdik trigger; extends portal_serdik_directory. |
| 31 | 20261129_core_membership_access.sql |
Patches portal_login to reject accounts with core_is_active = false. |
| 32 | 20261130010000_scoped_duties.sql |
Creates portal_private.scoped_grants & portal_scoped_duties(); integrates kasi & pic delegated grants into Korps workspace/progress. |
| 33 | 20261130020000_ib_retention.sql |
Creates ib_periods, ib_retention_settings, ib_retention_history; makes IB check-in GPS/photo nullable (evidence_removed_at); adds portal_ib_* RPCs & 168h purge logic. |
| 34 | 20261130030000_photo_gps_required.sql |
Adds valid_photo_gps NOT VALID CHECK constraint (accuracy <= 100 m, fix age 0..15000 ms) & strips GPS/location fields from live_attendance_photos.metadata. |
| 35 | 20261130040000_daily_condition.sql |
Creates daily_condition_rollout (Grup 2), consent, entries + portal_daily_condition (pagi 04:00–12:00 / malam >=18:00 WIB). |
| 36 | 20261130050000_pendampingan.sql |
Creates pendampingan_cases, notes, deletion_audit + portal_pendampingan_* RPCs (1 active case per subject). |
| 37 | 20261130060000_info_digital.sql |
Creates info_digital_* tables (Paminfo scopes, verified handles, 5/day WIB quota, anonymous reports, EXIF-stripped JPEG bytea, actions); blocks info_digital on pam_reports; adds portal_info_digital_* & portal_critical_batch_ready(). |
| 38 | 20261130070000_pengasuh_reports_governance.sql |
Creates pengasuh_reports & pengasuh_korps_notes; opens portal_korps_batch_report/matrix to Pengasuh; adds portal_pengasuh_* & portal_pengasuh_release_ready(). |
| 39 | 20261130080000_kerapian_kamar_review.sql |
Creates 8 portal_private.kamar_* tables & portal_kamar_* RPCs (3 photo slots, 14-day expiry, room commanders, IB exemptions, reviews, operation receipts). |
7. Private Master Data Seeding (scripts/generate-master-sql.mjs)
Section titled “7. Private Master Data Seeding (scripts/generate-master-sql.mjs)”- Never committed to Git: Source member rosters (
supabase/private/serdik.json), generated SQL (supabase/private/01_master_data.sql), and plain-text temporary PIN distributions (supabase/private/pin-baru.csv) remain strictly outside version control. - Generator Behavior (
scripts/generate-master-sql.mjs): Readssupabase/private/serdik.jsonandsrc/data/pengasuh.json, accepts--rotate=pengasuh|all|none, synthesizes non-weak 6-digit PINs using Nodecrypto.randomInt, and emits SQL hashing every PIN directly withcrypt('<pin>', gen_salt('bf', 8))plusmust_change_pin = truewhen rotated.