Dieu 38 P-A Schema Migration Report

Date: 2026-04-02 | Session: S157

EXECUTION LOG

Step	Action	Result
0	CREATE EXTENSION pgcrypto + btree_gist	OK
1a	CREATE nrm_doc_type_config + seed 6 rows	OK
1b	CREATE nrm_approval_rules + seed 3 rows	OK
2a	DROP 3 FK (jurisdiction, governance_registry, enforcement)	OK (in transaction)
3	DROP 4 triggers on law_registry (keep birth)	OK
4a	ADD 14 columns to law_registry	OK
4b	ADD valid_period GENERATED	OK
4c	ALTER council_score REAL→NUMERIC, species_code DEFAULT	OK
4d	DROP old CHECK, ADD 6-value CHECK	OK
4e	SET doc_type NOT NULL (temp default then drop)	OK
4f	FK doc_type → nrm_doc_type_config	OK
4g	FK self-references (parent_code, superseded_by)	OK
5	RENAME law_registry → normative_registry	OK
6	RE-CREATE 3 FK → normative_registry	OK
8	CREATE VIEW law_registry + readonly trigger	OK
COMMIT	Transaction committed	OK
9	CREATE binding_registry	OK
10	CREATE normative_relations	OK
11	RENAME birth trigger	OK
12	CREATE 9 triggers (doc_level_auto, enacted_immutable, parent_level_check, constitution_unique, hash_auto, binding_validate, enacted_must_have_approval, enacted_must_have_enforcement, retire_cascade) + EXCLUDE constraint	OK
13	CREATE 5 indexes (valid_period GiST, status_type, parent, sections GIN, superseded)	OK
14	Register 5 collections (COL-148 updated, COL-154-157 new)	OK

VERIFY RESULTS

#	Check	Expected	Actual	PASS
16a	Columns	27	27	YES
16b	VIEW law_registry	YES	YES	YES
16d	FK on sub-tables	>=3	5	YES
16e	Triggers on normative_registry	~10+	15 registrations	YES
16f	Config seeded	6+3	6+3	YES
16g	EXCLUDE constraint	excl_nrm_temporal	excl_nrm_temporal	YES
16h	Collections registered	5	5	YES
T1	VIEW INSERT blocked	PASS	PASS	YES
T2	Invalid doc_type rejected	PASS	PASS	YES
T3	Invalid FK relation rejected	PASS	PASS	YES
T4	Missing doc_type rejected	PASS	PASS	YES

TRIGGERS CREATED

#	Trigger	Table	Event	Function
1	nrm_doc_level_auto	normative_registry	INSERT/UPDATE(doc_type)	fn_nrm_doc_level_auto
2	nrm_enacted_immutable	normative_registry	UPDATE	fn_nrm_enacted_immutable
3	nrm_parent_level_check	normative_registry	INSERT/UPDATE(parent_code)	fn_nrm_parent_level_check
4	nrm_constitution_unique	normative_registry	INSERT/UPDATE	fn_nrm_constitution_unique
5	nrm_hash_auto	normative_registry	INSERT/UPDATE(sections)	fn_nrm_hash_auto
6	nrm_binding_validate	normative_registry	INSERT/UPDATE(sections)	fn_nrm_binding_validate
7	nrm_enacted_must_have_approval	normative_registry	UPDATE(status)	fn_nrm_enacted_must_have_approval
8	nrm_enacted_must_have_enforcement	normative_registry	UPDATE(status)	fn_nrm_enacted_must_have_enforcement
9	nrm_retire_cascade	normative_registry	UPDATE(status)	fn_nrm_retire_cascade
-	trg_birth_normative_registry	normative_registry	INSERT	fn_birth_registry_auto
-	trg_law_registry_view_block	law_registry (VIEW)	INSERT/UPDATE/DELETE	fn_law_registry_view_readonly
X	excl_nrm_temporal	normative_registry	EXCLUDE	GiST article_number + valid_period

DEFERRED (Phase D / document_instances table)

• Trigger 8 (doc_template_exists) — needs document_instances table
• Trigger 9 (doc_signed_immutable) — needs document_instances table
• Trigger 12 (nrm_shadow_ban) — needs binding_registry data + DOT-NRM-VERIFY experience
• Trigger 14 (nrm_legacy_guard) — needs document_instances table
• Trigger 6 (nrm_version_increment) — deferred to DOT-NRM-AMEND workflow

ISSUES

• First transaction attempt ROLLBACK: $$ delimiter expanded by bash→SSH→docker chain. Fixed with $func$.
• collection_registry INSERT failed first attempt: missing name NOT NULL field + group reserved word. Fixed.

SUMMARY

Metric	Count
Tables created	4 (nrm_doc_type_config, nrm_approval_rules, binding_registry, normative_relations)
Tables altered	1 (law_registry → normative_registry)
Columns added	15 (inc. 1 GENERATED)
Columns modified	3 (council_score type, species_code default, status CHECK)
Triggers created	10 (9 new + 1 VIEW block)
Triggers renamed	1 (birth)
Triggers dropped	4 (replaced by Dieu 38 triggers)
EXCLUDE constraints	1
Indexes created	5
FK updated	3
FK added	3 (doc_type, parent_code, superseded_by)
Collections registered	5 (1 updated, 4 new)
Config rows seeded	9 (6 doc_types + 3 approval_rules)
Negative tests passed	4/4
Views created	1 (law_registry readonly)