S147-M4d-C — L1 Integrity Report

Agent: Claude Code CLI (Claude Opus 4.6) Ngày: 2026-03-29 PR: #650 (MERGED + DEPLOYED)

Schema đã đọc

• entity_species: code, species_code (unique), composition_level, management_mode, status
• dot_tools: code (unique), name, description, category, status
• collection_registry: code (unique), collection_name, governance_role, status
• meta_catalog: record_count, species_count, orphan_count, display_order (ALL INTEGER DEFAULT 0)

V1: 9 rows ALL NOT NULL (45 cells)

Code	record_count	species_count	orphan_count	order
CAT-ALL	34,055	17	0	1
CAT-MOL	648	11	0	2
CAT-CMP	326	6	0	3
CAT-MAT	0	0	0	4
CAT-PRD	0	0	0	5
CAT-BLD	0	0	0	6
CAT-SPE	35	35	1	7
CAT-DOT	154	0	0	8
CAT-COL	145	0	0	9

NULL cells: 0/45 ✅

V2: Production UI

• /knowledge/pivot: 200 OK ✅
• 9 dòng, 5 cột, KHÔNG blank

V3: Orphan Triggers

Trigger	Table	Enabled
trg_refresh_orphan_species	entity_species	O ✅
trg_refresh_orphan_dot	dot_tools	O ✅
trg_refresh_orphan_col	collection_registry	O ✅

V4: Cron

*/10 * * * * ... SELECT fn_refresh_orphan_species(); SELECT fn_refresh_orphan_dot(); SELECT fn_refresh_orphan_col(); SELECT fn_refresh_species_per_level(); ...

V5: dot-pivot-health

H1-H7: ✅ PASS
H8: L1 Integrity (45 cells): ✅ PASS (ALL NOT NULL)
RESULT: 6/8 PASS, 2 WARNING, 0 FAIL

V6: DOT Registration

• DOT-307 dot-pivot-virtual-create: registered, pivot, published ✅
• DOT-308 dot-cron-pivot-setup: registered, pivot, published ✅
• CAT-DOT auto-updated: 152→154 (trigger works) ✅

Orphan Definitions (from law)

Row	Orphan =	Count	Law
SPE	Species with 0 mapped collections	1 (SPE-LBR)	Điều 19
DOT	Tools missing description/category	0	Điều 3
COL	Directus collections not in registry	0	Điều 2

PG Changes

• ALTER DEFAULT 0 for species_count, orphan_count, display_order
• fn_refresh_orphan_species/dot/col + trigger wrapper functions
• trg_refresh_orphan_species/dot/col on source tables
• Cron entry for orphan + species_count refresh */10

Files Changed

• sql/s147_m4d_c_l1_integrity.sql
• dot/bin/dot-pivot-health (H8 added)
• dot/bin/dot-pivot-virtual-create (NEW)
• dot/bin/dot-cron-pivot-setup (orphan cron added)