| name | db-migrations |
| description | Use when changing the PostgreSQL schema or views for this repo (script/sql/*.sql, tables sppg/dapodik_pd/kecamatan_mapping/kecamatan_coords, views v_sppg/v_sppg_per_kecamatan). Covers ordering, idempotency, and applying against the live sppg-db container. |
DB Migrations — konvensi repo data-bgn
Database PostgreSQL di container sppg-db (port 5435, db sppg). Semua DDL/view ada di
script/sql/. Tidak ada migration tool — file SQL dijalankan berurutan dan harus idempoten.
File & urutan
setup_db.sql — tabel: sppg, dapodik_pd, kecamatan_mapping, kecamatan_coords,
district_coords + index. Pakai CREATE TABLE IF NOT EXISTS.02_clean_provinsi.sql — ref_provinsi (38 provinsi) + view v_sppg.import_data.sql — COPY CSV ke tabel (lihat skill sppg-data-pipeline).analysis_views.sql — view analisis (v_sppg_per_kecamatan baca dari v_sppg, dst).03_stunting.sql — tabel stunting_provinsi (prevalensi stunting per provinsi, SSGI/SKI
2024; provinsi UPPERCASE agar match ref_provinsi).
Aturan
- Idempoten:
CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE VIEW,
INSERT ... ON CONFLICT DO NOTHING. Aman dijalankan ulang.
- View bertingkat:
v_sppg → v_sppg_per_kecamatan → v_analisis_sppg_pd. Kalau ubah
kolom view dasar, jalankan ulang file analysis_views.sql.
- Jangan taruh angka domain (2000 porsi, ambang 70/90) di SQL sebagai sumber-kebenaran;
itu milik
backend/lib/coverage.js. SQL hanya meniru.
Terapkan ke DB live
docker exec -i sppg-db psql -U postgres -d sppg < script/sql/<file>.sql
docker exec -i sppg-db psql -U postgres -d sppg -c "\dt"
docker exec -i sppg-db psql -U postgres -d sppg -c "SELECT count(*) FROM v_sppg;"
Setelah migrasi: restart backend, smoke test /api/stats (total_provinsi harus 38),
dan jalankan cd backend && node --test.
