Incomex AI Portal
KB-5A70

S146-M4b Fix Pivot Blockers Report

6 min read Revision 1
s146pivotfixreport

S146-M4b — Fix Pivot Blockers Report

Agent: Claude Code CLI (Claude Opus 4.6) Ngày: 2026-03-29 Trạng thái: 3 BLOCKERS FIXED + VERIFIED PRODUCTION PR: #645 (MERGED 2026-03-29T05:38:50Z)

Step 0: Checkpoint

  • OR version: v7.5
  • Merge rule: 4 required GREEN → MERGE NGAY (v4.68)
  • Assembly Gate Q0: PG đã giải quyết → ĐÃ FIX (mission này)

Bước 0: Source 2 Functions + 4 Câu Trả Lời

Q1: refresh_meta_catalog_from_pivot() join bằng gì?

JOIN: pivot_count() → pivot_definitions (code) → meta_catalog (registry_collection = source_object). Chỉ sync pivots có filter_spec = '{"filters":[]}' AND group_spec = '{"groups":[]}'. Update: record_count, active_count, last_scan_date cho managed rows.

Q2: fn_refresh_virtual_summaries() tính CAT-ALL thế nào?

TRƯỚC FIX: SUM(record_count) FROM meta_catalog WHERE identity_class = 'managed' — KHÔNG filter status, KHÔNG filter composition_level. SAU FIX: SUM(record_count) FROM meta_catalog WHERE identity_class = 'managed' AND status = 'active' AND composition_level = 'atom'

Q3: Hai functions xung đột không?

KHÔNG xung đột trực tiếp. refresh_meta_catalog_from_pivot update managed rows → trigger fn_refresh_virtual_summaries fires → update virtual rows. NHƯNG: fn_refresh_cat_all trigger CŨNG fire và update CAT-ALL với formula SAI → cạnh tranh.

Q4: Gốc lệch 176?

GỐC: 3 rows managed có status = 'published' (không phải 'active'):

  • CAT-015 (Checkpoint Instances): 121 records, molecule, published
  • CAT-018 (Nhãn hệ thống): 55 records, material, published
  • CAT-014 (Table Proposals): 0 records, molecule, published Tổng: 121 + 55 + 0 = 176 = ĐÚNG BẰNG SỐ LỆCH.

2 functions gây lệch:

  1. fn_refresh_virtual_summaries — SUM tất cả managed (kể cả published)
  2. fn_refresh_cat_all — SUM tất cả managed, không filter level

Blocker 1: CAT-ALL lệch → FIXED

Root cause: fn_refresh_virtual_summaries + fn_refresh_cat_all thiếu filter status='active' và composition_level.

Fix: Replace CẢ HAI functions:

  • fn_refresh_cat_all: Targeted update — chỉ refresh virtual row tương ứng với composition_level của row thay đổi. Filter status='active'.
  • fn_refresh_virtual_summaries: Mỗi virtual row SUM đúng level + status='active'.

Evidence TRƯỚC:

CAT-ALL: 34,441 (lệch +1,147 vs sum atom active = 33,294)
CAT-MOL: 766 (lệch +121 vs sum molecule active = 645)
CAT-MAT: 55 (managed material active = 0)

Evidence SAU:

CAT-ALL: 33,294 = SUM(atom managed active) = 33,294 → KHỚP
CAT-MOL: 645 = SUM(molecule managed active) = 645 → KHỚP
CAT-CMP: 326 = SUM(compound managed active) = 326 → KHỚP
CAT-MAT: 0 = SUM(material managed active) = 0 → KHỚP
CAT-PRD: 0 → KHỚP
CAT-BLD: 0 → KHỚP

Blocker 2: Cron path sai → FIXED

Root cause: cd /opt/incomex/deploys/web-test && dot/bin/dot-pivot-health — path không tồn tại.

Fix: 0 4 * * * /opt/incomex/dot/bin/dot-pivot-health >> /var/log/incomex/pivot-health.log 2>&1

Version control: Tạo dot/bin/dot-cron-pivot-setup — script quản lý cron entries cho pivot system.

Evidence: crontab -l | grep pivot-health → /opt/incomex/dot/bin/dot-pivot-health

Blocker 3: H7 không so COUNT thật → FIXED

Root cause: H7 chỉ check count_value < 0, không query COUNT(*) thực tế.

Fix: H7 giờ chạy DO $$ ... EXECUTE format('SELECT COUNT(*) FROM %I', source_object) ... $$ cho mỗi no-filter pivot → so với pivot_count().

Bonus fix: DOT tools detect VPS vs local (docker ps check) → chạy trực tiếp trên VPS không cần SSH loop. Fix ((PASS++)) arithmetic issue with set -e.

Evidence:

═══ PIVOT HEALTH CHECK v1.0.0 ═══
Total definitions: 27 (26 active, 1 inactive)
  H1: pivot_count() executes ...... ✅ PASS (26 rows, 423ms)
  H2: All counts non-negative ...... ✅ PASS
  H3: All source_objects exist ...... ✅ PASS (26/26)
  H4: meta_catalog coverage ...... ⚠️ WARN (121 entries missing pivot) [pre-existing]
  H5: Orphan pivots ...... ✅ PASS (0 orphans)
  H6: Duplicate detection ...... ⚠️ WARN (3 duplicates) [pre-existing]
  H7: Count cross-check (real COUNT) ...... ✅ PASS (20/20 match)
RESULT: 5/7 PASS, 2 WARNING, 0 FAIL

Blocker 4: pivot-query.get.ts hardcode VIEW_MAP → CHƯA FIX

Per mission: "KHÔNG SỬA TRONG MISSION NÀY. Cần mission riêng." Registries 2 sẽ đọc trực tiếp meta_catalog virtual rows qua Directus, không dùng pivot-query API.

V1-V5 Verify

# Check Result
V1 PG CAT-ALL.record_count 33,294
V1 PG COUNT(*) managed active atoms 33,294
V1 KHỚP? ✅ KHỚP
V2 fn_refresh_virtual_summaries ENABLED? ✅ (tgenabled = 'O')
V3 Cron refresh running? ✅ (last_scan_date = 2026-03-29 05:21:55)
V4 Cron dot-pivot-health running? ✅ (path fixed, output H1-H7)
V5 Directus API CAT-ALL 33,294
V5 PG = Directus? ✅ KHỚP

Automation Chain (post-fix)

Data changes (100 new atoms)
  → cron */10: refresh_meta_catalog_from_pivot()
    → pivot_count() per collection → UPDATE meta_catalog.record_count
    → TRIGGER trg_refresh_cat_all fires
      → fn_refresh_cat_all() → SUM(managed active [level]) → UPDATE virtual row
    → TRIGGER trg_refresh_virtual_summaries fires
      → fn_refresh_virtual_summaries() → UPDATE ALL 6 virtual rows
  → cron */10: refresh_pivot_results()
    → pivot_results table updated
  → Directus serves updated data automatically
  → Nuxt readItems('meta_catalog') → hiện số MỚI
  → KHÔNG AI LÀM GÌ BẰNG TAY

Files Changed

File Change
dot/bin/dot-pivot-health H7 real COUNT, VPS detect, arithmetic fix
dot/bin/dot-pivot-declare VPS detect for pg_sql
dot/bin/dot-cron-pivot-setup NEW — version-controlled cron management
sql/s146_m4b_fix_virtual_summaries.sql PG migration for fn_refresh_cat_all + fn_refresh_virtual_summaries