What is SIMAKES Bungo?

Klinik Bhayangkara Polres Bungo is a real police clinic in Bungo, Jambi. They needed a way to track their medicine and medical equipment inventory, manage patients and prescriptions, and generate reports, without paying for expensive pharmacy software or relying on an internet connection. I built them a desktop app that handles all of it offline, backed by a single SQLite file.

SIMAKES (Sistem Manajemen Obat dan Alat Kesehatan) split-catalogs medicines and medical equipment, tracks stock in/out per batch with FIFO/FEFO awareness, manages patient and medical staff records, handles prescription transactions, and exports date-filtered reports to Excel with actual embedded charts. It also does ZIP backup/restore of the entire database plus uploaded images in one click, has 32 DaisyUI themes (the staff at the clinic like to switch between cyberpunk and pastel), and seeds dummy data for simulation training so new staff can learn the system without messing up real inventory numbers.

How I Built It

I scaffolded this project with Electron + Vite (using electron-vite for the build tooling) and React 19 on the frontend. The database is a local SQLite file managed through better-sqlite3, which gives synchronous, fast queries from the Electron main process, no async overhead, no connection pools, just direct disk access. The renderer communicates through Electron's IPC bridge (contextBridge in the preload script) so the frontend never touches the database directly.

The dashboard analytics use Recharts with responsive bar charts, area charts for prescription trends, and pie charts for funding source breakdowns. I built the inventory management with batch-level stock tracking, every medicine can have multiple batches with different expiry dates, and the prescription input screen enforces FIFO ordering so the oldest stock gets used first. The reports page groups data by medicine, lets you filter by date range with week/month/year presets, and exports everything to Excel using exceljs on the Node side. Each export comes with embedded native Excel charts, so the clinic staff gets a ready-to-print document without touching the data themselves.

One thing I am proud of is the prescription transaction logic. When you create a prescription, it runs inside a single SQLite transaction: it inserts the prescription record, inserts each prescription item, and simultaneously deducts stock from the corresponding batches while recording OUT transactions. If anything fails halfway through, the entire thing rolls back, no partial prescriptions, no phantom stock changes.

// Atomic prescription insertion with stock deduction
const runTx = db.transaction((txData) => {
  const res = insertPrescription.run(
    txData.patient_id, txData.doctor_id, 
    txData.operator_id, txData.tanggal_resep
  );
  const presId = res.lastInsertRowid as number;

  for (const item of txData.items) {
    insertItem.run(presId, item.item_id, item.batch_id, 
                   item.jumlah, item.harga_saat_ini);
    addTransaction({
      batch_id: item.batch_id, tipe: 'OUT',
      jumlah: item.jumlah, tanggal: txData.tanggal_resep,
      keterangan: `Resep Pasien ID: ${txData.patient_id}`
    });
  }
  return { id: presId };
});
return runTx(data);

The backup system is also worth mentioning. I used adm-zip to compress the SQLite file plus the entire uploads/images directory (split into obat/ and alkes/ subfolders) into a single ZIP. Restore extracts everything to a temp directory, hot-swaps the database file, copies images back, and reinitializes the connection, all without restarting the app. Plus there is a one-click "reset to dummy data" button for training simulations, which drops and reseeds the database with realistic sample medicines, patients, and staff.

What I Learned

This was my first real client project, software deployed to actual users who depend on it daily. It taught me that polish matters: the 32 themes feature was something I added almost as a joke, but the clinic staff genuinely love switching between them. It also taught me that offline-first architecture (SQLite, no server, no auth) can be a feature, not a limitation, the clinic has unreliable internet, and they cannot afford downtime during patient consultations. The app just works, double-click from the desktop, no loading spinners, no API errors.

On the technical side, I got deep into Electron's security model, properly isolating the main process from the renderer through contextBridge, handling native file dialogs for save/open operations, and managing the Python helper script for Excel chart generation. I also learned that schema migrations in SQLite are manual and painful (you cannot drop columns, only add them), so I built a PRAGMA-based migration check that runs on every app startup to add missing columns without breaking existing installations. A small detail, but critical for a deployed desktop app that auto-updates.