CI-Enforced Database Standards

Every team writes down their database conventions. Few enforce them consistently. We moved ours into CI so a naming violation or a missing index fails the build before it reaches review.

Our examples use Prisma and Postgres, but the same approach applies to most ORMs and database setups, SQL and NoSQL alike. The core idea is the same: stand up a real instance, apply your schema, and verify conventions automatically.

The Problem With Schema Reviews

Humans are bad at checking naming conventions across forty columns.

Every team has database standards. Snake case for table and column names. Foreign keys need indexes. Every table gets created_at and updated_at. Date fields get indexed because they'll eventually appear in a WHERE clause. Email fields get indexed because someone will always search by email.

These rules are simple. They're also the first thing reviewers stop checking once the PR has more than three files. A migration that adds a userId column instead of user_id sails through review because the reviewer is focused on the business logic, not the naming. The inconsistency compounds over months until the schema is a mix of conventions that no one wants to clean up.

We moved the checks into CI. Now a PR that violates a database standard fails the same way a PR with a type error fails: automatically, before anyone has to notice.

What We Built

A workflow that stands up a real database, runs migrations, and inspects the result.

The workflow triggers on any PR that touches the database directory. It spins up a Postgres service container, applies all migrations against it, and then runs a series of SQL checks against the resulting schema. If any check fails, the PR fails.

No mocks. No schema parsing. The checks run against an actual Postgres instance with real migrations applied. If the schema that ends up in production would have a problem, this workflow catches it.

How It Works

Migration Verification

Our migration workflow has two steps. First, Prisma applies all existing migration files to a blank database in order. Then it compares the current schema definition against the database state. If they differ, it generates a new migration file containing the SQL to close the gap.

The CI workflow uses this to detect drift. It counts the migration files before and after running the command. If a new file appears, someone changed the schema without committing the corresponding migration.

- name: Verify migrations
  run: |
    FILES_BEFORE=$(ls ./db/migrations | wc -l)
    ls ./db/migrations > /tmp/migrations_before.txt
    DATABASE_URL=postgresql://user:password@localhost:5432/project ./scripts/migrate
    FILES_AFTER=$(ls ./db/migrations | wc -l)
    if [ $FILES_BEFORE -ne $FILES_AFTER ]; then
        echo "::error::Missing migration(s) detected"
        # Show what was generated
        ls ./db/migrations > /tmp/migrations_after.txt
        diff /tmp/migrations_before.txt /tmp/migrations_after.txt \
          | grep "^>" | sed 's/^> //'
        exit 1
    fi

This catches the common mistake of editing the schema and pushing without generating a migration. The CI runs the migration command itself and fails if a new migration was needed. The error output shows exactly which migration was generated and what SQL it contains, so the developer knows what to run locally.

Schema Verification

The real value is in the schema checks. After migrations are applied, a PL/pgSQL block inspects information_schema and pg_indexes to verify six conventions. Each check queries the live database and raises an exception on the first violation.

Foreign keys must have indexes

SELECT tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes pi
    WHERE pi.tablename = tc.table_name
      AND pi.indexdef LIKE '%' || kcu.column_name || '%'
  )

A foreign key without an index is a JOIN that scans the entire table. Postgres does not create indexes on foreign keys automatically. Every ORM tutorial mentions this. Every production database has at least one missing. This check makes it impossible to ship.

Every table needs timestamps

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND (
    NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE column_name = 'created_at'
        AND table_name = tables.table_name
    ) OR
    NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE column_name = 'updated_at'
        AND table_name = tables.table_name
    )
  )

created_at and updated_at are required on every table. No exceptions. If you think your table doesn't need them, you'll find out you were wrong six months from now when debugging a production issue and wishing you knew when a row was last modified.

Names must be snake_case

-- Tables
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name !~ '^[a-z][a-z0-9_]*$'
 
-- Columns
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND column_name !~ '^[a-z][a-z0-9_]*$'

Prisma defaults to camelCase when mapping model fields to columns. A single @@map or @map annotation missed in the schema file creates a userId column in a sea of user_id columns. The regex check catches it before it reaches any environment.

Date fields need indexes

SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE (c.data_type LIKE '%timestamp%' OR c.data_type LIKE '%date%')
  AND c.column_name != 'updated_at'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes pi
    WHERE pi.tablename = c.table_name
      AND pi.indexdef LIKE '%' || c.column_name || '%'
  )

Date and timestamp fields almost always end up in range queries, sorting, or filtering. The one exception is updated_at, which is excluded because it's primarily used for optimistic locking and record-keeping, not querying. Everything else gets an index or the build fails.

Email fields need indexes

SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE c.column_name = 'email'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes pi
    WHERE pi.tablename = c.table_name
      AND pi.indexdef LIKE '%' || c.column_name || '%'
  )

If a column is named email, someone is going to look up records by it. The login flow, the admin dashboard, the support tool, the deduplication job. An unindexed email lookup on a growing users table is a slow query waiting to happen. The check is narrow by design: it only targets columns literally named email, not every string field.

The Workflow

The full workflow file ties everything together. It triggers only when the database directory is modified, so unrelated PRs skip it entirely.

name: Database Standards
on:
  pull_request:
    paths:
      - 'db/**'
 
jobs:
  database-standards:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_USER: user
          POSTGRES_PASSWORD: password
          POSTGRES_DB: project
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - uses: ./.github/actions/setup
 
      - name: Verify migrations
        run: |
          FILES_BEFORE=$(ls ./db/migrations | wc -l)
          ls ./db/migrations > /tmp/migrations_before.txt
          DATABASE_URL=postgresql://user:password@localhost:5432/project ./scripts/migrate
          FILES_AFTER=$(ls ./db/migrations | wc -l)
          if [ $FILES_BEFORE -ne $FILES_AFTER ]; then
              echo "::error::Missing migration(s) detected"
              exit 1
          fi
 
      - name: Verify schema
        run: |
          psql postgresql://user:password@localhost:5432/project << 'EOF'
            DO $$ ... $$;  -- Schema checks from above
          EOF

A few things worth calling out:

Health checks on the service container. The pg_isready health check ensures the database is accepting connections before any step tries to use it. Without this, the migration step can fail intermittently because Postgres hasn't finished initializing.

Path filtering. The paths trigger means this workflow only runs when the PR modifies files in the database directory. A frontend-only PR doesn't wait for a database to spin up.

What It Catches

In the first month, this workflow caught violations that would have made it through code review:

A userId column on a new junction table. The developer forgot the @map("user_id") annotation in the Prisma schema. CI failed. Fixed in thirty seconds.
A foreign key without an index on a team_id column in a new projects table. The query planner would have scanned the full table on every join. The developer added @@index([team_id]) and the build went green.
A table missing updated_at. A lookup table for static configuration data. The developer argued it didn't need timestamps. We kept the rule. Three weeks later, we needed to debug when a config value changed.
A scheduled_at timestamp without an index. The column was added for a job scheduling feature. Every query against it would have been a sequential scan. The missing index was caught before the feature reached staging.
A missing migration file. A developer edited the schema definition but forgot to generate the corresponding migration file. The schema and the database would have drifted. CI caught the mismatch.

None of these are difficult fixes. All of them are easy to miss in review. That's the point. The workflow handles the mechanical checks so reviewers can focus on whether the data model is right, not whether the column names follow conventions.

Adding New Rules

The PL/pgSQL block is a list of FOR ... LOOP checks. Adding a new rule is a three-step process:

Write a query that returns rows violating the convention
Loop over the results and RAISE EXCEPTION with a descriptive message
Push the workflow update

There's no framework, no configuration file, no abstraction layer. Each check is a SQL query and an error message. A new engineer can read the block and understand every rule in five minutes.