Scaffold CRUD API

You are a backend engineer scaffolding a production-ready CRUD API.

Entity Name

{{ENTITY_NAME}}

Fields

{{FIELDS}}

Framework

{{FRAMEWORK}}

Database

{{DATABASE}}

CRUD API Generation

Generate the following for the "{{ENTITY_NAME}}" entity:

1. Database Schema

• CREATE TABLE statement with appropriate types and constraints
• Indexes for common query patterns
• Timestamps (created_at, updated_at) with auto-update triggers
• Soft delete support (deleted_at column)

2. Model / Type Definition

• TypeScript interface or equivalent type definition
• Create DTO (fields allowed during creation)
• Update DTO (partial, fields allowed during update)
• Response DTO (what the API returns, excluding internal fields)
• Query params type (filters, sorting, pagination)

3. Validation

• Input validation for create and update operations
• Type coercion and sanitization
• Custom validation rules based on field types
• Meaningful error messages

4. API Endpoints

Generate these endpoints:

Method	Path	Description
POST	/{{ENTITY_NAME}}s	Create a new {{ENTITY_NAME}}
GET	/{{ENTITY_NAME}}s	List with pagination, filtering, sorting
GET	/{{ENTITY_NAME}}s/:id	Get by ID
PATCH	/{{ENTITY_NAME}}s/:id	Partial update
DELETE	/{{ENTITY_NAME}}s/:id	Soft delete

Each endpoint should include:

• Request validation middleware
• Authentication middleware
• Authorization check
• Error handling with proper HTTP status codes
• Response formatting

5. Pagination

• Cursor-based pagination (preferred) or offset-based
• Configurable page size with max limit
• Total count in response metadata
• Next/previous page links

6. Filtering & Sorting

• Filter by each field where appropriate
• Support operators: eq, neq, gt, gte, lt, lte, in, contains
• Multi-field sorting with direction (asc/desc)
• Search across text fields

7. Error Handling

• 400: Validation errors with field-level details
• 401: Unauthenticated
• 403: Unauthorized
• 404: Resource not found
• 409: Conflict (duplicate unique fields)
• 500: Internal server error (logged, not exposed)

Output Format

Provide all files needed:

1. Database migration file
2. Model/type definitions
3. Validation schemas
4. Route handlers/controllers
5. Service layer (business logic)
6. Repository layer (database queries)
7. Route registration
8. Example API requests (curl)