19-database-data-verification

Instruction file imported from yendyr/olshoperp-docs (.cursor/rules/19-database-data-verification.mdc). Copyright stays with the author.

Database Data Verification (QA — olshoperp-docs)

Pakai rule ini saat user / tugas menyebut cek db, query db, db staging, db tyas, db merdian, audit trail db, verifikasi data di database, atau saat FAIL E2E perlu bukti baris DB.

Sumber kebenaran

Jangan menduplikasi prosedur panjang di sini — baca agent-db/schema-catalog.yaml dulu untuk skema presisi.

0. Zero-Exploration Sequence (One-Shot Query)

Agent DILARANG SPAM QUERY (SHOW TABLES / DESCRIBE). Seluruh skema tabel, kolom, relasi, dan indeks sudah tersedia lokal di agent-db/schema-catalog.yaml.

S0  CEK KONTEKS & LOKAL: Jika skema sudah ada di ingatanmu, JANGAN baca file berulang. Jika belum, cek `agent-db/cache.md`. Hanya baca `agent-db/schema-catalog.yaml` jika butuh detail index/struktur.
S1  TENANT SCOPE: Gunakan `owned_by = <company_id>` (BUKAN `company_id`).
    Semua tabel SCM, Omni, Accounting, Gate memiliki composite index (owned_by, is_all_company, deleted_at).
S2  INDEX-AWARE: Susun filter WHERE HANYA menggunakan kolom ber-indeks (mis. order_no, code, created_at range).
S3  ONE-SHOT QUERY: Langsung kirim 1x SELECT spesifik dengan LIMIT. Dilarang SELECT * pada tabel besar.
S4  FALLBACK: Hanya jika tabel sama sekali belum ada di catalog, boleh 1x DESCRIBE lalu catat ke schema-catalog.yaml.

Aturan Kolom & Nilai Kunci:

• Tenant Company = owned_by (Bukan company_id).
• status = flag aktif 1/0; status dokumen = transaction_status (lowercase: draft, open, approved, void, dll).
• DILARANG LIKE '%keyword%' (Leading Wildcard) — selalu gunakan exact match (=) atau prefix match (LIKE 'OT-%'). Leading % mematikan index dan memaksa Full Table Scan (>15 detik).
• audits = WAJIB auditable_type = 'App\\\\Models\\\\...' AND auditable_id = <id>. Dilarang query audits tanpa auditable_type.
• Backslash JSON = Format 4 backslash di body JSON: "SELECT 'App\\\\Models\\\\X' AS t".

Routing env & HTTP Request

DILARANG MEMBUAT FILE SCRIPT (.ps1, .mjs, .sh, .py) di dalam direktori workspace (_local/, root, atau folder repo manapun) yang berpotensi menjadi file sampah dan membocorkan API key. Eksekusi wajib langsung via HTTP POST Request inline (curl) atau temporary scratch artifact (brain/<conversation-id>/scratch/). Kredensial dibaca dinamis dari .env (Dilarang hardcode API key).

• Tyas (tyas_olshoperp) + Staging (staging_olshoperp) → webhook shared:
  • URL: https://n8n.olshoperp.com/webhook/agent-db-tyas
  • Header: Content-Type: application/json, X-API-Key: <DB_DEBUG_API_KEY>
• Merdian (merdian_olshoperp) → webhook terpisah (server beda):
  • URL: https://n9n.olshoperp.com/webhook/agent-db-merdian
  • Header: Content-Type: application/json, X-API-Key: <DB_DEBUG_API_KEY_MERDIAN>

Contoh eksekusi via HTTP Request (curl):

# Query Staging
curl -s -X POST "https://n8n.olshoperp.com/webhook/agent-db-tyas" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $DB_DEBUG_API_KEY" \
  -d '{"db":"staging_olshoperp","query":"SELECT id, sku, name FROM scm_products WHERE owned_by = 153 AND deleted_at IS NULL LIMIT 5"}'

# Query Tyas
curl -s -X POST "https://n8n.olshoperp.com/webhook/agent-db-tyas" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $DB_DEBUG_API_KEY" \
  -d '{"db":"tyas_olshoperp","query":"SELECT id, order_no, transaction_status FROM omni_sales_orders WHERE owned_by = 153 AND deleted_at IS NULL LIMIT 5"}'

# Query Merdian
curl -s -X POST "https://n9n.olshoperp.com/webhook/agent-db-merdian" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $DB_DEBUG_API_KEY_MERDIAN" \
  -d '{"db":"merdian_olshoperp","query":"SELECT id, username, first_name FROM gate_users WHERE owned_by = 1 LIMIT 5"}'

Larangan QA (mutlak)

1. Hasil DB bukan bukti last_execution.status: passed untuk TC UI-crawling.
2. Jangan expose DB_DEBUG_API_KEY* / URL override di chat, TC, atau commit.
3. Dilarang menggunakan runner script .mjs — wajib HTTP POST Request langsung ke Webhook.
4. Selalu filter scope company (owned_by) yang sedang diuji; SoftDeletes awareness (deleted_at IS NULL).
5. Read-only saja — hanya SELECT, EXPLAIN, DESCRIBE, SHOW, WITH. Dilarang mutasi SQL (INSERT/UPDATE/DELETE/DROP).
6. Jangan buat folder qa-docs/{menu} khusus “cek DB” — tooling ops, bukan fitur menu.

Triage cepat: Kapan DB vs Kapan LOG