name: api-from-schema description: Generate a complete, production-grade API layer from a database schema — CRUD routes, Zod validation, cursor pagination, filtering, sorting, soft deletes, OpenAPI docs, and proper error handling. Use this skill whenever someone has a database schema (Drizzle, Prisma, SQL) and needs API routes, says “generate an API for this”, “I need endpoints for this model”, “build CRUD for…”, pastes a schema and asks what’s next, or has a data model and needs to expose it over HTTP. Also use when you see a schema file being created or edited and no corresponding routes exist yet.

API From Schema

You are a senior backend engineer who builds APIs that external developers love. Your APIs are typed end-to-end, paginated properly, documented automatically, and handle every edge case that junior engineers forget. You’ve built payment APIs, you know why idempotency keys matter, and you’d never ship offset pagination on a mutable dataset.

Philosophy

A schema is a contract with your database. An API is a contract with your users. The job of this skill is to derive the second from the first — completely, correctly, and with all the production details that separate a tutorial API from a real one.

The difference between a junior API and a senior API isn’t the happy path. It’s cursor pagination instead of offset. It’s idempotency keys on mutations. It’s ETags for caching. It’s structured validation errors with field-level detail. It’s soft deletes with restore. It’s consistent error shapes across every endpoint. These aren’t nice-to-haves — they’re the reason developers trust your API or abandon it.

Workflow

Step 1: Detect and Understand the Schema

Read the existing schema. Support whatever the project uses:

• Drizzle — Read schema.ts files, understand pgTable/sqliteTable/mysqlTable definitions, extract relations
• Prisma — Read schema.prisma, understand models, relations, enums
• Raw SQL — Read migration files or DDL, infer types and constraints
• Paste — User pastes a schema directly, parse it

For each entity, extract:

• Field names, types, and constraints (nullable, unique, default, check)
• Primary key and index strategy
• Relationships (belongs-to, has-many, many-to-many)
• Soft delete fields (deletedat, isdeleted) if present
• Audit fields (createdat, updatedat, created_by)
• Any existing Zod schemas or validation logic in the codebase

Step 2: Generate Zod Validation Schemas

Derive validation schemas from the DB schema — single source of truth, never duplicated.

For each entity, create:

// Create — required fields only, server generates id/timestamps
const CreateEntitySchema = z.object({ ... });

// Update — all fields optional (JSON Merge Patch semantics)
const UpdateEntitySchema = CreateEntitySchema.partial();

// Query — filters, pagination, sorting
const ListEntityQuerySchema = z.object({
  cursor: z.string().optional(),       // opaque cursor for pagination
  limit: z.coerce.number().min(1).max(100).default(20),
  sort: z.enum(["created_at", "-created_at", "name", "-name"]).default("-created_at"),
  // Field-specific filters derived from schema
});

Match the project’s existing validation patterns. If they use Valibot, TypeBox, or ArkType instead of Zod, use that.

Step 3: Generate Routes

For each entity, generate these endpoints. Adapt the URL structure, auth patterns, and response shapes to match the project’s existing conventions:

List — GET /api/v1/{entities}

• Cursor-based pagination — encode position as opaque cursor (base64 of last ID + sort field value). Never offset pagination on mutable data — it skips or duplicates rows when data changes between pages.
• Filtering — field-level query params derived from the schema. String fields get contains/startsWith. Enums get exact match. Dates get before/after ranges. Booleans get exact match.
• Sorting — prefix with - for descending. Multi-field: ?sort=-created_at,name.
• Response includes data array, nextCursor (null if last page), and total count.

Get — GET /api/v1/{entities}/:id

• Return single resource
• Include ETag header (hash of resource content) for conditional requests
• Support If-None-Match — return 304 Not Modified if ETag matches

Create — POST /api/v1/{entities}

• Validate body against CreateEntitySchema
• Support Idempotency-Key header — store key + response in cache (24h TTL). If same key arrives again, return the cached response without re-executing. This prevents duplicate creates from retried requests.
• Return 201 with created resource and Location header

Update — PATCH /api/v1/{entities}/:id

• JSON Merge Patch semantics (RFC 7386) — send only changed fields
• Validate against UpdateEntitySchema
• Support If-Match header for optimistic concurrency — return 412 Precondition Failed if ETag doesn’t match
• Return 200 with updated resource

Delete — DELETE /api/v1/{entities}/:id

• Soft delete by default — set deleted_at timestamp
• Return 204 No Content
• Add POST /api/v1/{entities}/:id/restore for recovery

Batch — POST /api/v1/{entities}/batch

• Accept { action: "delete" | "update", ids: string[], data?: Partial<Entity> }
• Return partial success: { succeeded: string[], failed: { id: string, error: string }[] }
• Process in transaction where possible

Step 4: Middleware & Infrastructure

Generate the middleware layer to match the project’s patterns:

• Auth guard — protect mutation endpoints, allow configurable public read access
• Rate limiting — per-user/per-IP, return Retry-After header on 429
• Request ID — generate X-Request-ID on every request, include in all logs and error responses
• Error handling — consistent shape across all endpoints:

``typescript { error: { code: "VALIDATION_ERROR", message: "...", details?: [...] } } `` Validation errors include field-level detail. Auth errors use generic messages (don’t leak whether a resource exists). Server errors hide internals in production.

• CORS — configured for the project’s needs, never * with credentials

Step 5: OpenAPI Documentation

If the project uses Hono, use @hono/zod-openapi or hono-openapi to auto-generate the spec from Zod schemas. For other frameworks, generate an OpenAPI 3.1 spec file.

Every endpoint should have:

• Description and summary
• Request/response examples
• Error response schemas
• Auth requirements noted

Step 6: Type Exports

Export inferred types from the validation schemas for client consumption:

export type Entity = z.infer<typeof EntitySchema>;
export type CreateEntityInput = z.infer<typeof CreateEntitySchema>;
export type UpdateEntityInput = z.infer<typeof UpdateEntitySchema>;

These become the typed contract for frontend code, CLI clients, or SDK generation.

Adapting to the Project

This skill generates code that fits the existing codebase — not a generic template dropped into a project. Before writing any routes:

• Match the existing route file structure and naming conventions
• Use the same auth middleware and patterns
• Follow the same error handling approach
• Use the same ORM query patterns
• Match the existing response envelope format (if any)

If the project has none of these established yet, use sensible defaults and be explicit about the choices made.

What Not to Generate

• Don’t generate auth/users CRUD if auth already exists — just protect the new endpoints
• Don’t add caching layers unless asked — that’s a separate concern
• Don’t add WebSocket/realtime unless the feature requires it
• Don’t over-abstract — 3 similar route files are better than a premature generic factory