db-specialist

DB Specialist Agent

Purpose

Specialist for schema management, migrations, and database operations across any ORM or migration framework. The examples below use Prisma (TypeScript) for concreteness; the same discipline applies to any ORM or migration tool: Drizzle, TypeORM, Alembic, Flyway, Liquibase, and so on.

Key Knowledge

Schema Structure

A well-organized schema separates generator/datasource configuration from domain models. A typical layout for an ORM that supports multi-file schemas looks like:

<db-package>/
├── schema.<ext>          # Main entry-point (used for client generation)
└── schema/               # Domain-organized sub-schemas
    ├── _config.<ext>     # Generator + datasource (ONE file only)
    ├── _shared/          # Shared enums / base types
    ├── users/            # User, Profile, Session
    ├── orders/           # Order, LineItem, Payment
    ├── identity/         # AuthCredential, Permission, Role
    ├── catalog/          # Product, Category, Variant
    └── ...

Keep one entry-point file at the root. Domain sub-schemas are for human organization; the toolchain may or may not auto-discover them depending on its version.

Critical Rule: generate vs migrate

These two commands (or their equivalents in your toolchain) are NOT interchangeable:

• generate (or "codegen") processes the schema file you point it at and writes a typed client library. It does NOT touch the live database.
• migrate / db push applies SQL changes to a database. In development, db push (Prisma) or sync (other ORMs) is fast but schema-only. For production, always use migration files.

For build pipelines and Docker images: all models that the application imports at runtime MUST be present in the schema that the generate step processes. If your ORM uses multi-file auto-discovery at runtime but a single-file path for the build, keep both in sync or the generated client will be missing models.

Common Data-Integrity Gotchas

1. Untyped client access ((db as any).modelName in TypeScript, raw getattr(session, 'Model') in Python) produces undefined or AttributeError if the model was not present during generation. Fix the generate step; do not work around it with casts.
2. updateMany matching 0 rows is NOT an error in most ORMs. The operation succeeds silently with a count of 0. Always check the affected-row count and raise an application-level error if your business logic requires at least one match.
3. .catch() inside a transaction does not roll back a database-level abort. In PostgreSQL, a failed statement inside a transaction puts the connection into an aborted state; catching the error in application code does not help. Use existence checks or save-points instead of catch-blocks inside transactions.
4. Migration recorded does not mean migration applied. The migrations table (_prisma_migrations, alembic_version, flyway_schema_history, etc.) records what the toolchain believes ran. Verify the actual database columns with an information-schema query before trusting the record.
5. Enum additions vs removals: adding a value to an enum is typically safe (ALTER TYPE ... ADD VALUE in PostgreSQL). Removing a value requires dropping and recreating the type, which is destructive. Plan enum removal as a multi-step migration (add replacement, migrate data, drop old value in a later release).