Skip to content

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

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 from PUBLIC, anon, and authenticated roles—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 trigger core_owned_fields protecting Core-mastered profile columns on public.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.


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"

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'.
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:

  1. public.pam_reports — Security/lost-found/facility payload (<= 256 KB). Policy 20261130060000 strictly excludes payload->>'tipe' = 'info_digital'.
  2. public.paket_orders — Generated stored columns grup, elemen, status from payload; triggers verify_package_transition & notify_package_status manage courier receipt -> picked-up state transitions.
  3. public.live_attendance_photos — Payload <= 1.5 MB; generated columns taken_on (^\d{4}-\d{2}-\d{2}$), session_key (1..32), and metadata (payload - 'photoUrl' - 'gps' - 'locationName' after 20261130030000). Primary key format strictly verified as taken_on || '__' || session_key || '__' || owner_code. 20261130030000 attaches valid_photo_gps(payload) (NOT VALID check constraint preserving historical rows).
  4. public.apel_summaries — Generated tanggal, sesi, grup.
  5. public.rabuan_articles — Student-written Wednesday summaries & review metadata (<= 256 KB).
  6. public.kelas_notes — Classroom learning notes (<= 256 KB).
  7. public.calendar_events — Cohort calendar entries (<= 256 KB).
  8. public.calendar_day_notes — Day-level annotations (<= 256 KB).
  9. public.audit_logs — Operational client audit log payloads (<= 8 KB).
  10. public.notifications — Composite PK (id, recipient_code, recipient_type), server-written via triggers/portal_refresh_daily_notifications(), client only SELECT & 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 (id UUID PK, 3-tier current_tier danel|dangrup|kabid_sdm|selesai, status 7 values) and public.izin_bermalam_checkins (id UUID PK, nullable latitude/longitude/foto_selfie_url + evidence_removed_at added in 20261130020000, slot_number 1..4, status_verifikasi). Note: Frontend perizinanService.js persists izin_keluar in localStorage (p3md_perizinan_data_v1), whereas IB check-ins and 168-hour evidence purging run live through portal_ib_* SECURITY DEFINER RPCs (20261130020000).
  • public.catatan_disiplin, public.proker_korps, public.notulensi_pic_tasks, public.pengumuman_korps, public.gform_trackers: Initial schema created in 20261116_poldik_operasional.sql, while active frontend read/write modules (poldikService.js, operasionalService.js) maintain localStorage state 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.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)”
  1. Account enters must_change_pin = true -> router locks navigation to /ganti-pin (ForcePinView -> AccountSetupModal).
  2. portal_setup_pin updates bcrypt PIN hash (must_change_pin remains true until finish), setting pin_saved = true.
  3. If requires_sso = true, user must sequentially complete step: 'password' (12–128 chars via Authentik Core API) -> password_saved = true, then step: 'email' -> email_saved = true.
  4. portal_setup_finish validates all required flags (pin_saved, plus password_saved & email_saved if SSO-linked) and flips must_change_pin = false atomically, unlocking portal_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): Reads supabase/private/serdik.json and src/data/pengasuh.json, accepts --rotate=pengasuh|all|none, synthesizes non-weak 6-digit PINs using Node crypto.randomInt, and emits SQL hashing every PIN directly with crypt('<pin>', gen_salt('bf', 8)) plus must_change_pin = true when rotated.