orchestration-fixer

name	orchestration-fixer
parent_skill	etl-stabilization
description	Fix orchestration elements with EWI issues using test-file workbench. Scoped to elements assigned to the current phase. Use when the stabilization skill invokes fixing for an orchestration phase.
license	Proprietary. See License-Skills for complete terms

Orchestration Fixer

Fix orchestration SQL files generated by SnowConvert. These files contain Snowflake task graphs (CREATE TASK) and stored procedures (CREATE PROCEDURE) that replicate ETL control flow.

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

Main Mode (interactive / standalone use)

Rules

• Read the reference file for an EWI/FDM code BEFORE attempting a fix
• Use ---- Start / ---- Start block / ---- End block tags to locate elements, NEVER line numbers. Non-container elements (---- Start) have no closing tag — they end at the next tag. Container elements (---- Start block) have a matching ---- End block.
• Do NOT stop after every element — flow autonomously, pause only when genuinely blocked or needing user input
• Do NOT remove --** SSC-FDM-* / --** SSC-EWI-* comment markers — they are documentation
• DO remove !!!RESOLVE EWI!!! marker lines when applying a fix — they break compilation
• Keep the fix log updated after every fix attempt, including failures
• Record element statuses in your task artifact — the orchestrator updates session_status.json
• Valid output statuses: test-passed, test-failed, skipped, needs-user, auto-fixed-needs-review, failed. Do NOT invent new statuses. Elements with no-fix-needed stay unchanged.

Input

This reference expects:

• session_status.json — from track_status.py init (current status of each element; may include test_environment config if set via set-test-env)
• ROADMAP.md — phase plan with element assignments (authored by stabilization)
• The orchestration .sql file path (from scan results)
• Test files at <UNIT>/stabilization/tests/orchestration/<task_procedure_name>/<element_name>.sql (generated by orchestration-test-gen)
• <UNIT>/stabilization/tests/orchestration/test_report.md with baseline results

Workflow

Copy this checklist and track your progress:

Orchestration Fixer Progress:
- [ ] Step 1: Analyze and plan (read session + ROADMAP, scope to current phase, build fixing plan, get user confirmation)
- [ ] Step 2: Fix using test files (skip completed, skip dbt elements, fix each element via test-file workbench, apply proven fixes)
- [ ] Step 3: Report (present summary, return to parent skill)

Pre-Fix Context

Before starting fixes, review the Enriched Baseline Report (at {PHASES_DIR}/baseline_batch_{B}.md or in your prompt):

• Elements with baseline PASS → skip (status: no-fix-needed)
• Elements with baseline FAIL → these are your work items
• Clone annotations → process archetypes before their clones within this task
• Failure details → use these to guide your initial fix analysis

This baseline was generated by the test-gen agent. Test files are already on disk. Do NOT regenerate test files — use them as your TDD workbench.

Step 1: Analyze and Plan

1. Read session_status.json to understand the package structure, which elements are pending, and the test environment config
2. Read ROADMAP.md for the current phase number and its goals
3. Read session_status.json and filter elements where phase == <PHASE_NUM>. Only fix elements in this phase's scope. Ignore elements assigned to other phases.
4. Read test_report.md to understand the baseline test results (which assertions failed and why)
5. Build a fixing plan — prioritized list of statements (only those containing in-scope elements):
   • Simple single-element statements first (easier to validate)
   • Multi-element container statements after
   • Statements with only EXECUTE DBT PROJECT elements last (mark as skipped/dbt-dependency)
6. Present a summary to the user showing:
   • Current phase number and scope
   • Total statements and elements in this phase
   • Issues by code (e.g., "12 x SSC-FDM-0007, 2 x SSC-EWI-SSIS0002")
   • Baseline test failures (from test_report.md)
   • Recommended order
7. ⚠️ STOPPING POINT (interactive mode): Ask user to confirm or adjust the plan. If running as an autonomous agent, skip — proceed with the generated plan and document assumptions in the completion report.

Context efficiency: session_status.json is your input for building a fix plan. Read it once in Step 1 — don't re-read it per element in Step 2. Record statuses in your task artifact, not via track_status.py.

Batch fixing strategy: Group elements by EWI code in Step 1. Process all elements sharing the same code consecutively — this lets you build familiarity with the pattern and cross-reference prior fixes in {UNIT}/stabilization/tracking/fix_log.md. Read the orchestration SQL once at the start of Step 2 and build a mental index by ---- Start tags.

Step 2: Fix Using Test Files as Workbench

Skip-completed check: Before processing each element, check its status in session_status.json. Skip elements with status test-passed, auto-fixed-needs-review, or no-fix-needed. This enables idempotent re-entry if a phase is retried after partial completion.

Clone Element Handling

If the element is marked as a clone in the ROADMAP task section:

1. Read the archetype's fix from the learnings artifact (learnings_batch_{B}.md) — the archetype was processed first in this same batch
2. Understand what differs between archetype and clone (documented in ROADMAP: scope swap, table name change, file path substitution, etc.)
3. Adapt the archetype's fix with reasoning about the differences — NOT a mechanical copy
4. Update the ACT section in the clone's test file with the adapted fix
5. Execute: ARRANGE:SEED → ACT → ASSERT (batched UNION ALL format)
6. If assertions PASS:
   • Record as clone fix in task artifact and learnings
   • Status: test-passed
   • Note in learnings: clone-applicable: YES
7. If assertions FAIL:
   • ESCALATE: this element is not a true clone
   • Treat it as a regular element — full TDD reasoning from scratch
   • Note in learnings: "Clone escalation: fingerprint insufficient for {element_name}"
   • Continue with normal fix workflow (analyze, fix, test, iterate max 3 cycles)

Exception — disabled elements: disabled-in-source elements still require code cleanup — replace the commented-out XML/C# body with a clean one-liner (step 1 below). Elements with dbt-dependency or external-dependency skip reasons are fully skipped.

Ensure a backup exists before editing. The parent skill creates <UNIT>/stabilization/original/ during setup; create it if missing.

After user confirms, mark all EXECUTE DBT PROJECT elements as skipped with reason dbt-dependency in your task artifact, then work through statements autonomously.

Read the Statement: Locate CREATE TASK/CREATE PROCEDURE by name (not line number — lines shift after edits).

Fix Each Element (identified by ---- Start tags):

1. Disabled in source — if disabled-in-source in status or element body is commented-out XML with !!!RESOLVE EWI!!!. Replace the entire element body with:

   ---- Start '<element_name>'
   -- Disabled in source package. Original element removed.
   

   Update status skipped --reason disabled-in-source. Continue to next element.

2. EXECUTE DBT PROJECT — mark skipped --reason dbt-dependency. Continue.

3. Identify issues — look for !!!RESOLVE EWI!!! markers and --** SSC-* comments

4. Check prior fixes (cross-learning) — search {UNIT}/stabilization/tracking/fix_log.md for entries matching **EWI/FDM**: <ISSUE_CODE> (all entries are successful fixes — the file only records patterns that worked). If prior fixes exist for the same issue code:

   • Read their Fix pattern and After fields to understand what approaches have worked before
   • Assess whether the same approach applies to THIS element — the same EWI/FDM code can cover very different scenarios (e.g., SSC-EWI-SSIS0004 covers ScriptTask, ForEachLoop, and other control flow elements)
   • If the element is structurally identical to a prior fix (same element type, same pattern), you can apply the same approach — but still verify with the test file
   • If the element differs, use the prior fix as context to inform your reasoning, then proceed to step 5

   Prior fixes are learning material, not templates to blindly apply. Always analyze the concrete element.

5. Read the reference file (when needed) — for each issue code, see reference/ewi/.md (e.g., reference/ewi/SSC-FDM-0007.md). Follow the decision tree and fix patterns documented there. For unfamiliar element types, see {PLATFORM_DIR}/element-types.md. Skip if the element is structurally identical to a prior fix you already analyzed and you're confident the same approach applies.

## Fix Record
- **Unit**: <unit_name>
- **File**: <orchestration_file_path>
- **Statement**: <statement_name>
- **Element**: <element_name>
- **Issue Code**: <SSC-CODE>
- **Issue Description**: <description from marker>
- **Fix Applied**: <what you changed>
- **Test Result**: N of M assertions passed
- **Outcome**: SUCCESS | FAILED | NEEDS-USER
- **Timestamp**: <ISO timestamp>

---

Post-Fix Check: After all fixable elements in the statement are addressed, check for remaining !!!RESOLVE EWI!!! markers in the statement. If any remain, go back and address them — these break compilation.

Step 3: Report

After all statements in this phase are processed:

1. Read session_status.json to verify all phase elements have terminal statuses.