KB-2349
Điều 37 P1 Schema + Triggers Report
5 min read Revision 1
Điều 37 Bootstrap P1 — Schema + Triggers Report
Date: 2026-04-02 | Agent: Claude Code | OR v7.34 No PR (infrastructure-only: Directus API + PG triggers via SSH)
SUMMARY
- 6 collections created via Directus Collections API (AP-15 compliant)
- 10 PG triggers deployed via SSH
- 10 PG CHECK + UNIQUE constraints added
- Permissions set: NO DELETE on any collection (Luật Bảo toàn)
- 6 records in collection_registry (COL-148 → COL-153)
- 5 birth triggers deployed (fn_birth_registry_auto + fn_birth_registry_auto_id)
PG CHECK vs Directus Dropdown
| Field | Type | Location |
|---|---|---|
| law_registry.status | PG CHECK (draft/enacted/retired) | Trigger 1a+1b+2 depend |
| law_jurisdiction.coverage_type | PG CHECK (primary/secondary/reference) | Trigger 3 depends |
| law_jurisdiction.status | PG CHECK (active/retired) | Trigger 3 depends |
| governance_registry.status | PG CHECK (active/draft/retired) | Trigger 9 depends |
| governance_relations.source_type | PG CHECK (law/agency) | Trigger 6 depends |
| governance_relations.target_type | PG CHECK (law/agency) | Trigger 6 depends |
| governance_relations.discovery_source | PG CHECK (pg_catalog/manual/dot_scan) | Trigger 6c depends |
| law_registry.category | Directus dropdown | Expandable |
| governance_registry.gov_type | Directus dropdown | Expandable |
| governance_registry.gov_group | Directus dropdown | Expandable |
| governance_registry.output_target | Directus dropdown | Expandable |
| governance_relations.relation_type | Directus dropdown (TAM) | Expandable, needs Desktop confirm |
| law_dot_enforcement.enforcement_role | Directus dropdown | Expandable |
| governance_audit_log.result | Directus dropdown | Expandable |
VERIFY RESULTS
| # | Test | Result | Evidence |
|---|---|---|---|
| V1 | 6 PG tables | PASS | 6 rows in information_schema.tables |
| V2 | 6 Directus collections | PASS | 6 rows in directus_collections |
| V3 | 10 triggers | PASS | 15 rows (some fire INSERT+UPDATE) |
| V4 | Trigger 1a: enacted no enforcement → BLOCK | PASS | EXCEPTION: Luật enacted PHẢI có >=1 DOT enforcement active |
| V4b | Trigger 1b: INSERT enacted → BLOCK | PASS | EXCEPTION: Luật mới PHẢI bắt đầu từ draft |
| V5 | Trigger 2: modify enacted → BLOCK | PASS | EXCEPTION: Enacted law BẤT BIẾN |
| V5b | Trigger 2: same-value update enacted → ALLOW | PASS | No exception on non-content-change update |
| V6 | Trigger 3: 2 primary same domain → BLOCK | PASS | EXCEPTION: Domain governance đã có luật primary active |
| V7 | Trigger 4: fake primary_collection → BLOCK | PASS | EXCEPTION: primary_collection không tồn tại |
| V8 | Trigger 5: fake health_dot → BLOCK | PASS | EXCEPTION: health_dot không tồn tại hoặc không active |
| V9 | Trigger 6a: fake source_code → BLOCK | PASS | EXCEPTION: source_code không tồn tại |
| V10 | Trigger 6c: manual + pg_* → BLOCK | PASS | EXCEPTION: CẤM khai báo manual cho relation vật lý |
| V11 | Trigger 7: fake dot_code → BLOCK | PASS | EXCEPTION: dot_code không tồn tại |
| V12 | Trigger 8: retire law → cascade | PASS | jurisdiction+enforcement status=retired |
| V13 | Trigger 9: retire agency → cascade | PASS | relation status=retired |
| V14 | No DELETE permissions | PASS | 0 delete_perms |
| V15 | 6 collection_registry records | PASS | COL-148 → COL-153 |
| V16 | 5 birth triggers | PASS | trg_birth_* on 5 governed collections |
| V17 | Field meta (searchable/hidden/readonly) | PASS | Spot-check 3 fields all have required meta |
17/17 PASS
NOTES
- relation_type dropdown TAM: Used temporary values (pg_fk, pg_trigger, pg_index, pg_view, depends_on, enforces, manages, inspects, cooperates). KB search timed out. Needs Desktop confirm.
- fn_birth_registry_auto_id(): New function created for collections with auto-increment PK and no code field. Uses table_name:id pattern as entity_code.
- Species mapping: Not yet mapped for 6 new collections. Birth records will have NULL species_code. TD for P2/P3.
- governance_audit_log: No archive_field set (no status lifecycle). Observed role, not governed.
- Trigger 2 (immutable): Checks specific content fields, NOT full row comparison. Allows Directus system field updates.
- P4 DOT-GOV-VERIFY needs: (a) trigger existence check — verify 10 triggers still exist, (b) trigger fire log — check PG error logs for bypassed exceptions.
TD
- TD: Species mapping for 6 new collections
- TD: relation_type enum confirm from Desktop
- TD: DOT-GOV-VERIFY (P4) — Engine 2 for governance system