schemachecker

Database schema health analyzer for Prisma, Drizzle, SQL and Mongoose.

Catch missing indexes, anti-patterns, and design issues before they hit production. Works as a CLI, GitHub Action, or Node.js library.

npm install -g schemachecker
schemachecker ./prisma/schema.prisma

  schemachecker  v0.1.9
  prisma/schema.prisma

  Health score   34/100  ██████████░░░░░░░░░░░░░░░░░░  Grade F

  Format: prisma   Models: 5   Fields: 41

  6 errors  9 warnings  10 suggestions

  User
     ERR  [SC007] "User.email" should have a unique constraint
             ↳ Add @unique to the field or @@unique([email]) at model level

  Post
     ERR  [SC002] "Post.userId" looks like a foreign key but has no index
             ↳ Add @@index([userId]) to "Post"

     WRN  [SC003] "Post" is missing an updatedAt timestamp
             ↳ Add: updatedAt DateTime @updatedAt

Why schemachecker

Most database issues that cause production incidents are detectable at schema design time — missing indexes on foreign keys, unbounded string columns, no soft delete, inconsistent naming. schemachecker catches all of these before you ever write a migration.

Requirements

• Node.js v14 or higher
• GROQ_API_KEY — only required for --ai-fix and --deep flags (free at console.groq.com)

Installation

# Install globally (recommended)
npm install -g schemachecker

# Or run without installing
npx schemachecker ./prisma/schema.prisma

Usage

# Analyze a Prisma schema
schemachecker ./prisma/schema.prisma

# Analyze a SQL file
schemachecker ./schema.sql

# Analyze a Drizzle schema
schemachecker ./src/db/schema.ts --format=drizzle

# Analyze a Mongoose model
schemachecker ./src/models/user.ts --format=mongoose

# Output as JSON (pipe into other tools)
schemachecker ./schema.prisma --output=json

# CI mode — fail the build if score drops below 80
schemachecker ./schema.prisma --fail-below=80 --output=ci

# AI-powered fix — auto-generate corrected models for all errors
schemachecker ./schema.prisma --ai-fix

# Deep analysis — holistic architectural review beyond static rules
schemachecker ./schema.prisma --deep

Supported formats

| Format | File types | Detection | |--------|-----------|-----------| | Prisma | .prisma | Automatic | | Drizzle ORM | .ts / .js | Automatic — detects pgTable, mysqlTable, sqliteTable | | Raw SQL | .sql | Automatic | | Mongoose | .ts / .js | Automatic or --format=mongoose |

Format is auto-detected from the file extension and contents. Use --format to override if detection fails.

AI Features

schemachecker includes two AI-powered modes powered by Groq + Llama 3.3. These require a free API key from console.groq.com — set it once in your environment and both flags work automatically.

export GROQ_API_KEY=your-key-here

Who needs the key? The AI calls are made from the machine running the CLI. If you're using --ai-fix or --deep yourself during development, you need your own key. If you're sharing schemachecker with your team, each person running those flags will need their own free key.

--ai-fix — Automated schema fixes

Runs the full rule-based analysis first, then sends each model that has errors to the AI. The AI returns a fully corrected version of that model addressing every issue at once — ready to copy-paste back into your schema.

schemachecker ./prisma/schema.prisma --ai-fix

  schemachecker AI fix  powered by Groq + Llama 3.3
  prisma/schema.prisma

  Issues detected by rules:
  ──────────────────────────────────────────────────────────

  Post
    ERR  [SC002] "Post.userId" looks like a foreign key but has no index
               ↳ Add @@index([userId]) to "Post"
    WRN  [SC003] "Post" is missing an updatedAt timestamp
               ↳ Add: updatedAt DateTime @updatedAt

  AI-generated fixes (all issues combined per model):
  ──────────────────────────────────────────────────────────

  Post
  Sending 2 issues to AI...

    ┌── copy-paste this fully fixed model ───────────
    │ model Post {
    │   id        String   @id @default(cuid())
    │   userId    String
    │   title     String
    │   body      String
    │   createdAt DateTime @default(now())
    │   updatedAt DateTime @updatedAt
    │
    │   @@index([userId])
    │ }
    └────────────────────────────────────────────────

Behavior:

• Shows all rule-based issues first so you have the full picture before any AI output
• Groups all issues per model into a single AI call — no partial fixes
• Returns copy-paste ready corrected code with every issue resolved
• Only sends models that have at least one error to the AI — models with only warnings or suggestions are shown with their rule-based hints and left for you to fix manually
• Exits with code 1 if any errors were found, 0 if clean

--deep — Architectural review

Goes beyond static rules to surface design-level concerns that a linter can't infer: security gaps, scalability bottlenecks, missing integrity constraints, and multi-tenancy issues. The AI is also aware of what the rule-based analysis already found, so it doesn't repeat those — it looks further.

schemachecker ./schema.prisma --deep

  schemachecker deep analysis  powered by Groq + Llama 3.3
  prisma/schema.prisma

  AI architectural insights
  ──────────────────────────────────────────────────────────
  [Security] User.passwordHash has no field enforcing minimum length — add
             validation at the application layer or a check constraint
  [Scale] Post.body is unbounded TEXT with no pagination strategy — large
          tables will cause slow full scans on high-volume tables
  [Integrity] Order and OrderItem have no FK cascade rules — deleting an
              Order may orphan items silently
  [Missing] No audit log model — for financial or compliance workloads,
            consider an immutable append-only event table
  [Design] Session.token is not indexed — every auth lookup will be a
           full table scan as session count grows
  ──────────────────────────────────────────────────────────
  Run without --deep for the standard rule-based report.

Behavior:

• Reviews the whole schema holistically — not model by model
• Aware of rule-based findings and explicitly looks beyond them
• Uses tags — [Security], [Scale], [Design], [Integrity], [Performance], [Missing] — for scannability
• Returns 4–7 focused, actionable findings with no filler
• Always exits with code 0 (it's advisory, not a hard gate)

Rules

| Rule | Severity | What it catches | |------|----------|----------------| | SC001 | 🔴 error | Model with no primary key | | SC002 | 🔴 error | Foreign key field with no index (userId without @@index) | | SC003 | 🟡 warning | Missing createdAt or updatedAt timestamps | | SC004 | 🔵 info | No soft delete field (deletedAt) | | SC005 | 🟡 warning | God model — more than 20 fields | | SC006 | 🟡 warning | Unbounded TEXT column in SQL (use VARCHAR(n)) | | SC007 | 🔴 error | email field without a unique constraint | | SC008 | 🟡 warning | Mixed camelCase and snake_case in same model | | SC009 | 🔵 info | Boolean field not prefixed with is/has/can | | SC010 | 🔵 info | JSON column in relational schema | | SC011 | 🟡 warning | Inconsistent tenantId across models (multi-tenancy smell) |

Rule notes

SC002 — Foreign key detection looks for fields ending in Id, _id, Fk, or _fk. It intentionally skips fields that are commonly external or non-relational identifiers:

publicId clientId sessionId requestId correlationId traceId externalId

If your FK field is being skipped unexpectedly, check if its name matches one of these patterns.

SC003 — Mongoose timestamps: true is detected automatically. If your Mongoose schema uses { timestamps: true } or { timestamps: { createdAt, updatedAt } } in the Schema options, SC003 will not fire — schemachecker treats those models as having timestamps already.

SC006 — Unbounded strings only applies to SQL schemas. Prisma, Drizzle, and Mongoose handle string sizing differently and are not checked by this rule.

SC010 — JSON columns are skipped entirely for Mongoose schemas since documents naturally store embedded objects and arrays.

Scoring

Every schema gets a 0–100 health score and a letter grade:

| Grade | Score | Meaning | |-------|-------|---------| | A | 90–100 | Production ready | | B | 75–89 | Minor issues | | C | 60–74 | Needs attention | | D | 40–59 | Significant problems | | F | 0–39 | Do not ship |

Errors cost 10 points each, warnings cost 4, suggestions cost 1. The penalty is normalized against your schema size so a single issue on a large schema doesn't tank the score unfairly.

Output formats

Terminal (default) — colored output with issue descriptions and fix suggestions.

JSON — machine-readable output for custom tooling:

schemachecker ./schema.prisma --output=json

{
  "score": 72,
  "grade": "C",
  "format": "prisma",
  "totalModels": 3,
  "totalFields": 24,
  "summary": { "errors": 2, "warnings": 4, "infos": 3 },
  "issues": [
    {
      "ruleId": "SC002",
      "severity": "error",
      "model": "Post",
      "field": "userId",
      "message": "\"Post.userId\" looks like a foreign key but has no index",
      "suggestion": "Add @@index([userId]) to \"Post\""
    }
  ]
}

CI — compact GitHub Actions compatible output:

schemachecker ./schema.prisma --output=ci --fail-below=80

Exit codes

| Code | Meaning | |------|---------| | 0 | No errors found, or score is above --fail-below threshold | | 1 | One or more errors found, or score dropped below --fail-below |

The --deep flag always exits 0 — deep analysis is advisory and will not fail a build.

Environment variables

| Variable | Required | Description | |----------|----------|-------------| | GROQ_API_KEY | Only for --ai-fix and --deep | Free API key from console.groq.com |

GitHub Actions

Add schemachecker to your CI pipeline to automatically check schema health on every pull request:

# .github/workflows/schemacheck.yml
name: Schema health check

on: [pull_request]

jobs:
  schemacheck:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20

      - name: Install schemachecker
        run: npm install -g schemachecker

      - name: Check schema health
        run: schemachecker ./prisma/schema.prisma --fail-below=80 --output=ci

The build fails automatically if the health score drops below your threshold. To also run AI fixes in CI, add GROQ_API_KEY as a repository secret and pass it as an environment variable:

      - name: Check schema health (with AI fix)
        env:
          GROQ_API_KEY: ${{ secrets.GROQ_API_KEY }}
        run: schemachecker ./prisma/schema.prisma --ai-fix

Options

| Option | Description | Example | |--------|-------------|---------| | --format | Force schema format | --format=mongoose | | --output | Output format: terminal, json, ci | --output=json | | --fail-below | Exit 1 if score is below n | --fail-below=80 | | --ai-fix | AI-generated fixes for all errors (requires GROQ_API_KEY) | --ai-fix | | --deep | Holistic AI architectural review (requires GROQ_API_KEY) | --deep | | --help | Show help | --help |

Use as a library

import { parseSchema, analyze } from 'schemachecker'

const schema = parseSchema('./prisma/schema.prisma')
const report = analyze(schema)

console.log(report.score)   // 72
console.log(report.grade)   // 'C'
console.log(report.issues)  // array of issues

Known limitations

• Composite foreign keys are not detected — SC002 only checks single-field FK patterns by name suffix
• Drizzle relations (relations() helper) are not parsed — only table definitions are analyzed
• Mongoose nested subdocuments (e.g. subscription: { endpoint: {...}, keys: {...} }) are intentionally skipped during field extraction to avoid false positives
• Drizzle auto-detection only recognizes pgTable, mysqlTable, and sqliteTable — use --format=drizzle for other helpers
• SQL indexes defined outside CREATE TABLE (separate CREATE INDEX statements) are parsed only when they appear after the table definition in the same file

Contributing

Issues and pull requests are welcome. To add a new lint rule:

1. Open src/rules/index.ts
2. Add a new rule object following the existing pattern
3. Add it to the ALL_RULES array at the bottom
4. Add a fixture file to tests/fixtures/ and write a test

License

MIT © Gaurav