Diva Group

Appearance

● Nháp★ NewComplexity LPO/BA (NGUYỄN SƠN THỌ, IT Leader)Cập nhật 2026-05-15
voice-recordingbod-dashboardecommercepermission-v2materialized-viewstracking

Đặc tả kỹ thuật (Dev Spec) — Insight Ghi Âm cho BOD ​

Phiên bản: 1.0 Ngày: 15/05/2026 Tác giả: PO/BA (NGUYỄN SƠN THỌ) + Tech Lead review Mục đích: Data model + API + migration + security + performance + traceability. KHÔNG duplicate business formulas (xem prd.md A10).

C1) Phạm vi (Scope) ​

In Scope P0:

  • 1 page mới RecordInsightPage.tsx (route /e/record/insights)
  • 7 dashboard widgets components
  • 2 drill-down modals
  • 1 Pinia store
  • Extend RecordTable.tsx (parse URL params + 2 filter)
  • BE migration seed permission view_insight
  • BE migration indexes Tier 1 (4 indexes)
  • Optional BE action get_record_audio_url (nếu PD-007 cần)

Defer P1: Comparison mode + Hour-of-day filter + Anomaly banner trên /e/record

Moved lên P0 (sau code review feedback):

  • ✅ Hasura appointment.records array_relationship YAML (required cho FR-006/FR-007 query)
  • ✅ 3 Hasura materialized views (record_daily_summary, _staff_summary, _hourly_summary) — bắt buộc P0 để đạt TTFMP target với 105k records (xem §C9 Performance)
  • ✅ Hasura permission cho 3 views với filter _or: [view_all, branch_id IN allowed_branches] (BE enforcement P0)

Defer P2: Export Excel/PDF + Schedule email + Full Staff Ranking page + KPI drill-down panel + Configurable thresholds

C2) Impact ​

LayerFiles affectedType
FE Pagespages/RecordInsights.tsxNEW
FE Widgetscomponents/record-insight/*.tsx × 7NEW
FE Modalscomponents/record-insight/modals/*.tsx × 2NEW
FE Storesstores/useRecordInsightStore.tsNEW
FE Modifiedcomponents/record/RecordTable.tsxMODIFIED (extend filter + URL parse)
FE Modifiedpages/Records.tsxMODIFIED (button [📊 Insight →])
FE Modifiedmodules/ecommerce/module.tsMODIFIED (route + sidebar entry)
BE Migrationmigrations/<timestamp>_seed_view_insight_action/up.sqlNEW
BE Migrationmigrations/<timestamp>_create_record_insight_indexes/up.sqlNEW
BE Migration P0migrations/<timestamp>_create_record_insight_views/up.sqlNEW (moved to P0 — performance critical)
BE Hasura YAML P0public_appointment.yamlMODIFIED (array_relationship records — required FR-006/007)
BE Hasura YAML P0public_record_daily_summary.yaml, _staff_summary.yaml, _hourly_summary.yamlNEW + permission filter cho user role

C3) Quy tắc / Công thức (Rules / Formulas — Implementation delta, KHÔNG duplicate PRD A10) ​

Business formulas canonical = prd.md A10 (FORMULA-001 đến FORMULA-006). C3 chỉ ghi SQL implementation delta.

FORMULA-001: Số cuộc tư vấn (Implementation) ​

  • Ref: PRD A10 FORMULA-001
  • SQL:
    sql
    SELECT COUNT(*)::int AS record_count
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
WHERE r.disabled = false
  AND a.branch_id = ANY($branch_ids::uuid[])
  AND r.created_at >= $from::timestamptz
  AND r.created_at < ($to::timestamptz + interval '1 day');
  • Source mapping: record.id, record.disabled, appointment.branch_id, record.created_at
  • Index: idx_record_active_time (compound)
  • Performance note: Half-open range (CLAUDE.md SQL gotcha) — KHÔNG dùng BETWEEN

FORMULA-002: Thời lượng TB / cuộc (Implementation) ​

  • Ref: PRD A10 FORMULA-002
  • SQL:
    sql
    SELECT
  AVG(EXTRACT(EPOCH FROM rf.duration))::int AS avg_duration_sec,
  COUNT(*) FILTER (WHERE rf.duration IS NULL) AS records_without_duration
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
LEFT JOIN public.reference_file rf ON rf.reference_id = r.id  -- FIX: reference_id, KHÔNG entity_id
WHERE r.disabled = false
  AND a.branch_id = ANY($branch_ids::uuid[])
  AND r.created_at >= $from::timestamptz
  AND r.created_at < ($to::timestamptz + interval '1 day');
  • Source mapping: reference_file.duration (PostgreSQL time type, HH:MM:SS) → cast EPOCH → seconds
  • Precision: EXTRACT(EPOCH FROM rf.duration) returns numeric → cast ::int cho aggregate
  • Performance note: LEFT JOIN cho phép records không có file metadata vẫn được count; AVG tự động exclude NULL

FORMULA-003: NV hoạt động (Implementation) ​

  • Ref: PRD A10 FORMULA-003
  • SQL:
    sql
    SELECT COUNT(DISTINCT r.created_by)::int AS active_staff
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
WHERE r.disabled = false
  AND a.branch_id = ANY($branch_ids::uuid[])
  AND r.created_at >= $from::timestamptz
  AND r.created_at < ($to::timestamptz + interval '1 day');
  • Index: idx_record_created_by (partial)

FORMULA-004: Tuân thủ ghi âm (Implementation) ​

  • Ref: PRD A10 FORMULA-004
  • DEPENDENCY: PD-003 (appointment baseline) — PHẢI align trước khi implement query này
  • SQL (tentative, fallback all appointments):
    sql
    WITH base_appointments AS (
  SELECT a.id
  FROM public.appointment a
  WHERE a.branch_id = ANY($branch_ids::uuid[])
    AND a."from" >= $from::timestamptz
    AND a."from" < ($to::timestamptz + interval '1 day')
    -- PD-003: thêm filter status / type sau khi BE confirm
    -- AND a.status = 'completed'
    -- AND a.service_type = 'consultation'
),
appointments_with_record AS (
  SELECT DISTINCT ba.id
  FROM base_appointments ba
  JOIN public.record r ON r.appointment_id = ba.id AND r.disabled = false
)
SELECT
  COUNT(awr.id)::numeric / NULLIF(COUNT(ba.id), 0) * 100 AS compliance_rate_pct,
  COUNT(ba.id) AS total_appointments,
  COUNT(awr.id) AS appointments_with_record
FROM base_appointments ba
LEFT JOIN appointments_with_record awr ON awr.id = ba.id;
  • Precision: numeric for rate, 2 decimal display
  • Edge case: NULLIF(COUNT, 0) → trả NULL nếu mẫu số = 0 (UI hiển thị "—")

FORMULA-005: KH chờ TV (Implementation) ​

  • Ref: PRD A10 FORMULA-005
  • DEPENDENCY: PD-003 (baseline tư vấn) + appointment.records relationship P0 + pivot appointment_user (Diva schema)
  • Schema note: appointment table KHÔNG có column customer_id direct (verified migration 1662366406542). Customer extraction qua pivot appointment_user.user_id → ecommerce_user.
  • SQL:
    sql
    -- P0 implementation — count distinct user qua appointment_user pivot
SELECT COUNT(DISTINCT au.user_id)::int AS awaiting_count
FROM public.appointment_user au
JOIN public.appointment a ON a.id = au.appointment_id
WHERE a.branch_id = ANY($branch_ids::uuid[])
  AND a."from" >= $from::timestamptz
  AND a."from" < ($to::timestamptz + interval '1 day')
  -- PD-003: appointment loại tư vấn (status/type/service_type/consultant_behavior — BE confirm trước implement)
  -- AND a.status = '...' AND a.service_type = '...'
  AND NOT EXISTS (
    SELECT 1 FROM public.record r
    WHERE r.appointment_id = a.id AND r.disabled = false
  );
  • Performance note: NOT EXISTS thường nhanh hơn LEFT JOIN ... IS NULL. JOIN appointment_user pivot có FK index nên cheap.

FORMULA-006: Delta WoW (Implementation) ​

  • Ref: PRD A10 FORMULA-006
  • Logic FE side (TypeScript):
    typescript
    function calcDeltaWoW(current: number, previous: number): { pct: number, direction: 'up' | 'down' | 'flat' | 'new' } {
  if (previous === 0) return { pct: 0, direction: 'new' };
  const pct = ((current - previous) / previous) * 100;
  if (Math.abs(pct) < 0.01) return { pct: 0, direction: 'flat' };
  return { pct: Math.round(pct * 100) / 100, direction: pct > 0 ? 'up' : 'down' };
}
  • Note: Backend query 2 periods (current + previous shift back) song song, FE tính delta

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

C4.1) Tables involved (existing — không tạo mới P0) ​

Schema.TableColumns dùngIndexes hiện cóIndexes thêm (P0)
public.recordid, customer_id, appointment_id, created_at, created_by, updated_at, disabled(cần BE verify)4 indexes Tier 1
public.appointmentid, branch_id, "from", "to", type, status, service_type, consultant_behavior, name, method, description, url, canceled, end_time, cancel_reason, order_idVerified migration 1662366406542. KHÔNG có column customer_id direct — customer qua pivot appointment_user (line 53-58)(existing index nếu có)
public.appointment_user (pivot)PK(appointment_id, user_id); FK → appointment.id + ecommerce_user.idVerified migration 1662366406542:line 53-58; Hasura array_relationship appointment.users qua appointment_user (line 64 yaml)Reuse via GraphQL appointment.users[].search_user.id
public.reference_fileid, reference_id, duration, size, url, mime_type(cần BE verify reference_id)partial index trên reference_id WHERE duration IS NOT NULL
accountid, display_name, phone_numberexistingnone
public.module_permission_actionid, module_id, action, name JSON, description JSON, priority, visibleexisting PK + UNIQUE(module_id, action)INSERT row voice_recording_management:view_insight
public.role_moduleid, role_id, module_id, actions TEXT[], priority, created_at, updated_atexisting — actions là TEXT[] arrayUPDATE actions = array_append(actions, 'view_insight') cho BOD + ITLeader

C4.2) Indexes mới (P0 mandatory — DEC-012) ​

sql
-- Migration: <timestamp>_create_record_insight_indexes/up.sql

-- 1. Compound index — main aggregation query
CREATE INDEX CONCURRENTLY idx_record_active_time
  ON public.record (created_at DESC, appointment_id)
  WHERE disabled = false;

-- 2. Appointment branch filter (likely existing nhưng verify)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_appointment_branch_start
  ON public.appointment (branch_id, "from" DESC);

-- 3. Reference_file join (partial cho duration NOT NULL)
CREATE INDEX CONCURRENTLY idx_reference_file_ref_duration
  ON public.reference_file (reference_id)
  WHERE duration IS NOT NULL;

-- 4. BRIN index cho time-series (tiết kiệm 90% storage vs B-tree)
CREATE INDEX CONCURRENTLY idx_record_created_at_brin
  ON public.record USING BRIN (created_at)
  WITH (pages_per_range = 32);

Notes:

  • CONCURRENTLY để không block writes during creation
  • IF NOT EXISTS cho index #2 (case index đã tồn tại từ migration cũ)
  • BRIN index #4 hữu ích cho range scan trên large time-series (1M+ rows tương lai)

C4.3) Hasura materialized views (P0 — moved theo code review feedback) ​

Critical update: P0 ban đầu dùng inline aggregation (fetch rows + FE group). Reviewer phát hiện payload với 105k records sẽ > 100MB JSON → fail TTFMP target. Materialized views ĐƯỢC MOVE LÊN P0 để giải quyết cả performance (B5) + BE enforcement (B4).

sql
-- Migration P0: <timestamp>_create_record_insight_views/up.sql

CREATE MATERIALIZED VIEW public.record_daily_summary AS
SELECT
  date_trunc('day', r.created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS day,
  a.branch_id,
  r.created_by AS staff_id,
  COUNT(*) AS record_count,
  COALESCE(SUM(EXTRACT(EPOCH FROM rf.duration))::bigint, 0) AS total_duration_seconds,
  COUNT(*) FILTER (WHERE rf.duration IS NULL) AS records_without_duration
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
LEFT JOIN public.reference_file rf ON rf.reference_id = r.id
WHERE r.disabled = false
GROUP BY 1, 2, 3;

-- Unique index for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_record_daily_summary_pk
  ON public.record_daily_summary (day, branch_id, staff_id);

-- Query patterns
CREATE INDEX idx_record_daily_summary_day ON public.record_daily_summary (day DESC);
CREATE INDEX idx_record_daily_summary_branch ON public.record_daily_summary (branch_id);

-- ─────────────────────────────────────────────────────────
-- View 2: record_staff_summary (staff × day × branch aggregate)
-- ─────────────────────────────────────────────────────────
CREATE MATERIALIZED VIEW public.record_staff_summary AS
SELECT
  r.created_by AS staff_id,
  a.branch_id,
  date_trunc('day', r.created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS day,
  COUNT(*) AS record_count,
  COALESCE(SUM(EXTRACT(EPOCH FROM rf.duration))::bigint, 0) AS total_duration_seconds,
  COUNT(DISTINCT r.customer_id) AS unique_customers
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
LEFT JOIN public.reference_file rf ON rf.reference_id = r.id
WHERE r.disabled = false
GROUP BY 1, 2, 3;

CREATE UNIQUE INDEX idx_record_staff_summary_pk
  ON public.record_staff_summary (staff_id, branch_id, day);
CREATE INDEX idx_record_staff_summary_branch_day ON public.record_staff_summary (branch_id, day DESC);

-- ─────────────────────────────────────────────────────────
-- View 3: record_hourly_summary (hour × weekday × branch heatmap)
-- ─────────────────────────────────────────────────────────
CREATE MATERIALIZED VIEW public.record_hourly_summary AS
SELECT
  date_trunc('day', r.created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS day,
  EXTRACT(HOUR FROM r.created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::int AS hour_of_day,
  EXTRACT(ISODOW FROM r.created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::int AS day_of_week, -- 1=Mon, 7=Sun
  a.branch_id,
  COUNT(*) AS record_count,
  COALESCE(SUM(EXTRACT(EPOCH FROM rf.duration))::bigint, 0) AS total_duration_seconds
FROM public.record r
JOIN public.appointment a ON a.id = r.appointment_id
LEFT JOIN public.reference_file rf ON rf.reference_id = r.id
WHERE r.disabled = false
GROUP BY 1, 2, 3, 4;

CREATE UNIQUE INDEX idx_record_hourly_summary_pk
  ON public.record_hourly_summary (day, hour_of_day, day_of_week, branch_id);
CREATE INDEX idx_record_hourly_summary_branch_day ON public.record_hourly_summary (branch_id, day DESC);

-- ─────────────────────────────────────────────────────────
-- Cron refresh every 15 minutes (via pg_cron OR scheduler service):
-- ─────────────────────────────────────────────────────────
-- REFRESH MATERIALIZED VIEW CONCURRENTLY public.record_daily_summary;
-- REFRESH MATERIALIZED VIEW CONCURRENTLY public.record_staff_summary;
-- REFRESH MATERIALIZED VIEW CONCURRENTLY public.record_hourly_summary;
--
-- Note: CONCURRENTLY requires UNIQUE INDEX (đã có ở mỗi view)
-- Refresh time estimate: ~5-10s với 105k records — chạy background không block reads

C4.3.1) Hasura YAML cho 3 materialized views (P0 — manual_configuration relationships) ​

Critical: Materialized views KHÔNG có FK constraints native → KHÔNG dùng foreign_key_constraint_on. Phải dùng manual_configuration + column_mapping (pattern verified existing public_branch_active.yaml:4-12).

File mới public_record_daily_summary.yaml:

yaml
table:
  name: record_daily_summary
  schema: public
object_relationships:
  - name: branch
    using:
      manual_configuration:
        column_mapping:
          branch_id: id
        insertion_order: null
        remote_table:
          name: branch
          schema: public
select_permissions:
  - role: user
    permission:
      columns: '*'
      # SESSION-CLAIM STRATEGY (BE responsibility): JWT claim X-Hasura-Allowed-Branches
      # phải được set đầy đủ bởi BE middleware tùy theo permission user:
      #   - User có `view_insight` + `view_all`: claim = list 70 branch UUIDs
      #   - User có `view_insight` + branch_mode: claim = list own branches
      #   - User KHÔNG có `view_insight`: claim rỗng (FE route guard chặn trước; backup tại đây query 0 rows)
      filter:
        branch_id: { _in: X-Hasura-Allowed-Branches }
      allow_aggregations: true

File mới public_record_staff_summary.yaml:

yaml
table:
  name: record_staff_summary
  schema: public
object_relationships:
  - name: branch
    using:
      manual_configuration:
        column_mapping: { branch_id: id }
        remote_table: { name: branch, schema: public }
  - name: account
    using:
      manual_configuration:
        column_mapping: { staff_id: id }  # ecommerce_user.id
        remote_table: { name: ecommerce_user, schema: public }
select_permissions:
  - role: user
    permission:
      columns: '*'
      # Same session-claim strategy
      filter:
        branch_id: { _in: X-Hasura-Allowed-Branches }
      allow_aggregations: true

File mới public_record_hourly_summary.yaml: tương tự (chỉ có branch relationship qua manual_configuration). Filter cũng dùng X-Hasura-Allowed-Branches.

Lý do KHÔNG dùng _exists cross-source + _has_key:

  • role_module table nằm trong source default; views nằm trong source ecommerce — Hasura permission filter KHÔNG support cross-source _exists
  • role_module.actions là TEXT[] array (verified migration 1761808767073_perm_v2:line 205); _has_key là JSONB operator — sai type
  • Session-claim strategy clean hơn: BE pre-compute permission → inject vào JWT → Hasura chỉ filter đơn giản

Track YAML trong tables.yaml:

yaml
# controller/metadata/databases/ecommerce/tables/tables.yaml
- "!include public_record_daily_summary.yaml"
- "!include public_record_staff_summary.yaml"
- "!include public_record_hourly_summary.yaml"

Apply order: Migration SQL CREATE MATERIALIZED VIEW chạy TRƯỚC → Hasura console "Track new" 3 views → apply YAML metadata. Permission filter pattern chính xác phụ thuộc Diva JWT structure (PD-006 BE confirm).

C4.4) Appointment.records relationship (P0 YAML — moved từ P1) ​

yaml
# controller/metadata/databases/ecommerce/tables/public_appointment.yaml
# Existing array_relationships: addresses, campaign_appointments, users
# ADD:
array_relationships:
  - name: records
    using:
      foreign_key_constraint_on:
        column: appointment_id
        table:
          name: record
          schema: public

Required cho FR-006/FR-007 (Missing records + KH chờ TV query với _not: { records: {} }). Phải apply metadata TRƯỚC khi FE deploy P0.

C4.5) Permission seed (P0) — Dynamic Permission v2 model ​

Schema chính xác (verified migration 1761808767073_perm_v2/up.sql:140-205):

  • Table module_permission_action (catalog actions): columns id TEXT PK, module_id TEXT, action TEXT, name JSON, description JSON, keywords TEXT, priority INT, visible BOOL, created_at, updated_at; UNIQUE (module_id, action)
  • Table role_module đã có column actions TEXT[] (array, KHÔNG phải table riêng role_module.actions (TEXT[] array))
  • Seed pattern: INSERT module_permission_action + UPDATE role_module.actions với array_append()
  • FE constant ALL_PERMISSION_ACTIONS trong permissions.ts:6-14 phải bổ sung view_insight
sql
-- Migration: <timestamp>_seed_view_insight_action/up.sql

-- 1. Insert action vào catalog module_permission_action
INSERT INTO "public"."module_permission_action"
  (id, module_id, action, name, description, priority, visible, created_at, updated_at)
VALUES (
  'voice_recording_management:view_insight',
  'voice_recording_management',
  'view_insight',
  '{"vi": "Xem dashboard Insight ghi âm", "en": "View recording insight dashboard"}'::json,
  '{"vi": "Truy cập trang Insight Ghi Âm tổng quan cho BOD", "en": "Access BOD insight dashboard"}'::json,
  80,
  TRUE,
  NOW(),
  NOW()
)
ON CONFLICT (module_id, action) DO NOTHING;

-- 2. Seed cho role BOD + ITLeader — UPDATE role_module.actions array với array_append
-- IMPORTANT: role.id là TEXT (verified migration 1660041217108_initialize), values lowercase: 'bod', 'it_leader'
-- KHÔNG match qua role.name (JSON {VI, EN} không phải text)
-- Reference seed pattern: migration 1761808767073_perm_v2:line 329-349

-- Verify role IDs trước migration:
-- SELECT id, name FROM "public"."role" WHERE id IN ('bod', 'it_leader');
-- (must return 2 rows; nếu không có role 'it_leader' → check exact ID name với BE TL)

UPDATE "public"."role_module" rm
SET actions = array_append(actions, 'view_insight'),
    updated_at = NOW()
WHERE rm.module_id = 'voice_recording_management'
  AND rm.role_id IN ('bod', 'it_leader')  -- text values, lowercase per Diva convention
  AND NOT ('view_insight' = ANY(rm.actions));  -- idempotent guard

-- 3. Verify (must return ≥ 2 rows)
SELECT r.id AS role_id, r.name AS role_name_json, rm.module_id, rm.actions
FROM "public"."role_module" rm
JOIN "public"."role" r ON r.id = rm.role_id
WHERE rm.module_id = 'voice_recording_management'
  AND 'view_insight' = ANY(rm.actions);

-- Rollback (down.sql):
-- UPDATE "public"."role_module" SET actions = array_remove(actions, 'view_insight')
-- WHERE module_id = 'voice_recording_management';
-- DELETE FROM "public"."module_permission_action"
-- WHERE module_id = 'voice_recording_management' AND action = 'view_insight';

C4.5.1) FE constant update ​

File modified: diva-admin/src/shared/permissions.ts:6-14

typescript
// BEFORE:
export const ALL_PERMISSION_ACTIONS = [
  "access", "create", "update", "delete", "approve", "payment", "view_all"
] as const;

// AFTER (P0):
export const ALL_PERMISSION_ACTIONS = [
  "access", "create", "update", "delete", "approve", "payment", "view_all",
  "view_insight",  // NEW — Insight Ghi Âm dashboard access (cho BOD/ITLeader)
] as const;

Impact: permissions.ts thay đổi sẽ ripple đến tất cả module dùng hasPermission(module, action) API — nhưng đây chỉ là extend enum, KHÔNG break existing behavior.

Note PD-005 — STATUS: ⬜ OPEN, BLOCK migration seed P0: Tên view_insight là PROPOSED (pattern verified khớp action verbs hiện hữu access, view_all). BE TL FINAL CONFIRM bắt buộc trước khi run migration — có thể đổi sang view_dashboard / bod_view / tên khác theo Diva convention. Migration idempotent qua NOT ('view_insight' = ANY(actions)) guard — re-run safe sau khi đổi tên.

C5) API / GraphQL ​

C5.1) GraphQL queries (P0 — query materialized views, KHÔNG inline aggregation) ​

Update P0: Tất cả queries fetch từ materialized views (record_daily_summary, _staff_summary, _hourly_summary) đã pre-aggregate. FE KHÔNG còn group rows client-side. Payload < 10KB thay vì 100MB. TTFMP target 2s/7d, 3s/30d đạt được.

Query 1: KPI cards — query materialized view record_daily_summary (P0)

graphql
query GetRecordInsightKPIs(
  $branchIds: [uuid!]!
  $from: date!
  $to: date!
  $previousFrom: date!
  $previousTo: date!
  $appointmentFrom: timestamptz!
  $appointmentTo: timestamptz!
) @cached(ttl: 60) {
  # KPI #1 + #2: Cuộc TV + Thời lượng TB (current period) — từ daily_summary
  current: record_daily_summary_aggregate(where: {
    branch_id: { _in: $branchIds }
    day: { _gte: $from, _lt: $to }
  }) {
    aggregate {
      sum {
        record_count             # KPI #1 total cuộc
        total_duration_seconds   # → FE chia cho count = KPI #2 Thời lượng TB
      }
    }
  }

  # KPI #1: previous period (WoW comparison)
  previous: record_daily_summary_aggregate(where: {
    branch_id: { _in: $branchIds }
    day: { _gte: $previousFrom, _lt: $previousTo }
  }) {
    aggregate { sum { record_count, total_duration_seconds } }
  }

  # KPI #3: NV hoạt động — count distinct staff từ staff_summary
  active_staff_current: record_staff_summary(
    where: {
      branch_id: { _in: $branchIds }
      day: { _gte: $from, _lt: $to }
    }
    distinct_on: [staff_id]
  ) { staff_id }
  active_staff_previous: record_staff_summary(
    where: { branch_id: { _in: $branchIds }, day: { _gte: $previousFrom, _lt: $previousTo } }
    distinct_on: [staff_id]
  ) { staff_id }

  # KPI #4: Tuân thủ ghi âm — total appointment loại "tư vấn" (period × branch)
  # PD-003 baseline: thêm filter status/type/service_type/consultant_behavior sau khi BE confirm
  total_appointments: appointment_aggregate(where: {
    branch_id: { _in: $branchIds }
    from: { _gte: $appointmentFrom, _lt: $appointmentTo }
    # PD-003: AND status: { _eq: "completed" } AND service_type: { _eq: "consultation" }
  }) { aggregate { count } }

  # Appointments có record (denominator → tỷ lệ)
  appointments_with_record: appointment_aggregate(where: {
    branch_id: { _in: $branchIds }
    from: { _gte: $appointmentFrom, _lt: $appointmentTo }
    records: { disabled: { _eq: false } }  # require appointment.records array_relationship (P0)
  }) { aggregate { count } }

  # KPI #5: KH chờ TV — appointment KHÔNG có record, distinct customer
  awaiting_customers: appointment_aggregate(where: {
    branch_id: { _in: $branchIds }
    from: { _gte: $appointmentFrom, _lt: $appointmentTo }
    _not: { records: {} }
  }, distinct_on: [customer_id]) { aggregate { count } }
}

Notes:

  • Payload từ aggregate sum: ~10 rows total (5 KPI × 2 periods) thay vì 105k records
  • KPI #2 "Thời lượng TB" tính FE side: total_duration_seconds / record_count
  • KPI #3 "NV hoạt động" dùng distinct_on staff_id (Hasura support)
  • KPI #4 + #5 dependency: appointment.records array_relationship phải tracked (P0 YAML)

Query 2: Trend chart (P0 — query materialized view trực tiếp)

graphql
query GetRecordTrend(
  $branchIds: [uuid!]!
  $from: date!
  $to: date!
) @cached(ttl: 60) {
  record_daily_summary(
    where: {
      branch_id: { _in: $branchIds }
      day: { _gte: $from, _lt: $to }
    }
    order_by: { day: asc }
  ) {
    day
    record_count
    total_duration_seconds
  }
  # Payload: ~30 rows (30 ngày aggregated) thay vì 105k records
}

Query 3: Top staff (P0 — fetch view rows + FE reduce theo staff_id)

Note: Hasura GraphQL hiện chưa support GROUP BY trực tiếp (chỉ aggregate global + nodes raw). Pattern:

  • Option A (chọn) — Fetch view rows (đã pre-aggregated per day × staff × branch) → FE reduce theo staff_id (~700 NV rows max trong period)
  • Option B — Tạo additional materialized view record_staff_period_summary group sẵn theo staff_id (nếu FE reduce phức tạp quá)
graphql
query GetTopStaff(
  $branchIds: [uuid!]!
  $from: date!
  $to: date!
) @cached(ttl: 60) {
  # Fetch rows từ daily aggregated view (max ~700 NV × 30 days = 21k rows pre-aggregated)
  # FE: reduce theo staff_id → sort desc → take top 10
  record_staff_summary(
    where: {
      branch_id: { _in: $branchIds }
      day: { _gte: $from, _lt: $to }
    }
    order_by: { record_count: desc }
  ) {
    staff_id
    record_count
    total_duration_seconds
    account { display_name }
  }
  # FE logic:
  # const byStaff = groupBy(data, 'staff_id')
  # const top10 = sortBy(byStaff, sum('record_count'), 'desc').slice(0, 10)
}

Performance note: Payload ~21k pre-aggregated rows vẫn nhẹ (~500KB) vs 105k raw records (~100MB). Nếu BE muốn optimize hơn → P1 build view record_staff_period_summary materialized theo staff × period (refresh cùng schedule).

Query 4: Heatmap (P0 — query record_hourly_summary view)

graphql
query GetHeatmap(
  $branchIds: [uuid!]!
  $from: date!
  $to: date!
) @cached(ttl: 60) {
  record_hourly_summary(
    where: {
      branch_id: { _in: $branchIds }
      day: { _gte: $from, _lt: $to }
    }
  ) {
    hour_of_day
    day_of_week
    record_count
  }
  # Payload: max 168 rows (24h × 7 weekday) đã aggregated
}

Query 5: Branch top 10 + "Khác" (P0 — fetch view + FE reduce theo branch_id)

Same pattern Top Staff (Hasura chưa GROUP BY native):

graphql
query GetBranchTopDistribution(
  $branchIds: [uuid!]!
  $from: date!
  $to: date!
) @cached(ttl: 60) {
  # Fetch rows từ daily view (max ~70 CN × 30 days = 2,100 rows)
  record_daily_summary(
    where: {
      branch_id: { _in: $branchIds }
      day: { _gte: $from, _lt: $to }
    }
  ) {
    branch_id
    branch { name }
    record_count
  }
  # FE logic:
  # const byBranch = groupBy(data, 'branch_id')
  # const sortedDesc = sortBy(byBranch, sum('record_count'), 'desc')
  # const top10 = sortedDesc.slice(0, 10)
  # const other = sortedDesc.slice(10).reduce((acc, b) => acc + b.sum, 0)
  # Display: [top10..., { branch_name: 'Khác (N CN)', count: other }]
}

Performance note: Payload ~2,100 rows trong period 30 ngày × 70 CN — rất nhẹ.

Query 6: Missing records (FR-006)

graphql
# DEPENDENCY: array_relationship `records` trên appointment (P0 YAML — xem C4.4)
# P0 fallback: inline SQL
query GetMissingRecords(
  $branchIds: [uuid!]!
  $from: timestamptz!
  $to: timestamptz!
  $limit: Int = 10
  $offset: Int = 0
) @cached(ttl: 60) {
  appointment_aggregate(where: {
    branch_id: { _in: $branchIds }
    from: { _gte: $from, _lt: $to }
    # PD-003: status / type filter (BE confirm trước implement)
    _not: { records: {} }  # Requires array_relationship `records` P0 (xem C4.4)
  }) { aggregate { count } }

  appointment(where: {
    branch_id: { _in: $branchIds }
    from: { _gte: $from, _lt: $to }
    _not: { records: {} }
  }, limit: $limit, offset: $offset, order_by: { from: desc }) {
    id
    from
    branch_id
    branch { name }
    # Customer qua pivot appointment_user (KHÔNG có appointment.customer_id direct)
    users {
      user_id
      search_user { id, display_name }  # ecommerce_user via manual_configuration (appointment_user.yaml:12)
    }
    # FE display: lấy users[0].search_user.display_name làm "KHÁCH HÀNG"
  }
}

Note: appointment.users là array (M:N via appointment_user pivot). Trong context tư vấn, thường có 1 user (KH); nếu nhiều (vd có NV + KH), FE phải filter theo user.role hoặc convention nghiệp vụ (PD-003 confirm với BE).

Query 7: KH chờ TV (FR-007) — count distinct customer qua appointment.users

graphql
query GetAwaitingCustomers(
  $branchIds: [uuid!]!
  $from: timestamptz!
  $to: timestamptz!
  $limit: Int = 10
  $offset: Int = 0
) @cached(ttl: 60) {
  # Cách 1 (recommended): query appointment_user pivot directly, filter qua related appointment
  # Hasura có thể query `appointment_user` table với nested where appointment
  awaiting_count: appointment_user_aggregate(where: {
    appointment: {
      branch_id: { _in: $branchIds }
      from: { _gte: $from, _lt: $to }
      _not: { records: {} }
    }
    # Distinct user_id để count KH unique
  }, distinct_on: [user_id]) { aggregate { count } }

  # List paginated — qua appointment_user pivot
  appointment_user(where: {
    appointment: {
      branch_id: { _in: $branchIds }
      from: { _gte: $from, _lt: $to }
      _not: { records: {} }
    }
  }, limit: $limit, offset: $offset, distinct_on: [user_id], order_by: [{ user_id: asc }]) {
    user_id
    search_user { id, display_name, phone_number }
    appointment {
      id, from, branch_id
      branch { name }
    }
  }
}

Alternative: Nếu pivot table appointment_user chưa được tracked aggregate trong Hasura, fallback dùng custom SQL function HOẶC tạo view appointments_awaiting_customers ở P1.

C5.2) URL Query params parsing (RecordTable.tsx extend) ​

typescript
// diva-admin/src/modules/ecommerce/components/record/RecordTable.tsx
import { useRoute } from 'vue-router';

onMounted(() => {
  const route = useRoute();
  const q = route.query;

  if (q.branchId) selected.value.branchId = Array.isArray(q.branchId) ? q.branchId : [q.branchId];
  if (q.from) selected.value.from = q.from as string;
  if (q.to) selected.value.to = q.to as string;
  if (q.staffId) selected.value.created_by = q.staffId as string;
  if (q.customerId) selected.value.customerId = q.customerId as string;

  // dateExact override range
  if (q.dateExact) {
    selected.value.from = q.dateExact as string;
    selected.value.to = q.dateExact as string;
  }

  // Duration filters (NEW)
  if (q.durationGt) selected.value.durationGt = parseInt(q.durationGt as string, 10);
  if (q.durationLt) selected.value.durationLt = parseInt(q.durationLt as string, 10);
});

C5.3) GraphQL filter extension cho RecordTable ​

graphql
query GetRecords(
  $branchIds: [uuid!]
  $from: timestamptz
  $to: timestamptz
  $staffId: text
  $customerId: text
  $durationGtSec: time  # NEW
  $durationLtSec: time  # NEW
  $limit: Int = 20
  $offset: Int = 0
) {
  record(where: {
    disabled: { _eq: false }
    _and: [
      { appointment: { branch_id: { _in: $branchIds } } }
      { created_at: { _gte: $from, _lt: $to } }
      { created_by: { _eq: $staffId } }
      { customer_id: { _eq: $customerId } }
      # NEW: duration filter via files relationship (record → files via id ↔ reference_id)
      { files: { duration: { _gt: $durationGtSec } } }
      { files: { duration: { _lt: $durationLtSec } } }
    ]
  }, limit: $limit, offset: $offset, order_by: { created_at: desc }) {
    id, customer_id, appointment_id, created_at, created_by
    files { url, duration, size }
    # ... other existing fields
  }
}

C6) Scheduler / Background jobs ​

P0 — MANDATORY (sau code review B5/P0-3 feedback):

  • Materialized view refresh — mỗi 15 phút:
    • Option A (recommended): pg_cron extension chạy SQL REFRESH MATERIALIZED VIEW CONCURRENTLY public.record_*_summary
    • Option B: Go scheduler diva-backend/services/ecommerce-api/scheduler/record_insight_view_refresh.go chạy queries via DB connection — tích hợp với pattern scheduler existing
    • Decision criteria: Nếu Diva infra đã có pg_cron enabled → Option A; nếu không → Option B
    • Refresh time estimate: ~5-10s với 105k records, KHÔNG block reads (CONCURRENTLY)
    • Failure handling: scheduler log + Sentry alert nếu refresh fail 3 lần liên tiếp

P2:

  • record_insight_email_report.go — cron weekly T2 08:00 generate PDF + send email báo cáo định kỳ

Lý do refresh P0 mandatory: Materialized view = pre-computed snapshot. Không refresh → data stale forever (chỉ đúng tại thời điểm migration). DEC-012 v2 yêu cầu refresh 15 phút để dashboard data fresh đủ cho BOD tracking. Tradeoff: max 15 phút lag — acceptable cho monitoring dashboard.

C7) Kế hoạch migration ​

Order of migrations: ​

P0 — Order matters (sau code review feedback B1/B5):
  1. <ts>_create_record_insight_indexes/up.sql      (4 indexes Tier 1, CONCURRENTLY)
  2. <ts>_seed_view_insight_action/up.sql           (permission v2 schema: module_permission_action + role_module.actions)
  3. <ts>_create_record_insight_views/up.sql        (3 MATERIALIZED views + UNIQUE indexes + query indexes)
  4. Hasura metadata apply:
     - Track 3 materialized views
     - Add manual_configuration object_relationships (branch, account) — xem C4.3.1
     - Add appointment.records array_relationship YAML
     - Add Hasura SELECT permission cho 3 views (filter view_all / allowed_branches)
  5. <ts>_setup_record_insight_refresh_cron/up.sql  (P0 — pg_cron OR scheduler config — xem C6)

P1:
  6. (defer) Comparison mode FE-only — không cần DB migration
  7. <ts>_alter_record_table_anomaly_banner/up.sql  (optional — nếu cần track drill-down state)

P2:
  8. <ts>_seed_record_insight_thresholds/up.sql     (configurable anomaly settings + export schedule)

Rollback strategy: ​

  • Indexes: DROP INDEX CONCURRENTLY IF EXISTS idx_*; — safe
  • Permission seed rollback: UPDATE role_module SET actions = array_remove(actions, 'view_insight') WHERE module_id='voice_recording_management'; + DELETE FROM module_permission_action WHERE module_id='voice_recording_management' AND action='view_insight'; — affects users (BOD lose access)
  • Materialized views: DROP MATERIALIZED VIEW IF EXISTS public.record_*_summary CASCADE; — affects Hasura metadata tracking → must drop Hasura tracking first

Migration safety checklist (Diva Pitfalls Map): ​

  • ✅ Use CONCURRENTLY for index creation (zero-downtime)
  • ✅ IF NOT EXISTS / ON CONFLICT DO NOTHING for idempotency
  • ✅ Schema = public (verified, not ecommerce)
  • ✅ FK = reference_id (verified, not entity_id)
  • ✅ Half-open date range >= AND < (not BETWEEN)
  • ✅ Test migrations on staging trước production

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

C8.1) Permission enforcement ​

Frontend layer (display gating):

typescript
// Sidebar entry + button visibility
const canViewInsight = computed(() =>
  globalStore.hasPermission('voice_recording_management', 'view_insight')
);

// Route guard
beforeEnter: (to, from, next) => {
  if (!globalStore.hasPermission('voice_recording_management', 'view_insight')) {
    return next({ name: 'forbidden' });
  }
  next();
}

Backend layer (data gating — REQUIRED, không tin FE) — P0:

Critical update (code review feedback): Hiện tại Hasura permission cho record/appointment/reference_file đều có filter {} (empty) cho role user (verified public_record.yaml:58). Nghĩa là direct GraphQL query không có row-level security. KHÔNG sửa existing 3 tables (tránh break Staff workflow), thay vào đó enforce BE security ở 3 materialized views mới:

Hasura permission cho 3 materialized views (P0 mandatory) — Session-claim strategy:

yaml
# public_record_daily_summary.yaml + public_record_staff_summary.yaml + public_record_hourly_summary.yaml
select_permissions:
  - role: user
    permission:
      columns: '*'
      # Filter ĐƠN GIẢN qua session var — KHÔNG cross-source _exists, KHÔNG _has_key
      filter:
        branch_id: { _in: X-Hasura-Allowed-Branches }
      allow_aggregations: true

Permission enforcement = JWT claim setup (BE middleware) + Hasura filter (declarative):

LayerTrách nhiệmImplementation
1. JWT issuance (BE middleware)Compute branch list theo permission của userPre-compute lúc login / refresh token: nếu user có view_insight + view_all → claim chứa list 70 branch UUIDs; nếu chỉ view_insight + branch_mode → claim chứa own branches; nếu KHÔNG có view_insight → claim rỗng
2. Hasura filter (declarative)Apply filter trên query resultbranch_id IN session vars — chỉ check session var, KHÔNG join permission table
3. FE route guard (UI gating)Chặn user không có quyền vào tranghasPermission('voice_recording_management', 'view_insight') route beforeEnter
4. Audit (Sentry)Track ai dùng dashboardEvent record_insight_viewed mỗi request

Lý do KHÔNG dùng cross-source _exists:

  • Views ở source ecommerce, role_module ở source default — Hasura permission KHÔNG support cross-source lookup
  • role_module.actions là TEXT[] (verified) — _has_key JSONB operator sẽ fail
  • Pre-compute claim ở BE clean hơn + performant hơn (không re-check permission mỗi request)

Alternative pattern (nếu BE muốn full control): Tạo custom Hasura action get_record_insight_* với Go handler — BE handler check hasPermission + return least-data. Trade-off: thêm BE code, nhưng FE call action thay vì direct view query. Khuyến nghị: session-claim cho P0 đơn giản; custom action P1 nếu BOD muốn audit log chi tiết.

Layer 3 — Application enforcement (Sentry audit trail):

  • Log analytics event record_insight_viewed với user_id, role, branch_filter mỗi request
  • Slow query alert > 3s → Sentry breadcrumb cho audit

C8.2) Branch scoping ​

RoleClaimHasura filter
BOD / Admin / ITLeader (có view_all)x-hasura-view-all: "true"KHÔNG filter branch
BranchManagerx-hasura-allowed-branches: "uuid1,uuid2,..."branch_id IN allowed_branches
Staffx-hasura-user-id: "..."(P0 không có quyền view_insight nên không vào dashboard)

C8.3) Audit trail ​

  • Log analytics event record_insight_viewed với user_id, role, branch_filter để track ai dùng dashboard
  • Sentry breadcrumb cho slow query > 3s
  • Hasura query timeout: 10s (set in config)

C8.4) Data sensitivity ​

  • Audio files (reference_file.url) — PD-007 cần BE confirm presigned URL với TTL hay raw S3 path
  • Customer phone numbers (account.phone_number) — chỉ hiển thị cho BOD/Admin
  • Audit notes (đã loại bỏ DEC-001)

C9) Yêu cầu phi chức năng (NFR — Non-Functional Requirements) ​

Chi tiết: design doc §4.7 + §4.11 Performance Architecture (5 tiers)

C9.1) Performance ​

MetricTargetStrategy
TTFMP với 7 ngày × 70 CN (~24.5k records)< 2sTier 1 indexes + Hasura @cached(ttl:60) + URQL cache-and-network
TTFMP với 30 ngày × 70 CN (~105k records)< 3sSame as above; escalate Tier 2.4 materialized views nếu fail
Filter change → re-render< 800msURQL cancelation + memoize chart transform
Audio playback start< 1sStream presigned S3 URL, lazy load
Concurrent users~50BE OK; Hasura cache hit reduces load

C9.2) Scalability ​

  • Scale baseline: 70 CN, 700 NV, 3.500 records/ngày, 105k records/30 ngày
  • Growth assumption: 2x trong 12 tháng → 210k records/30 ngày → materialized views P0 đã handle; nếu > 500k/30d cần daily snapshot table (Tier 4, P2)
  • Hard limit: 365 ngày time range (UI disable Apply nếu vượt)

C9.3) Reliability ​

  • Auto-poll 60s khi tab active; pause khi background (document.hidden)
  • Disable auto-poll khi range > 30 ngày
  • Retry logic: 2 retries với exponential backoff (3s → 6s) cho transient errors
  • Slow query alert: Sentry breadcrumb > 3s

C9.4) Maintainability ​

  • Reuse RecordForm audio player (KHÔNG build mới)
  • Reuse Chart.js wrappers + DashboardCardItem
  • Comments rõ ràng cho complex aggregation logic
  • Type safety TypeScript strict mode

C9.5) Performance Architecture (Tier reference) ​

TierApplyPhase
1. DB Indexes (4 indexes mandatory)P0Migration trước FE deploy
2.1 Hasura @cached(ttl:60)P0Apply mọi query
2.4 Materialized viewsP0 (moved từ P1 sau review B5)Pre-aggregated views — KHÔNG dùng inline aggregation
2.5 Read replica routingP1Nếu Diva có multi-source
3.1 URQL normalized cacheP0Built-in
3.2 Stale-while-revalidateP0requestPolicy: 'cache-and-network'
3.3 Lazy load chart componentsP1IntersectionObserver
4.1 Daily snapshot tableP2Nếu data > 500k
5.1 Table partitioningP3Nếu data > 1M

C10) Theo dõi vận hành (Observability) ​

C10.1) Analytics events (FE) ​

EventTriggerProperties
record_insight_viewedPage mount sau permission passuser_id, role, branch_filter_count, time_range_preset
record_insight_filter_changedFilter applyfilter_type, value_count
record_insight_chart_clickedClick chart segmentchart_type, target_filter
record_insight_anomaly_drilldownClick anomaly CTAanomaly_type, count
record_insight_drilldown_navigatedDrill-down /e/recordfrom_widget, query_params
record_insight_audio_playedPlay audio sau drill-downrecord_id, source: 'insight_drilldown'

C10.2) Backend logs ​

  • Hasura query logs: track slow queries > 1s
  • Migration audit: log mọi seed permission change
  • DB monitoring: pg_stat_statements cho top slow queries

C10.3) Alerts ​

  • Sentry: slow query > 3s
  • DB CPU > 60% peak hours
  • Materialized view refresh fail (P1+)

C11) Tasks breakdown ​

C11.1) P0 tasks (~1.5 tuần — 1 FE + 1 BE — updated sau code review) ​

TaskOwnerEstimateDependencies
BE: Migration indexes Tier 1 (4 indexes, CONCURRENTLY)BE Dev0.5dVerify existing indexes
BE: Migration permission seed (model v2 — module_permission_action + role_module.actions array)BE Dev0.5dPD-005 confirm role names
BE: JWT middleware setup x-hasura-allowed-branches claim (NEW P0 — PD-011)BE Dev (auth team)1dCompute từ user permission: view_all → 70 IDs; else → own branches; Staff thiếu view_insight → empty array. Test bằng decode JWT 3 role + verify claim content
BE: Materialized views (3 views) + refresh schedule (NEW — moved từ P1)BE Dev + DevOps1dAfter indexes; pg_cron / scheduler config
BE: Hasura YAML — 3 view metadata + appointment.records relationship + permission filterBE Dev0.5dAfter views created
BE: Optional action get_record_audio_urlBE Dev0.5d (skip nếu PD-007 OK)PD-007 check presigned
BE: EXPLAIN ANALYZE benchmark + materialized view refresh testBE Dev + Tech Lead0.5dAfter indexes + views
FE: RecordInsightPage.tsx containerFE Dev0.5dModule route config
FE: RecordInsightFilterBar.tsx (3 dropdowns + 70 CN/700 NV scale)FE Dev1dFilter pattern reuse
FE: RecordInsightKPIRow.tsx (5 cards + delta + tooltips)FE Dev1dDashboardCardItem reuse
FE: RecordTrendChart.tsx (single line + click)FE Dev0.5dLineChart reuse
FE: RecordTopStaffChart.tsx (horizontal bar + click)FE Dev0.5dBarChart reuse
FE: RecordHourHeatmap.tsx (SVG grid custom + click)FE Dev1dSVG implementation
FE: RecordBranchTopChart.tsx (bar + "Khác" + click)FE Dev0.5dBarChart reuse
FE: RecordAnomalyAlerts.tsx (2 cards + CTA)FE Dev0.5d—
FE: MissingRecordsModal.tsx (pagination + search + sort + filter)FE Dev1dModal pattern
FE: AnomalyMissingTVModal.tsx (similar)FE Dev1dModal pattern
FE: useRecordInsightStore.ts (Pinia store)FE Dev0.5d—
FE: Extend RecordTable.tsx (URL params + duration filter)FE Dev0.5d—
FE: Modify Records.tsx (button [📊 Insight →])FE Dev0.25d—
FE: Modify module.ts (route + sidebar)FE Dev0.25d—
QA: Test plan executionQA2dAfter FE+BE complete
Ops: Deploy P0 (migration first, then FE)DevOps0.5dAll complete

Total estimate: FE ~9 ngày, BE ~2 ngày, QA 2 ngày, Ops 0.5d → tổng ~7-8 ngày dev wall-clock (FE bottleneck với buffer).

C11.2) P1 tasks (~1 tuần) ​

Note: Materialized views + Hasura YAML + appointment.records relationship đã MOVE LÊN P0 sau code review B1/B5/P0-2 feedback. Xem C4.3, C4.3.1, C4.4 + C11.1 P0 tasks list.

  • BE: Read replica routing (if Diva infra có multi-source Hasura)
  • FE: Comparison mode component + filter toggle (4.2.16)
  • FE: Lazy load chart components (IntersectionObserver, Tier 3.3)
  • FE: Anomaly banner trên /e/record khi drill-down từ insight
  • FE: Optional hour-of-day filter trong RecordTable (heatmap drill precision)

C11.3) P2 tasks (~1.5 tuần) ​

  • FE: 3 Export modals (Excel/PDF/Schedule)
  • FE: Full Staff Ranking page (sub-route)
  • FE: KPI drill-down panel
  • FE: Configurable anomaly thresholds Settings
  • BE: PDF export action
  • BE: Email report scheduler
  • BE: Threshold settings table

C12) Traceability ​

100% FR mapped from prd.md A5 to UI components + DB queries + tests.

FRUI ComponentGraphQL QueryDB TablesQA Test ID
FR-001 (5 KPI cards)RecordInsightKPIRow.tsxQuery 1 (KPI aggregated)public.record, public.appointment, public.reference_fileTC-001-1 đến TC-001-6
FR-002 (Trend chart)RecordTrendChart.tsxQuery 2 (Trend)public.record + public.appointmentTC-002-1 đến TC-002-5
FR-003 (Top Staff)RecordTopStaffChart.tsxQuery 3 (Top staff)+ accountTC-003-1 đến TC-003-4
FR-004 (Heatmap)RecordHourHeatmap.tsxQuery 4 (Heatmap)public.recordTC-004-1 đến TC-004-5
FR-005 (Branch Top)RecordBranchTopChart.tsxQuery 5 (Branch dist)+ public.branchTC-005-1 đến TC-005-4
FR-006 (Missing modal)MissingRecordsModal.tsxQuery 6 (Missing)public.appointment + relationship records (P0 — xem C4.4)TC-006-1 đến TC-006-9
FR-007 (KH chờ TV modal)AnomalyMissingTVModal.tsxQuery 7 (Awaiting)Same as #6TC-007-1 đến TC-007-4
FR-008 (Filter bar)RecordInsightFilterBar.tsxAll queries — variables—TC-008-1 đến TC-008-6
FR-009 (Audio drill-down)Drill-down pattern(RecordTable existing query extended)public.record + public.reference_fileTC-009-1 đến TC-009-4
FR-010 (Permission v2)Sidebar + button + route guard(Permission API call)public.module_permission_action (catalog) + public.role_module.actions TEXT[] (assigned)TC-010-1 đến TC-010-7
FR-011 (/e/record extend)RecordTable.tsx modifiedGraphQL extended với durationGt/LtSame as #9TC-011-1 đến TC-011-4

FR coverage: 11/11 = 100% AC coverage: 49 AC items mapped to 49 QA test IDs

Tham chiếu chéo:

  • PRD: ./prd.md A10 FORMULA single-source
  • UI Spec: ./ui-spec.md B sections
  • Source of Truth: ./SOURCE_OF_TRUTH.md
  • Design doc gốc §4.4 + §4.7 + §4.11: ../../superpowers/specs/2026-05-15-record-bod-insight-design.md
  • CLAUDE.md Pitfalls Map: schema gotchas, SQL gotchas