| name | mariadb-agents-schema-reviewer |
| description | Use when reviewing a proposed MariaDB schema before it ships, auditing an existing schema for engine / indexing / naming / normalization problems, or validating a migration DDL. Prevents the common mistake of shipping a schema with MyISAM tables, UUID-text PKs, missing tenant indexes, utf8 charset, or composite indexes in the wrong column order. Covers a deterministic schema-review checklist : storage-engine choice, primary-key type, indexing strategy and column-order, charset / collation, normalization fitness, multi-tenant pattern detection, naming-convention adherence, with severity grading and cross-references to mariadb-core-storage-engines, mariadb-syntax-indexing, mariadb-impl-schema-design. Keywords: schema review, schema audit, review my schema, is this schema correct, schema checklist, design review, storage engine audit, index audit, primary key audit, normalization check, multi-tenant check, naming convention, DDL review, before I ship this schema, ENGINE=InnoDB, MyISAM, BIGINT AUTO_INCREMENT, UUID primary key, utf8mb4, ROW_FORMAT=DYNAMIC, composite index column order, leftmost-prefix, how do I review a schema, what is wrong with my schema, is my table design good, getting started with schema review
|
| license | MIT |
| compatibility | Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x. |
| metadata | {"author":"OpenAEC-Foundation","version":"1.0"} |
mariadb-agents-schema-reviewer
A deterministic procedure for reviewing a MariaDB schema (CREATE TABLE DDL, migration script, or existing schema dump) before it ships. This is an ORCHESTRATION skill : it does not teach syntax, it applies a fixed 10-dimension checklist and produces a graded findings report. Every check cites the canonical skill that explains the underlying rule.
Quick Reference
- Run ALL 10 review dimensions in order. Never skip a dimension because the schema "looks fine".
- Grade every finding with one of three severities : BLOCKER, WARNING, SUGGESTION.
- BLOCKER : the schema must not ship. Examples : MyISAM on a transactional table, a table with no PRIMARY KEY, FLOAT used for a money column.
- WARNING : the schema works but carries a known cost or footgun. Examples : CHAR(36) UUID-text PK on InnoDB,
utf8 (utf8mb3) charset on text columns, a multi-tenant table with no leftmost tenant_id index.
- SUGGESTION : a stylistic or maintainability improvement. Examples : inconsistent naming case, oversized VARCHAR, TEXT where VARCHAR fits.
- Every finding cites the canonical skill that explains the rule. NEVER report a problem without a skill-ref.
- Output a single findings table : dimension, severity, location, problem, fix, skill-ref. Close with a verdict line.
- A clean schema still gets a report : the table has zero rows and the verdict is
PASS.
The 10 Review Dimensions
| # | Dimension | What it checks | Canonical skill |
|---|
| 1 | Storage engine | InnoDB for transactional tables ; flag MyISAM | mariadb-core-storage-engines |
| 2 | Primary key | every table has a PK ; flag UUID-text PK | mariadb-impl-schema-design |
| 3 | Indexing | composite column-order, redundant indexes, FK indexes | mariadb-syntax-indexing |
| 4 | Charset / collation | utf8mb4 on text columns ; flag utf8 / latin1 | mariadb-errors-encoding-and-collation |
| 5 | Normalization | repeating groups, unjustified denormalization | mariadb-impl-schema-design |
| 6 | Multi-tenant | detect tenant_id ; flag missing leftmost tenant index | mariadb-impl-schema-design |
| 7 | Naming | case consistency, reserved words, Frappe tab pattern | mariadb-impl-schema-design |
| 8 | Constraints | missing CHECK / FK where invariants are implied | mariadb-syntax-check-constraints |
| 9 | Data types | DECIMAL for money, oversized VARCHAR, TEXT misuse | mariadb-syntax-sql-ddl |
| 10 | JSON | JSON column without CHECK (JSON_VALID(col)) | mariadb-syntax-json |
The full rule set per dimension (IF condition THEN severity) is in references/methods.md.
Review Decision Tree
For each table in the schema :
1. ENGINE clause present ?
absent -> NOTE : engine defaults to InnoDB (acceptable, 10.2+)
MyISAM / MEMORY on a table with FK / transactional intent -> BLOCKER
ARIA on user data -> WARNING
-> ref mariadb-core-storage-engines
2. PRIMARY KEY present ?
absent -> BLOCKER (InnoDB synthesises a hidden 6-byte PK, unindexable)
CHAR(36) / VARCHAR(36) UUID-text PK -> WARNING (use BINARY(16))
-> ref mariadb-impl-schema-design
3. Indexes :
composite index leftmost column not the equality predicate -> WARNING
two indexes where one is a prefix of the other -> SUGGESTION (redundant)
FOREIGN KEY column with no index -> WARNING
-> ref mariadb-syntax-indexing
4. CHARSET on text columns :
utf8 / utf8mb3 -> WARNING (3-byte, no emoji, no full Unicode)
latin1 with no explicit reason -> WARNING
utf8mb4 -> PASS
-> ref mariadb-errors-encoding-and-collation
5. Normalization :
column names like addr1, addr2, addr3 (repeating group) -> WARNING
comma-separated list stored in one column -> WARNING
denormalized copy with no stated reason -> SUGGESTION
-> ref mariadb-impl-schema-design
6. Multi-tenant :
tenant_id / org_id / company column present ?
YES and no index starts with it -> WARNING
YES and PK does not lead with it (for tenant-scoped tables) -> SUGGESTION
-> ref mariadb-impl-schema-design
7. Naming :
mixed case across tables (Users + order_line) -> SUGGESTION
reserved word as identifier without backticks -> BLOCKER
tab<Doctype> prefix -> NOTE : Frappe/ERPNext schema, do not rename
-> ref mariadb-impl-schema-design
8. Constraints :
status / type column with finite value set, no CHECK -> SUGGESTION
child table referencing a parent, no FOREIGN KEY -> WARNING
-> ref mariadb-syntax-check-constraints
9. Data types :
FLOAT / DOUBLE for a money / price / amount column -> BLOCKER
VARCHAR(255) for a country code / boolean flag -> SUGGESTION
TEXT for a column that is always short -> SUGGESTION
-> ref mariadb-syntax-sql-ddl
10. JSON :
JSON column without CHECK (JSON_VALID(col)) -> WARNING
JSON column queried by path with no virtual-column index -> SUGGESTION
-> ref mariadb-syntax-json
JSON Storage Caveat
MariaDB JSON is a LONGTEXT alias, not native binary. Use CHECK (JSON_VALID(col)) for structure ; use functional indexes on virtual columns for index access. A JSON column without that CHECK accepts 'not even json' silently, so dimension 10 grades a missing CHECK as a WARNING, not a SUGGESTION.
Output Format
Always produce a findings table, then a verdict. Severity sort order : BLOCKER first, then WARNING, then SUGGESTION.
## Schema Review : <schema name or "supplied DDL">
| Dimension | Severity | Location | Problem | Fix | Skill ref |
|-----------|----------|----------|---------|-----|-----------|
| Storage engine | BLOCKER | `orders` | ENGINE=MyISAM on a transactional table | ALTER TABLE orders ENGINE=InnoDB | mariadb-core-storage-engines |
| Primary key | WARNING | `users.id` | CHAR(36) UUID-text PK bloats secondary indexes | Use BINARY(16) with UUID_TO_BIN(uuid, 1) | mariadb-impl-schema-design |
| ... | ... | ... | ... | ... | ... |
**Verdict** : FAIL (1 blocker, 1 warning) | Re-review after blockers are fixed.
Verdict rules :
- Any BLOCKER ->
FAIL. The schema must not ship.
- Zero BLOCKER, one or more WARNING ->
PASS WITH WARNINGS. May ship, but warnings carry documented cost.
- Zero BLOCKER and zero WARNING ->
PASS.
Cross-Reference Map
This skill orchestrates and references the following skills. When a finding needs more depth than the fix column allows, point the user at the cited skill :
mariadb-core-storage-engines : engine selection, InnoDB vs MyISAM vs Aria.mariadb-impl-schema-design : PK strategy, normalization, multi-tenant pattern, naming.mariadb-syntax-indexing : leftmost-prefix rule, composite column order, FK indexes.mariadb-errors-encoding-and-collation : utf8mb3 vs utf8mb4, collation pitfalls.mariadb-syntax-check-constraints : CHECK syntax and JSON_VALID enforcement.mariadb-syntax-json : JSON-as-LONGTEXT, virtual-column functional indexes.mariadb-syntax-sql-ddl : column data types, VARCHAR vs TEXT, DECIMAL for money.
When NOT to Use This Skill
- For tuning an individual slow query : use
mariadb-agents-query-optimizer.
- For writing new DDL from scratch : use
mariadb-impl-schema-design and mariadb-syntax-sql-ddl directly.
- This skill reviews structure, not data : it does not inspect row contents or run profiling.
References
references/methods.md : the complete 10-dimension review procedure, each check as a deterministic IF / THEN rule with severity and skill-ref.references/examples.md : 8 worked reviews including a MyISAM schema, a multi-tenant schema, a Frappe-style schema, a JSON-heavy schema, a clean schema, and a migration DDL.references/anti-patterns.md : 6 reviewer anti-patterns, why each one ships broken schemas, and the correct procedure.
