Incomex AI Portal
KB-3A82

S178 Fix30 Gen Code Adaptive Report

13 min read Revision 1
reports178fix30gen_codesequencev_registry_countstd-closed

S178 Fix30 — gen_code adaptive width + sequence align + v_registry_counts insert verify

Date: 2026-04-23 Executor: Codex Scope: Production PostgreSQL on Contabo /opt/incomex/docker, DB directus, user directus.

Bước 0 — 3 câu Tuyên ngôn

  1. Vĩnh viễn? Có. Root fix nằm ở toàn bộ gen_code_* trigger functions: width = GREATEST(_min_width, length(_next::text)), không tăng hardcode từ 3 lên 4/7.
  2. Nhầm được không? Khó nhầm hơn. Sequence được căn bằng actual max từ dữ liệu thật; future nextval không quay lại code đã cấp.
  3. 100% tự động? Có cho code mới. Trigger tự sinh code theo sequence; không cần operator sửa width khi vượt 999/9999.

Luật đã đọc

  • .claude/skills/incomex-rules.md: 36 mục, quy trình 8 bước.
  • search_knowledge("operating rules SSOT"): tìm thấy knowledge/dev/ssot/operating-rules.md trong context-pack; query đầu bị lỗi transport, retry thành công.
  • search_knowledge("hiến pháp v4.0 constitution"): trả context architecture/constitution và các luật Điều 14/15/16.
  • search_knowledge("Luật Registry 3 chữ số tối thiểu code prefix PREFIX-NNN gen_code"): đọc knowledge/dev/laws/law-02-registry.md — ID duy nhất PREFIX-NNN do hệ thống cấp.
  • search_knowledge("law-01-foundation-principles SSOT tự động DOT dual-trigger metadata code"): đọc Điều 35 DOT Governance v5.2 và related governance context.

1. TRƯỚC KHI SỬA — toàn bộ gen_code_* function body

Evidence query:

SELECT p.oid::regprocedure AS signature, p.proname, p.prosrc
FROM pg_proc p
JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname='public' AND p.proname LIKE 'gen_code_%'
ORDER BY p.proname;

Pre-state bodies all used fixed-width lpad(nextval(...)::text, 3, '0') except these fixed-width 4 variants: gen_code_entity_dependencies, gen_code_system_issues, gen_code_workflow_steps.

gen_code_agents: NEW.code := 'AGT' || '-' || lpad(nextval('seq_agents_code')::text, 3, '0');
gen_code_checkpoint_instances: NEW.code := 'CPI' || '-' || lpad(nextval('seq_checkpoint_instances_code')::text, 3, '0');
gen_code_checkpoint_sets: NEW.code := 'CPS' || '-' || lpad(nextval('seq_checkpoint_sets_code')::text, 3, '0');
gen_code_checkpoint_types: NEW.code := 'CP' || '-' || lpad(nextval('seq_checkpoint_types_code')::text, 3, '0');
gen_code_collection_registry: NEW.code := 'COL' || '-' || lpad(nextval('seq_collection_registry_code')::text, 3, '0');
gen_code_dot_tools: NEW.code := 'DOT' || '-' || lpad(nextval('seq_dot_tools_code')::text, 3, '0');
gen_code_entity_dependencies: NEW.code := 'DEP' || '-' || lpad(nextval('seq_entity_dependencies_code')::text, 4, '0');
gen_code_meta_catalog: NEW.code := 'CAT' || '-' || lpad(nextval('seq_meta_catalog_code')::text, 3, '0');
gen_code_modules: NEW.code := 'MOD' || '-' || lpad(nextval('seq_modules_code')::text, 3, '0');
gen_code_system_issues: NEW.code := 'ISS' || '-' || lpad(nextval('seq_system_issues_code')::text, 4, '0');
gen_code_table_proposals: NEW.code := 'TP' || '-' || lpad(nextval('seq_table_proposals_code')::text, 3, '0');
gen_code_table_registry: NEW.table_id := 'TBL' || '-' || lpad(nextval('seq_table_registry_code')::text, 3, '0');
gen_code_tasks: NEW.code := 'TSK' || '-' || lpad(nextval('seq_tasks_code')::text, 3, '0');
gen_code_taxonomy: NEW.code := 'LBL-' || lpad(nextval('seq_taxonomy_code')::text, 3, '0');
gen_code_trigger_registry: NEW.code := 'TRG-' || lpad(nextval('seq_trigger_registry_code')::text, 3, '0');
gen_code_ui_pages: NEW.code := 'PG' || '-' || lpad(nextval('seq_ui_pages_code')::text, 3, '0');
gen_code_workflow_change_requests: NEW.code := 'WCR' || '-' || lpad(nextval('seq_workflow_change_requests_code')::text, 3, '0');
gen_code_workflow_steps: NEW.code := 'ND' || '-' || lpad(nextval('seq_workflow_steps_code')::text, 4, '0');
gen_code_workflows: NEW.process_code := 'WF' || '-' || lpad(nextval('seq_workflows_code')::text, 3, '0');

Preflight INSERT evidence before fix:

BEGIN
WARNING: Birth Gate [meta_catalog]: Code không trùng: Code "CAT-999" đã tồn tại;
ERROR: duplicate key value violates unique constraint "idx_meta_catalog_meta_catalog_code_unique"
DETAIL: Key (code)=(CAT-999) already exists.

2. TRƯỚC KHI SỬA — sequence last_value vs actual max

seq_name|last_value|actual_max|diff
seq_agents_code|10|6|4
seq_checkpoint_instances_code|121|121|0
seq_checkpoint_sets_code|2|2|0
seq_checkpoint_types_code|36|31|5
seq_collection_registry_code|175|175|0
seq_dot_tools_code|308|317|-9
seq_entity_dependencies_code|357|345|12
seq_meta_catalog_code|998|1010|-12
seq_modules_code|4|5|-1
seq_system_issues_code|3912|3912|0
seq_table_proposals_code|1|0|1
seq_table_registry_code|1|0|1
seq_tasks_code|12|17|-5
seq_taxonomy_code|510|506|4
seq_trigger_registry_code|89|86|3
seq_ui_pages_code|38|37|1
seq_workflow_change_requests_code|3|3|0
seq_workflows_code|4|2|2
seq_workflow_steps_code|70|70|0

3. TRƯỚC KHI SỬA — v_registry_counts relation + trigger body

v_registry_counts is not a view in current production; it is a table:

relation | v_registry_counts
relkind  | r
kind     | table

Columns relevant to TD-S178-21:

id | is_nullable=NO | data_type=integer | default=nextval('v_registry_counts_id_seq'::regclass)
cat_code | nullable YES
entity_type | nullable YES
record_count | default 0
orphan_count | default 0
composition_level | default ''::text
code_column | default ''::text
active_count | default 0

Trigger on meta_catalog:

tgname: trg_auto_sync_registry_counts
table_name: meta_catalog
function_name: fn_auto_sync_v_registry_counts
trigger_def: CREATE TRIGGER trg_auto_sync_registry_counts AFTER INSERT ON public.meta_catalog FOR EACH ROW EXECUTE FUNCTION fn_auto_sync_v_registry_counts()

Function body:

BEGIN
  IF NEW.identity_class != 'managed' OR NEW.code IS NULL THEN
    RETURN NEW;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM v_registry_counts WHERE cat_code = NEW.code) THEN
    INSERT INTO v_registry_counts (cat_code, entity_type, record_count, orphan_count, composition_level, code_column, prev_count, count_b, count_c, cross_check, active_count)
    VALUES (
      NEW.code,
      COALESCE(NEW.entity_type, ''),
      COALESCE(NEW.record_count, 0),
      COALESCE(NEW.orphan_count, 0),
      COALESCE(NEW.composition_level, ''),
      'code',
      0,
      COALESCE(NEW.record_count, 0),
      0,
      'NEW',
      COALESCE(NEW.active_count, 0)
    );
    RAISE NOTICE 'Auto-added % to v_registry_counts', NEW.code;
  END IF;

  RETURN NEW;
END;

Conclusion before fix: TD-S178-21 symptom was not current id NOT NULL view DDL; id has a default. The observed INSERT block was caused by seq_meta_catalog_code generating duplicate CAT-999.

4. SAU KHI SỬA — function body mới

Applied in one production transaction:

  • CREATE OR REPLACE FUNCTION for all 19 gen_code_*.
  • ALTER SEQUENCE seq_table_proposals_code MINVALUE 0 and seq_table_registry_code MINVALUE 0 so empty tables can have last_value=actual_max=0 while next code still starts at 001.
  • setval() for all 19 sequence values to actual max.

Canonical new body pattern:

DECLARE _next bigint; _min_width int := 3;
BEGIN
  IF NEW.code IS NULL OR NEW.code = '' THEN
    _next := nextval('seq_<name>_code');
    NEW.code := '<PREFIX>-' || lpad(_next::text, GREATEST(_min_width, length(_next::text)), '0');
  END IF;
  RETURN NEW;
END;

Variants retain existing field/prefix:

gen_code_table_registry uses NEW.table_id and prefix TBL-
gen_code_workflows uses NEW.process_code and prefix WF-
gen_code_taxonomy uses btrim(NEW.code) and prefix LBL-
gen_code_trigger_registry uses btrim(NEW.code) and prefix TRG-

Verification output:

VERIFY 1: gen_code fixed-width lpad scan
proname                             | has_fixed_lpad | has_adaptive_width
gen_code_agents                     | f              | t
gen_code_checkpoint_instances       | f              | t
gen_code_checkpoint_sets            | f              | t
gen_code_checkpoint_types           | f              | t
gen_code_collection_registry        | f              | t
gen_code_dot_tools                  | f              | t
gen_code_entity_dependencies        | f              | t
gen_code_meta_catalog               | f              | t
gen_code_modules                    | f              | t
gen_code_system_issues              | f              | t
gen_code_table_proposals            | f              | t
gen_code_table_registry             | f              | t
gen_code_tasks                      | f              | t
gen_code_taxonomy                   | f              | t
gen_code_trigger_registry           | f              | t
gen_code_ui_pages                   | f              | t
gen_code_workflow_change_requests   | f              | t
gen_code_workflow_steps             | f              | t
gen_code_workflows                  | f              | t
(19 rows)

Sequence after fix:

seq_name|last_value|actual_max|diff
seq_agents_code|6|6|0
seq_checkpoint_instances_code|121|121|0
seq_checkpoint_sets_code|2|2|0
seq_checkpoint_types_code|31|31|0
seq_collection_registry_code|175|175|0
seq_dot_tools_code|317|317|0
seq_entity_dependencies_code|345|345|0
seq_meta_catalog_code|1010|1010|0
seq_modules_code|5|5|0
seq_system_issues_code|3912|3912|0
seq_table_proposals_code|0|0|0
seq_table_registry_code|0|0|0
seq_tasks_code|17|17|0
seq_taxonomy_code|506|506|0
seq_trigger_registry_code|86|86|0
seq_ui_pages_code|37|37|0
seq_workflow_change_requests_code|3|3|0
seq_workflows_code|2|2|0
seq_workflow_steps_code|70|70|0

5. SAU KHI SỬA — test tràn qua 999

Direct meta_catalog insert with setval(999) cannot commit because CAT-1000 already exists under HP NT1. Dry-run before rollback produced duplicate on CAT-1000, proving generation is not truncated.

Clean trigger-level overflow test used a temp table with gen_code_meta_catalog, then reset sequence to actual max before rollback because PostgreSQL sequence changes are non-transactional:

VERIFY 3: overflow through 999 via gen_code_meta_catalog on temp trigger table
BEGIN
CREATE TABLE
WARNING: [TRIGGER-GUARD] DDL detected: CREATE TRIGGER on s178_tmp_meta_code_bi on pg_temp.s178_tmp_meta_code
CREATE TRIGGER
 forced_seq
------------
        999
(1 row)

INSERT 0 1
 generated_code
----------------
 CAT-1000
(1 row)

 reset_seq
-----------
      1010
(1 row)

ROLLBACK

6. SAU KHI SỬA — test INSERT meta_catalog

Actual rollback INSERT into meta_catalog at actual max, then reset sequence before rollback:

VERIFY 4: meta_catalog INSERT rollback at actual max
BEGIN
INSERT 0 1
 id  |   code   |                name
-----+----------+-------------------------------------
 239 | CAT-1011 | S178 Fix30 post-fix insert rollback
(1 row)

 reset_seq
-----------
      1010
(1 row)

ROLLBACK

Backward compatibility:

VERIFY 5: backward compatibility existing codes
code
--------
CAT-001
CAT-100
CAT-1000
CAT-1010
(4 rows)

7. KẾT LUẬN — TD status

  • TD-S178-20 CLOSED. All 19 code sequences aligned to actual max; diff=0 for every checked sequence.
  • TD-S178-21 CLOSED. meta_catalog INSERT no longer blocked; current v_registry_counts is a table with id default and trigger insert path works. No view DDL change was necessary because root failure was duplicate generated code.
  • TD-S178-22 CLOSED. All 19 gen_code_* functions use adaptive width. No fixed lpad(..., 3, ...) or lpad(..., 4, ...) remains.

Remaining TD: _min_width int := 3 is still in function body. This satisfies current Điều 2 minimum-three rule and prompt acceptance, but HP NT13 would be stronger if _min_width came from dot_config. Proposed follow-up TD: move code minimum width to PG config table and use one shared helper function for all code generation.

Git/Repo note

DB production was changed directly through PostgreSQL transaction. /opt/incomex and /opt/incomex/dot were clean before DB work; no source file changed yet. A migration SQL should be committed to both repos if git history must mirror the production DB hotfix.