Database and Backend Logic Skill

Database and Backend Logic Skill

Purpose

Implement and modify database schemas, queries, and backend logic safely and consistently — keeping data access in the correct layer, validating inputs, and avoiding destructive or schema-breaking changes without approval.

When to Use

• Adding/modifying database tables, fields, relationships, or migrations.
• Writing or changing queries, ORM models, server actions, or API route handlers.
• Seeding or modifying data for development/testing.
• Validating backend input/output for an endpoint.

When Not to Use

• Pure front-end tasks with no backend/database involvement.
• Tasks that only consume an existing, unchanged API (see Connect Data and API Skill).

Required Inputs

• The existing schema/data model (tables/fields/relationships, ORM models, migration history).
• The existing backend framework/pattern (API routes, server actions, ORM such as Prisma/Drizzle, raw SQL, etc.) from Understand the Project Skill.
• The specific data/behavior requirement driving this task.

Assumptions

• An existing schema/data model is in place unless explicitly told this is a from-scratch setup.
• Schema changes and destructive data operations require explicit approval before execution.

Step-by-Step Workflow

1. Review the existing schema/data model: tables/collections, fields, types, relationships, indexes, and existing migrations.
2. Check for existing tables/fields/relations that already satisfy the need before adding new ones — don't invent parallel structures for the same concept.
3. For schema changes:
   • Define the change (new table/field/relation, type change, constraint change).
   • Identify migration impact: does this require a migration? Does it affect existing data (nullable vs. required, defaults, renames)?
   • Flag any change that could cause data loss (dropping/renaming columns, changing types incompatibly, removing tables) for explicit approval before applying.
4. For queries/ORM logic:
   • Follow the existing query/ORM pattern and naming conventions.
   • Keep queries efficient (avoid N+1 patterns where the existing codebase already avoids them; use existing includes/joins patterns).
   • Keep database logic in the backend/data layer (/db, server actions, API routes) — not in front-end components.
5. For API routes/server actions:
   • Validate inputs on the backend (types, required fields, ranges) regardless of front-end validation.
   • Return clear, consistent error responses matching the project's existing error shape/conventions.
   • Don't expose internal error details (stack traces, raw DB errors) to clients in production-style responses; log details server-side per project convention.
6. For seed data: use existing seed scripts/conventions if present; don't overwrite existing seed data wholesale without confirming it's safe to do so.