omnix-sopiga/docs/implementasi_guide.md

Gadai Mulia Collection Broadcast Integration

Complete Implementation Guide

---

📋 Overview

Purpose: Send collection invoices via WhatsApp (Sopiga Collar API) to Gadai Mulia customers.

Architecture:

Gadai Collection Service 
  → INSERT broadcast_staging (JSON payload)
    → Go Worker (poll every 30s)
      → Query template_variable_mapping
        → Build message dynamically
          → POST Sopiga Collar API
            → WhatsApp delivery
              → Update status (delivered/failed)

Key Innovation: Flexible template system via template_variable_mapping table — NO code redeploy for new templates.

---

🗄️ Database Schema (Simplified Denormalized)

Master Configuration (Read-only)

Table: sopiga_template_config

• Stores Sopiga template references (pre-created in Sopiga)
• Fields: id, sopiga_template_id, template_name, channel (waba), template_type, description, active
• Pre-populate: One row per template in Sopiga

Table: template_variable_mapping

• Maps Sopiga template variables → message_payload keys
• CRITICAL: variable_order determines message interpolation sequence
• Fields: id, sopiga_template_id, variable_order, sopiga_variable_name, variable_type (string/integer/date/decimal), db_field_source, is_required, example_value
• Example:

  sopiga_template_id=2, order=1, name='Nama', field='nasabah_nama'
  sopiga_template_id=2, order=2, name='TotalTagihan', field='nominal_tagihan'
  sopiga_template_id=2, order=3, name='TanggalJatuhTempo', field='tanggal_tempo'
  

Table: sopiga_collar_config

• Stores Sopiga collar references (pre-created in Sopiga)
• Fields: id, sopiga_collar_id, broadcast_name, sopiga_template_id (FK), status (open/closed), description
• Pre-populate: One row per collar in Sopiga

Processing (Core)

Table: broadcast_staging ← Main table

• DENORMALIZED: All data in message_payload (JSONB)
• NO FK to nasabah or gadai_contract
• Fields:
  • id (PK)
  • sopiga_collar_id (FK)
  • sopiga_template_id (FK)
  • message_payload (JSONB) ← Single source of truth
  • status (pending → dispatched → delivered/failed)
  • error_message, error_count
  • sopiga_recipient_detail_id (response from Sopiga)
  • created_at, updated_at, dispatched_at, delivered_at, failed_at

Example message_payload:

{
  "nasabah_nama": "Budi Santoso",
  "nasabah_phone": "6281234567890",
  "nominal_tagihan": 1500000,
  "tanggal_tempo": "2026-06-30",
  "invoice_pdf_url": "https://storage.gadai.com/invoices/inv-001.pdf",
  "contract_no": "GAD-2026-001"
}

Audit & Tracking

Table: broadcast_audit_log

• Tracks status transitions
• Fields: id, broadcast_id (FK), old_status, new_status, reason, sopiga_response (JSONB), changed_by, created_at

Table: broadcast_error_log

• Detailed error tracking
• Fields: id, broadcast_id (FK), error_type, error_code, error_message, error_details (JSONB), attempt_number, next_retry_at

Table: sopiga_sync_job

• Tracks delivery status sync from Sopiga
• Fields: id, broadcast_id (FK), sopiga_recipient_detail_id, last_synced_at, last_status_from_sopiga, sync_count, next_sync_at

---

🔧 Setup Process

Step 1: Setup in Sopiga (Manual)

1. Create template in Sopiga:

   POST https://omnix.promas.site/api/client/template
   {
     "template_name": "Collection Invoice",
     "channel": "waba",
     "variables": [
       {"name": "Nama", "type": "string"},
       {"name": "TotalTagihan", "type": "integer"},
       {"name": "TanggalJatuhTempo", "type": "date"}
     ]
   }
   → Response: template_id = 2
   

2. Create collar in Sopiga:

   POST https://omnix.promas.site/api/client/collar
   {
     "judul_broadcast": "Invoice May 2026",
     "template_id": 2
   }
   → Response: collar_id = 70
   

Step 2: Register in Database (5 minutes)

2.1 Insert template config:

INSERT INTO collection_broadcasts.sopiga_template_config 
  (template_name, sopiga_template_id, channel, template_type, description)
VALUES 
  ('Collection Invoice May 2026', 2, 'waba', 'utility', 'Invoice bulanan');

2.2 Insert variable mappings (ORDER IS CRITICAL!):

INSERT INTO collection_broadcasts.template_variable_mapping 
  (sopiga_template_id, variable_order, sopiga_variable_name, variable_type, db_field_source, is_required, example_value)
VALUES
  (2, 1, 'Nama', 'string', 'nasabah_nama', TRUE, 'Budi Santoso'),
  (2, 2, 'TotalTagihan', 'integer', 'nominal_tagihan', TRUE, '1500000'),
  (2, 3, 'TanggalJatuhTempo', 'date', 'tanggal_tempo', TRUE, '2026-06-30');

2.3 Insert collar config:

INSERT INTO collection_broadcasts.sopiga_collar_config 
  (broadcast_name, sopiga_collar_id, sopiga_template_id, status)
VALUES 
  ('Collection Invoices May 2026', 70, 2, 'open');

Step 3: Deploy Application

Deploy Go worker with:

• Database connection string
• Sopiga base URL: https://omnix.promas.site
• Sopiga API token

Worker runs continuously:

• Broadcast dispatch: Poll every 30s, process pending records
• Status sync: Poll every 5 min, sync delivery status from Sopiga

---

🔄 How It Works

Runtime Flow

Gadai Collection Service (sends data):

// When collection reminder needed:
payload := map[string]interface{}{
  "nasabah_nama": "Budi Santoso",
  "nasabah_phone": "6281234567890",
  "nominal_tagihan": 1500000.0,
  "tanggal_tempo": "2026-06-30",
  "invoice_pdf_url": "https://storage.gadai.com/invoices/inv-001.pdf",
  "contract_no": "GAD-2026-001",
}

// Insert to broadcast_staging
db.Exec(`
  INSERT INTO collection_broadcasts.broadcast_staging 
    (sopiga_collar_id, sopiga_template_id, message_payload, status)
  VALUES ($1, $2, $3, 'pending')
`, 70, 2, payload)

Go Worker (processes data - automatic):

1. Poll broadcast_staging WHERE status='pending' LIMIT 100
2. For each record:
   a. Get message_payload from database (already has all fields)
   b. Query template_variable_mapping WHERE sopiga_template_id=2 ORDER BY variable_order
   c. Build message:
      - For each variable in order (1,2,3...):
        - Get value from payload[db_field_source]
        - Format by type (string/integer/date)
        - Append to parts[]
      - message = parts.join("#")
      - Result: "Budi Santoso#1500000#2026-06-30"
   
   d. Call Sopiga API:
      POST /api/client/collar/add-recipient
      {
        "broadcast_id": 70,
        "template_id": 2,
        "details": {
          "recipient": "6281234567890",
          "message": "Budi Santoso#1500000#2026-06-30"
        },
        "attachment": {
          "type": "document",
          "file": "https://storage.gadai.com/invoices/inv-001.pdf"
        }
      }
   
   e. Response (HTTP 202 Accepted):
      {
        "success": true,
        "data": {
          "recipient_detail_id": 512,
          "status": "pending"
        }
      }
   
   f. Update broadcast_staging:
      UPDATE status='dispatched', sopiga_recipient_detail_id=512

3. Status sync worker (every 5 min):
   - For dispatched records:
   - GET /api/client/collar/add-recipient/{recipient_detail_id}/detail
   - Update status: delivered OR failed

---

📊 Adding New Template (Zero Code Change)

Scenario: Template now has 4 variables instead of 3

Old:

1. Nama
2. TotalTagihan
3. TanggalJatuhTempo

New:

1. Nama
2. NoKontrak ← NEW!
3. TotalTagihan
4. TanggalJatuhTempo

Setup (5 minutes, Database only)

1. Create template in Sopiga → template_id = 3

2. Create collar in Sopiga → collar_id = 71

3. Register in database:

-- Template
INSERT INTO sopiga_template_config (template_name, sopiga_template_id, ...)
VALUES ('Collection Invoice Extended', 3, ...);

-- Variables (URUTAN PENTING!)
INSERT INTO template_variable_mapping 
  (sopiga_template_id, variable_order, sopiga_variable_name, variable_type, db_field_source, ...)
VALUES
  (3, 1, 'Nama', 'string', 'nasabah_nama', ...),
  (3, 2, 'NoKontrak', 'string', 'contract_no', ...),        ← NEW!
  (3, 3, 'TotalTagihan', 'integer', 'nominal_tagihan', ...),
  (3, 4, 'TanggalJatuhTempo', 'date', 'tanggal_tempo', ...);

-- Collar
INSERT INTO sopiga_collar_config (broadcast_name, sopiga_collar_id, sopiga_template_id, ...)
VALUES ('Collection Invoices June 2026', 71, 3, ...);

Go code? ✅ ZERO changes — buildDynamicMessage() queries mapping every time.

---

🚀 Gadai Service Integration

How Gadai Collection Service calls this:

package gadai

type CollectionBroadcastService struct {
  db *sql.DB
}

func (s *CollectionBroadcastService) SendInvoiceReminder(
  ctx context.Context,
  nasabahID, contractNo string,
  nominalTagihan float64,
  dueDate time.Time,
) error {
  // Get invoice URL (from Gadai storage or API)
  invoiceURL := fmt.Sprintf("https://storage.gadai.com/invoices/%s.pdf", contractNo)
  
  // Build payload (flexible - can have extra fields)
  payload := map[string]interface{}{
    "nasabah_nama": "Budi Santoso",
    "nasabah_phone": "6281234567890",
    "nominal_tagihan": nominalTagihan,
    "tanggal_tempo": dueDate.Format("2006-01-02"),
    "invoice_pdf_url": invoiceURL,
    "contract_no": contractNo,
    "cif": "12345",  // Extra field - ignored by worker if not in mapping
  }
  
  // Marshal to JSONB
  payloadJSON, _ := json.Marshal(payload)
  
  // Insert to broadcast_staging
  // Worker will automatically pick it up (every 30s poll)
  query := `
    INSERT INTO collection_broadcasts.broadcast_staging 
      (sopiga_collar_id, sopiga_template_id, message_payload, status)
    VALUES ($1, $2, $3, 'pending')
  `
  
  _, err := s.db.ExecContext(ctx, query,
    70,  // sopiga_collar_id (hardcoded or from config)
    2,   // sopiga_template_id (hardcoded or from config)
    payloadJSON,
  )
  
  return err
}

---

📈 Monitoring & Reporting

Views Available

v_template_variables_ordered

SELECT * FROM collection_broadcasts.v_template_variables_ordered;
-- Shows all template variables with proper ordering

v_collar_summary

SELECT * FROM collection_broadcasts.v_collar_summary;
-- Shows delivery rate per collar

v_delivery_rate_24h

SELECT * FROM collection_broadcasts.v_delivery_rate_24h;
-- Shows delivery rate for last 24 hours

v_failed_records_24h

SELECT * FROM collection_broadcasts.v_failed_records_24h;
-- Shows failed records with error details

---

🛠️ Troubleshooting

Issue: Message format wrong

Cause: variable_order in mapping doesn't match Sopiga template order

Fix:

-- Check current mapping
SELECT variable_order, sopiga_variable_name FROM template_variable_mapping 
WHERE sopiga_template_id=2 ORDER BY variable_order;

-- If order wrong, delete and re-insert correctly
DELETE FROM template_variable_mapping WHERE sopiga_template_id=2;
-- Re-insert with correct order

Issue: Missing field in message_payload

Cause: Gadai service didn't include field in payload, but it's marked required in mapping

Fix:

-- Either make it optional
UPDATE template_variable_mapping SET is_required=FALSE 
WHERE sopiga_variable_name='NoKontrak';

-- Or ensure Gadai service includes it

Issue: Delivery status stuck at 'dispatched'

Cause: Status sync worker not running or Sopiga API unreachable

Fix:

-- Check sync jobs
SELECT * FROM sopiga_sync_job WHERE next_sync_at < NOW();

-- Manually trigger status check
SELECT collection_broadcasts.get_template_variables(2);

---

🔐 Security Considerations

1. Sopiga API Token: Store in environment variable, never hardcode
2. Database: Restrict access to collection_broadcasts schema
3. Phone Numbers: Already PII, treat as sensitive
4. PDFs: Ensure URLs are time-limited or access-controlled

---

📋 Checklist: New Template

• Create template in Sopiga, note template_id
• Create collar in Sopiga, note collar_id
• Insert sopiga_template_config row
• Insert template_variable_mapping rows (check order!)
• Insert sopiga_collar_config row
• Verify mapping: SELECT * FROM v_template_variables_ordered WHERE sopiga_template_id=X
• Test with sample record in broadcast_staging
• Monitor first 10 deliveries in logs
• Confirm in v_collar_summary delivery rate

---

📞 Support

Schema files:

• gadai_collection_broadcast_simplified_schema.sql — Complete DDL

Application files:

• collection_broadcast_worker_simplified.go — Go worker implementation

Deployment:

• Requires: PostgreSQL 11+, Go 1.16+
• Connects to: Sopiga API at https://omnix.promas.site

---

Version: 1.0
Last Updated: 2026-08-03
Status: Ready for deployment ✅