Runbook — Tenant Onboarding End-to-End

Version: 1.0 Last Updated: 2026-05-26 Owner: DBA + Backend Lead

Dokumen ini berisi prosedur lengkap menambahkan tenant baru ke sistem ERP multi-tenant, dari pembuatan schema PostgreSQL hingga aktivasi CDC pipeline.

---

📋 Prerequisites

Requirement	Status	Owner
Kode tenant unik (max 5 char, lowercase)	[ ]	PM
Nama tenant lengkap	[ ]	PM
Bidang usaha (dagang/jasa/manufaktur)	[ ]	PM
Server PostgreSQL siap	[ ]	DevOps
Kredensial MariaDB legacy (jika migrasi)	[ ]	DBA
Tenant pilot atau production?	[ ]	PM

---

📊 Overview Arsitektur

┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL erp                           │
├─────────────────────────────────────────────────────────────┤
│  Schema pusat (shared):                                     │
│    - pusat.wilayah_* (referensi global)                     │
│    - pusat.tenant_* (registry tenant)                       │
│                                                             │
│  Schema logika (shared):                                    │
│    - logika.fn_* (fungsi bisnis & trigger)                  │
│                                                             │
│  Schema tenant (per tenant):                                │
│    - tenant_<kode>.transaksi                                │
│    - tenant_<kode>.transaksi_detail                          │
│    - tenant_<kode>.jurnal                                   │
│    - tenant_<kode>.stok                                     │
│    - ... (60+ tabel operasional)                            │
└─────────────────────────────────────────────────────────────┘
                              │
                              ▼
              ┌───────────────────────────────┐
              │   Debezium CDC Connector      │
              │   (Kafka Connect)             │
              └───────────────────────────────┘
                              │
                              ▼
              ┌───────────────────────────────┐
              │   Apache Kafka Topics         │
              │   erp.tenant_<kode>.*         │
              └───────────────────────────────┘
                              │
                              ▼
              ┌───────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────┐
│                  ClickHouse erp_clickhouse                  │
├─────────────────────────────────────────────────────────────┤
│  Tabel per tenant (via kolom tenant_kode):                  │
│    - transaksi, transaksi_riwayat                           │
│    - transaksi_detail, transaksi_detail_riwayat             │
│    - jurnal, jurnal_riwayat                                 │
│    - stok, stok_mutasi                                      │
│    - akun, akun_riwayat, ...                                │
│                                                             │
│  Materialized Views:                                        │
│    - mv_saldo_akun                                          │
│    - mv_dashboard_penjualan_harian                          │
│    - mv_dashboard_penjualan_produk                          │
└─────────────────────────────────────────────────────────────┘

---

🚀 Step-by-Step Onboarding

Phase 1: Persiapan (H-7)

1.1 — Validasi Kode Tenant

# Cek kode unik (max 5 karakter, lowercase, tanpa spasi)
psql "$ERP_MIGRASI_URL" -c "
  SELECT kode FROM pusat.tenant WHERE kode = 'namatenant';
"

# Harus return 0 rows

Aturan penamaan: - lowercase: tenant_sparepart (bukan tenant_Sparepart) - max 5 karakter: spare, leon, manuf - tanpa spasi/simbol: hanya [a-z0-9]

1.2 — Siapkan Kredensial Legacy (jika migrasi)

# Test koneksi MariaDB
mysql -h <host> -u <user> -p -e "SELECT 1"

# Cek ukuran data
mysql -h <host> -u <user> -p -e "
  SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = '<database_legacy>'
  ORDER BY TABLE_ROWS DESC;
"

---

Phase 2: Buat Tenant di PostgreSQL (H-3)

2.1 — Jalankan Script Buat Tenant

cd /home/sic/Projects/migrasi/database

# Dry-run dulu
scripts/buat_tenant.sh \
  --kode namatenant \
  --nama "Nama Tenant Lengkap" \
  --bidang dagang \
  --dry-run

# Eksekusi nyata
scripts/buat_tenant.sh \
  --kode namatenant \
  --nama "Nama Tenant Lengkap" \
  --bidang dagang \
  --yes

Script ini melakukan: 1. Insert ke pusat.tenant 2. Buat schema tenant_namatenant 3. Set ownership ke erp_owner 4. Grant permissions ke erp_app, erp_readonly 5. Buat semua tabel (60+) 6. Apply semua migrasi (V001 s.d V009+)

Durasi: 5-15 menit (tergantung jumlah migrasi)

2.2 — Verifikasi Schema

# Cek schema terdaftar
psql "$ERP_MIGRASI_URL" -c "
  SELECT kode, nama, bidang, created_at
  FROM pusat.tenant
  WHERE kode = 'namatenant';
"

# Cek semua tabel ada
psql "$ERP_MIGRASI_URL" -c "
  SELECT count(*) AS table_count
  FROM information_schema.tables
  WHERE table_schema = 'tenant_namatenant'
    AND table_type = 'BASE TABLE';
"
# Harus >= 60

# Cek fungsi logika accessible
psql "$ERP_MIGRASI_URL" -c "
  SELECT routine_name
  FROM information_schema.routines
  WHERE routine_schema = 'logika'
  LIMIT 10;
"

2.3 — Apply Migrasi Tenant (jika belum otomatis)

cd /home/sic/Projects/migrasi/database

# Jalankan semua migrasi tenant
ERP_MIGRASI_URL="postgresql://..." \
  scripts/jalankan_migrasi_tenant.sh \
  --tenant namatenant

---

Phase 3: Load Data Awal (H-2)

3.1 — Load Referensi Global (sekali untuk semua tenant)

cd /home/sic/Projects/migrasi/database/etl

# Load wilayah (provinsi, kabupaten, kecamatan, desa)
python3 load_wilayah.py \
  --source ../data/wilayah_indonesia.csv \
  --target "$ERP_MIGRASI_URL"

3.2 — ETL dari Legacy (jika migrasi)

cd /home/sic/Projects/migrasi/database/etl

# 1. Ekstrak MariaDB → staging
python3 etl_main.py \
  --source mysql://legacy_host/legacy_db \
  --target "$ERP_MIGRASI_URL" \
  --tenant namatenant \
  --stage-only

# 2. Transform staging → schema tenant
python3 etl_transform.py \
  --tenant namatenant \
  --mappings ../mappings/namatenant_mapping.json

# 3. Bangun ulang stok
psql "$ERP_MIGRASI_URL" -c "
  SELECT logika.bangun_ulang_stok();
"

# 4. Validasi awal
python3 ../scripts/rekonsiliasi-kpi.py \
  --tenant namatenant \
  --legacy mysql://legacy_host/legacy_db

Acceptance Criteria: - [ ] Count transaksi PG vs legacy match (±5%) - [ ] Saldo akun match (±1%) - [ ] Stok match (±0 toleransi) - [ ] Jurnal imbang (100%)

Durasi: 30-120 menit (tergantung volume)

---

Phase 4: Aktivasi CDC Pipeline (H-1)

4.1 — Tambah Tabel ke Publication Debezium

-- Jalankan di PostgreSQL (superuser)
ALTER PUBLICATION debezium_erp_pub
  ADD TABLE tenant_namatenant.transaksi,
           tenant_namatenant.transaksi_detail,
           tenant_namatenant.jurnal,
           tenant_namatenant.stok,
           tenant_namatenant.akun,
           tenant_namatenant.akun_klas,
           tenant_namatenant.akun_subklas;

-- Verifikasi
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'debezium_erp_pub'
  AND schemaname = 'tenant_namatenant'
ORDER BY tablename;

Harus muncul 7 tabel.

4.2 — Update Debezium Connector Config

Edit file database/connectors/debezium-postgres-erp.json:

{
  "table.include.list": "tenant_leontech.transaksi,tenant_leontech.transaksi_detail,...,tenant_namatenant.transaksi,tenant_namatenant.transaksi_detail,tenant_namatenant.jurnal,tenant_namatenant.stok,tenant_namatenant.akun,tenant_namatenant.akun_klas,tenant_namatenant.akun_subklas"
}

Deploy update:

# Non-destruktif (production-safe)
curl -X PUT http://localhost:8083/connectors/debezium-postgres-erp/config \
  -H "Content-Type: application/json" \
  -d @database/connectors/debezium-postgres-erp.json

# Verifikasi
curl -s http://localhost:8083/connectors/debezium-postgres-erp/status | python3 -m json.tool

4.3 — Restart Consumer dengan Tenant Baru

# Stop consumer lama
pm2 stop clickhouse-etl

# Start dengan env TENANT_SCHEMAS diperbarui
TENANT_SCHEMAS=tenant_leontech,tenant_sparepart,tenant_namatenant \
pm2 start database/etl/clickhouse/consumer_clickhouse.py \
  --name clickhouse-etl --interpreter python3

# Cek log
pm2 logs clickhouse-etl --lines 30
# Harus terlihat: "Topics: [..., 'erp.tenant_namatenant.transaksi', ...]"

4.4 — Verifikasi CDC Berjalan

# 1. Cek lag Kafka
sudo docker exec clickhouse-kafka-1 \
  kafka-consumer-groups \
  --bootstrap-server localhost:9092 \
  --group clickhouse-etl-group \
  --describe | grep namatenant

# Lag harus < 1000

# 2. Cek data masuk ClickHouse
CH="http://localhost:8123/?user=knavinkids&password=knavinkids"

curl -s -X POST "$CH" \
  --data "SELECT tenant_kode, count() FROM erp_clickhouse.transaksi WHERE tenant_kode='namatenant' GROUP BY tenant_kode"

# Harus return row dengan count > 0

---

Phase 5: Initial Load ClickHouse (H-1)

Catatan: Jika tenant baru hasil migrasi dari legacy, data sudah di-load di Phase 3. CDC hanya untuk delta.

5.1 — Bulk Load Manual (opsional, jika perlu reset)

cd /home/sic/Projects/migrasi/database/scripts

# Bulk load transaksi
python3 bulk_load_ch.py \
  --tenant namatenant \
  --table transaksi

# Bulk load jurnal
python3 bulk_load_ch.py \
  --tenant namatenant \
  --table jurnal

# Bulk load stok
python3 bulk_load_ch.py \
  --tenant namatenant \
  --table stok

5.2 — Validasi PG vs CH

python3 rekonsiliasi-kpi.py \
  --tenant namatenant \
  --clickhouse \
  --verbose

Acceptance Criteria: - [ ] Count transaksi PG = CH (±0) - [ ] Count jurnal PG = CH (±0) - [ ] Count stok PG = CH (±0) - [ ] Saldo akun MV = PG (±0)

---

Phase 6: Setup Backend & Auth (H-0)

6.1 — Daftarkan Tenant ke Backend

# Backend akan auto-detect via schema pusat.tenant
# Tidak perlu manual register

# Verifikasi via API
curl -X GET http://localhost:3000/api/tenants \
  -H "Authorization: Bearer <admin_token>" | jq .

# Harus muncul tenant_namatenant di list

6.2 — Setup User Pertama Tenant

# Via backend API
curl -X POST http://localhost:3000/api/auth/sesi \
  -H "Content-Type: application/json" \
  -d '{
    "email": "admin@namatenant.com",
    "password": "temp_password"
  }'

# Pilih tenant
curl -X POST http://localhost:3000/api/tenants/namatenant/pilih \
  -H "Authorization: Bearer <jwt_token>"

6.3 — Verifikasi Pool Koneksi Tenant

# Cek pool backend
curl http://localhost:3000/api/health | jq .tenant_pool

# Harus ada koneksi aktif ke tenant_namatenant

---

Phase 7: Go-Live (H-0)

7.1 — Smoke Test Operasional

# 1. Login
TOKEN=$(curl -s -X POST http://localhost:3000/api/auth/sesi \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@namatenant.com","password":"..."}' | jq -r '.token')

# 2. Pilih tenant
curl -s -X POST http://localhost:3000/api/tenants/namatenant/pilih \
  -H "Authorization: Bearer $TOKEN"

# 3. Buat transaksi test
curl -s -X POST http://localhost:3000/api/transaksi/PJ \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "tanggal": "2026-05-26",
    "kontak_id": 1,
    "detail": [...]
  }'

# 4. Verifikasi muncul di CDC < 10 detik
sleep 5
curl -s -X POST "$CH" \
  --data "SELECT count() FROM erp_clickhouse.transaksi WHERE tenant_kode='namatenant' AND nomor_bukti='PJ-20260526-00001'"

7.2 — Monitoring Dashboard

Buka monitor web: http://100.124.170.3:5000

Cek: - [ ] Tenant namatenant muncul di list - [ ] Lag Kafka < 1000 - [ ] Consumer running tanpa error - [ ] DLQ (dead letter queue) = 0

---

✅ Checklist Onboarding

Pre-Onboarding

#	Task	Status	Owner
1	Kode tenant unik & valid	[ ]	PM
2	Kredensial legacy siap	[ ]	DBA
3	Server PostgreSQL ready	[ ]	DevOps

Phase 2 (PostgreSQL)

#	Task	Status	Owner
1	Schema tenant_<kode> created	[ ]	DBA
2	Semua tabel (60+) ada	[ ]	DBA
3	Migrasi V001-V009+ applied	[ ]	DBA
4	Fungsi logika.* accessible	[ ]	DBA

Phase 3 (Data Load)

#	Task	Status	Owner
1	Referensi global loaded	[ ]	DBA
2	ETL legacy → PG complete	[ ]	DBA
3	Stok rebuild OK	[ ]	DBA
4	Rekonsiliasi awal LULUS	[ ]	QA

Phase 4 (CDC)

#	Task	Status	Owner
1	Publication updated (7 tabel)	[ ]	DBA
2	Debezium config updated	[ ]	DevOps
3	Consumer restarted	[ ]	DevOps
4	Lag Kafka < 1000	[ ]	DevOps

Phase 5 (ClickHouse)

#	Task	Status	Owner
1	Initial load complete	[ ]	DBA
2	PG vs CH count match	[ ]	QA
3	MV saldo_akun terisi	[ ]	DBA

Phase 6 (Backend)

#	Task	Status	Owner
1	Tenant terdeteksi API	[ ]	Backend
2	User pertama created	[ ]	Backend
3	Pool koneksi aktif	[ ]	Backend

Phase 7 (Go-Live)

#	Task	Status	Owner
1	Smoke test LULUS	[ ]	QA
2	Monitoring dashboard OK	[ ]	DevOps
3	User acceptance sign-off	[ ]	PM

---

🔄 Troubleshooting

Schema tidak ter-create

# Cek error migrasi
psql "$ERP_MIGRASI_URL" -c "
  SELECT * FROM logs.migrasi_log
  WHERE tenant_kode = 'namatenant'
  ORDER BY executed_at DESC
  LIMIT 10;
"

CDC lag tinggi (>10000)

# 1. Cek consumer log
pm2 logs clickhouse-etl --lines 100

# 2. Cek Kafka topic size
sudo docker exec clickhouse-kafka-1 \
  kafka-run-class kafka.tools.GetOffsetShell \
  --broker-list localhost:9092 \
  --topic erp.tenant_namatenant.transaksi

# 3. Restart consumer jika stuck
pm2 restart clickhouse-etl

Data tidak masuk ClickHouse

# 1. Cek publication
psql "$ERP_MIGRASI_URL" -c "
  SELECT * FROM pg_publication_tables
  WHERE schemaname = 'tenant_namatenant';
"

# 2. Cek connector status
curl -s http://localhost:8083/connectors/debezium-postgres-erp/status | python3 -m json.tool

# 3. Cek DLQ
curl -s -X POST "$CH" \
  --data "SELECT count() FROM erp_clickhouse.cdc_dead_letter WHERE tenant_kode='namatenant'"

Backend tidak detect tenant

# 1. Cek schema pusat.tenant
psql "$ERP_MIGRASI_URL" -c "
  SELECT * FROM pusat.tenant WHERE kode = 'namatenant';
"

# 2. Restart backend
pm2 restart erp-backend

# 3. Cek backend log
pm2 logs erp-backend --lines 50 | grep -i tenant

---

📊 Monitoring Pasca-Onboarding

Week 1

Metric	Target	Check
CDC lag	< 1000	Dashboard
API error rate	< 0.1%	Grafana
User complaints	< 5	Support ticket
Data PG vs CH match	100%	Daily rekonsiliasi

Month 1

Metric	Target	Check
Performance vs legacy	≥ 2x faster	Benchmark
Infra cost	Within budget	Finance report
Tenant stability	No critical incident	Incident report

---

🔗 Referensi

• database/scripts/buat_tenant.sql
• database/cdc/RUNBOOK.md
• docs/04-ARSITEKTUR-MULTI-TENANT.md
• backend/RUNBOOK.md

---

Appendix: Tenant Onboarding Runbook v1.0