MOWD Phase 1 — Commit Readiness Pack (Branch B)
Branch B — Commit Readiness Pack (Additive EXTEND)
Run only after the recorded Điều 32 approval (doc 01 §9) exists. Channel: ssh contabo → docker exec -i postgres psql -U workflow_admin -d directus. Window: off-peak. Do NOT COMMIT without approval.
This SQL is byte-identical to the GREEN rehearsal (this session) except ROLLBACK is replaced by COMMIT. The VALIDATE CONSTRAINT step is separate (§7).
1. Preflight (read-only; abort if any fails)
-- P1 gate closed
SELECT (fn_iu_gate_verify_closed()->>'all_safe')='true' AS ok_gate;
-- P2 no idle-in-transaction
SELECT count(*)=0 AS ok_no_idle FROM pg_stat_activity WHERE state='idle in transaction';
-- P3 baseline column counts == 17/20/8 (un-extended)
SELECT (SELECT count(*) FROM information_schema.columns WHERE table_name='workflows')=17
AND (SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_steps')=20
AND (SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_step_relations')=8 AS ok_base;
-- P4 approval artifact present
SELECT EXISTS(SELECT 1 FROM approval_requests a JOIN apr_approvals ap ON ap.apr_id=a.id
WHERE a.entity_code='MOWD-PHASE1-EXTEND' AND a.status='approved' AND ap.decision='approve'
GROUP BY a.id HAVING count(*)>=2) AS ok_approved;
-- P5 no name collision with target objects
SELECT count(*)=0 AS ok_no_view FROM information_schema.views WHERE table_name IN ('v_mow_design_workflow','v_mow_design_step');
All five must return true. If ok_approved is false, STOP — there is no authority to commit.
2. Session guards (server-side timeouts — never client-timeout-kill)
SET statement_timeout='60s';
SET lock_timeout='5s';
SET idle_in_transaction_session_timeout='30s';
3. Transaction wrapper (single tx)
BEGIN;
-- workflows +8 (nullable)
ALTER TABLE workflows
ADD COLUMN owner_gov_code varchar, ADD COLUMN design_iu_ref uuid,
ADD COLUMN active_design_version integer, ADD COLUMN freeze_active boolean,
ADD COLUMN freeze_reason text, ADD COLUMN freeze_at timestamptz,
ADD COLUMN freeze_by varchar, ADD COLUMN design_health jsonb;
-- workflow_steps +7 (nullable)
ALTER TABLE workflow_steps
ADD COLUMN step_iu_ref uuid, ADD COLUMN guide_iu_ref uuid, ADD COLUMN dot_ref text,
ADD COLUMN output_table_ref integer, ADD COLUMN event_domain_ref varchar,
ADD COLUMN event_type_ref varchar, ADD COLUMN step_version integer;
-- workflow_step_relations +1 (nullable)
ALTER TABLE workflow_step_relations ADD COLUMN condition_iu_ref uuid;
-- 8 FK NOT VALID (no scan, no rewrite)
ALTER TABLE workflows ADD CONSTRAINT fk_wf_owner_gov FOREIGN KEY (owner_gov_code) REFERENCES governance_registry(code) NOT VALID;
ALTER TABLE workflows ADD CONSTRAINT fk_wf_design_iu FOREIGN KEY (design_iu_ref) REFERENCES information_unit(id) NOT VALID;
ALTER TABLE workflow_steps ADD CONSTRAINT fk_step_iu FOREIGN KEY (step_iu_ref) REFERENCES information_unit(id) NOT VALID;
ALTER TABLE workflow_steps ADD CONSTRAINT fk_step_guide_iu FOREIGN KEY (guide_iu_ref) REFERENCES information_unit(id) NOT VALID;
ALTER TABLE workflow_steps ADD CONSTRAINT fk_step_dot FOREIGN KEY (dot_ref) REFERENCES dot_iu_command_catalog(command_name) NOT VALID;
ALTER TABLE workflow_steps ADD CONSTRAINT fk_step_output_table FOREIGN KEY (output_table_ref) REFERENCES table_registry(id) NOT VALID;
ALTER TABLE workflow_steps ADD CONSTRAINT fk_step_event FOREIGN KEY (event_domain_ref,event_type_ref) REFERENCES event_type_registry(event_domain,event_type) NOT VALID;
ALTER TABLE workflow_step_relations ADD CONSTRAINT fk_rel_condition_iu FOREIGN KEY (condition_iu_ref) REFERENCES information_unit(id) NOT VALID;
-- 2 read-only views (definitions in doc 04 §A)
CREATE OR REPLACE VIEW v_mow_design_workflow AS
SELECT w.id, w.title, w.status, w.process_code, w.owner_gov_code, g.name AS owner_name,
w.design_iu_ref, w.active_design_version, w.freeze_active, w.design_health,
(SELECT count(*) FROM workflow_steps s WHERE s.workflow_id=w.id) AS step_count,
(SELECT count(*) FROM workflow_steps s WHERE s.workflow_id=w.id AND s.step_iu_ref IS NOT NULL) AS bound_step_count
FROM workflows w LEFT JOIN governance_registry g ON g.code=w.owner_gov_code;
CREATE OR REPLACE VIEW v_mow_design_step AS
SELECT s.id, s.workflow_id, s.step_key, s.step_type, s.actor_type, s.title,
s.step_iu_ref, s.guide_iu_ref, s.dot_ref, s.output_table_ref,
s.event_domain_ref, s.event_type_ref, s.step_version,
(s.step_iu_ref IS NOT NULL) AS is_iu_bound
FROM workflow_steps s;
-- COMMIT; <-- uncomment ONLY with approval; otherwise ROLLBACK;
4. In-transaction verify (before COMMIT decision)
SELECT (SELECT count(*) FROM information_schema.columns WHERE table_name='workflows')=25 wf25,
(SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_steps')=27 st27,
(SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_step_relations')=9 rel9,
(SELECT count(*) FROM pg_constraint WHERE conname IN
('fk_wf_owner_gov','fk_wf_design_iu','fk_step_iu','fk_step_guide_iu','fk_step_dot','fk_step_output_table','fk_step_event','fk_rel_condition_iu'))=8 fks8,
(SELECT count(*) FROM v_mow_design_workflow) wf_rows,
(SELECT count(*) FROM v_mow_design_step) step_rows;
Expected: t,t,t,t,2,70. If correct → COMMIT. Else → ROLLBACK.
5. Post-commit verify (fresh connection)
SELECT (SELECT count(*) FROM information_schema.columns WHERE table_name='workflows') wf, -- 25
(SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_steps') st, -- 27
(SELECT count(*) FROM information_schema.columns WHERE table_name='workflow_step_relations') rel, -- 9
(SELECT count(*) FROM pg_constraint WHERE convalidated=false AND conname LIKE 'fk_%') notvalid_fks, -- >=8
(fn_iu_gate_verify_closed()->>'all_safe') all_safe, -- true
(SELECT count(*) FROM pg_stat_activity WHERE state='idle in transaction'); -- 0
6. Rollback script (Điều 30 — full hard reversal, pre-population)
BEGIN;
DROP VIEW IF EXISTS v_mow_design_step;
DROP VIEW IF EXISTS v_mow_design_workflow;
ALTER TABLE workflow_step_relations DROP CONSTRAINT IF EXISTS fk_rel_condition_iu;
ALTER TABLE workflow_steps DROP CONSTRAINT IF EXISTS fk_step_event, DROP CONSTRAINT IF EXISTS fk_step_output_table,
DROP CONSTRAINT IF EXISTS fk_step_dot, DROP CONSTRAINT IF EXISTS fk_step_guide_iu, DROP CONSTRAINT IF EXISTS fk_step_iu;
ALTER TABLE workflows DROP CONSTRAINT IF EXISTS fk_wf_design_iu, DROP CONSTRAINT IF EXISTS fk_wf_owner_gov;
ALTER TABLE workflow_step_relations DROP COLUMN IF EXISTS condition_iu_ref;
ALTER TABLE workflow_steps DROP COLUMN IF EXISTS step_iu_ref, DROP COLUMN IF EXISTS guide_iu_ref, DROP COLUMN IF EXISTS dot_ref,
DROP COLUMN IF EXISTS output_table_ref, DROP COLUMN IF EXISTS event_domain_ref, DROP COLUMN IF EXISTS event_type_ref, DROP COLUMN IF EXISTS step_version;
ALTER TABLE workflows DROP COLUMN IF EXISTS owner_gov_code, DROP COLUMN IF EXISTS design_iu_ref, DROP COLUMN IF EXISTS active_design_version,
DROP COLUMN IF EXISTS freeze_active, DROP COLUMN IF EXISTS freeze_reason, DROP COLUMN IF EXISTS freeze_at, DROP COLUMN IF EXISTS freeze_by, DROP COLUMN IF EXISTS design_health;
COMMIT;
Preferred long-term reversal once columns are populated: forward-only soft-retire (do not DROP populated columns) — null the refs via a governed DOT and version-pin/freeze, per Điều 30.
7. VALIDATE constraints strategy (separate, later, off-peak)
After legacy rows are confirmed clean (all refs NULL at commit; after binding, all refs must resolve), validate one FK at a time to take only a SHARE UPDATE EXCLUSIVE lock, not block writes:
SET lock_timeout='5s';
ALTER TABLE workflows VALIDATE CONSTRAINT fk_wf_owner_gov; -- repeat per FK, one tx each
Do not validate fk_step_event until every step with an event ref has a matching (event_domain,event_type) in event_type_registry. Validate composite FK last.
8. Off-peak guidance
Run during lowest write traffic (workflows/workflow_steps are low-churn; primary risk is contending with Directus DDL/migrations). Confirm no Directus schema apply is in flight (incomex-directus healthy, idle). Each ALTER takes a brief AccessExclusiveLock on its table only — lock_timeout=5s ensures we fail fast rather than queue behind a long txn.
9. Stop conditions
Abort/rollback immediately if: any preflight false; lock_timeout fires (a long txn holds the table — retry later); in-tx verify ≠ expected; fn_iu_gate_verify_closed() not all_safe; any unexpected NOTICE about rewrite. Never kill the client to "stop" — issue ROLLBACK; in-session.
10. Incident plan
If a connection is lost mid-tx: the server rolls the open tx back automatically (no partial DDL persists — DDL is transactional in Postgres). Verify with §5 fresh-connection check; expect un-extended baseline. If an idle-in-transaction backend is observed, terminate it server-side (pg_terminate_backend) — never client-timeout-kill. Re-run preflight before any retry.
11. No-data-rewrite guarantee
All 16 columns are added nullable with no DEFAULT → Postgres records a catalog-only change (no heap rewrite). All 8 FKs are NOT VALID → no validating scan at add time. The 2 views are metadata. Existing 2 workflows / 70 steps / 80 relations rows are physically untouched.
12. Performance risk
- Add-column / add-NOT-VALID-FK: O(1) catalog ops,
AccessExclusiveLockheld for milliseconds per statement. - View creation: metadata only.
- Future
VALIDATEscans: workflows(2)/workflow_steps(70)/relations(80) — trivial today; the strategy is written for when these grow to thousands/10k+ (still seconds; weaker lock). - The two views use correlated subqueries for
step_count/bound_step_count; at thousands of workflows replace with aGROUP BYaggregate join (doc 04 §A note) before exposing to UI.
13. Commit readiness verdict
READY, human-executable. SQL is rehearsed-identical; preflight/verify/rollback/validate all specified. Single blocker: the recorded Điều 32 approval (doc 01). No commit performed by the agent.