Cursor / Claude Code / Windsurf rules 模板

# MariaDB project rules

## Schema conventions
- Charset: `utf8mb4`, collation `utf8mb4_unicode_ci` (or `utf8mb4_uca1400_ai_ci` on 11.5+)
- Primary keys: `BIGINT AUTO_INCREMENT PRIMARY KEY`. No composite PK unless join-table.
- Foreign keys: explicit `CONSTRAINT fk_<table>_<col>` named
- Timestamps: every table has `created_at`, `updated_at DATETIME DEFAULT CURRENT_TIMESTAMP`
- Money: `*_cents BIGINT NOT NULL`, never float/decimal in app code
- Booleans: `is_*` BOOLEAN (not TINYINT(1))
- Status: ENUM with explicit values + COMMENT explaining the state machine
- Soft delete: `deleted_at DATETIME NULL` (don't use `is_deleted`)
- Every table has a `COMMENT` describing its purpose
- Every column with non-obvious meaning has a `COMMENT`

## Engine and storage
- Default engine: InnoDB. Never MyISAM.
- Use `BIGINT` for IDs even if you think `INT` is enough
- Vector columns: only on MariaDB 11.8+, declare distance in index

## Query conventions
- Never `SELECT *` outside ad-hoc shell
- Every `UPDATE`/`DELETE` must have a `WHERE`. Bare-table mutations need
  `-- intentional: full table` comment
- `LIMIT` is required when result set could exceed 1000
- Prefer `EXISTS` over `IN (subquery)` when subquery could be large
- Use named indexes: `INDEX idx_<table>_<cols>`
- For JSON, use `JSON` type, not `TEXT`; access with `->>` operator

## Migration rules
- Never `DROP COLUMN` in a single deploy; add `ALTER TABLE ... DROP` 2 deploys later
- Schema changes go through Liquibase / Flyway / Atlas, never raw ALTER in prod
- Always wrap multi-statement migrations in a transaction where MariaDB supports it
  (DDL is not transactional; use staged rollback plans)

## Performance
- Run `EXPLAIN` before merging any new query
- Reject queries with `type=ALL` and `rows>10000`
- Slow log threshold is 500ms, anything above needs an index or rewrite

## Security
- App connects with least-privilege account (no `SUPER`, no `ALL PRIVILEGES`)
- Never log raw SQL with parameters in production
- Always use parameterized queries; never string-concatenate SQL
- TLS required for any non-localhost connection

## Differences from MySQL 8.0 (LLM tends to confuse these)
- `JSON_TABLE` syntax differs — refer to MariaDB KB before generating
- `caching_sha2_password` auth plugin does NOT exist in MariaDB; use `mysql_native_password`
- Default `utf8mb4` collation differs (MariaDB 11.5+: `utf8mb4_uca1400_ai_ci`)
- Window functions and CTE both work, but optimizer behavior differs
- `RETURNING` clause works since MariaDB 10.5 (`INSERT … RETURNING`)

---
description: MariaDB conventions and pitfalls
globs:
  - "**/*.sql"
  - "**/migrations/**"
  - "**/schema/**"
  - "**/*.ts"
  - "**/*.py"
apply: always
---

# MariaDB rules

(把上面"通用规则"全部粘贴到这里)

## Cursor-specific
- When asked to "write a query", first check if `lib/db/schema.ts` (or `*.sql`) exists
- Use the project's existing ORM if any (Prisma / Drizzle / SQLAlchemy)
- For new migrations, look at the last 3 migrations in the folder for style
- When adding a column, also update the relevant TypeScript / Python type

## Database access
- The `mariadb` MCP server is available with these tools:
  - `list_tables()` — see all tables
  - `describe_table(name)` — schema for one table
  - `run_query(sql, limit)` — read-only, 500-row cap
- Always use `describe_table` before writing a query against unfamiliar tables
- Never paste a query result with >50 rows back into chat

# CLAUDE.md

(把上面"通用规则"全部粘贴)

## Workflow
- Before writing any SQL, list the relevant tables with `list_tables` MCP tool
- For any UPDATE/DELETE, show me an EXPLAIN first
- For new migrations, place them under `db/migrations/YYYY-MM-DD-name.sql`
- After running migrations, update `db/schema.ts` to match

## Approved scripts
- `pnpm db:migrate` — apply pending migrations
- `pnpm db:reset` — drop and recreate dev DB
- `pnpm db:dump` — `mariadb-dump --single-transaction` of current dev DB

# Windsurf rules — MariaDB project

(通用规则)

## Cascade-specific
- Cascade can execute `pnpm db:*` scripts automatically — please ask before destructive ones
- Always commit migrations and `db/schema.ts` in the same commit
- When running tests, use the `mariadb-test` MCP server (separate test DB)

(通用规则)

## Project-specific
- ORM: Drizzle
- Migrations: `drizzle-kit generate`
- Tests use `mariadb:11.4` container per test suite