How to Generate CRUD APIs Safely

Generated CRUD endpoints are easy to demonstrate and surprisingly hard to operate safely. The reliable approach is not to generate four handlers from a table name; it is to generate a constrained contract from a schema, then apply the same validation, tenancy, authorization, and observability rules to every request. This checklist shows how to do that for a schema-driven REST API.

1. Make the schema the contract

Start with one authoritative data-source definition. A source should identify its project, slug, description, and ordered fields. Each field needs a stable key, display name, type, required and nullable rules, uniqueness, default value, and any type-specific options. In Dashier, field definitions drive generated forms, tables, import/export, and REST validation; duplicating those rules inside individual routes guarantees drift.

Treat the source slug as an API identifier, not merely a label. Make it URL-safe, unique within its project, and immutable whenever possible. If a slug must change, provide an explicit migration or alias period; silently changing /api/v1/members breaks clients that may have no way to discover the new URL.

A generated route should resolve the organization, project, source, and fields from trusted server state. Never accept a client-provided organization ID as proof of tenancy. The request may select a source by slug, but the server must verify that the source belongs to the resolved project and that every record operation remains inside the resolved organization.

2. Validate at the boundary and again at the write

Validation has two jobs: produce a useful client error and prevent invalid state. Parse JSON with a bounded body size, reject unknown or disallowed fields according to the contract, and validate each value against the field type. Email, URL, date, datetime, decimal, enum, array, JSON, image, file, and relationship fields each need deliberately different checks. A string that is valid for a text field is not automatically valid as a URL or a referenced record ID.

Required does not always mean non-null. Keep required, nullable, and default separate, and define their order: apply an explicit default only when a value is absent, reject null when the field is non-nullable, then enforce requiredness. On update, distinguish a missing property (leave it unchanged) from a property set to null (clear it only when allowed). This matters particularly for partial updates, even if the public operation is named PUT.

Validate twice: once in the route handler for a clear response, and once in the persistence path so another caller cannot bypass the invariant. Use a safe parser rather than trusting a TypeScript cast. A compact TypeScript pattern looks like this:

import { z } from "zod";

type FieldDef = { key: string; type: string; required: boolean; nullable: boolean };

function valueSchema(field: FieldDef): z.ZodTypeAny {
  switch (field.type) {
    case "number": return z.number().finite();
    case "boolean": return z.boolean();
    case "email": return z.string().email();
    case "url": return z.string().url();
    case "array": return z.array(z.unknown());
    case "json": return z.unknown();
    default: return z.string();
  }
}

export function inputSchema(fields: FieldDef[], partial = false) {
  const shape: Record<string, z.ZodTypeAny> = {};
  for (const field of fields) {
    let value = valueSchema(field);
    if (field.nullable) value = value.nullable();
    if (!field.required || partial) value = value.optional();
    shape[field.key] = value;
  }
  return z.object(shape).strict();
}

The example is intentionally incomplete: production code should cover every supported field type, enforce maximum lengths and array sizes, validate relationship targets, and apply defaults. The important property is that the schema is assembled from persisted field definitions and that .strict() prevents an accidental mass-assignment surface.

3. Keep identifiers stable and opaque

A record identifier is part of the public contract. Use an immutable, non-sequential identifier such as a UUID, and never derive it from a mutable field like an email or name. Do not expose database row numbers that reveal creation order or invite enumeration. Resolve an ID together with its source and organization in one authorization-aware query. A safe lookup is conceptually WHERE id = ? AND data_source_id = ? AND organization_id = ?; a lookup by ID followed by a separate tenant check creates an avoidable race and a dangerous opportunity for a missed check. If the ID is well-formed but not visible to the caller, return the same not-found response as an unknown ID rather than confirming another tenant’s record exists.

Do not permit clients to set server-owned metadata such as created_by, timestamps, source ID, organization ID, or audit actor. Generate those values from the authenticated context. ## 4. Design list endpoints for bounded work

A list endpoint is the highest-volume generated operation, so make its work predictable. Require a bounded page size, apply a server maximum, and return a cursor or an explicit offset contract. Cursor pagination is usually safer for changing datasets: encode the last sort key and stable ID, then query values after that tuple. If using offsets, document that inserts and deletes can shift later pages.

Always use a deterministic order. A request such as sort=created_at should become ORDER BY created_at, id, with a declared direction and a stable tie-breaker. Never interpolate a query-string column directly into SQL; map public field keys to an allowlisted internal expression. The same allowlist should govern filters and searchable fields.

Keep filtering expressive but finite. Support typed operators such as eq, neq, contains, gte, and lte only where the field type permits them. Bound the number of filter clauses, search length, and page size. Reject an unknown field or operator with a structured 400 response rather than ignoring it, because silent filtering changes are difficult to debug and can produce misleading exports.

GET /api/v1/members?limit=25&after=eyJjcmVhdGVkIjoi...&status=active&sort=-created_at
Authorization: Bearer dk_live_example

{
  "data": [
    { "id": "8c7f...", "email": "person@example.test", "status": "active" }
  ],
  "page": { "limit": 25, "next_cursor": "eyJjcmVhdGVkIjoi..." }
}

Do not return every internal column by default. A generated API should have an explicit projection policy, and file or image fields should return their supported storage URLs or metadata rather than leaking storage internals. Relationships need a bounded expansion rule; otherwise one list request can become an unbounded graph traversal.

5. Authenticate, then authorize the operation

Authentication answers “who is calling?” Authorization answers “may this caller perform this method on this source and record?” Keep them separate and run both on every request. Dashier API keys can arrive in x-api-key or as Authorization: Bearer; a session can also authenticate a request. Keys are resolved by hash, can be revoked, and carry scopes, so never log or compare raw key material after the boundary.

A useful endpoint policy has three modes: Public, Authenticated, and Role. Public means no credential is required, not that the data is exempt from tenant scoping or field restrictions. Authenticated requires a session or a key with the operation’s scope. Role mode requires an allowed role for a session, while a key must carry the appropriate elevated scope defined by the policy. In the current implementation, GET needs read, writes need write, and role-protected key access requires admin; owners or explicitly allowed role IDs can satisfy a role policy through a session.

Apply source permissions after endpoint mode. A project or organization member may still lack access to a particular source. For ordinary CRUD permissions, use stable keys such as {slug}.read, {slug}.write, and {slug}.delete; do not infer permission from a client-supplied role name. The server must load membership and role data, enforce project membership, and keep the organization predicate on every read and write. Row-level security remains a second boundary, not a replacement for application checks.

Be precise about method permissions. A generic “write” check should not accidentally authorize deletion. Resolve the operation (read, create, update, or delete) first, then check the matching scope and role permission. Public creation is especially risky: restrict fields, validate aggressively, rate-limit it, and consider whether the endpoint should be enabled at all.

6. Use stable errors and safe status codes

Clients need errors they can handle without parsing prose. Return a consistent envelope with a machine-readable code, human-readable message, and optional field details:

{
  "error": {
    "code": "VALIDATION_FAILED",
    "message": "One or more fields are invalid.",
    "fields": { "email": "Must be a valid email address." }
  }
}

Use a small, documented status vocabulary: 400 for malformed request or unsupported query, 401 for missing or invalid authentication, 403 for a valid caller without permission, 404 for an unknown or invisible resource, 409 for uniqueness or state conflicts, 413 for an oversized body, 422 when syntactic input is valid but semantic validation fails, and 429 for rate limiting. Avoid returning SQL errors, stack traces, raw storage paths, or whether a hidden tenant’s identifier exists.

For create and update, translate uniqueness violations into 409 with the field or constraint that the client can correct. Use a request or correlation ID in logs and, when available, in the response header. ## 7. Rate-limit by the real identity

Rate limits are part of the endpoint contract, not an afterthought. Apply them per source and method, with a key identity when available and an IP fallback for unauthenticated traffic. The current policy implementation uses a configured per-minute limit, defaults to 120 when unset, and returns 429 with X-RateLimit-Limit, X-RateLimit-Remaining, and Retry-After headers when the limit is exceeded.

Choose limits according to cost: a filtered list, a large export, and a single record lookup should not necessarily consume the same budget. Bound page size and payload size even for authenticated keys. Rate limiting reduces abuse, but it does not fix an unbounded query or an authorization bug; enforce query and tenant constraints independently.

8. Test the generated contract, not just the happy path

Generate tests from the same manifest that generates the route, then add cross-cutting security tests. Every method should be exercised with valid and invalid field types, missing required values, nulls, unknown keys, duplicate unique values, malformed IDs, oversized bodies, and empty results. List tests should cover deterministic ordering, cursor expiration or invalidation, each allowed operator, rejected operators, maximum limits, and cross-tenant records.

Authorization tests should matrix endpoint mode, credential type, scope, role, membership, source allowlist, revoked key, and disabled endpoint. A key with read must not create, update, or delete; an authenticated user without the source permission must not read it; a role-protected endpoint must reject a merely authenticated session. Assert both status code and error code so accidental regressions are visible.

9. Evolve the API deliberately

A schema-driven API changes when users add fields, tighten validation, rename labels, or alter relationships. Separate additive changes from breaking changes. Adding an optional response field is usually compatible; changing a field’s type, making an optional field required, removing a filter, changing identifier semantics, or renaming a slug is not.

Keep public keys stable even when display names change. Version behavior that truly must break, and document the affected source and migration. If a field is deprecated, continue accepting it for a stated period when feasible, stop advertising it, and emit a response warning or changelog entry rather than silently changing its meaning. Store enough schema revision information to explain how an old record was interpreted, especially for imports and relationship values.

Finally, make generated output observable and reversible. Record schema and policy changes in audit logs, expose a contract or example request for developers, and roll out changes in stages: validate the new schema, test generated routes against existing records, enable the policy, and monitor errors and rate-limit responses. Safe generation is not about producing less code; it is about making every generated endpoint obey the same explicit contract, tenant boundary, authorization decision, and evolution policy.