Đặc tả triển khai (Dev Spec) — Tích hợp Pancake CRM (Pancake CRM Integration)

Phiên bản: 1.0 Ngày: 15/05/2026 Tác giả: PO/BA + AI Tech Lead Profile: L (Large feature, ~37 dev-days, 5.5-6 tuần calendar) Phạm vi: MVP-1 — Inbound Pancake → Diva, 4-layer recovery, Settings UI 4 tabs, DLQ replay, Pilot 4 tuần

Tài liệu đầu vào chuẩn

Tài liệu	Vai trò	Tham chiếu canonical
`SOURCE_OF_TRUTH.md`	Solution Lock 25 DEC + Fact code Phase 3	§3 Fact code, §4 Solution Lock
`EVIDENCE_PACK.md`	File path + line number Phase 3 ANALYZE	§4 Fact DB, §5 Reuse map
`prd.md`	FR-001..018, LIFECYCLE-001/002, A10 FORMULA-001..006, A9 Glossary	A5, A10, A12
`ui-spec.md`	SCR-01..06 wireframes, B5 Permission Matrix, B9 Tooltip	(đồng cấp)

Quy tắc đọc: Dev Spec là implementation delta từ PRD/SOT. Mọi business rationale → đọc PRD. Mọi UI design → đọc UI Spec. Mọi test case detail → đọc QA Test Plan. Formula Single-Source: PRD A10 = canonical. C3 chỉ SQL implementation + index + precision delta. Trích dẫn EVIDENCE: File path luôn ghi kèm line number nếu có (vd services/crm-api/scheduler/distribute_ticket.go:157).

C1) Phạm vi (Scope)

Modules ảnh hưởng

Layer	Module / service	Loại tác động	Files chính
Backend webhook	`services/webhook`	🆕 Add handler + 🔧 Extend route	`handler/pancake.go` (NEW), `handler/route.go:35-48` (ADD route), `server/main.go`
Backend CRM	`services/crm-api`	🆕 Add event handler + 6 cron + 🔧 Refactor distribute helper	`event/event_pancake_process_record.go` (NEW), `event/event.go` (ADD map entry), `scheduler/pancake_*.go` × 6 (NEW), `scheduler/distribute_ticket.go:157` (refactor public)
Backend notification	`services/notification-v2-api`	🔧 Seed 3 template + reuse `sendNotifications` action	`action/action.go` (no change), migration seed
Shared package	`pkg/pancake/`	🆕 New package (REST client + phone normalize + advisory lock helper)	`client.go`, `phone_normalize.go`, `advisory_lock.go` (NEW)
Shared store	`pkg/store/`	🆕 Build 4 Go struct + 1 customer_consent struct	`pancake_webhook_event.go`, `pancake_connection.go`, `pancake_source_routing.go`, `pancake_outage_state.go`, `customer_consent.go` (NEW)
Frontend admin	`diva-admin/src/modules/settings/pages/`	🆕 1 container + 4 child page	`PancakeCrmSetting.tsx`, `pancake/PancakeConnection.tsx`, `pancake/PancakeSourceRouting.tsx`, `pancake/PancakeAuditDlq.tsx`, `pancake/PancakeHealth.tsx`
Frontend admin	`diva-admin/src/modules/settings/graphql/`	🆕 GraphQL pancake operations	`pancake.graphql` (NEW)
Frontend admin	`diva-admin/src/modules/crm/`	🔧 3-file delta cho slot 9 ticket source	`types.ts:146-164` (ADD const), `i18n/vi.ts:130-137` (ADD key), `pages/Tickets.tsx:128-149` (ADD sourceDescriptions)
Frontend routing	`diva-admin/src/router/routes.ts`	🔧 ADD 5 routes + meta.permission	(5 entries mới)
Frontend menu	`diva-admin/src/layouts/...`	🔧 ADD menu entry Settings → "Tích hợp Pancake"	(1 entry)
Frontend permission	`diva-admin/src/shared/permissions.ts`	🔧 ADD enum value `replay_dlq`	(nếu chưa có)
Database	`controller/migrations/default/` + `controller/migrations/crm/`	🆕 8 migration mới	(xem C7 sequence)
Hasura metadata	`controller/metadata/databases/default/tables/`	🆕 4 YAML mới cho bảng pancake_* + ADD permission cho `account`	`public_pancake_*.yaml` × 4
Hasura cron	`controller/metadata/cron_triggers.yaml`	🔧 ADD 6 cron mới	(xem C5 Event Trigger / Scheduler)
Hasura action	`controller/metadata/actions.yaml` + `actions.graphql`	🔧 ADD `pancake_replay_event` action	(1 entry)
Go module	`diva-backend/go.mod`	🔧 ADD dependency `sony/gobreaker`	(1 line)

Không thuộc phạm vi Dev Spec

Mục	Đọc ở đâu
Business rationale, FR/AC, KPI definition, A9 Glossary	`prd.md` A2/A3/A5/A8/A9
Color/font/spacing, screen wireframes detail	`ui-spec.md` B-Desktop/B-Mobile
Test cases detail, seed data, entry/exit criteria	`qa-test-plan.md` D3/D4/D5
Go-live gates W1-W4, sign-off	`go-live-checklist.md` E1-E4
Outbound Diva → Pancake (deferred MVP-2)	`SOURCE_OF_TRUTH.md` §7
Pancake POS / Botcake / Messenger sync	`SOURCE_OF_TRUTH.md` §7 (Out of scope)

C2) Tóm tắt ảnh hưởng + mapping nghiệp vụ → kỹ thuật

Mỗi FR PRD A5 ánh xạ tới 1+ component/file. Ghi rõ Reuse / Extend / Build mới (Codebase-First Rule). File path + line number trích từ EVIDENCE_PACK.

FR	Mô tả ngắn	Component / file / handler	Loại	Dữ liệu chính	Điều kiện kích hoạt	Ngoại lệ / Edge	Rủi ro kỹ thuật
FR-001	Webhook receiver endpoint trả 200<1s	`services/webhook/handler/pancake.go` (NEW), route `POST /api/pancake/record/{token}`	🆕 Build mới (reuse pattern Stringee `handler/route.go:35-48`)	`pancake_webhook_event.status` (ingested→received hoặc *_failed)	Pancake POST request bất kỳ	Token sai → status='auth_failed' 200; IP không whitelist → status='ip_blocked' 200; JSON parse fail → status='parse_error' 200	DDoS public endpoint (mitigation: rate limit reverse proxy 1000 req/s/IP)
FR-002	Persist raw event audit trail	`pkg/store/pancake_webhook_event.go` (NEW) — INSERT raw payload JSONB	🆕 Build mới	`pancake_webhook_event` (raw `payload jsonb`, `headers jsonb`, `source_ip`, `received_at`)	Mọi webhook request (kể cả fail)	Payload > 1 MB → log warning, vẫn lưu	Storage growth (~100k events/tháng × 5 KB avg = 500 MB/tháng → archive sau 6 tháng)
FR-003	Idempotency 3-tuple UNIQUE	DB constraint `UNIQUE (record_id, modified_on, payload_hash)` ở bảng `pancake_webhook_event`	🆕 Build mới	3-tuple constraint	INSERT mọi event	Duplicate → ON CONFLICT DO NOTHING + UPDATE `last_received_at`, set status='skipped_duplicate'	Hash collision (mitigation: SHA-256 payload_hash, 256 bit space)
FR-004	Async processor qua Hasura event trigger	Hasura event trigger `pancake_webhook_event_status_update` watch column `status` → fire `services/crm-api/event/event_pancake_process_record.go` (NEW)	🆕 Build mới (reuse pattern `services/crm-api/event/event.go:24-50` dispatcher)	Hasura trigger payload (op=`UPDATE`, columns=`[status]`)	UPDATE pancake_webhook_event SET status='received'	STEP 0 filter: `NEW.status != 'received'` OR `NEW.record_id IS NULL` → return nil	Hasura trigger retry storm (mitigation: retry_conf=3, interval=30s)
FR-005	Phone E.164 normalize + match/upsert account	`pkg/pancake/phone_normalize.go` (NEW) reuse `github.com/ttacon/libphonenumber` (go.mod L32)	🔧 Extend pattern từ `services/auth/action/otp.go:20,769`	`account.normalized_phone` TEXT + `account.pancake_metadata` JSONB	STEP 2-5 process record flow	Phone không parse được → fallback `phone_code=84` + raw `phone_number`, log warning	Pancake gửi format khác (vd US +1) — RSK-009
FR-006	Advisory lock per phone chống race	`pkg/pancake/advisory_lock.go` (NEW): `pg_advisory_xact_lock(hashtext('account_phone_' \|\| normalized_phone))` ở STEP 3	🆕 Build mới pattern	(lock scope: transaction)	Trước STEP 4-5 match/upsert account	2 webhook đồng thời cùng phone → 1 lock, 1 wait — serialized	Lock contention (mitigation: lock scope nhỏ, < 50ms typical)
FR-007	Auto-create contact_book entry	STEP 5b `event_pancake_process_record.go` — query-before-insert pattern	🔧 Extend `contact_book` ALTER ADD `primary_phone` + FK + UNIQUE (DEC-006)	`contact_book(account_id, primary_phone, role='customer')`	Sau STEP 5 nếu account vừa được tạo OR contact chưa có	Race đã được advisory lock (STEP 3) serialize	Migration ALTER `contact_book` lock table — mitigation: NOT VALID ban đầu, VALIDATE sau (xem C7)
FR-008	Auto-assign telesale REUSE round-robin 1-tier	`services/crm-api/scheduler/distribute_ticket.go:157` `GetTicketUpdates` refactored public (DEC-012)	🔧 Extend refactor	`ticket_distribute(branch_id, target_id, user_id, created_at)`	STEP 9 sau INSERT ticket nếu có `branch_id`	Branch=NULL (Loose mode) → assignee_id=NULL; branch có nhưng không có telesale active → assignee_id=NULL	Refactor breaks existing cron `distribute_ticket` — mitigation: backward-compat signature
FR-009	Smart update Q2.b	STEP 8 logic `event_pancake_process_record.go` so sánh `account` old vs Pancake new payload	🆕 Build mới	Compare: `phone_number`, `pancake_source_id`, `pancake_tag_names`	Sau STEP 6 update `pancake_metadata`	Cùng record update khác (vd `full_address` change) → CHỈ update account, KHÔNG tạo ticket	Logic bug bỏ sót case quan trọng — mitigation: TC-009-001..006 coverage
FR-010	Tạo ticket source=`ticket_source_pancake`	STEP 10 INSERT `ticket` với `source_id='ticket_source_pancake'`, `target_id='telesales'`, `status_id='ticket_status_new'`, `due_date=today 23:59 ICT`	🔧 Extend (table reuse) + INSERT crm_master_data	`ticket` row + `ticket_log` audit	Sau STEP 9 (assignee resolved)	Branch=NULL → vẫn INSERT ticket nhưng assignee=NULL	Schema FK fail nếu master data chưa seed — mitigation: migration order C7
FR-011	Notification telesale + admin (Loose mode)	STEP 12 call Hasura action `sendNotifications` (reuse `services/notification-v2-api`)	🔧 Extend reuse + seed 3 template	3 template mới: `noti_ticket_assigned_pancake`, `noti_pancake_unmapped_branch`, `noti_pancake_outage`	Sau STEP 11 update `last_sync_at`	Notification fail KHÔNG block flow (alert ops nếu > 5%/5 phút — RSK-011)	Notification-v2-api downtime — mitigation: STEP 12 non-blocking, log only
FR-012	Settings UI 4 tabs Admin only	`src/modules/settings/pages/PancakeCrmSetting.tsx` (NEW container) + 4 child pages	🆕 Build mới (reuse pattern `AppSettingsSmsTemplate.tsx:14-28` XDetailLayout)	GraphQL queries từ `pancake.graphql`	Admin login + `hasPermission('pancake_crm_integration','access')`	Non-admin → menu hidden (Diva quy tắc)	Permission leak nếu chỉ FE check — mitigation: Hasura permission Admin-only
FR-013	REST API consumption Pancake (chỉ /sources, Cron 3 1h cache)	`pkg/pancake/client.go` (NEW) + `services/crm-api/scheduler/pancake_source_sync.go` (NEW) + `sony/gobreaker` (DEC-019)	🆕 Build mới	`pancake_source_routing(pancake_source_id, pancake_source_name, last_synced_at)`	Cron 3 mỗi 1h	API 401 → status=`error`, alert admin; 429 → exponential backoff retry; timeout → circuit breaker open	Pancake API rate limit (mitigation: gobreaker 5 fail/60s → open)
FR-014	4-layer outage recovery + DLQ replay UI	Cron 4 detect + Cron 6 polling + Cron 7 reconciliation + DLQ replay Hasura action `pancake_replay_event`	🆕 Build mới (3 cron + 1 action + UI Tab 3)	`pancake_outage_state` 4 states + `pancake_webhook_event.status='dead_letter'`	Cron 4 every 1 min check; Cron 6 adaptive 1-15 min; Cron 7 daily 02:00 AM	Reconciliation Pancake API down → skip + retry hour sau (FORMULA-005 ngoại lệ)	Cron overlapping race — mitigation: advisory lock per cron
FR-015	Feature flag 3 mức + pilot rollout	`app_setting.app_settings.pancake_integration` + `pancake_connection.status` + `pancake_source_routing.is_active`	🔧 Extend `app_setting` + 🆕 build 2 bảng	JSON config	3 mức toggle (DEC-021)	Kill switch off → webhook trả 200 status='skipped_kill_switch'	App_setting cache stale — mitigation: invalidate cache khi update
FR-016	Test mode event tagged `is_test=true`	Cờ `pancake_webhook_event.is_test BOOLEAN` + Cron `pancake_test_event_cleanup` (NEW)	🆕 Build mới	`is_test=true` event không count KPI, soft-delete (`disabled=true`) sau 24h	Webhook payload có cờ test hoặc admin manual mark	Cron soft-delete chạy 04:00 AM daily	Test event leak vào production metric — mitigation: filter `is_test=false` ở mọi KPI query
FR-017	Opt-out qua `customer_consent.consent_data->>'marketing'='false'`	STEP 7 query `customer_consent` (DEC-014)	🆕 Build Go struct `pkg/store/customer_consent.go` (reuse table EXIST)	`customer_consent.consent_data->>'marketing'`	Sau STEP 6 update account	Opt-out → vẫn update account, KHÔNG tạo ticket, status='skipped_opt_out'	Key name PD-003 chưa confirm — mitigation: default `marketing`
FR-018	FE delta dropdown ticket source (3 file)	`diva-admin/src/modules/crm/types.ts` + `i18n/vi.ts` + `pages/Tickets.tsx` sourceDescriptions	🔧 Extend 3-file delta (DEC-017)	`TICKET_SOURCE_PANCAKE` constant slot 9	Component dropdown render slot 9 auto sau ADD entries	Report module có thể hardcode slot 1..8 (RSK-005) — grep audit `'ticket_source_'` trước deploy	None nếu grep clean

C3) Quy tắc và công thức (chỉ phần triển khai)

Canonical: PRD A10 FORMULA-001..006. Section này chỉ ghi SQL + index + precision + performance delta.

FORMULA-001: Latency webhook → ticket assigned (p95)

Ref: PRD A10 FORMULA-001.
SQL:

sql

SELECT
  percentile_cont(0.95) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (processed_at - created_at))
  ) AS latency_p95_seconds
FROM pancake_webhook_event
WHERE status = 'processed'
  AND is_test = false
  AND processed_at IS NOT NULL
  AND created_at >= NOW() - INTERVAL '24 hours'
  AND EXTRACT(EPOCH FROM (processed_at - created_at)) < 300;  -- Outlier > 5 phút loại

Source mapping:
- processed_at ← pancake_webhook_event.processed_at (set ở STEP 13 trước COMMIT)
- created_at ← pancake_webhook_event.created_at (set ở webhook handler INSERT)
Index recommendation: idx_pancake_webhook_event_status_created (status, created_at DESC) WHERE status = 'processed' — partial index để giảm size
Precision: Lưu trong DB là TIMESTAMPTZ microsecond → output round 1 decimal giây (format Việt Nam dấu phẩy: "18,7s")
Performance notes: Window 24h × 100k events = ~3.3k rows. With partial index, query < 50ms p95.

FORMULA-002: SLA telesale phản hồi (p95, giờ làm việc)

Ref: PRD A10 FORMULA-002.
SQL:

sql

-- Helper function business_hours_diff (chỉ tính giờ làm 08:00-22:00 Asia/Ho_Chi_Minh)
CREATE OR REPLACE FUNCTION business_hours_diff(start_ts TIMESTAMPTZ, end_ts TIMESTAMPTZ)
RETURNS NUMERIC AS $$
DECLARE
  total_seconds NUMERIC := 0;
  cur TIMESTAMPTZ;
  day_start TIMESTAMPTZ;
  day_end TIMESTAMPTZ;
BEGIN
  IF end_ts IS NULL OR start_ts IS NULL OR end_ts <= start_ts THEN
    RETURN NULL;
  END IF;
  cur := start_ts;
  WHILE cur < end_ts LOOP
    day_start := date_trunc('day', cur AT TIME ZONE 'Asia/Ho_Chi_Minh') + INTERVAL '8 hours';
    day_end := date_trunc('day', cur AT TIME ZONE 'Asia/Ho_Chi_Minh') + INTERVAL '22 hours';
    day_start := day_start AT TIME ZONE 'Asia/Ho_Chi_Minh';
    day_end := day_end AT TIME ZONE 'Asia/Ho_Chi_Minh';
    IF cur < day_start THEN cur := day_start; END IF;
    IF cur >= day_end THEN
      cur := day_start + INTERVAL '1 day';
      CONTINUE;
    END IF;
    total_seconds := total_seconds + EXTRACT(EPOCH FROM LEAST(day_end, end_ts) - cur);
    cur := day_start + INTERVAL '1 day';
  END LOOP;
  RETURN total_seconds;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Query SLA p95
SELECT
  percentile_cont(0.95) WITHIN GROUP (
    ORDER BY business_hours_diff(t.created_at, t.first_response_at) / 60.0
  ) AS sla_minutes_p95
FROM ticket t
WHERE t.source_id = 'ticket_source_pancake'
  AND t.first_response_at IS NOT NULL
  AND t.created_at >= NOW() - INTERVAL '7 days';

Source mapping: t.created_at, t.first_response_at ← ticket table
Index recommendation: idx_ticket_source_first_response (source_id, first_response_at) WHERE source_id = 'ticket_source_pancake'
Precision: 2 decimal phút ("25,40 phút")
Performance notes: business_hours_diff là PL/pgSQL loop O(days). Window 7 ngày × ~1k ticket Pancake = 7k call. Function IMMUTABLE → query planner cache.

FORMULA-003: Auto-assign correct rate

Ref: PRD A10 FORMULA-003.
SQL:

sql

WITH pancake_tickets AS (
  SELECT id, assignee_id, created_by
  FROM ticket
  WHERE source_id = 'ticket_source_pancake'
    AND created_at >= NOW() - INTERVAL '7 days'
)
SELECT
  CASE
    WHEN COUNT(*) = 0 THEN NULL
    ELSE ROUND(
      COUNT(*) FILTER (WHERE assignee_id IS NOT NULL AND created_by = 'system_pancake_webhook')::NUMERIC
      / COUNT(*)::NUMERIC * 100,
      2
    )
  END AS correct_rate_pct
FROM pancake_tickets;

Source mapping: ticket.source_id, ticket.assignee_id, ticket.created_by
Index recommendation: reuse idx_ticket_source_first_response (cover scan)
Precision: 2 decimal % ("92,00%")
Edge: Mẫu = 0 → return NULL, FE hiển thị —

FORMULA-004: Event success rate

Ref: PRD A10 FORMULA-004.
SQL:

sql

WITH eligible AS (
  SELECT status
  FROM pancake_webhook_event
  WHERE is_test = false
    AND created_at >= NOW() - INTERVAL '24 hours'
    AND status NOT LIKE 'skipped_%'   -- Loại intentional skip
)
SELECT
  CASE
    WHEN COUNT(*) = 0 THEN NULL
    ELSE ROUND(
      COUNT(*) FILTER (WHERE status = 'processed')::NUMERIC
      / COUNT(*)::NUMERIC * 100,
      3
    )
  END AS success_rate_pct
FROM eligible;

Source mapping: pancake_webhook_event.status, is_test, created_at
Index recommendation: idx_pancake_webhook_event_status_created (đã định nghĩa ở FORMULA-001)
Precision: 3 decimal % ("99,950%") — KPI alert threshold < 99% trigger
Edge: is_test=true loại khỏi cả tử + mẫu

FORMULA-005: Zero-miss SLO (Cron 7 reconciliation)

Ref: PRD A10 FORMULA-005.
SQL (Diva side count):

sql

SELECT COUNT(DISTINCT record_id) AS diva_count
FROM pancake_webhook_event
WHERE record_id IS NOT NULL
  AND created_at >= NOW() - INTERVAL '25 hours'
  AND is_test = false;

Pancake side count: REST API call GET /workspaces/{ws}/records?modified_since=NOW()-25h (limit/page) — implementation ở services/crm-api/scheduler/pancake_daily_reconciliation.go
Miss detection:

// Pseudo: detect missing records and inject
divaRecordIDs := store.GetPancakeRecordIDsLast25h(db)
pancakeRecords := client.GetRecordsModifiedSince(time.Now().Add(-25*time.Hour))
for _, rec := range pancakeRecords {
  if _, ok := divaRecordIDs[rec.ID]; !ok {
    store.InsertPancakeWebhookEvent(db, &store.PancakeWebhookEvent{
      RecordID:   &rec.ID,
      ModifiedOn: rec.ModifiedOn,
      Payload:    rec.Raw,
      Status:     "received",   // bypass ingested
      EventType:  "reconciled", // audit tag
      IsTest:     false,
    })
  }
}

Window 25h: Overlap 1h chống miss khi clock skew (DEC-025)
Alert threshold: > 5 events/ngày miss → alert ops escalate

FORMULA-006: Outage MTTR

Ref: PRD A10 FORMULA-006.
SQL:

sql

-- Cần audit table history pancake_outage_state (xem C4)
SELECT
  ROUND(
    AVG(EXTRACT(EPOCH FROM (outage_ended_at - outage_started_at)) / 60.0)::NUMERIC,
    2
  ) AS mttr_minutes
FROM pancake_outage_history
WHERE outage_ended_at IS NOT NULL
  AND outage_started_at >= NOW() - INTERVAL '30 days';

Source mapping: pancake_outage_history.outage_started_at, outage_ended_at
Precision: 2 decimal phút ("4,40 phút")
Edge: Outage chưa recover (outage_ended_at IS NULL) → loại khỏi tính

STATE-001 — Vòng đời `pancake_webhook_event.status` (ref PRD LIFECYCLE-001)

Từ	Sang	Trigger	Guard / Validation	Side effect
`(NULL)`	`ingested`	Webhook handler INSERT row	—	None
`ingested`	`auth_failed`	Token URL không match	`pancake_connection.webhook_token = $token`	Audit log; nếu rate > 10/min → alert ops
`ingested`	`ip_blocked`	Source IP không trong whitelist	`app_setting.pancake_integration.ip_whitelist[]`	Audit log
`ingested`	`parse_error`	JSON schema invalid	Required fields: `record_id`, `phone_number`, `modified_on`, `source_id`	Audit log; alert nếu rate > 5%
`ingested`	`skipped_duplicate`	3-tuple đã tồn tại	UNIQUE constraint violation	UPDATE `last_received_at`, increment `retry_count`
`ingested`	`skipped_source_disabled`	`pancake_source_routing.is_active=false`	Routing exists nhưng disabled	None
`ingested`	`skipped_kill_switch`	`app_setting.pancake_integration.enabled=false`	Global flag off	None
`ingested`	`received`	Tất cả verify pass	All above checks negate	PROMOTE — fire Hasura trigger `pancake_webhook_event_status_update`
`received`	`processing`	Hasura trigger fire, handler STEP 0 pass	`NEW.status='received' AND record_id IS NOT NULL`	None
`processing`	`skipped_opt_out`	STEP 7 `customer_consent.marketing='false'`	Account đã update info xong	UPDATE account_info OK, KHÔNG INSERT ticket
`processing`	`processed`	STEP 14 COMMIT success	Tất cả 15 STEP pass	Set `processed_at=NOW()`, notify telesale (STEP 12)
`processing`	`dead_letter`	STEP 1-14 exception	Panic recovery in handler	UPDATE `retry_count++`, set `error_message`, log ERROR
`dead_letter`	`received`	Admin manual replay qua action `pancake_replay_event`	Permission `pancake_crm_integration.replay_dlq` + status='dead_letter'	Reset `retry_count=0`, set `error_message=NULL`
`dead_letter`	`permanently_failed`	Cron `pancake_dlq_retry` check retry_count >= 3	(terminal)	Alert ops manual review

Terminal states: processed, auth_failed, ip_blocked, parse_error, skipped_*, permanently_failed.

STATE-002 — Vòng đời `pancake_outage_state.status` (ref PRD LIFECYCLE-002)

Từ	Sang	Trigger	Guard	Side effect
`healthy`	`outage_started`	Cron 4 detect: 0 event nhận trong 5 phút giờ làm việc	`last_event_received_at < NOW()-5min AND now in 08:00-22:00 ICT`	INSERT `pancake_outage_history`, send `noti_pancake_outage` admin, start Cron 6 adaptive polling
`outage_started`	`outage_recovered`	Webhook nhận event mới (real-time)	`last_event_received_at >= outage_started_at`	UPDATE `outage_history.outage_ended_at=NOW()`
`outage_recovered`	`healthy`	Cron 4 verify: sustain 5 phút receiving events	`last_event_received_at > NOW()-1min` consistent for 5 min	Stop Cron 6
`outage_started`	`outage_started`	Re-detect (no recovery)	Cron 6 escalate polling interval 1→2→5→15 min	None

C4) Mô hình dữ liệu (Data Model)

Bảng hiện có (chỉ đọc / ref)

Bảng	Ref code	Cách dùng
`account`	`controller/migrations/default/1660041217108_initialize/up.sql`, `pkg/store/account.go`	🔧 Extend: ADD 2 cột `normalized_phone`, `pancake_metadata` + index
`contact_book`	`controller/migrations/ecommerce/1693035151427_create_table_public_contact_book/up.sql`	🔧 Extend: ADD `primary_phone` + FK + UNIQUE (DEC-006)
`crm_master_data`	(table EXIST, 8 ticket_source)	🔧 Extend: INSERT 1 row `ticket_source_pancake`
`customer_consent`	`controller/migrations/default/1775011824496_..._customer_consent_*`	✅ Reuse table as-is, build Go struct mới
`ticket`	`controller/migrations/crm/1680065571080_create_table_public_ticket/up.sql`	✅ Reuse as-is (KHÔNG đổi schema)
`ticket_distribute`	(table EXIST)	✅ Reuse as-is — Pancake handler INSERT entry qua refactored helper
`app_setting`	`pkg/store/app_setting.go`	🔧 Extend: ADD key `pancake_integration` trong JSON
`notification_template`	(table EXIST)	🔧 Extend: INSERT 3 row template mới
`module_permission_action`	(table EXIST)	🔧 Extend: INSERT module + 6 actions + default seed

Bảng hiện có — ALTER detail

ALTER account:

sql

-- Migration ts 1777870069928_alter_table_public_account_add_pancake_columns/up.sql
ALTER TABLE "public"."account"
  ADD COLUMN IF NOT EXISTS "normalized_phone" TEXT,
  ADD COLUMN IF NOT EXISTS "pancake_metadata" JSONB NOT NULL DEFAULT '{}';

CREATE INDEX IF NOT EXISTS account_normalized_phone_idx
  ON "public"."account" ("normalized_phone")
  WHERE "normalized_phone" IS NOT NULL;

COMMENT ON COLUMN "public"."account"."normalized_phone" IS
  'Phone in E.164 format (libphonenumber). Used for Pancake CRM integration phone matching. Backfilled from phone_code + phone_number.';
COMMENT ON COLUMN "public"."account"."pancake_metadata" IS
  'Latest-state cache from Pancake CRM. Keys: pancake_tag_names (text[]), pancake_source_ids (text[]), last_sync_at (timestamptz), pancake_source_names (text[]).';

Down migration:

sql

DROP INDEX IF EXISTS account_normalized_phone_idx;
ALTER TABLE "public"."account"
  DROP COLUMN IF EXISTS "normalized_phone",
  DROP COLUMN IF EXISTS "pancake_metadata";

ALTER contact_book (DEC-006):

sql

-- Migration ts 1777870069930_alter_table_public_contact_book_add_primary_phone_fk_unique/up.sql

-- Step 1: ADD primary_phone (no constraint, nullable)
ALTER TABLE "public"."contact_book"
  ADD COLUMN IF NOT EXISTS "primary_phone" TEXT;

-- Step 2: Backfill từ account.phone_number cho contact đã có (1-time)
UPDATE "public"."contact_book" cb
SET "primary_phone" = a."normalized_phone"
FROM "public"."account" a
WHERE cb."account_id" = a."id"
  AND cb."primary_phone" IS NULL
  AND a."normalized_phone" IS NOT NULL;

-- Step 3: ADD FK NOT VALID (avoid full table scan)
ALTER TABLE "public"."contact_book"
  ADD CONSTRAINT contact_book_account_id_fkey
  FOREIGN KEY ("account_id") REFERENCES "public"."account"("id")
  ON UPDATE RESTRICT ON DELETE CASCADE
  NOT VALID;

-- Step 4: VALIDATE FK in background
ALTER TABLE "public"."contact_book" VALIDATE CONSTRAINT contact_book_account_id_fkey;

-- Step 5: ADD UNIQUE (account_id) — REQUIRED for query-before-insert pattern STEP 5b
-- Cleanup duplicate trước nếu có
DELETE FROM "public"."contact_book" cb1
USING "public"."contact_book" cb2
WHERE cb1."account_id" = cb2."account_id"
  AND cb1."created_at" > cb2."created_at";  -- giữ row cũ nhất

ALTER TABLE "public"."contact_book"
  ADD CONSTRAINT contact_book_account_id_unique UNIQUE ("account_id");

COMMENT ON COLUMN "public"."contact_book"."primary_phone" IS
  'Primary phone E.164 format. Auto-created from Pancake webhook (FR-007) or manually entered.';

Down migration:

sql

ALTER TABLE "public"."contact_book"
  DROP CONSTRAINT IF EXISTS contact_book_account_id_unique,
  DROP CONSTRAINT IF EXISTS contact_book_account_id_fkey,
  DROP COLUMN IF EXISTS "primary_phone";

INSERT crm_master_data slot 9 (DEC-017):

sql

-- Migration ts 1777870069932_insert_ticket_source_pancake/up.sql
INSERT INTO "public"."crm_master_data" ("id", "name", "type", "disabled", "keywords")
VALUES ('ticket_source_pancake', 'Pancake CRM', 'ticket_source', false,
        'pancake,crm,lead,facebook,zalo,tiktok')
ON CONFLICT ("id") DO NOTHING;

Down migration:

sql

DELETE FROM "public"."crm_master_data" WHERE "id" = 'ticket_source_pancake';

Bảng mới (4 bảng `pancake_*`)

Diva pattern: disabled BOOLEAN (KHÔNG deleted_at — DEC-007). Audit fields: created_at, updated_at, created_by, updated_by. Timezone: tất cả TIMESTAMPTZ default Asia/Ho_Chi_Minh.

4.1 `pancake_webhook_event` — Audit raw + status lifecycle

sql

-- Migration ts 1777870069931_create_pancake_tables/up.sql (Part 1/4)
CREATE TABLE IF NOT EXISTS "public"."pancake_webhook_event" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Idempotency 3-tuple (DEC-004)
  "record_id" TEXT,            -- Pancake record ID (nullable: case test event / parse_error)
  "modified_on" TIMESTAMPTZ,   -- Pancake-side modified_on
  "payload_hash" TEXT NOT NULL, -- SHA-256 hex of canonical payload

  -- Raw audit
  "payload" JSONB NOT NULL,    -- Full raw Pancake webhook payload
  "headers" JSONB DEFAULT '{}',-- HTTP headers for forensic (HMAC, IP, User-Agent)
  "source_ip" INET,
  "event_type" TEXT NOT NULL DEFAULT 'record',  -- record / reconciled / replayed
  "is_test" BOOLEAN NOT NULL DEFAULT false,     -- DEC-009

  -- Lifecycle status (STATE-001 — 12 states)
  "status" TEXT NOT NULL DEFAULT 'ingested',
  "error_message" TEXT,
  "retry_count" SMALLINT NOT NULL DEFAULT 0,
  "processed_at" TIMESTAMPTZ,
  "last_received_at" TIMESTAMPTZ DEFAULT now(),  -- update khi duplicate retry

  -- FK ref (resolved sau process)
  "pancake_source_id" TEXT,    -- raw source ID from Pancake payload
  "resolved_branch_id" UUID,   -- after routing resolution
  "resolved_account_id" TEXT,  -- after match/upsert
  "resolved_ticket_id" UUID,   -- after INSERT ticket

  -- Audit fields (Diva pattern)
  "disabled" BOOLEAN NOT NULL DEFAULT false,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "created_by" TEXT DEFAULT 'system_pancake_webhook',
  "updated_by" TEXT,

  -- Constraints
  CONSTRAINT pancake_webhook_event_status_check CHECK (status IN (
    'ingested', 'received', 'processing', 'processed',
    'auth_failed', 'ip_blocked', 'parse_error',
    'skipped_duplicate', 'skipped_source_disabled', 'skipped_kill_switch', 'skipped_opt_out',
    'dead_letter', 'permanently_failed'
  )),
  CONSTRAINT pancake_webhook_event_event_type_check CHECK (event_type IN ('record', 'reconciled', 'replayed')),
  CONSTRAINT pancake_webhook_event_idempotency_unique UNIQUE ("record_id", "modified_on", "payload_hash")
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_status_created
  ON "public"."pancake_webhook_event" ("status", "created_at" DESC)
  WHERE status = 'processed';  -- partial cover FORMULA-001

CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_status_received
  ON "public"."pancake_webhook_event" ("status", "created_at" DESC)
  WHERE status IN ('received', 'processing', 'dead_letter');  -- partial cover DLQ + queue

CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_record_id
  ON "public"."pancake_webhook_event" ("record_id")
  WHERE record_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_resolved_account
  ON "public"."pancake_webhook_event" ("resolved_account_id")
  WHERE resolved_account_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_resolved_ticket
  ON "public"."pancake_webhook_event" ("resolved_ticket_id")
  WHERE resolved_ticket_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pancake_webhook_event_is_test
  ON "public"."pancake_webhook_event" ("is_test", "created_at" DESC)
  WHERE is_test = true;  -- cron cleanup query

-- Update trigger
CREATE TRIGGER set_pancake_webhook_event_updated_at
  BEFORE UPDATE ON "public"."pancake_webhook_event"
  FOR EACH ROW EXECUTE FUNCTION set_current_timestamp_updated_at();

COMMENT ON TABLE "public"."pancake_webhook_event" IS
  'Pancake CRM webhook audit log + lifecycle state machine. See dev-spec C3 STATE-001 for valid transitions.';

4.2 `pancake_connection` — Workspace config

sql

-- Part 2/4
CREATE TABLE IF NOT EXISTS "public"."pancake_connection" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "workspace_id" TEXT NOT NULL,            -- Pancake workspace identifier
  "workspace_name" TEXT,                   -- Display name
  "api_key_encrypted" TEXT,                -- Encrypted via pgcrypto (RSK-012)
  "webhook_token" TEXT NOT NULL,           -- Random 32-byte hex, rotate quarterly
  "webhook_url" TEXT,                      -- Full URL for display (computed)
  "vip_tag_names" TEXT[] NOT NULL DEFAULT '{}',  -- DEC-008: text array, case-insensitive match
  "ip_whitelist" INET[] NOT NULL DEFAULT '{}',   -- Pancake IP allowlist (PD-002)
  "status" TEXT NOT NULL DEFAULT 'paused',  -- active / paused / suspended_by_pancake / error

  -- Health metrics (cached)
  "last_event_received_at" TIMESTAMPTZ,
  "last_sources_sync_at" TIMESTAMPTZ,      -- Cron 3
  "last_error_at" TIMESTAMPTZ,
  "last_error_message" TEXT,

  -- Audit fields
  "disabled" BOOLEAN NOT NULL DEFAULT false,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "created_by" TEXT,
  "updated_by" TEXT,

  CONSTRAINT pancake_connection_status_check CHECK (status IN ('active', 'paused', 'suspended_by_pancake', 'error')),
  CONSTRAINT pancake_connection_workspace_unique UNIQUE ("workspace_id"),
  CONSTRAINT pancake_connection_webhook_token_unique UNIQUE ("webhook_token")
);

CREATE INDEX IF NOT EXISTS idx_pancake_connection_status
  ON "public"."pancake_connection" ("status") WHERE disabled = false;

CREATE TRIGGER set_pancake_connection_updated_at
  BEFORE UPDATE ON "public"."pancake_connection"
  FOR EACH ROW EXECUTE FUNCTION set_current_timestamp_updated_at();

COMMENT ON TABLE "public"."pancake_connection" IS
  'Pancake workspace connection config. MVP-1 single-row (1 workspace). VIP tag matching uses text NAMES case-insensitive (DEC-008).';

4.3 `pancake_source_routing` — Source → Branch mapping

sql

-- Part 3/4
CREATE TABLE IF NOT EXISTS "public"."pancake_source_routing" (
  "pancake_source_id" TEXT PRIMARY KEY,    -- From Pancake REST /sources
  "pancake_source_name" TEXT NOT NULL,     -- Display label (FB page name, Zalo OA name...)
  "diva_branch_id" UUID,                   -- Nullable for Loose mode (DEC-013)
  "is_active" BOOLEAN NOT NULL DEFAULT true,
  "last_synced_at" TIMESTAMPTZ DEFAULT now(),  -- From Cron 3
  "first_seen_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "note" TEXT,                             -- Admin can add note

  -- Audit fields
  "disabled" BOOLEAN NOT NULL DEFAULT false,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "created_by" TEXT,
  "updated_by" TEXT,

  CONSTRAINT pancake_source_routing_branch_fkey
    FOREIGN KEY ("diva_branch_id") REFERENCES "public"."branch"("id")
    ON UPDATE RESTRICT ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_pancake_source_routing_active
  ON "public"."pancake_source_routing" ("is_active", "diva_branch_id")
  WHERE disabled = false;

CREATE TRIGGER set_pancake_source_routing_updated_at
  BEFORE UPDATE ON "public"."pancake_source_routing"
  FOR EACH ROW EXECUTE FUNCTION set_current_timestamp_updated_at();

COMMENT ON TABLE "public"."pancake_source_routing" IS
  'Pancake source → Diva branch mapping. branch_id NULL = Loose mode (DEC-013, ticket created with assignee=NULL).';

4.4 `pancake_outage_state` — Single-row outage tracking + history

sql

-- Part 4/4
CREATE TABLE IF NOT EXISTS "public"."pancake_outage_state" (
  "id" SMALLINT PRIMARY KEY DEFAULT 1,      -- Single-row enforced
  "status" TEXT NOT NULL DEFAULT 'healthy', -- STATE-002
  "outage_started_at" TIMESTAMPTZ,
  "outage_ended_at" TIMESTAMPTZ,
  "last_event_received_at" TIMESTAMPTZ,
  "last_check_at" TIMESTAMPTZ DEFAULT now(),
  "current_polling_interval_sec" INT DEFAULT 60,  -- Cron 6 adaptive
  "polling_interval_step" SMALLINT DEFAULT 0,     -- 0=60s, 1=120s, 2=300s, 3=900s
  "notes" TEXT,

  -- Audit fields
  "disabled" BOOLEAN NOT NULL DEFAULT false,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT pancake_outage_state_single_row CHECK (id = 1),
  CONSTRAINT pancake_outage_state_status_check CHECK (status IN ('healthy', 'outage_started', 'outage_recovered'))
);

-- Audit history table (cho FORMULA-006 MTTR query)
CREATE TABLE IF NOT EXISTS "public"."pancake_outage_history" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "outage_started_at" TIMESTAMPTZ NOT NULL,
  "outage_ended_at" TIMESTAMPTZ,           -- NULL = still ongoing
  "max_polling_interval_sec" INT,
  "missed_events_count" INT DEFAULT 0,     -- Filled by Cron 7 reconciliation
  "notes" TEXT,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_pancake_outage_history_started
  ON "public"."pancake_outage_history" ("outage_started_at" DESC);

-- Init single row
INSERT INTO "public"."pancake_outage_state" ("id", "status")
VALUES (1, 'healthy')
ON CONFLICT ("id") DO NOTHING;

CREATE TRIGGER set_pancake_outage_state_updated_at
  BEFORE UPDATE ON "public"."pancake_outage_state"
  FOR EACH ROW EXECUTE FUNCTION set_current_timestamp_updated_at();

CREATE TRIGGER set_pancake_outage_history_updated_at
  BEFORE UPDATE ON "public"."pancake_outage_history"
  FOR EACH ROW EXECUTE FUNCTION set_current_timestamp_updated_at();

COMMENT ON TABLE "public"."pancake_outage_state" IS
  'Single-row state machine for Pancake webhook outage detection. See dev-spec C3 STATE-002.';
COMMENT ON TABLE "public"."pancake_outage_history" IS
  'Outage incident audit log. Used for FORMULA-006 MTTR calculation.';

Hàm SQL helper

sql

-- Migration 1777870069931 (Part 5 — appended in same migration)

-- Generic update trigger function (đã tồn tại trong Diva, ref nếu cần)
-- CREATE OR REPLACE FUNCTION set_current_timestamp_updated_at() RETURNS TRIGGER ...

-- Pancake-specific: advisory lock helper (clarity wrapper)
CREATE OR REPLACE FUNCTION pancake_advisory_lock_account(p_normalized_phone TEXT)
RETURNS VOID AS $$
BEGIN
  IF p_normalized_phone IS NULL OR p_normalized_phone = '' THEN
    RETURN;  -- Skip lock if phone unknown (rare edge)
  END IF;
  PERFORM pg_advisory_xact_lock(hashtext('account_phone_' || p_normalized_phone));
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION pancake_advisory_lock_account(TEXT) IS
  'Transaction-scoped advisory lock per normalized phone. Releases on COMMIT/ROLLBACK. DEC-020.';

Backfill `normalized_phone` (batch script)

sql

-- Migration ts 1777870069929_backfill_normalized_phone/up.sql
-- Note: Phone normalization is preferred in Go (libphonenumber) for accuracy.
-- This SQL backfill is approximate: prefix +84 to phone_number if phone_code=84.
-- A follow-up Go batch job will refine using libphonenumber if needed.

DO $$
DECLARE
  batch_size INT := 10000;
  total_updated INT := 0;
  iteration_count INT;
BEGIN
  LOOP
    UPDATE "public"."account"
    SET "normalized_phone" = CASE
      WHEN phone_code IS NOT NULL AND phone_number IS NOT NULL AND phone_number != '' THEN
        '+' || phone_code::TEXT || regexp_replace(phone_number, '^0+', '')
      ELSE NULL
    END
    WHERE "id" IN (
      SELECT "id" FROM "public"."account"
      WHERE "normalized_phone" IS NULL
        AND "phone_number" IS NOT NULL
      LIMIT batch_size
    );

    GET DIAGNOSTICS iteration_count = ROW_COUNT;
    total_updated := total_updated + iteration_count;

    EXIT WHEN iteration_count = 0;

    -- Commit per batch (long-running migration safety)
    -- Note: In Hasura migration this runs as single tx; for production scale use external job.
    RAISE NOTICE 'Backfilled % rows so far (total: %)', iteration_count, total_updated;
  END LOOP;

  RAISE NOTICE 'Backfill complete: % rows updated', total_updated;
END $$;

Note: For 1M+ account dataset, run backfill as external Go job (split transactions). Estimate ~8.3 phút for 1M rows × 0.5ms.

C5) Quy ước tích hợp (Integration Conventions)

Hasura metadata

File: controller/metadata/databases/default/tables/public_pancake_webhook_event.yaml

yaml

table:
  name: pancake_webhook_event
  schema: public
configuration:
  custom_root_fields: {}
  custom_column_names: {}

# Permissions: Admin only
select_permissions:
  - role: admin
    permission:
      columns: '*'
      filter: {}
      allow_aggregations: true
insert_permissions:
  - role: admin
    permission:
      columns: '*'
      check: {}
update_permissions:
  - role: admin
    permission:
      columns: [status, error_message, retry_count, processed_at, updated_by, disabled]
      filter: {}
      check: {}
delete_permissions:
  - role: admin
    permission:
      filter: {}

# Event trigger watch status column → fire crm-api
event_triggers:
  - name: pancake_webhook_event_status_update
    definition:
      enable_manual: true
      update:
        columns: [status]
    webhook_from_env: CRM_API_BASE_URL
    webhook_path: /events
    retry_conf:
      num_retries: 3
      interval_sec: 30
      timeout_sec: 60
    headers:
      - name: X-Hasura-Event-Source
        value: pancake_webhook_event_status_update

Files (4 YAML): public_pancake_webhook_event.yaml, public_pancake_connection.yaml, public_pancake_source_routing.yaml, public_pancake_outage_state.yaml (+ public_pancake_outage_history.yaml).

Permissions tóm tắt: Admin only select/insert/update/delete. Manager/Telesale/POS: NO access.

GraphQL query / mutation

File: diva-admin/src/modules/settings/graphql/pancake.graphql (NEW)

graphql

# Connection
query GetPancakeConnection {
  pancake_connection(limit: 1, where: { disabled: { _eq: false } }) {
    id
    workspace_id
    workspace_name
    webhook_token
    webhook_url
    vip_tag_names
    ip_whitelist
    status
    last_event_received_at
    last_sources_sync_at
    last_error_at
    last_error_message
    updated_at
  }
}

mutation UpdatePancakeConnection($id: uuid!, $set: pancake_connection_set_input!) {
  update_pancake_connection_by_pk(pk_columns: { id: $id }, _set: $set) {
    id
    updated_at
  }
}

# Source routing
query ListPancakeSourceRouting {
  pancake_source_routing(
    where: { disabled: { _eq: false } }
    order_by: { pancake_source_name: asc }
  ) {
    pancake_source_id
    pancake_source_name
    diva_branch_id
    is_active
    last_synced_at
    note
    branch {
      id
      label
    }
  }
}

mutation UpdatePancakeSourceRouting(
  $pancake_source_id: String!
  $set: pancake_source_routing_set_input!
) {
  update_pancake_source_routing_by_pk(
    pk_columns: { pancake_source_id: $pancake_source_id }
    _set: $set
  ) {
    pancake_source_id
    updated_at
  }
}

# Audit / DLQ
query ListPancakeWebhookEvents(
  $where: pancake_webhook_event_bool_exp
  $limit: Int = 50
  $offset: Int = 0
) {
  pancake_webhook_event(
    where: $where
    order_by: { created_at: desc }
    limit: $limit
    offset: $offset
  ) {
    id
    record_id
    status
    event_type
    is_test
    retry_count
    error_message
    pancake_source_id
    resolved_branch_id
    resolved_account_id
    resolved_ticket_id
    created_at
    processed_at
  }
  pancake_webhook_event_aggregate(where: $where) {
    aggregate {
      count
    }
  }
}

query GetPancakeWebhookEventDetail($id: uuid!) {
  pancake_webhook_event_by_pk(id: $id) {
    id
    record_id
    modified_on
    payload
    headers
    source_ip
    status
    error_message
    retry_count
    event_type
    is_test
    pancake_source_id
    resolved_branch_id
    resolved_account_id
    resolved_ticket_id
    created_at
    processed_at
    last_received_at
  }
}

# Hasura action: replay DLQ event
mutation PancakeReplayEvent($event_id: uuid!) {
  pancake_replay_event(event_id: $event_id) {
    success
    message
  }
}

mutation PancakeReplayEventBulk($event_ids: [uuid!]!) {
  pancake_replay_event_bulk(event_ids: $event_ids) {
    success
    replayed_count
    skipped_count
    message
  }
}

# Health
query GetPancakeHealthMetrics($from: timestamptz!, $to: timestamptz!) {
  pancake_outage_state(limit: 1) {
    status
    outage_started_at
    outage_ended_at
    current_polling_interval_sec
    last_event_received_at
    last_check_at
  }
  pancake_outage_history(
    where: { outage_started_at: { _gte: $from, _lte: $to } }
    order_by: { outage_started_at: desc }
  ) {
    id
    outage_started_at
    outage_ended_at
    max_polling_interval_sec
    missed_events_count
  }
}

Hasura Action: `pancake_replay_event` / `pancake_replay_event_bulk`

Path: controller/metadata/actions.yaml + actions.graphql

Schema:

graphql

type SuccessOutput {
  success: Boolean!
  message: String!
}

type BulkReplayOutput {
  success: Boolean!
  replayed_count: Int!
  skipped_count: Int!
  message: String!
}

type Mutation {
  pancake_replay_event(event_id: uuid!): SuccessOutput
  pancake_replay_event_bulk(event_ids: [uuid!]!): BulkReplayOutput
}

actions.yaml:

yaml

- name: pancake_replay_event
  definition:
    kind: synchronous
    handler: '{{CRM_API_BASE_URL}}/actions/pancake_replay_event'
    forward_client_headers: true
    headers:
      - name: x-hasura-admin-secret
        value_from_env: HASURA_GRAPHQL_ADMIN_SECRET
  permissions:
    - role: admin

- name: pancake_replay_event_bulk
  definition:
    kind: synchronous
    handler: '{{CRM_API_BASE_URL}}/actions/pancake_replay_event_bulk'
    forward_client_headers: true
  permissions:
    - role: admin

Handler: services/crm-api/action/pancake_replay_event.go (NEW)

package action

import (
  "context"
  "fmt"
  "github.com/diva-group/diva-backend/pkg/store"
)

// Logic:
// 1. Check session_variables['x-hasura-role'] == 'admin' (extra defense beyond Hasura permission)
// 2. Load event by ID → must exist + status='dead_letter' or 'permanently_failed'
// 3. UPDATE status='received', retry_count=0, error_message=NULL → fires Hasura trigger
// 4. Return success
func PancakeReplayEvent(ctx context.Context, input PancakeReplayEventInput) (*SuccessOutput, error) {
  // Extract session vars
  if !isAdmin(ctx) {
    return &SuccessOutput{Success: false, Message: "UNAUTHORIZED"}, nil
  }
  event, err := store.GetPancakeWebhookEventByID(db, input.EventID)
  if err != nil {
    return &SuccessOutput{Success: false, Message: "PANCAKE_EVENT_NOT_FOUND"}, nil
  }
  if event.Status != "dead_letter" && event.Status != "permanently_failed" {
    return &SuccessOutput{
      Success: false,
      Message: fmt.Sprintf("PANCAKE_EVENT_NOT_REPLAYABLE: status=%s", event.Status),
    }, nil
  }
  err = store.UpdatePancakeWebhookEventReplay(db, event.ID, ctx.Value("user_id").(string))
  if err != nil {
    return &SuccessOutput{Success: false, Message: err.Error()}, nil
  }
  return &SuccessOutput{Success: true, Message: "Đã đẩy lại sự kiện vào hàng xử lý."}, nil
}

// Bulk: rate-limited 100 events / call, replay sequential
func PancakeReplayEventBulk(ctx context.Context, input PancakeReplayEventBulkInput) (*BulkReplayOutput, error) {
  if !isAdmin(ctx) {
    return &BulkReplayOutput{Success: false, Message: "UNAUTHORIZED"}, nil
  }
  if len(input.EventIDs) > 100 {
    return &BulkReplayOutput{Success: false, Message: "PANCAKE_REPLAY_RATE_LIMIT: max 100/call"}, nil
  }
  // ... iterate, rate-limit 5/giây
}

Endpoint pattern (BẮT BUỘC — không tự sáng tạo route)

Diva services chỉ expose 4 endpoint patterns:

POST /actions      → Hasura action handlers (crm-api/action/*.go)
POST /events       → Hasura event trigger handlers (crm-api/event/*.go)
POST /schedulers   → Cron job handlers (crm-api/scheduler/*.go)
GET  /healthz      → Health check

Ngoại lệ public webhook receiver ở services/webhook:

POST /api/pancake/record/{token} — chỉ endpoint public (no Hasura action), trả 200 fail-open

Hasura action pancake_replay_event đăng ký 4 nơi:

✅ Handler file: services/crm-api/action/pancake_replay_event.go
✅ Register map: services/crm-api/action/action.go
✅ Action def:   controller/metadata/actions.yaml
✅ GraphQL sig:  controller/metadata/actions.graphql

Flow chi tiết 15 STEP (`event_pancake_process_record.go`) — pseudocode Go

// services/crm-api/event/event_pancake_process_record.go
package event

import (
  "context"
  "fmt"
  "time"

  "github.com/diva-group/diva-backend/pkg/pancake"
  "github.com/diva-group/diva-backend/pkg/store"
)

const (
  PancakeCreatedBy = "system_pancake_webhook"
  TicketSourcePancake = "ticket_source_pancake"
)

// EventPancakeProcessRecord — handler for Hasura trigger `pancake_webhook_event_status_update`.
// Fires when pancake_webhook_event.status is updated. Filter STEP 0 ensures only status='received'.
func EventPancakeProcessRecord(ctx context.Context, payload EventPayload) error {
  newRow := payload.Event.Data.New

  // STEP 0 — Filter: only process status='received' with record_id present.
  if newRow.Status != "received" || newRow.RecordID == nil {
    return nil
  }

  // STEP 1 — BEGIN TX + re-fetch FOR UPDATE (lock row).
  tx, err := db.BeginTx(ctx, nil)
  if err != nil {
    return err
  }
  defer func() {
    if p := recover(); p != nil {
      tx.Rollback()
      _ = store.MarkPancakeEventDeadLetter(db, newRow.ID, fmt.Sprintf("panic: %v", p))
      panic(p)
    }
  }()

  event, err := store.GetPancakeWebhookEventForUpdate(tx, newRow.ID)
  if err != nil {
    tx.Rollback()
    return err
  }
  // Re-check (idempotent guard: maybe another worker already processed)
  if event.Status != "received" {
    tx.Rollback()
    return nil
  }

  // STEP 1.5 — UPDATE status='processing'.
  if err := store.UpdatePancakeEventStatus(tx, event.ID, "processing", nil); err != nil {
    tx.Rollback()
    return err
  }

  // Wrap remaining steps with err handling — on any error, mark dead_letter.
  err = func() error {
    var rawPayload pancake.RecordPayload
    if err := event.UnmarshalPayload(&rawPayload); err != nil {
      return fmt.Errorf("parse payload: %w", err)
    }

    // STEP 2 — Phone normalize (E.164).
    normalizedPhone, err := pancake.NormalizePhone(rawPayload.PhoneNumber, "VN")
    if err != nil {
      // Fallback: store raw, log warning
      normalizedPhone = ""
    }

    // STEP 3 — Advisory lock per phone (DEC-020).
    if _, err := tx.ExecContext(ctx,
      "SELECT pancake_advisory_lock_account($1)", normalizedPhone); err != nil {
      return fmt.Errorf("advisory lock: %w", err)
    }

    // STEP 4 — Resolve branch from source routing.
    routing, err := store.GetPancakeSourceRouting(tx, rawPayload.SourceID)
    if err != nil && err != store.ErrNotFound {
      return fmt.Errorf("source routing: %w", err)
    }
    var branchID *string
    if routing != nil && routing.IsActive && routing.DivaBranchID != nil {
      bID := routing.DivaBranchID.String()
      branchID = &bID
    }
    // branchID = nil → Loose mode (DEC-013)

    // STEP 5 — Match/upsert account.
    var (
      account    *store.Account
      isNewAccount bool
    )
    if normalizedPhone != "" {
      account, _ = store.GetAccountByNormalizedPhone(tx, normalizedPhone)
    }
    if account == nil {
      account, err = store.CreateAccountFromPancake(tx, &rawPayload, normalizedPhone, branchID)
      if err != nil {
        return fmt.Errorf("create account: %w", err)
      }
      isNewAccount = true
    } else {
      // Update basic info (display_name, address text in pancake_metadata) — KHÔNG override existing customer data
      if err := store.UpdateAccountInfoFromPancake(tx, account.ID, &rawPayload); err != nil {
        return fmt.Errorf("update account: %w", err)
      }
    }

    // STEP 5b — Auto-create contact_book (FR-007, DEC-006).
    contact, _ := store.GetContactBookByAccountID(tx, account.ID)
    if contact == nil {
      if err := store.CreateContactBookFromPancake(tx, account.ID, normalizedPhone, &rawPayload); err != nil {
        return fmt.Errorf("create contact: %w", err)
      }
    }

    // STEP 6 — Update pancake_metadata cache.
    oldMeta := account.PancakeMetadata
    if err := store.UpdateAccountPancakeMetadata(tx, account.ID, &rawPayload); err != nil {
      return fmt.Errorf("update pancake_metadata: %w", err)
    }

    // STEP 7 — Check opt-out marketing (FR-017, DEC-014).
    consent, _ := store.GetCustomerConsent(tx, account.ID)
    if consent != nil && consent.ConsentData["marketing"] == "false" {
      _ = store.UpdatePancakeEventStatus(tx, event.ID, "skipped_opt_out", nil)
      return tx.Commit()  // Early return — account updated, no ticket
    }

    // STEP 8 — Smart update Q2.b (FR-009, DEC-011).
    phoneChanged := !isNewAccount && oldMeta.LastPhone != normalizedPhone
    sourceNew := !isNewAccount && !contains(oldMeta.PancakeSourceIDs, rawPayload.SourceID)
    vipTagNew := false
    {
      conn, _ := store.GetActivePancakeConnection(tx)
      if conn != nil {
        newVIPTags := pancake.MatchVIPTags(rawPayload.TagNames, conn.VIPTagNames)
        oldVIPTags := pancake.MatchVIPTags(oldMeta.PancakeTagNames, conn.VIPTagNames)
        for _, t := range newVIPTags {
          if !contains(oldVIPTags, t) {
            vipTagNew = true
            break
          }
        }
      }
    }
    shouldCreateTicket := isNewAccount || phoneChanged || sourceNew || vipTagNew
    if !shouldCreateTicket {
      _ = store.UpdatePancakeEventStatus(tx, event.ID, "processed", timePtr(time.Now()))
      return tx.Commit()
    }

    // STEP 9 — Round-robin assignee (FR-008, DEC-012) — REUSE GetTicketUpdates refactored helper.
    ticket := &store.Ticket{
      ID:           store.NewUUID(),
      CustomerID:   account.ID,
      SourceID:     TicketSourcePancake,
      TargetID:     "telesales",
      BranchID:     branchID,
      DueDate:      todayEndOfDayICT(),
      StatusID:     "ticket_status_new",
      InputNote:    pancake.BuildPancakeInputNote(&rawPayload),
      CreatedBy:    PancakeCreatedBy,
    }

    // STEP 10 — INSERT ticket unassigned first, then call helper.
    if err := store.InsertTicket(tx, ticket); err != nil {
      return fmt.Errorf("insert ticket: %w", err)
    }

    var assigneeID *string
    if branchID != nil {
      branchUsers, _ := store.GetActiveTelesalesByBranch(tx, *branchID)
      if len(branchUsers) > 0 {
        updates, err := scheduler.GetTicketUpdates(tx, []store.Ticket{*ticket}, branchUsers, true /* isTelesales */)
        if err == nil && len(updates) > 0 {
          assigneeID = &updates[0].AssigneeID
          if err := store.UpdateTicketAssignee(tx, ticket.ID, *assigneeID); err != nil {
            return fmt.Errorf("update assignee: %w", err)
          }
          if err := store.InsertTicketDistribute(tx, *branchID, "telesales", *assigneeID); err != nil {
            return fmt.Errorf("insert distribute: %w", err)
          }
        }
      }
    }

    // STEP 11 — Update pancake_metadata.last_sync_at.
    if err := store.SetAccountPancakeMetadataLastSync(tx, account.ID, time.Now()); err != nil {
      return fmt.Errorf("set last_sync_at: %w", err)
    }

    // STEP 12 — Notification dispatch (FR-011, DEC-024). NON-BLOCKING.
    go func(ticketID string, branch *string, assignee *string) {
      if assignee != nil {
        _ = notification.SendTicketAssignedPancake(ticketID, *assignee)
      } else if branch == nil {
        _ = notification.SendPancakeUnmappedBranch(ticketID)
      } else {
        _ = notification.SendPancakeNoTelesaleActive(ticketID, *branch)
      }
    }(ticket.ID, branchID, assigneeID)

    // STEP 13 — UPDATE event.status='processed' + resolved fields.
    if err := store.MarkPancakeEventProcessed(tx, event.ID, &ticket.ID, account.ID, branchID); err != nil {
      return fmt.Errorf("mark processed: %w", err)
    }

    // STEP 14 — COMMIT.
    return tx.Commit()
  }()

  if err != nil {
    _ = tx.Rollback()
    _ = store.MarkPancakeEventDeadLetter(db, newRow.ID, err.Error())
    return err
  }
  return nil
}

func todayEndOfDayICT() time.Time {
  loc, _ := time.LoadLocation("Asia/Ho_Chi_Minh")
  now := time.Now().In(loc)
  return time.Date(now.Year(), now.Month(), now.Day(), 23, 59, 59, 0, loc)
}

Webhook receiver flow (`services/webhook/handler/pancake.go`) — pseudocode

// services/webhook/handler/pancake.go
package handler

import (
  "crypto/sha256"
  "encoding/hex"
  "encoding/json"
  "io"
  "net/http"
  "time"

  "github.com/gin-gonic/gin"
  "github.com/diva-group/diva-backend/pkg/store"
)

// PancakeWebhookHandler — POST /api/pancake/record/{token}
// Fail-open: always return 200 (DEC-003).
func PancakeWebhookHandler(c *gin.Context) {
  startedAt := time.Now()
  defer func() {
    metrics.PancakeWebhookLatency.Observe(time.Since(startedAt).Seconds())
    c.JSON(http.StatusOK, gin.H{"ok": true})  // Always 200
  }()

  token := c.Param("token")
  rawBody, err := io.ReadAll(c.Request.Body)
  if err != nil {
    insertEventFailed(c, "parse_error", rawBody, "read body: "+err.Error())
    return
  }

  // 1. INSERT raw event status='ingested' (audit before verify).
  payloadHash := sha256hex(rawBody)
  event := &store.PancakeWebhookEvent{
    ID:          store.NewUUID(),
    PayloadHash: payloadHash,
    Payload:     rawBody,
    Headers:     captureHeaders(c.Request.Header),
    SourceIP:    c.ClientIP(),
    Status:      "ingested",
    EventType:   "record",
  }
  // Parse to extract record_id / modified_on / source_id (best-effort)
  var pData struct {
    RecordID    string    `json:"record_id"`
    ModifiedOn  time.Time `json:"modified_on"`
    SourceID    string    `json:"source_id"`
    PhoneNumber string    `json:"phone_number"`
    IsTest      bool      `json:"is_test"`
  }
  parseErr := json.Unmarshal(rawBody, &pData)
  if parseErr == nil {
    event.RecordID = &pData.RecordID
    event.ModifiedOn = &pData.ModifiedOn
    event.PancakeSourceID = &pData.SourceID
    event.IsTest = pData.IsTest
  }

  if err := store.InsertPancakeWebhookEvent(db, event); err != nil {
    // Dedup hit? Check ON CONFLICT
    if isDuplicateErr(err) {
      _ = store.MarkDuplicate(db, payloadHash, pData.RecordID, pData.ModifiedOn)
      return
    }
    logger.Error("INSERT pancake_webhook_event failed", err)
    return
  }

  // 2. Verify token URL (lookup pancake_connection)
  conn, err := store.GetPancakeConnectionByToken(db, token)
  if err != nil || conn == nil {
    _ = store.UpdatePancakeEventStatus(db, event.ID, "auth_failed", strPtr("invalid token"))
    return
  }

  // 3. Verify IP whitelist (if configured)
  if len(conn.IPWhitelist) > 0 {
    if !ipInList(c.ClientIP(), conn.IPWhitelist) {
      _ = store.UpdatePancakeEventStatus(db, event.ID, "ip_blocked", strPtr("ip not in whitelist"))
      return
    }
  }

  // 4. Verify HMAC (if PD-001 resolved + conn.hmac_secret set)
  // [TBD-PD-001] — defer to W4

  // 5. Validate JSON schema (parse_err already covered)
  if parseErr != nil || pData.RecordID == "" {
    _ = store.UpdatePancakeEventStatus(db, event.ID, "parse_error", strPtr(parseErr.Error()))
    return
  }

  // 6. Check kill switch
  if !appSetting.PancakeIntegration.Enabled {
    _ = store.UpdatePancakeEventStatus(db, event.ID, "skipped_kill_switch", nil)
    return
  }

  // 7. Check source routing active
  routing, _ := store.GetPancakeSourceRouting(db, pData.SourceID)
  if routing != nil && !routing.IsActive {
    _ = store.UpdatePancakeEventStatus(db, event.ID, "skipped_source_disabled", nil)
    return
  }

  // 8. Update connection.last_event_received_at + outage_state
  _ = store.UpdatePancakeConnectionLastReceived(db, conn.ID, time.Now())
  _ = store.UpdatePancakeOutageStateLastReceived(db, time.Now())

  // 9. PROMOTE: UPDATE status='received' → fires Hasura trigger.
  if err := store.UpdatePancakeEventStatus(db, event.ID, "received", nil); err != nil {
    logger.Error("PROMOTE failed", err)
    return
  }
}

Cron schedulers (services/crm-api/scheduler)

6 cron mới (theo design doc, mapping):

Cron name	Schedule	Handler file	Purpose
`pancake_source_sync_hourly`	`0 * * * *`	`pancake_source_sync.go`	Cron 3 — call Pancake REST `/sources`, upsert `pancake_source_routing`
`pancake_outage_detect`	`* * * * *` (1 min)	`pancake_outage_detect.go`	Cron 4 — check `last_event_received_at`, transition state
`pancake_adaptive_polling`	Dynamic (via `pancake_outage_state.current_polling_interval_sec`)	`pancake_adaptive_polling.go`	Cron 6 — polling Pancake `/records?modified_since` when outage
`pancake_daily_reconciliation`	`0 2 * * *` (02:00 AM ICT)	`pancake_daily_reconciliation.go`	Cron 7 — 25h reconciliation (DEC-025)
`pancake_dlq_retry`	`/5 * * *` (every 5 min)	`pancake_dlq_retry.go`	Auto retry dead_letter events with `retry_count < 3`
`pancake_test_event_cleanup`	`0 4 * * *` (04:00 AM ICT)	`pancake_test_event_cleanup.go`	Soft-delete (disabled=true) events `is_test=true AND created_at < NOW()-24h` (DEC-009)

Hasura cron_triggers.yaml config example:

yaml

- name: pancake_source_sync_hourly
  webhook: '{{CRM_API_BASE_URL}}/schedulers'
  schedule: 0 * * * *
  payload:
    name: pancake_source_sync_hourly
  retry_conf:
    num_retries: 2
    timeout_seconds: 600
    tolerance_seconds: 21600
    retry_interval_seconds: 60
  include_in_metadata: true
  comment: 'Pancake CRM: hourly sync /sources REST (DEC-018, Cron 3)'

Bảng mã lỗi (M+)

Tình huống	Error code	HTTP / GraphQL	FE xử lý	Copy hiển thị (vi)
Event không tìm thấy (replay)	`PANCAKE_EVENT_NOT_FOUND`	200 (GraphQL error in body)	Toast lỗi đỏ	"Không tìm thấy sự kiện. Có thể đã bị xóa."
Không quyền replay	`UNAUTHORIZED`	401 (Hasura permission)	Button hidden	(no UI surface)
Replay khi event không phải `dead_letter`/`permanently_failed`	`PANCAKE_EVENT_NOT_REPLAYABLE`	200 GraphQL	Toast warning vàng	"Sự kiện không thể phát lại. Trạng thái: {status}."
Bulk replay vượt batch limit	`PANCAKE_REPLAY_RATE_LIMIT`	200 GraphQL	Toast warning + progress	"Đã phát lại {X}/{Y} sự kiện. Tạm dừng do giới hạn."
Pancake REST timeout	`PANCAKE_REST_TIMEOUT`	200 GraphQL	Banner connection panel	"Pancake không phản hồi. Đang dùng dữ liệu cache."
Pancake REST 401 (api_key sai)	`PANCAKE_REST_UNAUTHORIZED`	200 GraphQL	Banner đỏ + alert	"API key Pancake không hợp lệ. Liên hệ admin."
Pancake REST 429 (rate limit)	`PANCAKE_REST_RATE_LIMITED`	200 GraphQL	Banner vàng	"Pancake giới hạn truy cập. Sẽ tự thử lại."
HMAC fail (PD-001)	`PANCAKE_HMAC_INVALID`	200 (fail-open)	(no FE, audit log only)	(Log only)
Webhook token sai	`PANCAKE_TOKEN_INVALID`	200 (fail-open)	(no FE)	(Log + alert ops nếu > 10/min)
JSON parse error	`PANCAKE_PARSE_ERROR`	200 (fail-open)	(no FE)	(Log + alert nếu rate > 5%)
Circuit breaker open	`PANCAKE_CIRCUIT_OPEN`	200 GraphQL	Banner connection	"Tạm dừng gọi Pancake API. Tự thử lại sau {sec}s."
Source routing không tồn tại	`PANCAKE_SOURCE_NOT_FOUND`	200 (continued, branch=NULL)	(no FE)	(Log INFO — Loose mode)

C6) Component frontend

Cấu trúc file

diva-admin/src/modules/settings/pages/
├── PancakeCrmSetting.tsx                ← NEW container 4 tabs (XDetailLayout)
└── pancake/
    ├── PancakeConnection.tsx            ← NEW (SCR-01) — Tab 1 Connection
    ├── PancakeSourceRouting.tsx         ← NEW (SCR-02) — Tab 2 Source → Branch
    ├── PancakeAuditDlq.tsx              ← NEW (SCR-03) — Tab 3 Audit + DLQ replay
    └── PancakeHealth.tsx                ← NEW (SCR-04) — Tab 4 Health monitor

diva-admin/src/modules/settings/graphql/
└── pancake.graphql                      ← NEW (queries + mutations)

diva-admin/src/modules/crm/
├── types.ts                              ← EDIT (line 146-164): ADD TICKET_SOURCE_PANCAKE
├── i18n/vi.ts                            ← EDIT (line 130-137): ADD key ticket_source_pancake
├── i18n/en.ts                            ← EDIT: ADD key (if file exists)
└── pages/Tickets.tsx                     ← EDIT (line 128-149): ADD sourceDescriptions entry

diva-admin/src/router/routes.ts           ← EDIT: ADD 5 routes (parent + 4 child)
diva-admin/src/layouts/navigation/...     ← EDIT: ADD menu entry Settings → "Tích hợp Pancake"
diva-admin/src/shared/permissions.ts      ← EDIT: ADD `replay_dlq` enum value (if not exists)

Logic lõi (pseudocode TypeScript)

PancakeAuditDlq.tsx — Replay flow:

typescript

import { useMutation, useQuery } from 'villus';
import { useGlobalStore } from '@/stores/useGlobalStore';
import {
  ListPancakeWebhookEventsDocument,
  PancakeReplayEventDocument,
  PancakeReplayEventBulkDocument,
} from '@/modules/settings/graphql/pancake';

export default defineComponent({
  setup() {
    const globalStore = useGlobalStore();
    const canReplay = computed(() =>
      globalStore.hasPermission('pancake_crm_integration', 'replay_dlq')
    );

    const filter = ref({ status: ['dead_letter'], is_test: false });
    const { data, refetch } = useQuery({
      query: ListPancakeWebhookEventsDocument,
      variables: () => ({ where: buildWhere(filter.value), limit: 50, offset: 0 }),
    });

    const { execute: replayOne } = useMutation(PancakeReplayEventDocument);
    const { execute: replayBulk } = useMutation(PancakeReplayEventBulkDocument);

    const replayEvent = async (eventId: string) => {
      const result = await replayOne({ event_id: eventId });
      if (result.error || !result.data?.pancake_replay_event?.success) {
        notify.error(result.data?.pancake_replay_event?.message ?? 'Lỗi không xác định');
        return;
      }
      notify.success('Đã đẩy lại sự kiện vào hàng xử lý.');
      refetch();
    };

    const bulkReplaySelected = async (selectedIds: string[]) => {
      if (selectedIds.length === 0) return;
      if (selectedIds.length > 100) {
        notify.warning('Tối đa 100 sự kiện mỗi lần.');
        return;
      }
      const result = await replayBulk({ event_ids: selectedIds });
      const out = result.data?.pancake_replay_event_bulk;
      if (out?.success) {
        notify.success(`Đã phát lại ${out.replayed_count}/${selectedIds.length} sự kiện.`);
      } else {
        notify.error(out?.message ?? 'Lỗi bulk replay');
      }
      refetch();
    };

    return { canReplay, data, replayEvent, bulkReplaySelected };
  },
});

PancakeConnection.tsx — Test connection action:

typescript

const testConnection = async () => {
  loading.value = true;
  const result = await urqlClient.mutation(PancakeTestConnectionDocument, {});
  loading.value = false;
  if (result.error) {
    notify.error(result.error.message);
    return;
  }
  const out = result.data?.pancake_test_connection;
  if (out?.success) {
    notify.success(`Kết nối thành công. Phát hiện ${out.sources_count} nguồn.`);
    detectedSources.value = out.sources;
  } else {
    banner.value = { type: 'error', message: out?.message };
  }
};

PancakeSourceRouting.tsx — Autosave per row:

typescript

import { debounce } from 'lodash-es';

const saveBranch = debounce(async (sourceId: string, branchId: string | null) => {
  await urqlClient.mutation(UpdatePancakeSourceRoutingDocument, {
    pancake_source_id: sourceId,
    set: { diva_branch_id: branchId },
  });
  notify.success('Đã lưu.', { duration: 1500 });
}, 500);

const toggleActive = async (sourceId: string, isActive: boolean) => {
  await urqlClient.mutation(UpdatePancakeSourceRoutingDocument, {
    pancake_source_id: sourceId,
    set: { is_active: isActive },
  });
};

types.ts delta — slot 9:

typescript

// diva-admin/src/modules/crm/types.ts (line 146-164 edit)
export const TICKET_SOURCE_1 = 'ticket_source_1';
// ... (1-8 existing)
export const TICKET_SOURCE_8 = 'ticket_source_8';
export const TICKET_SOURCE_PANCAKE = 'ticket_source_pancake'; // NEW

export const TicketSources = [
  TICKET_SOURCE_1,
  // ... 2-8
  TICKET_SOURCE_8,
  TICKET_SOURCE_PANCAKE, // append slot 9
] as const;

i18n/vi.ts delta:

typescript

// diva-admin/src/modules/crm/i18n/vi.ts (line 130-137 edit)
ticket_source_1: 'Nguồn 1',
// ... 2-8
ticket_source_8: 'Nguồn 8',
ticket_source_pancake: 'Pancake CRM', // NEW

Tickets.tsx delta — sourceDescriptions:

typescript

// diva-admin/src/modules/crm/pages/Tickets.tsx (line 128-149 edit)
const sourceDescriptions: Record<string, string> = {
  [TICKET_SOURCE_1]: 'Tạo bởi các nhóm sau: ...',
  // ... 2-8
  [TICKET_SOURCE_8]: 'Khách hàng có sinh nhật hôm nay & thực thu tối thiểu 1tr',
  [TICKET_SOURCE_PANCAKE]:
    'Lead từ Pancake CRM (40+ kênh: FB, Zalo, TikTok, Shopee...). Auto-assign telesale theo chi nhánh.',
};

routes.ts delta (5 routes):

typescript

{
  path: 'settings/pancake',
  component: () => import('@/modules/settings/pages/PancakeCrmSetting.tsx'),
  meta: { permission: { module: 'pancake_crm_integration', action: 'access' } },
  children: [
    { path: '', redirect: 'connection' },
    { path: 'connection', component: () => import('@/modules/settings/pages/pancake/PancakeConnection.tsx'),
      meta: { permission: { module: 'pancake_crm_integration', action: 'access' } } },
    { path: 'source-routing', component: () => import('@/modules/settings/pages/pancake/PancakeSourceRouting.tsx'),
      meta: { permission: { module: 'pancake_crm_integration', action: 'access' } } },
    { path: 'audit', component: () => import('@/modules/settings/pages/pancake/PancakeAuditDlq.tsx'),
      meta: { permission: { module: 'pancake_crm_integration', action: 'access' } } },
    { path: 'health', component: () => import('@/modules/settings/pages/pancake/PancakeHealth.tsx'),
      meta: { permission: { module: 'pancake_crm_integration', action: 'access' } } },
  ],
},

C7) Chiến lược migration (Migration Strategy)

Sequence chuẩn: timestamps consecutive từ latest current 1777870069927 (verified). Mỗi migration là cặp up.sql + down.sql. Idempotent guards bắt buộc (IF NOT EXISTS, ON CONFLICT DO NOTHING, anchor pre-check theo CLAUDE.md pitfalls #4).

Migration sequence

#	Timestamp	Folder name	Up SQL nội dung	Reversible?
1	`1777870069928`	`alter_table_public_account_add_pancake_columns`	ADD `normalized_phone TEXT`, `pancake_metadata JSONB DEFAULT '{}'`, index partial	✅ Yes
2	`1777870069929`	`backfill_normalized_phone`	UPDATE `account` SET `normalized_phone` từ `phone_code + phone_number` (batch 10k)	✅ Yes (UPDATE SET NULL)
3	`1777870069930`	`alter_table_public_contact_book_add_primary_phone_fk_unique`	ALTER `contact_book` ADD `primary_phone` + FK NOT VALID + VALIDATE + UNIQUE (DEC-006)	✅ Yes
4	`1777870069931`	`create_pancake_tables`	CREATE 4 tables `pancake_webhook_event/connection/source_routing/outage_state` + history + indexes + triggers + helper function `pancake_advisory_lock_account`	✅ Yes
5	`1777870069932`	`insert_ticket_source_pancake`	INSERT `crm_master_data` slot 9 (DEC-017)	✅ Yes (DELETE)
6	`1777870069933`	`insert_pancake_module_permission`	INSERT `module_permission_action` rows × 6 + default seed cho `Admin` role	✅ Yes
7	`1777870069934`	`insert_pancake_notification_templates`	INSERT 3 row `notification_template` (assigned, unmapped_branch, outage)	✅ Yes
8	`1777870069935`	`update_app_setting_pancake_integration`	UPDATE `app_setting` JSON ADD key `pancake_integration: { enabled: false, kill_switch: false, ip_whitelist: [] }`	✅ Yes

Migration template — Idempotent + Pre-check anchor (CLAUDE.md pattern)

sql

-- Example for migration #8 — update_app_setting_pancake_integration/up.sql
DO $$
DECLARE
  target_missing INT;
  affected INT;
BEGIN
  -- 1. Count rows needing update (filter chặt theo runtime usage)
  SELECT COUNT(*) INTO target_missing
  FROM "public"."app_setting"
  WHERE id = 1
    AND NOT (app_settings ? 'pancake_integration');

  -- 2. Idempotent guard
  IF target_missing = 0 THEN
    RAISE NOTICE 'pancake_integration key already present, skipping';
    RETURN;
  END IF;

  -- 3. Pre-check that app_setting row exists (anchor)
  IF NOT EXISTS (SELECT 1 FROM "public"."app_setting" WHERE id = 1) THEN
    RAISE EXCEPTION 'app_setting row id=1 not found — base data missing';
  END IF;

  -- 4. UPDATE with anchor in WHERE
  UPDATE "public"."app_setting"
  SET "app_settings" = jsonb_set(
    "app_settings",
    '{pancake_integration}',
    '{"enabled": false, "kill_switch": false, "ip_whitelist": []}'::jsonb,
    true
  )
  WHERE id = 1
    AND NOT (app_settings ? 'pancake_integration');

  GET DIAGNOSTICS affected = ROW_COUNT;

  -- 5. Defense-in-depth assert
  IF affected <> target_missing THEN
    RAISE EXCEPTION 'pancake_integration update: expected %, got %', target_missing, affected;
  END IF;
END $$;

Thứ tự deploy

1. DB migrations (in order ts 1-8) — apply via Hasura migrate up
2. Hasura metadata apply (cron_triggers.yaml + actions.yaml + table YAMLs)
3. Backend release (services/webhook + crm-api + notification-v2-api) — rolling deploy
4. FE codegen (urql generated types from new GraphQL schema)
5. FE release (diva-admin)
6. Enable feature flag: app_setting.pancake_integration.enabled = false  (still off after deploy)
7. Pilot rollout per W1-W4 (see go-live-checklist E1 Gates)

Backfill `normalized_phone` capacity

Dataset: ~1M account rows (PRD ASM-006)
Strategy: Migration #2 batch 10k row/iteration với LIMIT subquery
Estimate: 1M × 0.5ms = ~8.3 phút total
Mitigation lock contention: Run during low-traffic window (02:00-06:00 ICT)
Failure recovery: Re-run idempotent (WHERE normalized_phone IS NULL)

Rollback strategy

Mỗi migration có down.sql reversible (DROP COLUMN / DROP CONSTRAINT / DELETE)
Feature flag kill switch (app_setting.pancake_integration.enabled=false) là rollback soft không cần migration revert — webhook trả 200 skipped_kill_switch
Hard rollback (DROP bảng): chỉ áp dụng nếu data corruption, không cần cho rollback feature

C8) Bảo mật (Security)

Ma trận Dynamic Permission

Module	Action	Default Admin	Default Manager	Default Telesale	Default POS-only
`pancake_crm_integration`	`access`	✓	✗	✗	✗
`pancake_crm_integration`	`create`	✓	✗	✗	✗
`pancake_crm_integration`	`update`	✓	✗	✗	✗
`pancake_crm_integration`	`delete`	✓	✗	✗	✗
`pancake_crm_integration`	`view_all`	✓	✗	✗	✗
`pancake_crm_integration`	`replay_dlq`	✓	✗	✗	✗

Default Admin only. Có thể Dynamic Permission UI override sau MVP-1.

Migration permission / metadata delta

Migration #6 (insert_pancake_module_permission):

sql

-- INSERT module + 6 actions
INSERT INTO "public"."module_permission_action" ("module_id", "action_id", "portal", "branch_mode")
VALUES
  ('pancake_crm_integration', 'access', 'admin', 'system-wide'),
  ('pancake_crm_integration', 'create', 'admin', 'system-wide'),
  ('pancake_crm_integration', 'update', 'admin', 'system-wide'),
  ('pancake_crm_integration', 'delete', 'admin', 'system-wide'),
  ('pancake_crm_integration', 'view_all', 'admin', 'system-wide'),
  ('pancake_crm_integration', 'replay_dlq', 'admin', 'system-wide')
ON CONFLICT ("module_id", "action_id") DO NOTHING;

-- Seed default role-permission for Admin role (assuming role_permission table)
INSERT INTO "public"."role_permission" ("role_id", "module_id", "action_id")
SELECT 'Admin', 'pancake_crm_integration', a.action_id
FROM (VALUES ('access'), ('create'), ('update'), ('delete'), ('view_all'), ('replay_dlq')) AS a(action_id)
ON CONFLICT DO NOTHING;

Backend enforcement

Layer	Cơ chế
Webhook public endpoint	URL token (random 32-byte hex) + IP whitelist + HMAC (PD-001 TBD W4). Rate limit reverse proxy 1000 req/s/IP. NO Hasura role check (public).
Hasura action `pancake_replay_event`	Permission `admin` role in actions.yaml + handler double-check `session_variables['x-hasura-role']='admin'`
Hasura table query/mutation	Permission Admin only (xem C5 Hasura metadata) — Manager/Telesale/POS cannot SELECT bảng pancake_*
`api_key_encrypted` storage	pgcrypto encrypt at rest. Decrypt chỉ trong handler khi gọi REST API. Audit log mọi access.
Audit log toggle kill switch	Mọi UPDATE `pancake_connection.status` hoặc `app_setting.pancake_integration` → log vào audit (existing pattern Diva)

Hasura Row-Level Security

Bảng pancake_* không cần row-level filter (Admin scope toàn hệ thống)
Bảng ticket Pancake-sourced: telesale chỉ thấy ticket được assign (existing ticket_management.access filter, không cần delta)

Sensitive data redaction

Tab 3 detail drawer (Audit): Mặc định hiện full payload cho Admin. Non-admin không có quyền access → không vào được Tab 3.
api_key_encrypted: Không render trong FE response (Hasura permission exclude column). Mask ••••XXXX khi display ở Tab 1.
Phone number ở audit: Show full cho Admin. Future Dynamic Permission có thể redact last-4-digits cho non-admin (defer MVP-2).

Rate limiting

Surface	Limit	Action khi vượt
Webhook receiver public	1000 req/s/IP (reverse proxy Nginx)	Drop request (NOT trả 200 — let Pancake retry)
Hasura action `pancake_replay_event_bulk`	100 events/call, sequential 5/giây internal	Return `PANCAKE_REPLAY_RATE_LIMIT`
Pancake REST API call (Cron 3)	gobreaker 5 fail / 60s → open, half-open 30s	Circuit open, retry hour sau

C9) Phi chức năng (NFR)

Hiệu năng + index

Loại	Target	Cách đo	Action nếu fail
Latency webhook receiver	p95 < 1s, p99 < 2s	Prometheus histogram `pancake_webhook_latency_seconds`	Scale crm-api horizontally; optimize INSERT (no FK check at ingest)
Throughput peak sustained	100 req/s	Load test K6/JMeter pre-W3	Add DB read replica; tune Postgres connection pool
End-to-end p95 (webhook → ticket assigned)	≤ 30s	FORMULA-001 query daily	Optimize STEP 9 (round-robin query), batch DB reads
DB query latency p95	< 100ms	pg_stat_statements	ADD index per slow query
FE Settings UI load	< 2s	Lighthouse audit	Code-split per tab, lazy load
Cron 7 reconciliation duration	< 30 phút	Cron metrics + log timing	Parallelize REST page fetch; optimize compare SQL
DLQ bulk replay (100 events)	< 5 phút	Action timing	Reduce batch size to 50; async dispatch

Recommended indexes (đã include ở C4 schema):

sql

-- pancake_webhook_event (5 partial indexes)
idx_pancake_webhook_event_status_created (status, created_at DESC) WHERE status = 'processed'
idx_pancake_webhook_event_status_received (status, created_at DESC) WHERE status IN ('received', 'processing', 'dead_letter')
idx_pancake_webhook_event_record_id (record_id) WHERE record_id IS NOT NULL
idx_pancake_webhook_event_resolved_account (resolved_account_id) WHERE resolved_account_id IS NOT NULL
idx_pancake_webhook_event_is_test (is_test, created_at DESC) WHERE is_test = true

-- account (1 partial)
account_normalized_phone_idx (normalized_phone) WHERE normalized_phone IS NOT NULL

-- ticket (cover SLA query)
idx_ticket_source_first_response (source_id, first_response_at) WHERE source_id = 'ticket_source_pancake'

-- pancake_source_routing
idx_pancake_source_routing_active (is_active, diva_branch_id) WHERE disabled = false

-- pancake_outage_history
idx_pancake_outage_history_started (outage_started_at DESC)

Capacity

Volume target: 100k events/tháng = ~3.3k/ngày peak (PRD ASM-006)
Storage estimate:
- Raw payload avg 5 KB × 100k = 500 MB/tháng
- 6 tháng retention = 3 GB
- Index overhead ~30% = 4 GB total
Archive strategy: Cron pancake_archive_old_events (post-MVP-1) — move events created_at < NOW()-180d AND status='processed' to pancake_webhook_event_archive table
Memory webhook handler: < 256 MB per pod (Prometheus container_memory_usage_bytes)
Connection pool: Tune Postgres max_connections to handle peak 100 req/s × 50ms = 5 concurrent + slack 20 = 25 conn dedicated webhook service

NFR table

Loại	Target	Cách đo	Action nếu fail
Latency webhook receiver	p95 < 1s, p99 < 2s	Log timing in handler	Scale crm-api horizontally
Throughput peak	100 req/s sustained	Load test K6/JMeter	Add DB read replica
DB capacity	100k events/tháng	Storage calc + index size	Archive old events sau 6 tháng
Pancake suspension count	0 lần/quarter	`pancake_connection.status` history	Alert + ops on-call
Memory webhook	< 256 MB per pod	Prometheus	Tune Go runtime GC
Cron 7 duration	< 30 phút	Cron metrics	Optimize query, parallel
DLQ bulk replay	< 5 phút cho 100 events	Action timing	Reduce batch size
FE bundle size	< 100 KB delta (4 tab + GraphQL)	Webpack bundle analyzer	Code split per tab
Migration backfill duration	< 15 phút for 1M rows	Migration log	Run as external Go job

C10) Quan sát vận hành (Observability)

Metrics (Prometheus)

# Counter — total requests
pancake_webhook_requests_total{status, source_ip} counter
pancake_event_processed_total{result, branch_id} counter
pancake_dlq_replay_total{outcome} counter
pancake_rest_api_calls_total{endpoint, status} counter

# Histogram — latency
pancake_webhook_latency_seconds histogram (buckets: 0.1, 0.5, 1, 2, 5)
pancake_event_process_duration_seconds{status} histogram (buckets: 1, 5, 10, 30, 60)
pancake_rest_api_duration_seconds{endpoint} histogram

# Gauge — current state
pancake_circuit_breaker_state{name="pancake_rest"} gauge (0=closed, 1=half-open, 2=open)
pancake_outage_active gauge (1 if outage_started)
pancake_dlq_count gauge (current count of status='dead_letter')
pancake_polling_interval_seconds gauge (current Cron 6 interval)
pancake_reconciliation_missed_events gauge (last Cron 7 result)
pancake_connection_status{workspace} gauge (1=active, 0=others)

Logs (structured, zerolog)

Level	Tình huống	Fields bắt buộc
ERROR	`status='dead_letter'`, `status='auth_failed' > 10/phút`, `outage_started`, Pancake REST 401	`pancake_event_id`, `record_id`, `error_code`, `error_message`, `retry_count`
WARN	`circuit_breaker_open`, `rate_limit_429`, parse_error rate > 5%, reconciliation_missed > 5/ngày	`pancake_event_id`, `source_ip`, `event_type`
INFO	processed, replay success, sync source completed, cron run start/end	`pancake_event_id`, `account_id`, `branch_id`, `status`, `duration_ms`
DEBUG	STEP entry/exit, advisory lock acquired, smart update decision	`pancake_event_id`, `step`

Common fields cho mọi Pancake log:

service: webhook | crm-api
pancake_event_id: UUID
record_id: Pancake record ID (nullable)
account_id: Diva account UUID
branch_id: Diva branch UUID (nullable in Loose mode)
status: lifecycle state
error_code: from C5 error table

Alerts (alertmanager)

Severity	Rule	Notification target
Critical	`pancake_webhook_error_rate > 10% for 1 phút`	Ops on-call (PagerDuty/Telegram)
Critical	`pancake_outage_active = 1 for 5 phút`	Ops on-call
Critical	`pancake_connection_status = 0 AND status='suspended_by_pancake'`	Ops on-call + Tech Lead
Warning	`pancake_event_success_rate < 99% for 10 phút`	Ops Telegram
Warning	`pancake_dlq_count > 50`	Admin email + Telegram
Warning	`pancake_reconciliation_missed > 5 events/ngày`	Admin email
Warning	`pancake_circuit_breaker_state = 2 for 5 phút`	Tech Lead
Info	`pancake_polling_interval_seconds > 60` (degraded fallback active)	Dashboard only

Dashboard / Runbook (L+ khuyến nghị)

Grafana board "Pancake CRM Integration" (post-MVP-1):

Section 1: KPI 5 metric — Latency p95 / SLA p95 / Auto-assign rate / Event success rate / Zero-miss
Section 2: Volume — Events/giờ per source / per branch (heatmap)
Section 3: Outage tracking — Outage history timeline + MTTR per incident
Section 4: DLQ queue — Current count + replay rate / day
Section 5: REST API health — Circuit breaker state, latency, error rate per endpoint
Section 6: Audit funnel — ingested → received → processed (drop-off per step)

Runbook:

RUNBOOK-001: Pancake suspended webhook recovery (manual re-enable steps)
RUNBOOK-002: DLQ event triage (admin replay vs permanently_failed)
RUNBOOK-003: Outage detected — verify Pancake side vs Diva side
RUNBOOK-004: Reconciliation reported missed events (Cron 7) — investigate root cause

C11) Danh sách việc triển khai (Tasks)

Tổng: ~37 dev-days breakdown. Parallel: 2 BE + 1 FE + 1 QA. Calendar: 5.5-6 tuần (W1 pilot launch + W2-W3 iterate + W4 production).

Giai đoạn 1 — Foundation (W1, ~10 dev-days)

#	Hạng mục	Effort	Owner	Phụ thuộc
1	Schema 4 bảng pancake_* + ALTER `account` + ALTER `contact_book` + backfill normalized_phone + INSERT master data	1.5d	BE-1	—
2	`pkg/pancake/` REST client + phone_normalize + advisory_lock helper	1.5d	BE-1	—
3	`services/webhook/handler/pancake.go` (handler + verify + dedup + fail-open)	2d	BE-1	#1
4	`services/crm-api/event/event_pancake_process_record.go` (15 STEP)	3.5d	BE-2	#1, #2
5	Refactor `GetTicketUpdates` thành public callable helper	1d	BE-2	—
6	F18 FE delta (types.ts + vi.ts + sourceDescriptions + all surfaces grep audit)	1d	FE	#1
7	Hasura event trigger config + 4 metadata YAML + permissions	0.5d	BE-1	#1
8	Confirm `consent_data.marketing` key (PD-003) + build `customer_consent` Go struct	0.75d	BE-1 + PO	—

Giai đoạn 2 — Recovery + UI (W2-W3, ~16 dev-days)

#	Hạng mục	Effort	Owner	Phụ thuộc
9	Cron 3 `pancake_source_sync_hourly` + Pancake REST client integration	1d	BE-1	#2
10	Cron 4 `pancake_outage_detect` (state machine)	1d	BE-1	#1
11	Cron 6 `pancake_adaptive_polling` fallback	3d	BE-1	#10
12	Cron 7 `pancake_daily_reconciliation` (25h window)	2d	BE-2	#2
13	Notification integration (3 template seed + handler call)	1.5d	BE-2	#1, #4
14	Circuit breaker `sony/gobreaker` REST (DEC-019) + tests	1d	BE-1	#2
15	Settings UI Tab 1 Connection (form, test connection action)	1.5d	FE	#7
16	Settings UI Tab 2 Source Routing (autosave per-row, table inline-edit)	1.5d	FE	#7
17	Settings UI Tab 3 Audit + DLQ (list + drawer + replay action)	1.5d	FE	#18
18	Hasura action `pancake_replay_event` + bulk replay	1d	BE-2	#1
19	Backfill custom window endpoint + UI modal (post-outage manual replay range)	1.5d	BE-2 + FE	#4

Giai đoạn 3 — Production hardening (W4, ~11 dev-days)

#	Hạng mục	Effort	Owner	Phụ thuộc
20	Cron `pancake_dlq_retry` (auto retry retry_count < 3)	0.5d	BE-2	#4
21	Cron `pancake_test_event_cleanup` (soft-delete is_test=true sau 24h)	0.5d	BE-2	#1
22	Health monitoring + Prometheus metrics + alertmanager rules	2.5d	BE-1 + DevOps	#4, #11
23	Settings UI Tab 4 Health (metrics card + outage history + alert config)	0.5d	FE	#22
24	HMAC signature verify (PD-001 resolved) + IP whitelist enforcement	1d	BE-1	PD-001/PD-002
25	Test (unit + integration + E2E + load + chaos outage + duplicate stress)	6d	QA + Dev	All
26	Pilot W1 launch + bug fix + iterate	2d	All	All
27	Pilot W2-W4 iterate (chaos test fake outage + duplicate retry)	4d	All	#26

Sơ đồ phụ thuộc

W1 ─────────────────────────────────────────────
#1 Schema ──┬─→ #3 webhook handler
            ├─→ #4 event handler ──┬─→ #18 replay action
            ├─→ #7 Hasura YAML     ├─→ #13 notification
            └─→ #6 FE delta        └─→ #20 DLQ cron
#2 pkg/pancake ──┬─→ #3, #4
                 ├─→ #9 Cron 3
                 ├─→ #12 Cron 7
                 └─→ #14 circuit breaker
#5 Refactor GetTicketUpdates ──→ #4 STEP 9
#8 PD-003 confirm ──→ #4 STEP 7

W2-W3 ──────────────────────────────────────────
#9 Cron 3 ──→ #15 Tab 1 (test connection)
#10 Cron 4 ──→ #11 Cron 6 ──→ #22 metrics
#12 Cron 7
#17 Tab 3 audit ──→ #19 backfill custom window
#18 Replay action ──→ #17 Tab 3

W4 ──────────────────────────────────────────────
#22 metrics ──→ #23 Tab 4 health
#24 HMAC + IP (PD-001/002 resolved)
#25 Test (concurrent với pilot)
#26 W1 pilot (live!) → #27 W2-W4 iterate

C12) Truy vết (Traceability)

Matrix FR → File/Component → Test Case

FR	File / handler / component	API / endpoint	Test Case (xem `qa-test-plan.md`)
FR-001 webhook receiver	`services/webhook/handler/pancake.go`	`POST /api/pancake/record/{token}`	TC-001-001..008 (auth, IP, parse, dup, kill, source disabled, happy, load 100 req/s)
FR-002 persist raw event	`pkg/store/pancake_webhook_event.go` (INSERT)	(Direct DB)	TC-002-001..003 (audit fields, raw payload, headers)
FR-003 idempotency 3-tuple	UNIQUE constraint `pancake_webhook_event_idempotency_unique`	INSERT ON CONFLICT	TC-003-001..002 (duplicate same → skipped, retry update last_received_at)
FR-004 async processor	`services/crm-api/event/event_pancake_process_record.go`	Hasura trigger `pancake_webhook_event_status_update`	TC-004-001..010 (15 STEP coverage, panic recovery, dead_letter)
FR-005 phone normalize + match	`pkg/pancake/phone_normalize.go` + STEP 2, 5	(helper)	TC-005-001..005 (E.164 +84, fallback raw, edge format, NULL phone)
FR-006 advisory lock	`pkg/pancake/advisory_lock.go` + STEP 3 SQL `pancake_advisory_lock_account`	(Transaction-scoped)	TC-006-001 (concurrent 10 webhook cùng phone → 1 account)
FR-007 auto-create contact	STEP 5b `event_pancake_process_record.go`	INSERT `contact_book`	TC-007-001..003 (new account → contact created, existing account → skip, race serialized)
FR-008 round-robin assign	`services/crm-api/scheduler/distribute_ticket.go:157` (refactored public)	Helper call	TC-008-001..005 (round-robin, Loose mode NULL branch, no-active-telesale → NULL)
FR-009 smart update	STEP 8 logic	(in-handler)	TC-009-001..006 (phone change/source new/VIP tag new → ticket; same payload → no ticket)
FR-010 create ticket	STEP 10	INSERT `ticket`	TC-010-001..004 (source_id, due_date, target_id telesales, input_note populated)
FR-011 notification	STEP 12 + 3 template	Hasura action `sendNotifications`	TC-011-001..003 (telesale push, admin unmapped, ops outage)
FR-012 Settings UI 4 tabs	`diva-admin/src/modules/settings/pages/pancake/*.tsx`	GraphQL queries	TC-012-001..020 (Tab 1-4 render, autosave, test connection, replay, health metrics)
FR-013 REST consumption	`pkg/pancake/client.go` + Cron 3 + `sony/gobreaker`	Pancake REST `GET /workspaces/{ws}/sources`	TC-013-001..005 (happy, timeout, 401, 429, circuit breaker open)
FR-014 outage recovery	Cron 4/6/7 + DLQ replay action	Schedulers + action `pancake_replay_event`	TC-014-001..015 (chaos test fake outage, polling adaptive, reconciliation inject)
FR-015 feature flag	`app_setting` + `pancake_connection.status` + `pancake_source_routing.is_active`	(config)	TC-015-001..003 (3 mức toggle independent)
FR-016 test mode	`is_test=true` flag + Cron cleanup	(in-handler + cron)	TC-016-001..003 (flag set, KPI exclude, cleanup soft-delete 24h)
FR-017 opt-out	STEP 7	(in-handler)	TC-017-001..002 (consent.marketing=false → skipped_opt_out + account updated)
FR-018 FE delta	`diva-admin/src/modules/crm/types.ts` + `i18n/vi.ts` + `sourceDescriptions`	(FE delta)	TC-018-001..002 (dropdown render slot 9, all surfaces auto-render, no hardcoded slot 1..8 in report module)

Mapping quyết định (DEC) → triển khai → cách kiểm chứng

DEC	Triển khai	Cách kiểm chứng
DEC-001 Inbound 1 chiều MVP-1	KHÔNG có code outbound, KHÔNG endpoint POST records	Code review + grep `client.PostRecord` returns zero
DEC-002 Split webhook + crm-api	`services/webhook/handler/pancake.go` + `services/crm-api/event/event_pancake_process_record.go`	TC-001 (webhook 200<1s) + TC-004 (handler async qua Hasura trigger)
DEC-003 Fail-open 200	Webhook handler always `c.JSON(200, ...)` mọi case	TC-001-001 (auth fail 200), TC-001-002 (IP block 200), TC-001-003 (parse error 200)
DEC-004 Idempotency 3-tuple	DB UNIQUE constraint `pancake_webhook_event_idempotency_unique (record_id, modified_on, payload_hash)`	TC-003-001 (duplicate skipped), TC-003-002 (UPDATE last_received_at on dup)
DEC-005 Phone E.164	`pkg/pancake/phone_normalize.go` reuse `libphonenumber`	TC-005-001..005 (parse VN +84, fallback, edge)
DEC-006 ALTER `contact_book`	Migration ts 1777870069930 (ADD primary_phone + FK + UNIQUE)	TC-007-001..003 (auto-create contact, FK enforce, UNIQUE prevent dup) + migration test up/down
DEC-007 4 bảng `disabled BOOLEAN`	Migration ts 1777870069931 (audit fields với `disabled`, no `deleted_at`)	Schema verify test: `\d pancake_webhook_event` → has `disabled`, no `deleted_at`
DEC-008 VIP tag NAME match	STEP 8 `pancake.MatchVIPTags` case-insensitive text compare	TC-009-003 (VIP tag new "Hot Lead" → ticket; "hot lead" lowercase → match)
DEC-009 Test mode 24h soft-delete	`is_test BOOLEAN` flag + Cron `pancake_test_event_cleanup` 04:00 AM	TC-016-001..003 (flag respect, cleanup 24h boundary)
DEC-010 Loose mode admin push only	STEP 12 logic + template `noti_pancake_unmapped_branch`	TC-011-002 (branch=NULL → push admin, no email/SMS)
DEC-011 Smart update Q2.b	STEP 8 logic compare phone/source/VIP-tag	TC-009-001..006 (full coverage)
DEC-012 REUSE `GetTicketUpdates`	Refactor public + STEP 9 call	TC-008-001..005 + cron `distribute_ticket` regression test
DEC-013 1-tier round-robin	STEP 9 logic single-tier	TC-008-002 (no-active-telesale → NULL, no fallback to other branch)
DEC-014 Opt-out via `customer_consent`	STEP 7 query `consent_data->>'marketing'`	TC-017-001..002 (false → skip, true → process, NULL → process)
DEC-015 4-layer recovery	Cron 4/6/7 + DLQ replay	TC-014-001..015 (chaos test outage simulation, reconciliation inject, DLQ replay)
DEC-016 Settings UI 4 tabs Admin only	FE permission `pancake_crm_integration.access` + Hasura permission Admin	TC-012-018 (non-admin no menu, gõ URL → redirect)
DEC-017 Slot 9 `ticket_source_pancake`	Migration #5 + FE delta 3 file	TC-010-001 (INSERT ticket with source_id) + TC-018-001 (dropdown render)
DEC-018 REST chỉ `/sources`	Cron 3 only call `/workspaces/{ws}/sources`	TC-013-001 + grep `client.Get` returns only `/sources` endpoint
DEC-019 Circuit breaker `sony/gobreaker`	`pkg/pancake/client.go` wrap REST call	TC-013-003..005 (open after 5 fail, half-open, retry)
DEC-020 Advisory lock per phone	STEP 3 `pancake_advisory_lock_account(normalized_phone)`	TC-006-001 (concurrent 10 webhook → 1 account, no duplicate)
DEC-021 Feature flag 3 mức	`app_setting` + `pancake_connection.status` + `pancake_source_routing.is_active`	TC-015-001..003 (kill switch off → skipped_kill_switch; connection paused → skipped; source disabled → skipped_source_disabled)
DEC-022 Pilot 4 tuần	Feature flag control + monitoring metrics	go-live-checklist E1 W1-W4 gates + Grafana board section 2
DEC-023 KPI 5 metric	A8 metrics queries (FORMULA-001..005)	Grafana board + daily report cron
DEC-024 3 notification template	Migration #7 INSERT + STEP 12 handler call	TC-011-001..003 (each template renders + sends)
DEC-025 25h reconciliation window	Cron 7 SQL window NOW()-25h	TC-014-008..010 (missed event detected, injected, no duplicate)

Hoàn thành Dev Spec L. Mọi business rationale → PRD; mọi UI detail → ui-spec; mọi test detail → qa-test-plan. Mọi schema delta + flow pseudocode + migration sequence + DEC traceability đã canonical ở file này.

C13) Pass 1 Resolutions (Phase 5.2 Multi-Perspective Review fixes)

Date: 15/05/2026 | Trigger: Tech Lead BLOCK verdict + FE codebase verify. Section này OVERRIDE conflict với C1-C12 gốc. Bao gồm:

C13.1 Architecture refactor — Direct pgxpool (DEC-028)
C13.2 Sweep Hasura YAML conventions CRM_BASE_URL (Tech Lead P0-3)
C13.3 Migration sequence revised (drop primary_phone ADD COLUMN, external backfill job)
C13.4 Idempotency UNIQUE NULL handling (NULLS NOT DISTINCT hoặc partial index)
C13.5 Cron 6 fixed schedule + handler throttle
C13.6 Cron 3 ON CONFLICT DO NOTHING (PO/BA CI-4)
C13.7 F18 = 4 file (thêm CustomerTicketManager.tsx)
C13.8 Component library audit (8 rename + 2 build-new)
C13.9 STATE-001 trigger filter OLD.status check
C13.10 DLQ replay permission update reuse (DEC-027)
C13.11 New tasks C11 delta (+7.25d effort)

C13.1) Architecture refactor — Direct pgxpool (DEC-028)

Spec cũ C5 giả định tx, err := db.BeginTx(ctx, nil) — không khả thi với Hasura layer hiện tại (grep "database/sql" services/ = 0).

Resolution Option A: Dedicated pgxpool.Pool riêng cho Pancake module.

// pkg/pancake/db.go (NEW — ~80 lines)
package pancake

import (
  "context"
  "github.com/jackc/pgx/v5/pgxpool"
)

var pool *pgxpool.Pool

func InitDB(ctx context.Context, dbURL string) error {
  var err error
  pool, err = pgxpool.New(ctx, dbURL) // env: PANCAKE_DB_URL
  if err != nil { return err }
  return pool.Ping(ctx)
}

func Pool() *pgxpool.Pool { return pool }

Update event handler services/crm-api/event/event_pancake_process_record.go:

func EventPancakeProcessRecord(ctx context.Context, payload EventPayload) error {
  // STEP 0: filter — OLD.status check (C13.9)
  old := payload.Event.Data.Old
  if old == nil || old.Status != "ingested" { return nil }
  if payload.Event.Data.New.Status != "received" || payload.Event.Data.New.RecordID == nil { return nil }

  tx, err := pancake.Pool().Begin(ctx)
  if err != nil { return err }
  defer tx.Rollback(ctx)

  // STEP 1.5: re-fetch FOR UPDATE defense
  var event PancakeWebhookEvent
  err = tx.QueryRow(ctx, "SELECT id, status, record_id, raw_payload FROM pancake_webhook_event WHERE id=$1 FOR UPDATE", payload.Event.Data.New.ID).Scan(...)
  if err != nil { return err }
  if event.Status != "received" { return nil }

  _, err = tx.Exec(ctx, "UPDATE pancake_webhook_event SET status='processing', processing_started_at=NOW() WHERE id=$1", event.ID)
  // STEP 2-3: Phone normalize + advisory lock
  normalizedPhone, _ := pancake.NormalizePhone(event.Payload.PhoneNumber, "VN")
  _, err = tx.Exec(ctx, "SELECT pg_advisory_xact_lock(hashtext($1))", "account_phone_"+normalizedPhone)
  // STEP 4-13: business logic via tx.Exec / tx.QueryRow
  // ...
  err = tx.Commit(ctx)
  if err != nil { return err }

  // STEP 15 (POST-COMMIT): notification qua Hasura action (non-atomic — accepted trade-off)
  go func() {
    callHasuraAction(ctx, "sendNotifications", notificationPayload)
  }()
  return nil
}

Deploy config: ADD env PANCAKE_DB_URL cho crm-api service.

C13.2) Sweep Hasura YAML conventions

Pattern cũ	Correct
`/actions/pancake_replay_event`	`/actions` (suffix-less, dispatcher routes theo action name)
`webhook_from_env: CRM_API_BASE_URL`	`webhook: "/events"` (string template)

Verified: grep "CRM_BASE_URL" actions.yaml = 8 occurrences. Sweep áp dụng cho C5 toàn bộ YAML example + cron config + event trigger.

C13.3) Migration sequence REVISED

#	Migration	Change từ Pass 0
2	`1777870069929_backfill_normalized_phone`	REMOVE DO $$ block trong migration. Move backfill ra EXTERNAL Go job chạy off-peak window 02:00-06:00 ICT với batch 10k/tx + `pg_sleep(0.1)` giữa batches. Migration #2 chỉ tạo helper SQL function `pancake_normalize_phone_helper(phone_code, phone_number)`. Owner BE-1, Sprint 1.
3	`1777870069930_alter_table_public_contact_book_*`	DROP `ADD COLUMN primary_phone` (đã exist từ migration `1693889973118`). CHỈ ADD UNIQUE `(account_id)` constraint sau cleanup duplicate. KHÔNG ADD FK tới `account.id` (Hasura `manual_configuration` hiện tại đang map tới `ecommerce_user`). Pre-script: detect duplicate `SELECT account_id, COUNT() FROM contact_book GROUP BY account_id HAVING COUNT() > 1` — manual cleanup nếu có
4	`1777870069931_create_pancake_tables`	UNIQUE 3-tuple dùng `NULLS NOT DISTINCT` (PG 15+) hoặc partial index (PG <15). Xem C13.4
5	`1777870069932_insert_ticket_source_pancake`	Chuẩn bị `down-soft.sql`: `UPDATE crm_master_data SET disabled=true WHERE id='ticket_source_pancake'` thay vì DELETE (rollback safety)
6	`1777870069933_insert_pancake_module_permission`	5 actions (drop `replay_dlq` per DEC-027). Verify `module_permission_action` table schema column names trước Sprint 1

C13.4) Idempotency UNIQUE NULL handling

sql

-- Option A (PG 15+):
CREATE UNIQUE INDEX pancake_webhook_event_idem_unique
  ON pancake_webhook_event (record_id, modified_on, payload_hash)
  NULLS NOT DISTINCT;

-- Option B (PG <15):
CREATE UNIQUE INDEX pancake_webhook_event_idem_unique
  ON pancake_webhook_event (record_id, modified_on, payload_hash) WHERE record_id IS NOT NULL;
CREATE UNIQUE INDEX pancake_webhook_event_idem_null_unique
  ON pancake_webhook_event (payload_hash) WHERE record_id IS NULL;

Verify PG version Diva production trước W1.

C13.5) Cron 6 fixed schedule + handler throttle

yaml

# cron_triggers.yaml
- name: pancake_adaptive_polling
  webhook: "{{CRM_BASE_URL}}/schedulers"
  schedule: "* * * * *"   # every 1 min FIXED
  payload: { action: pancake_adaptive_polling }
  retry_conf: { num_retries: 0, timeout_seconds: 60 }

// scheduler/pancake_adaptive_polling.go
func PancakeAdaptivePolling(ctx context.Context) error {
  state := getOutageState()
  if state.Status != "outage_started" { return nil }
  if time.Since(state.LastPolledAt) < time.Duration(state.CurrentPollingIntervalSec)*time.Second {
    return nil // not yet due
  }
  records, err := pancakeClient.GetRecordsModifiedSince(state.LastEventReceivedAt)
  for _, rec := range records { insertPancakeWebhookEvent(rec, "polled") }
  updateOutageState(time.Now(), increasedInterval(state.CurrentPollingIntervalSec))
  return nil
}

C13.6) Cron 3 ON CONFLICT DO NOTHING (PO/BA CI-4)

sql

INSERT INTO pancake_source_routing (pancake_source_id, pancake_source_name, diva_branch_id, is_active, last_synced_at)
VALUES ($1, $2, NULL, false, NOW())
ON CONFLICT (pancake_source_id) DO UPDATE
  SET pancake_source_name = EXCLUDED.pancake_source_name,
      last_synced_at = NOW();
-- KHÔNG TOUCH diva_branch_id hoặc is_active

C13.7) F18 = 4 file (FE P0-FE-2)

Verified: CustomerTicketManager.tsx:111-158 có bản sao sourceDescriptions. F18 delta đầy đủ:

#	File	Delta
1	`crm/types.ts:146-164`	ADD const + array entry
2	`crm/i18n/vi.ts:129-138`	ADD nested key `crm.label.ticket.ticket_source.ticket_source_pancake`
3	`crm/pages/Tickets.tsx:128-158`	ADD entry sourceDescriptions
4	`user/components/customer/CustomerTicketManager.tsx:111-158`	NEW — ADD entry sourceDescriptions (file thứ 4 đã miss)

P1 refactor: extract sourceDescriptions thành single-source crm/types.ts (defer M2).

C13.8) Component library audit (FE P0-FE-3)

Spec cũ	Actual	Action
`XPassword`	`XInputPassword`	Rename
`XTextarea`	(no) — `QInput type="textarea"`	Quasar primitive
`XBadge`	(no) — `QBadge`	Quasar
`XDrawer`	(no) — `QDrawer` hoặc `QDialog seamless`	Verify
`XSearchInput`	(no) — `XInput` + clear icon	Use existing
`XDateRangePicker`	`XInputDateRange`	Rename
`XJsonViewer`	BUILD NEW (~1d FE) — `<pre> + JSON.stringify(payload, null, 2) + copy + collapse`	NEW `components/core/display/XJsonViewer.tsx`
`XMetricCard`	BUILD NEW (~0.5d FE) — card với label + value + icon trend + tooltip	NEW `components/core/display/XMetricCard.tsx`
`XChart`	REUSE `modules/dashboard/components/{BarChart,LineChart}`	Update C6 ref

C13.9) STATE-001 trigger filter OLD.status check

old := payload.Event.Data.Old
new := payload.Event.Data.New
if old == nil || old.Status != "ingested" { return nil }
if new.Status != "received" || new.RecordID == nil { return nil }
// proceed STEP 1+

Tránh loop khi Hasura watch columns: [status] fires mọi UPDATE status.

C13.10) DLQ replay permission `update` reuse (DEC-027)

yaml

- name: pancake_replay_event
  definition: { handler: "{{CRM_BASE_URL}}/actions" }
  permissions: [{ role: admin }]

if !ctx.HasPermission("pancake_crm_integration", "update") {
  return errors.New("UNAUTHORIZED")
}

C13.11) New tasks C11 delta (+7.25d)

#	Hạng mục	Effort	Owner	Sprint
NEW-1	`pkg/pancake/db.go` pgxpool + env setup	2d	BE-1	W1
NEW-2	External backfill Go job	0.5d	BE-1	W1
NEW-3	Build `XJsonViewer.tsx`	1d	FE	W2
NEW-4	Build `XMetricCard.tsx`	0.5d	FE	W2
NEW-5	Cleanup contact_book duplicate audit script	0.25d	BE-1	W1
NEW-6	`pancake_replay_event` permission refactor (use `update`)	0.25d	BE-1 + FE	W2
NEW-7	F18 4th file delta	0.25d	FE	W1
NEW-8	3 alertmanager rules thêm	0.25d	DevOps	W3
NEW-9	Deploy step 9 split 9a/9b	0.25d	DevOps	W3
NEW-10	`down-soft.sql` rollback safety	0.25d	BE-1 + FE	W1
NEW-11	HMAC verification path spec	0.5d	TL + Ops	W3
NEW-12	Permission audit lint script	0.5d	TL	W3
NEW-13	Rollback drill rehearsal staging	1d	BE-1 + DevOps	W3
Tổng		+7.25d

New total estimate: ~44.25d (vs 37d cũ). Calendar 5.5-6 tuần OK với 2 BE + 1 FE + 1 QA + 1 Ops.

Hết Pass 1 Resolutions Dev Spec. Sẵn sàng BE Dev implement W1 Sprint 1 với architecture DEC-028 chốt.

Đặc tả triển khai (Dev Spec) — Tích hợp Pancake CRM (Pancake CRM Integration) ​

Tài liệu đầu vào chuẩn ​

C1) Phạm vi (Scope) ​

Modules ảnh hưởng ​

Không thuộc phạm vi Dev Spec ​

C2) Tóm tắt ảnh hưởng + mapping nghiệp vụ → kỹ thuật ​

C3) Quy tắc và công thức (chỉ phần triển khai) ​

FORMULA-001: Latency webhook → ticket assigned (p95) ​

FORMULA-002: SLA telesale phản hồi (p95, giờ làm việc) ​

FORMULA-003: Auto-assign correct rate ​

FORMULA-004: Event success rate ​

FORMULA-005: Zero-miss SLO (Cron 7 reconciliation) ​

FORMULA-006: Outage MTTR ​

STATE-001 — Vòng đời pancake_webhook_event.status (ref PRD LIFECYCLE-001) ​

STATE-002 — Vòng đời pancake_outage_state.status (ref PRD LIFECYCLE-002) ​

C4) Mô hình dữ liệu (Data Model) ​

Bảng hiện có (chỉ đọc / ref) ​

Bảng hiện có — ALTER detail ​

Bảng mới (4 bảng pancake_*) ​

4.1 pancake_webhook_event — Audit raw + status lifecycle ​

4.2 pancake_connection — Workspace config ​

4.3 pancake_source_routing — Source → Branch mapping ​

4.4 pancake_outage_state — Single-row outage tracking + history ​

Hàm SQL helper ​

Backfill normalized_phone (batch script) ​

C5) Quy ước tích hợp (Integration Conventions) ​

Hasura metadata ​

GraphQL query / mutation ​

Hasura Action: pancake_replay_event / pancake_replay_event_bulk ​

Endpoint pattern (BẮT BUỘC — không tự sáng tạo route) ​

Flow chi tiết 15 STEP (event_pancake_process_record.go) — pseudocode Go ​

Webhook receiver flow (services/webhook/handler/pancake.go) — pseudocode ​

Cron schedulers (services/crm-api/scheduler) ​

Bảng mã lỗi (M+) ​

C6) Component frontend ​

Cấu trúc file ​

Logic lõi (pseudocode TypeScript) ​

C7) Chiến lược migration (Migration Strategy) ​

Migration sequence ​

Migration template — Idempotent + Pre-check anchor (CLAUDE.md pattern) ​

Thứ tự deploy ​

Backfill normalized_phone capacity ​

Rollback strategy ​

C8) Bảo mật (Security) ​

Ma trận Dynamic Permission ​

Migration permission / metadata delta ​

Backend enforcement ​

Hasura Row-Level Security ​

Sensitive data redaction ​

Rate limiting ​

C9) Phi chức năng (NFR) ​

Hiệu năng + index ​

Capacity ​

NFR table ​

C10) Quan sát vận hành (Observability) ​

Metrics (Prometheus) ​

Logs (structured, zerolog) ​

Alerts (alertmanager) ​

Dashboard / Runbook (L+ khuyến nghị) ​

C11) Danh sách việc triển khai (Tasks) ​

Giai đoạn 1 — Foundation (W1, ~10 dev-days) ​

Giai đoạn 2 — Recovery + UI (W2-W3, ~16 dev-days) ​

Giai đoạn 3 — Production hardening (W4, ~11 dev-days) ​

Sơ đồ phụ thuộc ​

C12) Truy vết (Traceability) ​

Matrix FR → File/Component → Test Case ​

Mapping quyết định (DEC) → triển khai → cách kiểm chứng ​

C13) Pass 1 Resolutions (Phase 5.2 Multi-Perspective Review fixes) ​

C13.1) Architecture refactor — Direct pgxpool (DEC-028) ​

C13.2) Sweep Hasura YAML conventions ​

C13.3) Migration sequence REVISED ​

C13.4) Idempotency UNIQUE NULL handling ​

C13.5) Cron 6 fixed schedule + handler throttle ​

C13.6) Cron 3 ON CONFLICT DO NOTHING (PO/BA CI-4) ​

C13.7) F18 = 4 file (FE P0-FE-2) ​

C13.8) Component library audit (FE P0-FE-3) ​

C13.9) STATE-001 trigger filter OLD.status check ​

C13.10) DLQ replay permission update reuse (DEC-027) ​

C13.11) New tasks C11 delta (+7.25d) ​

Đặc tả triển khai (Dev Spec) — Tích hợp Pancake CRM (Pancake CRM Integration)

Tài liệu đầu vào chuẩn

C1) Phạm vi (Scope)

Modules ảnh hưởng

Không thuộc phạm vi Dev Spec

C2) Tóm tắt ảnh hưởng + mapping nghiệp vụ → kỹ thuật

C3) Quy tắc và công thức (chỉ phần triển khai)

FORMULA-001: Latency webhook → ticket assigned (p95)

FORMULA-002: SLA telesale phản hồi (p95, giờ làm việc)

FORMULA-003: Auto-assign correct rate

FORMULA-004: Event success rate

FORMULA-005: Zero-miss SLO (Cron 7 reconciliation)

FORMULA-006: Outage MTTR

STATE-001 — Vòng đời `pancake_webhook_event.status` (ref PRD LIFECYCLE-001)

STATE-002 — Vòng đời `pancake_outage_state.status` (ref PRD LIFECYCLE-002)

C4) Mô hình dữ liệu (Data Model)

Bảng hiện có (chỉ đọc / ref)

Bảng hiện có — ALTER detail

Bảng mới (4 bảng `pancake_*`)

4.1 `pancake_webhook_event` — Audit raw + status lifecycle

4.2 `pancake_connection` — Workspace config

4.3 `pancake_source_routing` — Source → Branch mapping

4.4 `pancake_outage_state` — Single-row outage tracking + history

Hàm SQL helper

Backfill `normalized_phone` (batch script)

C5) Quy ước tích hợp (Integration Conventions)

Hasura metadata

GraphQL query / mutation

Hasura Action: `pancake_replay_event` / `pancake_replay_event_bulk`

Endpoint pattern (BẮT BUỘC — không tự sáng tạo route)

Flow chi tiết 15 STEP (`event_pancake_process_record.go`) — pseudocode Go

Webhook receiver flow (`services/webhook/handler/pancake.go`) — pseudocode

Cron schedulers (services/crm-api/scheduler)

Bảng mã lỗi (M+)

C6) Component frontend

Cấu trúc file

Logic lõi (pseudocode TypeScript)

C7) Chiến lược migration (Migration Strategy)

Migration sequence

Migration template — Idempotent + Pre-check anchor (CLAUDE.md pattern)

Thứ tự deploy

Backfill `normalized_phone` capacity

Rollback strategy

C8) Bảo mật (Security)

Ma trận Dynamic Permission

Migration permission / metadata delta

Backend enforcement

Hasura Row-Level Security

Sensitive data redaction

Rate limiting

C9) Phi chức năng (NFR)

Hiệu năng + index

Capacity

NFR table

C10) Quan sát vận hành (Observability)

Metrics (Prometheus)

Logs (structured, zerolog)

Alerts (alertmanager)

Dashboard / Runbook (L+ khuyến nghị)

C11) Danh sách việc triển khai (Tasks)

Giai đoạn 1 — Foundation (W1, ~10 dev-days)

Giai đoạn 2 — Recovery + UI (W2-W3, ~16 dev-days)

Giai đoạn 3 — Production hardening (W4, ~11 dev-days)

Sơ đồ phụ thuộc

C12) Truy vết (Traceability)

Matrix FR → File/Component → Test Case

Mapping quyết định (DEC) → triển khai → cách kiểm chứng

C13) Pass 1 Resolutions (Phase 5.2 Multi-Perspective Review fixes)

C13.1) Architecture refactor — Direct pgxpool (DEC-028)

C13.2) Sweep Hasura YAML conventions

C13.3) Migration sequence REVISED

C13.4) Idempotency UNIQUE NULL handling

C13.5) Cron 6 fixed schedule + handler throttle

C13.6) Cron 3 ON CONFLICT DO NOTHING (PO/BA CI-4)

C13.7) F18 = 4 file (FE P0-FE-2)

C13.8) Component library audit (FE P0-FE-3)

C13.9) STATE-001 trigger filter OLD.status check

C13.10) DLQ replay permission `update` reuse (DEC-027)

C13.11) New tasks C11 delta (+7.25d)