Proc Fixer

Proc Fixer (scripting flavor)

Repair a single converted mapping procedure — an Informatica mapping converted to a standalone Snowflake Scripting stored procedure — that still contains unsupported-transform stubs (!!!RESOLVE EWI!!!). Each materialized transformation is a block delimited by boundary markers; the fixer reconstructs each defective block's SQL from the source transformation node it was converted from, splices it back, then deploys, CALLs, and grades the procedure against the source-derived assertions until it creates clean and all assertions pass.

The fix is authored from source semantics, not guessed from the broken SQL, and the expected result the fix is graded against is computed by the test generator from the source — so the loop has an independent target.

Scope. This is the data-flow counterpart to orchestration-fixer (which repairs control-flow elements of a task graph) and the scripting-flavor analog of dbt-fixer (which repairs dbt models). In dbt mode a mapping becomes a model repaired by dbt-fixer; in scripting mode a mapping becomes its own stored procedure repaired here. It consumes the assertions authored by proc-test-gen — that pair owns the ARRANGE + ASSERT; this skill owns the ACT (the fix) and drives the repair loop.

Autonomous mode: When spawned as a team agent, skip all interactive stopping points, make reasonable defaults, and document assumptions in the completion report. When done, send_message to main with your results, and respond to any shutdown_request with type: "shutdown_response", approve: true.

Where this sits in the fix loop

block-locate  ── the defective block + its span/body (scripts/stabilization_tools.py block-locate, marker-scoped)
ewi-extract   ── the block's !!!RESOLVE EWI!!! / SSC-* defects (scripts/stabilization_tools.py ewi-extract)
proc-fixer    ── [THIS SKILL] reconstruct the fixed block SQL from the source transformation node
block-replace ── splice the fix back between the markers, integrity-checked (scripts/stabilization_tools.py block-replace)
deploy-and-call ─ deploy the one proc into the test schema + CALL it (scai code deploy-and-call)
grade         ── run the source-derived assertions against the populated target; PASS/FAIL feeds this loop

proc-fixer owns the ACT: for each defective block it authors a minimal, source-faithful replacement, splices it in, and drives the deploy → CALL → grade cycle. The seed (ARRANGE) and the assertions (ASSERT) come from proc-test-gen and are treated as fixed inputs the fix must satisfy — never edited to make a fix pass.

AAA framing

• Arrange — the input seed and the target table shape are already created and seeded by proc-test-gen (<proc_name>.seed.sql). Re-run the seed before each grade to reset to the known state.
• Act — deploy the current procedure into the test schema and CALL it, so its output lands in <test_schema>.<TARGET>. This is what the fix changes.
• Assert — run proc-test-gen's batched target-only assertions (<proc_name>.assert.sql); any non-PASS (or a query that errors) means not-yet-converged.

Input

• session_status.json — with source_file_path, the mapping proc unit, and migration_object_path
• The converted proc .sql file (a mapping unit's converted output, with boundary markers + !!!RESOLVE EWI!!! stubs) — this is the file the fixer edits in place, via block-replace
• The source mapping definition (Informatica .xml) at source_file_path
• The proc-test-gen artifacts under <UNIT>/stabilization/tests/proc/<proc_name>/: <proc_name>.seed.sql, <proc_name>.assert.sql, test_report.md (the baseline result + which assertions failed) — required. If they are absent, tests have not been generated yet: STOP and return to the parent skill to run proc-test-gen first.

Prerequisites

• Python 3.11+; the deterministic tool layer present (scripts/stabilization_tools.py — the block-locate / ewi-extract / block-replace / task-error-locate subcommands — and scripts/scan_unit.py)
• The headless executor available: scai code deploy-and-call (deploy one proc + CALL, returns a ProcCallResult JSON) — see Tooling
• An active Snowflake connection with a warehouse, and CREATE PROCEDURE on the user-provided (test) database + schema that proc-test-gen used
• A backup: ensure <UNIT>/stabilization/original/ holds the pre-fix procedure. The parent skill creates it during setup; create it if missing before the first edit.

Output Location

• The repaired converted .sql — edited in place via block-replace (boundary markers preserved).
• <UNIT>/stabilization/tracking/fix_log.md (Main Mode) — one record per fix attempt, successes and failures. Task Mode writes fix records to its learnings artifact instead; the orchestrator merges them.

Tooling (headless, no IDE / no snowflake_sql_execute)

Like proc-test-gen, this loop is headless — the mechanical steps are deterministic scripts and the deploy/grade steps shell to the executor / snow sql, never the interactive snowflake_sql_execute MCP tool.

block-replace integrity-checks the marker pair and refuses to write if the reconstruction would drop or add a marker; treat a non-zero exit as a failed splice, not a fixed block.

Reconstruction rules

The core of the fix is reconstructing a block from the source transformation node. These rules are non-negotiable; the per-transform recipes are in block-reconstruction-patterns.md.

Author from source, not from the broken SQL.

• Reconstruct only from the source mapping semantics + the block's role. Do NOT invent behavior and do NOT hardcode result rows or mock constants (0, NULL, a literal expected value) to make a grade pass — that is never a valid fix.
• Read the source transformation node behind the block. The broken stub embeds the source <TRANSFORMATION> / <CONNECTOR> XML as comments; use it, and the source .xml, to recover ports, expressions, join/lookup conditions, and group-by keys. See {PLATFORM_DIR}/{transformation_guide} (Informatica: mapping-guide.md) and {PLATFORM_DIR}/element-types.md.

Preserve the block's role and output shape.

• Same INSERT target and column list; same final SELECT column set and order; same block boundary markers.
• Minimal change — keep the surrounding statement, sensible CTE/temp-table names, and any code not related to the defect. Do NOT reformat or rename beyond what the fix requires.

Faithful Informatica semantics (recipes + worked examples in the patterns file):

• Lookup (connected) → LEFT OUTER JOIN to the lookup table; no-match → NULL outputs; no fan-out when the lookup key is unique; honor the multiple-match policy only when the key can duplicate.
• Filter → WHERE <condition>; a NULL predicate is not TRUE, so that row is dropped — do NOT rewrite x != 'Y' as x != 'Y' OR x IS NULL.
• Aggregator → GROUP BY the GROUPBY port(s) with the stated aggregates (SUM/COUNT/AVG/MIN/MAX).
• Joiner → the stated join type (Normal = inner; Master/Detail/Full Outer keep the named side).
• Router → one output per group condition (WHERE), unmatched rows to the default group.
• Rank → window function with QUALIFY, partitioned by the group ports.
• Union → UNION ALL (no dedup unless stated).
• Expression → apply the port expressions in dependency order; translate IIF→IFF, DECODE→CASE.
• Update Strategy → the DD_INSERT/UPDATE/DELETE/REJECT operations against the target.
• Preserve transformation ORDER — e.g. lookup, then filter (which may test a looked-up column), then aggregate. Reordering changes the result.

EWI markers. Remove !!!RESOLVE EWI!!! marker lines when applying the fix (they break compilation). Keep --** SSC-EWI-* / --** SSC-FDM-* one-line comment markers — they are documentation. Replace the entire stub body (marker line + the commented-out source XML below it) with working SQL; do not leave dead commented code.

Workflow

Proc Fixer Progress:
- [ ] Step 1: Analyze & plan — read proc-test-gen report + block-locate the defective blocks; order them
- [ ] Step 2: Reconstruct → splice each defective block (block-scoped, source-derived, minimal change)
- [ ] Step 3: Deploy + CALL (smoke); on error, locate the block and re-reconstruct
- [ ] Step 4: Grade against the assertions; on FAIL, identify the block and re-reconstruct (bounded, then escalate)
- [ ] Step 5: Report (fix log, return to parent skill)

Step 1: Analyze & plan

1. Read test_report.md for the baseline: did the proc deploy/CALL, which assertions failed, and the source→block mapping proc-test-gen recorded. Read session_status.json and the test environment.
2. stabilization_tools.py block-locate --json over the converted .sql to enumerate blocks; stabilization_tools.py ewi-extract to find which blocks carry !!!RESOLVE EWI!!! / structural defects. Those are the work items.
3. Order the defective blocks by data-flow dependency (a block that feeds another is fixed first), so a downstream block is reconstructed against a fixed upstream one.
4. Interactive mode: present the plan (defective blocks, their source transforms, baseline failures) and confirm (⚠️ STOPPING POINT). Autonomous: proceed and document.

Step 2: Reconstruct and splice each defective block

For each defective block, in dependency order:

1. stabilization_tools.py block-locate to read the current block body; read the source transformation node behind it (from the embedded XML + the source .xml).
2. Reconstruct the block SQL per the Reconstruction rules + the patterns file — minimal change, source-faithful, same output shape, no hardcoded rows.
3. Write the reconstruction to a temp file and splice with stabilization_tools.py block-replace. A non-zero exit (marker integrity violation) means the reconstruction is malformed — fix it, do not force the write.
4. stabilization_tools.py ewi-extract on the block to confirm no !!!RESOLVE EWI!!! remains in it.

Step 3: Deploy + CALL (smoke loop)

1. Re-run <proc_name>.seed.sql to reset the input + target state.
2. scai code deploy-and-call … --proc-file <proc.sql> --call "CALL <proc>(...)". Read the ProcCallResult:
   • deployed=false → the proc failed to CREATE (a syntax/structural error). Read errorText.
   • called=false → it errored at CALL. Read errorText.
3. On a deploy/CALL error, map it back to the offending block with stabilization_tools.py task-error-locate --error-file <errfile>, and return to Step 2 for that block, feeding the error text into the reconstruction. This is the inner (compile/smoke) loop; iterate until the proc creates clean and CALLs without error.

Step 4: Grade (outer loop)

1. Run <proc_name>.assert.sql and read the PASS/FAIL rows.
2. All PASS → the procedure is stabilized. Go to Step 5.
3. Any FAIL → identify which block is responsible from the failing assertion (e.g. a wrong total points at the aggregator or the filter; a spurious/missing row points at the lookup/filter order) and return to Step 2 for that block, feeding the failing assertions in as grader feedback.
4. Bounded retry: at most 5 proc-level grade attempts. If still failing, revert the proc to <UNIT>/stabilization/original/, record the failure with the last error/assertion summary, and escalate — do NOT leave a partially-fixed proc or weaken the assertions to force a pass.
5. If the assert query itself ERRORS (not FAIL) — that is a test-generation bug, not a proc bug. Do NOT edit the proc for it; return to the parent skill to have proc-test-gen simplify the assertion.

Step 5: Report

Append a fix record to <UNIT>/stabilization/tracking/fix_log.md (Main Mode) or the learnings artifact (Task Mode):

## Fix Record
- **Unit**: <unit_name>
- **Procedure**: <proc_name>
- **Block(s) fixed**: <FullName>, …
- **Source transform(s)**: <e.g. LKP_Customer (Lookup), FIL_NotStandard (Filter), AGG_ByRegion (Aggregator)>
- **Fix summary**: <what each block was reconstructed to — e.g. "LEFT JOIN → WHERE tier!='STANDARD' → GROUP BY region">
- **Deploy/CALL**: deployed=<bool> called=<bool>
- **Grade**: N of M assertions PASS
- **Attempts**: <k of 5>
- **Outcome**: SUCCESS | FAILED | ESCALATED

Then return to the parent skill.

Do NOT call track_status.py directly. Record statuses in your task artifact; the orchestrator updates session_status.json after fix agents complete.

Reference Files (loaded on-demand)

Task Mode (spawned by orchestrator)

Input (provided at spawn time)

• Unit name + converted proc .sql path (edited in place via block-replace)
• Source mapping .xml path (READ-ONLY)
• The proc-test-gen artifacts (seed / assert / test_report) for this proc
• The defective block FullName(s) to repair
• Test schema (TEST_SCHEMA), database (DATABASE), connection (CONNECTION)
• Skill directory (SKILL_DIR); platform directory (PLATFORM_DIR)
• Run all SQL headless via scai code deploy-and-call + snow sql --format json — no snowflake_sql_execute

Autonomous behavior

• No user questions — reasonable defaults, documented in the report.
• On ambiguity (unclear transform semantics, missing source element): make a best-effort source-faithful reconstruction and document the assumption. If a transform is genuinely non-reconstructable from source (dynamic SQL, external mapplet), revert the block, record it as a coverage gap, and escalate — do NOT fabricate a fix or hardcode the expected rows.
• Respect the bounded retry (5 proc-level grade attempts). Prefer reverting + escalating over an unbounded loop.
• Write the fix record incrementally after each proc completes.

Team protocol

When complete, results return to the orchestrator automatically. Respond to a shutdown_request with send_message type: "shutdown_response", approve: true.