-- ============================================================================= -- File 02: Prereq dot_config table (generic utility, not Đ43-specific) -- Target DB: directus -- Run as: directus (owns DB) -- Idempotent: YES (IF NOT EXISTS + ON CONFLICT + CREATE OR REPLACE) -- Spec: Đ35 v5.1 §4.1 KHOẢN 1B (fn_is_in_grace_period uses dot_config) -- Đ43 v1.2 §5.9 (7 Đ43 keys seed in File 03) -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude CLI, S178 Fix 12 Phase 1.5 -- Date: 2026-04-17 -- Order: Run FIRST (before File 03 INSERT seeds) -- =============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS dot_config ( key TEXT PRIMARY KEY, value TEXT NOT NULL, description TEXT, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );

COMMENT ON TABLE dot_config IS 'Generic key-value config. value is TEXT — callers cast to ::jsonb/::int/::timestamptz as needed. Đ35 v5.1 §4.1 KHOẢN 1B + Đ43 v1.2 §5.9.';

CREATE OR REPLACE FUNCTION fn_dot_config_touch() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$;

-- Idempotent CREATE TRIGGER via DO block (PG không support IF NOT EXISTS cho CREATE TRIGGER; -- dùng DO vì DROP+CREATE xung đột event trigger fn_evt_trigger_guard_drop trong cluster này) DO $guard$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='trg_dot_config_touch' AND NOT tgisinternal) THEN CREATE TRIGGER trg_dot_config_touch BEFORE UPDATE ON dot_config FOR EACH ROW EXECUTE FUNCTION fn_dot_config_touch(); END IF; END $guard$;

COMMIT;

-- Post-check SELECT 'dot_config_table=' || CASE WHEN EXISTS(SELECT 1 FROM pg_tables WHERE tablename='dot_config' AND schemaname='public') THEN 'YES' ELSE 'NO' END AS result; SELECT 'dot_config_touch_trigger=' || CASE WHEN EXISTS(SELECT 1 FROM pg_trigger WHERE tgname='trg_dot_config_touch') THEN 'YES' ELSE 'NO' END AS result;