Skip to main content

CRUD API Factory

Reusable factory for building consistent, multi-tenant safe CRUD APIs with Zod validation, DI, QueryEngine listing, optional Custom Fields integration, hooks, and event emission.

Goals​

  • DRY: stop re-writing GET/POST/PUT/DELETE handlers.
  • Multi-tenant safety: enforce required tenantId and optional organizationId filtering and assignment.
  • Validation with Zod: schemas co-located with entities per module.
  • Extensible: lifecycle hooks before/after each operation.
  • Events: emit coherent CRUD events consumable by subscribers.
  • Custom Fields: seamlessly persist prefixed cf_ inputs via the entities module.

Usage​

Define a route.ts under src/modules/<module>/api/<path>/route.ts and use the factory.

Example (packages/example/.../api/todos/route.ts):

import { z } from 'zod'
import { makeCrudRoute } from '@open-mercato/shared/lib/crud/factory'
import { Todo } from '@open-mercato/example/modules/example/data/entities'
import { E } from '@open-mercato/example/datamodel/entities'
import { id, title, tenant_id, organization_id, is_done } from '@open-mercato/example/datamodel/entities/todo'

const querySchema = z.object({
  page: z.coerce.number().min(1).default(1),
  pageSize: z.coerce.number().min(1).max(100).default(50),
  sortField: z.string().optional().default('id'),
  sortDir: z.enum(['asc', 'desc']).optional().default('asc'),
  format: z.enum(['json','csv']).optional().default('json'),
})

const createSchema = z.object({ title: z.string().min(1), is_done: z.boolean().optional().default(false), cf_priority: z.number().int().min(1).max(5).optional() })
const updateSchema = z.object({ id: z.string().uuid(), title: z.string().min(1).optional(), is_done: z.boolean().optional() })

export const { metadata, GET, POST, PUT, DELETE } = makeCrudRoute({
  metadata: {
    GET: { requireAuth: true, requireRoles: ['admin'] },
    POST: { requireAuth: true, requireRoles: ['admin','superuser'] },
    PUT: { requireAuth: true, requireRoles: ['admin'] },
    DELETE: { requireAuth: true, requireRoles: ['admin','superuser'] },
  },
  orm: { entity: Todo, idField: 'id', orgField: 'organizationId', tenantField: 'tenantId', softDeleteField: 'deletedAt' },
  events: { module: 'example', entity: 'todo', persistent: true },
  indexer: { entityType: E.example.todo },
  list: {
    schema: querySchema,
    entityId: E.example.todo,
    fields: [id, title, tenant_id, organization_id, is_done, 'cf:priority'],
    sortFieldMap: { id, title, is_done, cf_priority: 'cf:priority' },
    buildFilters: () => ({}),
  },
  create: {
    schema: createSchema,
    mapToEntity: (input) => ({ title: input.title, isDone: !!(input as any).is_done }),
    customFields: { enabled: true, entityId: E.example.todo, pickPrefixed: true },
  },
  update: {
    schema: updateSchema,
    applyToEntity: (entity, input) => {
      if ((input as any).title !== undefined) (entity as any).title = (input as any).title
      if ((input as any).is_done !== undefined) (entity as any).isDone = !!(input as any).is_done
    },
    customFields: { enabled: true, entityId: E.example.todo, pickPrefixed: true },
  },
  del: { idFrom: 'query', softDelete: true },
})

This exports metadata and HTTP handlers, which the modules registry auto-discovers and serves under /api/<module>/<path>.

Options Overview​

  • orm: MikroORM entity config
    • entity: entity class
    • idField/orgField/tenantField/softDeleteField: customize property names
  • list: ListConfig
    • schema: zod schema for querystring
    • entityId + fields: enable QueryEngine listing (with custom fields)
    • sortFieldMap: map UI sort keys to datamodel fields (e.g. cf_priority -> 'cf:priority')
    • buildFilters(query, ctx): produce typed QueryEngine filters
    • customFieldSources: add extra tables whose records should contribute custom fields (useful when a base entity links to person/company profiles or other inheritance-style tables)
    • transformItem: post-process each item
    • export: configure export formats (CSV/JSON/XML/Markdown), filename, and optional column mappings
    • csv: legacy helper for specifying export columns/rows; still used to infer export data when export is omitted

Export what you view vs full export – Leave export undefined to stream the same shape the UI sees (i.e. whatever transformItem returns). Define export.columns when you need a canonical dump of every field (including custom fields) or want to reshape headers specifically for downstream systems.

  • create: CreateConfig
    • schema: zod
    • mapToEntity: input -> entity data (org/tenant injected automatically)
    • customFields: set { enabled: true, entityId, pickPrefixed: true } to map cf_* inputs to CF values
    • response: customize response payload (default returns { id })
  • update: UpdateConfig
    • schema: zod (must include id)
    • applyToEntity: mutate entity instance
    • customFields: same as create
    • response: customize response
  • del: DeleteConfig
    • idFrom: query (default) or body
    • softDelete: true (default) or false to hard-delete
  • events: CrudEventsConfig
    • module/entity: used for event naming
    • persistent: mark emitted events as persistent (for offline replay)
    • buildPayload(ctx): optional override for emitted payloads (ctx includes action, entity, and resolved identifiers)
  • indexer: CrudIndexerConfig
    • entityType: datamodel entity identifier (e.g. E.example.todo)
    • buildUpsertPayload(ctx) / buildDeletePayload(ctx): override payloads sent to the query indexer bus events
  • resolveIdentifiers(entity, action): override how { id, organizationId, tenantId } are derived before emitting events/indexer notifications
  • hooks: lifecycle hooks (beforeList, afterList, beforeCreate, afterCreate, beforeUpdate, afterUpdate, beforeDelete, afterDelete)

Response caching​

  • Opt-in per environment with ENABLE_CRUD_API_CACHE=true. When disabled (default), the factory behaves as before.
  • Only GET list handlers are cached. Keys incorporate the request path, tenant + selected organization scope, and a canonical serialization of the query string so each filter/sort/page combination is isolated.
  • Cached payloads are tagged with both the record ids that appear on the page and broader collection tags (crud/<module.entity>/tenant/<id>/org/<id>/collection) so follow-up mutations can evict the exact variations that might be stale.
  • Any mutation that flows through the CRUD factory or the command bus (including CLI calls, admin APIs, and the audit-log undo/redo flows) invalidates the relevant tags automatically. Command metadata (resourceKind, resourceId, tenant/org ids, plus context.cacheAliases) carries the canonical <module>.<entity> tag, so undo/redo hit the exact same cache keys as the original write. If you override command metadata, merge the provided context before executing to keep cache eviction intact.
  • When caching is active, list responses include an x-om-cache header set to either hit or miss. Backoffice tables read this header to surface the green/amber footer indicator, and you can log or trace the value in custom clients to understand whether a page came from cache.
  • When QUERY_ENGINE_DEBUG_SQL=true, extra debug logs surface cache hits/misses with timings, cache stores with tag lists, and invalidation events with tag counts. This mirrors the hybrid query engine’s SQL debug mode for easier troubleshooting.

Cache tags are now canonicalized to a single <module>.<entity> identifier. Flush any shared cache stores when upgrading from an older build so legacy alias-based entries don’t linger.

Event Naming​

The factory emits Medusa-style module events on CRUD:

  • <module>.<entity>.created
  • <module>.<entity>.updated
  • <module>.<entity>.deleted

Example: example.todo.created

Enable persistence via events.persistent: true so events are persisted (local/Redis) and available for offline processing.

See also: Events and subscribers for strategy, persistence, and CLI.

When an indexer config is provided, the factory also emits query_index.upsert_one (for creates/updates) and query_index.delete_one (for deletes) with the resolved identifiers so search/index subscribers stay in sync automatically.

Multi-tenant Safety​

  • Automatically injects organizationId and tenantId on create.
  • Filters update/delete by id + organizationId + tenantId.
  • GET list via QueryEngine requires tenantId; organizationId is optional. Pass withDeleted to include soft-deleted rows.

Custom Fields​

When customFields.enabled is set and entityId is provided, the factory picks cf_* inputs by default (e.g., cf_priority -> priority) and persists them via the Custom Fields module.

If you need full control, supply customFields.map(body) => Record<string, any>.

Multiple sources for custom fields​

Some entities span several tables—for example a customer row joined to either a person profile or a company profile. When those linked tables carry their own custom fields, wire them in with list.customFieldSources. Each source defines the entity id whose custom fields should be hydrated, the table/alias to join, and how to reach the base entity:

const { GET } = makeCrudRoute({
  list: {
    entityId: E.customers.customer_entity,
    fields: ['id', 'display_name', 'cf:vip', 'cf:birthday'],
    customFieldSources: [
      {
        entityId: E.customers.customer_person_profile,
        table: 'customer_people',
        alias: 'person_profile',
        recordIdColumn: 'id',
        join: { fromField: 'id', toField: 'entity_id' },
      },
      {
        entityId: E.customers.customer_company_profile,
        table: 'customer_companies',
        alias: 'company_profile',
        recordIdColumn: 'id',
        join: { fromField: 'id', toField: 'entity_id' },
      },
    ],
  },
})

The Query Engine auto-discovers definitions for all provided entity ids, picks the best match for each key, and joins the corresponding custom_field_values using the supplied alias. Filters such as cf_birthday=... continue to work because the engine aliases joined values back to cf:<key> regardless of the source table.

Reusable helpers​

Use helpers from @open-mercato/shared/lib/crud/custom-fields to keep routes DRY and dynamic:

  • buildCustomFieldSelectorsForEntity(entityId, fieldSets) → { keys, selectors, outputKeys }
    • selectors: ['cf:priority', 'cf:severity', ...] for QueryEngine fields
    • outputKeys: ['cf_priority', 'cf_severity', ...] for CSV headers or typed output
  • extractCustomFieldsFromItem(item, keys) → maps projections cf:<k>/cf_<k> into { cf_<k>: value }
  • buildCustomFieldFiltersFromQuery({ entityId | entityIds, query, em, orgId, tenantId }) → builds a Record<string, WhereValue> for cf:<k> and cf:<k> $in based on query keys cf_<k> and cf_<k>In. Values are coerced to the correct type from CustomFieldDef.kind.

Example wiring:

import fieldSets from '.../data/fields'
import { buildCustomFieldSelectorsForEntity, extractCustomFieldsFromItem, buildCustomFieldFiltersFromQuery } from '@open-mercato/shared/lib/crud/custom-fields'

const cf = buildCustomFieldSelectorsForEntity(E.example.todo, fieldSets)

makeCrudRoute({
  list: {
    fields: [id, title, ...cf.selectors],
    sortFieldMap: { id, title, ...Object.fromEntries(cf.keys.map(k => [`cf_${k}`, `cf:${k}`])) },
    buildFilters: async (q, ctx) => ({
      ...(await buildCustomFieldFiltersFromQuery({ entityId: E.example.todo, query: q, em: ctx.container.resolve('em'), orgId: ctx.auth.orgId, tenantId: ctx.auth.tenantId }))
    }),
    transformItem: (item) => ({ id: item.id, title: item.title, ...extractCustomFieldsFromItem(item as any, cf.keys) }),
    csv: {
      headers: ['id','title', ...cf.outputKeys],
      row: (t) => [t.id, t.title, ...cf.outputKeys.map(k => String((t as any)[k] ?? ''))],
    }
  }
})

Hooks​

Hooks receive the DI container and auth context so you can resolve services and inject custom logic.

Examples:

hooks: {
  beforeCreate: async (input, { container, auth }) => {
    const svc = container.resolve('someService')
    await svc.enforceBusinessRule(input, auth)
  },
  afterUpdate: async (entity) => { /* ... */ },
}