Db Enforcer

DB Enforcer

Overview

Enforces data integrity and architectural consistency between the TypeScript application layer and the PostgreSQL persistence layer. Prevents type drift by ensuring CHECK constraints mirror TypeScript types, migrations are generated before applying changes, and Row-Level Security protects every table.

When to use: Schema design, migration planning, RLS policy authoring, Prisma model mapping, constraint auditing, zero-downtime deployments.

When NOT to use: Application-level business logic, frontend state management, non-PostgreSQL databases. For full RLS auditing, performance tuning, and compliance validation, use the database-security skill instead.

Quick Reference

Synchronization Protocol

Every schema modification MUST follow these steps:

1. Type-to-DB Verification: When adding an enum or union in TS, verify the equivalent CHECK constraint in SQL 2. Migration-First Generation: Generate SQL migrations using prisma migrate dev --create-only BEFORE applying 3. Naming Alignment: Enforce snake_case in SQL and camelCase in TS via explicit @map/@@map directives 4. Integrity Audit: Run prisma validate and check for missing indices on relation scalars 5. RLS Verification: Confirm every new table has RLS enabled with appropriate policies 6. Lock Assessment: Evaluate whether migration requires CREATE INDEX CONCURRENTLY or NOT VALID patterns

PostgreSQL Version Requirements

Several patterns in this skill require specific PostgreSQL versions:

Common Mistakes

Naming Conventions

Prisma models use camelCase in TypeScript and must map to snake_case in PostgreSQL:

Deployment Pipeline

Migrations follow a strict pipeline order:

1. prisma migrate dev --create-only -- generate and review SQL locally 2. prisma validate -- verify schema consistency 3. Apply to staging/preview database and run integration tests 4. prisma migrate deploy -- apply in CI/CD pipeline to production 5. Monitor for lock contention and query plan regressions

Relationship to Other Skills

• `database-security`: Covers full RLS auditing, PGAudit configuration, Supabase-specific patterns, Convex auth guards, and compliance validation. Use database-security for in-depth policy review and access simulation. Use db-enforcer for schema design and migration patterns that include RLS as part of the integrity workflow.

Delegation

• Audit existing schema for missing constraints or indices: Use Explore agent
• Plan a zero-downtime migration strategy for production databases: Use Plan agent
• Execute a full schema refactor with type alignment and RLS setup: Use Task agent
• Review RLS policies for bypasses and performance issues: Use database-security skill

References

• PostgreSQL integrity patterns, UUIDv7, virtual columns, and temporal constraints
• Prisma architecture, TypedSQL, extensions, and edge-first patterns
• Migration safety protocols, destructive changes, and rollback strategies
• Row-Level Security, column-level security, and audit logging

Migration Safety

The Migration Lifecycle

1. Generate: Use prisma migrate dev --create-only to review the SQL first 2. Audit: Check for destructive changes (DROP COLUMN, RENAME COLUMN) 3. Test: Apply to a staging/preview database before production 4. Execute: Run prisma migrate deploy in the CI/CD pipeline

Zero-Downtime Column Rename

Never rename a column in a single deployment. Use the expand-and-contract pattern across multiple deployments:

Phase 1 — Expand: add new column

ALTER TABLE users ADD COLUMN display_name TEXT;

Phase 2 — Dual-write in application code

Update the application to write to both name and display_name simultaneously. Deploy this before any data migration.

await prisma.user.update({
  where: { id },
  data: { name: value, displayName: value },
});

Phase 3 — Backfill existing rows

Run as a separate migration after dual-write is deployed and stable:

UPDATE users
SET display_name = name
WHERE display_name IS NULL;

For large tables, use the batched backfill pattern described in the Backfill Patterns section.

Phase 4 — Migrate reads to new column

Deploy application code that reads exclusively from display_name. Keep dual-write active during this phase.

Phase 5 — Wait for rollback window

Allow at least 48 hours before proceeding. If rollback is needed, the old column still has current data.

Phase 6 — Contract: drop old column

Deploy a separate migration to remove the old column only after the rollback window has passed:

ALTER TABLE users DROP COLUMN name;

Idempotent Migrations

Migrations that can safely re-run prevent failures in pipelines where migrations may execute multiple times (e.g., due to retries or multi-region deploys).

ALTER TABLE users ADD COLUMN IF NOT EXISTS verified_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_users_verified_at ON users (verified_at);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_name = 'users_email_verified_check'
      AND table_name = 'users'
  ) THEN
    ALTER TABLE users ADD CONSTRAINT users_email_verified_check
      CHECK (email ~* '^[^@]+@[^@]+\.[^@]+$');
  END IF;
END $$;

For enum types, check existence before creating:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
    CREATE TYPE user_status AS ENUM ('active', 'suspended', 'deleted');
  END IF;
END $$;

Lock Management

Set a Lock Timeout

Always set a lock timeout before DDL statements to prevent indefinite blocking:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN last_seen_at TIMESTAMPTZ;

If the lock cannot be acquired within the timeout, the statement fails immediately rather than blocking all queries on that table.

Avoid Long-Running Transactions During DDL

DDL inside a long transaction holds locks for the entire transaction duration. Keep DDL statements in short, isolated transactions:

BEGIN;
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS fulfilled_at TIMESTAMPTZ;
COMMIT;

Never combine DDL with bulk DML in the same transaction on high-traffic tables.

CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY builds the index without holding a lock on the table, allowing reads and writes to continue. It cannot run inside a transaction block.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id
  ON orders (user_id);

In Prisma raw migrations, wrap concurrent index creation outside transactions:

-- This file must not be wrapped in BEGIN/COMMIT by the migration runner
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status
  ON orders (status)
  WHERE status != 'completed';

If a concurrent index build fails partway through, it leaves an invalid index. Clean it up before retrying:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

NOT NULL Addition on Large Tables

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill (run as a batch job)
UPDATE users SET display_name = name WHERE display_name IS NULL;

-- Step 3: Add constraint without full lock
ALTER TABLE users ADD CONSTRAINT users_display_name_not_null
  CHECK (display_name IS NOT NULL) NOT VALID;

-- Step 4: Validate separately
ALTER TABLE users VALIDATE CONSTRAINT users_display_name_not_null;

Backfill Patterns

Batched UPDATE with LIMIT

Backfilling millions of rows in a single UPDATE locks the table and risks transaction log exhaustion. Use cursor-based batching instead:

DO $$
DECLARE
  batch_size INT := 1000;
  last_id UUID := '00000000-0000-0000-0000-000000000000';
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET display_name = name
    WHERE id > last_id
      AND display_name IS NULL
      AND id IN (
        SELECT id FROM users
        WHERE id > last_id
          AND display_name IS NULL
        ORDER BY id
        LIMIT batch_size
      )
    RETURNING id INTO last_id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

Monitoring Backfill Progress

Track progress without interrupting the backfill:

SELECT
  COUNT(*) FILTER (WHERE display_name IS NOT NULL) AS backfilled,
  COUNT(*) FILTER (WHERE display_name IS NULL)     AS remaining,
  COUNT(*)                                         AS total,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE display_name IS NOT NULL) / COUNT(*),
    2
  ) AS pct_complete
FROM users;

Avoiding Table Locks During Backfill

• Use small batch sizes (500–2000 rows) with short sleeps between batches
• Target rows by primary key range, not offset, to avoid full scans
• Run backfill during low-traffic windows when possible
• Never wrap the entire backfill in a single transaction

CI/CD Integration

Pre-Deploy vs Post-Deploy Migrations

Classify each migration before running it in the pipeline:

Running Migrations in the Pipeline

# Verify schema is in sync before deploying
npx prisma validate

# Apply pending migrations (non-interactive, safe for CI)
npx prisma migrate deploy

# Run post-deploy migrations separately after smoke tests pass
npx prisma migrate deploy --schema=prisma/post-deploy.prisma

For raw SQL migrations outside Prisma:

psql "$DATABASE_URL" \
  --set ON_ERROR_STOP=1 \
  --single-transaction \
  -f db/migrations/042_add_fulfilled_at.sql

Use --single-transaction for DDL-only migrations where atomicity is safe. Omit it when the migration contains CREATE INDEX CONCURRENTLY (which cannot run inside a transaction).

Deployment Rollback

Every migration must have a documented rollback. Store rollback scripts alongside forward migrations:

db/migrations/042_add_fulfilled_at.sql
db/migrations/042_add_fulfilled_at.rollback.sql

For irreversible changes (data deletion, column removal), verify a point-in-time backup exists before running the migration.

Numbered Migration Standard

Use 3-digit numbered sequences for clarity and ordering:

db/migrations/001_initial_schema.sql
db/migrations/002_add_roles.sql
db/migrations/003_add_team_permissions.sql

Rollback Strategy

Every migration must have a corresponding rollback plan documented, even if not automated. For irreversible changes (data deletion, column removal), ensure data is backed up before execution.

PostgreSQL Integrity

Native UUIDv7 Support (PostgreSQL 18+)

PostgreSQL 18 introduces the native uuidv7() function (RFC 9562). This is the preferred primary key format, combining global uniqueness with sequential ordering for significantly improved B-tree index performance and reduced page splits. The implementation includes a 12-bit sub-millisecond timestamp fraction that guarantees monotonicity within a session.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  email TEXT UNIQUE NOT NULL
);

For PostgreSQL versions before 18, use the pgcrypto extension with gen_random_uuid() (UUIDv4) or install a third-party extension for UUIDv7 support.

Security note: UUIDv7 embeds a 48-bit timestamp, leaking creation time. Avoid exposing UUIDv7 primary keys in public-facing APIs where creation time is sensitive.

Virtual Generated Columns (PostgreSQL 18+)

PostgreSQL 18 introduces virtual generated columns that occupy zero disk space and are calculated on the fly during SELECT. Virtual is the default kind in PostgreSQL 18; the VIRTUAL keyword is optional.

CREATE TABLE products (
  price_cents INTEGER NOT NULL,
  tax_rate DECIMAL NOT NULL,
  -- VIRTUAL is default in PG 18, STORED writes to disk
  total_price_cents INTEGER GENERATED ALWAYS AS (price_cents * (1 + tax_rate)) VIRTUAL
);

Limitations of virtual columns: Cannot be indexed (indexing support planned for PostgreSQL 19), cannot use user-defined types or functions in the generation expression, and cannot be logically replicated.

For PostgreSQL versions before 18, only STORED generated columns are available.

Advanced CHECK Constraints

Enforce business logic at the database level. CHECK constraints mirror TypeScript types to prevent drift.

ALTER TABLE orders
ADD CONSTRAINT check_discount_logic
CHECK (discount_price < original_price);

Conditional constraints for enums ensure data consistency:

ALTER TABLE tasks
ADD CONSTRAINT check_completion_date
CHECK (
  (status = 'COMPLETED' AND completed_at IS NOT NULL) OR
  (status != 'COMPLETED' AND completed_at IS NULL)
);

Temporal Constraints

Define uniqueness over time ranges to prevent overlapping schedules or double-bookings natively.

CREATE TABLE bookings (
  room_id INTEGER,
  booking_period TSTZRANGE,
  EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)
);

NOT VALID Constraint Pattern

Add constraints to large tables without locking the database for hours. This is a two-step process:

1. Add as NOT VALID (takes a brief lock, does not scan existing rows):

ALTER TABLE logs
ADD CONSTRAINT check_level
CHECK (level IN ('INFO', 'WARN', 'ERROR')) NOT VALID;

2. Validate later (scans rows but only takes a SHARE UPDATE EXCLUSIVE lock):

ALTER TABLE logs VALIDATE CONSTRAINT check_level;

Prisma Architecture

TypedSQL

Replace raw SQL strings with .sql files that generate fully typed functions. TypedSQL provides type-safe inputs and outputs while preserving full SQL flexibility.

Workflow:

1. Create a .sql file (the filename must be a valid JS identifier and cannot start with $):

-- prisma/sql/get_active_users.sql
SELECT u.id, u.name, COUNT(p.id) as "postCount"
FROM "User" u
LEFT JOIN "Post" p ON u.id = p."authorId"
GROUP BY u.id, u.name

2. Run prisma generate to produce typed functions 3. Import and execute with full type safety:

import { PrismaClient } from './generated/prisma/client';
import { getActiveUsers } from './generated/prisma/sql';

const prisma = new PrismaClient();
const users = await prisma.$queryRawTyped(getActiveUsers());

Parameters are passed as typed function arguments:

import { getUsersByAge } from './generated/prisma/sql';

const users = await prisma.$queryRawTyped(getUsersByAge(18, 30));

Relation Mode (Emulated Integrity)

In environments that do not support foreign keys (PlanetScale, certain Vitess setups), use emulated relation mode. GA since Prisma 4.8.0.

Two modes are available:

• "foreignKeys" (default for relational databases) -- uses database-level foreign keys
• "prisma" -- emulates referential integrity in Prisma Client with additional queries

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

You MUST manually create indices for all scalar fields used in relations. Without foreign keys, the database does not auto-create these indices, leading to full table scans:

model Post {
  id       Int @id @default(autoincrement())
  authorId Int
  author   User @relation(fields: [authorId], references: [id])

  @@index([authorId])
}

Performance note: Emulated mode uses additional queries per operation to maintain integrity. Prefer native foreign keys when the database supports them.

Extensions for Cross-Cutting Concerns

Use Prisma Extensions for soft deletes, automatic auditing, and other middleware patterns:

const prisma = new PrismaClient().$extends({
  model: {
    user: {
      async softDelete(id: string) {
        return prisma.user.update({
          where: { id },
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

Edge-First Query Engine

Prisma uses the TypeScript/WASM engine by default, eliminating the need for bulky Rust binaries in Edge Functions. Ensure prisma generate is run with the correct engine target for your deployment platform (Vercel, Cloudflare).

Native Distinct and Skip Scan

Use PostgreSQL performance improvements with native Prisma filters:

const uniqueUsers = await prisma.user.findMany({
  distinct: ['email'],
  take: 10,
});

RLS and Security

Enabling RLS

Every table in a Supabase or Neon project MUST have RLS enabled:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Standard Policy Patterns

Own-Data Access

The default policy for personal data:

CREATE POLICY "Users can manage their own projects"
ON projects
FOR ALL
USING (auth.uid() = user_id);

Team-Based Access

Use EXISTS subqueries for permission checks via join tables:

CREATE POLICY "Team members can view shared data"
ON team_data
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.team_id = team_data.team_id
    AND team_members.user_id = auth.uid()
  )
);

Security Definers vs Security Invokers

• Definer: Function runs with the owner's privileges. Use sparingly and only for trusted administrative operations.
• Invoker: Function runs with the caller's privileges. Recommended for API integration where RLS should still apply.

Column-Level Security (CLS)

Use PostgreSQL Views to hide sensitive columns from public APIs:

CREATE VIEW public_user_profiles AS
SELECT id, name, avatar_url
FROM users
WHERE is_public = true;

This prevents exposure of password hashes, internal IDs, or other sensitive fields through the public-facing API layer.

Audit Logging with Trigger-Based History

PostgreSQL does not have native SQL:2011 system versioning (SYSTEM_TIME periods). Use trigger-based audit logging to maintain a complete history of all changes:

CREATE TABLE sensitive_data_history (
  history_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  changed_by TEXT NOT NULL DEFAULT current_user,
  row_data JSONB NOT NULL
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO sensitive_data_history (operation, row_data)
    VALUES (TG_OP, to_jsonb(OLD));
    RETURN OLD;
  ELSE
    INSERT INTO sensitive_data_history (operation, row_data)
    VALUES (TG_OP, to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sensitive_data_audit
AFTER INSERT OR UPDATE OR DELETE ON sensitive_data
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

For Supabase projects, the database-security skill covers PGAudit configuration and advanced audit trail patterns in more detail.