npm package: @sdflc/backend-helpers
Description: Set of helper functions and classes to simplify building backend apps within @sdflc ecosystem
Author: Sergey Demidenko
Latest version: 1.0.127

# @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](#installation)
- [Requirements](#requirements)
- [Architecture overview](#architecture-overview)
- [Quick start](#quick-start)
- [Configuration](#configuration)
  - [buildConfig](#buildconfig)
  - [buildKnexConfig](#buildknexconfig)
- [Application setup](#application-setup)
  - [Logger](#logger)
  - [Redis client](#redis-client)
  - [Knex handle](#knex-handle)
  - [Context](#context)
  - [Express app](#express-app)
- [Core concepts](#core-concepts)
  - [ApiContext](#apicontext)
  - [BaseCore](#basecore)
  - [BaseGateway](#basegateway)
  - [BaseController](#basecontroller)
  - [BaseRestApiCrudController](#baserestapicrudcontroller)
  - [BaseExternalApiGateway](#baseexternalapigatewayx)
- [IP rate limiting](#ip-rate-limiting)
  - [How it works](#how-it-works)
  - [Setup](#setup)
  - [Global tier](#global-tier)
  - [Core-level rules](#core-level-rules)
  - [Action-level rules](#action-level-rules)
  - [Per-call override](#per-call-override)
  - [Disabling rate limiting](#disabling-rate-limiting)
  - [Resetting a counter after success](#resetting-a-counter-after-success)
  - [Custom limiter](#custom-limiter)
- [Database](#database)
  - [Database class](#database-class)
  - [DataCache and custom loaders](#datacache-and-custom-loaders)
  - [RedisDataLoader](#redisdataloader)
- [Authentication](#authentication)
  - [JWT token helpers](#jwt-token-helpers)
- [GraphQL helpers](#graphql-helpers)
  - [buildDefaultResolvers](#builddefaultresolvers)
- [CLI utilities](#cli-utilities)
- [Migration and seeding utilities](#migration-and-seeding-utilities)
  - [DbMigratorAndSeeder](#dbmigratorandseeder)
  - [dbHelpers](#dbhelpers)
- [Field helpers](#field-helpers)
- [Validation helpers](#validation-helpers)
- [Utility functions](#utility-functions)
  - [downloadFile](#downloadfile)
  - [passwordHelpers](#passwordhelpers)
- [Constants](#constants)
- [Interfaces reference](#interfaces-reference)

---

## Installation

```bash
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.

```typescript
// 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(','),
};
```

```bash
# .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.

```typescript
// 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

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

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

---

### Redis client

```typescript
// 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).

```typescript
// 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.

```typescript
// 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

```typescript
// 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.

```typescript
// 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 };
```

```typescript
// 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

```typescript
// 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):

```typescript
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:

```typescript
// 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.

```typescript
// 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                                        |
| `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](#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                 |

#### 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.

---

#### 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.

```typescript
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).

```typescript
// 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.

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

```typescript
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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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.

```typescript
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`.

```typescript
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.

```typescript
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.

```typescript
// 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:

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
// src/gateways/NoteGw.ts — gateway with a custom loader
import { BaseGateway, BaseGatewayPropsInterface, DataCache } from '@sdflc/backend-helpers';

class NoteGw extends BaseGateway {
  constructor(props: BaseGatewayPropsInterface) {
    super({ ...props, table: 'note', keyPrefix: 'note' });

    // Replace the default loader with one that enriches notes with author info
    this.setLoader(
      new DataCache({
        ...props,
        table: 'note',
        idField: 'id',
        keyPrefix: 'note-with-author',
        loaderFn: this.batchLoadWithAuthor.bind(this),
      }),
    );
  }

  // Must return results in the same order as the input ids array
  private async batchLoadWithAuthor(ids: string[]): Promise<any[]> {
    const rows = await this.getDb()
      .getBuilder('note')
      .select('note.*', 'user.name as authorName')
      .leftJoin('user', 'user.id', 'note.user_id')
      .whereIn('note.id', ids);

    const map = new Map(rows.map((row: any) => [row.id, row]));
    return ids.map((id) => map.get(id) || null);
  }
}
```

You can also swap loaders at runtime:

```typescript
// Swap to a Redis-backed cache:
gateway.setLoaderConfig({ redisClient, cacheTtl: 600 });

// Replace with a fully custom loader:
gateway.setLoader({
  load: (id: any) => fetchFromExternalAPI(id),
  loadMany: (ids: any[]) => Promise.all(ids.map(fetchFromExternalAPI)),
  clear: (_key: any) => gateway,
  clearAll: () => gateway,
  prime: (_key: any, _value: any) => gateway,
});
```

---

### RedisDataLoader

Used internally by `DataCache` when a `redisClient` is provided. Enable it for all gateways by passing `redisClient` via `getGatewayContext()`:

```typescript
getGatewayContext: () => ({
  redisClient,
  cacheTtl: 300,
}),
```

---

## Authentication

### JWT token helpers

```typescript
import { createUserToken, verifyUserToken } from '@sdflc/backend-helpers';

// Create a token with fingerprint (CSRF protection):
const result = createUserToken(
  { userId: 'user-uuid', accountId: 'account-uuid', roleId: 'admin', spaceId: 'space-id', lang: 'en' },
  process.env.JWT_SECRET!,
  { expiresIn: '7d', useFingerprint: true },
);

if (result.didSucceed()) {
  const { token, fingerprint } = result.getDataFirst();
  res.cookie('fp', fingerprint, { httpOnly: true, secure: true, sameSite: 'strict' });
  res.json({ token });
}

// Verify a token (called automatically by BaseCore.authenticate()):
const verifyResult = verifyUserToken(token, process.env.JWT_SECRET!, {
  algorithms: ['HS256'],
  audience: context.spaceId,
  issuer: 'my-service',
  fingerprint: req.cookies.fp,
});

if (verifyResult.didFail()) {
  return res.status(401).json(verifyResult.toJS());
}
```

---

## GraphQL helpers

### buildDefaultResolvers

Generates a standard set of GraphQL resolvers for a resource.

```typescript
// src/app/graphql/resolvers/noteResolvers.ts
import { buildDefaultResolvers } from '@sdflc/backend-helpers';

const resolvers = buildDefaultResolvers({
  prefix: 'note',
  core: 'noteCore',
  disableCreateMany: true,
  disableUpdateMany: true,
  disableSet: true,
  types: {
    Note: {
      async __resolveReference(reference: any, context: any) {
        if (!reference.id) return null;
        return context.gateways.noteGw.get(reference.id);
      },
      userCreated(parent: any, _args: any, _context: any) {
        return { __typename: 'User', id: parent.createdBy };
      },
    },
  },
});

export default resolvers;
```

```typescript
// src/app/graphql/types/noteTypes.ts
const typeDefs = `#graphql
  type Note @key(fields: "id") {
    id:          ID
    title:       String
    body:        String
    userId:      ID
    createdAt:   String
    updatedAt:   String
    userCreated: User
  }

  type NoteResult implements OpResult {
    code:   Int!
    errors: [Error!]
    data:   [Note!]
  }

  input NoteInput {
    title: String!
    body:  String
  }

  input NoteFilter {
    id:            [ID]
    title:         String
    searchKeyword: String
  }

  input NoteWhereInput {
    id: ID!
  }

  type Query {
    noteList(filter: NoteFilter, params: PaginationAndSorting): NoteResult
    noteGet(id: ID): NoteResult
    noteGetMany(ids: [ID]): NoteResult
  }

  type Mutation {
    noteCreate(params: NoteInput): NoteResult
    noteUpdate(where: NoteWhereInput, params: NoteInput): NoteResult
    noteRemove(where: NoteWhereInput): NoteResult
    noteRemoveMany(where: [NoteWhereInput]): NoteResult
  }
`;

export default typeDefs;
```

Generated resolvers:

| Resolver              | Type     | Core method             |
| --------------------- | -------- | ----------------------- |
| `${prefix}List`       | Query    | `core.list(args)`       |
| `${prefix}Get`        | Query    | `core.get(args)`        |
| `${prefix}GetMany`    | Query    | `core.getMany(args)`    |
| `${prefix}Create`     | Mutation | `core.create(args)`     |
| `${prefix}CreateMany` | Mutation | `core.createMany(args)` |
| `${prefix}Update`     | Mutation | `core.update(args)`     |
| `${prefix}UpdateMany` | Mutation | `core.updateMany(args)` |
| `${prefix}Set`        | Mutation | `core.set(args)`        |
| `${prefix}Remove`     | Mutation | `core.remove(args)`     |
| `${prefix}RemoveMany` | Mutation | `core.removeMany(args)` |

---

## CLI utilities

Because all business logic lives in core classes, the same operations available over HTTP and GraphQL are equally accessible from a CLI command — with no duplication. The CLI simply constructs a context with synthetic request headers and calls core methods directly.

### Project setup

Add a `cmd` script to `package.json` that points at your CLI entry point:

```json
{
  "scripts": {
    "cmd": "ts-node -r tsconfig-paths/register --project ./tsconfig.json ./src/app/cli/index.ts",
    "build": "cross-env NODE_ENV=production tsc -p ./tsconfig.json",
    "start": "node dist/src/index.js",
    "start:dev": "nodemon --exec \"ts-node -r tsconfig-paths/register --project ./tsconfig.json ./src/index.ts\" -e ts",
    "lint": "eslint */**/*.{js,ts,tsx} --quiet --fix",
    "test": "jest --config jest.config.ts",
    "create-migration": "knex migrate:make -x ts --knexfile ./src/knexConfig.ts",
    "migrations": "knex migrate:latest --knexfile ./src/knexConfig.ts",
    "migration-down": "knex migrate:down --knexfile ./src/knexConfig.ts",
    "rollback-migration": "knex migrate:rollback --knexfile ./src/knexConfig.ts",
    "seed": "knex seed:run --knexfile ./src/knexConfig.ts",
    "reset-db": "npm run cmd -- reset"
  }
}
```

Usage:

```bash
npm run cmd -- reset            # wipe DB, run migrations, seed
npm run cmd -- recalculate      # trigger a recalculation job
npm run cmd -- fetch-recalls    # fetch data from an external API
npm run cmd -- --help           # list all available commands
```

### CLI entry point

```typescript
// src/app/cli/index.ts
import yargs from 'yargs';
import { HEADERS, dbReset } from '@sdflc/backend-helpers';

import { createContext } from '../../context';
import config from '../../config';
import knexHandle from '../../knexHandle';
import { logger } from '../../logger';
import { redisClient } from '../../redisClient';

const getContext = () =>
  createContext({
    res: {},
    req: {
      socket: { remoteAddress: 'LOCAL' },
      headers: {
        [HEADERS.SPACE_ID]: 'develop',
        [HEADERS.API_KEY]: config.interserviceApiKey,
        [HEADERS.ACCOUNT_ID]: 'system',
        [HEADERS.USER_ID]: 'system',
      },
    },
  });

const resetDb = () => {
  dbReset(knexHandle, config).then(() => knexHandle.destroy());
};

const recalculateStats = async () => {
  const context = await getContext();

  try {
    logger.log('Recalculating stats...');
    await context.cores.noteCore.recalculateAll({});
    logger.log('Done.');
  } finally {
    process.exit(0);
  }
};

const syncExternalData = async () => {
  const context = await getContext();

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

    logger.log('Syncing data from external API...');
    const result = await context.cores.noteCore.syncFromExternal({
      batchSize: 100,
      maxRe