KB-173A
PIDX Candidate DDL + Inventory/Readiness View SQL + Rollback v0.1 (text only)
28 min read Revision 1
workflow-manageprocedure-indexpidxddl-candidatesqlinventory-viewreadiness-viewrollbacktext-onlyv0.12026-06-23
PIDX Candidate DDL + View SQL + Rollback v0.1 — TEXT ONLY
Path:
knowledge/dev/laws-new/workflow-manage/design/pidx-ddl-candidate-v0.1.sql.mdStatus: CANDIDATE SQL · TEXT ONLY · NOT EXECUTED · 0 PG objects created. Every statement below is design text for Codex review and later Owner-approved governed build. Nothing here was run. Date: 2026-06-23 · target DB observed:directus(PostgreSQL on VPS) · schemapublicCompanions:pidx-build-design-v0.1.md,pidx-readiness-logic-v0.1.md,pidx-test-plan-v0.1.md.
0. Constraint-strictness decision (Task 3 — justify)
Decision: SOFT constraints for v0.1. Enforce a tight
CHECKon the small, closed enumerations (kinds, levels, modes, classes) but do NOT put a regexCHECKoningredient_ref. Instead carry aref_statusflag + detect malformed refs inv_pidx_procedure_readinessasINVALID_REF.
Why softer-for-refs is safer for the first build:
- Reversibility / liberal writes. A regex
CHECKoningredient_refwould reject the row at write time. The whole philosophy is "declarations are liberal hints; PG computes truth." A bad ref must be recordable (so it can be triaged) and surfaced asINVALID_REFby the computed layer — not bounced at insert. - Grammar churn. Ref grammar is frozen for v0.1 but
function(argtypes), realio/checkerSSOTs, andtemplatesource are explicit v0.2 follow-ups. A hard regex bakes today's grammar into aCHECKthat is awkward to migrate. - Detection belongs in the view.
INVALID_REFis a readiness state, computed alongsideMISSING/UNKNOWN_SOURCE, so all four "not-EXISTS" states live in one place (v_pidx_procedure_readiness) with one precedence — not split between a write-timeCHECKand a query-time view.
What we still enforce hard (cheap, stable, closed sets): ingredient_kind, required_level, usage_role, ref_status, plus declared_maturity, automation_mode, safety_class, status on the procedure table.
1. Candidate table — pidx_procedure
-- CANDIDATE — DO NOT EXECUTE. Governed/owner-approved build only.
CREATE TABLE public.pidx_procedure (
procedure_code text PRIMARY KEY, -- PROC_* (self) ; legacy WF-* indexed via inventory, not stored here
title text NOT NULL,
short_note text,
intent text,
domain_group text NOT NULL DEFAULT 'UNMAPPED', -- controlled tag; 'UNMAPPED' if unknown
assembly_layer text NOT NULL DEFAULT 'UNMAPPED', -- from meta_catalog.composition_level if known, else 'UNMAPPED'
declared_maturity text CHECK (declared_maturity IN
('known','text_ready','machine_readable',
'checklist_ready','dot_sequence_ready','one_button_ready')),
automation_mode text CHECK (automation_mode IN
('manual','checklist','dot_sequence','one_button','agent_assisted')),
safety_class text CHECK (safety_class IN
('read_only','ai_delegatable','owner_gated')),
status text NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','active','retired')),
manifest_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb, -- light structured NOTE/cache only, never truth
source_ref text, -- provenance (KB path / WF-* / DOT chain)
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE public.pidx_procedure IS
'PG-native Procedure Index: one row = one known procedure (DECLARED hints/cache; readiness is computed by v_pidx_procedure_readiness, never stored here).';
COMMENT ON COLUMN public.pidx_procedure.declared_maturity IS
'HINT only. computed_readiness in v_pidx_procedure_readiness overrides; mismatch raises READINESS_DRIFT.';
COMMENT ON COLUMN public.pidx_procedure.manifest_jsonb IS
'Light structured note. NEVER a source of truth and NEVER able to set READY.';
Notes:
assembly_layer/domain_groupdefaultUNMAPPED— the survey proved procedures carry no layer metadata today; they are never a blocker.- No computed columns.
computed_readiness,exists_bool,freshness_statusare produced only by the readiness view at query time.
2. Candidate table — pidx_procedure_ingredient
-- CANDIDATE — DO NOT EXECUTE.
CREATE TABLE public.pidx_procedure_ingredient (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
procedure_code text NOT NULL
REFERENCES public.pidx_procedure(procedure_code)
ON UPDATE CASCADE ON DELETE CASCADE,
step_code text, -- optional step grouping (declared)
ingredient_kind text NOT NULL
CHECK (ingredient_kind IN
('dot','collection','view','field','trigger','function',
'approval','event','procedure','label',
'io','checker','template','report')),
ingredient_ref text NOT NULL, -- normalized ref, grammar v0.1 (declared). NO regex CHECK (see §0)
usage_role text CHECK (usage_role IN
('reads','writes','calls','requires','produces')),
required_level text NOT NULL DEFAULT 'required'
CHECK (required_level IN
('required','optional','nice_to_have','UNKNOWN','NEEDS_TRIAGE')),
source_probe_kind text, -- declared hint: which inventory branch should verify
missing_route_procedure_code text, -- SOFT ref (NOT a FK — see note); where to go if MISSING
ref_status text NOT NULL DEFAULT 'NORMALIZED'
CHECK (ref_status IN ('NORMALIZED','UNNORMALIZED')),
note text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE public.pidx_procedure_ingredient IS
'Ingredient Map: one row = one ingredient a procedure DECLARES it needs. Lazy human/Agent cache; NEVER truth.';
COMMENT ON COLUMN public.pidx_procedure_ingredient.ingredient_ref IS
'Ref grammar v0.1 join key. If un-normalizable, store raw + ref_status=UNNORMALIZED -> readiness yields INVALID_REF.';
COMMENT ON COLUMN public.pidx_procedure_ingredient.missing_route_procedure_code IS
'SOFT reference only. Deliberately NOT a FK: the routed-to procedure may not be seeded yet (routing to "create it" is the point).';
ref_statusis an explicit addition beyond the macro's minimum field list (the macro lists "minimum fields to consider"). It is the write-time normalization flag that feeds theINVALID_REFprecedence in the readiness view (§0 rationale).
missing_route_procedure_codeis intentionally not a FK. A hard FK would make it impossible to declare "if X is missing, go run PROC_CREATE_X" before PROC_CREATE_X exists. The readiness view resolves it as aprocedure:probe and reports whether the route target exists.
3. Candidate indexes
-- CANDIDATE — DO NOT EXECUTE.
-- pidx_procedure(procedure_code) is already the PRIMARY KEY (no extra index needed).
CREATE INDEX pidx_procedure_group_layer_idx
ON public.pidx_procedure (domain_group, assembly_layer); -- treeview: assembly_layer x domain_group
CREATE INDEX pidx_proc_ingr_procedure_idx
ON public.pidx_procedure_ingredient (procedure_code); -- HOT PATH: fetch one procedure's ingredients
CREATE INDEX pidx_proc_ingr_kind_ref_idx
ON public.pidx_procedure_ingredient (ingredient_kind, ingredient_ref); -- reverse lookup: who uses object X
-- OPTIONAL anti-duplicate guard (PG15+ NULLS NOT DISTINCT so a NULL step_code still de-dupes):
-- CREATE UNIQUE INDEX pidx_proc_ingr_uniq
-- ON public.pidx_procedure_ingredient (procedure_code, step_code, ingredient_kind, ingredient_ref) NULLS NOT DISTINCT;
-- Fallback for < PG15:
-- CREATE UNIQUE INDEX pidx_proc_ingr_uniq
-- ON public.pidx_procedure_ingredient (procedure_code, COALESCE(step_code,''), ingredient_kind, ingredient_ref);
The optional unique index is commented out for v0.1 to avoid over-constraining liberal declarations; enable once seeding settles.
4. Candidate view — v_pidx_inventory_current (browsing / treeview; NOT the hot path)
A UNION ALL over EXISTING sources. Illustrative candidate SQL — Codex to syntax-check; some branches are abbreviated where the pattern repeats. Column order matches the contract in pidx-build-design-v0.1.md §4.1.
-- CANDIDATE — DO NOT EXECUTE. Pure read-only view; copies nothing.
CREATE VIEW public.v_pidx_inventory_current AS
-- 1) dot <- dot_tools (authoritative_registry)
SELECT 'dot'::text AS object_kind,
'dot:' || d.code AS object_ref,
d.code AS object_code,
d.name AS object_name,
'public'::text AS source_schema,
'dot_tools'::text AS source_object,
d.code AS source_pk,
true AS exists_bool,
'authoritative_registry'::text AS authority_level,
jsonb_build_object('tier', d.tier, 'status', d.status,
'trigger_type', d.trigger_type, 'domain', d.domain) AS metadata_jsonb,
'fresh'::text AS freshness_status,
d.date_updated AS last_seen_at,
NULL::text AS notes,
ARRAY[]::text[] AS warning_flags
FROM public.dot_tools d
UNION ALL
-- 2) collection <- information_schema.tables (physical) LEFT JOIN directus_collections (logical)
SELECT 'collection',
'collection:' || t.table_schema || '.' || t.table_name,
t.table_name, t.table_name, t.table_schema, 'information_schema.tables',
t.table_schema || '.' || t.table_name, true,
'authoritative_catalog',
jsonb_build_object('physical', true, 'logical', (dc.collection IS NOT NULL)),
'fresh', now(), NULL,
CASE WHEN dc.collection IS NULL THEN ARRAY['LOGICAL_PHYSICAL_MISMATCH']::text[]
ELSE ARRAY[]::text[] END
FROM information_schema.tables t
LEFT JOIN public.directus_collections dc
ON dc.collection = t.table_name
WHERE t.table_type = 'BASE TABLE'
AND t.table_schema NOT IN ('pg_catalog','information_schema')
UNION ALL
-- 3) view <- information_schema.views + matviews (pg_class relkind 'm')
SELECT 'view', 'view:' || v.table_schema || '.' || v.table_name,
v.table_name, v.table_name, v.table_schema, 'information_schema.views',
v.table_schema || '.' || v.table_name, true, 'authoritative_catalog',
jsonb_build_object('relkind','v'), 'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.views v
WHERE v.table_schema NOT IN ('pg_catalog','information_schema')
UNION ALL
SELECT 'view', 'view:' || n.nspname || '.' || c.relname,
c.relname, c.relname, n.nspname, 'pg_class',
n.nspname || '.' || c.relname, true, 'authoritative_catalog',
jsonb_build_object('relkind','m'), 'fresh', now(), NULL, ARRAY[]::text[]
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm' AND n.nspname NOT IN ('pg_catalog','information_schema')
UNION ALL
-- 4) field <- information_schema.columns (HEAVY — exposed, never eagerly scanned; see perf model)
SELECT 'field',
'field:' || c.table_schema || '.' || c.table_name || '.' || c.column_name,
c.column_name, c.column_name, c.table_schema, 'information_schema.columns',
c.table_schema || '.' || c.table_name || '.' || c.column_name, true,
'authoritative_catalog', jsonb_build_object('data_type', c.data_type),
'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.columns c
WHERE c.table_schema NOT IN ('pg_catalog','information_schema')
UNION ALL
-- 5) trigger <- information_schema.triggers
SELECT 'trigger',
'trigger:' || tr.trigger_schema || '.' || tr.event_object_table || '.' || tr.trigger_name,
tr.trigger_name, tr.trigger_name, tr.trigger_schema, 'information_schema.triggers',
tr.trigger_schema || '.' || tr.event_object_table || '.' || tr.trigger_name, true,
'authoritative_catalog', jsonb_build_object('event', tr.event_manipulation),
'fresh', now(), NULL, ARRAY[]::text[]
FROM information_schema.triggers tr
WHERE tr.trigger_schema NOT IN ('pg_catalog','information_schema')
UNION ALL
-- 6) function <- pg_proc (name-only; flag overloads)
SELECT 'function', 'function:' || n.nspname || '.' || p.proname,
p.proname, p.proname, n.nspname, 'pg_proc',
n.nspname || '.' || p.proname, true, 'authoritative_catalog',
jsonb_build_object('overloaded', (count(*) OVER (PARTITION BY n.nspname, p.proname) > 1)),
'fresh', now(), NULL,
CASE WHEN count(*) OVER (PARTITION BY n.nspname, p.proname) > 1
THEN ARRAY['OVERLOADED_FUNCTION']::text[] ELSE ARRAY[]::text[] END
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
UNION ALL
-- 7) approval <- apr_action_types (flag unimplemented handlers)
SELECT 'approval', 'approval:' || a.action_code,
a.action_code, a.description, 'public', 'apr_action_types',
a.action_code, true, 'authoritative_registry',
jsonb_build_object('risk_level', a.risk_level, 'handler_ref', a.handler_ref, 'status', a.status),
'fresh', a.created_at, NULL,
CASE WHEN a.handler_ref IS NULL OR a.handler_ref = 'unimplemented'
THEN ARRAY['APPROVAL_HANDLER_UNIMPLEMENTED']::text[] ELSE ARRAY[]::text[] END
FROM public.apr_action_types a
UNION ALL
-- 8) event <- event_type_registry
SELECT 'event', 'event:' || e.event_domain || '.' || e.event_type,
e.event_type, e.description, 'public', 'event_type_registry',
e.event_domain || '.' || e.event_type, true, 'authoritative_registry',
jsonb_build_object('active', e.active, 'severity', e.default_severity),
'fresh', e.created_at, NULL, ARRAY[]::text[]
FROM public.event_type_registry e
UNION ALL
-- 9) procedure <- pidx_procedure (self) UNION workflows.process_code (legacy BPMN)
SELECT 'procedure', 'procedure:' || pr.procedure_code,
pr.procedure_code, pr.title, 'public', 'pidx_procedure',
pr.procedure_code, true, 'authoritative_registry',
jsonb_build_object('declared_maturity', pr.declared_maturity, 'status', pr.status),
'fresh', pr.updated_at, NULL, ARRAY[]::text[]
FROM public.pidx_procedure pr
UNION ALL
SELECT 'procedure', 'procedure:' || w.process_code,
w.process_code, w.title, 'public', 'workflows',
w.process_code, true, 'authoritative_registry',
jsonb_build_object('source','bpmn'), 'fresh', w.date_updated, NULL, ARRAY[]::text[]
FROM public.workflows w
WHERE w.process_code IS NOT NULL
UNION ALL
-- 10) label <- taxonomy (PARTIAL: facet caveat; flag bare-code multi-facet ambiguity)
SELECT 'label', 'label:' || tx.code,
tx.code, tx.name, 'public', 'taxonomy',
tx.id::text, true, 'authoritative_registry',
jsonb_build_object('facet_id', tx.facet_id, 'depth', tx.depth),
'fresh', tx.date_created, NULL,
CASE WHEN count(*) OVER (PARTITION BY tx.code) > 1
THEN ARRAY['AMBIGUOUS_LABEL']::text[] ELSE ARRAY[]::text[] END
FROM public.taxonomy tx ;
io,checker,template,reportare not branches — they have no clean PG SSOT in v0.1 and resolve toUNKNOWN_SOURCEinside the readiness view, emitting no inventory rows.
5. Candidate view — v_pidx_procedure_readiness (the computed-truth layer)
Grain = one row per procedure (LEFT JOIN ingredients so zero-ingredient procedures still surface as UNMAPPED). A per-ingredient detail array (ingredients jsonb) is embedded for debugging, keeping the core at 2 views. Per-ingredient status precedence and rollup precedence are specified in pidx-readiness-logic-v0.1.md; this is the candidate SQL.
-- CANDIDATE — DO NOT EXECUTE. Pure read-only. Resolver = narrow correlated EXISTS per ref.
CREATE VIEW public.v_pidx_procedure_readiness AS
WITH ingr AS (
SELECT
i.procedure_code,
i.id, i.ingredient_kind AS kind, i.ingredient_ref AS ref, i.ref_status,
i.required_level, i.usage_role, i.missing_route_procedure_code,
-- identifier = everything after the first ':'
substr(i.ingredient_ref, strpos(i.ingredient_ref, ':') + 1) AS ident
FROM public.pidx_procedure_ingredient i
),
resolved AS (
SELECT g.*,
-- structural parseability (cheap guard; full INVALID_REF precedence below)
(strpos(g.ref, ':') = 0 OR length(g.ident) = 0) AS unparseable,
-- catalog parts (only meaningful for CATALOG kinds)
split_part(g.ident, '.', 1) AS p_schema,
split_part(g.ident, '.', 2) AS p_rel,
split_part(g.ident, '.', 3) AS p_col,
-- existence probe per kind (narrow EXISTS)
CASE g.kind
WHEN 'dot' THEN EXISTS (SELECT 1 FROM public.dot_tools d WHERE d.code = g.ident)
WHEN 'collection' THEN EXISTS (SELECT 1 FROM information_schema.tables t
WHERE t.table_schema = split_part(g.ident,'.',1)
AND t.table_name = split_part(g.ident,'.',2))
OR EXISTS (SELECT 1 FROM public.directus_collections dc
WHERE dc.collection = split_part(g.ident,'.',2))
WHEN 'view' THEN EXISTS (SELECT 1 FROM information_schema.views v
WHERE v.table_schema = split_part(g.ident,'.',1)
AND v.table_name = split_part(g.ident,'.',2))
OR EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE c.relkind='m' AND n.nspname=split_part(g.ident,'.',1)
AND c.relname=split_part(g.ident,'.',2))
WHEN 'field' THEN EXISTS (SELECT 1 FROM information_schema.columns c
WHERE c.table_schema = split_part(g.ident,'.',1)
AND c.table_name = split_part(g.ident,'.',2)
AND c.column_name = split_part(g.ident,'.',3))
WHEN 'trigger' THEN EXISTS (SELECT 1 FROM information_schema.triggers tr
WHERE tr.trigger_schema = split_part(g.ident,'.',1)
AND tr.event_object_table= split_part(g.ident,'.',2)
AND tr.trigger_name = split_part(g.ident,'.',3))
WHEN 'function' THEN EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname = split_part(g.ident,'.',1)
AND p.proname = split_part(g.ident,'.',2))
WHEN 'approval' THEN EXISTS (SELECT 1 FROM public.apr_action_types a WHERE a.action_code = g.ident)
WHEN 'event' THEN EXISTS (SELECT 1 FROM public.event_type_registry e
WHERE e.event_domain = split_part(g.ident,'.',1)
AND e.event_type = split_part(g.ident,'.',2))
WHEN 'procedure' THEN EXISTS (SELECT 1 FROM public.pidx_procedure pp WHERE pp.procedure_code = g.ident)
OR EXISTS (SELECT 1 FROM public.workflows w WHERE w.process_code = g.ident)
WHEN 'label' THEN EXISTS (SELECT 1 FROM public.taxonomy tx WHERE tx.code = g.ident)
ELSE false -- io/checker/template/report handled as UNKNOWN_SOURCE below
END AS found,
-- per-kind warnings
CASE WHEN g.kind='function'
AND (SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname=split_part(g.ident,'.',1) AND p.proname=split_part(g.ident,'.',2)) > 1
THEN 'OVERLOADED_FUNCTION' END AS w_fn,
CASE WHEN g.kind='approval'
AND EXISTS (SELECT 1 FROM public.apr_action_types a
WHERE a.action_code=g.ident AND (a.handler_ref IS NULL OR a.handler_ref='unimplemented'))
THEN 'APPROVAL_HANDLER_UNIMPLEMENTED' END AS w_appr,
CASE WHEN g.kind='label' AND strpos(g.ident,'.')=0
AND (SELECT count(*) FROM public.taxonomy tx WHERE tx.code=g.ident) > 1
THEN 'AMBIGUOUS_LABEL' END AS w_label
FROM ingr g
),
status AS (
SELECT r.*,
CASE
WHEN r.ref_status='UNNORMALIZED' OR r.unparseable THEN 'INVALID_REF'
WHEN r.kind IN ('io','checker','template','report') THEN 'UNKNOWN_SOURCE'
WHEN r.kind IN ('collection','view','field','trigger','function')
AND r.p_schema IN ('cutter_governance','sandbox_tac') THEN 'READ_BLOCKED'
WHEN r.found THEN 'EXISTS'
ELSE 'MISSING'
END AS computed_status
FROM resolved r
)
SELECT
p.procedure_code,
p.title,
p.domain_group,
p.assembly_layer,
p.declared_maturity,
p.automation_mode,
p.safety_class,
p.status,
count(s.id) AS ingredient_count,
count(s.id) FILTER (WHERE s.required_level='required'
AND s.computed_status='MISSING') AS req_missing_count,
count(s.id) FILTER (WHERE s.required_level='required'
AND s.computed_status='UNKNOWN_SOURCE') AS req_unknown_count,
count(s.id) FILTER (WHERE s.required_level='required'
AND s.computed_status IN ('INVALID_REF','READ_BLOCKED')) AS req_dataq_count,
count(s.id) FILTER (WHERE s.computed_status='EXISTS') AS exists_count,
-- rollup
CASE
WHEN count(s.id) = 0 THEN 'UNMAPPED'
WHEN count(s.id) FILTER (WHERE s.required_level='required'
AND s.computed_status IN ('INVALID_REF','READ_BLOCKED','MISSING','UNKNOWN_SOURCE')) > 0
THEN 'NOT_READY'
WHEN count(s.id) FILTER (WHERE (s.w_fn IS NOT NULL OR s.w_appr IS NOT NULL OR s.w_label IS NOT NULL)
OR (s.required_level <> 'required'
AND s.computed_status IN ('MISSING','UNKNOWN_SOURCE','INVALID_REF','READ_BLOCKED'))
OR s.required_level IN ('UNKNOWN','NEEDS_TRIAGE')) > 0
THEN 'READY_WITH_WARNINGS'
ELSE 'READY'
END AS computed_readiness,
-- orthogonal drift flag
(p.declared_maturity IN ('checklist_ready','dot_sequence_ready','one_button_ready')
AND CASE
WHEN count(s.id)=0 THEN 'UNMAPPED'
WHEN count(s.id) FILTER (WHERE s.required_level='required'
AND s.computed_status IN ('INVALID_REF','READ_BLOCKED','MISSING','UNKNOWN_SOURCE')) > 0
THEN 'NOT_READY' ELSE 'READY' END IN ('NOT_READY','UNMAPPED')) AS readiness_drift,
-- aggregated distinct warning flags
(SELECT COALESCE(array_agg(DISTINCT w) FILTER (WHERE w IS NOT NULL), ARRAY[]::text[])
FROM (SELECT s.w_fn AS w UNION ALL SELECT s.w_appr UNION ALL SELECT s.w_label) ww) AS warning_flags,
-- per-ingredient detail (debug; keeps core at 2 views)
jsonb_agg(jsonb_build_object(
'kind', s.kind, 'ref', s.ref, 'computed_status', s.computed_status,
'required_level', s.required_level, 'missing_route', s.missing_route_procedure_code,
'warnings', array_remove(ARRAY[s.w_fn, s.w_appr, s.w_label], NULL))
) FILTER (WHERE s.id IS NOT NULL) AS ingredients
FROM public.pidx_procedure p
LEFT JOIN status s ON s.procedure_code = p.procedure_code
GROUP BY p.procedure_code, p.title, p.domain_group, p.assembly_layer,
p.declared_maturity, p.automation_mode, p.safety_class, p.status ;
The aggregated
warning_flagssubselect is illustrative; Codex should confirm the correlated-aggregate form or refactor to aLATERAL. Thereadiness_driftCASE is duplicated for clarity — Codex may hoist it into a CTE column. These are review items, not blockers.
6. Rollback / drop script (Task 9 — text only)
-- CANDIDATE ROLLBACK — DO NOT EXECUTE NOW. Safe order: views before tables, child before parent.
DROP VIEW IF EXISTS public.v_pidx_procedure_readiness;
DROP VIEW IF EXISTS public.v_pidx_inventory_current;
DROP TABLE IF EXISTS public.pidx_procedure_ingredient; -- child (FK to pidx_procedure)
DROP TABLE IF EXISTS public.pidx_procedure; -- parent
-- Indexes drop automatically with their tables. No functions/triggers/sequences created beyond the
-- IDENTITY sequence owned by pidx_procedure_ingredient (dropped with the table).
6.1 Rollback verification queries (read-only)
-- (a) confirm all four pidx objects are absent
SELECT relname, relkind FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND relname IN
('pidx_procedure','pidx_procedure_ingredient','v_pidx_inventory_current','v_pidx_procedure_readiness');
-- expect: 0 rows
-- (b) confirm NO non-pidx object was touched (spot-check the sources the views read)
SELECT count(*) AS dot_tools_rows FROM public.dot_tools; -- expect ~309 (unchanged)
SELECT count(*) AS apr_action_types_rows FROM public.apr_action_types; -- expect 14
SELECT count(*) AS event_types_rows FROM public.event_type_registry; -- expect 40
SELECT count(*) AS workflows_rows FROM public.workflows; -- expect 2
-- (c) confirm source catalogs/tables still readable (no permission/lock damage)
SELECT 1 FROM information_schema.tables LIMIT 1;
SELECT 1 FROM information_schema.columns LIMIT 1;
SELECT 1 FROM pg_proc LIMIT 1;
7. Build-order note (for the eventual governed build, not this macro)
CREATE TABLE pidx_procedure→ 2.CREATE TABLE pidx_procedure_ingredient(FK needs parent first) → 3. indexes → 4.CREATE VIEW v_pidx_inventory_current→ 5.CREATE VIEW v_pidx_procedure_readiness(readspidx_procedure, the resolver, and self-referencespidx_procedure). Seed rows (pidx-seed-slice-v0.1.md) only after the two tables exist. All of this is owner-gated, governed, and out of scope for this design macro.