Data Service Utils

This directory holds shared utility functions used by the data-service layer. These utilities have a well-defined responsibility boundary and are not project-wide utilities.

Before using, read the Row → Entity Mapping section of data-api-in-main.md to understand the service-layer paradigm and conventions (what rowToEntity looks like, when to use nullsToUndefined, etc.). The section below captures the design-decision history behind these utilities.

File Index

rowMappers.ts — Row → Entity mapping utilities

Serves each Service's rowToEntity function, performing the boundary translation from a SQLite row to a domain entity.

Exports:

nullsToUndefined<T>(obj: T): { [K in keyof T]: null extends T[K] ? Exclude<T[K], null> | undefined : T[K] }

Shallowly replaces top-level null values in the object with undefined, preserving all other values.

Design boundaries:

• Shallow: iterates top-level fields only; does not recurse into nested objects or arrays
• Replace, not delete: the returned object keeps every original field (value becomes undefined); it does not produce a Partial<T>
• SQLite-column-boundary only: designed for column NULL → TS undefined translation; null should not appear inside JSON payloads (if it does, fix the Zod schema instead)
• Precise typing: only fields whose type includes null are narrowed to Exclude<T, null> | undefined; notNull() columns pass through unchanged. This matches runtime reality — a notNull() column cannot produce undefined at this boundary.

Example:

import { nullsToUndefined } from './rowMappers'

const row = { id: 'x', name: 'MCP-1', description: null, timeout: null }
const clean = nullsToUndefined(row)
// clean = { id: 'x', name: 'MCP-1', description: undefined, timeout: undefined }
// type: { id: string; name: string; description: string | undefined; timeout: number | undefined }

timestampToISO(value: number | Date): string

Convert a guaranteed-present timestamp (millisecond epoch) to an ISO string. Use when the input type is already narrowed to number | Date — typically for .notNull() columns or post-validation values.

Why the signature rejects null | undefined: new Date(null).toISOString() silently returns the Unix epoch ("1970-01-01T00:00:00.000Z"). Letting the type system refuse null | undefined at the call site turns a silent bug into a compile error.

Behavioral note on 0: 0 is a legitimate timestamp (Unix epoch); this helper passes it through. This differs from timestampToISOOrUndefined which treats 0 as falsy.

timestampToISOOrUndefined(value: number | Date | null | undefined): string | undefined

Convert an optional DB timestamp to an ISO string, preserving absence as undefined. Reserved for construction paths where the entire source row may not exist — not "this column might be null". The audit columns createdAt / updatedAt are DB-level NOT NULL (see createUpdateTimestamps in _columnHelpers.ts), so a row read from the DB always has real values.

The canonical use case is a merge between a builtin/preset definition and an optional DB preference row:

function builtinToMiniApp(def: BuiltinMiniAppDefinition, dbRow?: MiniAppSelect): MiniApp {
  return {
    /* ... builtin fields ... */
    createdAt: timestampToISOOrUndefined(dbRow?.createdAt), // undefined when builtin has no preference row yet
    updatedAt: timestampToISOOrUndefined(dbRow?.updatedAt)
  }
}

Behavioral note on 0: the helper treats 0 as falsy (matching the prior row.x ? ... : undefined idiom). Zero is not a valid business timestamp in this codebase.

Picking between the two helpers:

Example:

import { timestampToISO, timestampToISOOrUndefined } from './rowMappers'

timestampToISO(1700000000000)                       // "2023-11-14T22:13:20.000Z"
timestampToISO(0)                                   // "1970-01-01T00:00:00.000Z" (passes through)

timestampToISOOrUndefined(1700000000000)            // "2023-11-14T22:13:20.000Z"
timestampToISOOrUndefined(undefined)                // undefined (e.g. builtin with no preference row)

orderKey.ts — order_key column runtime operations

Backs every Service's reorder write path and POST-create. Encapsulates the fractional-indexing library, transactional SQL, and scope filtering behind a small set of wrappers. Required in all service POST-create and reorder paths; migrator helpers and migration scripts re-import from here.

Exports:

• generateOrderKeySequence(count) / generateOrderKeyBetween(before, after) / generateOrderKeySequenceBetween(before, after, count) — the ONLY wrappers around fractional-indexing in this codebase. Migrator helpers and migration scripts re-import from here.
• insertWithOrderKey(tx, table, values, { pkColumn, position?, scope? }) — the only correct entry for POST-create endpoints on sortable tables; never write tx.insert(table).values(...) directly.
• insertManyWithOrderKey(tx, table, valuesList, { pkColumn, position?, scope? }) — batch variant. Does ONE boundary-key lookup and ONE bulk INSERT .. RETURNING for N rows. Preferred whenever creating ≥2 rows at once (bulk imports, multi-row service ops). insertWithOrderKey internally delegates to it.
• applyMoves(tx, table, moves, { pkColumn, scope? }) — the only correct entry for reorder operations (batch + single). Dedups duplicate ids (keeps last, warns). Contract rejections surface as DataApiError: missing target id → NOT_FOUND, missing anchor id → NOT_FOUND, anchor === own id → VALIDATION_ERROR. Resource name in the error is the Drizzle table name. Suitable for both fixed-scope and nullable-scope callers — the consumer constructs scope? (e.g. isNull(col) or eq(col, value)) and propagates the error verbatim.
• resetOrder(tx, table, orderedRows, { pkColumn }) — paired with POST /:res/order:reset; rewrites orderKey with a fresh evenly-spaced sequence in the given order.
• computeNewOrderKey(tx, table, request, { pkColumn, scope? }) — exported only for unit tests.

Design boundaries:

• Only operates on order_key: business validation (does :id exist in the resource sense) lives in the service/handler layer, not here.
• Must run inside an outer transaction: helpers take tx and never open their own transaction.
• scope? (SQL): constrains neighbor queries to a subset for partial ordering (e.g. userModel.providerId, group.entityType). Scope applies to BOTH the target lookup and the anchor lookup — anchoring across scopes throws.
• pkColumn is required: tables have heterogeneous primary-key column names (miniapp.appId, mcpServer.id, topic.id, group.id). Helpers make zero assumptions.
• External imports of fractional-indexing are forbidden: always go through the three generator wrappers above.
• Character set is locked to base62 (library default); no digits parameter is exposed. Changing the alphabet requires a whole-database migration, and the source-of-truth constant lives at the top of orderKey.ts.

keysetCursor.ts — keyset (cursor) pagination codec + predicate

Backs every list endpoint that pages by a (sortKey, id) tuple. Owns the <key>:<id> wire-format codec and the strict-tuple keyset WHERE predicate, so the tie-break direction and the warn message live in one tested place instead of being hand-rolled (and drifting) per service.

Exports:

• parseCursor<K>(raw, parseKey) — pure <key>:<id> parser; splits on the FIRST : (so ids may contain :), returns null for any unparseable input (absent/empty raw, no separator, empty key, empty id, or a parseKey that rejects the key). Shared with ftsSearch so list and search parse identically.
• encodeCursor(key, id) — encode a (key, id) boundary into <key>:<id>; key may be a number or a string.
• asNumericKey(s) / asStringKey(s) — parseKey helpers for numeric (createdAt) and string (orderKey) sort columns. Both reject the empty string — asNumericKey must, because Number('') === 0 is finite.
• decodeListCursor<K>(raw, parseKey, context) — list-browsing decode: an absent cursor returns null (first page, no warn); a malformed cursor warns once with the locked message and falls back to the first page (null). context is a short caller tag carried in the warn payload.
• keysetOrdering(keyCol, idCol, { major, tie }) — returns { where(cursor), orderBy } from one direction spec: where builds after(keyCol) OR (keyCol = cursor.key AND after(idCol)) (after is gt for 'asc', lt for 'desc'); orderBy is [<major> keyCol, <tie> idCol] ready to spread into .orderBy(...). Both derive from the same dir, so the predicate and the ORDER BY cannot drift apart.

Design boundaries:

• Two decode policies, deliberately split: list browsing warns and falls back to the first page (decodeListCursor → null), while search throws 422 (ftsSearch.decodeSearchCursor). A stale server-issued list token must not lock the renderer; a malformed search cursor is a client contract violation.
• Warn message is locked: 'decodeCursor: cursor unparseable, falling back to first page' — kept uniform across call sites; the context field distinguishes the source.
• Single-tuple keyset only: covers (key, id) pagination. Multi-band / sentinel cursors (e.g. TopicService's pin/topic union with a first-page sentinel) cannot be expressed as one (key, id) tuple, and their malformed-fallback returns a sentinel rather than null — they keep their own codec and must NOT be routed here.
• Direction is declared once: keysetOrdering emits both the where predicate and the matching orderBy from a single { major, tie }, so the WHERE clause and the ORDER BY cannot disagree — the classic keyset skip/repeat bug becomes unrepresentable.

Example:

import { asNumericKey, decodeListCursor, encodeCursor, keysetOrdering } from './utils/keysetCursor'

const ordering = keysetOrdering(table.createdAt, table.id, { major: 'desc', tie: 'asc' })
const cursor = decodeListCursor(query.cursor, asNumericKey, 'translate-history')
const conditions: SQL[] = [...filterConditions]
if (cursor) conditions.push(ordering.where(cursor))
const rows = await db
  .select()
  .from(table)
  .where(and(...conditions))
  .orderBy(...ordering.orderBy) // never drifts from ordering.where
  .limit(limit + 1)
const nextCursor = hasNext ? encodeCursor(tail.createdAt, tail.id) : undefined

ftsSearch.ts — FTS cursor, filtering, and pagination core

Shared by full-text search services that use SQLite FTS5 trigram tables. It owns the common opaque cursor codec, trigram-FTS candidate filtering, literal regex revalidation, bounded offset scanning, and next-cursor assembly.

FTS contract:

• The caller's SQL must join its FTS5 virtual table aliased as fts.
• The FTS table must be created with tokenize='trigram' and expose a searchable_text column.
• The utility builds fts.searchable_text LIKE ... conditions and the caller inserts those conditions into its own SQL shape.

Design boundaries:

• Cursor codec is shared: decodeSearchCursor / encodeSearchCursor delegate the <key>:<id> parsing to keysetCursor.parseCursor / encodeCursor; this module keeps only the 422-throw policy and the SearchCursor = { createdAt, id } shape.
• SQL shape stays with the owning service: callers provide the raw SQL query and row mapper because each domain joins different tables.
• Read-only search only: this utility never writes, opens transactions, or applies domain ownership rules.
• Snippet construction is injected: callers decide how to build display snippets from matched text and terms.
• Cursor sort keys are caller-owned: mapRow returns the public item plus the (createdAt, id) boundary used to assemble nextCursor.
• Candidate scans are bounded: LIKE candidates that fail regex revalidation stop at the configured ceiling and log a warning instead of scanning an entire FTS table for one page.
• Role coercion is caller-owned: role subsets live with the message domain in @shared/data/types/message; this generic utility does not know message roles.

singleFileRef.ts — single-file (logo) slot mechanics

Backs the provider / mini-app logo slots. A single-file slot is an association table where one owner row holds at most one file: the ref row is the single source of truth for that owner's uploaded file, and the owner row keeps only a preset key.

Exports:

• getSingleFileRefId(table, sourceId) — the uploaded file's file_entry id for a slot, or null. One indexed lookup on the unique (sourceId) index.
• clearSingleFileRefTx(tx, table, sourceId) — drop the slot's ref row.
• insertSingleFileRefTx(tx, table, sourceId, fileId) — insert a ref row without clearing first (the migrator's empty-slot path).
• reconcileLogoSlotTx(tx, table, sourceId, input) — replace the slot's ref per a LogoBindInput and return the logoKey to persist on the owner row; null when input is undefined (update no-op).
• LogoBindInput / LogoColumns / SingleFileRefTable — the bind-input union, the resolved owner column, and the structural table constraint.

Design boundaries:

• The table is a parameter, never a switch: each owner service passes its own table, so a service has no way to reach another owner's slot (services/README "Own your table"), and adding a slot type needs no change here. Same rationale as orderKey.ts.
• DB-only: never touches the filesystem. The caller stores the bytes first and passes an opaque fileId; superseded files are preserved per the file layer's policy.
• Structural table constraint: SingleFileRefTable requires only fileEntryId + sourceId columns plus a unique index on (sourceId) — no assumption about the owning domain.
• "Single-file" is a precondition, not a label: it names the category (opposed to the roled collection ref tables chat_message_file_ref / painting_file_ref, where one owner holds many rows), and the write path relies on it — it clears before inserting, so passing a table that permits several rows per sourceId would delete rows the caller never meant to touch.
• Two naming layers, deliberately: the SingleFileRef* helpers are the table-agnostic mechanism; reconcileLogoSlotTx / LogoBindInput / LogoColumns sit above it and are logo-specific, because every single-file slot that exists today is a logo slot. Do not genericize the reconcile layer until a second kind of slot exists — logoKey maps to a real column name.
• sourceType → table resolution belongs to the caller: callers holding a source type instead of a table (the v1 migrator) resolve it via singleFileRefTablesBySourceType in db/schemas/fileRelations.ts; this module never sees a source type.

Criteria for Adding a New Utility

Before adding a new utility to this directory, confirm:

1. Is domain-neutral — the file must not name a specific business table, entity, or source type. The test: when a new consumer adopts it, does this file have to change? A generic mechanism is closed to that change (orderKey.ts and singleFileRef.ts take the table as a parameter); logic that grows a branch per consumer is shared domain logic and belongs with its owners, not here. Consumer count alone does not qualify a utility — two consumers of the same domain logic is still domain logic.
2. Has at least two real consumers (history: stripNulls qualified because MiniAppService had made a copy-paste duplicate)
3. Do not extract simple single-field operations: operations like value ?? undefined are already well-covered by TypeScript itself — do not wrap them
4. Does not duplicate an existing third-party library (e.g. lodash) — unless we have specific boundary constraints
5. Add a new entry to the "File Index" above documenting responsibility, signature, boundaries, and an example

Rejected Alternatives

The following approaches to the "SQLite NULL ↔ TypeScript optional" bridge were evaluated and rejected. Do not re-propose them unless you have new evidence that invalidates the reason given; if so, cite the data explicitly.