メニュー
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.
Codex または Claude でインストール この Prompt をコピーして Codex、Claude、または他のアシスタントに貼り付けると、Skill ページを確認してインストールできます。
SOC 職業分類に基づく
Use when adding or changing frontend pages/components in this repo (frontend/src, Vue 3 + Vite + Tailwind + Leaflet + Chart.js). Covers routing, API calls, the coverage tier colors, and the build/verify loop.
Use when adding or changing backend API endpoints in this repo (backend/api/routes.js, Express + pg). Covers query conventions, the v_sppg clean view, error handling, and the coverage business rule.
Use when scraping, cleaning, mapping, or importing the SPPG/DAPODIK data in this repo (script/*.py, script/*.mjs, script/data, script/sql/import_data.sql). Covers the CSV→Postgres flow and known data-quality issues.
| 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. |
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.
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).CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE VIEW,
INSERT ... ON CONFLICT DO NOTHING. Aman dijalankan ulang.v_sppg → v_sppg_per_kecamatan → v_analisis_sppg_pd. Kalau ubah
kolom view dasar, jalankan ulang file analysis_views.sql.backend/lib/coverage.js. SQL hanya meniru.docker exec -i sppg-db psql -U postgres -d sppg < script/sql/<file>.sql
# verifikasi
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.