BE_IBL/docs/pdp-encryption-runbook.md

# Runbook: Implementasi Enkripsi PII Pasien (UU PDP) — Production

> Dibuat: 2026-05-31 | Task: FHM31052601IBL
> Teknologi: AES-256-GCM, Trigram Blind Index, PHP CodeIgniter 3, MariaDB

---

## Arsitektur

```
.env (passphrase)
    │
    ▼
Ibl_encryptor library
├── encrypt/decrypt  → kolom _enc  (AES-256-GCM)
└── search_bidx      → kolom _bidx (HMAC-SHA256 trigram, untuk search)

m_patient (plaintext kolom lama = MASKED)
├── M_PatientName     → "FAJRI H*******"   (masked)
├── M_PatientName_enc → "base64ciphertext"  (encrypted, real value)
├── M_PatientName_bidx→ ["tok1","tok2",...] (search index)
└── ...field lainnya

Search patient: nama + HP + DOB + NIK via JSON_CONTAINS(_bidx)
Read patient  : decrypt _enc di PHP sebelum return ke client
```

---

## Pre-flight Checklist

```bash
# 1. Pastikan disk minimal 10GB free
df -h /

# 2. Catat ukuran DB sebelum (untuk verifikasi)
mysql -e "SELECT table_name, ROUND((data_length+index_length)/1024/1024,1) MB
  FROM information_schema.tables WHERE table_schema='one_lab'
  ORDER BY (data_length+index_length) DESC LIMIT 15;"

# 3. Pastikan tidak ada proses berat berjalan
pgrep -a php
```

> ⚠️ **Disk Space**: Enkripsi menambah ~1-2GB ke database (kolom _enc + _bidx).
> Jika disk penuh, lihat bagian **Troubleshooting** di bawah.

---

## Urutan Eksekusi di Production

### Step 1 — Buat file `.env`

```bash
cat > /path/to/one-api-lab/.env << 'EOF'
IBL_ENCRYPT_KEY=<passphrase-kamu>
IBL_ENCRYPT_SEARCH_KEY=<passphrase-search-kamu>
EOF
chmod 600 /path/to/one-api-lab/.env
```

> ⚠️ **WAJIB** simpan kedua passphrase di password manager sebelum lanjut.
> Key hilang = data di `_enc` tidak bisa didekripsi selamanya.

---

### Step 2 — Backup Database

```bash
bash scripts/backup_pdp_tables.sh
# Backup tersimpan di: ~/backup_pdp_YYYY_MM_DD_HHMMSS/
# Verifikasi backup ada dan tidak kosong
ls -lh ~/backup_pdp_*/
```

---

### Step 3 — Jalankan SQL Migration (tambah kolom + update trigger)

```bash
# Tambah kolom _enc dan _bidx
mysql one_lab < sql/manual_changes/2026-05-31-pdp-encrypt-columns.sql

# Update trigger m_patient & m_patientaddress (pakai _enc di log JSON)
mysql one_lab < sql/manual_changes/2026-05-31-pdp-update-triggers-enc.sql

# Ubah M_PatientDOB dari DATE ke VARCHAR(20) agar masked value tersimpan
mysql one_lab < sql/manual_changes/2026-06-11-alter-m-patient-dob-to-varchar.sql

# Ubah Mcu_PreregisterPatientsDOB dari DATE ke VARCHAR(20) (sama)
mysql one_lab < sql/manual_changes/2026-06-11-alter-mcu-preregister-dob-to-varchar.sql

# Verifikasi kolom terbentuk
mysql -e "SHOW COLUMNS FROM one_lab.m_patient LIKE '%_enc';"
mysql -e "SHOW COLUMNS FROM one_lab.m_patient LIKE '%_bidx';"
mysql -e "SHOW COLUMNS FROM one_lab.m_patientaddress LIKE '%_enc';"
mysql -e "SHOW COLUMNS FROM one_lab.m_patient WHERE Field = 'M_PatientDOB'\G"
```

---

### Step 4 — Encrypt Data Pasien (m_patient)

```bash
# Estimasi: 30-60 menit untuk 178K rows
php scripts/migrate_encrypt_patient.php

# Verifikasi
mysql -e "SELECT COUNT(*) total, COUNT(M_PatientName_enc) done
  FROM one_lab.m_patient;"
# Expected: total == done
```

---

### Step 5 — Populate NIK Bidx

```bash
# Isi search index untuk NIK (dari _enc yang sudah ada)
# Estimasi: 5-10 menit
php scripts/migrate_nik_bidx.php

# Verifikasi
mysql -e "SELECT COUNT(*) total, COUNT(M_PatientNIK_bidx) done
  FROM one_lab.m_patient WHERE M_PatientNIK_enc IS NOT NULL;"
```

---

### Step 6 — Encrypt Alamat Pasien (m_patientaddress)

```bash
# Enkripsi alamat TANPA bidx (hemat disk)
# Estimasi: 15-30 menit untuk 133K rows
php scripts/migrate_address_enc.php

# Verifikasi
mysql -e "SELECT COUNT(*) total, COUNT(M_PatientAddressDescription_enc) done
  FROM one_lab.m_patientaddress;"
# Expected: total == done
```

---

### Step 6b — Encrypt Address (m_patientaddress)

```bash
# 357 baris tersisa di dev — jalankan sampai selesai di prod
php scripts/migrate_address_enc.php

# Verifikasi
mysql -e "SELECT COUNT(*) total, COUNT(M_PatientAddressDescription_enc) done FROM one_lab.m_patientaddress;"
```

---

### Step 7 — Encrypt Tujuan Pengiriman Hasil (t_orderdelivery)

```bash
# HANYA t_orderdelivery — berisi email/HP pasien (PII nyata, bisa dimasking)
# Tabel hasil lab (t_orderdetail, so_resultentry*, dll) TIDAK dienkripsi —
# lihat bagian "Keputusan Arsitektur" di bawah
php scripts/migrate_encrypt_orderdelivery.php

# Verifikasi
mysql -e "SELECT COUNT(*) total, COUNT(T_OrderDeliveryDestination_enc) done
  FROM one_lab.t_orderdelivery;"
```

---

### Step 8 — Masking Kolom Plaintext

```bash
# Masking semua kolom PII lama di m_patient & m_patientaddress
# Format nama: "FAJRI H*******" (kata pertama penuh + inisial)
php scripts/mask_patient_plaintext.php

# Re-mask nama dengan format terbaru (jika sudah pernah dimasking sebelumnya)
php scripts/remask_patient_name.php

# Verifikasi: cek beberapa baris
mysql -e "SELECT M_PatientID, M_PatientName, M_PatientHP, M_PatientEmail
  FROM one_lab.m_patient ORDER BY RAND() LIMIT 5;"
# Expected: tampil "BUDI S******", "0812*****890", "bu***@gmail.com"
```

---

### Step 8b — Buat patient_print_cache (untuk BIRT decrypt)

```bash
mysql one_lab < sql/manual_changes/2026-05-31-pdp-birt-sp-cache-join.sql
```

Tabel ini dibutuhkan oleh:
- 6 SP header BIRT (sp_rpt_hasil_header, _2, _eng, sp_rpt_fo_001, sp_rpt_card_patient, sp_rpt_t_002)
- Birt_proxy.php controller
- Ibl_patient_decrypt library
- Qr_report_uploader, Ibl_merge_report_gateway, send_email.php

---

### Step 8c — Update sp_rpt_t_002_eng (jika dipakai)

```bash
# Cek apakah sp_rpt_t_002_eng ada
mysql -e "SHOW PROCEDURE STATUS WHERE Db='one_lab' AND Name='sp_rpt_t_002_eng'\G"
# Jika ada, update manual dengan LEFT JOIN ke patient_print_cache
# (sama seperti sp_rpt_t_002 di 2026-05-31-pdp-birt-sp-cache-join.sql)
```

---

### Catatan Disk Space untuk Production

> ⚠️ **Sangat penting**: Pastikan disk minimal **10GB free** sebelum mulai.
> Trigger m_patient + m_patientaddress nulis ke log_patient setiap UPDATE.
> Untuk migration + masking 178K+133K rows → log bisa 2-3GB.
>
> **Strategi aman**:
> 1. DROP trigger dulu (`vm_patient_ai`, `vm_patient_bu`, `m_patientaddress_ai`, `m_patientaddress_bu`)
> 2. Jalankan semua migration + masking scripts
> 3. Recreate trigger: `mysql one_lab < sql/manual_changes/2026-05-31-pdp-update-triggers-enc.sql`
>
> File trigger SQL ada di: `sql/manual_changes/2026-05-31-pdp-update-triggers-enc.sql`

---

### Step 9 — Truncate Log Lama (Opsional tapi Direkomendasikan)

```bash
# log_patient berisi JSON plaintext PII dari sebelum enkripsi
# Truncate meningkatkan compliance (hapus data PII lama yang tidak terenkripsi)
mysql one_lab_log -e 'TRUNCATE TABLE log_patient;'

# Verifikasi
mysql -e "SELECT COUNT(*) FROM one_lab_log.log_patient;"
# Expected: 0
```

---

### Step 10 — Verifikasi End-to-End

```bash
# 1. Cek search patient berjalan
curl -s -X POST https://[SERVER]/mockup/fo/ibl_registration/patient/search \
  -H "Content-Type: application/json" \
  -d '{"token":"[VALID_TOKEN]","search":"BUDI","noreg":"","current_page":1}' \
  | python3 -m json.tool | head -20

# 2. Cek data terdekripsi dengan benar (nama muncul lengkap, bukan masked)
# Expected di response: "M_PatientName": "BUDI SANTOSO" (bukan "BUDI S******")

# 3. Cek disk usage akhir
df -h /

# 4. Cek MySQL masih sehat
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
```

---

## Field yang Dienkripsi

### `one_lab.m_patient`
| Field | Tipe kolom plain | `_enc` | `_bidx` (search) | Catatan |
|-------|:---:|:------:|:----------------:|---------|
| M_PatientName | VARCHAR | ✅ | ✅ | |
| M_PatientHP | VARCHAR | ✅ | ✅ | |
| M_PatientDOB | **VARCHAR(20)** | ✅ | ✅ | Diubah dari DATE → VARCHAR agar mask `**-**-YYYY` tersimpan |
| M_PatientNIK | VARCHAR | ✅ | ✅ | ⚠️ `M_PatientNIK_bidx` diisi dari **`M_PatientIDNumber`**, bukan NIK |
| M_PatientEmail | VARCHAR | ✅ | — | |
| M_PatientPhone | VARCHAR | ✅ | — | |
| M_PatientPOB | VARCHAR | ✅ | — | |
| M_PatientIDNumber | VARCHAR | ✅ | — | Masked plain, bidx-nya lewat `M_PatientNIK_bidx` |
| M_PatientNIP | VARCHAR | ✅ | — | |

### `one_lab.m_patientaddress`
| Field | `_enc` | `_bidx` |
|-------|:------:|:-------:|
| M_PatientAddressDescription | ✅ | — (dihapus, hemat disk) |
| M_PatientAddressEmail | ✅ | — |
| M_PatientAddressPhone | ✅ | — |

### Tujuan Pengiriman Hasil (PII nyata)
| Tabel | Field |
|-------|-------|
| `t_orderdelivery` | T_OrderDeliveryDestination (email/HP) |

### Log
| Tabel | Field |
|-------|-------|
| `one_lab_log.log_patient` | Log_PatientJsonBefore/After (**di-truncate di production**) |
| `one_lab_log.log_fo` | Log_FoJson |
| `one_lab_log.log_resultentry` | Log_ResultEntryJSONBefore/After |

### TIDAK Dienkripsi (keputusan disengaja)
| Tabel | Alasan |
|-------|--------|
| `t_orderdetail`, `t_orderheader` | Nilai hasil lab bukan PII tanpa identitas pasien. Trigger butuh plaintext untuk flag H/L/N. |
| `so_resultentry_*`, `member_eligible` | Nilai klinis, bukan PII langsung. Plaintext dibutuhkan proses operasional. |
| `mcu_resume_results` | JSON nilai lab tanpa PII. Enkripsi memberatkan global MCU report. |

**Perlindungan hasil lab** tetap via: identitas pasien terenkripsi di `m_patient` + access control + audit log.

---

## Format Masking Kolom Plaintext

| Field | Format | Contoh |
|-------|--------|--------|
| Nama (multi kata) | Kata pertama penuh + inisial+bintang per kata | `FAJRI H******* M****` |
| Nama (satu kata) | 2 karakter pertama + bintang sisanya | `SI**` / `BI*****` |
| HP/Phone | 4 digit pertama + bintang + 3 digit akhir | `0812*****890` |
| Email | 2 huruf pertama + *** + @domain | `fa***@gmail.com` |
| NIK/IDNumber | 4 digit pertama + *** + 2 digit akhir | `3201***01` |
| POB | 2 huruf pertama + *** | `JA***` |
| Alamat | 5 karakter pertama + *** | `Jl. S***` |

---

## Search Pasien

Parameter via `+` separator di field `search`:
```
search=NAMA+HP+DOB+NIK
```
| Posisi | Field | Contoh |
|--------|-------|--------|
| `e[0]` | Nama (min 3 karakter) | `BUD` |
| `e[1]` | HP (min 3 karakter) | `081` |
| `e[2]` | DOB format dd-mm-yyyy | `25-0` |
| `e[3]` | NIK (min 3 karakter) | `320` |

---

## BIRT Report & FPDF

### Strategi Decrypt untuk Report

**BIRT Reports** (`print_transaction`):
- PHP `Birt_proxy.php` → decrypt PII → INSERT `patient_print_cache` → call BIRT
- 6 SP header yang diupdate dengan LEFT JOIN ke cache: `sp_rpt_hasil_header`, `sp_rpt_hasil_header_2`, `sp_rpt_hasil_header_eng`, `sp_rpt_fo_001`, `sp_rpt_card_patient`, `sp_rpt_t_002`
- SP signature tidak berubah — `.rptdesign` tidak perlu diupdate
- Cache TTL: 5 menit, auto-cleanup di request berikutnya

**Endpoint Birt_proxy:**
- `POST /tools/birt_proxy/stream` — return PDF binary langsung
- `POST /tools/birt_proxy/get_url` — return URL untuk buka di browser

**FPDF Controllers** (`tools/`):
- `Inform_consent.php`, `Medical_checkup_report.php` — decrypt langsung dari `_enc` (direct SQL)
- `Kartu_kontrol.php`, `Rpt_t_002.php`, `Rpt_t_002_eng.php` — populate cache → call SP → delete cache

**SQL produksi yang perlu dijalankan:**
```bash
mysql one_lab < sql/manual_changes/2026-05-31-pdp-birt-sp-cache-join.sql
```

---

## Controller yang Sudah Diupdate (Decrypt + Encrypt)

| Controller | Fungsi |
|-----------|--------|
| `mockup/fo/ibl_registration/Patient.php` | Search, add, edit pasien FO |
| `mockup/fo/ibl_registration/Order.php` | Order management (nama, email, HP) |
| `mockup/fo/ibl_registration/Payment.php` | Kasir (nama pasien) |
| `mockup/fo/ibl_registration/History.php` | History delivery (email/HP) |
| `mockup/fo/ibl_registration/Delivery.php` | Pengiriman hasil (email/HP) |
| `mockup/fo/ibl_registration/Order copy.php` | Order MCU |
| `mockup/masterdata/Patientv4.php` | Masterdata pasien — tampil data **lengkap** |
| `klinik/Registrationv3.php` | Registrasi & edit pasien klinik (`save()`, `newpatient()`) |
| `mockup/setupmcuoffline-ibl/Preregister.php` | MCU offline IBL — batch CSV & newpatient |
| `mockup/mcuoffline/Preregisterapp.php` | MCU offline app — batch CSV, search, newpatient |

## mcu_preregister_patients — Masking

Data PII yang dimasking saat INSERT ke staging table `mcu_preregister_patients`:

| Field | Tipe kolom | Mask |
|-------|:---:|------|
| `*PatientName` | VARCHAR | `_mask_name()` |
| `*KTP` | VARCHAR | `_mask_id()` |
| `*NIP` / `*NIK` | VARCHAR | `_mask_id()` |
| `*Email` | VARCHAR | `_mask_email()` |
| `*Hp` | VARCHAR | `_mask_phone()` |
| `*DOB` | **VARCHAR(20)** | `_mask_dob()` — diubah dari DATE → VARCHAR agar mask `**-**-YYYY` tersimpan |

Tidak ada kolom `_enc` di tabel ini — data asli bisa diambil dari `m_patient` via `*M_PatientID`.

4 lokasi INSERT yang sudah diupdate: `savecsv()` & `save()` di setupmcuoffline-ibl, `save()` & `savenewform()` di mcuoffline/Preregisterapp.

## one_lab_dashboard.mcu_patient — Enkripsi via SP

Kolom `Mcu_PatientName` dan `Mcu_PatientDOB` diubah ke `TEXT` dan menyimpan
nilai `_enc` dari `one_lab.m_patient` (JOIN via `Mcu_PreregisterPatientsM_PatientID`).

SP yang diupdate:
- `sp_upsert_mcu_patient_by_preregister_id`
- `sp_upsert_mcu_patient_by_mgm_mcuid`

SQL: `sql/manual_changes/2026-05-31-pdp-mcu-patient-dashboard-enc-sp.sql`

```bash
mysql one_lab < sql/manual_changes/2026-05-31-pdp-mcu-patient-dashboard-enc-sp.sql
```

## Controller yang Belum Diupdate (Tampil Data Masked)

Semua ~300+ controller lain otomatis tampilkan data termasking karena kolom
plaintext sudah dimasking. Tidak perlu update satu-satu untuk compliance dasar.

**Sprint berikutnya** — update controller prioritas yang butuh data lengkap:
- Sampling (samplinglab-vvii, samplingelectromedisnew)
- Result entry (resultentrysoothers-v20, resultentrysoxray-v8, resultentrysoelectromedis-v8)
- Result verification
- MCU resume (resumeindividufacelift)

---

## Troubleshooting

### Disk Penuh Saat Migration

```bash
# Cek pemakai disk terbesar
du -sh /home/one/* /tmp/* 2>/dev/null | sort -rh | head -20

# Bersihkan file lama yang aman dihapus:
# - /home/one/project/one/dump_*.sql (backup lama)
# - /home/one/project/one/*.tar.gz (archive lama)
# - /tmp/intelephense (cache IDE)

# Bersihkan journal (butuh sudo)
sudo journalctl --vacuum-size=300M
sudo truncate -s 0 /var/log/btmp
```

### MySQL Crash (Disk Penuh)

```bash
# Restart MySQL setelah disk dibebaskan
sudo systemctl start mariadb
# atau
sudo service mysql start
```

### Migration Lambat (Trigger Overhead)

Trigger `m_patientaddress_bu` nulis ke `log_patient` setiap UPDATE.
Jika log_patient sangat besar (>1GB) dan disk hampir penuh:

```bash
# Truncate log lama (aman — data PII lama di log justru harus dihapus untuk compliance)
mysql one_lab_log -e 'TRUNCATE TABLE log_patient;'
```

### Restore jika Ada Masalah

```bash
mysql one_lab < ~/backup_pdp_YYYY_MM_DD_HHMMSS/one_lab_tables.sql
mysql one_lab_log < ~/backup_pdp_YYYY_MM_DD_HHMMSS/one_lab_log_tables.sql
```

---

## Catatan Key Management

- Key disimpan di `.env` — **JANGAN commit ke git** (sudah ada di `.gitignore`)
- Backup passphrase di: password manager + file enkripsi di lokasi terpisah dari server
- Key rotation di masa depan: perlu re-encrypt semua data (decrypt lama → encrypt baru)
- Tidak ada recovery jika key hilang