design-sql-migration

Files (1)

• SKILL.md

SKILL.md (first lines)

# SQL Migration Design

Shared files: a path written `@rules/…`, `@skills/…`, `@templates/…` or `@docs/…` is relative to the
plugin root, `${CLAUDE_PLUGIN_ROOT}` — not to the project being worked on. Read it from there.

## Desired Outcome

For every SQL statement the existing system runs, a decision that answers precisely: **how does this
statement run on ScalarDB, what does acting on that decision take, and what is still unproven?**

1. **SQL inventory** — `sql-inventory.json`: every statement from application code (MyBatis, JDBC, Spring
   JDBC, JPA native queries), SQL files and investigated DDL, views and routines, each with its source
   evidence, category and dynamic-SQL flags, and no stored literal. What static reading could not extract is
   counted, not dropped.
2. **Schema** — `schema.json`: the ScalarDB schema (Schema Loader JSON) the converter judged access paths
   against, with the key split recorded and justified per table.
3. **Converter verdicts** — `conversion.json`: OK / WARN / PLANNED / ERROR per statement with the findings,
   access paths and cost estimates, literals masked.
4. **Migration manifest** — `sql-migration-manifest.json`: **the canonical decision record**, one route per
   statement with its rationale and what the route requires (@rules/sql-migration.md). The validator accepts it.
5. **Design view** — `sql-migration-design.md`: the routes, the application-side work, the redesign
   proposals, the open questions and an effort picture a reader can act on.

Read @rules/sql-migration.md before Stage 1. The mechanism behind the converter is
`skills/common/sql-migration/references/architecture.md`; the grammar and finding codes are
`references/scalardb-grammar.md`; application-side semantics are `references/app-side-notes.md`.

## Invocation

```
/architect:design-sql-migration [target_path] [--source=oracle|postgres|mysql] [--app-root=<path>] [--sql-file=<path>] [--db-run=<path>] [--live-run=<path>] [--edition=community|enterprise_standard|enterprise_premium] [--storage=jdbc|cassandra] [--namespace=<name>] [--auto] [--lang=en|ja]
```

- `target_path` — Project directory; defaults to the current directory.
- `--source` — Source dialect. Taken from the investigation run's product when omitted.
- `--app-root` — Application source root to inventory (repeatable).
- `--sql-file` — SQL script to inventory: batch jobs, report queries, migration scripts (repeatable).
- `--db-run` — An `investigate-db-design` run whose DDL, views and routines are inventoried (repeatable).
- `--live-run` — An `investigate-db-live` run whose row estimates feed the cost estimates (repeatable).
- `--edition` — ScalarDB edition; decides whether ScalarDB SQL is available. Taken from
  `scalardb-edition-selection.md` when omitted.
- `--storage` — Storage behind ScalarDB; `cassandra` has no cross-partition scan. Taken from the
  `design-scalardb` ADR when omitted.
- `--namespace` — ScalarDB namespace of the migrated tables. Taken from `scalardb-schema.md` when omitted.
- `--auto` — No questions; everything the user owns becomes an `unasked` Open Question.
- `--lang` — Output language of the design view. Defaults to `options.output_language`.

## Decision Criteria

- **The analysis decides the inputs, not the converter.** Keys, storage, edition and row estimates come
  from `design-scalardb`, `select-scalardb-edition` and the investigation runs; the converter judges
  statements against them. A key split the converter defaulted from a source primary key is a proposal to
  confirm, not a design.
- **One statement, one route, with a reason.** Every inventoried statement is decided; a route without the
  payload the rule requires is not a decision.
- **`OK` means the grammar accepted it.** It is not equivalence: NULL handling, rounding, collation and
  types still differ. Say so in the rationale of every `scalardb_sql` / `core_api` route whose converter
  findings include a WARN, and leave `verification.status: pending` for `verify-sql-migration`.
- **The edition gates ScalarDB SQL.** It is Enterprise Premium (@rules/scalardb-edition-profiles.md). Under
  any other edition a convertible statement takes `core_api`; never propose a license to make a route work.
- **Dynamic SQL is a family of statements.** The inventory keeps one rendering with its dynamic parts marked,
  which the converter usually cannot parse (`ERROR` with `PARSE`) — that verdict says nothing about the
  real renderings. Ask which renderings exist and convert each before any automatic route; otherwise take
  `app_side`, `redesign` or `retire`.
- **Cost comes from measured estimates.** Row counts are the live run's estimates (`semantics: estimate`),
  quoted as such; a statement over its row limit or deadline is a redesign candidate, not a plan to accept.
- **Resolve, then ask, then record** (@rules/open-questions.md). Never ask what the reports already state;
  batch 1–4 questions per call with derived candidates; what stays open becomes an `OQ-` entry.
- **Read only.** The inventory reads files and investigation runs; nothing here connects to a database.

## Prerequisites

| Input | Required/Recommended | Source |
|------|---------------------|--------|
| `sqlglot` from `requirements.txt` | Required | `pip install -r "${CLAUDE_PLUGIN_ROOT}/requirements.txt"` |
| At least one of: application source root, SQL file, `investigate-db-design` run | Required | the project / /architect:investigate-db-design |
| `reports/01_analysis/database-investigation/<target>/design/<run>/` | Recommended | /architect:investigate-db-design — DDL, view and routine evidence |
| `reports/01_analysis/database-investigation/<target>/live/<run>/` | Recommended | /architect:investigate-db-live — row estimates for cost |
| `reports/03_design/scalardb-schema.md` and its ADRs | Recommended | /architect:design-scalardb — key design and storage backend |
| `reports/03_design/scalardb-edition-selection.md` | Recommended | /architect:select-scalardb-edition — whether ScalarDB SQL is available |
| `reports/01_analysis/data-model-analysis.md` | Optional | /architect:analyze-data-model — relationships behind join-heavy statements |
| `reports/03_design/aggregates/aggregate-manifest.json` | Optional | /architect:design-aggregate — transaction boundaries for rewritten writes; `AGG-` upstreams |
| `work/traceability.json` | Optional | earlier phases — `FR-` / `AGG-` nodes a statement serves |

Select investigation runs explicitly: list the runs that exist and let the user choose. Never pick the latest
run silently, and never combine a design run and a live run as if they were one baseline.

## Execution Modes

### Interactive Mode (default)

Six stages, at most two question rounds each. In both modes, record `in_progress` in
`work/pipeline-progress.json` (`plugin: architect`) before the first stage and `completed` with the outputs at the
end (@skills/common/progress-registry.md).

**Stage 1 — Scope and target**
Resolve from the reports what they already state: source dialect and version (the investigation run's
`product` / `version`), edition, storage backend, namespace. Ask for what is missing in one batch — the
edition question states that ScalarDB SQL needs Enterprise Premium and what the Core API route costs
instead. Confirm the application roots, SQL files and investigation runs in scope.

**Stage 2 — Inventory**

```bash
python3 "${CLAUDE_PLUGIN_ROOT}/skills/common/sql-migration/scripts/inventory.py" --source <dialect> \
  --out reports/03_design/sql-migration/sql-inventory.json \
  --app-root <root> --sql-file <file> --db-run <design-run> \
  --previous reports/03_design/sql-migration/sql-inventory.json
```

Name the earlier inventory (the last argument above) only when one already exists, so statement IDs
survive the re-run. Exit 2 means
problems: a stale evidence line means the DDL changed after the investigation — re-run the investigation or
pass the file with `--sql-file`; do not continue on stale input. Present the counts by origin and category,
the dynamic statements, the JPQL statements, the `unextracted` calls and the `unavailable` definitions.
Every `unextracted` call and `unavailable` object is open work in the design view.

**Stage 3 — Keys and schema**
Take the key design from `scalardb-schema.md` when it exists and write it as Schema Loader JSON for

From wfukatsu/nexus-architect, MIT.

More from wfukatsu/nexus-architect