YESS Programme - MIS
Role & duration: Senior Assistant Professional for Management Information System, sole full-stack engineer on the MIS. 2022-2025 under the IFAD YESS Programme, Ministry of Agriculture Indonesia.
TL;DR
- Problem: Inherited a broken, untrusted MIS with siloed apps and dirty data that hindered IFAD and Ministry reporting.
- Solution: Fixed the database foundation, unified 4 different applications into a single dashboard using Metabase, and automated financial consolidation.
- Impact: Query times dropped from 10s to <2s, reporting from 30 days to 1 day, and the system became the official audited KPI source.
- Tech Stack: PHP (Laravel, CodeIgniter), MySQL, PostgreSQL, Metabase, Cloudflare.
Context & Problem
I joined in year four of the project as the first engineer with a proper software background. The previous consultant had no formal IT training.
- Who had the problem. The YESS Programme management, Ministry of Agriculture (MoA), lender IFAD, and BPK all needed reliable data to track programme KPIs and justify disbursements, but the MIS couldn’t provide it.
- Why it mattered. YESS is an IFAD-funded agricultural youth entrepreneurship programme. Without a credible MIS, there was no verifiable way to show that training, grants, and financial inclusion activities were happening at scale. This was a massive compliance and disbursement risk for both the Ministry and the lender.
- Condition before. The codebase was PHP native with no framework, all logic stacked in single files. The database had no constraints, no foreign keys, and was full of duplicates. Multiple applications (legacy MIS, government grant registration portal, LMS, news website) operated as siloes with no shared reporting. The finance team spent 30 days each month manually consolidating reports. No one in management trusted the numbers.
Constraint
- Effectively solo for most of the engagement. For the first stretch I worked alongside another MIS consultant, but the collaboration was unproductive, and by early 2023 their contract wasn’t renewed. I then operated as the sole engineer for nearly a year, making every technical decision independently, before a new consultant joined in late 2023 to work alongside me for the remainder of the project.
- Inherited codebase, no rewrite budget. Stakeholder expectations and programme timelines didn’t allow a full rewrite. Every improvement had to be incremental and backward-compatible.
- Multi-stakeholder accountability. Every data change had to be explainable to non-technical audiences like MoA eselon officials, IFAD field representatives, and government auditors. “It was broken before” is not a valid audit response.
- No formal technical requirements. The programme had never produced a structured MIS spec. I had to work backward from what KPIs IFAD and MoA actually needed to report.
Decision & Trade-off
Fix the data layer before touching the application. Any new feature or dashboard built on the existing database would just surface dirty numbers faster. The root issue was structural. Beneficiary records could be deleted while the farmer had already received programme benefits (no foreign key protection); duplicate NIK (national ID numbers) meant the same person was counted multiple times toward KPIs; heavy multi-table joins with no indexing were causing query timeouts. Rather than building on top of the mess, I fixed the foundation first, adding unique constraints, foreign keys across dozens of tables, and proper column indexing, coordinating with regional data-entry operators to resolve conflicts before deleting anything.
Build a unified KPI dashboard, not isolated fixes. The grant portal, LMS, legacy MIS, and news website each ran on different stacks and separate databases. I tried fixing each application individually first. But since they didn’t share a database, there was still no consolidated picture of programme achievement for stakeholders. The only way to give IFAD, MoA, and eselon management a single view was to build a dashboard layer that pulled from all sources regardless of origin. I did this using Metabase to surface aggregated data without requiring users to touch SQL.
Separate the consolidation logic from the reporting layer. The finance team was spending 30 days per month manually reconciling reports. I built a dedicated module to handle the aggregation and business rules, keeping that logic precise and auditable, and surfaced the output through Metabase for non-technical users. Keeping the two layers separate meant finance staff could explore and export data flexibly without touching anything critical.
Implementation
Stack: PHP (Laravel, CodeIgniter, Yii, native), MySQL, PostgreSQL, Metabase, Docker, Linux, Cloudflare (WAF + CDN).
Database tuning. Added unique constraints, foreign keys, and column indexing across the schema. Query optimization brought critical views from ~10 seconds down to under 2 seconds, and duplicate removal eliminated roughly 30% of total records.
Unified KPI dashboard. Consolidated data from the legacy MIS, grant registration system, LMS (Moodle), and content platform into a single reporting layer surfaced through Metabase. This gave non-technical stakeholders one place to track programme achievement.
Financial consolidation module. Replaced 30 days of manual monthly work with an automated aggregation pipeline. The finance team went from dreading month-end to finishing it in a day.
Cross-ministry data cleaning (off-project request). Cleaned and aggregated 4 million farmer records for cross-ministry data matching with SIKP (Ministry of Finance’s agricultural credit database). The output was presented at a DPR (parliament) session.
Scalability & DDoS mitigation. When a separate agricultural extension reporting app went from ~1,000 to ~30,000 concurrent users overnight, I was brought in to implement load balancing (least-connection), Cloudflare WAF (AS number filtering, geo-based human challenge, IP blocklist), and CDN offloading, all while mitigating an active DDoS attack during the incident.
Security patch (post-breach). A file upload vulnerability was exploited to deface the MIS and host unauthorized content on the server. I implemented strict file type validation, server-side sanitization, and moved DNS management to Cloudflare with WAF enabled. No successful intrusion since.
Business Impact
| Metric | Before | After |
|---|---|---|
| Monthly financial report consolidation | 30 days (manual) | 1 day (automated) |
| Query response time (critical views) | ~10 seconds | < 2 seconds |
| Duplicate records in database | ~30% of total data | Eliminated |
| Concurrent user support (ext. app) | ~1,000 | ~30,000 (stabilized) |
| MIS credibility with stakeholders | Distrusted | Used as official KPI source |
The unified KPI dashboard became the official programme achievement reference used by IFAD, MoA, and BPK for audit and disbursement review. The farmer data aggregation (4M records) was presented at a DPR session as cross-ministry policy data.
Lesson Learned
Prioritize by business impact, not engineering interest. I sometimes spent too long on improvements that were technically satisfying but went unnoticed by the people who mattered. I learned to ask: “who will notice this, and does it affect what they report or decide?” If a change doesn’t move a KPI that someone is accountable for, it should probably wait.
Collect only what you need. There was a culture of gathering as much data as possible from field operators since more data felt like more coverage. But most of it was never queried, while collecting it consumed time and goodwill from regional staff. I should have mapped the required KPIs first, then worked backward to define exactly what data was needed.
Tech debt has a due date, not a maybe date. The upload vulnerability that got us hacked was something I had noticed but deprioritized. Deferring a security analysis isn’t “doing it later,” it’s “doing it after the breach.” Every piece of deferred work is a future incident waiting for the right moment.