All projects
Information systemPetra Christian University
SIM Kerjasama
The official system of record for every PETRA MoU and MoA.
About the system
SIM Kerjasama records who proposed each agreement, who approved it, when it is valid, and whether it has been renewed. Two other systems, the internationalisation KPI dashboard and SIM Realisasi, depend on it as a stable source of partnership data.
Live demo
Try SIM Kerjasama
The working app, on demo data. Sign in with a demo account to see the dashboard, queues and renewals.
sim-kerja-sama-petra.vercel.app/loginOpen in new tabOpen SIM Kerjasama in a new tab
Live demo
SIM Kerjasama
The working app, running on demo data. It loads here, inside the page.
Point of view
9Goals, G1–G9
8Menus
6User groups
3Approval tiers
Background
Why a system of record
Partnership documents used to live in inboxes and folders. SIM Kerjasama gives each one a single, traceable path.
The problem
- Proposals travelled by email and paper, so nobody could say where a document was or who was holding it.
- Approvals had no clock: a draft could sit with one office for weeks without anyone noticing.
- Renewals were noticed late, usually after the agreement had already expired, and decided without evidence.
- The same partner was recorded under several spellings, so counts of partners and countries were unreliable.
With the system
- Every MoU and MoA has one record, one status and one owner, from the first draft to the archive.
- The approval order is enforced by the software, tier by tier, with an SLA clock per approver.
- Renewal reminders start six months ahead and require evaluations from both the faculty and the partner.
- Partner and country master data can be merged, so the dataset stays clean enough for other systems to use.
Objectives · Tujuan
Nine goals, three themes
Governance
Approvals follow the official hierarchy, and every delay is visible.
- G1Enforce the approval hierarchy in software
- G2Give approvers an option besides rejecting
- G3Make approval delays visible
- G4Measure document completion time
Data quality
One clean, evidence-based partnership dataset that other systems can rely on.
- G5Proactive, evidence-based renewals
- G6A clean partnership dataset others can reference
- G8Structured partnership evaluation data
Visibility
Active partnerships open to the whole university, and to partners as PETRA.
- G7Active partnerships visible to everyone
- G9Present officially as a PETRA property, including to partners
Business process
Two processes, one document
Modelled in BPMN from the rules the database enforces: who acts, in what order, and which status each step sets.
A unit proposes, the International Office routes it, approvers decide tier by tier, and the signed document is activated.
Proposing unit
International Office
Approvers (Tier 1–3)
SIM Kerjasama
Partnership need
Fill in the proposal formDraft
Submit proposalDiajukan
Choose approvers and routeDiproses
Open the lowest pending tier and notify
Review the proposalDisposisi
×Decision?ApproveRevisePendingReject
×Higher tier left?Yes: next tierNo
Print and mark ready to signSiap TTD
Sign offline, upload the signed file
Agreement activeAktif
- Proposing unit: Partnership need
- Proposing unit: Fill in the proposal form, status Draft
- Proposing unit: Submit proposal, status Diajukan
- International Office: Choose approvers and route, status Diproses
- SIM Kerjasama: Open the lowest pending tier and notify
- Approvers (Tier 1–3): Review the proposal, status Disposisi
- Approvers (Tier 1–3): Decision? (Approve, Revise, Pending, Reject)
- SIM Kerjasama: Higher tier left? (Yes: next tier, No)
- International Office: Print and mark ready to sign, status Siap TTD
- International Office: Sign offline, upload the signed file
- SIM Kerjasama: Agreement active, status Aktif
- Draft
- Diajukan
- Diproses
- Disposisi Tier 1·2·3
- Disetujui
- Siap TTD
- Aktif
Approval tiers
- Tier 1
- Head of the International Office, Head of the Rector's Secretariat
- Tier 2
- Deans, heads of study programs, programs and units
- Tier 3
- Vice Rectors and the Rector, in parallel
R1
Tier gating
A tier opens only when no lower tier in the current round is still waiting. Empty tiers are skipped.
R2
Four decisions
Approve opens the next tier. Revise asks for a new draft while the SLA keeps running. Pending freezes the document and its clock. Reject archives it.
R3
SLA per approver
Counted in business days from when the approver's tier opens, excluding weekends, holidays and time in Pending. Yellow at 2 days, red at 4.
R4
Already signed?
The office can record a document signed outside the system directly, so the dataset is complete from day one.
As an agreement nears its end date, the unit and the partner each evaluate it, and renewal opens only on evidence.
SIM Kerjasama
International Office
Owning unit
Partner institution
Daily expiry sweep, 05:15 WIB
Flag "ending soon" and send remindersAkan Berakhir
Send evaluation requests
Faculty evaluation
Partner evaluation by secure link
×Both recommend?Both continueDiffer: override with reasonBoth stop
Start the renewal process
Upload draft, create renewal proposal
Approval flow (Process 1)
Link to the old agreement and archive itsuperseded_by_renewal
Partnership renewed
- SIM Kerjasama: Daily expiry sweep, 05:15 WIB
- SIM Kerjasama: Flag "ending soon" and send reminders, status Akan Berakhir
- International Office: Send evaluation requests
- Owning unit: Faculty evaluation
- Partner institution: Partner evaluation by secure link
- International Office: Both recommend? (Both continue, Differ: override with reason, Both stop)
- International Office: Start the renewal process
- Owning unit: Upload draft, create renewal proposal
- International Office: Approval flow (Process 1)
- SIM Kerjasama: Link to the old agreement and archive it, status superseded_by_renewal
- SIM Kerjasama: Partnership renewed
- Aktif
- Akan Berakhir
- Diarsipkan
R1
Reminders
Monthly from six months before the end date, then weekly for the last two months. The expiry sweep runs daily at 05:15 WIB.
R2
Two evaluations
The owning unit evaluates in the app; the lead partner evaluates through a secure link, without an account.
R3
Renewal gate
Renewal opens only when both evaluations agree, or when the office overrides with a recorded reason.
R4
Hand-over
Activating the renewal links it to the old agreement and archives the old one in a single transaction.
Lifecycle
One agreement, six stages
The two processes above, in one line.
Step 1/6
Propose. A unit proposes an agreement, or records one already signed.
The app
Eight menus, one document lifecycle
SIM Kerja SamaDashboardFind AgreementsNew AgreementMy QueueRenewalsNotificationsMaster DataAdmin
- Dashboard
- KPIs, global partner map, chart studio, bottlenecks
- Find Agreements
- Lifecycle tabs, per-column filters, export
- New Agreement
- Propose a new agreement, or record one already signed
- My Queue
- Documents waiting for your position to act
- Renewals
- Requests, faculty and partner evaluations, gates
- Notifications
- In the app and by email
- Master Data
- Units, positions and tiers, partners (merge), countries, agendas
- Admin
- Settings and SLA thresholds
Benefits · Manfaat
Six user groups
Tap a card.
For the university
Institutional benefits
Accreditation evidence
Valid agreements and their documents are ready for BAN-PT and LAM assessors.
RENSTRA reporting
Domestic and international MoU/MoA counts are available at any time, without a manual recap.
Ranking data
A dependable count of international partners and countries for QS and THE submissions.
Institutional memory
Every decision, revision and renewal stays on record when staff change.
Before and after
Unmeasured, now measured
- Measured and reportedDocuments completed in under a month
- Any timeDomestic vs international MoU/MoA
- Visible per approverApprovals taking more than 4 working days
- The default pathRenewals with a complete evaluation
- Detected and mergeableDuplicate partner records
- Every renewed partnershipStructured partnership evaluation data
- Counted per renewal chainAgreements actually carried out (RENSTRA 1.19.24)
Programmer POV
How SIM Kerjasama is built
Transcribed from the source repository: migrations, functions, README and rules. The database owns the business rules; the app calls into it.
61SQL migrations
43Tables
77Postgres functions
2Scheduled sweeps
Rules live in the database
Access control is RLS; tier gating and SLA are Postgres functions. They behave the same from a Server Action, a sweep or the read API.
Server Components read, Server Actions write
No client-side data fetching and no internal API routes. Each action is a thin call into the function that owns the rule.
Views are security_invoker
RLS still decides which rows a view returns, so reporting never leaks.
Never hard-delete
Every archival records a reason: rejected, expired_without_renewal, superseded_by_renewal or terminated_early.
Architecture
Stack and moving parts
Browser → Next.js on Vercel
- Server Components (read)
- Server Actions (write)
- /api/ekspor (session)
- /api/v1 read API (API key)
- /evaluasi/[token] (public)
Supabase Postgres
- RLS on every table
- recompute_tiers, SLA, cascade, renewal gate
- pg_cron: SLA sweep, expiry sweep
- notifikasi queue
Outside
- kirim-email Edge Function → Resend
- SIM Realisasi (read views)
- Internationalisation KPI dashboard
| Layer | Choice |
|---|---|
| Frontend | Next.js 15 (App Router, typed routes), React 18, TypeScript, Tailwind CSS 3 |
| Backend | Supabase: Postgres 17, Auth, Storage, Row-Level Security, Edge Functions, pg_cron |
| Domain logic | Postgres functions: tier gating, business-day SLA with pause, unit cascade, renewal gate |
| Charts / map | ECharts (Studio Grafik), Leaflet (Peta Mitra Global) |
| Exports | ExcelJS (.xlsx) |
| kirim-email Supabase Edge Function via Resend | |
| Hosting | Vercel (frontend), Supabase (database and functions) |
Business process → data
What each step does to the database
Pick a step: the functions it calls, the tables it writes, the status it sets and the rules that guard it. The touched tables light up in the ERD.
Step 2 of 11 · Proposing unit
Fill in the proposal form
The proposal and its child rows (partners, proposers, Lingkup units, agendas, fields, SDGs, MoU/MoA detail).
Functions
simpan_anak_proposal() Tables
proposal_dokumenpartner_pengusulpengusulproposal_dokumen_unitproposal_dokumen_agendastatus_proposal = Draft
Highlighted in amber: proposal_dokumen, partner_pengusul, pengusul, proposal_dokumen_unit, proposal_dokumen_agenda.
Step 2 of 11 · SIM Kerjasama
Flag "ending soon" and send reminders
Documents near their end date move to Akan Berakhir; reminders are queued monthly from 6 months out, weekly for the last 2. Auto Renewed documents are skipped.
Functions
sapu_kedaluarsa()catat_notifikasi() Tables
dokumen_kerja_samanotifikasiAktif → Akan Berakhir
Rules: K-06
Highlighted in amber: dokumen_kerja_sama, notifikasi.
Entity relationship diagram
Entities and attributes
The core of the SIM Kerjasama schema, grouped by domain. Select a table to trace its relationships.
- Master data
- Proposal
- Disposition & approval
- Agreement & renewal
- foreign key
- logical reference
Select a table to highlight its relationships. Scroll the diagram in both directions.
Data dictionary
Every table shown, column by column
Master data (7)
negara
id (PK), kode, nama, is_domestic
unit
id (PK), nama, id_parent_unit (FK), id_jenis_unit (FK), is_active
jabatan
id (PK), nama, id_unit (FK), tier_disposisi
akun
id (PK), auth_user_id (FK), id_jabatan (FK), email, role, +1
partner
id (PK), nama, is_international, id_negara (FK), latitude, longitude, id_merged_into (FK), +10
partner_contact
id (PK), id_partner (FK), nama, email, +2
agenda
id (PK), nama, is_amendment
Proposal (6)
proposal_dokumen
id (PK), jenis_kerjasama, status_proposal, tujuan_kerjasama, manfaat_bagi_petra, id_dokumen_sebelumnya (FK), id_akun_pembuat (FK), waktu_disetujui, status_sla, +8
partner_pengusul
id_partner (FK), id_proposal_dokumen (FK), is_lead
pengusul
id_jabatan (FK), id_proposal_dokumen (FK)
proposal_dokumen_unit
id_proposal_dokumen (FK), id_unit (FK)
proposal_dokumen_agenda
id_proposal_dokumen (FK), id_agenda (FK)
proposal_status_history
id (PK), id_proposal_dokumen (FK), status_lama, status_baru, waktu
Disposition & approval (5)
disposisi
no (PK), id_proposal_dokumen (FK), no_dokumen_kerjasama (FK), jenis_disposisi, round_ke, id_akun_pengirim (FK), +3
disposisi_target
no (PK), no_disposisi (FK), id_jabatan (FK), tier, status, waktu_unlock, batas_waktu_sla, durasi_hari_kerja, status_sla, +1
riwayat_approval
id (PK), id_disposisi_target (FK), id_proposal_dokumen (FK), id_akun (FK), aksi, catatan, +2
revisi_proposal
id (PK), id_proposal_dokumen (FK), file_proposal, id_disposisi_target_peminta (FK), +3
pending_periods
id (PK), id_proposal_dokumen (FK), mulai, selesai, +2
Agreement & renewal (6)
dokumen_kerja_sama
no (PK), id_proposal_dokumen (FK), no_dokumen, tanggal_mulai, tanggal_berakhir, status, alasan_arsip, upload_dokumen, +6
evaluasi
no (PK), id_dokumen_kerjasama (FK), respondent_type, id_partner_contact (FK), id_jabatan_pengusul (FK), rekomendasi, status, id_supersedes (FK), +15
partner_eval_token
id (PK), id_evaluasi (FK), token, is_active, waktu_submit, +4
keputusan_pembaruan
id (PK), id_dokumen_kerjasama (FK), keputusan, alasan, +2
notifikasi
id (PK), id_proposal_dokumen (FK), no_dokumen_kerjasama (FK), id_jabatan_penerima (FK), jenis_notifikasi, status, +3
implementasi_dokumen
no (PK), no_dokumen_kerjasama (FK), jenis, judul, status, +5
FK* = logical reference to a view or registry, checked by a function because views cannot be foreign-key targets.
Business rules
Where each rule is enforced
| ID | Rule | Enforced by |
|---|---|---|
| K-01 | A target unlocks when no lower-tier target in the current round is still unapproved; an empty tier is skipped. recompute_tiers is the single idempotent writer of this state. | Function |
| K-02 | SLA is per target, from unlock, in business days excluding weekends, holidays and time in Pending. Yellow at 2, red at 4; thresholds come from settings. | Function + pg_cron |
| K-03 | A revision overwrites the draft in place and is logged in revisi_proposal. No other target is affected and the SLA clock keeps running. | Function |
| K-04 | Pending freezes the document and stops the SLA clock. Reactivation starts a new round from tier 1, including tiers that had already approved. | Function |
| K-05 | Reject is terminal; the document is archived with alasan_arsip = rejected. | Function |
| K-06 | A renewal proposal can be created only when both evaluations are submitted and both recommend continuing, or KUI recorded an override with a reason. | buat_proposal_perpanjangan |
| K-07 | The public surface is exactly two anon-executable functions, each requiring a 256-bit token scoped to one evaluation. | Grants |
| K-08 | An account may act on a document only while an open disposisi_target is routed to its jabatan and is pending_action. | RLS |
| K-09 | Documents are never hard-deleted; every archival records one of four reasons. | CHECK + functions |
| K-10 | While a document is in disposition IO can add targets (they join the current tier) or remove pending ones; an approved target cannot be removed. | tambah_target / hapus_target |
Enums & state machines
The values the database allows
proposal_dokumen.status_proposal- Draft
- Diajukan
- Diproses
- Disposisi - Tier 1
- Disposisi - Tier 2
- Disposisi - Tier 3
- Pending
- Disetujui
- Siap TTD
- Ditolak
dokumen_kerja_sama.status- Aktif
- Akan Berakhir
- Kedaluarsa
- Diarsipkan
disposisi_target.status- waiting
- pending_action
- approved
- rejected
- removed
alasan_arsip- rejected
- expired_without_renewal
- superseded_by_renewal
- terminated_early
akun.role- submitter
- io_staff
- io_admin
- viewer
jenis_kerjasama_t- MoU
- MoA
Scheduled jobs
What runs on its own
simks-sapu-sla → sapu_sla()pg_cron 22:00 UTC (05:00 WIB)Recompute SLA, set flags, queue yellow, red and escalation reminderssimks-sapu-kedaluarsa → sapu_kedaluarsa()pg_cron 22:15 UTCMove documents to Akan Berakhir, archive expired ones, queue expiry reminderskirim-email Edge FunctionEvery 5 minutesSend pending notifikasi rows via Resend, after the transaction commits
Security
Access is the database's job
- Two authorization axesApplication role on akun.role, plus approver status, which is derived from open targets and never stored.
- RLS everywhereEvery table has RLS. Middleware only redirects to /login; RLS decides which rows a request reaches.
- Read API for SIM Realisasi/api/v1/kerja-sama with an API key compared in constant time: active agreements, one agreement, and the renewal-chain successor.
- Revoke by defaultNew functions are revoked from PUBLIC in the migration that creates them and granted only to authenticated.
- Private storageFiles are served through signed URLs after an authorization check.
Integration contract
What SIM Realisasi reads, and what it gets back
- Read API /api/v1/kerja-samaActive agreements (Akan Berakhir included), one agreement by number, and /penerus to follow a renewal chain to its current successor. API key in x-api-key, compared in constant time.
- Read-only viewsIn the shared Supabase project SIM Realisasi reads documents, partners, units, agendas and countries through views in schema kerjasama; it never writes SIM-KS tables.
- Implementation tabVerified realisations appear on each agreement, with a flag on active agreements that have no activity yet.
Testing
What the test suite covers
- Tier gating when a tier is empty
- Pending reset and the new round
- SLA pause netting and business-day arithmetic
- Revision isolation
- Live edit: add, remove, refuse removing an approved target
- Sweep idempotency
- Transaction rollback
- The evaluation gate
- Reopening a partner evaluation supersedes the old one
- Token scope
- The RLS boundary
- Lingkup cascade
- Renewal-chain resolution