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
| Pattern |
API/Tool |
Key Points |
| Type-to-DB sync |
prisma migrate dev --create-only |
Generate SQL before applying changes |
| Naming alignment |
@map / @@map |
snake_case in SQL, camelCase in TS |
| Primary keys |
DEFAULT uuidv7() |
Sequential, globally unique, fast indexing (PG 18+) |
| Virtual columns |
GENERATED ALWAYS AS (...) VIRTUAL |
Zero disk cost, computed on read (PG 18+) |
| Temporal uniqueness |
EXCLUDE USING gist |
Prevent overlapping ranges natively |
| NOT VALID constraints |
ADD CONSTRAINT ... NOT VALID |
Add constraints without table locks |
| TypedSQL |
prisma.$queryRawTyped() |
Type-safe raw SQL via .sql files |
| Relation emulation |
relationMode = "prisma" |
Integrity in FK-less environments (GA since 4.8.0) |
| Soft deletes |
Prisma $extends |
Cross-cutting concern via client extensions |
| RLS standard |
(select auth.uid()) = user_id |
Default own-data access policy with initPlan caching |
| Team RLS |
EXISTS subquery |
Permission checks via join tables |
| Column-level security |
PostgreSQL Views |
Hide sensitive columns from public APIs |
Synchronization Protocol
Every schema modification MUST follow these steps:
- Type-to-DB Verification: When adding an enum or union in TS, verify the equivalent CHECK constraint in SQL
- Migration-First Generation: Generate SQL migrations using
prisma migrate dev --create-only BEFORE applying
- Naming Alignment: Enforce snake_case in SQL and camelCase in TS via explicit
@map/@@map directives
- Integrity Audit: Run
prisma validate and check for missing indices on relation scalars
- RLS Verification: Confirm every new table has RLS enabled with appropriate policies
- 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:
| Feature |
Minimum Version |
Fallback |
uuidv7() |
PostgreSQL 18 |
gen_random_uuid() (UUIDv4) via pgcrypto |
| Virtual columns |
PostgreSQL 18 |
STORED generated columns (PG 12+) |
EXCLUDE USING |
PostgreSQL 9.0 |
Application-level overlap checks |
NOT VALID |
PostgreSQL 9.1 |
Schedule constraint addition during downtime |
security_invoker |
PostgreSQL 15 |
Use security_definer with restricted grants |
Common Mistakes
| Mistake |
Correct Pattern |
| Running SQL changes manually without migrations |
Generate numbered migrations with prisma migrate dev --create-only before applying |
| Using auto-increment or raw IDs exposed in URLs |
Use UUIDv7 for globally unique, non-enumerable identifiers |
| Skipping CHECK constraints on enums or unions |
Add database-level CHECK constraints that mirror TypeScript types |
| Mixing snake_case and camelCase without explicit mapping |
Use @map and @@map to enforce snake_case in SQL and camelCase in TypeScript |
| Tables without Row-Level Security policies |
Apply RLS policies to every table, defaulting to (select auth.uid()) = user_id |
| DROP or RENAME column in a single deployment |
Use expand-and-contract: add new column, dual-write, backfill, switch reads, drop old |
| Adding NOT NULL to large tables with full lock |
Add column as NULL first, backfill, then add NOT NULL with NOT VALID |
| Creating indices without CONCURRENTLY |
Use CREATE INDEX CONCURRENTLY in raw SQL migrations to avoid table locks |
Using auth.uid() directly in RLS without subselect |
Wrap in (select auth.uid()) to trigger initPlan caching |
Assuming uuidv7() works on all PG versions |
Verify PostgreSQL 18+; fall back to gen_random_uuid() on older versions |
Naming Conventions
Prisma models use camelCase in TypeScript and must map to snake_case in PostgreSQL:
| Layer |
Convention |
Enforced By |
| TypeScript |
camelCase |
Prisma model field names |
| PostgreSQL |
snake_case |
@map / @@map |
| Enums |
UPPER_SNAKE |
CHECK constraints |
| Indices |
snake_case |
idx_table_column |
Deployment Pipeline
Migrations follow a strict pipeline order:
prisma migrate dev --create-only -- generate and review SQL locallyprisma validate -- verify schema consistency- Apply to staging/preview database and run integration tests
prisma migrate deploy -- apply in CI/CD pipeline to production- 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
1---
2name: db-enforcer
3description: 'Enforces database integrity for PostgreSQL and Prisma systems. Use when designing schemas, writing migrations, or configuring Row-Level Security. Use for type-safe SQL, naming alignment, constraint validation, zero-trust RLS policies, UUIDv7 primary keys, and zero-downtime deployments.'
4license: MIT
5metadata:
6 author: oakoss
7 version: '1.1'
8---
9
10# DB Enforcer
11
12## Overview
13
14Enforces 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.
15
16**When to use:** Schema design, migration planning, RLS policy authoring, Prisma model mapping, constraint auditing, zero-downtime deployments.
17
18**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.
19
20## Quick Reference
21
22| Pattern | API/Tool | Key Points |
23| --------------------- | ----------------------------------- | ---------------------------------------------------- |
24| Type-to-DB sync | `prisma migrate dev --create-only` | Generate SQL before applying changes |
25| Naming alignment | `@map` / `@@map` | snake_case in SQL, camelCase in TS |
26| Primary keys | `DEFAULT uuidv7()` | Sequential, globally unique, fast indexing (PG 18+) |
27| Virtual columns | `GENERATED ALWAYS AS (...) VIRTUAL` | Zero disk cost, computed on read (PG 18+) |
28| Temporal uniqueness | `EXCLUDE USING gist` | Prevent overlapping ranges natively |
29| NOT VALID constraints | `ADD CONSTRAINT ... NOT VALID` | Add constraints without table locks |
30| TypedSQL | `prisma.$queryRawTyped()` | Type-safe raw SQL via `.sql` files |
31| Relation emulation | `relationMode = "prisma"` | Integrity in FK-less environments (GA since 4.8.0) |
32| Soft deletes | Prisma `$extends` | Cross-cutting concern via client extensions |
33| RLS standard | `(select auth.uid()) = user_id` | Default own-data access policy with initPlan caching |
34| Team RLS | `EXISTS` subquery | Permission checks via join tables |
35| Column-level security | PostgreSQL Views | Hide sensitive columns from public APIs |
36
37## Synchronization Protocol
38
39Every schema modification MUST follow these steps:
40
411. **Type-to-DB Verification**: When adding an enum or union in TS, verify the equivalent CHECK constraint in SQL
422. **Migration-First Generation**: Generate SQL migrations using `prisma migrate dev --create-only` BEFORE applying
433. **Naming Alignment**: Enforce snake_case in SQL and camelCase in TS via explicit `@map`/`@@map` directives
444. **Integrity Audit**: Run `prisma validate` and check for missing indices on relation scalars
455. **RLS Verification**: Confirm every new table has RLS enabled with appropriate policies
466. **Lock Assessment**: Evaluate whether migration requires `CREATE INDEX CONCURRENTLY` or `NOT VALID` patterns
47
48## PostgreSQL Version Requirements
49
50Several patterns in this skill require specific PostgreSQL versions:
51
52| Feature | Minimum Version | Fallback |
53| ------------------ | --------------- | --------------------------------------------- |
54| `uuidv7()` | PostgreSQL 18 | `gen_random_uuid()` (UUIDv4) via `pgcrypto` |
55| Virtual columns | PostgreSQL 18 | `STORED` generated columns (PG 12+) |
56| `EXCLUDE USING` | PostgreSQL 9.0 | Application-level overlap checks |
57| `NOT VALID` | PostgreSQL 9.1 | Schedule constraint addition during downtime |
58| `security_invoker` | PostgreSQL 15 | Use `security_definer` with restricted grants |
59
60## Common Mistakes
61
62| Mistake | Correct Pattern |
63| -------------------------------------------------------- | ------------------------------------------------------------------------------------- |
64| Running SQL changes manually without migrations | Generate numbered migrations with `prisma migrate dev --create-only` before applying |
65| Using auto-increment or raw IDs exposed in URLs | Use UUIDv7 for globally unique, non-enumerable identifiers |
66| Skipping CHECK constraints on enums or unions | Add database-level CHECK constraints that mirror TypeScript types |
67| Mixing snake_case and camelCase without explicit mapping | Use `@map` and `@@map` to enforce snake_case in SQL and camelCase in TypeScript |
68| Tables without Row-Level Security policies | Apply RLS policies to every table, defaulting to `(select auth.uid()) = user_id` |
69| DROP or RENAME column in a single deployment | Use expand-and-contract: add new column, dual-write, backfill, switch reads, drop old |
70| Adding NOT NULL to large tables with full lock | Add column as NULL first, backfill, then add NOT NULL with NOT VALID |
71| Creating indices without CONCURRENTLY | Use `CREATE INDEX CONCURRENTLY` in raw SQL migrations to avoid table locks |
72| Using `auth.uid()` directly in RLS without subselect | Wrap in `(select auth.uid())` to trigger initPlan caching |
73| Assuming `uuidv7()` works on all PG versions | Verify PostgreSQL 18+; fall back to `gen_random_uuid()` on older versions |
74
75## Naming Conventions
76
77Prisma models use camelCase in TypeScript and must map to snake_case in PostgreSQL:
78
79| Layer | Convention | Enforced By |
80| ---------- | ----------- | ------------------------ |
81| TypeScript | camelCase | Prisma model field names |
82| PostgreSQL | snake_case | `@map` / `@@map` |
83| Enums | UPPER_SNAKE | CHECK constraints |
84| Indices | snake_case | `idx_table_column` |
85
86## Deployment Pipeline
87
88Migrations follow a strict pipeline order:
89
901. `prisma migrate dev --create-only` -- generate and review SQL locally
912. `prisma validate` -- verify schema consistency
923. Apply to staging/preview database and run integration tests
934. `prisma migrate deploy` -- apply in CI/CD pipeline to production
945. Monitor for lock contention and query plan regressions
95
96## Relationship to Other Skills
97
98- **`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.
99
100## Delegation
101
102- **Audit existing schema for missing constraints or indices**: Use `Explore` agent
103- **Plan a zero-downtime migration strategy for production databases**: Use `Plan` agent
104- **Execute a full schema refactor with type alignment and RLS setup**: Use `Task` agent
105- **Review RLS policies for bypasses and performance issues**: Use `database-security` skill
106
107## References
108
109- [PostgreSQL integrity patterns, UUIDv7, virtual columns, and temporal constraints](references/postgres-integrity.md)
110- [Prisma architecture, TypedSQL, extensions, and edge-first patterns](references/prisma-architecture.md)
111- [Migration safety protocols, destructive changes, and rollback strategies](references/migration-safety.md)
112- [Row-Level Security, column-level security, and audit logging](references/rls-security.md)
oakoss