--- name: migrate-oracle-app description: "Assess and convert Oracle-dependent codebases to PostgreSQL. Use for architecture and Mermaid diagrams, Oracle JDBC, SQL, PL/SQL calls, ORM mappings, missing dependencies, missing PostgreSQL objects, Docker integration tests, synthetic data, code coverage, packaging, and migration-readiness reporting." argument-hint: "mode= codebase= postgres-connection= database= [schema=] [oracle-ddl-folder=] [notes=] [postgres-image=] [options=]" user-invocable: true disable-model-invocation: false --- # Oracle to PostgreSQL Application Migration Assess deeply, then convert optimistically in reversible increments. Use compiler errors, tests, coverage, target metadata, and Docker behavior as evidence. Preserve codebase architecture and observable behavior unless a change is necessary and approved. ## Plugin Tool Paths `` means the directory containing this `SKILL.md`. `` is exactly two parent directories above `` and contains `plugin.json` and `package.json`. Migration scripts are stored under `\scripts` and remain portable with the skill. Resolve the detector directly as `\scripts\detect-migration-scope.mjs`, normalize that path once, and verify the file exists before execution. Resolve an absolute path from the loaded skill directory, regardless of the current working directory. Do not search/glob for an alternate copy or infer `` as ``. ## Modes - **Assess**: complete a database-read-only assessment using [git-workspace.md](./references/git-workspace.md), [assessment.md](./references/assessment.md) and [testing-and-coverage.md](./references/testing-and-coverage.md). Use [connection-context.md](./references/connection-context.md) to resolve the PostgreSQL target, [postgres-provider-routing.md](./references/postgres-provider-routing.md) to select the exact VS Code or Copilot CLI PostgreSQL provider, [postgres-mcp.md](./references/postgres-mcp.md) for target discovery, and [diagrams.md](./references/diagrams.md) for report diagrams and Code Coverage sections. - **Convert**: consume the assessment, preview impact, obtain approval, then follow [git-workspace.md](./references/git-workspace.md), [conversion.md](./references/conversion.md), [database-safety.md](./references/database-safety.md), and [testing-and-coverage.md](./references/testing-and-coverage.md). Use [connection-context.md](./references/connection-context.md) to validate or resume the target, [postgres-provider-routing.md](./references/postgres-provider-routing.md) to preserve one provider/profile store for the run, [options.md](./references/options.md) to resolve opt-in actions, [state-and-convergence.md](./references/state-and-convergence.md) throughout assessment and conversion, [language-adapters.md](./references/language-adapters.md) for Java, .NET, Python, C, and C++ toolchains, [agent-orchestration.md](./references/agent-orchestration.md) for parallel execution, [performance-and-execution.md](./references/performance-and-execution.md) for optimized build/test/package execution, and [diagrams.md](./references/diagrams.md) for transformation evidence. - **Assess and convert**: run assessment and conversion as one uninterrupted source workflow. The invocation authorizes reversible source edits implied by the mode and explicitly enabled options; record previews for audit but do not pause between stages. A migration plan is not required. Select PostgreSQL tools with [postgres-provider-routing.md](./references/postgres-provider-routing.md). Native VS Code Copilot, including the **Copilot Session Target**, requires the Microsoft PostgreSQL extension and uses only `PostgreSQL MCP` `pgsql_*` operations. Copilot CLI, including a CLI session connected to VS Code, uses only the plugin-bundled `postgres-mcp` `postgres_mcp_*` operations. Never fallback or mix providers. Deferred tools require exact full function-name searches for the selected family and individual probes for every still-missing operation. Wrong-family or bounded search results never prove unavailability; reject those results and retry as required by the routing protocol. Use resolved MCP tools for target-backed work. Only after that discovery protocol proves the selected provider unresolved, continue only independent code-only work, report `ACTION NEEDED`, and do not claim target validation. Never request secrets through chat. ## Required Inputs - `codebase=` for the single in-place codebase workspace - `postgres-connection=` and `database=` for target-backed assessment, validation, or persistent DDL - Exact `schema=` when `test-target-writes` is on; otherwise schema when codebase evidence and search path do not resolve it - A saved write-enabled profile for the selected database/schema when `create-persistent-ddl` is on and missing objects exist, or for the same explicit database/schema when `test-target-writes` is on and applicable mutation scenarios exist; never accept credentials in chat - Optional `oracle-ddl-folder=` pointing to the migration-project/ancestor root. The preferred schema layout is `.github\postgres-migrations\\artifacts\oracle\\`. Equivalent layouts are accepted at other depths, and the schema may contain any nonempty set of object-type folders. Follow [oracle-ddl-source.md](./references/oracle-ddl-source.md). It is preferred authoritative evidence, but absence or no unique match triggers documented best-effort reconstruction rather than stopping conversion. - Optional `notes=` under [coding-notes.md](./references/coding-notes.md), scope restrictions, and target PostgreSQL image - Optional conversion actions from [options.md](./references/options.md) - Node.js 22+ with `node`, `npm`, and `npx` on `PATH` for every harness. VS Code's PostgreSQL extension does not replace the deterministic Node.js scripts. - Docker Engine/Desktop for PostgreSQL integration tests; the user starts Docker unless `start-docker-desktop` is explicitly enabled. - For Java codebases, the JDK required by the project and Maven or the repository Maven wrapper. Derive build commands, dependency coordinates, SQL mappings, files, and routine signatures from evidence. Ask only when a choice changes public behavior, architecture, security, scope, or database state. ## Statuses Assessment uses only: - ✅ **READY**: compatible, inactive, or no change needed - 🔧 **CONVERTIBLE**: a bounded, reversible conversion is available - ⚠️ **ACTION NEEDED**: a decision, environment capability, or external prerequisite is needed Assessment always completes and never reports `BLOCKED`. Continue independent analysis when an item needs action. Conversion uses only: - ✅ **READY**: build, required tests, Docker integration, packaging, and configured runtime checks pass - 🧪 **RUNTIME VALIDATION NEEDED**: source, build, tests, Docker, and packaging pass, but a target environment check remains - ⚠️ **PARTIAL**: useful validated work is complete, with bounded unresolved items - ⛔ **BLOCKED**: no safe or useful conversion can continue `BLOCKED` is exceptional and conversion-only. Use it only when an essential issue prevents the entire conversion, independent work cannot help, repair/shim/defer options were exhausted, and continuing risks data, security, user work, or fabricated behavior. One failure, missing object, or unavailable runtime check is not enough. The status names above remain canonical uppercase values in machine state. In customer-facing report Status and Result columns, render one emoji followed by plain title-case text with no bold and no all-caps label, for example `✅ Passed`, `⚠️ Action needed`, `➖ Not available`, and `🔧 Convertible`. ## Non-Negotiable Rules - Require the codebase folder to be a Git repository root with an attached branch and at least one commit. Never initialize Git automatically. - Convert the codebase in place. Do not create or require a separate codebase target folder. - Preserve user changes. Never stage, create branches, stash, reset, discard, commit, push, or publish changes. - Retain repository files classified only as unused unless `remove-unused-artifacts` is explicitly enabled. Classify checked-in JARs and other unused artifacts in evidence instead of deleting them. - Assessment is tracked-source read-only by default and always database-read-only. On a clean attached branch, it may apply an exact authorized baseline-enablement batch when the corresponding options are on. It never writes Oracle or PostgreSQL. - Retain coverage locally. Apply the scoped root `.gitignore` rules in [git-workspace.md](./references/git-workspace.md) only after source-edit authorization; never modify `.gitignore` during read-only assessment or a no-conversion/scope-review stop. - Select exactly one PostgreSQL provider according to [postgres-provider-routing.md](./references/postgres-provider-routing.md). In VS Code Copilot use the PostgreSQL extension `pgsql_*` tools; in Copilot CLI use `postgres-mcp` `postgres_mcp_*`. Never fallback or mix profile stores. - Query only target objects derived from active codebase evidence. - Never call `postgres_mcp_db_context`, `pgsql_db_context`, or any tool whose function name ends in `_db_context`, including for one object, connection verification, or timeout recovery. Tool availability is not permission. Use server-capability/list-database tools plus scalar, exact-name, and OID-bounded `postgres_mcp_query` catalog probes. - Build an exact code-derived object manifest. Normally probe at most 10 same-kind names (20 hard maximum), then query details for at most 5 returned OIDs (10 hard maximum) at a time. - Prefer a unique, hash-verified `oracle-ddl-folder` match for missing routines, package members, triggers, views, materialized views, and types. When the folder is absent or no unique object is found, reconstruct the object best-effort from all code/test/mapping/contract evidence, generate deploy/undo DDL, update application calls, and run synthetic Docker validation. Never hide the inference: record body completeness, confidence, assumptions, limitations, and required customer validation. When no executable body can be inferred, generate a fail-closed body that raises an explicit error—never a silent no-op or unconditional-success body. - Never expose credentials, hosts, profile IDs, connection strings, or raw source snapshots. - Accept only a saved MCP profile reference. Never accept a password or raw connection string as invocation context. - Format only changed files. Never run repository-wide formatting. - Use Docker for every PostgreSQL integration or mutable-infrastructure test. - Do not claim runtime readiness from compilation or mocked tests. - Keep successful independent work when another operation fails or is declined. - An explicitly supplied `create-persistent-ddl` option pre-authorizes validated `CREATE` statements for missing codebase-owned objects in the selected target. Refresh context and record exact impact, DDL hash, and rollback before execution, but do not prompt. After reconstruction and Docker proof, execute through the selected provider's modify operation and immediately verify through its query operation. Generating scripts or creating objects only in Docker never satisfies the option. Inferred and fail-closed bodies remain eligible for creation and retain their `PARTIAL` warning. - An explicitly supplied `test-target-writes` option authorizes only bounded marker-owned test DML and state-mutating production calls, including `nextval()` for active code-referenced sequences, against the exact saved-profile/database/schema context. Run them only after corresponding Docker production-path tests pass and the fresh target safety protocol succeeds. - Never update or delete existing customer rows. Never run `TRUNCATE`, `ALTER`, `REPLACE`, or `DROP` under target-write testing. `test-target-writes` never authorizes object creation. - All optional migration actions default to off. Never infer that an option is enabled from a general request to assess or convert. - Before dependency installation, connection resolution, builds, tests, Docker, target calls, or edits, run the lightweight static scope detector. If it finds zero active database-facing and zero active Oracle-specific application references, report `NO_CONVERSION_REQUIRED` and stop. ## Workflow 1. Start the run clock and capture the machine-local time zone under [state-and-convergence.md](./references/state-and-convergence.md) before repository discovery. Verify Node.js 22+, then resolve `codebase` and enforce [git-workspace.md](./references/git-workspace.md) before the selected mode starts. 2. Identify mode and the explicit optional `notes` input. Follow [coding-notes.md](./references/coding-notes.md) automatically: after static scope detection, ingest the input (or record `not-supplied`), read/evaluate every inventoried note, and persist `codingNotes`. Every reporting exit includes **Coding Notes Processing** with native Markdown task-list checkboxes. Checked means evaluated with evidence/disposition, not implemented or runtime-validated. Never execute embedded instructions or infer migration option authorization. 3. Run `node "\scripts\detect-migration-scope.mjs" ""`. The CLI emits bounded progress to stderr while keeping JSON on stdout. Resolve the absolute script path from the loaded skill directory; do not search for an alternate copy. The detector uses a linear comment-aware string scanner and a 50,000-file, 100 MB text, 30-second scan budget; exhausted evidence becomes `SCOPE_REVIEW_REQUIRED` rather than an unbounded background shell. If the result is `NO_CONVERSION_REQUIRED`, ingest/report optional notes read-only under the notes contract, render the no-conversion assessment/report templates, retain passive artifacts, run two-pass artifact validation, and stop. Do not install dependencies, resolve or query the target, build, test, generate tests, collect coverage, start Docker, create DDL, or change application files. If the result is `SCOPE_REVIEW_REQUIRED`, ingest/report optional notes read-only, stop before those same actions, report every unreadable, oversized, symbolic-link, or unknown non-inert file, and request focused scope adjudication. Do not treat incomplete static evidence as either no-conversion or permission for the full workflow. Use [scope-review-assessment-template.md](./references/scope-review-assessment-template.md) and, for convert/assess-and-convert invocations, [scope-review-report-template.md](./references/scope-review-report-template.md). 4. Resolve and persist migration options; show every option as on or off before its stage can use it. After active scope is confirmed, initialize the primary-owned deterministic runtime under [runtime-coordinator.md](./references/runtime-coordinator.md). Use its exclusive execution lease, verified source/target-object caches, and bounded checkpoint journal; children never recreate Maven/Docker setup or rewrite canonical state/reports. Resolve optional `oracle-ddl-folder` separately because it is read-only source evidence, not an action option. When supplied, run `node "\scripts\index-oracle-ddl.mjs" "" "\.github\migrate-oracle-app\assessment\oracle-ddl-index.json" --schema ""` before target missing-object conversion and persist the sanitized index. Any ambiguous layout, duplicate primary object, known-folder kind mismatch, or unreadable entry makes that source evidence unavailable and `ACTION NEEDED`; never use it by filename guess. Continue affected objects through documented best-effort reconstruction from code/tests. Index every immediate object-type directory beneath the resolved schema—not a fixed list—and ignore unrelated files elsewhere beneath the supplied root. 5. Select and persist the deterministic PostgreSQL provider with [postgres-provider-routing.md](./references/postgres-provider-routing.md). Complete exact-name deferred discovery and individual missing-operation probes, recording actual queries/results under `postgresProvider.discovery`; never infer unavailability from one bounded response. Then resolve `postgres-connection`, `database`, and optional `schema` with [connection-context.md](./references/connection-context.md); verify the exact target and cache its sanitized context digest. When `create-persistent-ddl` or `test-target-writes` is on, check saved-profile access mode, selected-provider modify availability (`pgsql_modify` or `postgres_mcp_modify`), `default_transaction_read_only=off`, privileges, and primary/non-recovery status. Treat `transaction_read_only=on` from the selected query operation only as inspection-tool isolation. If applicable writes lack true write capability, request a write-enabled saved profile reference; never silently downgrade the requested option. 6. Build a dependency/ownership graph and follow [agent-orchestration.md](./references/agent-orchestration.md). When child agents are available and at least two ready units are independent, launch at least two in a parallel batch. Preserve Git/database boundaries and persist orchestration evidence. 7. Assess first unless a current matching assessment exists. Detect every language/build adapter. Retrieve unchanged source and code-derived target-object manifests from the runtime cache before repeating semantic discovery. Byte/directory or fresh target-context changes invalidate them. Target-write privileges, locks, recovery checks, and mutation safety are never cached. Before accepting the untouched build, inventory test frameworks/containers/cases, verify every required runner/adapter in the effective dependency/build graph, and reconcile fresh native reports. Persist the reviewed `test-discovery-baseline.json`; a green build exit alone is insufficient. 8. When the untouched baseline is blocked, use [options.md](./references/options.md) and [testing-and-coverage.md](./references/testing-and-coverage.md) to preview and apply a bounded baseline-enablement batch. In standalone `assess`, obtain approval first; in `assess-and-convert`, record the preview and continue without prompting. Measure coverage before semantic conversion or generated tests. Include any pending coverage ignore edits in the exact baseline-enablement preview under [git-workspace.md](./references/git-workspace.md); read-only assessment never writes `.gitignore`. 9. Generate the assessment architecture/dependency diagrams, critical database interaction sequences, canonical Oracle Migration Hotspots table, and measured coverage heatmap. Organize the report from Executive Summary through architecture, impact, target readiness, test readiness, success criteria, outcome, and operational appendix; persist every evidence model. 10. Persist an exhaustive code-derived Oracle finding manifest and reuse it until file, build, schema, or failure evidence invalidates it. Reconcile every finding as converted, intentionally retained, or deferred with exact unsafe/external evidence. Match each missing target object to the indexed Oracle DDL source when available. Convert only the uniquely matched object and direct dependencies; never bulk-convert the folder. When no source match exists, perform the best-effort reconstruction and synthetic-validation protocol instead of omitting the object. 11. For conversion, validate the original Git baseline and any current-run baseline-enablement changes, then record a concise impact preview. 12. In standalone `convert`, obtain source-edit approval. In `assess-and-convert`, proceed directly from assessment into bounded conversion groups without a stage-transition prompt. Use the validation ladder after each unit. After authorization, apply the coverage Git ignore rules in [git-workspace.md](./references/git-workspace.md) as part of the previewed source-edit batch. 13. Reach compilation early; cluster diagnostics by root cause and fix failure classes. 14. Enforce test runner/adapter availability, container/case-level discovery reconciliation, and failure repair as three separate gates. Repair tests or generate tests only when their corresponding options are on. Before any Java test generation, run the cached effective-release preflight in [runtime-coordinator.md](./references/runtime-coordinator.md); validate draft syntax against the module's test release before staging it. Unknown releases stop dependent generation, never default to the installed JDK. With `fix-broken-tests` on, repair stale or migration-caused failures exposed by the complete suite—including pre-existing failures—and rerun to zero failures/errors. Record per-class attempts; "legacy debt" is not a stop reason without attempted repair and exact unsafe/external evidence. Never generate a test before the authoritative assessment baseline is captured. 15. Probe the Docker engine without changing desktop/service state. Use an already-running engine. Start or resume Docker Desktop only when `start-docker-desktop` is explicitly on; otherwise record the unavailable integration gate and continue independent work. Reuse a run-scoped Docker environment to deploy DDL, seed or use existing safe data, call production paths, verify, clean, undo, and redeploy. Record exact per-table fixture counts, exercised paths/assertions, and cleanup proof for the conversion report. 16. Deploy reconstructed or converted missing objects before dependent target runtime tests. When `create-persistent-ddl` is on, follow [conversion.md](./references/conversion.md#persistent-missing-object-deployment) and [database-safety.md](./references/database-safety.md#persistent-create-authorization): refresh exact target absence/contracts and write safety, execute the hash-verified create-only DDL in dependency order through `pgsql_modify` or `postgres_mcp_modify`, then immediately verify each object through the matching query operation. Record per-object `persistentCreate` evidence and `optionResults.create-persistent-ddl`. Do not stop at deploy/undo files, defer solely because a body is inferred, or require `test-target-writes` to deploy DDL. Verify matching already-present objects without replacing them. If a safety prerequisite fails, record `not-executed`, the exact reason and next action, and `PARTIAL`; continue independent work. 17. When `test-target-writes` is on, run only the corresponding Docker-passed production paths against the exact authoritative target. Refresh identity, role, access mode, privileges, bounded contracts, and relevant locks; record the exact write impact; create only `MIG_TEST_` marker-owned rows; execute bounded target mutations and cleanup through the selected provider's modify operation (`pgsql_modify` in VS Code Copilot or `postgres_mcp_modify` in Copilot CLI); record every consumed sequence value; clean in dependency order; and prove zero marker-owned rows remain. Defer only dependent target-write tests when any safety check fails. Reconcile the option explicitly: when applicable paths exist, only a passed target execution satisfies it. If no writable context is supplied, record `not-executed`, set conversion status to `PARTIAL`, and give the exact next action; do not report `RUNTIME_VALIDATION_NEEDED`. 18. Generate coverage only after test discovery reconciliation passes. Record separate applicable framework/toolchain, integration, and overlap-safe combined lanes, proving execution data is current. Compare line/branch coverage with the assessment. When `create-unit-tests` is on, run at least two bounded unit/characterization waves. When `create-integration-tests` is on, independently run at least two bounded integration waves. Never exceed ten waves in either lane. Batch related scenarios, retain validated Maven preparation and one run-owned Docker environment, and use an unrestricted final validation wave as the complete suite where its runner permits. Waves three through ten require specific value-backed uncovered behaviors, not repeated unchanged tests or percentage chasing. Follow [performance-and-execution.md](./references/performance-and-execution.md) for preparation reuse, shared endpoint binding, finalization without duplicate execution, and per-stage timings. Prioritize changed and database-facing behavior and work toward an aspirational 80% combined coverage level without adding a report target/objective column or treating the percentage as a readiness gate. Report measured counters, deltas, wave results, and classified remaining gaps. Follow the stopping evidence required by [testing-and-coverage.md](./references/testing-and-coverage.md). 19. Generate the Before → After, traceability, and migration-validation coverage diagrams from actual evidence. Integrate convergence stages, results, evidence, and readiness impact into the single Validation Results table; never create a duplicate Validation / Convergence Map section. Organize the report around executive outcome, transformation, validation, quality, database test environment, remaining work, final recommendation, and execution appendix. The opening answers only how conversion went and what the customer must do next: use the canonical six-row Conversion Scorecard, then one to five owner-specific customer actions and an ordered production-readiness path. Keep file counts and technical inventory in later evidence sections. 20. Run final quality gates, inspect artifacts, scan for active Oracle remnants, and capture final Git status/diff evidence. Reconcile an exhaustive path-by-path Changes inventory containing every modified, added, deleted, renamed, and generated codebase file; grouped summaries never replace exact paths. Record ignored local coverage in coverage evidence, not the Git-reconciled Changes inventory. Report honest readiness. Every Validation Results row must include the required emoji status and text label. Report Docker PostgreSQL runtime validation, authoritative target contract validation, and authoritative target runtime/write validation as three separate gates. Require zero safely convertible findings remaining; a small modified-file count is never proof of scope. Record every unused artifact and whether it was retained or explicitly removed. Reconcile every PostgreSQL runtime descriptor with final deployment archives using `inspect-driver-packaging.mjs`; do not accept a driver that exists only inside the WAR when the runtime descriptor expects an external server library path. 21. Assemble `assessment-report.md`, `conversion-report.md`, and their full machine state only at phase/final reporting boundaries through `migration-runtime.mjs finalize`, using the exact templates in [assessment-report-template.md](./references/assessment-report-template.md) and [conversion-report-template.md](./references/conversion-report-template.md). Fill or explicitly classify every table row/placeholder; do not omit a required section because an object kind, fixture set, target gate, or metric is zero/unavailable. Finalize precise `runMetrics` evidence and generate both Run Metrics sections with `node "\scripts\format-run-metrics.mjs" ""`. Use the same pinned local time zone, readable Start/Completed timestamps, and spelled-out hours/minutes/seconds. Both tables contain Assessment, Conversion, and Total Runs. Synchronize the final combined-run timing snapshot into both reports and states before artifact validation. During execution, journal bounded state operations and changed section bodies instead of rewriting full artifacts. The assembler also verifies the current notes input and generates Coding Notes Processing directly from `codingNotes`; changed notes invalidate stale evaluation evidence in both phases. The primary assembler supplies canonical navigation, timing, and verifiable checkpoint/receipt provenance. 22. After final report/state/diff persistence, the runtime finalizer runs `node "\scripts\validate-migration-artifacts.mjs" "" "" --require-runtime --record`, then reruns without `--record` to independently verify the persisted report and diff digests. Repair report/evidence failures and repeat both phases. A conversion cannot be `READY` and is not complete until both validation phases pass; if unavailable evidence cannot be produced safely, retain the required row with the exact limitation and report `PARTIAL` or `RUNTIME VALIDATION NEEDED`. Persist conversion evidence using [conversion-state-schema.md](./references/conversion-state-schema.md); do not use alternate key names that bypass validation. Static no-conversion/scope-review exits retain their read-only early-stop path and two-pass validation without initializing build/Java/cache execution. They do not require runtime proof. 23. Confirm the frozen timing snapshot is reflected in both report appendices and machine state. Never change timestamps or durations after final artifact digests have been verified. Stop only when all available gates pass or the report records an exact bounded limitation. Never turn uncertainty into a blanket blocker when the LLM can inspect, implement, compile, test, or isolate a safe alternative. ## Outputs Assessment creates: ```text .github/migrate-oracle-app/assessment/ |-- assessment-report.md |-- assessment-data.json |-- test-discovery-baseline.json `-- coverage/ ``` Conversion creates: ```text .github/migrate-oracle-app/conversions/ |-- conversion-report.md |-- conversion-state.json |-- file-diffs.md |-- coverage/ `-- database-ddl/ # when needed and no repository convention exists ``` Use this one stable conversion artifact set. Never create timestamped or named conversion subdirectories. A rerun atomically replaces the prior skill-owned report, state, diff, coverage, and DDL artifacts after validating ownership/digests; remove obsolete skill-owned artifacts from the previous run. Never overwrite or delete an unrelated/user-owned file in `conversions/`. Both coverage directories remain local validation evidence. Once source edits are authorized, exclude them from Git using only the scoped root `.gitignore` rules in [git-workspace.md](./references/git-workspace.md). These rules do not ignore reports, state, diffs, or DDL, and never authorize untracking or deleting existing coverage. Keep reports concise, sanitized, evidence-based, and resumable. Record the active version from the plugin's root `plugin.json` in assessment and conversion reports when installed as a plugin. When a report preview tool is available, open the final report after generating it.