453 lines
13 KiB
Markdown
453 lines
13 KiB
Markdown
# 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:**
|
|
```json
|
|
{
|
|
"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:**
|
|
```sql
|
|
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!):**
|
|
```sql
|
|
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:**
|
|
```sql
|
|
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):**
|
|
```go
|
|
// 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:
|
|
```sql
|
|
-- 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:**
|
|
|
|
```go
|
|
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**
|
|
```sql
|
|
SELECT * FROM collection_broadcasts.v_template_variables_ordered;
|
|
-- Shows all template variables with proper ordering
|
|
```
|
|
|
|
**v_collar_summary**
|
|
```sql
|
|
SELECT * FROM collection_broadcasts.v_collar_summary;
|
|
-- Shows delivery rate per collar
|
|
```
|
|
|
|
**v_delivery_rate_24h**
|
|
```sql
|
|
SELECT * FROM collection_broadcasts.v_delivery_rate_24h;
|
|
-- Shows delivery rate for last 24 hours
|
|
```
|
|
|
|
**v_failed_records_24h**
|
|
```sql
|
|
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:**
|
|
```sql
|
|
-- 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:**
|
|
```sql
|
|
-- 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:**
|
|
```sql
|
|
-- 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 ✅ |