Skip to content

APIs & System Interfaces (HUB-API-001)

Field Value
Document ID HUB-API-001
Pillar Software Engineering
Standard Alignment Interface Control Document (ICD) / OpenAPI & PostgREST Contract
Status Active / Synchronized with Codebase
Last Updated 2026-11-21
Source of Truth src/lib/supabase.js, src/lib/offlineQueue.js, src/lib/p3mdi-sso.js, src/lib/korpsReports.js, src/lib/pengasuhReportsService.js, src/lib/kerapianKamarService.js, src/lib/pendampinganService.js, src/lib/infoDigitalService.js, ops/p3mdi-sso/*.ts, google-apps-script/Code.gs, supabase/migrations/20261024_secure_auth.sql through 20261130080000_kerapian_kamar_review.sql

Soedirman Hub communicates across four external/internal interface boundaries:

  1. Supabase PostgREST REST Tables (/rest/v1/*): Direct table/view CRUD with RLS and x-portal-token session authentication.
  2. PostgreSQL SECURITY DEFINER & Invoker RPCs (/rest/v1/rpc/*): 67 active public.portal_* functions handling auth, role previews, Corps/Pengasuh governance, mentoring, IB retention, Info Digital, and Kerapian Kamar.
  3. Deno SSO & Core Broker HTTP API (/api/sso*): 11 route paths on app.p3mdi.id (proxied to port 8000) mediating Authentik OIDC, Core Directory profile synchronization, guided first-login onboarding, and Super Admin SSO management.
  4. Google Apps Script Drive Webhook (VITE_GDRIVE_WEBHOOK_URL): Authenticated doPost JPEG upload bridge returning public CDN (lh3.googleusercontent.com) and download links.
flowchart LR
    SPA["Vue 3 Client (BackendAPI + Domain Services)"] -->|"apikey + x-portal-token"| PG_REST["Supabase /rest/v1/{table|view}"]
    SPA -->|"apikey + x-portal-token"| PG_RPC["Supabase /rest/v1/rpc/portal_* (67 RPCs)"]
    SPA -->|"x-portal-token (no-store)"| SSO["Deno Broker (/api/sso/*)"]
    SPA -->|"POST text/plain JSON { token, data }"| GAS["Google Apps Script /exec"]
    SSO -->|"Bearer API Token"| AUTH["Authentik /api/v3/core/* & /application/o/*"]
    SSO -->|"Bearer Bridge Token"| CORE["Core Bridge /v1/bridge/members/*"]
    SSO -->|"apikey SUPABASE_SECRET_KEY"| PG_RPC
    GAS -->|"POST /rest/v1/rpc/portal_me"| PG_RPC

2. Supabase PostgREST Contract (src/lib/supabase.js)

Section titled “2. Supabase PostgREST Contract (src/lib/supabase.js)”

Every live request from supabaseRequest(path, options) (15,000 ms default AbortController timeout; 30,000 ms for uploads) transmits:

  • apikey: VITE_SUPABASE_ANON_KEY
  • Authorization: Bearer <VITE_SUPABASE_ANON_KEY> (omitted automatically when VITE_SUPABASE_ANON_KEY uses Supabase’s sb_publishable_ format)
  • Content-Type: application/json
  • x-portal-token: <64-hex-session-token> (present whenever authenticated)
  • Prefer: defaults to return=minimal on mutating calls unless explicitly set (return=representation, resolution=merge-duplicates,return=minimal, resolution=ignore-duplicates,return=minimal, or count=exact).

Paginated reads (fetchAllPages) page in batches of PAGE_SIZE = 500 using &limit=500&offset=<n> with Prefer: count=exact on page 0 and abort cleanly if sessionToken or demoDb changes mid-flight. Retryability distinguishes network/timeout/408/429/5xx (retryable: true) from permanent 4xx RLS rejections (retryable: false).

Resource Read Columns / Query Mutating Operations & Headers
portal_serdik_directory SERDIK_COLUMNS (id,nosis,nama,jenis_kelamin,asal_institusi,grup,elemen,tempat_lahir,tanggal_lahir,birthday,alamat,telepon,email,role,core_serdik_id,core_version) Read-only masked view (security_barrier = true, security_invoker = false).
serdik Never fetched via bulk SELECT directly (tests/security.test.mjs invariant) POST (createSerdik), PATCH ?nosis=eq.<n> (updateSerdikRole or updateSerdik after /api/sso/core), DELETE ?nosis=eq.<n>.
pengasuh PENGASUH_COLUMNS (id,nrp,nama,pangkat,jabatan,matra,satker,grup,elemen,role) POST, PATCH ?nrp=eq.<nrp>, DELETE ?nrp=eq.<nrp>.
absensi_apel & absensi_lock Range query (tanggal=gte...&tanggal=lte... + optional updated_at=gte...) & paginated personal view POST ?on_conflict=tanggal,sesi,nosis & ?on_conflict=tanggal,sesi,grup with resolution=merge-duplicates,return=minimal.
rundown_harian select=*&order=tanggal.desc POST ?on_conflict=tanggal (merge-duplicates), DELETE ?tanggal=eq.<date>.
dokumentasi, sosiometri, notulensi_apel select=*&order=created_at.desc,id.asc Idempotent POST ?on_conflict=id (resolution=ignore-duplicates,return=minimal) and DELETE ?id=eq.<id>.
saran SARAN_COLUMNS (id,tanggal,kategori,pesan,is_anonymous,pengirim,pengirim_nama,status,tanggapan,created_at) Plain POST saran (return=minimal, no ON CONFLICT so anonymous reporters do not evaluate SELECT policy) + PATCH ?id=eq.<id>.
commander_intents, rabuan_sessions Ordered by tanggal.desc,created_at.desc,id.asc POST, PATCH ?id=eq.<id> (return=representation to detect 0-row RLS denials), DELETE ?id=eq.<id>.
OPERATIONAL_TABLES (pam_reports, paket_orders, live_attendance_photos, apel_summaries, rabuan_articles, kelas_notes, calendar_events, calendar_day_notes, audit_logs) select=* (except live_attendance_photos: select=id,metadata for summaries or select=id,payload for one taken_on + session_key) mutateOperationalRow: PATCH ?id=eq.<key> -> fallback POST ?on_conflict=id (ignore-duplicates) -> PATCH retry; DELETE ?id=eq.<key>.
notifications portal_refresh_daily_notifications() -> fetchOperationalRows('notifications') Server-generated only; client can only PATCH notifications?read_at=is.null with { read_at }.

3. PostgreSQL RPC Catalog (public.portal_* — 67 Functions)

Section titled “3. PostgreSQL RPC Catalog (public.portal_* — 67 Functions)”

All RPCs are invoked via POST /rest/v1/rpc/<fn>. Functions tagged [service_role] are revoked from anon/authenticated and callable only by the Deno SSO broker or Core sync workers.

3.1 Authentication, Session & Guided Setup (11 RPCs)

Section titled “3.1 Authentication, Session & Guided Setup (11 RPCs)”
Function & Signature Return Caller / Access Purpose
portal_login(p_code TEXT, p_pin TEXT) JSONB anon, authenticated Bcrypt PIN auth, 15-min IP/account lockout (login_attempts), rejects core_is_active = false, mints 32-byte token.
portal_login_p3mdi(p_issuer TEXT, p_subject TEXT, p_provider_pk INTEGER DEFAULT NULL, p_provider_username TEXT DEFAULT NULL) JSONB service_role Resolves p3mdi_identities OIDC link, refreshes provider metadata, mints 32-byte session token.
portal_logout() VOID anon, authenticated Deletes active session row hashed from x-portal-token.
portal_me() JSONB anon, authenticated Returns user_profile via session_identity() (includes must_change_pin, effective role, and real_role).
portal_change_pin(p_old_pin TEXT, p_new_pin TEXT) JSONB anon, authenticated Self-service PIN change (must_change_pin = false required); deletes all other device sessions.
portal_admin_set_pin(p_user_type TEXT, p_code TEXT, p_new_pin TEXT) JSONB super_admin Sets temporary bcrypt PIN, marks must_change_pin = true, invalidates target sessions.
portal_admin_unlock_login(p_user_type TEXT, p_code TEXT) JSONB super_admin Clears account lockout entries in portal_private.login_attempts.
portal_switch_role(p_role TEXT DEFAULT NULL) JSONB super_admin Updates sessions.preview_role (NULL restores super_admin); denied to non-admins.
portal_setup_status() JSONB Authenticated (session_identity) Returns { ok, required, requires_sso, pin_saved, password_saved, email_saved, provider_pk, username }.
portal_setup_pin(p_new_pin TEXT) JSONB Onboarding (must_change_pin = true) Saves strong 6-digit PIN without premature unlock; marks pin_saved = true.
portal_setup_mark(p_step TEXT) & portal_setup_finish() JSONB Onboarding Advances password/email progress (portal_setup_mark) and finalizes must_change_pin = false (portal_setup_finish).

(Total: 12 functions including both portal_setup_mark and portal_setup_finish.)

3.2 SSO Identity, Confirmation, Directory & Core Sync (15 RPCs)

Section titled “3.2 SSO Identity, Confirmation, Directory & Core Sync (15 RPCs)”
Function & Signature Return Caller Purpose
portal_my_sso_link() JSONB Active session Returns { linked, provider_pk, provider_username, linked_at }.
portal_link_p3mdi(p_issuer, p_subject, p_provider_pk, p_provider_username) JSONB service_role Links current session’s user to OIDC subject; rejects subjects owned by another user; audits link.
portal_unlink_p3mdi() JSONB service_role Unlinks current user’s OIDC binding and writes unlink audit row.
portal_sso_unlink_by_pk(p_provider_pk INTEGER) JSONB service_role Removes link after admin Authentik user deletion.
portal_sso_audit_list(p_limit INTEGER DEFAULT 50) JSONB Active session Admin sees all audit rows; non-admins see rows where actor or target matches self.
portal_sso_audit_log(p_action, p_target_pk, p_target_code, p_detail) JSONB service_role Records broker-verified identity events (update_profile, activate, deactivate, create, delete).
portal_sso_security_status() JSONB Active session Checks active 5-min re-auth confirmation in sso_confirmations.
portal_sso_confirm_identity(p_subject TEXT, p_auth_time BIGINT) JSONB service_role Validates fresh auth_time (<= 300s) matching linked user and creates 5-min confirmation.
portal_sso_directory() JSONB super_admin (service_role via broker) Lists Hub members with SSO link status (provider_pk, username).
portal_sso_bind_member(p_provider_pk, p_subject, p_username, p_code) JSONB service_role Admin binds newly created Authentik user to unlinked Serdik.
portal_sso_reset_setup(p_provider_pk INTEGER, p_email_present BOOLEAN) JSONB service_role Marks target account for guided setup (must_change_pin) upon admin password reset.
portal_sso_hub_sessions(p_provider_pk INTEGER, p_revoke BOOLEAN DEFAULT false) JSONB service_role Lists or revokes Hub sessions associated with p_provider_pk.
portal_sso_sync_username(p_provider_pk INTEGER, p_username TEXT) JSONB service_role Syncs updated Authentik username into p3mdi_identities.
portal_core_cache_state() JSONB service_role Returns { cursor, last_success_at } from core_cache.sync_state.
portal_core_cache_apply(p_records JSONB, p_cursor BIGINT) JSONB service_role Batch-applies Core Directory member updates (<= 500 rows) bypassing core_owned_fields trigger.

3.3 Attendance, Daily Notifications & Daily Condition (5 RPCs)

Section titled “3.3 Attendance, Daily Notifications & Daily Condition (5 RPCs)”
Function & Signature Return Caller Purpose
portal_attendance_exemptions(p_tanggal DATE, p_sesi TEXT) TABLE(nosis, status, keterangan, sampai, tanggal, sesi) Active session (SECURITY INVOKER) Resolves active multi-day sick/leave exemptions carried into (p_tanggal, p_sesi).
portal_correct_attendance(p_tanggal DATE, p_sesi TEXT, p_rows JSONB) VOID komandan_grup / super_admin Applies post-lock attendance corrections while honoring absensi_lock.
portal_my_absensi_summary() JSONB Active session (SECURITY INVOKER) Returns personal total, hadir, sakit, izin, dinas_luar, terbaru.
portal_my_absensi_streak() INTEGER Active session Computes personal consecutive attendance streak.
portal_refresh_daily_notifications() VOID Active session Materializes daily alerts (IB reminders, Korps deadline warnings, review queues) into public.notifications.
portal_daily_condition(p_action, p_version, p_peer, p_fisik, p_psikologis, p_catatan, p_tanggal, p_slot) JSONB Rollout group (Grup 2) Checks availability, saves consent, or submits pagi (04:00–12:00 WIB) / malam (>=18:00 WIB) condition check-ins (1..5).

(Total: 6 functions in Section 3.3.)

3.4 Corps 17-Unit, Pengasuh Supervision & Scoped Duties (13 RPCs)

Section titled “3.4 Corps 17-Unit, Pengasuh Supervision & Scoped Duties (13 RPCs)”
Function & Signature Return Purpose
portal_scoped_duties(p_admin, p_action, p_subject, p_capability, p_scope, p_key, p_start, p_end, p_reason, p_id) JSONB list, create, revoke time-bounded kasi / pic delegations in portal_private.scoped_grants.
portal_korps_workspace(p_tanggal DATE, p_periode DATE, p_bidang SMALLINT DEFAULT NULL) JSONB Loads active 17-unit report, last_report prefill, Proker items, strength metrics, and user capabilities.
portal_korps_configure(p_bidang SMALLINT, p_nama TEXT, p_kasi TEXT[], p_kabid TEXT, p_bpi TEXT[] DEFAULT NULL) VOID Configures unit Kasi makers, Kabid checker, and BPI reviewers (super_admin).
portal_korps_report_action(p_tanggal DATE, p_bidang SMALLINT, p_version INTEGER, p_action TEXT, p_content JSONB DEFAULT NULL, p_note TEXT DEFAULT '') korps_reports Saves draft, submits (submitted_at), approves (kabid_pending -> bpi_pending -> final), or returns with optimistic version check & lampiran validation (<= 20).
portal_korps_review_queue(p_tanggal DATE) JSONB Lists units awaiting checker/BPI approval for navigation badge counts.
portal_korps_batch_report(p_tanggal DATE) JSONB Consolidated 17-unit batch overview for komandan_batch, super_admin, BPI, and pengasuh.
portal_korps_batch_matrix(p_from DATE, p_to DATE) JSONB Multi-day (up to 31d) unit submission status & timeliness matrix.
portal_korps_strength_history(p_from DATE, p_to DATE) JSONB Multi-day roll-call strength array feeding 7-day SVG sparklines.
portal_korps_save_proker(p_bidang SMALLINT, p_id BIGINT, p_version INTEGER, p_content JSONB) BIGINT Creates or updates work program in public.korps_proker.
portal_korps_record_progress(p_id BIGINT, p_date DATE, p_version INTEGER, p_progress_version INTEGER, p_realisasi NUMERIC) VOID Logs period realization (korps_proker_progress) by unit maker or delegated pic.
portal_pengasuh_workspace(p_tanggal DATE) JSONB Loads 21-instructor supervision workspace + pengasuh_korps_notes.
portal_pengasuh_report_action(p_tanggal DATE, p_unit_id TEXT, p_version INTEGER, p_action TEXT, p_content JSONB, p_note TEXT) pengasuh_reports Drives draft -> dangrup_pending -> danbatch_pending -> final (+ return) transitions.
portal_pengasuh_add_note(p_tanggal DATE, p_bidang SMALLINT, p_prioritas TEXT, p_isi TEXT) pengasuh_korps_notes Records instructor directive (Atensi, Penting, Apresiasi) onto one of 17 Corps units.
portal_pengasuh_release_ready() BOOLEAN CI deploy smoke gate verifying 20261130070000 schema & functions exist.

(Total: 14 functions in Section 3.4.)

3.5 IB Retention, Pendampingan, Info Digital & Kerapian Kamar (20 RPCs)

Section titled “3.5 IB Retention, Pendampingan, Info Digital & Kerapian Kamar (20 RPCs)”
Domain Function Signatures & Returns
IB Check-in & Retention (5) portal_ib_periods() -> JSONB; portal_ib_checkins(p_period) -> JSONB; portal_ib_photo(p_id uuid) -> JSONB; portal_ib_save_checkin(p_id, p_period, p_lat, p_lng, p_photo, p_location, p_companion, p_match, p_reason) -> JSONB; portal_ib_retention(p_action, p_period, p_start, p_end) -> JSONB (status, preview, run, toggle, add_period).
Pendampingan (4) portal_pendampingan_list() -> JSONB; portal_pendampingan_detail(p_id uuid) -> JSONB; portal_pendampingan_save(p_id, p_revision, p_next_revision, p_nosis, p_awal, p_tingkat, p_kebutuhan, p_tindakan, p_selesai, p_tanggal_catatan, p_catatan) -> JSONB; portal_pendampingan_delete(p_id uuid, p_revision uuid) -> JSONB.
Info Digital & CI Gate (6) portal_info_digital_access() -> JSONB; portal_info_digital_submit(p_receipt, p_platform, p_category, p_handle, p_subject, p_link, p_description, p_evidence, p_attested) -> JSONB; portal_info_digital_queue(p_status) -> JSONB; portal_info_digital_update(p_id, p_operation, p_revision, p_status, p_subject, p_note) -> JSONB; portal_info_digital_evidence(p_id) -> JSONB; portal_critical_batch_ready() -> BOOLEAN.
Kerapian Kamar (5) portal_kamar_review_list(p_tanggal date) -> JSONB; portal_kamar_evidence(p_room_id uuid, p_tanggal date) -> JSONB; portal_kamar_appoint_commander(p_room_id uuid, p_nosis text, p_operation_id uuid) -> JSONB; portal_kamar_set_exemption(p_room_id uuid, p_tanggal date, p_exempt boolean, p_reason text, p_operation_id uuid) -> JSONB; portal_kamar_save_review(p_room_id uuid, p_tanggal date, p_verdict text, p_note text, p_operation_id uuid) -> JSONB.

(Total across Sections 3.1–3.5: 12 + 15 + 6 + 14 + 20 = 67 public portal_* RPCs, matching the verified schema catalog.)


4. Deno SSO & Core Broker HTTP API (ops/p3mdi-sso/, :8000)

Section titled “4. Deno SSO & Core Broker HTTP API (ops/p3mdi-sso/, :8000)”

Every endpoint checks exact Origin === P3MDI_HUB_ORIGIN on non-GET requests, restricts payloads via Traefik (maxRequestBodyBytes: 8192), enforces Cache-Control: no-store, and verifies x-portal-token via POST /rest/v1/rpc/portal_me.

Method & Path Auth Requirement Request / Query Contract Response / Behavior
GET /api/sso Public None { clientId } (503 if broker env vars unconfigured).
POST /api/sso Public + PKCE callback { code, verifier (43 chars), nonce (43 chars) } (application/json) Exchanges code at auth.p3mdi.id/application/o/token/, verifies RS256 id_token via JWKS, calls portal_login_p3mdi, returns { ok, token, expires_at, user }.
GET / POST /api/sso/setup portal_me (session_identity) POST: { step: 'pin'|'password'|'email'|'finish', value? } Guides first-login PIN change, 12–128 char Authentik password setup, email assignment, and portal_setup_finish.
GET / PATCH /api/sso/profile Active session (PATCH requires 5-min confirmation) PATCH: { name?, email? } Reads or updates linked Authentik user’s name/email; logs update_profile audit.
POST / DELETE /api/sso/link Active session (DELETE requires 5-min confirmation) POST: { code, verifier, nonce } Self-links or unlinks user’s Authentik account via portal_link_p3mdi / portal_unlink_p3mdi.
GET / POST /api/sso/security Active session (must_change_pin === false) POST: { action: 'confirm', callback } or { action: 'password', password } Confirms identity (prompt=login&max_age=0) for 5 minutes or changes Authentik password + revokes Hub/SSO sessions.
GET / POST /api/sso/sessions Active session + 5-min confirmation (?id other than self requires super_admin) GET: ?id=<pk>; POST: { id? } Lists active Hub sessions + Authentik AuthenticatedSession browser sessions or revokes them all.
GET / PATCH /api/sso/core Active session (patching another ?nosis requires super_admin) GET: ?nosis=<3-digit>; PATCH: { version, full_name?, contact_email?, phone?, address?, employer?, group_name?, element_name? } Reads or updates Core Directory (/v1/bridge/members/*) with optimistic version lock and immediately triggers portal_core_cache_apply.
GET / POST / PATCH / DELETE /api/sso/accounts super_admin (POST/PATCH/DELETE require 5-min confirmation) GET: list; POST: { ids } (max 8 per batch); PATCH: { id, username?, name?, email?, active? }; DELETE: ?id=<pk> Lists managed group members (Soedirman Hub Serdik), activates batches, updates/deactivates accounts (revoking sessions), or deletes inactive accounts (is_active === false + 0 sessions).
POST /api/sso/accounts/create super_admin + 5-min confirmation { username, name, email, code } Provisions internal Authentik user in group, generates random 24-byte temp password, binds via portal_sso_bind_member, activates, returns { ok, temporary_password, account }.
GET /api/sso/accounts/directory & POST /api/sso/accounts/reset-password super_admin (reset-password requires 5-min confirmation) POST: { id } Fetches Hub SSO directory (portal_sso_directory) or resets another user’s Authentik password + sets portal_sso_reset_setup + revokes sessions.

5. Google Apps Script Media Webhook (google-apps-script/Code.gs)

Section titled “5. Google Apps Script Media Webhook (google-apps-script/Code.gs)”
  • Health Check (GET <VITE_GDRIVE_WEBHOOK_URL>): Returns JSON { "status": "active" }.
  • Upload Contract (POST <VITE_GDRIVE_WEBHOOK_URL>):
    • Sent by BackendAPI.uploadToGoogleDrive with Content-Type: text/plain;charset=utf-8 (avoiding preflight quirks) and 30,000 ms timeout.
    • JSON body: { token, filename/fileName, mimeType, data/base64, folderName, description, judul, tanggal, uploader }.
    • Verification: verifySession(token) calls POST <SUPABASE_URL>/rest/v1/rpc/portal_me, requiring HTTP 200, user.nosis, and user.must_change_pin === false.
    • Authorization: Subfolder "Sosiometri" (OPEN_FOLDERS) allows any authenticated user; all other folders require user.role in ["penerangan", "super_admin"].
    • Validation: Accepts image/jpeg, image/png, image/webp, image/heic, image/heif; rejects decoded payloads > 8 MB.
    • Response: { success: true, fileId, directViewUrl: "https://lh3.googleusercontent.com/d/<id>", downloadUrl: "https://drive.google.com/uc?export=download&id=<id>" } (or { success: false, error }).

6. Frontend BackendAPI & Offline Outbox Interface

Section titled “6. Frontend BackendAPI & Offline Outbox Interface”
  • BackendAPI (src/lib/supabase.js): Exposes typed-by-convention JS wrappers across auth (login, logout, fetchMe, changePin, adminSetPin, switchRole, adminUnlockLogin), directory/attendance (fetchUsersAndRoles, fetchAttendanceRange, fetchAttendanceLocks, fetchAttendanceExemptions, correctAttendance, upsertAbsensi, upsertAbsensiBatch, upsertAbsensiLock), operational persistence (fetchOperationalRows, mutateOperationalRow, fetchNotifications, markNotificationsRead), SSO broker calls, and Drive uploads (uploadToGoogleDrive).
  • Offline Outbox Queue (src/lib/offlineQueue.js): Instantiated by src/store/portal.js (createOfflineQueue({ getScope, send, onResult })), serializing queued mutations per session scope via navigator.locks (soedirman-outbox-v1:<scope>:<uuid>), retrying retryable errors up to 8 times, and falling back to a bounded 100-entry in-memory map if localStorage throws quota errors.
  • Standalone Domain Services (src/lib/): korpsReports.js, pengasuhReportsService.js, kerapianKamarService.js, pendampinganService.js, and infoDigitalService.js invoke Supabase rpc() directly (src/lib/supabase.js), mapping PostgreSQL exceptions cleanly to localized Indonesian user alerts while honoring state.isDemoMode where supported.