@sdflc/backend-helpers

A comprehensive backend framework for Node.js applications providing database access, authentication, caching, IP-based rate limiting, GraphQL resolver helpers, REST controllers, CLI utilities, and migration tools.

Table of contents

• Installation
• Requirements
• Architecture overview
• Quick start
• Configuration
  • buildConfig
  • buildKnexConfig
• Application setup
  • Logger
  • Redis client
  • Knex handle
  • Context
  • Express app
• Core concepts
  • ApiContext
  • BaseCore
  • BaseGateway
  • BaseController
  • BaseRestApiCrudController
  • BaseExternalApiGateway
• IP rate limiting
  • How it works
  • Setup
  • Global tier
  • Core-level rules
  • Action-level rules
  • Per-call override
  • Disabling rate limiting
  • Resetting a counter after success
  • Custom limiter
• Database
  • Database class
  • DataCache and custom loaders
  • RedisDataLoader
• Authentication
  • JWT token helpers
• GraphQL helpers
  • buildDefaultResolvers
• CLI utilities
• Migration and seeding utilities
  • DbMigratorAndSeeder
  • dbHelpers
• Field helpers
• Validation helpers
  • commonValidators
  • UUID helpers
  • UUID rule factories
• Utility functions
  • downloadFile
  • passwordHelpers
• Constants
• Interfaces reference

Installation

npm install @sdflc/backend-helpers

Requirements

• Node.js >= 22
• TypeScript >= 5.7
• PostgreSQL or MSSQL (via Knex)

Architecture overview

The library implements a simplified variation of Clean Architecture — a layered approach where each layer has a single, well-defined responsibility and dependencies only flow inward.

Application layer   →   GraphQL resolvers / REST controllers / CLI
                        Accept requests from the outside world and optionally
                        transform inputs before passing them to cores.

Business layer      →   Core classes (BaseCore subclasses)
                        Manage transactions, handle request-level authentication,
                        validation, IP rate limiting, enforce business rules,
                        and normalise responses into a consistent OpResult shape.

Data layer          →   Gateway classes (BaseGateway subclasses)
                        Perform CRUD operations on database tables, run complex
                        queries with joins and filters, communicate with
                        external APIs and third-party services.

Each inbound request — whether from an HTTP client, a GraphQL operation, or a CLI command — creates a fresh ApiContext that carries the database connection, all gateway instances, all core instances, and request-scoped data (userId, spaceId, headers, etc.).

Key invariants:

• Gateways and cores are instantiated per-request and must never be reused across requests
• The data layer (gateways) has no knowledge of HTTP, GraphQL, or authentication — it only handles data
• The business layer (cores) has no knowledge of transport details — it receives plain args and returns OpResults
• The application layer (controllers/resolvers/CLI) has no direct database access — it goes through cores

This separation means the same core logic is accessible identically from a GraphQL resolver, a REST endpoint, and a CLI command — as demonstrated throughout this documentation.

Quick start

The typical project structure:

src/
  config.ts               ← environment config via buildConfig()
  knexConfig.ts           ← Knex config via buildKnexConfig()
  knexHandle.ts           ← Knex instance for migrations/seeds/CLI
  logger.ts               ← shared logger instance
  redisClient.ts          ← optional Redis client
  context.ts              ← per-request ApiContext factory
  app.ts                  ← Express + Apollo setup
  index.ts                ← server entry point
  core/
    AppCore.ts            ← project-level base core
    NoteCore.ts           ← resource-specific core
  gateways/
    NoteGw.ts             ← resource-specific gateway
  app/
    graphql/
      types/              ← GraphQL type definitions
      resolvers/          ← GraphQL resolvers
    restapi/              ← REST controllers
    cli/
      index.ts            ← CLI entry point

Configuration

buildConfig

Loads environment variables from a .env file and returns a flat camelCase config object. Boolean strings ('true'/'1' → true, 'false'/'0' → false) are coerced automatically.

// src/config.ts
import { buildConfig } from '@sdflc/backend-helpers';

const config = buildConfig(__dirname, {
  envFile: '../.env',
  envFileExample: '../.env.example',
  defaultNodeEnv: 'development',
  allowEmptyValues: true,
});

export default {
  ...config,
  // Post-process any values that need further transformation:
  currencies: (config.allowedCurrencies || '').split(','),
};

# .env.example — documents all required variables
NODE_ENV=development
DB_TYPE=pg
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_SCHEMA=public
DB_USER=postgres
DB_PASSWORD=secret
DB_POOL_MIN=0
DB_POOL_MAX=10
JWT_SECRET=change-me
LOG_LEVEL=debug
REDIS_HOST=
REDIS_PORT=6379
REDIS_PASSWORD=
REDIS_SSL=false
CACHE_TTL=300
COOKIE_NAME=token
FINGERPRINT_COOKIE_NAME=fp
INTERSERVICE_API_KEY=

buildKnexConfig

Builds a Knex configuration object for the current environment. The library handles port coercion, MSSQL connection options, and PostgreSQL searchPath automatically — no manual post-processing needed.

// src/knexConfig.ts
import { buildKnexConfig } from '@sdflc/backend-helpers';
import config from './config';

const knexConfig = buildKnexConfig(__dirname, {
  ...config,
  migrationsFolder: 'database/migrations',
  seedsFolder: 'database/seeds',
  migrationsTemplateFileName: 'migrations-template.ts',
  useSchemaForMigrationsTable: true,
});

export default knexConfig;

Built-in behaviours:

• All query results are post-processed to camelCase
• All identifiers are converted to snake_case
• Database port is coerced to Number
• connectionTimeout: 30000 is set by default
• MSSQL connection.options added automatically when dbType === 'mssql'
• PostgreSQL searchPath set automatically when dbType === 'pg' and dbSchema is provided
• debug is forced to false in production

Available options

| Option | Type | Description | | --------------------------------------- | --------- | ------------------------------------------------------- | | dbType | string | 'pg' or 'mssql' (default: 'pg') | | dbHost/Port/Name/Schema/User/Password | string | Connection parameters | | dbPoolMin/Max | number | Pool size (defaults: 0/50) | | dbPool | object | Full tarn pool config (overrides min/max) | | migrationsFolder | string | Path to migrations directory | | seedsFolder | string | Seeds directory (all environments) | | seedsFolderDev/Prod | string | Environment-specific seeds directories | | migrationsTable | string | Migrations tracking table name | | migrationsTemplateFileName | string | Template for knex migrate:make | | useSchemaForMigrationsTable | boolean | Scope migrations table to schema | | wrapIdentifierOverrideMap | object | Override snake_case conversion for specific identifiers |

Application setup

Logger

// src/logger.ts
import { Logger } from '@sdflc/utils';
import config from './config';

export const logger = new Logger({ level: config.logLevel });

Redis client

// src/redisClient.ts
import { createRedisClient } from '@sdflc/backend-helpers';
import config from './config';
import { logger } from './logger';

// Returns null if redisHost is not configured — safe to pass to DataCache
export const redisClient = config.redisHost
  ? createRedisClient({
      redisHost: config.redisHost,
      redisPort: config.redisPort,
      redisPassword: config.redisPassword,
      redisSsl: config.redisSsl,
      connectionTimeout: 5000,
      pingInterval: 5000,
      onConnecting: () => logger.log('Redis connecting'),
      onConnected: () => logger.log('Redis connected'),
      onDisconnected: () => logger.log('Redis disconnected'),
      onReconnecting: () => logger.log('Redis reconnecting'),
      onError: (err) => logger.error('Redis error:', err),
    })
  : null;

// Call redisClient.connect() at startup — see app.ts and cli/index.ts

The client is created but not connected — call await redisClient.connect() during app or CLI startup. This allows the client to be created early and connected at a controlled point.

The same redisClient instance is used for both DataLoader caching (via gateways) and IP rate limiting (via createContextDefault). Pass it once — the library routes it to both uses automatically.

Knex handle

Used directly for migrations and seeds (separate from the per-request Database instance).

// src/knexHandle.ts
import knex from 'knex';
import knexConfig from './knexConfig';

export default knex({
  ...knexConfig,
  pool: {
    min: 2,
    max: 20,
    acquireTimeoutMillis: 30000,
    createTimeoutMillis: 30000,
    destroyTimeoutMillis: 5000,
    idleTimeoutMillis: 30000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 200,
  },
  acquireConnectionTimeout: 30000,
});

Context

The context factory creates a fresh ApiContext for every request. All gateways and cores are instantiated here. The same factory is used by GraphQL, REST, and CLI.

// src/context.ts
import { createContextDefault } from '@sdflc/backend-helpers';
import { STATUSES } from '@sdflc/utils';
import dayjs from 'dayjs';

import config from './config';
import * as cores from './core';
import * as gateways from './gateways';
import knexConfig from './knexConfig';
import { redisClient } from './redisClient';
import { logger } from './logger';

export const createContext = async (args: any) => {
  return createContextDefault({
    req: args.req,
    dbSchema: config.dbSchema,
    cores,
    gateways,
    knexConfig,
    isProduction: config.isProduction,
    logger,
    doAuth: true,
    interserviceApiKey: config.interserviceApiKey,
    jwtSecret: config.jwtSecret,
    verifyTokenArgs: {
      ignoreExpiration: false,
      ignoreNotBefore: false,
    },
    // Pass redisClient to enable IP rate limiting automatically.
    // An IpRateLimiter instance is constructed once here and shared
    // across all cores for the lifetime of this request.
    redisClient,
    // Optional: cap every IP at 300 requests per minute across all cores.
    globalRateLimit: { limit: 300, windowSec: 60 },
    // Provide extra props to every gateway constructor
    getGatewayContext: () => ({
      redisClient,
      cacheTtl: config.cacheTtl,
      activeStatuses: [STATUSES.ACTIVE],
    }),
    // Extend context with app-specific fields after construction
    extendContext: ({ context }: any) => {
      context.nowUtc = dayjs().utc().toDate();
      context.space = {
        cookieName: config.cookieName,
        fingerprintCookieName: config.fingerprintCookieName,
      };
    },
  });
};

createContextDefault options

All options from the original factory plus the new rate-limiting options:

| Option | Type | Description | | ----------------- | ------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | redisClient | any \| null | Redis client for the built-in IpRateLimiter. If provided and no rateLimiter is set, an IpRateLimiter is constructed automatically and placed on context.rateLimiter. | | rateLimiter | RateLimiterInterface \| null | Custom rate-limiter instance. Overrides redisClient. Set to null to disable rate limiting explicitly. | | globalRateLimit | RateLimitRule | Service-wide rule applied as the global tier to every runAction call across all cores. |

Express app

// src/app.ts
import express from 'express';
import cors from 'cors';
import cookieParser from 'cookie-parser';
import http from 'http';
import dayjs from 'dayjs';
import { ApolloServer } from '@apollo/server';
import { expressMiddleware } from '@as-integrations/express5';
import { buildSubgraphSchema } from '@apollo/subgraph';
import { ApolloServerPluginDrainHttpServer } from '@apollo/server/plugin/drainHttpServer';
import {
  ApolloServerPluginUsageReportingDisabled,
  ApolloServerPluginInlineTraceDisabled,
} from '@apollo/server/plugin/disabled';
import { OpResult, OP_RESULT_CODES } from '@sdflc/api-helpers';
import { HEADERS } from '@sdflc/backend-helpers';

import typeDefs from './app/graphql/types';
import resolvers from './app/graphql/resolvers';
import { createContext } from './context';
import config from './config';
import * as controllers from './app/restapi';
import { redisClient } from './redisClient';
import { isOpResult } from './utils';
import packageJson from '../package.json';

const corsOptions = {
  origin: (_origin: any, callback: any) => callback(null, true),
  credentials: true,
};

const configureApp = async (app: any) => {
  app.disable('x-powered-by');

  app.use('/api/webhooks/stripe', express.raw({ type: 'application/json' }));
  app.use(express.json({ limit: config.maxReqBodySize }));
  app.use(express.urlencoded({ limit: config.maxReqBodySize, extended: true }));
  app.use(cookieParser());
  app.use(cors(corsOptions));

  // Health check
  app.get('/', (req: any, res: any) => {
    res.json(
      new OpResult({
        data: {
          version: packageJson.version,
          name: packageJson.name,
          utcDateTime: dayjs().toISOString(),
          remoteIp: req.headers[HEADERS.IP_ADDRESS] || req.socket?.remoteAddress,
        },
      }).toJS(),
    );
  });

  // Attach context to non-GraphQL requests
  app.use(async (req: any, _res: any, next: any) => {
    if (req.path !== '/graphql') {
      req.context = await createContext({ req });
    }
    next();
  });

  // Register REST controllers
  Object.keys(controllers).forEach((key) => {
    const controller = new (controllers as any)[key]();
    controller.init({ app });
  });

  const httpServer = http.createServer(app);

  const apolloServer = new ApolloServer({
    schema: buildSubgraphSchema([{ typeDefs, resolvers: resolvers as any }]),
    introspection: !config.isProduction,
    plugins: [
      ApolloServerPluginDrainHttpServer({ httpServer }),
      ApolloServerPluginUsageReportingDisabled(),
      ApolloServerPluginInlineTraceDisabled(),
    ],
  });

  if (redisClient) {
    await redisClient.connect();
  }

  await apolloServer.start();

  app.use(config.graphqlUrl, cors(corsOptions), expressMiddleware(apolloServer, { context: createContext }));

  // 404 handler
  app.use((req: any, res: any) => {
    res
      .status(404)
      .json(
        new OpResult()
          .addError('', `API entry point not supported: '${req.originalUrl}'`, OP_RESULT_CODES.EXCEPTION)
          .toJS(),
      );
  });

  // Global error handler
  app.use((err: any, _req: any, res: any, _next: any) => {
    if (isOpResult(err)) {
      res.status(500).json(err.toJS());
    } else if (typeof err === 'string') {
      res.status(500).json(new OpResult().addError('', err, OP_RESULT_CODES.FAILED).toJS());
    } else if (err instanceof Error) {
      res.status(500).json(new OpResult().addError('', err.message, OP_RESULT_CODES.FAILED).toJS());
    } else {
      res
        .status(500)
        .json(
          new OpResult()
            .setData(err)
            .addError('', 'An error has occurred when processing this request', OP_RESULT_CODES.FAILED)
            .toJS(),
        );
    }
  });

  return httpServer;
};

export default configureApp;

Core concepts

ApiContext

The request context object shared across all layers. Created once per request — or once per CLI invocation.

| Field | Type | Description | | -------------------- | ----------------------- | ----------------------------------------------------------------------------------------------- | | requestId | string | UUID generated per request | | cores | Record<string, any> | All registered core instances (camelCase keys) | | gateways | Record<string, any> | All registered gateway instances (camelCase keys) | | usedKeyPrefixes | Record<string, any> | Tracks gateway key prefixes to detect collisions | | db | Database | Shared database instance | | req / res | any | Express request/response objects | | headers | Record<string, any> | Raw request headers | | cookies | Record<string, any> | Parsed request cookies | | spaceId | string? | From request headers or JWT token | | apiKey | string? | From x-api-key header | | managementApiKey | string? | From management API key header | | ipAddress | string? | Client IP address — used by the rate limiter | | authorization | string? | Bearer token stripped from Authorization header | | fingerprint | string? | CSRF fingerprint from cookie or header | | acceptLanguage | string? | Raw Accept-Language header value | | languages | string[]? | Parsed language preference list | | requestedLang | string? | Resolved language for the request (default 'en') | | userAgent | string? | User-Agent header | | referer | string? | Referer header | | authorizationToken | string? | Raw authorization token passed explicitly | | isExternalDb | boolean? | True when a pre-built db was passed in | | userId | string? | Populated after successful authentication | | accountId | string? | Populated after successful authentication | | roleId | string? | Populated after successful authentication | | isSuperAdmin | boolean? | True when roleId === 'superadmin' | | lang | string? | Resolved language from token or header | | rateLimiter | RateLimiterInterface? | App-level rate-limiter singleton — set by createContextDefault when redisClient is provided | | globalRateLimit | RateLimitRule? | Service-wide rate-limit rule applied to all cores |

BaseCore

The base class for all business logic. The AppCore pattern lets you add project-wide helpers once and extend it for every resource.

// src/core/AppCore.ts
import { BaseCore, BaseCorePropsInterface } from '@sdflc/backend-helpers';

class AppCore extends BaseCore {
  constructor(props: BaseCorePropsInterface) {
    super(props);
  }

  // Add project-wide helpers available to all core subclasses
  public now(): Date {
    return new Date();
  }
}

export { AppCore };

// src/core/NoteCore.ts
import { OP_RESULT_CODES, OpResult } from '@sdflc/api-helpers';
import { BaseCorePropsInterface, BaseCoreActionsInterface, BaseCoreValidatorsInterface } from '@sdflc/backend-helpers';

import { AppCore } from './AppCore';
import { validators } from './validators/noteValidators';

class NoteCore extends AppCore {
  constructor(props: BaseCorePropsInterface) {
    super({
      ...props,
      gatewayName: 'noteGw',
      name: 'Note',
      doAuth: true,
      doingWhat: {
        list: 'listing notes',
        get: 'getting a note',
        getMany: 'getting multiple notes',
        create: 'creating a note',
        update: 'updating a note',
        remove: 'removing a note',
        removeMany: 'removing multiple notes',
      },
      // Rate limiting — tighten create, leave reads unrestricted
      rateLimit: { limit: 60, windowSec: 60 },
      rateLimitOnAction: {
        list: false,
        get: false,
        create: { limit: 10, windowSec: 60 },
      },
    });
  }

  // ... validators, hooks, etc.
}

export { NoteCore };

Per-action authentication

// Require auth globally but allow public listing:
new NoteCore({
  doAuth: true,
  doAuthOnAction: { list: false },
  ...props,
});

Soft authentication (tryAuth)

For public endpoints that behave differently when a user is authenticated (e.g. show "saved" state):

return this.runAction({
  args,
  tryAuth: true, // populate context if token present, never fail
  hasTransaction: false,
  doingWhat: 'listing public notes',
  action: async (args, opt) => {
    const isAuthenticated = !!this.getContext().userId;
    // proceed with or without an authenticated user
  },
});

Before/after hooks

| Hook | Called by | Purpose | | ------------------ | ------------------------- | ---------------------------------------------- | | beforeList | list() | Transform/scope filter args | | afterList | list() | Transform result array | | beforeGet | get() | Validate/transform id args | | afterGet | get() | Enforce ownership, transform single result | | beforeGetMany | getMany() | Transform ids args | | afterGetMany | getMany() | Filter/transform multiple results | | beforeCreate | create() | Enrich params (inject userId, accountId, etc.) | | afterCreate | create() | Transform created records | | beforeUpdate | update() | Transform params, inject ownership into where | | afterUpdate | update() | Transform updated records, send notifications | | beforeRemove | remove() | Inject ownership into where clause | | afterRemove | remove() | Post-removal side effects | | beforeRemoveMany | removeMany() | Inject ownership into each where clause | | afterRemoveMany | removeMany() | Post-removal side effects | | processItemOnIn | All before-mutation hooks | Normalise inbound data | | processItemOnOut | All after hooks | Shape outbound data |

Action-scoped request store

When you need to share data between a before* hook and its corresponding after* hook — for example, fetching a record once before an update and reusing it for audit logging afterward — BaseCore provides a typed key-value store scoped to a single action invocation. A fresh store is created for every runAction call, so there is no bleed between list(), create(), etc., even on the same core instance.

Use the three protected helpers from any before*, after*, validate, or action handler:

| Method | Signature | Description | | ------------------ | ---------------------------------- | ------------------------------------------------------------------------- | | storeSet<T> | (opt, key, value) → T | Stores a value; returns it as a pass-through | | storeGet<T> | (opt, key) → T \| undefined | Retrieves a typed value; undefined when absent | | storeGetOrSet<T> | (opt, key, factory) → Promise<T> | Returns stored value or calls factory(), stores, and returns the result |

Define keys as constants to catch typos at compile time:

// src/core/ProductCore.ts
const STORE = {
  ORIGINAL_RECORD: 'originalRecord',
  PARENT_CATEGORY: 'parentCategory',
} as const;

class ProductCore extends AppCore {
  async beforeUpdate(params: any, opt: BaseCoreActionsInterface): Promise<any> {
    // Fetch the current record once; storeGetOrSet avoids a second DB hit if
    // another hook already loaded it.
    const current = await this.storeGetOrSet(opt, STORE.ORIGINAL_RECORD, () =>
      this.getGateway().get(opt.args?.where?.id),
    );

    this.logger.debug(`Updating product, original slug was '${current?.slug}'`);
    return this.processItemOnIn(params, opt);
  }

  async afterUpdate(items: any[], opt: BaseCoreActionsInterface): Promise<any> {
    const original = this.storeGet<{ slug: string }>(opt, STORE.ORIGINAL_RECORD);

    if (original?.slug && items[0]?.slug !== original.slug) {
      this.logger.log(`Product slug changed from '${original.slug}' to '${items[0]?.slug}'; updating references`);
      // trigger side effects — redirect rules, search index update, etc.
    }

    return Promise.all(items.map((item) => this.processItemOnOut(item, opt)));
  }
}

Notes:

• Prefer storeGetOrSet for anything fetched from the database — it guarantees at most one DB call per action invocation regardless of how many hooks reference the same key.
• The store is also available inside the validate function and the inline action handler passed to runAction() via the opt argument.
• The store is not shared between a parent call and a nested same-request call — each runAction invocation gets its own independent Map.
• storeGet uses Map.has() internally, so storing null or false is treated as a hit, not a miss.

BaseGateway

The base class for all database-backed data access. One gateway per table.

// src/gateways/NoteGw.ts
import { BaseGateway, BaseGatewayPropsInterface } from '@sdflc/backend-helpers';

class NoteGw extends BaseGateway {
  constructor(props: BaseGatewayPropsInterface) {
    super({
      ...props,
      table: 'note',
      keyPrefix: 'note',
      hasStatus: true,
      hasCreatedAt: true,
      hasUpdatedAt: true,
      hasRemovedAt: true, // soft-delete
      hasCreatedBy: true,
      hasUpdatedBy: true,
      hasUserId: true,
      selectFields: ['note.*'],
      idField: 'note.id',
      idFieldUpdateRemove: 'id',
      defaultSorting: [{ name: 'created_at', order: 'desc' }],
    });
  }

  async onListFilter(query: any, filterParams: any): Promise<void> {
    await super.onListFilter(query, filterParams);

    const { userId, title } = filterParams || {};

    if (userId) {
      query.where('note.user_id', userId);
    }

    if (title) {
      query.whereILike('note.title', `%${title}%`);
    }
  }

  async onUpdateFilter(query: any, whereParams: any): Promise<number> {
    let count = await super.onUpdateFilter(query, whereParams);

    if (whereParams?.userId != null) {
      count++;
      query.where({ user_id: whereParams.userId });
    }

    return count;
  }
}

export { NoteGw };

Gateway config flags

| Flag | Type | Description | | ---------------------- | --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | table | string | Database table name | | idField | string | Primary key for SELECT/loader — table-qualify for joined queries (default: id) | | idFieldAs | string | Alias for the id column when it differs from the lookup key | | idFieldUpdateRemove | string | Primary key for UPDATE/DELETE WHERE — must be unqualified | | statusField | string | Column name for the status field (default: 'status'). Set to 'Status_ID' etc. on legacy tables — affects all status WHERE clauses and the soft-delete patch | | setFields | string \| string[] | Conflict target field(s) for the set() upsert operation | | mergeFields | string[] | Explicit field list for the ON CONFLICT MERGE clause | | hasStatus | boolean | Status filtering and REMOVED guard | | hasCreatedAt | boolean | Auto-set created_at on insert | | hasUpdatedAt | boolean | Auto-set updated_at on update | | hasRemovedAt | boolean | Soft-delete via removed_at timestamp | | hasRemovedAtStr | boolean | Also writes a formatted string timestamp to removed_at_str | | hasCreatedBy | boolean | Auto-set created_by from context.userId on insert | | hasUpdatedBy | boolean | Auto-set updated_by from context.userId on update | | hasRemovedBy | boolean | Auto-set removed_by from context.userId on soft-delete | | hasSpace | boolean | Scopes reads/writes to context.spaceId | | hasUserId | boolean | Sets userId from context on create | | hasVersion | boolean | Optimistic locking via version field | | hasLang | boolean | Language-scoped records | | localizationForField | string | Field combined with lang to build a composite localisation id | | hardRemove | boolean | Hard DELETE instead of soft-delete | | noCache | boolean | Bypass DataLoader cache | | returnFields | string[] | Columns in RETURNING clause (default: ['*']) | | defaultSorting | SortItemInterface[] | Default ORDER BY when none provided | | filterByUserField | string | Auto-scope list queries to context.userId | | fieldForKeyword | string | Column to apply keyword ILIKE/LIKE search against | | selectFields | string[] | Columns selected when useCache: false (default: ['*']) | | activeStatuses | number[] | Status values considered active for list filtering | | insertChunkSize | number | Records per INSERT batch — prevents DB parameter limit errors (default: 2500) | | listFilters | GatewayFilterRule[] | Declarative WHERE rules for list() — see Declarative filter rules | | updateFilters | GatewayFilterRule[] | Declarative WHERE rules for update() | | removeFilters | GatewayFilterRule[] | Declarative WHERE rules for remove() | | countFilters | GatewayFilterRule[] | Declarative WHERE rules for count() — falls back to listFilters when not set | | nonCreatableFields | string[] | Field names callers cannot set on create — stripped before INSERT. snake_case or camelCase both accepted | | nonUpdatableFields | string[] | Field names callers cannot change on update — stripped before UPDATE. snake_case or camelCase both accepted |

Implicit write protection. In addition to nonCreatableFields / nonUpdatableFields, the framework always strips a baseline set of fields the framework owns:

• On create — id, removedAt, removedBy (the framework generates id via generateId(); removal markers only belong on the remove path)
• On update — id, createdAt, createdBy, removedAt, removedBy (write-once and remove-only markers)

This implicit stripping happens regardless of whether nonCreatableFields / nonUpdatableFields are set. Use those arrays only for additional resource-specific immutables (e.g. partyId on a junction table whose identity is fixed at creation time). |

Filter hooks

| Hook | Signature | Purpose | | ---------------- | ----------------------------------------------------- | -------------------------------------------------------------- | | onListFilter | (query, filterParams) => Promise<void> | Custom WHERE clauses for list() | | onUpdateFilter | (query, whereParams) => Promise<number> | Custom WHERE clauses for update() — return incremented count | | onRemoveFilter | (query, whereParams) => Promise<number> | Custom WHERE clauses for remove() — return incremented count | | onCountFilter | (query, filterParams) => void | Custom WHERE clauses for count() | | buildListQuery | (args: BuildListQueryArgsInterface) => Promise<any> | Override to add JOINs and computed columns to list queries |

Always call super.onListFilter() / super.onUpdateFilter() / super.onRemoveFilter() at the top of any override so base-class scoping (soft-delete, space, user) runs first.

Soft-delete guards on writes. When hasRemovedAt: true, the base implementations of onUpdateFilter and onRemoveFilter automatically add WHERE table.removed_at IS NULL. This guard is independent of hasStatus — it fires on tables that use Removed_At without the framework's standard status column (a common shape for PartyLabz tables, where hasStatus is false and the row's lifecycle is tracked through Status_ID + Removed_At instead).

The guard prevents two failure modes silently breaking writes:

• An update() on an already-removed row would otherwise produce a half-mutated record (new updated_at and updated_by, original removed_at still set).
• A remove() on an already-removed row would otherwise re-write removed_at and removed_by with a fresh timestamp, masking the fact that the row was removed earlier. Configurable status column. When statusField is set in the config (e.g. statusField: 'Status_ID'), every status-related WHERE clause in onListFilter, onUpdateFilter, onRemoveFilter, and the soft-delete patch in transformOnRemove uses that column name. Set this once on legacy tables that do not use the framework's default status column.

Field-level write protection

BaseGateway strips fields that callers should not be able to set from params before the INSERT or UPDATE statement runs. Two layers cooperate:

Implicit framework-managed fields are always stripped, regardless of configuration. These are columns the framework owns and either generates itself or sets only on a specific path.

| Path | Always stripped | | -------- | -------------------------------------------------------- | | create | id, removedAt, removedBy | | update | id, createdAt, createdBy, removedAt, removedBy |

removedAt and removedBy are only included when their has* flags are set on the gateway. id is always stripped — BaseGateway.generateId() produces it.

Gateway-configured immutables are declared per-gateway via nonCreatableFields and nonUpdatableFields. Names can be supplied in snake_case (DB column form) or camelCase (VM/API form) — both spellings are stripped from the params object so callers can be consistent with whichever convention they prefer.

class PartyGuestGw extends BaseGateway {
  constructor(props: BaseGatewayPropsInterface) {
    super({
      ...props,
      table: 'Party_Guest',
      hasStatus: false, // PartyLabz uses Status_ID
      statusField: 'Status_ID',

      // partyId / personId define the row's identity. Once a guest record
      // exists in a party, those values must never change — only the RSVP
      // state, notes, and audit fields evolve. Stripping them on update
      // ensures a malformed client payload cannot reassign a guest.
      nonUpdatableFields: ['partyId', 'personId'],
    });
  }
}

Logging. Every stripped field is logged at debug level with the table name and field name, so it is easy to confirm during troubleshooting that a "ignored" client value was, in fact, dropped by the framework rather than silently overwritten.

Why this matters. Without this protection, a request like update(where, { id: 'evil', createdBy: 'attacker', myField: 'ok' }) would attempt to rewrite framework-owned columns. The framework strips id and createdBy automatically; the gateway author can extend the strip-list to cover any additional columns whose values must come from the server, not the client.

Declarative filter rules

For the common case of simple equality or array IN filters, declarative rules eliminate the need to override onListFilter, onUpdateFilter, onRemoveFilter, or onCountFilter at all. Rules are passed in the constructor props and applied automatically after the base-class scoping logic.

class PartyGuestGw extends BaseGateway {
  private static readonly DOMAIN_FILTER_RULES = [
    { field: 'partyId', column: 'Party_Guest.Party_ID', type: 'array' as const },
    { field: 'rsvpStatus', column: 'Party_Guest.Rsvp_Status', type: 'array' as const },
    { field: 'wasInvited', column: 'Party_Guest.Invited_At', type: 'nullableBool' as const },
    { field: 'rsvpAt', column: 'Party_Guest.Rsvp_At', type: 'nullableDateRange' as const },
    { field: 'isStarred', column: 'Party_Guest.Is_Starred', type: 'bool' as const },
    {
      field: 'search',
      column: '',
      type: 'search' as const,
      columns: ['Person.First_Name', 'Person.Last_Name', 'Person.Email'],
    },
  ];

  constructor(props: BaseGatewayPropsInterface) {
    super({
      ...props,
      table: TABLES.PARTY_GUEST,
      listFilters: PartyGuestGw.DOMAIN_FILTER_RULES,
      updateFilters: PartyGuestGw.DOMAIN_FILTER_RULES,
      removeFilters: PartyGuestGw.DOMAIN_FILTER_RULES,
      // countFilters omitted — BaseGateway falls back to listFilters automatically
    });
  }
}

Best practice: define the rules once as a private static readonly constant and reference it from all four filter props. This ensures list, update, remove, and count always stay in sync without duplicating the field/column/type data.

Filter rule types

Each GatewayFilterRule has a type that determines the WHERE clause strategy:

| Type | Behaviour | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | array | whereIn(column, values) for arrays; where(column, value) for scalars. Skipped when value is null/undefined/''. | | eq | where(column, value). Strict single-value equality — never expands to whereIn. Skipped when value is null/undefined. | | bool | where(column, true\|false). Fires only on strict true or false; null/undefined leaves the column unconstrained. | | nullableBool | true → whereNotNull(column) / false → whereNull(column) / null/undefined → skipped. Use when a nullable datetime encodes presence (e.g. invitedAt IS [NOT] NULL). | | dateRange | Reads filter[field + 'From'] and filter[field + 'To']. Applies >= and/or <= bounds independently. The field value is the base name (e.g. 'rsvpAt'); actual keys are rsvpAtFrom / rsvpAtTo. | | nullableDateRange | Extends dateRange with a filter[field + 'Null'] boolean: true → whereNull (from/to skipped); false → whereNotNull (from/to still applied); null/undefined → null-ness unconstrained. | | search | LIKE/ILIKE across one or more columns with OR semantics. Reads filter[field] (string). Requires the columns array on the rule. The operator is resolved per-database (ilike on PostgreSQL, like on MSSQL/MySQL). |

Rules with unsupported types on updateFilters/removeFilters (search, dateRange, nullableDateRange) are silently skipped with a warning — update and remove filters must be precise identifiers.

count()

Returns the count of non-removed rows matching a filter. Applies the same soft-delete, space, and user scoping as list(), then runs onCountFilter() and the declarative countFilters rules (falling back to listFilters when countFilters is not set).

// In a core method:
const total = await this.gateway.count({ partyId: args.partyId, rsvpStatus: [1, 2] });

Gateways that already declare listFilters get count() for free with no additional configuration. Set countFilters explicitly only when count needs different scoping than list.

Soft-delete guard order. count() adds the soft-delete guard automatically based on the gateway's flags:

1. When hasRemovedBy: true → WHERE table.removed_by IS NULL
2. Otherwise, when hasRemovedAt: true → WHERE table.removed_at IS NULL
3. Otherwise — no soft-delete guard Removed_By IS NULL is preferred because, in this codebase, Removed_By and Removed_At are always set together on the remove path and Removed_By is the primary sentinel. The fallback to Removed_At covers tables that have a timestamp column but no author column (uncommon but valid).

To add custom WHERE logic beyond what declarative rules express, override onCountFilter():

protected onCountFilter(query: any, filterParams: any): void {
  if (filterParams?.accountId) {
    query.where(`${TABLES.PARTY}.${FIELDS.ACCOUNT_ID}`, filterParams.accountId);
  }
}

Join-variant gateways

For read operations that need columns from related tables alongside the primary table, create additional read-only gateway variants that add JOINs via buildListQuery. The base gateway remains the sole source of truth for mutations.

// PartyGuestGw         — base gateway: all mutations, count(), plain reads
// PartyGuestWithMainPersonGw  — read-only: joins Person for name/email/phone
// PartyGuestWithFullPersonGw  — read-only: joins Person + avatar + extra fields

class PartyGuestWithMainPersonGw extends PartyGuestGw {
  constructor(props: BaseGatewayPropsInterface) {
    super({
      ...props,
      // Each variant must have a unique keyPrefix to prevent DataLoader
      // cache collisions between plain and joined result sets.
      keyPrefix: 'party-guest-with-person',
      selectFields: [
        `${TABLES.PARTY_GUEST}.*`,
        `${TABLES.PERSON}.${FIELDS.FIRST_NAME}`,
        `${TABLES.PERSON}.${FIELDS.EMAIL}`,
        `${TABLES.PERSON}.${FIELDS.PHONE}`,
      ],
    });
  }

  // Join logic lives in buildListQuery, not in list() itself.
  // This ensures the framework's cache/direct-query branching is respected —
  // when useCache is true, only ids are selected here and the DataLoader
  // hydrates records; the join only fires on the useCache: false path.
  protected async buildListQuery(args: BuildListQueryArgsInterface): Promise<any> {
    const query = await super.buildListQuery(args);

    query.leftJoin(TABLES.PERSON, `${TABLES.PERSON}.${FIELDS.ID}`, `${TABLES.PARTY_GUEST}.${FIELDS.PERSON_ID}`);

    return query;
  }
}

Rules for join-variant gateways:

• Mutations always go through the base gateway — join variants are strictly read-only
• Each variant needs a unique keyPrefix — prevents cross-contamination between plain and joined result sets in the DataLoader cache
• Join logic belongs in buildListQuery, not in onListFilter or list() — this respects the framework's useCache branching
• idField must be table-qualified in the base gateway (Party_Guest.ID) to prevent ambiguity in joined queries

BaseController

Base class for custom Express REST controllers with consistent response handling.

// src/app/restapi/EmbedController.ts
import { BaseController } from '@sdflc/backend-helpers';
import { OP_RESULT_CODES, OpResult } from '@sdflc/api-helpers';
import fs from 'fs';
import path from 'path';

class EmbedController extends BaseController {
  init(props: any): void {
    const { app } = props;
    app.get('/api/v1/embed.js', this.getEmbedScript.bind(this));
  }

  async getEmbedScript(req: any, res: any): Promise<void> {
    const scriptPath = path.join(__dirname, '../../public/api/v1/embed.js');

    if (!fs.existsSync(scriptPath)) {
      return res
        .status(404)
        .json(new OpResult().addError('', 'Embed script not found', OP_RESULT_CODES.NOT_FOUND).toJS());
    }

    const script = fs.readFileSync(scriptPath, 'utf8');
    res.setHeader('Content-Type', 'application/javascript; charset=utf-8');
    res.setHeader('Access-Control-Allow-Origin', req.headers.origin || '*');
    res.send(script);
  }
}

export { EmbedController };

The json() wrapper handles all response shapes automatically:

• OpResult → uses result.getHttpStatus() and result.toJS()
• { redirectUrl } → calls res.redirect()
• Plain object → 200 JSON
• null/undefined → 204 No Content

BaseRestApiCrudController

Registers standard CRUD routes automatically. Comma-separated :id values route to getMany/removeMany automatically.

// src/app/restapi/NoteRestController.ts
import { BaseRestApiCrudController } from '@sdflc/backend-helpers';
import { createContext } from '../../context';

class NoteRestController extends BaseRestApiCrudController {
  constructor() {
    super({
      prefix: 'note',
      getContext: (req: any) => createContext({ req }),
      disableCreateMany: true,
      disableUpdateMany: true,
      disableSet: true,
    });
  }
}

// In app.ts:
const controller = new NoteRestController();
controller.init({ app, root: '/api/notes' });

Routes registered for root = '/api/notes':

| Method | Path | Core method | Notes | | -------- | ------------------------ | ----------------------- | --------------------------- | | GET | /api/notes | core.list(query) | Query string becomes filter | | GET | /api/notes/:id | core.get() | Single id | | GET | /api/notes/id1,id2,id3 | core.getMany() | Comma-separated ids | | POST | /api/notes | core.create(body) | | | POST | /api/notes/many | core.createMany(body) | Array body | | PUT | /api/notes/:id | core.update() | | | PUT | /api/notes/many | core.updateMany() | | | DELETE | /api/notes/:id | core.remove() | Single id | | DELETE | /api/notes/id1,id2 | core.removeMany() | Comma-separated ids |

BaseExternalApiGateway

Base class for external API integrations. Credentials are injected at runtime after construction.

// src/gateways/StripeGateway.ts
import { BaseExternalApiGateway } from '@sdflc/backend-helpers';
import Stripe from 'stripe';

interface StripeCredentials {
  secretKey: string;
}

class StripeGateway extends BaseExternalApiGateway {
  private client: Stripe | null = null;

  protected onCredentialsSet(): void {
    const { secretKey } = this.credentials as StripeCredentials;
    this.client = new Stripe(secretKey);
  }

  async chargeCard(amount: number, currency: string): Promise<any> {
    this.assertCredentials();
    return this.client!.paymentIntents.create({ amount, currency });
  }
}

// In a core method:
const gw = new StripeGateway({ baseUrl: 'https://api.stripe.com' });
const creds = await db.getCredentials(context.spaceId, 'stripe');
gw.setCredentials(creds); // accepts string or Record<string, any>
await gw.chargeCard(1000, 'usd');

IP rate limiting

How it works

Rate limiting is built into BaseCore and runs automatically on every runAction call — before the database transaction is opened and before authentication. This protects auth endpoints from brute-force without any DB overhead.

The implementation uses two independent tiers, both of which must pass for a request to proceed. Each tier maintains its own Redis counter keyed on (space, scope, action, ip).

Global tier — a service-wide ceiling applied to every core and every action. Cannot be opted out by core or action configuration. Guards the whole service from a flood regardless of which endpoint is targeted.

Core-or-action tier — a more specific limit scoped to a core or a single action. Core and action rules are mutually exclusive: an action rule replaces the core rule for that action so that loosening a limit actually raises it — the core counter will not silently block first.

Resolution for the core-or-action tier (first defined value wins):

1. CoreActionInterface.rateLimit — per-call override in runAction()
2. BaseCorePropsInterface.rateLimitOnAction[actionName] — per-action props
3. BaseCorePropsInterface.rateLimit — core-level default
4. Nothing — tier is skipped

Using false at steps 1 or 2 disables the core-or-action tier for that action entirely so only the global tier applies. false never suppresses the global tier.

Blocked requests receive OP_RESULT_CODES.TOO_MANY_REQUESTS (429) with a human-readable message including the time until the window resets.

Redis errors are swallowed silently — the limiter allows requests through rather than taking down an endpoint. If Redis is not configured, rate limiting is completely skipped with zero overhead.

Setup

The minimum to enable rate limiting is passing redisClient to createContextDefault. An IpRateLimiter instance is constructed once per request context and shared across all cores automatically — no per-core wiring needed.

// src/context.ts
export const createContext = async (args: any) => {
  return createContextDefault({
    // ...existing options...
    redisClient, // enables rate limiting
    globalRateLimit: { limit: 300, windowSec: 60 }, // optional service-wide cap
  });
};

That is the only change needed to get rate limiting running across all cores. Per-core and per-action rules are additive on top.

Global tier

Set globalRateLimit on the context to cap every IP across all cores and all actions. This is the flood guard — it runs regardless of what the core or action configures.

createContextDefault({
  redisClient,
  globalRateLimit: { limit: 500, windowSec: 60 }, // 500 req/min per IP, service-wide
});

The global tier has no opt-out. If you need an endpoint to be completely unmetered, do not set globalRateLimit.

Core-level rules

Add rateLimit to a core's constructor props to apply a default rule to every action in that core. Individual actions can override or disable it via rateLimitOnAction.

class AuthCore extends AppCore {
  constructor(props: BaseCorePropsInterface) {
    super({
      ...props,
      rateLimit: { limit: 30, windowSec: 60 }, // 30 req/min for all auth actions
      rateLimitOnAction: {
        list: false, // no core-tier limit for list
        get: false, // no core-tier limit for get
        create: { limit: 5, windowSec: 300 }, // tighter for sign-up (replaces core rule)
      },
    });
  }
}

rateLimitOnAction values:

| Value | Effect | | --------------- | -------------------------------------------------------------- | | RateLimitRule | Replaces the core rule for this action (action wins) | | false | Disables the core-or-action tier for this action (global only) | | undefined | Falls back to the core-level rateLimit default |

Action-level rules

Pass rateLimit directly to runAction() for a one-off rule on a specific call. This is the highest-priority override and replaces any rateLimitOnAction or rateLimit configured in props for that call.

return this.runAction({
  args,
  doAuth: true,
  hasTransaction: true,
  actionName: 'signIn',
  rateLimit: { limit: 5, windowSec: 300 }, // 5 attempts per 5 minutes
  doingWhat: 'signing in',
  action: async (args, opt) => {
    /* ... */
  },
});

Disabling rate limiting

Set rateLimit: false at the call site or in rateLimitOnAction to disable the core-or-action tier for a specific action. The global tier still runs.

// Disable core-tier for an internal helper action:
return this.runAction({
  args,
  rateLimit: false,
  hasTransaction: false,
  doingWhat: 'fetching internal config',
  action: async (args, opt) => {
    /* ... */
  },
});

// Disable core-tier for specific actions in props:
super({
  ...props,
  rateLimit: { limit: 60, windowSec: 60 },
  rateLimitOnAction: {
    list: false, // reads are unrestricted at core tier
    get: false,
  },
});

To disable rate limiting entirely for a core (including ignoring context.rateLimiter), pass rateLimiter: null in the core's props:

super({
  ...props,
  rateLimiter: null, // this core is completely exempt from rate limiting
});

Resetting a counter after success

After a successful sensitive operation (e.g. sign-in), you can reset the IP rate-limit counter so a legitimate user who was briefly throttled is not stuck waiting for the window to expire.

Call this.resetRateLimit(actionName) from any BaseCore subclass. The actionName must match the value used in the runAction call that incremented the counter — either the actionName field or the rule.action override if one was set. The method silently no-ops when no limiter is configured or when Redis is unavailable.

public async signIn(args: any) {
  return this.runAction({
    args,
    actionName: 'signIn',
    rateLimit: { limit: 20, windowSec: 900 },
    action: async (args) => {
      // ... validate credentials ...

      if (!passwordValid) {
        return this.failure(OP_RESULT_CODES.UNAUTHORIZED, 'Username/Password pair is invalid');
      }

      // Successful sign-in — clear the failed-attempt counter so the user
      // is not stuck throttled until the 15-minute window expires.
      await this.resetRateLimit('signIn');

      // ... issue cookies, return user ...
    },
    hasTransaction: false,
    doingWhat: 'signing in a user',
  });
}

The optional second argument controls which tier's counter is reset. It defaults to 'core-action', which covers rules set via rateLimit on runAction or rateLimitOnAction / rateLimit in props. Pass 'global' to reset the global tier counter instead.

await this.resetRateLimit('signIn'); // resets the core-or-action counter (default)
await this.resetRateLimit('signIn', 'global'); // resets the global counter

Custom limiter

To use a different rate-limiting backend (e.g. in-memory for tests, DynamoDB for Lambda), implement RateLimiterInterface and pass it via rateLimiter in createContextDefault or in per-core props.

import { RateLimiterInterface, RateLimitCheckArgs, RateLimitResult } from '@sdflc/backend-helpers';

class NoOpRateLimiter implements RateLimiterInterface {
  async check(_args: RateLimitCheckArgs): Promise<RateLimitResult> {
    return { blocked: false, count: 0, ttlSec: -1 };
  }
  async reset(_args: Pick<RateLimitCheckArgs, 'action' | 'ip' | 'spaceId'>): Promise<void> {}
}

// In tests — disable all rate limiting:
createContextDefault({
  rateLimiter: new NoOpRateLimiter(),
  // no redisClient needed
});

Database

Database class

Wraps Knex with connection pool deduplication, transaction management, and schema support.

import { Database } from '@sdflc/backend-helpers';

const db = new Database({
  knexConfig: { client: 'pg', connection: { host, port, user, password, database } },
  dbSchema: 'public',
  logger,
});

// Read utility with filtering, mapping, and computed fields:
const userMap = await db.read({
  table: 'user',
  selectFields: ['id', 'email', 'first_name', 'last_name'],
  where: { status: 100 },
  mapBy: 'id',
  calculated: [{ name: 'displayName', value: (row: any) => `${row.firstName} ${row.lastName}` }],
});

// Transactions are managed automatically by BaseCore.runAction()
await db.startTransaction();
try {
  await db.commitTransaction();
} catch {
  await db.rollbackTransaction();
}

DataCache and custom loaders

DataCache is the default per-request DataLoader-backed cache. You can provide a custom loader when you need non-standard fetching — for example enriching records with a JOIN.

// src/gateways/NoteGw.ts
import { BaseGateway, BaseGatewayPropsInterface } from '@sdflc/backend-helpers';

class NoteGw extends BaseGateway {
  constructor(props: BaseGatewayPropsInterface) {
    super({
      ...props,
      table: 'note',
      keyPrefix: 'note',
      hasStatus: true,
      // statusField: 'Status_ID',     // ← uncomment for legacy tab