Metadata-Version: 2.5
Name: mysql-audit-ai-agent
Version: 0.2.0
Summary: MySQL schema-change safety review for AI coding agents (MCP server and CLI)
Project-URL: Homepage, https://github.com/jimmyjan0824/mysql_audit_ai_agent
Project-URL: Issues, https://github.com/jimmyjan0824/mysql_audit_ai_agent/issues
Author: jimmyjan0824
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: ai-agent,claude-code,cursor,mcp,migration,mysql,online-ddl,schema-change,sql-review
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: >=3.10
Requires-Dist: mcp>=2
Requires-Dist: sqlglot>=30
Provides-Extra: dev
Requires-Dist: pymysql>=1.1; extra == 'dev'
Requires-Dist: pytest>=8; extra == 'dev'
Description-Content-Type: text/markdown

# mysql_audit_ai_agent

<!-- mcp-name: io.github.jimmyjan0824/mysql-audit -->

MySQL schema-change safety review for AI coding agents.

Before Claude Code, Cursor or another agent runs a migration, it sends the SQL to
`review_migration` and gets back a verdict (`safe` / `review` / `block`) plus, for
every risk, the line, what will happen in production, and a safe way to write it.
The checks are deterministic rules written from DBA experience, not another LLM guess.

> Status: early development (v0.1). All twelve planned rules are in.

## Rules

| ID  | Checks | Default | Needs |
| --- | --- | --- | --- |
| L01 | DDL without explicit `ALGORITHM` / `LOCK`; `ALGORITHM=COPY`, `LOCK=SHARED/EXCLUSIVE` | warn / block | |
| L02 | Column changes that force a table copy: type change, VARCHAR shrink or crossing 255 bytes, ENUM reorder, charset conversion, dropping the PK | block | schema for exact answers |
| L03 | Row-touching DDL on a large table: use gh-ost / pt-online-schema-change | warn | stats |
| L04 | DDL without a short `SET SESSION lock_wait_timeout` (metadata-lock pile-up) | warn | |
| L05 | `ADD FOREIGN KEY` while `foreign_key_checks=1` (COPY only) | block | |
| L06 | UPDATE / DELETE / INSERT ... SELECT over a large table in one transaction (replication lag) | warn | stats |
| L07 | `DROP` / `TRUNCATE` / `RENAME` of columns, tables, databases | block | |
| L08 | `UPDATE` / `DELETE` without `WHERE`; `LIMIT` without `ORDER BY` | block / warn | |
| L09 | Backfill not limited to a key range or batch size | warn / info | |
| L10 | Auto-increment column smaller than BIGINT; `utf8` / `utf8mb3` | warn | |
| L11 | `WHERE` / `JOIN` on a column that no index starts with | warn | schema |
| L12 | SQL built by f-strings, template literals, concatenation, `%` / `.format` / `Sprintf` (SQL injection) | block | source code |

Tables created earlier in the same script are treated as empty, so DDL on them is not flagged.

- **schema**: the current `CREATE TABLE` statements (`SHOW CREATE TABLE`).
- **stats**: approximate row counts, `{"orders": 25000000}`, from
  `SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE()`.
  Tables of 1,000,000 rows or more count as large; under 10,000 as small.

## Use with an AI agent (MCP)

Requires [uv](https://docs.astral.sh/uv/). The server runs locally over stdio; your SQL never
leaves your machine.

**Claude Code**

```bash
claude mcp add mysql-audit -- uvx mysql-audit-ai-agent
```

**Cursor, Windsurf, Claude Desktop and other MCP clients** (`mcp.json` / `claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "mysql-audit": { "command": "uvx", "args": ["mysql-audit-ai-agent"] }
  }
}
```

**Claude Desktop, one click**: download `mysql-audit.mcpb` from the
[latest release](https://github.com/jimmyjan0824/mysql_audit_ai_agent/releases/latest) and open it.

Tools: `review_migration(sql, schema?, stats?)`, `review_query(sql, schema?, stats?)`,
`review_code(code)`, `explain_rule(rule_id)`, `list_rules()`.

In our [agent evaluation](docs/agent-eval.md) Claude Code called the tool on its own in 10 of
10 migration tasks, with no extra prompt.

## Command line

```bash
pip install mysql-audit-ai-agent
mysql-audit migrations/20261027_add_index.sql --schema schema.sql
mysql-audit migration.sql --stats rows.json --format json
mysql-audit --code app/models/user.py      # SQL injection check
```

Exit code: 0 safe, 1 review, 2 block, so it can gate CI.

## GitHub Action

Reviews the migrations each pull request adds or changes, annotates the lines in the diff,
keeps one summary comment on the pull request up to date, and fails the check on `block`.

```yaml
# .github/workflows/mysql-audit.yml
name: MySQL Audit
on: pull_request
permissions:
  contents: read
  pull-requests: write   # for the summary comment
jobs:
  audit:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: jimmyjan0824/mysql_audit_ai_agent@v0.2.0
        with:
          paths: db/migrate/**/*.sql      # default: **/*.sql
          schema: db/schema.sql           # optional, makes checks exact
          # stats: db/table_rows.json     # optional row counts, enables large-table rules
          # code-paths: app/**/*.py       # optional SQL injection check
          # fail-on: review               # block (default), review or never
```

## Development

```bash
pip install -e ".[dev]"
pytest
```

Every rule has positive and negative test cases under `tests/`.

`tests/test_mysql_oracle.py` checks the rules against a real server: each ALTER is
run with `ALGORITHM=INPLACE, LOCK=NONE`, and MySQL's refusal (or not) must match the
rule's verdict. CI runs it on MySQL 8.0 and 8.4. Locally:

```bash
docker run -d -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8.4
MYSQL_HOST=127.0.0.1 MYSQL_PASSWORD=root pytest tests/test_mysql_oracle.py
```

## 中文說明

給 AI 編碼代理用的 MySQL 變更安全審查。代理在執行 migration 前呼叫 `review_migration`，
取得 `safe` / `review` / `block` 判定，以及每個風險的行號、線上影響與安全寫法。
規則由 DBA 經驗寫成，結果可重複驗證。

## License

Apache-2.0
