-- ============================================================================= -- File 07: Đ43 v1.2 rev 3 — FDW lookup optimization (TD-S178-20) -- Approach: Local cache table in incomex_metadata + sync trigger on source dot_config -- via postgres_fdw bidirectional write. -- HOT PATH (kb_documents INSERT): trigger reads LOCAL table (~100us, no FDW) -- COLD PATH (dot_config UPDATE): trigger pushes via FDW to remote (~5ms, rare) -- Benefit: 20x speedup on kb_documents INSERT while keeping runtime-configurable. -- Spec: Đ43 v1.2 rev 3 §11 Bước 12 P11 (runtime config) — preserve semantics -- Run as: workflow_admin (superuser — extension + foreign server + DEFAULT PRIVILEGES) -- Idempotent: YES (DO blocks + CREATE OR REPLACE + IF NOT EXISTS patterns) -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude CLI, S178 Fix 12 Phase 1.6 -- Date: 2026-04-17 -- =============================================================================

---

-- PART A — incomex_metadata: local cache table + updated trigger function

---

\c incomex_metadata

CREATE TABLE IF NOT EXISTS _cp_patterns_cache ( id INT PRIMARY KEY DEFAULT 1 CHECK (id = 1), patterns JSONB NOT NULL DEFAULT '[]'::jsonb, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );

COMMENT ON TABLE _cp_patterns_cache IS 'Đ43 v1.2 Phase 1.6 V3 FDW optimize — local cache of dot_config.context_pack_watched_key_patterns. Single row (id=1). Synced via trigger on source directus.dot_config. Read hot path by fn_context_pack_on_law_enact.';

-- Grant readonly role SELECT (via DEFAULT PRIVILEGES granted in Phase 1.5) -- Explicit grant just in case table created after DEFAULT PRIVILEGES was set GRANT SELECT ON _cp_patterns_cache TO context_pack_readonly;

-- Seed from foreign dot_config ONE-TIME (if empty or patterns changed) INSERT INTO _cp_patterns_cache(id, patterns, updated_at) SELECT 1, value::jsonb, now() FROM dot_config WHERE key = 'context_pack_watched_key_patterns' ON CONFLICT (id) DO UPDATE SET patterns = EXCLUDED.patterns, updated_at = EXCLUDED.updated_at WHERE _cp_patterns_cache.patterns <> EXCLUDED.patterns;

-- Trigger function — read from LOCAL cache (hot path optimized) CREATE OR REPLACE FUNCTION fn_context_pack_on_law_enact() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_catalog AS $func$ DECLARE p TEXT; v_patterns JSONB; BEGIN SELECT patterns INTO v_patterns FROM _cp_patterns_cache WHERE id = 1;

IF v_patterns IS NULL OR jsonb_array_length(v_patterns) = 0 THEN RAISE WARNING 'fn_context_pack_on_law_enact: _cp_patterns_cache empty — run sync trigger fn_sync_cp_patterns_to_meta or manual refresh'; RETURN NEW; END IF;

FOR p IN SELECT jsonb_array_elements_text(v_patterns) LOOP IF NEW.key LIKE p THEN PERFORM pg_notify('context_pack_event', json_build_object('kind','law_enact','key',NEW.key,'pattern',p)::text); RETURN NEW; END IF; END LOOP; RETURN NEW; END; $func$;

ALTER FUNCTION fn_context_pack_on_law_enact() OWNER TO workflow_admin;

COMMENT ON FUNCTION fn_context_pack_on_law_enact() IS 'Đ43 v1.2 rev 3 Phase 1.6 V3 — reads LOCAL _cp_patterns_cache (hot path ~100us). Runtime-configurable via trg_sync_cp_patterns_to_meta on directus.dot_config.';

---

-- PART B — directus: postgres_fdw back to incomex_metadata + sync trigger

---

\c directus

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

DO $srv$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_foreign_server WHERE srvname='incomex_meta_srv') THEN CREATE SERVER incomex_meta_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'incomex_metadata', port '5432'); END IF; END $srv$;

DO $um$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_user_mappings WHERE srvname='incomex_meta_srv' AND usename='workflow_admin') THEN CREATE USER MAPPING FOR workflow_admin SERVER incomex_meta_srv OPTIONS (user 'incomex'); END IF; IF NOT EXISTS (SELECT 1 FROM pg_user_mappings WHERE srvname='incomex_meta_srv' AND usename='directus') THEN CREATE USER MAPPING FOR directus SERVER incomex_meta_srv OPTIONS (user 'incomex'); END IF; END $um$;

DROP FOREIGN TABLE IF EXISTS _cp_patterns_cache_remote; CREATE FOREIGN TABLE _cp_patterns_cache_remote ( id INT, patterns JSONB, updated_at TIMESTAMPTZ ) SERVER incomex_meta_srv OPTIONS (schema_name 'public', table_name '_cp_patterns_cache');

COMMENT ON FOREIGN TABLE _cp_patterns_cache_remote IS 'Đ43 v1.2 Phase 1.6 V3 — sync target. Trigger fn_sync_cp_patterns_to_meta UPDATEs via this FT when dot_config.context_pack_watched_key_patterns changes.';

-- Sync function: fired on directus.dot_config UPDATE/INSERT CREATE OR REPLACE FUNCTION fn_sync_cp_patterns_to_meta() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_catalog AS $sync$ BEGIN IF NEW.key = 'context_pack_watched_key_patterns' THEN UPDATE _cp_patterns_cache_remote SET patterns = NEW.value::jsonb, updated_at = now() WHERE id = 1; END IF; RETURN NEW; END; $sync$;

ALTER FUNCTION fn_sync_cp_patterns_to_meta() OWNER TO workflow_admin;

COMMENT ON FUNCTION fn_sync_cp_patterns_to_meta() IS 'Đ43 v1.2 Phase 1.6 V3 — sync dot_config.context_pack_watched_key_patterns to incomex_metadata._cp_patterns_cache via FDW on UPDATE. P11 runtime config preserved (immediate propagation).';

-- Attach trigger idempotent (DO block avoids DROP xung đột event guard) DO $attach$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='trg_sync_cp_patterns' AND NOT tgisinternal) THEN CREATE TRIGGER trg_sync_cp_patterns AFTER INSERT OR UPDATE OF value ON dot_config FOR EACH ROW EXECUTE FUNCTION fn_sync_cp_patterns_to_meta(); END IF; END $attach$;

---

-- Force-sync current value (in case seed happened BEFORE sync trigger installed)

---

DO $force$ DECLARE v TEXT; BEGIN SELECT value INTO v FROM dot_config WHERE key='context_pack_watched_key_patterns'; IF v IS NOT NULL THEN UPDATE _cp_patterns_cache_remote SET patterns = v::jsonb, updated_at = now() WHERE id = 1; END IF; END $force$;

---

-- Post-check

---

\c directus

SELECT 'directus_fdw_to_meta=' || CASE WHEN EXISTS(SELECT 1 FROM pg_foreign_server WHERE srvname='incomex_meta_srv') THEN 'YES' ELSE 'NO' END; SELECT 'sync_trigger_attached=' || CASE WHEN EXISTS(SELECT 1 FROM pg_trigger WHERE tgname='trg_sync_cp_patterns' AND NOT tgisinternal) THEN 'YES' ELSE 'NO' END;

\c incomex_metadata

SELECT 'cache_table_exists=' || CASE WHEN EXISTS(SELECT 1 FROM pg_tables WHERE tablename='_cp_patterns_cache') THEN 'YES' ELSE 'NO' END; SELECT 'cache_row_count=' || COUNT(*) FROM _cp_patterns_cache; SELECT 'cache_patterns=' || patterns::text FROM _cp_patterns_cache WHERE id=1;