Add AWS → Oracle AIDP Migrator (Claude Code plugin) - #108

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 — the offline pipeline, tests, fixture and secret hygiene are solid; but the plugin cannot be installed or run as shipped, both translators silently change semantics in routine cases, and the "verified live end-to-end" claim is not backed by migrator output

@mppotnuru — thank you for a substantial contribution; the AWS half of the migration story is genuinely needed and the deterministic-first design is the right instinct. This review was done by execution, not by reading: the 19 tests, the four-verb fixture pipeline, claude plugin validate, a wheel build + non-editable install into a fresh venv, the MCP server driven over stdio, a 113-case Presto battery whose Spark output was executed on PySpark 3.5.9 (the AIDP kernel's Spark minor), 25 hostile Glue jobs, botocore-Stubber runs of the inventory, a recording fake for the live seed/teardown scripts, and every binary in docs/ (pptx/pdf text layers, all 31 video + 9 gif frames viewed). The aws_aidp/, tests/, scripts/, fixtures/, README, TESTING and pyproject trees are byte-identical to #109, so every engine finding below applies to both PRs; #109 gets its own comment for the Codex-specific items.

Verified working ✔

• Tests and offline pipeline: pytest tests → 19 passed (8 Athena + 8 Glue + 3 S3, matching the body); inventory --fixture demo → plan → migrate --demo → verify runs clean from a neutral cwd (plan 113 assets; migrate ok=20 needs_review=13 planned=80 error=0; verify PASS 20 / REVIEW 13 / SKIP 80 / FAIL 0); 18 of the 20 translated Athena queries analyse and execute on Spark 3.5.9.
• Honest flagging where it exists: unparseable SQL is flagged parse_failed and returned byte-identical; histogram() and UNNEST … WITH ORDINALITY are flagged as documented (POSEXPLODE alias order verified on Spark); ApplyMapping/ResolveChoice/… .apply forms are flagged and left in place; the Glue translator is idempotent on all 8 fixture jobs and all 25 hostile cases; receiver aliasing (ctx/glue_context/glueContext), multi-line balanced-paren argument lifting and the s3://→oci:// rewrite (f-strings, %-templates, s3a://) work.
• Secret hygiene: no OCIDs, keys, e-mails, hostnames or account ids anywhere (68 regex hits reviewed, all placeholders/synthetic); the fixture is fully synthetic (account 999900001111, 0 PII-like literals) and its builder is deterministic apart from a timestamp; pptx/pdf metadata clean; the mp4/gif are rendered cards, not screen recordings — no console, tenancy or path appears in any frame; .gitignore covers demo.env, .aidp/, .env.
• Manifest validity: claude plugin validate passes (one warning: category belongs in marketplace.json); command frontmatter and mcpServers: "./.mcp.json" are documented forms; the SKILL description is action-oriented; the generated rclone job uses rclone copy (never sync/move) with real flags and a temp config, so nothing moves until the user runs it.
• HTML report escapes user text (<script>/<img onerror> in asset names and SQL come out as entities); CLI exit codes are consistent (migrate 1 on error, verify 1 on FAIL / 2 on missing input).

Blockers

1. The MCP server is dead on a fresh install. pyproject declares mcp>=1.2 with no upper bound; today that resolves to mcp 2.2.0, where mcp.server.fastmcp no longer exists. aws_aidp/mcp_server.py:18 fails to import and the except ImportError rewrites it into The MCP SDK is required. Install it with: pip install -e '.[mcp]' — i.e. it tells the user to run the command they just ran. Executed: pip install -e '.[mcp]' → mcp 2.2.0 → aws-aidp-mcp and python -m aws_aidp.mcp_server both exit 1. The server works only with mcp 1.x (verified: handshake + 4 tools on 1.26/1.30). Fix: mcp>=1.2,<2 (or port to mcp.server.mcpserver.MCPServer), surface the real ImportError, add a smoke test that imports the module and lists tools.
2. Nothing installs the CLI for a plugin user, so every command and the MCP server fail after /plugin install. .mcp.json spawns a bare aws-aidp-mcp (exit 127 on a clean PATH; Claude Code installs Node deps only, never pip); the plugin never references ${CLAUDE_PLUGIN_ROOT} (0 hits); SKILL.md:16 says pip install -e . (assumes the cwd is the repo — a marketplace plugin lives under ~/.claude/plugins/cache/…) "or pip install aws-aidp-migrator" — that package does not exist on PyPI (404). Counter-proof that the fix is viable: PYTHONPATH=<plugin root> python -m aws_aidp.mcp_server handshakes and lists the four tools. Fix: .mcp.json → {"command":"python","args":["-m","aws_aidp.mcp_server"],"env":{"PYTHONPATH":"${CLAUDE_PLUGIN_ROOT}"}}, document a one-time pip install "${CLAUDE_PLUGIN_ROOT}[mcp]" (or a SessionStart hook like the databricks-migrator), and have the commands fall back to python -m aws_aidp.cli. Related: fixtures/ is not packaged (packages.find include=["aws_aidp*"], path resolved as a sibling of the package) — a wheel/non-editable install breaks --fixture demo and the MCP inventory(fixture="demo") tool (executed: exit 2 fixture not found: …site-packages\fixtures\…).
3. The plugin still carries its personal-repo identity, and that repo is private. README.md:47-48 installs from /plugin marketplace add mppotnuru/aws-aidp-migrator + /plugin install aws-aidp-migrator@aws-aidp-migrator — gh api reports that repository private: true (anonymous fetch 404), and aws-aidp-migrator is not the name in plugin.json (oracle-ai-data-platform-workbench-aws-migrator). Plugin commands are namespaced by plugin name, so commands/inventory.md:14, plan.md:14, migrate.md:17 and README.md:51 tell Claude to suggest /aws-aidp-migrator:plan|migrate|verify — dead slash commands handed to the user after every verb. plugin.json author.url/homepage/repository and marketplace.json owner point at the personal repo (siblings point at the oracle-samples tree); TESTING.md:14, docs/DEMO_VIDEO_SCRIPT.md:29 clone it; docs/build_demo_video.py:162 bakes /plugin install aws-aidp-migrator into the shipped video. ai/claude-code-plugins/README.md's plugin table is not updated (the diff touches nothing outside the plugin dir), so a reader of this repo has no working install path at all. Fix: replace every /aws-aidp-migrator: with /oracle-ai-data-platform-workbench-aws-migrator:, point URLs at oracle-samples, document the repo's anthropics/claude-plugins-community flow, add the catalog row, regenerate or drop the video.

Majors — Athena → Spark SQL transpiler (all executed on PySpark 3.5.9; every case is flags=0 → status ok → verify PASS)

The README says the transpiler "won't corrupt SQL it doesn't recognize" and verbs.md:36 defines PASS as "auto-translated & runnable". Neither holds today; the wrapper trusts sqlglot.transpile() unconditionally and discards its warnings:

• array_agg(x ORDER BY y) → COLLECT_LIST(x) — ordering dropped; demo query q-1004 (driver_history_per_policy) is PASS and returns ['third','first','second'] for events dated Mar/Jan/Feb.
• date_add('hour'|'minute'|'second', n, ts) → DATE_ADD(ts, n) — adds n days and returns a DATE (date_add('hour', 3, …) → 2024-01-04). 7/2 → 3.5 (Presto 3). day_of_week/dow/EXTRACT(DOW) → Monday=3 (Presto 1). to_hex(sha256(…)) → HEX(SHA2(…)) — hex applied twice, 128-char digest (the common PII-hashing idiom; md5 is handled correctly, so the inconsistency is silent). log(2, 8) → LOG(8, 2) = 0.333. concat_ws(',', 'x', NULL, 'z') → wrapped in a NULL-propagating CASE → NULL (a sqlglot ≥30 regression passed through).
• sqlglot UnsupportedError warnings are never read (athena_to_spark_sql.py:97 uses the default WARN level): strpos(s, '-', 2) loses the occurrence, max_by(a, b, 2) loses n, truncate(1.239, 2) → CAST(… AS BIGINT) = 1, TRY(CAST('nope' AS DATE)) → bare CAST (raises under ANSI). Re-running with ErrorLevel.RAISE catches all four.
• Multi-statement input is truncated to the first statement ([0] on the transpile result) and reported as a normal rewrite — Athena saved queries routinely hold several.
• Unknown Presto functions pass through verbatim as PASS: demo q-1015 zip(...) → ZIP(...) → UNRESOLVED_ROUTINE on Spark; likewise format_datetime, with_timezone, current_time, map_agg, json_size, to_iso8601, date_format(ts, '%Y-%m-%dT…') (Unknown pattern letter: T), CAST(s AS JSON), WITH RECURSIVE, > ANY (subquery), approx_percentile(x, w, 0.5), width_bucket(x, ARRAY[…]).
• Clauses dropped with no warning: LEFT JOIN UNNEST … ON TRUE → non-OUTER LATERAL VIEW (empty-array rows vanish), split(s, ',', 2) limit, FETCH FIRST n ROWS WITH TIES, from_unixtime(e, zone), repeat(1, 3) → '111'.
• sqlglot>=25.0 is an unsafe floor: under 25.0.0 the 8 tests still pass but 26 of 113 battery outputs differ — element_at(arr, 1) → TRY_ELEMENT_AT(arr, 0) (INVALID_INDEX_OF_ZERO, would break demo q-1012/q-1016), multi-array UNNEST → cartesian product, sequence() → GENERATE_SERIES. Neither report.json nor the .spark.sql header records the sqlglot version. Pin the tested range and stamp it.
• DDL is not covered: CREATE EXTERNAL TABLE … LOCATION 's3://…' passes through unchanged with the s3 path retained (status ok); CTAS WITH (format=…, external_location='s3://…', bucketed_by=…) → TBLPROPERTIES (… ARRAY('id') …) which is a Spark ParseException; ALTER TABLE … ADD PARTITION, SHOW PARTITIONS, PREPARE parse as opaque Command and are still ok.

Fix direction: run the Presto AST after transpile and flag Order inside aggregates, sub-day DateAdd, Div on non-decimal operands, Hex(SHA2/SHA), ConcatWs, Log arity, Join+UNNEST non-inner, Split limits, Fetch WITH TIES, Command nodes and s3:// literals; set unsupported_level=RAISE; join all statements; add a Spark-3.5 built-in allowlist check so PASS can mean "runnable"; pin sqlglot. The 8 shipped Athena tests are substring checks that cannot see any of this — add golden-output tests for each case above (and ideally a local PySpark execution test).

Majors — Glue → PySpark translator (executed; each is flags=0 → PASS unless noted)

• Every parameterised Glue job is translated to a parameter API the live-validated AIDP toolkit does not use — and it has never been executed on a cluster. glue_to_spark.py:169 rewrites getResolvedOptions to args = {"JOB_NAME": oidlUtils.widgets.get("JOB_NAME"), …} at module scope with no import, classified as a rewrite (not a flag) — so all 8 fixture artifacts including both PASS ones carry it (an AST unbound-name scan of the PASS artifacts prints undefined names: ['oidlUtils']). The Databricks→AIDP toolkit's live-verified mapping ("dbutils API mapping [verified live: 2026-09-08]") is dbutils.widgets.* → aidputils.widgets.* for Python, with oidlUtils documented as exposing .notebook/.parameters (oidlUtils.parameters.getParameter(name, default)); oidlUtils.widgets.get appears there only as a permissive static-matcher pattern, never as an API use. scripts/live_land_aidp.sh has zero mentions of glue, so no translated Glue job has run on AIDP (my own live kernel check was blocked by an expired session token — two independent verifiers rate this major rather than blocker for exactly that reason). If the attribute is absent, every parameterised job raises AttributeError on its first executable line while verify says PASS. Fix: emit aidputils.widgets.get(…) (or oidlUtils.parameters.getParameter(k, default)), fix README.md:116, downgrade resolved_options to a flag until one translated job has run on a cluster, and add that run to the live script.
• write_dynamic_frame.from_options forces .mode("overwrite") and drops partitionKeys and format_options (glue_to_spark.py:249): a daily incremental sink (Glue appends by default) becomes a full-path wipe with the partition layout lost — destructive, unflagged. format="glueparquet" is copied verbatim into spark.write.format("glueparquet") (not a Spark source; Glue Studio's default sink format); on reads format_options (withHeader, separator) are dropped and only the first of several paths is kept.
• DynamicFrame method-form API is never flagged (:307 matches only <Name>.apply(): dyf.apply_mapping(…).resolveChoice(…), drop_fields, rename_field, glueContext.getSink(…), write_dynamic_frame.from_catalog, Spigot, DynamicFrameCollection all survive into a PASS artifact that raises AttributeError on a DataFrame.
• The job-lifecycle regex is receiver-agnostic (:132): repo.commit(), txn.commit(), session.commit(), db.init(), logger.init(cfg) are all commented out as "removed Glue Job lifecycle" (a JDBC/SQLAlchemy commit silently deleted from a job labelled Ready), while the real self.job.init(a, b) is not matched.
• Multi-line from_catalog with non-literal kwargs (the exact shape Glue Studio generates) embeds newlines from args.strip()[:60] in a trailing comment → IndentationError; flagged REVIEW, but the artifact cannot be opened by any Python tooling and push_down_predicate is silently discarded.
• verify PASS is a status relabel (checker.py:22-28 maps ok→PASS; nothing is compiled or run): a parenthesised multi-line from awsglue.dynamicframe import (…) leaves the continuation line → IndentationError; def finish(job): job.commit() → empty body → IndentationError; a Scala Glue job is "translated" as Python with 4 rewrites — all PASS. README.md:126 ("auto-translates to a runnable Spark script (PASS)") and html_report.py:73 ("Ready (PASS)") overstate. At minimum compile() each artifact and run an unbound-name scan (glueContext, DynamicFrame, awsglue, Job, oidlUtils, <transform>.apply) before granting PASS.

Majors — plan / verify / report / packaging (executed)

• Every DCAT table target has a malformed location: planner.py:35 does .replace("/", f"@{ns}/", 1) on oci://…, so the first slash replaced is the one inside oci:// → oci:@acme-demo-ns//acme-raw-data/claim_events/ for all 50 tables (0 of 50 well-formed; the Glue translator itself produces the correct oci://bucket@ns/key).
• The <your-oci-namespace> placeholder ships inside PASS deliverables: with OCI_NAMESPACE unset (the default aws-aidp plan inv.json path; run_demo.sh never sets it) plan.json has 56 occurrences, every transfer script 3, and glue/acme_etl_daily_aggregates.py:23 writes to oci://acme-curated-data@<your-oci-namespace>/… while verify says PASS. cmd_plan never calls load_dotenv() (only cmd_inventory does), so README.md:66's "put OCI_NAMESPACE in .env" does nothing for plan/migrate. Fail fast or flag.
• migrate crashes with UnicodeEncodeError on Windows default encoding (runner.py:193 write_text without encoding=; the → comes from the tool's own finding text) — exit 1 after report.json, no report.md/html; html_report.render is additionally wrapped in except Exception: pass. Every Windows plugin user without PYTHONUTF8 hits this. Pass encoding="utf-8" everywhere and log render failures.
• Duplicate names overwrite artifacts while both rows report ok (runner.py:32, paths keyed on the human name; Athena permits identical NamedQuery names across workgroups — the fixture models two): two dup_name queries → one .spark.sql containing only the second, verify PASS for both. Asset names are also used unsanitised as paths — ../../traversal_probe wrote outside -o (runner.py:60).
• verify --filter glue hides the 52 catalog stubs (migrate filters on source_type, verify on target kind): demo.sh's headline verify prints SKIP: 0 while report.json holds 52 planned Glue assets. And verify never inspects artifacts: deleting athena/ glue/ transfer/ or corrupting every translated_sql still yields PASS 20.
• Generated rclone config has an inline # comment on the env_auth = true line (s3_to_oci.py:72); rclone's INI parser (goconfig) only treats #/; at line start as comments, so env_auth becomes true # uses AWS_PROFILE … and the S3 remote fails to construct; the mkdir || true masks its own failure and the copy then fails. Code-read against fetched docs + parser source (rclone not installed here). Compartment OCID and OCI region are never wired either (build_transfer accepts them, the runner never passes them; every script hard-codes <compartment-ocid> and us-ashburn-1).
• License metadata contradicts itself: pyproject.toml:10 license = { text = "Apache-2.0" } while LICENSE, both plugin.json files and both PR bodies say MIT; the built wheel's METADATA reads License: Apache-2.0 + License-File: LICENSE (MIT text). scripts/build_pr_trees.sh:56-61 explains it — the packaging script patches plugin.json to MIT but never pyproject. Siblings also ship NOTICE and PRIVACY.md; this plugin ships neither, and it needs a PRIVACY.md because scripts/ai_assist_flags.py sends flagged source SQL/PySpark to an LLM (see below).

Majors — live path and the live-validation claim (executed with fakes/Stubber; nothing touched AWS or OCI)

• "Verified end-to-end on a live AIDP workspace" is not exercised by any migrator code path. The only live artifact, scripts/live_land_aidp.sh, uploads a 5-row CSV written inline in the script with oci os object put (not the generated rclone job) and runs a hand-typed Spark query (:49-52) whose text differs from what the translator emits for the seed query (different table, date predicate dropped, split(…, ';') vs the transpiler's SPLIT(…, CONCAT('\\Q', ';', '\\E'))). Non-demo migrate returns skipped: live mode not yet wired for all Athena/Glue assets with zero AIDP calls, and AidpClient/run_until_green are referenced by nothing outside their own modules. The PR body, pptx slides 1/8/11 and SPEAKER_NOTES.md:61-63 say "proven end-to-end"/"Working"; README.md:153, SKILL.md:43/56, TESTING.md:141-142 and SESSION_RUNBOOK.md:176 say "demo-only". Pick one truth. Likewise "Glue Data Catalog — working" (SKILL.md:54, README mapping table, slide 11): all 2 databases + 50 tables are SKIP translator not yet implemented stubs; no DDL is produced.
• scripts/live_teardown.py deletes by fixed name with no tag/ownership check, confirmation or dry-run (:42): recorded calls against a fake session — delete_database('acme_curated') (drops every table in it), delete_named_query for every query the workgroup lists (including two non-seed ids I injected), delete_work_group(RecursiveDeleteOption=True), empty + delete_bucket. The Project=aws-aidp-migrator-test tag the seed applies is never read back; AWS_PROFILE=<prod> python3 scripts/live_teardown.py deletes immediately.
• Inventory swallows per-item AccessDenied (glue.py:76-83, athena.py:33/55, same pattern in emr/sagemaker): with get_tables(db2) and get_jobs denied, the summary is table_count: 1, job_count: 0 with no error key, so a partially-authorised scan is presented as the complete estate and plan/migrate proceed on it.

Minors (executed unless noted)

aws-aidp --version prints 0.1.0 (pyproject/plugin.json/CHANGELOG say 0.2.0) and stubs say "deferred to v0.2" in the 0.2.0 release; CHANGELOG says 15 tests (19), describes a regex UNNEST fix that has no counterpart in the sqlglot code, and cites docs/FDE_PRESENTATION.md which doesn't ship; README lists DEMO.md (absent), TESTING.md:20 says boto3 is the only runtime dependency (sqlglot is a hard import); --filter sagemaker matches nothing (source types are sm_*) and --filter accepts any string silently; --sources is ignored in fixture mode; report.html is written but never mentioned by the CLI/verbs.md/TESTING; the _NAMED_REWRITES substring check mis-attributes rewrites in the report (json_extract listed for a query that only uses json_extract_scalar); .toDF() is stripped from RDDs (rows.toDF() → rows), a positional write_dynamic_frame.from_options(dyf, …) invents a df receiver, a non-literal getResolvedOptions list becomes args = {...} (a set of Ellipsis), JDBC/DynamoDB from_options are replaced with a parquet stub (connection details deleted from the artifact), s3:// inside comments/docstrings is rewritten, _kwarg truncates at apostrophes and _balanced_args mis-handles \\", \w+\.spark_session rewrites unrelated objects; RunStore writes under ~/Documents by default with unsanitised keys; AidpClient supports api_key auth only (session-token profiles → KeyError 'user'; the toolkit supports both); list_buckets ignores ContinuationToken; no test touches any live/cloud module; demo.sh/run_demo.sh embed /tmp inside inline Python and assume python3 (fails under Git Bash); report.md code fences can be broken out of by SQL containing ```; the fixture builder stamps wall-clock scanned_at, so regenerating dirties the tree; requires-python >=3.9 cannot be satisfied with the mcp extra (≥3.10). Live-path minors (verifiers downgraded these from major; the code is unreachable from the CLI today): demo.env.example defines AIDP_* names with an export prefix while AidpConfig.from_env requires OCI_PROFILE/OCI_REGION/DATALAKE_OCID/WORKSPACE_ID and README.md:66 points at a non-existent .env.example; run/jobruns.py:115 fires a repair and immediately re-polls, so a 202-accepted repair followed by a stale FAILED read burns all three repairs in 10 ms and reports FAILED; live_seed.py:163 turns AlreadyExistsException into update_table/update_job, repointing a same-named customer table/job at the demo CSV; scripts/ai_assist_flags.py base64-embeds the full flagged Athena SQL / Glue script into ai_generate('google.gemini-2.5-flash', …) on the user's own cluster (not Anthropic — the anthropic extra is dead code) and rewrites report.json/report.html in place, persisting even (ai_generate call failed: …) as a suggestion; nothing discloses that egress (hence PRIVACY.md), --help exits 1 unless AIDP_DATALAKE is set, and it hard-depends on the sibling engineer-agent plugin at a version-pinned cache path (demo.env.example:24) that plugin.json never declares as a dependency; _balanced_args treats an escaped backslash before a closing quote as escaping the quote, so one "x\\" argument swallows the rest of the file into the call (verifiers rate this above note).

Notes — repo hygiene and content

• Internal material shipped in a public sample: docs/LIVE_DEMO.md:126/130 and the scripts/ai_assist_flags.py:13 docstring name an Oracle-internal AIDP workspace nickname and its Spark cluster (values deliberately not reproduced here); DEMO_VIDEO_SCRIPT.md:119 and TESTING.md:151 name colleagues and an internal channel; SESSION_RUNBOOK.md/SPEAKER_NOTES.md/LIVE_DEMO.md are first-person presenter scripts ("Ask me to start it", cd ~/Documents/aws-aidp-migrator) — while SESSION_RUNBOOK.md:5 asserts "No personal names anywhere — safe to share". scripts/build_pr_trees.sh is the tooling that generated these PR trees (rsyncs from a personal checkout into ~/Documents/aidp-samples-prs) and docs/build_*.py are deck/video generators with undeclared matplotlib/PIL/python-pptx deps; the 3.7 MB of mp4/gif/pptx/pdf/png binaries have no precedent under ai/ (largest tracked file today is 509 KB). Suggest keeping the presenter kit and generators in your internal repo and shipping only user docs.
• The demo counts in LIVE_DEMO.md:91/113, SESSION_RUNBOOK.md:126 and the video's verify card (18/2, 20/8/85) are three different sets, none matching the tool's output (20/13/80); the video's footer renders the → as a missing-glyph box on every card (font at build_demo_video.py:42-44), and DEMO_VIDEO_SCRIPT.md describes a 5–7 min screen recording while the mp4 is a 2:05 TTS card slideshow.
• README.md:5-7 and slide 3 state an unsourced market statistic ("about half of incoming AIDP migrations…") — not appropriate in an Oracle-published sample; "AIDP DCAT"/"AIDP saved queries" are not AIDP vocabulary (0 hits in the toolkit and the 37 engineer-agent skills; the AIDP term is catalog/schema/table and "verified queries"), and plan targets omit the catalog level entirely.
• Merge gate you already know about: OCA unsigned (CI red), no Signed-off-by trailer, PR template/issue reference not used — CONTRIBUTING.md requires all three.

Bottom line: keep the architecture (four verbs, sqlglot AST core, generated rclone job, flag-don't-guess) — it is the right shape, and the offline demo proves it. Before this can merge: pin mcp<2, make the plugin self-installing via ${CLAUDE_PLUGIN_ROOT}, fix the plugin identity/namespace and register it in the catalog, settle the widgets API with one live run of a translated Glue job, stop sqlglot warnings from becoming PASS, fix the planner location bug and the Windows encoding crash, align the license metadata, add PRIVACY/NOTICE, make the live claims match the code, and move the presenter kit out of the public tree. 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. 🙏

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.