name: Database Schema Reviewer
description: Reviews database schemas for normalization issues, missing indexes, naming inconsistencies, and scalability risks.
category: data
tags:
- database
- sql
- schema
- postgresql
- mysql
author: simplyutils

Database Schema Reviewer

What this skill does

This skill directs the agent to review a database schema — provided as SQL DDL, an ORM model file (Drizzle, Prisma, SQLAlchemy, ActiveRecord, etc.), or a plain description — and produce a prioritized list of issues. It checks normalization, indexes, constraints, naming conventions, nullable columns, and overall scalability. Every issue includes a concrete SQL or ORM fix.

Use this before deploying a new schema to production, during code review of a migration file, or when a database is growing and you're starting to feel query pain.

How to use

Claude Code / Cline

Copy this file to .agents/skills/database-schema-reviewer/SKILL.md in your project root.

Then share your schema and ask:
- "Use the Database Schema Reviewer skill on shared/schema.ts."
- "Review this SQL migration file for schema issues using the Database Schema Reviewer skill."

Provide the full schema file, a SQL DDL dump, or paste the relevant CREATE TABLE statements.

Cursor

Add the "Prompt / Instructions" section to your .cursorrules file. Open your schema or migration file and ask Cursor to review it.

Codex

Paste the schema DDL or ORM model definitions into the chat along with the instructions below. Include any known query patterns if you want index recommendations tailored to your workload.

The Prompt / Instructions for the Agent

When asked to review a database schema, follow these steps:

1. Parse the schema. Accept any format:
2. SQL DDL (CREATE TABLE statements)
3. Prisma schema (model blocks)
4. Drizzle ORM table definitions
5. SQLAlchemy/Django/ActiveRecord model classes

6. A plain-text description of tables and columns

7. Check normalization:

8. 1NF: Flag any column that stores multiple values (comma-separated lists, JSON arrays used as a substitute for a proper relation)
9. 2NF: In tables with composite primary keys, flag non-key columns that depend on only part of the key

10. 3NF: Flag transitive dependencies — non-key columns that describe another non-key column rather than the primary key (e.g., zip_code and city in the same table as user_id)

11. Check indexes:

12. Foreign key columns almost always need an index — flag any FK column without one
13. Columns frequently used in WHERE, ORDER BY, or JOIN conditions should be indexed — infer from column names (e.g., email, status, created_at, user_id)
14. Unique constraints are implicit indexes, but flag columns that should be unique but aren't (e.g., email in a users table)

15. Flag any table with no index beyond its primary key if it's likely to be queried by non-PK columns

16. Check constraints:

17. Flag foreign key relationships that exist logically but have no REFERENCES constraint
18. Flag columns that should have NOT NULL but are nullable: primary-purpose columns like email, name, created_at
19. Flag columns that should have a DEFAULT value but don't (e.g., created_at, boolean flags)

20. Flag missing ON DELETE / ON UPDATE behavior on foreign keys

21. Check naming conventions:

22. Table names should be consistent: all lowercase_snake_case plural, or all PascalCase — flag inconsistencies
23. Column names should be consistent: all lowercase_snake_case — flag camelCase or mixed styles
24. Primary keys should consistently be named id (or table_id) — flag non-standard PK names
25. Foreign keys should be named referenced_table_id — flag deviations

26. Boolean columns should be named with is_, has_, or can_ prefix — flag ambiguous names like active or enabled

27. Flag scalability risks:

28. Using TEXT or VARCHAR(MAX) for columns that are used in WHERE clauses or indexes
29. Storing large blobs (images, documents) in the database instead of referencing an object store
30. Tables that are likely to grow very large with no partitioning strategy mentioned
31. Using ENUM types in PostgreSQL/MySQL where the values change frequently (painful to alter)

32. SELECT * traps: wide tables with many columns that are rarely all needed together (suggest splitting)

33. Suggest missing tables or relationships if the schema implies data that has no home (e.g., a role column on users with no roles table, suggesting future extensibility issues).

34. Prioritize findings:

35. High — data integrity risk or will cause query failures at scale
36. Medium — performance risk or maintainability issue

37. Low — style/convention issue

38. Output format:

## Schema Review Results

### Summary
[X] High · [Y] Medium · [Z] Low findings across [N] tables

---

### High Priority

#### [Issue title] — [Table.column]
**Problem:** [Plain English explanation]
**Risk:** [What goes wrong if this isn't fixed]
**Fix:**
```sql
[concrete SQL or ORM fix]

[repeat by priority level]

Passed Checks

• [List areas that look good]
  ```

Example

Input to Agent:

"Use the Database Schema Reviewer skill on this schema:

```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
role VARCHAR(50),
address TEXT,
city VARCHAR(100),
zip VARCHAR(20),
created TIMESTAMP
);

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
userId INTEGER,
items TEXT,
status VARCHAR(50),
total DECIMAL(10,2)
);
```"

Output from Agent:

Schema Review Results

Summary

4 High · 3 Medium · 2 Low findings across 2 tables

---

High Priority

Missing UNIQUE constraint on users.email

Problem: Email is the primary identifier for login but has no unique constraint. Duplicate accounts can be created.
Risk: Authentication bugs, data corruption, duplicate account merging nightmare.
Fix:
sql ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email); CREATE INDEX idx_users_email ON users (email);

orders.userId has no foreign key constraint

Problem: userId references the users table logically but has no REFERENCES constraint. Orphaned orders with deleted users will exist silently.
Risk: Referential integrity violations, broken order history views.
Fix:
sql ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (userId) REFERENCES users(id) ON DELETE RESTRICT; CREATE INDEX idx_orders_user_id ON orders (userId);

orders.items stores multiple values in a TEXT column (1NF violation)

Problem: Storing order items as a text blob violates first normal form and makes querying individual items impossible without string parsing.
Risk: Impossible to query "which orders contain product X", no referential integrity on items.
Fix: Create an order_items table:
sql CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, unit_price DECIMAL(10,2) NOT NULL );

---

Medium Priority

users.city and users.zip are a transitive dependency (3NF violation)

Problem: City and zip code describe each other, not the user. Moving address data to an addresses table is cleaner for reuse and consistency.

users.created should be NOT NULL with a DEFAULT

Fix: created TIMESTAMP NOT NULL DEFAULT NOW()

Naming inconsistency: orders uses order_id and userId (mixed conventions)

Fix: Rename to id and user_id to match PostgreSQL snake_case convention.

---

Passed Checks

• Primary keys are present on both tables
• DECIMAL used correctly for monetary values (not FLOAT)