Information systemPetra Christian University

SIM Realisasi

Collect, verify, store and report every activity carried out under PETRA MoUs and MoAs.

From agreements on paper to real activities.

Try the live demo Programmer view

Live demo

Try SIM Realisasi

The working app, on demo data: the dashboard, the activity list and the single-page report form.

sim-realisasi.vercel.app/Open in new tab

Live demo

SIM Realisasi

The working app, running on demo data. It loads here, inside the page.

Demo environment with generated activities over real agreements. Figures there are not official PETRA indicators.

Point of view

133Activities in the demo

297RENSTRA 1.1 students, 2025/26

5Indicators reported

RENSTRA 1.1 by semester, 2025/26

Odd semester (Ganjil)16556%
Even semester (Genap)13244%

Demo data generated over the real SIM Kerjasama agreements and units; not yet real activities.

Background

Why realisation needed its own system

An agreement is only worth what is carried out under it. Until now, nobody could count that reliably.

The problem

Realisations reached the International Office through scattered reports, emails and spreadsheets.
Counting inbound and outbound students meant manual reconciliation every semester.
The same student could be reported by two units and counted twice.
Last semester's numbers shifted whenever a late report arrived.

With the system

Each academic unit reports its own activities on one page, with the IA, IR and participants attached.
Student and employee numbers are checked against BAAK and HR, and duplicates are decided once.
Indicators are computed by the database from verified data only.
Each semester is frozen into a snapshot on its cutoff date, so reported numbers never change.

Objectives · Tujuan

Five objectives

Every activity on record
Collect each activity carried out under a PETRA MoU or MoA, linked to that exact agreement.
Verified before counted
Mobility activities are checked by the IO Mobility team; only verified data reaches the numbers.
Indicators without recaps
RENSTRA 1.1, 1.19.S1 and 1.19.S4 and the International Awards come straight from the data.
Reports that stay put
Frozen semester snapshots make every report reproducible.
Show which agreements are used
Feed activity back to SIM Kerjasama so dormant partnerships surface.

Outcomes

Three things it delivers

RENSTRA indicators

Calculated from verified data, with drill-down to every activity.

Semester reports

Built from frozen, reproducible snapshots.

Dormant partnerships

Made visible, with that information fed back to SIM Kerjasama.

Included: Activities that implement a PETRA MoU or MoA recorded in SIM Kerjasama.

Not included: IISMA, free movers, government scholarships, and imports of historical data.

Key concepts

Six terms the system is built on

Kegiatan: One activity under one agreement, documented by one Implementation Arrangement (IA) and one Implementation Report (IR).
Kerja sama: The single MoU or MoA from SIM Kerjasama the activity implements, picked from documents valid on the activity dates.
Renewal chain: An agreement plus all its renewals. Realisation of agreements (1.19.S4) counts chains, so a mid-year renewal counts once.
Jenis Kegiatan: The activity type list. IO decides per type whether it is mobility and whether it counts for 1.19.S1.
Mobility activity: Needs a participant list and one combined PDF, and is verified by the IO Mobility team. Others are verified on submit.
Snapshot: A semester's frozen indicator values, with the IDs of every record behind them and the settings used.

Business process

From activity to indicator

Modelled in BPMN: one process follows an activity through verification; the other covers what runs on a schedule.

One activity, from the unit that ran it to the indicators it counts toward.

Academic unit

IO Mobility team

SIM Realisasi

Activity completed

Fill in details, pick the agreement

Save draft; set semester and deadlineDraf

×Mobility activity?No: upload IA & IRYes: add participants

Look up student and staff numbers (BAAK & HR)

Submit the activityDiajukan

Validate, flag late, detect duplicatesMenunggu verifikasi

Review participants and the mobility PDF

×In order?No: request revisionYes: approve

Mark verified, notify the unitTerverifikasi

Counted toward RENSTRA and AwardsTerhitung

Scroll sideways to follow the flow. Dark boxes with a cog are steps the system runs on its own; blue mono labels are the status a step sets.

Draf
Diajukan
Menunggu verifikasi
Terverifikasi
Terhitung

FlagsPerlu revisiTerlambat

Reporting deadline

Semester and academic year come from the start date; the deadline is the end date plus 30 days. Late submissions are flagged, not refused.

Nothing is rejected

A wrong submission always comes back as a revision with a note, and returns as participant version n+1.

Duplicate students

If two units report the same student, the Mobility team picks the activity that keeps them. Approval waits until it is decided.

Drafts only

Drafts can be deleted by their creator or IO Admin. A submitted activity is never deleted.

Report an activity

Four steps, one page

Step 1/4

Details. Pick the agreement valid on the activity date.

The app

Seven menus

RealisasiDashboardActivitiesNew ActivityMobility VerificationReports & ExportSettingsDocuments

Dashboard: RENSTRA indicators and activity overview
Activities: Every reported activity and its status
New Activity: Report an activity on a single page
Mobility Verification: Participant checks with duplicate handling
Reports & Export: Semester snapshots and downloads
Settings: Periods and reference data
Documents: Browse SIM Kerjasama agreements with their activity summary

Data flow

Three sources in, four results out

SIM KerjasamaAgreements valid on the activity date, with renewal chains
Academic unitsActivity details, IA, IR and participants
BAAK & HRStudent and employee numbers

SIM RealisasiVerifies, de-duplicates, counts verified data only, and freezes each semester.

RENSTRA 1.1Inbound and outbound students
RENSTRA 1.19.S1 · 1.19.S4International activities; agreements realised
International AwardsStudy programs ranked on verified activity
Back to SIM KerjasamaImplementation tab and "no activity yet" flag

Access

Who can do what

Enforced in the database with row-level security, not only hidden in the interface. Every export with student data is logged (UU PDP 27/2022).

Action	Academic unit	IO Mobility	IO Admin	Leadership
Create, submit and revise activities	Own unit	No	On behalf of a unit	No
See activities	Own and co-unit	All	All	Verified only
See participant names and mobility PDFs	Own	Yes	Yes	No
Verify mobility, decide duplicate students	No	Yes	Yes	No
Edit a verified activity (logged)	No	Participants	Yes	No
Settings, calendar, activity-type rules, freeze	No	No	Yes	No
Dashboard, awards, non-personal Excel	Own unit	Yes	Yes	Yes
Excel with student data (logged)	Own unit	Yes	Yes	No

Integration

Each shows the other's data

Tap a card.

In SIM Kerjasama

In SIM Realisasi

Benefits · Manfaat

Who gains what

Tap a card.

Programmer POV

How SIM Realisasi is built

Transcribed from the source repository: migrations, functions, README and rules. The database owns the business rules; the app calls into it.

39Tables & views

249Columns

32Relationships

64Numbered business rules

One database, two systems

Realisasi lives in schema realisasi beside SIM Kerjasama and only reads it through views in schema kerjasama. It never writes SIMKS tables.

Business rules in the database

Status derivation, period derivation, chain resolution and KPI math are SQL, so the UI, exports and snapshots can never disagree.

External systems behind an RPC

BAAK and HR are mocked, but the app only calls lookup_students and lookup_employees, so production swaps the implementation, not the app.

Frozen means frozen

Snapshots are written once; a correction creates a new superseding snapshot with a reason.

Privacy by default

Participant data and transcripts sit behind RLS and private storage; every personal-data export is logged.

View the repository

Architecture

Stack and moving parts

Next.js on Vercel

Dashboard and period selector
Kegiatan list, detail, new, revision
Verifikasi Mobilitas and duplicates
Laporan and Excel route handlers
Pengaturan (IO Admin)

Supabase (shared project)

schema realisasi: tables, triggers, RPCs
schema kerjasama: read-only views over SIM-KS
mock_baak / mock_hr registries
pg_cron: realisasi-daily-jobs

Outputs

RENSTRA 1.1, 1.19.S1, 1.19.24
International Awards
Frozen semester snapshots
Realisasi tab back in SIM Kerjasama

Layer	Choice
Frontend	Next.js 15 App Router, TypeScript, Tailwind, shadcn/ui
Forms	react-hook-form + zod, schemas shared client and server
Data	Supabase Postgres, RLS, SQL functions, @supabase/ssr
Auth	Supabase Auth, shared with SIM Kerjasama; demo role switcher
Files	Supabase Storage, private buckets, 5-minute signed URLs
Jobs	pg_cron for reminders and freezes; triggers for the duplicate scan
Excel & charts	exceljs in route handlers (streamed), Recharts
Tests	SQL acceptance tests, Vitest, Playwright

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 · Academic unit

Fill in details, pick the agreement

The activity, its units and one agreement link (the original document plus its chain_id). Partner name and country are snapshotted by trigger.

Functionsdocuments_valid_between()save_activity_draft()

Tablesactivitiesactivity_unitsactivity_documentsactivity_partner_snapshotdocuments

Rules: R-03, R-04, R-05, R-06

Highlighted in amber: activities, activity_units, activity_documents, activity_partner_snapshot, documents.

Step 2 of 10 · SIM Realisasi

Check drafts near deadline and open revisions

Refreshes stored chain ids (SIM Kerjasama may re-parent documents), then scans drafts and open revisions.

Functionsrun_daily_jobs()_mark_once()

Tablesactivity_documentsjob_marks

Highlighted in amber: activity_documents.

Entity relationship diagram

Entities and attributes

The core of the SIM Realisasi schema, grouped by domain. Select a table to trace its relationships.

SIM Kerjasama views
Kegiatan
Participants & verification
Calendar, config & RENSTRA
Operations & mock registries
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

SIM Kerjasama views (5)

kerjasama.units

id (PK), name, parent_id, is_academic, +2

kerjasama.agendas

id (PK), name, is_active

kerjasama.documents

id (PK), doc_number, kind, status, start_date, end_date, auto_renewed, predecessor_id (FK*), +5

kerjasama.partners

id (PK), name, country_code (FK*), merged_into_id, +1

kerjasama.countries

code (PK), name, is_domestic, +2

Kegiatan (5)

activity_units

activity_id (PK), unit_id (PK), is_submitter

activity_documents

activity_id (PK), original_document_id (PK), chain_id, out_of_scope_warning

activity_partner_snapshot

id (PK), activity_id (FK), partner_id (FK*), partner_name, country_code, +2

activity_files

id (PK), activity_id (FK), kind, version, storage_path, is_current, +6

activities

id (PK), code (UQ), agenda_id (FK*), direction, start_date, end_date, academic_year_id (FK), semester_id (FK), status, mobility_status, reporting_deadline, is_late, verified_at, +13

Participants & verification (4)

participant_set_versions

id (PK), activity_id (FK), version, status, review_note, +4

participant_students

id (PK), set_version_id (FK), section, nrp (FK*), prodi_name, home_institution, +4

participant_staff

id (PK), set_version_id (FK), employee_id (FK*), full_name, +1

participant_conflicts

id (PK), nrp, activity_a (FK), activity_b (FK), status, kept_activity_id (FK), +4

Calendar, config & RENSTRA (5)

agenda_rules

agenda_id (PK), mobility_category, counts_for_s1, +2

academic_years

id (PK), label (UQ), start_date, end_date

semesters

id (PK), academic_year_id (FK), term, cutoff_date, +2

kpi_snapshots

id (PK), academic_year_id (FK), kind, values, settings_used, superseded_by (FK), refreeze_reason, +6

kpi_snapshot_items

snapshot_id (FK), kpi_code, ref_id, is_late_addition, +2

Operations & mock registries (6)

activity_log

id (PK), activity_id (FK), kind, action, diff, in_frozen_period, +4

mock_baak.students

nrp (PK), full_name, prodi_name, category, status, +5

mock_hr.employees

employee_id (PK), full_name, unit_name, status, +1

notifications

id (PK), recipient_id, kind, read_at, +4

email_outbox

id (PK), to_email, subject, sent_at, +2

export_log

id (PK), actor_id, export_kind, row_count, contains_personal_data, +2

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
R-03	An activity links to exactly one agreement. The link stores the original document; the current one is always resolved through the renewal chain.	`activity_documents + chain_current`
R-04	An agreement is selectable if its validity overlaps the activity dates, whatever its status today. Rejected and in-process documents never are.	`documents_valid_between`
R-06	Partner name and country are snapshotted at link time; later edits or merges in SIM Kerjasama do not change them.	`Trigger`
R-09	Semester and academic year are derived from Tanggal Mulai; a date outside any configured year cannot be submitted.	`Trigger`
R-10	Reporting deadline = Tanggal Selesai + reporting_deadline_days (default 30). Late submissions are accepted and flagged.	`Trigger`
R-11	A mobility kegiatan needs participants and one mobility PDF; its track starts pending. Any other kegiatan is verified on submit.	`submit_activity`
R-16	Every counted student must have an NRP that resolves in BAAK. Unknown NRPs block submission.	`lookup_students`
R-21	Participant sets are versioned. Only the single approved version counts; approving a new one supersedes the previous.	`Trigger`
R-25	Overall status is derived, never set directly: draft, revision_requested, verified or in_verification.	`_trg_activity_status`
R-26	Nothing is rejected: a wrong submission goes back to the unit as a revision.	`RPC`
R-27a	Mobility cannot approve while the kegiatan has open duplicate-student conflicts.	`mobility_approve`
R-33	Same NRP in two non-draft mobility kegiatan of different submitting units with overlapping dates is a conflict.	`_scan_conflicts`
R-36	Only verified activities count. Activity date = Tanggal Mulai.	`compute_kpis`
R-42	Realisation of agreements counts renewal chains, so a mid-year renewal counts once.	`compute_kpis`
R-43	The denominator excludes chains starting within grace_period_months (default 6) of the cutoff; auto-renewed chains are always included.	`compute_kpis`
R-55	On each semester cutoff the system freezes a snapshot: Ganjil → ganjil_ytd, Genap → genap_full_year.	`run_daily_jobs`
R-56	A snapshot stores values, contributing IDs and the settings used. Settings changes never alter existing snapshots.	`kpi_snapshots`
R-63	Every export containing personal data is logged with actor, filters and row count.	`log_export`
R-64	No hard deletes after submission; everything is status plus log.	`RLS + RPC`

Enums & state machines

The values the database allows

activity_status

draft
in_verification
revision_requested
verified

track_status

not_required
pending
revision_requested
approved

pset_status

draft
pending
revision_requested
approved
superseded

mobility_category

jd_dd
student_exchange
short_summer
other_mobility

file_kind

ia
ir
mobility_bundle
evidence

snapshot_kind

ganjil_ytd
genap_full_year

conflict_status

open
resolved

direction

inbound
outbound

Scheduled jobs

What runs on its own

realisasi-daily-jobs → run_daily_jobs()pg_cron 18:00 UTC (01:00 WIB)Refresh chain ids, revision and deadline reminders, semester freezes
Duplicate scanTrigger, on submit and every participant changeOpen or close participant_conflicts

Security

Access is the database's job

Read-only adapterSIM Kerjasama data is reached only through views in schema kerjasama. Views cannot be FK targets, so the RPCs check those references.
RLS and per-function checksRow-level security plus permission checks inside every SQL function (_require_admin, _require_mobility, _require_team).
Roles of its ownaccount_roles keeps a Realisasi role per SIM-KS account, so SIM-KS roles stay untouched.
Personal dataParticipant names and mobility PDFs are visible to the unit and IO only; exports with student data are logged (UU PDP 27/2022).
Private filesPrivate buckets with 5-minute signed URLs.

Integration contract

Reading SIM Kerjasama without writing it

Adapter views in schema kerjasamaprofiles, units, agendas, documents, document_partners, document_scope_units, partners and countries, mapped from the SIM-KS tables (0001_kerjasama_adapter.sql).
Chains, not documentsAn activity stores the original agreement and its chain_id; the current document is always resolved through the renewal chain (chain_current, chain_root).
Feedback to SIM Kerjasamaagreement_realization and agreement_flags feed the Implementation tab and the "no activity yet" flag.

Testing

What the test suite covers

SQL acceptance tests AT-01..AT-11
Status machine
RLS boundaries
RENSTRA and KPI math
Duplicate conflicts
International Awards
Snapshots and freezes
Kerjasama adapter
Vitest unit tests
Playwright journeys

Source system

SIM Kerjasama

The system of record SIM Realisasi reports against.

View SIM Kerjasama →

Action

Academic unit

IO Mobility

IO Admin

Leadership

Create, submit and revise activities

Own unit

On behalf of a unit

See activities

Own and co-unit

All

Verified only

See participant names and mobility PDFs

Own

Verify mobility, decide duplicate students

Edit a verified activity (logged)

Participants

Settings, calendar, activity-type rules, freeze

Dashboard, awards, non-personal Excel

Own unit

Excel with student data (logged)

Own unit

Layer

Choice

Frontend

Next.js 15 App Router, TypeScript, Tailwind, shadcn/ui

Forms

react-hook-form + zod, schemas shared client and server

Data

Supabase Postgres, RLS, SQL functions, @supabase/ssr

Auth

Supabase Auth, shared with SIM Kerjasama; demo role switcher

Files

Supabase Storage, private buckets, 5-minute signed URLs

Jobs

pg_cron for reminders and freezes; triggers for the duplicate scan

Excel & charts

exceljs in route handlers (streamed), Recharts

Tests

SQL acceptance tests, Vitest, Playwright

Rule

Enforced by

R-03

An activity links to exactly one agreement. The link stores the original document; the current one is always resolved through the renewal chain.

activity_documents + chain_current

R-04

An agreement is selectable if its validity overlaps the activity dates, whatever its status today. Rejected and in-process documents never are.

documents_valid_between

R-06

Partner name and country are snapshotted at link time; later edits or merges in SIM Kerjasama do not change them.

Trigger

R-09

Semester and academic year are derived from Tanggal Mulai; a date outside any configured year cannot be submitted.

Trigger

R-10

Reporting deadline = Tanggal Selesai + reporting_deadline_days (default 30). Late submissions are accepted and flagged.

Trigger

R-11

A mobility kegiatan needs participants and one mobility PDF; its track starts pending. Any other kegiatan is verified on submit.

submit_activity

R-16

Every counted student must have an NRP that resolves in BAAK. Unknown NRPs block submission.

lookup_students

R-21

Participant sets are versioned. Only the single approved version counts; approving a new one supersedes the previous.

Trigger

R-25

Overall status is derived, never set directly: draft, revision_requested, verified or in_verification.

_trg_activity_status

R-26

Nothing is rejected: a wrong submission goes back to the unit as a revision.

RPC

R-27a

Mobility cannot approve while the kegiatan has open duplicate-student conflicts.

mobility_approve

R-33

Same NRP in two non-draft mobility kegiatan of different submitting units with overlapping dates is a conflict.

_scan_conflicts

R-36

Only verified activities count. Activity date = Tanggal Mulai.

compute_kpis

R-42

Realisation of agreements counts renewal chains, so a mid-year renewal counts once.

compute_kpis

R-43

The denominator excludes chains starting within grace_period_months (default 6) of the cutoff; auto-renewed chains are always included.

compute_kpis

R-55

On each semester cutoff the system freezes a snapshot: Ganjil → ganjil_ytd, Genap → genap_full_year.

run_daily_jobs

R-56

A snapshot stores values, contributing IDs and the settings used. Settings changes never alter existing snapshots.

kpi_snapshots

R-63

Every export containing personal data is logged with actor, filters and row count.

log_export

R-64

No hard deletes after submission; everything is status plus log.

RLS + RPC

SIM Realisasi

Try SIM Realisasi

Why realisation needed its own system

Five objectives

Every activity on record

Verified before counted

Indicators without recaps

Reports that stay put

Show which agreements are used

Three things it delivers

RENSTRA indicators

Semester reports

Dormant partnerships

Six terms the system is built on

From activity to indicator

Reporting deadline

Nothing is rejected

Duplicate students

Drafts only

Daily reminders

Semester freeze

Late changes

Reports

Four steps, one page

Seven menus

Three sources in, four results out

Who can do what

Each shows the other's data

Who gains what

How SIM Realisasi is built

One database, two systems

Business rules in the database

External systems behind an RPC

Frozen means frozen

Privacy by default

Stack and moving parts

What each step does to the database

Fill in details, pick the agreement

Check drafts near deadline and open revisions

Entities and attributes

Every table shown, column by column

Where each rule is enforced

The values the database allows

What runs on its own

Access is the database's job

Reading SIM Kerjasama without writing it

What the test suite covers

SIM Kerjasama

SIM Realisasi

Try SIM Realisasi

Why realisation needed its own system

Five objectives

Every activity on record

Verified before counted

Indicators without recaps

Reports that stay put

Show which agreements are used

Three things it delivers

RENSTRA indicators

Semester reports

Dormant partnerships

Six terms the system is built on

From activity to indicator

Reporting deadline

Nothing is rejected

Duplicate students

Drafts only

Daily reminders

Semester freeze

Late changes

Reports

Four steps, one page

Seven menus

Three sources in, four results out

Who can do what

Each shows the other's data

Who gains what

How SIM Realisasi is built

One database, two systems

Business rules in the database