Add AWS → Oracle AIDP Migrator (Codex plugin) - #109

Thank you for your pull request and welcome to our community! To contribute, please sign the Oracle Contributor Agreement (OCA).
The following contributors of this PR have not signed the OCA:

• PR author: mppotnuru

To sign the OCA, please create an Oracle account and sign the OCA in Oracle's Contributor Agreement Application.

When signing the OCA, please provide your GitHub username. After signing the OCA and getting an OCA approval from Oracle, this PR will be automatically updated.

If you are an Oracle employee, please make sure that you are a member of the main Oracle GitHub organization, and your membership in this organization is public.

Deep review — same engine as #108 (every engine finding there applies here byte-for-byte); Codex-specific: the plugin sits outside plugins/ and is unregistered so codex plugin add cannot resolve it, the MCP server — its only integration surface — dies on a fresh install and hangs on Windows, and the docs are the Claude-oriented personal-repo docs with broken links

@mppotnuru — reviewed by execution against the repo's Codex conventions (both marketplace manifests, ai/codex-plugins/README.md + TESTING.md, all five sibling .codex-plugin/plugin.json files and their 98 SKILL.md frontmatters), the documented install path (pip install -e '.[mcp]'), the MCP server driven over stdio with a real mcp client, a wheel build + non-editable install, and diff -rq against #108. The aws_aidp/, tests/, scripts/, fixtures/, README, TESTING, pyproject, .mcp.json, LICENSE, CHANGELOG and skills/ are byte-identical to #108, and MCP.md here is identical to #108's docs/MCP.md — so the translator, planner, verify, packaging, live-path and content findings in my #108 review (#108 (comment)) all apply to this PR and are not repeated in full below.

Verified working ✔

• Parity holds: the "same tested engine" claim in the body is accurate (diff -rq differs only in .claude-plugin/, commands/, docs/ vs .codex-plugin/, MCP.md); 19/19 tests pass from this tree; the offline fixture pipeline runs (113 assets planned; verify PASS 20 / REVIEW 13 / SKIP 80 / FAIL 0).
• The MCP server design is sound once two fixes land: with mcp pinned below 2 and stdin detached (see below) it initialises (serverInfo aws-aidp-migrator, protocol 2025-11-25), exposes exactly the four documented tools with sane schemas (plan.manifest and migrate.plan_path required; migrate.demo defaults true — a safe offline default), and the full inventory(fixture="demo") → plan → migrate → verify chain ran end-to-end over MCP, all isError=False. The MCP.md tool table matches list_tools() exactly, and its [mcp_servers.aws-aidp] config.toml snippet matches the documented Codex MCP config shape.
• Manifest basics match siblings: .codex-plugin/plugin.json is valid JSON, "mcpServers": "./.mcp.json" and "skills": "./skills/" follow ask-aidp / fusion-autopilot; the SKILL.md frontmatter uses exactly the name/description fields every sibling skill uses; references/verbs.md resolves.
• Secret hygiene: only xxxx placeholders and the synthetic Acme account id; no e-mails or hostnames; LICENSE is standard MIT with the Oracle copyright line the siblings use.

Blockers

1. Wrong location, no registration — codex plugin add oracle-ai-data-platform-workbench-aws-migrator@oracle-aidp-codex has nothing to resolve. All 55 files live under ai/codex-plugins/oracle-ai-data-platform-workbench-aws-migrator/; every existing Codex plugin lives under ai/codex-plugins/plugins/<name>/ and is listed in both .agents/plugins/marketplace.json (./ai/codex-plugins/plugins/<name>) and ai/codex-plugins/.agents/plugins/marketplace.json (./plugins/<name>) with a policy block and category. git diff 88ebf4b..42f6078 -- .agents ai/codex-plugins/.agents ai/codex-plugins/README.md ai/codex-plugins/TESTING.md is empty — neither manifest, the plugin table, the codex plugin add list nor the layout tree knows this plugin. ai/codex-plugins/TESTING.md documents that source.path resolves relative to the marketplace root, so the only documented Codex install path cannot work (the codex CLI is not installed on my machine, so this is established from the manifests, not executed). Fix: move to ai/codex-plugins/plugins/<name>/, add entries to both manifests (policy: {installation: AVAILABLE, authentication: ON_INSTALL}, category: Engineering), add the README row + install line + layout entry and a TESTING.md section.
2. The MCP server cannot start after the documented install. MCP.md/README say pip install -e '.[mcp]'; pyproject declares mcp>=1.2 unbounded, which today resolves to mcp 2.2.0 where mcp.server.fastmcp was removed (renamed MCPServer). aws_aidp/mcp_server.py:18 fails and the except ImportError masks the real error as The MCP SDK is required. Install it with: pip install -e '.[mcp]'. Executed: aws-aidp-mcp and python -m aws_aidp.mcp_server both exit 1 (pip_install.log: Successfully installed … mcp-2.2.0). For Add AWS → Oracle AIDP Migrator (Codex plugin) #109 this is the only Codex integration surface, so the plugin is dead on arrival for any fresh install. Fix: mcp = ["mcp>=1.2,<2; python_version >= '3.10'"] (or port to mcp.server.mcpserver.MCPServer), print the real ImportError, add a smoke test.
3. .mcp.json spawns a bare aws-aidp-mcp that nothing installs for a Codex user. The console script exists only after a pip install from the plugin directory; no skill, hook or README step performs it (README's only install text is git clone <this-repo> + pip install -e ., and the Codex section links a file that doesn't exist — below). Siblings avoid this: ask-aidp uses "cwd": ".", "command": "node", "args": ["./mcp/…"]; engineer-agent and databricks-migrator stage their Python engine from a hooks/session_start.py. Fix: "command": "python", "args": ["-m", "aws_aidp.mcp_server"], "cwd": "." plus a bootstrap skill or SessionStart hook that runs pip install -e "<plugin root>[mcp]" once (or uvx --from <path> aws-aidp-mcp), and Codex install steps in the README.

Majors — Codex-specific (executed)

• Every MCP tool call hangs on Windows. With mcp pinned to 1.30 so the server starts, list_tools succeeds but inventory(fixture="demo") never returns: the spawned python -m aws_aidp.cli … child sat 10+ minutes at 0.03 s CPU / 1 thread, blocked at interpreter start, while the identical command run directly finishes in 2.4 s. Root cause: _run (mcp_server.py:29) uses subprocess.run(capture_output=True) and the child inherits the MCP transport's stdin pipe. Controlled experiment with my own FastMCP server: the plugin's exact call pattern blocked until a 90 s timeout killed it (rc=143); the same call with stdin=subprocess.DEVNULL returned in 0.2 s. With that one-line patch on a copy, all four tools completed. Reproduced via both the pip launcher and python -m; macOS/Linux not tested. Better still: call aws_aidp.cli.main(args) in-process with redirected stdout/stderr — no child, real exit codes.
• README links three files that don't exist in this tree. README.md:57 sends Codex/Cursor users to [docs/MCP.md](docs/MCP.md) — MCP.md was relocated to the plugin root and docs/ doesn't exist here (scripts/build_pr_trees.sh:76-77 copies README verbatim and moves the file without rewriting the link); README.md:66 cp .env.example .env (only demo.env.example ships, with different variables); README.md:145 lists DEMO.md. The rest of README/TESTING is the personal repo's Claude-Code-oriented text: git clone <this-repo>, /plugin marketplace add mppotnuru/aws-aidp-migrator (a private repository per gh api), /aws-aidp-migrator:inventory slash commands that do not exist in Codex, TESTING.md:14 cloning the personal repo, TESTING.md:20 "the only runtime dependency is boto3" (sqlglot is a hard import). Nowhere does the plugin explain codex plugin add …@oracle-aidp-codex, how the aws-aidp-migrator skill is invoked, or that the MCP server is auto-registered by plugin.json. Write a Codex-specific README.
• MCP.md:3-4 claims "OpenAI's Codex CLI has no plugin marketplace" — contradicted by the repository this PR targets (codex plugin marketplace add oracle-samples/oracle-aidp-samples, verified there on codex-cli 0.141.0) and by the PR's own .codex-plugin/plugin.json. MCP.md:38-40 then tells Codex users to hand-edit ~/.codex/config.toml [mcp_servers.aws-aidp], which would register a second aws-aidp server next to the one the plugin manifest already declares. Keep the manual snippet for non-plugin clients (Cursor, Claude Desktop) only.
• plugin.json diverges from all five sibling manifests: every sibling carries an interface block (displayName, shortDescription, longDescription, developerName, category, capabilities, defaultPrompt, mostly websiteURL/privacyPolicyURL/termsOfServiceURL/brandColor #C74634) and four carry homepage + repository. This one has a top-level displayName (the Claude Code field), no interface, no homepage/repository, author "Oracle AI Data Platform", and ships no PRIVACY.md/NOTICE although ai/codex-plugins/README.md:87 says each plugin has license/privacy files (4 of 5 siblings do). The OpenAI manifest reference could not be fetched in usable form, so this is rated against the in-repo convention.
• Shared engine blockers/majors (detail and evidence in the Add AWS → Oracle AIDP Migrator (Claude Code plugin) #108 comment): translated Glue jobs call oidlUtils.widgets.get, an API the live-validated AIDP toolkit does not use for Python (never executed live); the Athena transpiler silently drops array_agg ORDER BY, mistranslates sub-day date_add, integer division, day_of_week, to_hex(sha256), log(b,x), concat_ws NULLs, discards sqlglot Unsupported warnings, truncates multi-statement input and passes unknown functions through as PASS (demo q-1004 and q-1015 are wrong/failing PASSes on Spark 3.5.9); the Glue translator forces .mode("overwrite") and drops partitionKeys; the planner emits a malformed oci:@ns//bucket/… for all 50 tables; <your-oci-namespace> ships inside PASS artifacts; migrate crashes with UnicodeEncodeError on Windows default encoding; fixtures/ is not packaged (non-editable install breaks --fixture demo and the MCP inventory(fixture="demo") tool — executed here too, exit 2); pyproject says Apache-2.0 while LICENSE/plugin.json say MIT (wheel METADATA carries both); live_teardown.py deletes by fixed name with no ownership check; the "verified end-to-end on live AIDP" claim is a hand-written smoke script that never consumes migrator output.

Minors (executed)

• MCP tools report CLI failures as ordinary text with isError=False, stderr merged into stdout and full Python tracebacks with absolute local paths (plan(manifest='nope.json') → 1,488-char traceback); a Codex agent cannot structurally tell success from failure. Raise ToolError/set isError on non-zero exit and validate paths first.
• Relative defaults (inv.json, plan.json, migrated) resolve against the server process cwd, which the Codex user doesn't control unless they add cwd to config.toml: executed with distinct server/client cwds, inv.json landed in the server cwd. Require absolute paths or honour MCP roots.
• migrate(demo=False) is a silent no-op that exits 0 with error=0 and writes a report (runner.py:132-142 marks Athena/Glue planned: live mode not yet wired), while runner.py:7 says live mode "calls AIDP REST via aidp_client/" and the MCP schema exposes demo as a real toggle. Fail fast or drop the parameter until it does something.
• fixture is interpolated into a path unsanitised (inventory(fixture='../../etc') → traversal honoured, exit 2); validate against the bundled list.
• The live scripts hard-depend on the Claude plugin cache path for the sibling's aidp_sql.py (demo.env.example:24, pinned to engineer-agent 0.5.0); the Codex engineer-agent sibling ships that helper at aidp/aidp_sql.py, so a Codex-only user cannot run the PR's "live" path at all.
• CHANGELOG.md describes the Claude tree (.claude-plugin/, commands/*.md, docs/FDE_PRESENTATION.md, docs/MCP.md) — none exist here — and says 15 tests (19); aws-aidp --version prints 0.1.0 (pyproject/plugin.json 0.2.0).
• 5.5K lines duplicated byte-for-byte across the two PRs with no canonical source: the repo's convention for shared content is ai/shared-plugin-content/<plugin>/ + a sync script that copies and filecmp-verifies into both trees (spark-connectors); the only "sync" here is scripts/build_pr_trees.sh, which lives outside the repo workflow, rm -rfs a path under ~/Documents, needs docs/ and .claude-plugin/ (absent from this tree, so it cannot even run from here), and has already produced drift (the docs/MCP.md link). Put the engine under shared-plugin-content with a sync script and keep the two plugin dirs as thin manifests + variant READMEs; drop build_pr_trees.sh from the shipped plugin.

Note

Merge gate you already know about: OCA unsigned (CI red), no Signed-off-by, PR template/issue not used. The 3.7 MB of demo media is (rightly) absent from this tree.

Bottom line: the Codex variant is one marketplace entry, one mcp<2 pin and one stdin=DEVNULL away from working — but today it cannot be installed, cannot start, and would hang on Windows if it did; fix those three, write a Codex README, bring plugin.json in line with the sibling manifests, then pick up the shared-engine items from the #108 review. Happy to re-verify. 🤖 Deep review by Claude (Fable)

Thanks for the exceptionally thorough review — force-pushed a substantial revision that addresses it.

Engine (both PRs): the Athena transpiler was reworked off sqlglot onto a hardened deterministic translator with explicit semantic-gap guards, so the silent-semantics findings (array_agg ORDER BY, sub-day date_add, integer /, day_of_week, to_hex(sha256), log arity, concat_ws NULLs, unknown-function passthrough, multi-statement truncation, sqlglot version drift) are gone — those cases now flag rather than PASS, backed by a corpus + Spark-runtime tests. Glue now emits oidlUtils.parameters.getParameter(...), flags method-form DynamicFrame APIs, and verify PASS requires a compile + unbound-name scan.

Fixed here:

• MCP: mcp>=1.2,<2 (+ py>=3.10 marker); stdin=DEVNULL (Windows hang); self-installing .mcp.json (python -m + ${CLAUDE_PLUGIN_ROOT} / cwd for Codex); real ImportError surfaced.
• Packaging: fixtures/ now under aws_aidp.fixtures (packaged); planner OCI location bug fixed; Windows UnicodeEncodeError fixed (utf-8); collision-safe artifact paths; namespace validated.
• Identity: plugin name/namespace/URLs point at oracle-samples; private-repo + dead slash-command refs removed; broken links fixed.
• Licensing/hygiene: MIT aligned across pyproject/LICENSE/plugin.json; added NOTICE + PRIVACY.md; rclone inline-comment fixed; unsourced market stat + non-AIDP "DCAT" vocabulary removed; presenter kit, live scripts, build tooling and demo media removed from the shipped tree; no internal names/OCIDs.
• Process: Signed-off-by on all commits (OCA green); registered in the plugin catalog / marketplace manifests.

Remaining follow-ups called out honestly: one live run of a translated Glue job on a cluster to promote the widgets mapping from flagged→rewrite, and the shared-plugin-content dedup between the two trees. Happy to iterate. 🙏

Codex-specific: moved under ai/codex-plugins/plugins/, registered in both .agents/plugins/marketplace.json manifests + the README table; added a Codex-specific README (no Claude slash-command text); .mcp.json launches python -m aws_aidp.mcp_server with cwd: "."; MCP tool calls no longer hang on Windows (stdin=DEVNULL).

Update (additive commit, no force-push): the "verify PASS is a status relabel" major is now addressed.

Root cause was exactly as you found — when the SQL side moved off sqlglot to regex rules, it lost the free parse-validation the transpiler gave for granted (parse_failed finding count went 1 → 0). Restored it as explicit output-validation gates in athena_to_spark_sql.translate(), mirroring the ast-parse gate the Glue translator already had:

• multi-statement, unrecognised/unbalanced statement, and 10 Presto-only constructs Spark rejects (WITH RECURSIVE, FETCH FIRST, CAST AS JSON, CTAS WITH(...), > ANY(...), AT TIME ZONE, TABLESAMPLE, PREPARE/EXECUTE, UNLOAD) → now flag → REVIEW instead of PASS.
• A Spark-3.5 builtins allowlist so unknown functions are caught rather than passed through.
• Verdict wording made honest across report.html / verbs.md / README: PASS = "translated, no known issue detected — not execution-verified."
• Adversarial 20-query junk manifest that previously returned PASS 20/20 (incl. CREATE EXTERNAL TABLE … s3://… byte-identical) now flags correctly; added test_output_validation.py + test_verdict_claims.py.

Also included the earlier Spark-3.5.3 translator fixes (JOIN-after-UNNEST, 1-based subscripts, scoped .spark_session). Full stress suite green. Thanks again — happy to re-verify.