Branch F — MOW Design Registry Validation Harness

Read-only SQL pack. Two profiles: pre-EXTEND (checks runnable today, design columns absent) and post-EXTEND (full checks once doc 02 is committed). All SELECT only; no COMMIT. Surfaced via dot_mow_design_validate (per-workflow) and a system-wide health roll-up.

---

1. Pre-EXTEND baseline (RUN THIS SESSION, read-only) — results

WITH orphan_wf AS (SELECT w.id FROM workflows w WHERE NOT EXISTS (SELECT 1 FROM workflow_steps s WHERE s.workflow_id=w.id)),
     orphan_steps AS (SELECT s.id FROM workflow_steps s WHERE NOT EXISTS (SELECT 1 FROM workflow_step_relations r WHERE r.from_step_id=s.id OR r.to_step_id=s.id)),
     dup_pcode AS (SELECT process_code FROM workflows WHERE process_code IS NOT NULL GROUP BY process_code HAVING count(*)>1),
     dup_stepkey AS (SELECT workflow_id, step_key FROM workflow_steps GROUP BY workflow_id, step_key HAVING count(*)>1),
     long_text AS (SELECT id FROM workflow_steps WHERE length(coalesce(description,''))>256 OR length(coalesce(trigger_in_text,''))>256 OR length(coalesce(trigger_out_text,''))>256)
SELECT (SELECT count(*) FROM orphan_wf) orphan_workflows,
       (SELECT count(*) FROM orphan_steps) orphan_steps,
       (SELECT count(*) FROM dup_pcode) dup_process_codes,
       (SELECT count(*) FROM dup_stepkey) dup_step_keys,
       (SELECT count(*) FROM long_text) steps_long_inline_text;

Live result (2026-05-29): orphan_workflows=0, orphan_steps=0, dup_process_codes=0, dup_step_keys=0, steps_long_inline_text=0. Clean baseline — no structural debt blocks the EXTEND.

2. The 10 acceptance checks (post-EXTEND)

#	Check	SQL (post-EXTEND)	PASS
C1	Every active workflow has owner_gov_code	SELECT count(*) FROM workflows WHERE status='active' AND owner_gov_code IS NULL	=0
C2	Every active workflow has active_design_version	... WHERE status='active' AND active_design_version IS NULL	=0
C3	Every active workflow can be frozen (freeze fields well-formed)	... WHERE status='active' AND freeze_active IS NULL (default false on activate)	=0
C4	Every step has step_iu_ref OR documented exception	SELECT count(*) FROM workflow_steps s JOIN workflows w ON w.id=s.workflow_id WHERE w.status='active' AND s.step_iu_ref IS NULL AND s.step_type NOT IN ('parallel')	=0 (exceptions logged)
C5	Every step dot_ref exists in catalog	SELECT count(*) FROM workflow_steps s WHERE s.dot_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dot_iu_command_catalog c WHERE c.command_name=s.dot_ref)	=0
C5b	dot_ref test_passed where required (mutating DOT on active design)	join to DOT test status (Điều 35 paired-test); flag mutating dot_ref without passing test	=0
C6	Every event ref resolves	SELECT count(*) FROM workflow_steps s WHERE s.event_domain_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM event_type_registry e WHERE e.event_domain=s.event_domain_ref AND e.event_type=s.event_type_ref)	=0
C7	No trigger name collision	see §3	=0
C8	No orphan workflow (no steps)	SELECT count(*) FROM workflows w WHERE NOT EXISTS (SELECT 1 FROM workflow_steps s WHERE s.workflow_id=w.id)	=0 (live: 0)
C9	No hardcoded long text in active design	SELECT count(*) FROM workflow_steps s JOIN workflows w ON w.id=s.workflow_id WHERE w.status='active' AND (length(coalesce(s.description,''))>256 OR length(coalesce(s.trigger_in_text,''))>256 OR length(coalesce(s.trigger_out_text,''))>256)	=0 (live: 0)
C10	Rollback/freeze policy present	SELECT count(*) FROM workflows WHERE status='active' AND (freeze_active IS NULL OR active_design_version IS NULL)	=0

3. Trigger name collision check (C7)

Design "triggers" here = step event refs and step_key uniqueness (the live trigger_registry 107 rows are DB-triggers, a separate namespace — collision avoided by construction). Check intra-workflow step_key uniqueness + event-ref ambiguity:

-- duplicate step_key within a workflow (live: 0)
SELECT workflow_id, step_key, count(*) FROM workflow_steps GROUP BY workflow_id, step_key HAVING count(*)>1;
-- a step binding to an event domain/type not in registry (post-EXTEND)
SELECT s.id, s.event_domain_ref, s.event_type_ref FROM workflow_steps s
 WHERE s.event_domain_ref IS NOT NULL AND NOT EXISTS
   (SELECT 1 FROM event_type_registry e WHERE e.event_domain=s.event_domain_ref AND e.event_type=s.event_type_ref);

4. Dangling-reference sweep (post-EXTEND, the 🔴 source for UI traffic-lights)

SELECT 'design_iu' k, count(*) n FROM workflows w WHERE w.design_iu_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM information_unit i WHERE i.id=w.design_iu_ref)
UNION ALL SELECT 'step_iu', count(*) FROM workflow_steps s WHERE s.step_iu_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM information_unit i WHERE i.id=s.step_iu_ref)
UNION ALL SELECT 'guide_iu', count(*) FROM workflow_steps s WHERE s.guide_iu_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM information_unit i WHERE i.id=s.guide_iu_ref)
UNION ALL SELECT 'condition_iu', count(*) FROM workflow_step_relations r WHERE r.condition_iu_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM information_unit i WHERE i.id=r.condition_iu_ref)
UNION ALL SELECT 'dot_ref', count(*) FROM workflow_steps s WHERE s.dot_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dot_iu_command_catalog c WHERE c.command_name=s.dot_ref)
UNION ALL SELECT 'output_table', count(*) FROM workflow_steps s WHERE s.output_table_ref IS NOT NULL AND NOT EXISTS (SELECT 1 FROM table_registry t WHERE t.id=s.output_table_ref);
-- all must be 0

5. Per-workflow validation roll-up (backs dot_mow_design_validate)

SELECT w.id, w.process_code, w.status,
  (w.owner_gov_code IS NOT NULL) has_owner,
  (w.active_design_version IS NOT NULL) has_version,
  sc.step_count, sc.bound, (sc.bound=sc.step_count) fully_bound,
  CASE WHEN w.owner_gov_code IS NULL OR sc.bound<sc.step_count THEN 'RED'
       WHEN w.active_design_version IS NULL THEN 'YELLOW' ELSE 'GREEN' END light
FROM workflows w
JOIN (SELECT workflow_id, count(*) step_count, count(*) FILTER (WHERE step_iu_ref IS NOT NULL) bound
      FROM workflow_steps GROUP BY workflow_id) sc ON sc.workflow_id=w.id
ORDER BY w.process_code;

6. DAG integrity (defensive, runnable now)

-- relations referencing a step not in the same workflow
SELECT r.id FROM workflow_step_relations r
 JOIN workflow_steps fs ON fs.id=r.from_step_id JOIN workflow_steps ts ON ts.id=r.to_step_id
 WHERE fs.workflow_id<>r.workflow_id OR ts.workflow_id<>r.workflow_id;
-- self-loops
SELECT id FROM workflow_step_relations WHERE from_step_id=to_step_id;

7. Harness packaging

Ship as fn_mow_design_validate(workflow_id int DEFAULT NULL) returning JSON of checks (NULL = system-wide). Register as read DOT dot_mow_design_validate. Wire to UI Surface 2/4 traffic-lights. Run §1 + §6 today (read-only) as a pre-commit gate; run §2–§5 post-EXTEND and after each migration batch.

8. Validation harness verdict

Defined and partially executed. Pre-EXTEND checks RUN live → all clean (0/0/0/0/0). Post-EXTEND checks (C1–C10), dangling sweep, per-workflow roll-up, DAG integrity all specified as runnable SQL. No mutation.