playbooks

home / skills / yuniorglez / gemini-elite-core / db-enforcer

db-enforcer skill

/skills/db-enforcer

This skill enforces data integrity between TypeScript and PostgreSQL by applying zero-trust, migration-safe patterns with Prisma 7 and PostgreSQL 18.

npx playbooks add skill yuniorglez/gemini-elite-core --skill db-enforcer

Review the files below or copy the command above to add this skill to your agents.

Files (5)
SKILL.md
3.8 KB
---
name: db-enforcer
id: db-enforcer
version: 2.1.0
description: "Guardian of Database Integrity. Architect of High-Performance PostgreSQL & Prisma 7 Systems. Expert in PostgreSQL 18, TypedSQL, and Zero-Trust RLS."
---

# 💎 Skill: DB Enforcer (v2.1.0)

## Executive Summary
The `db-enforcer` is the supreme guardian of data integrity and architectural consistency. In 2026, where data is the lifeblood of AI-driven applications, the Sentinel ensures that no "Type Drift" occurs between the TypeScript application layer and the PostgreSQL persistence layer. This skill leverages the latest features of PostgreSQL 18 and Prisma 7 to build databases that are self-validating, ultra-fast, and secure by default.

---

## 📋 Table of Contents
1. [The Synchronization Protocol](#the-synchronization-protocol)
2. [The "Do Not" List (Anti-Patterns)](#the-do-not-list-anti-patterns)
3. [PostgreSQL 18 Excellence](#postgresql-18-excellence)
4. [Prisma 7 Elite Patterns](#prisma-7-elite-patterns)
5. [Zero-Trust Data Security (RLS)](#zero-trust-data-security-rls)
6. [Migration Safety & Zero-Downtime](#migration-safety--zero-downtime)
7. [Reference Library](#reference-library)

---

## 🛠️ The Synchronization Protocol

Every schema modification MUST follow these 4 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 changes.
3.  **Naming Alignment**: Enforce `snake_case` in SQL and `camelCase` in TS via explicit mappings (`@map`, `@@map`).
4.  **Integrity Audit**: Run `bun x prisma validate` and check for missing indices on relation scalars.

---

## 🚫 The "Do Not" List (Anti-Patterns)

| Anti-Pattern | Why it fails in 2026 | Modern Alternative |
| :--- | :--- | :--- |
| **Manual SQL execution** | Unrecorded changes lead to drift. | Use **Numbered Migrations**. |
| **Exposing raw IDs** | Vulnerable to enumeration. | Use **Native UUIDv7**. |
| **Missing CHECK constraints**| Invalid data corrupts AI models. | Use **Database-level Validation**. |
| **Foreign Keys in Edge DBs**| PlanetScale/Vitess limitations. | Use **Prisma Relation Mode**. |
| **`select *` Queries** | High latency and over-fetching. | Use **Explicit Typed Selection**. |

---

## 🐘 PostgreSQL 18 Excellence

We prioritize native 2026 features for performance:
-   **UUIDv7**: Sequential, globally unique IDs for faster indexing.
-   **Virtual Columns**: Zero-cost calculated fields.
-   **Temporal Constraints**: Range-based uniqueness for scheduling.
-   **AIO Subsystem**: Faster reads for massive datasets.

---

## 💎 Prisma 7 Elite Patterns

-   **TypedSQL**: High-performance, type-safe raw SQL integration.
-   **TypeScript Engine**: Native WASM execution for Edge compatibility.
-   **Prisma Extensions**: Clean orchestration of soft deletes and audit logs.
-   **Relation Emulation**: Integrity in FK-less environments.

---

## 🔒 Zero-Trust Data Security (RLS)

Every table MUST be protected by Row-Level Security.
-   **Standard**: `auth.uid() = user_id` for personal data.
-   **Advanced**: `EXISTS` checks for team and project permissions.
-   **CLS**: Use PostgreSQL Views to hide sensitive columns from public APIs.

---

## 📖 Reference Library

Detailed deep-dives into Database Integrity:

- [**PostgreSQL 18 Integrity**](./references/postgres-18-integrity.md): Advanced constraints and UUIDv7.
- [**Prisma 7 Architecture**](./references/prisma-7-architecture.md): TypedSQL and Edge-first patterns.
- [**Migration Safety**](./references/migration-safety-protocols.md): Zero-downtime deployment rules.
- [**RLS & Security**](./references/rls-and-security.md): Building a zero-trust database.

---

*Updated: January 22, 2026 - 18:10*

Overview

This skill is the DB Enforcer: a guardian of database integrity and high-performance PostgreSQL + Prisma 7 architectures. It enforces type alignment, migration discipline, and zero-trust Row-Level Security to prevent type drift and protect AI-driven systems. The goal is resilient, audit-friendly schemas that scale with modern PostgreSQL 18 features.

How this skill works

DB Enforcer inspects TypeScript schema and Prisma models to verify parity with PostgreSQL constraints and indices. It enforces a four-step synchronization protocol: type-to-DB verification, migration-first generation, naming alignment, and integrity audits. It also recommends PostgreSQL 18 features (UUIDv7, virtual columns, temporal constraints) and Prisma 7 patterns (TypedSQL, relation emulation) to optimize performance and safety.

When to use it

  • When you must prevent runtime type drift between TS application types and persisted data.
  • During review or CI to validate generated migrations before they are applied.
  • When adopting PostgreSQL 18 features like UUIDv7 or virtual columns safely.
  • When building zero-trust databases that require Row-Level Security by default.
  • When migrating or scaling Prisma-backed apps and you need zero-downtime guarantees.

Best practices

  • Always generate migrations first (prisma migrate --create-only) and review SQL before applying.
  • Map naming: use snake_case in SQL and camelCase in TypeScript with explicit @map/@@map annotations.
  • Express enums and unions as SQL CHECK constraints so the DB enforces type invariants.
  • Protect every table with RLS; prefer auth.uid() checks and EXISTS-based permission clauses for teams/projects.
  • Use typed selection instead of select * and add missing indices for relation scalar fields.

Example use cases

  • CI pipeline validation: fail builds on schema drift or missing constraints before deploy.
  • Hardening a user table with RLS and UUIDv7 to prevent ID enumeration and enforce per-user access.
  • Adding virtual columns for computed values to offload runtime calculations to the DB.
  • Safe Prisma upgrades: validate TypedSQL raw queries and migration SQL before switching runtime.
  • Implementing temporal uniqueness for scheduling systems using PostgreSQL 18 range constraints.

FAQ

How does DB Enforcer prevent type drift?

It verifies TypeScript enums/unions against SQL CHECK constraints, requires migration-first workflows, and runs integrity audits to detect missing indices or mismatched mappings.

Do I have to enable RLS on every table?

Yes: the default stance is zero-trust. Enable RLS and implement clear policy functions (auth.uid(), EXISTS checks) to minimize accidental data exposure.