The 5-Phase Pipeline

Every 3xcode pyspark convert run (single file or bulk directory) moves each SQL file through five distinct phases. Each phase is its own fresh AI engine call, chained together by a runner that maps them onto the CLI's progress UI as four stages: Analysis (Discovery + Planning), Conversion, Validation, and Audit (Auto-Fix). Between Planning and Conversion sits one free, deterministic grouping step (no LLM call) that decides how objects get bundled into output files.

Phase 1: Discovery

Discovery turns one raw SQL file into a structured inventory of every convertible object (procedures, functions, views, triggers) with exact line boundaries and metadata. Files over 4,000 lines are read in two chunks so large scripts don't blow the context window, and the agent prescans with a lightweight regex tool before reading the full file.

Platform detection covers 10 vendors: Databricks, Snowflake, PostgreSQL, MySQL, SQL Server, Oracle, Redshift, BigQuery, Teradata, and Hive. It never blocks discovery: if it fails, detection is simply skipped and the pipeline continues.

For each object, discovery records its type, exact start_line/end_line, parameters, cross-references to other objects, temp tables, cursor / dynamic-SQL / transaction flags, window-function count, a complexity score (Simple → Very Complex), known limitations, and the specific SQL constructs it uses, the same tags that later drive selective knowledge injection in Planning.

Discovery is deliberately honest rather than silently degrading: if the model's JSON response partially fails validation, every object that does validate on its own is salvaged rather than the whole result being discarded. A run ends in one of three states: OK (objects found), EMPTY (a clean, genuinely-nothing-to-convert file, e.g. DML-only, or already Spark SQL), or FAILED (unparseable / processing error). A failed discovery is treated as a hard pipeline failure with an honest audit, never a silent green "completed."

Phase 2: Planning

Planning reads every discovered object once and distills targeted, per-object conversion instructions, so the Conversion phase never needs the full knowledge base re-shipped to it. This alone saves 60–80% of input tokens versus re-sending knowledge on every conversion call.

The dependency-tier ordering matters in practice: a procedure that selects from a view needs that view's PySpark signature to exist (or at least be known) before it can be correctly converted or validated. If Claude's plan fails to parse, a fallback builds a trivial single-level sequential plan covering every object, so the pipeline degrades gracefully instead of dying.

A deterministic, no-LLM grouping step then runs between Planning and Conversion, bundling objects into output files by production-style rules: utilities (pure functions + simple procs called by 2+ other objects), main_pipeline (the highest-complexity procedure plus its exclusive helpers), and standalone (everything else, independently schedulable jobs).

Phase 3: Conversion

Conversion turns each output group into one production-quality .py file. Groups are processed in strict dependency tiers: utilities (0) → standalone (1) → main_pipeline (2), with groups within the same tier converting concurrently, but tiers always running in order.

A signature registry is threaded across tiers: after each tier finishes, every successfully converted group's public function signatures are extracted and added to the registry, snapshotted before the next tier starts. This means dependent code always sees the real signatures of what it calls, never a guess. It's the same dependency-ordering principle from Planning, applied at the output-file level.

Phase 4: Validation

Every generated file goes through a three-stage, context-aware review:

Real gotcha categories the pipeline actively checks for, pulled from the knowledge base:

Only relevant knowledge sections are pulled into the prompt based on which constructs (date functions, window functions, NULL handling, string ops, cursors, CTEs, JSON, type casts, stored procs, MERGE, temp tables, joins, aggregations) are actually detected in the SQL, cutting a simple SELECT+JOIN prompt by roughly 81%.

Phase 5: Auto-Fix

Every validation issue is first classified into an action bucket: infrastructure (needs external setup, never auto-fixed), manual (cursors, dynamic SQL, transactions, MERGE, recursive queries; no safe automatic transform, HIGH/ERROR only), auto_fix (any other HIGH/ERROR issue, eligible for a fix pass), or review (below HIGH/ERROR severity, surfaced for awareness, never touched).

Only files with at least one auto_fix-eligible issue get a fresh fix-pass call (tools limited to Read and Write). Claude fixes only the listed issues, preserves TODOs/docstrings/blank lines exactly, and explicitly skips anything unsafe rather than guessing.

Every issue across every file finally lands in one of four developer-facing buckets: auto_fixed, requires_manual (with prescriptive fix guidance, e.g. "rewrite as a window function using F.lag()/F.lead()"), infrastructure_setup, and recommended_review, each with a priority level, drawn from a lookup table of roughly 20 known patterns. Every output file also gets a per-file confidence/completion grade (A+ through F) so you know at a glance which files need a human look before they ship.

Your Code Never Leaves Your Machine

This is a genuine architectural boundary, not a policy promise. 3xcode splits cleanly into two data paths, and your SQL source code is only ever on one of them.

Here's the flow that makes this true: the 3XDE backend supplies the conversion prompts and the SQL-to-PySpark knowledge base (gotchas, mapping tables, limitations) at runtime. Nothing ships inside the installed package, which is also how the proprietary conversion logic stays protected. But once those prompts and knowledge are assembled locally, the CLI sends your SQL source directly to the LLM provider (Anthropic, AWS Bedrock, Google Vertex AI, or Azure AI Foundry) from your own machine. The 3XDE backend is never in that request path. It handles auth, licensing, and usage metadata; it does not proxy, log, or ever see your source code.

You can see this boundary directly in how failures are handled: if the backend is unreachable, the CLI first tries an encrypted local cache of the last-fetched prompts, and only if that fallback also fails does it give up, raising an honest PromptUnavailableError and writing an audit explaining why, rather than silently doing something it can't stand behind.

# 1. CLI authenticates + fetches license/usage metadata from the 3XDE backend
# 2. CLI resolves conversion prompts + knowledge base from the 3XDE backend
#    (falls back to an encrypted local cache if unreachable; fails honestly if both fail)
# 3. CLI reads your .sql file (this stays on your machine)
# 4. CLI sends the SQL + assembled prompt DIRECTLY to your LLM provider
#    (Anthropic / Bedrock / Vertex / Azure, never routed through 3XDE)
# 5. Generated PySpark is written to your local output directory

3xcode pyspark convert your_file.sql