Queries

Import from @workspace/training/application/shared. This module holds the cross-cutting query layer: the contracts a module's XxxQuery, XxxCriteria, XxxProjections and XxxAggregations implement, the where-clause operator primitives, the pagination value objects, and the two generic repository ports a module aliases to get its read and write surfaces. Repository implementations in @workspace/database consume only the plain payloads these types describe, never the concrete classes.

This page is also the reference for the repository doctrine: the canonical surface, who may build a repository, junction tables, public links, the ports that stay bespoke and the lockstep rule.

The themes module is the worked example on this page. Boards and editor boards bind the same abstractions under their own names, see Boards, Editor boards and Themes.

Query objects

A query object encapsulates query criteria as an immutable, valid-by-construction value. The concrete query exposes a business-oriented fluent API; the repository consumes only the result of toParams() and must not depend on the concrete class, which keeps the persistence layer free of business semantics.

On QueryParams, pagination is honored by findMany as a row cap without a COUNT(*); the count is deferred to findManyPaginated, the only method that needs the pagination metadata.

Where operators

ORM-agnostic filter shapes reused across every entity's Where type. The repository implementation translates them to its native query language, so adding an operator here makes it available to every entity at once. Business-specific predicates (theme-exists groups, trump-suit matching, language overlap) encode domain rules and do not belong here; a module defines them as named fields of its own Where.

A Where is a plain payload, not a value object. Its fields are independent and AND-combined; an unset field is no constraint. Field names mirror domain concepts, not database columns: for themes, ownerId maps to themes.user_id and slug to themes.identifier inside the implementation.

Criteria

Criteria<TWhere> is the abstract base of every module's filter-only query object, used by the write-path methods (update, delete) and by aggregate. Its constructor is protected and refuses an empty where: constructing a criteria with no field throws, so an unscoped update or delete cannot be expressed.

Subclasses add their entity-specific static constructors (byId, byIds, where, plus bySlug or byEmail style helpers) and an instance and(where) that returns a new instance.

Projections and aggregations

A projection separates what to search (the query object) from what to return. The repository signature is generic on the projection, so the call site infers the result type from the projection it passes and never annotates it.

A module exposes them through static factories on an XxxProjections and an XxxAggregations class whose constructors are private. An aggregation may carry a criteria to scope the rows it counts.

Pagination

PaginationMeta

paginationToLimitOffset({}); // => { limit: 20, offset: 0 }
paginationToLimitOffset({ page: 3, pageSize: 25 }); // => { limit: 25, offset: 50 }
paginationToLimitOffset({ offset: 40, pageSize: 10 }); // => { limit: 10, offset: 40 }

export function toResponse(
  items: BoardTheme[],
  pagination: PaginationMeta
): PaginatedResponse<BoardTheme, "themes"> {
  return { success: true, pagination, themes: items };
}

Repository ports

Two generic types give every module the same read and write surfaces. A module aliases them with its own query, criteria and patch types, and intersects the write side with its entity-specific insert primitives.

ReadRepository

ReadRepository<TQuery, TGetByIdOpts = undefined, TId extends string | number = string> is the query-side surface.

WriteRepository

WriteRepository<TCriteria, TPatch, TPersistableWrite, TDeleteOpts = undefined, TUpdateResult = { updatedIds: string[] }, TDeleteResult = { deletedIds: string[] }> is the command-side surface: the two filtered primitives every entity reuses verbatim. Insert primitives (writeOne, writeMany, overwriteMany) stay on the module's alias because their result shapes diverge, but their input type is part of this generic through TPersistableWrite.

ScopedDeal

One deal of an ordered deal list (a session, a collection), at its slot. Shared by the ports that read deal lists for telemetry.

How a module binds them: themes

The themes module, imported from @workspace/training/application/themes, declares one file per concern and exposes all of them from its barrel.

ThemeQuery

const params = ThemeQuery.empty()
  .withTypes("user_theme")
  .asViewer("user-1")
  .toParams();
// => { where: { type: { in: ["user_theme"] } }, viewerUserId: "user-1" }
export const whereOnly = params.where;

Reading through a ThemeReadRepository

The service builds the query and the projection and hands both to the port; the port's generic method infers the row type from the projection.

export function listUserThemesPage(
  themesRead: ThemeReadRepository,
  page: number,
  ctx: ServiceContext
): Promise<{ items: BoardTheme[]; pagination: PaginationMeta }> {
  const query = ThemeQuery.empty()
    .withTypes(["user_theme", "user_inspiration"])
    .asViewer(ctx.userId) // a viewer reads system themes and their own only
    .withSorting({ column: "createdAt", desc: true })
    .withPagination({ page, pageSize: 20 });

  return themesRead.findManyPaginated(query, ThemeProjections.entity());
  // the result type follows the projection: Projection<BoardTheme> => BoardTheme[]
}

export function findThemeIdsBySlugs(
  themesRead: ThemeReadRepository,
  slugs: readonly string[]
): Promise<ThemeIdsRow[]> {
  return themesRead.findMany(
    ThemeQuery.empty().bySlugs(slugs),
    ThemeProjections.ids() // minimal { id, slug } rows
  );
}

export function countThemesOf(
  themesRead: ThemeReadRepository,
  ownerId: string
): Promise<number> {
  return themesRead.aggregate(
    ThemeAggregations.count(ThemeCriteria.byOwner(ownerId))
  );
}

export function readThemeOrThrow(
  themesRead: ThemeReadRepository,
  themeId: string
): Promise<BoardTheme> {
  return themesRead.getById(themeId, ThemeProjections.entity()); // throws NotFoundError
}

Writing through a ThemeRepository

A criteria scopes every update and delete. ThemesService.update and remove go through ThemeCriteria.byId and treat an empty updatedIds as not found.

export async function renameTheme(
  themes: ThemeRepository,
  themeId: string,
  labels: LocalizedLabels
): Promise<string[]> {
  const { updatedIds } = await themes.update(ThemeCriteria.byId(themeId), {
    labels,
  });
  return updatedIds; // [] when no row matched
}

export function removeThemesOf(
  themes: ThemeRepository,
  ownerId: string,
  slugs: readonly string[]
): Promise<{ deletedIds: string[]; boardsAffected: number }> {
  const criteria = ThemeCriteria.byOwner(ownerId).and({ slug: { in: [...slugs] } });
  // criteria.toParams() => { where: { ownerId: { eq }, slug: { in } } }
  return themes.delete(criteria);
}

export function unscopedCriteriaThrows(): void {
  ThemeCriteria.where({}); // throws: refusing to construct an empty criteria
}

The same pattern elsewhere

Boards expose BoardQuery, BoardCriteria, BoardProjections, BoardAggregations, BoardWhere, BoardSort and alias BoardReadRepository = ReadRepository<BoardQuery, BoardGetByIdOptions>, with a delete that requires options. Editor boards expose EditorBoardQuery, EditorBoardCriteria, EditorBoardProjections, EditorBoardAggregations (with count and codeSet), EditorBoardWhere, EditorBoardSort, EditorBoardPatch, and their identity column is code rather than id. The public deals view is keyed by code too: DealQuery, DealProjections, DealWhere, DealSort and DealReadRepository = ReadRepository<DealQuery>, read side only. Their pages list each fluent API.

The canonical surface

A repository port exposes the ratified primitives and nothing else:

The list is a ratified surface, not a count. Adding a generic, parametric primitive to ReadRepository or WriteRepository is a doctrine change that every module inherits at once; a stream primitive for cursor reads is ratified in principle and will join ReadRepository the day a consumer needs it. Adding a business-named method to one port (findActiveByOwner, markAsPublished) is refused: the business name belongs one layer up, as a method of the service, which composes a query object, a criteria or a projection and calls a primitive.

update, updateMany and delete live on the generic type because every entity reuses them verbatim. The insert primitives stay declared on each module's alias, which picks the subset it needs: themes bind writeOne and writeMany, boards and editor boards the three. Their result shapes diverge ({ themeId }, { boardId }, void, reports of inserted and failed codes), while their input is the module's TPersistableWrite, so a write repository bound to one entity cannot receive another entity's rows.

Where the tree stands. Twenty-one modules bind the canonical aliases: themes, boards, editor boards, the public deals view, ads sponsorships, announcements, groups, students, sharing groups, deal collections, group sessions, accounts, bookmarks, votes, auction systems, the impersonation log, telemetry events, brand assets, billing customers, the plans catalogue (grids, plans and prices, three pairs), and subscriptions. Several pick only what they use: bookmarks and votes findMany, writeOne and delete, the auction systems catalogue findMany, the append-only impersonation log writeOne, telemetry events writeMany and a delete whose { limit } option is required, so the retention purge runs in bounded batches, brand assets findMany, update and writeMany, billing customers findMany, writeOne and update, the plans catalogue findMany on each read side, update on each write side and writeOne on plans and prices. Subscriptions bind the whole read side, runtime and admin reads in one; their write side is the ratified upsert alone. The other ports are ratified in Bespoke by design.

The usage module binds the same aliases for its read model: the append-only serves writeOne, the daily rollups and their execution trace aggregate (plus the trace's writeOne for a failed run), the perseverance snapshot findMany. The jobs that rebuild that read model and the multi-table usage reads are ratified bespoke.

Frontier: who builds a repository

Repositories are infrastructure. Only a composition root imports a repository factory: in apps/api, create-router.ts, router.ts, the request-context middlewares they mount (one per execution context, whose only job is to build that context's services on its connection) and the webhook handler registry; in apps/admin, lib/services.ts and lib/event-ingestion.ts; in apps/cli, src/lib/context.ts. Everything else, a route handler, a Server Action, any other middleware, a lifecycle script in packages/database/scripts, a CLI command, consumes a service from @workspace/training and never composes a Query, a Criteria or a Projection itself. A one-off data repair that carries no business rule, such as restamping a column from another table, is the one exception: it is a function of @workspace/database/maintenance, taking the connection, and a CLI command calls it through the context. An import type of a port to type a service dependency is fine; an import of a createDrizzleXxxRepository value outside a composition root is not.

Biome enforces the frontier: the root noRestrictedImports rule bans @workspace/database/repositories/**, the composition roots are overrides, and packages/database/scripts keeps the ban on its relative imports. No file is whitelisted by name: a new crossing is a new composition root, declared as one, or a service the caller lacks.

Two consequences:

• A composition root exposes services and ports, never a raw repository. The admin surface and the API context are typed as services.
• The business-named methods the old bespoke repositories carried come back one layer up, as service methods tested on the in-memory fakes. There is no separate tier of admin services: when a method must see every owner's rows, the viewer scoping is an explicit parameter of the service method, not a different service.

Where the tree stands. No API handler or middleware builds its own repository any more, and no API handler reads a connection: the whitelist is gone, its last database scripts now apps/cli commands. Neither the admin getServices() nor the API contexts expose a raw repository: every key is a service or a port such as identityProvider.

Usage shows the wiring in each app.

Junction tables

A many-to-many link (boards to themes, sessions to boards, sessions to collections, collections to boards, groups to students, sharing groups to members) is its own repository, named after both sides: BoardThemesRepository, GroupSessionBoardsRepository, SharingGroupMembersRepository. The owning entity's port never takes an update({ add, remove }) patch for its links and never grows a linkXxx method.

An ordered junction exposes the common verb replaceAll(parentId, orderedChildIds): the caller hands the whole intended list, the repository computes the diff. The transaction and any row lock stay inside the junction repository; the service never opens one.

Where the tree stands. Seven junction repositories exist. BoardThemesRepository offers findMany by board or by theme, writeMany, delete(criteria) and replaceAll(boardId, themeIds), with the lock and the transaction inside the Drizzle implementation; BoardUpdatePatch.themeIds still replaces a board's theme set through BoardRepository.update. GroupStudentsRepository (Groups) offers findMany by group or by student, writeMany and delete(criteria); it has no replaceAll, because nothing hands it a whole student set. SharingGroupMembersRepository (Sharing) offers findMany by group or by member, an aggregate count, writeMany as a set union and delete by criteria; it has no replaceAll, because members join and leave one at a time. DealCollectionBoardsRepository (Collections) is the first ordered junction: findMany by collection or by board, writeMany appending after each collection's last position, delete(criteria) and replaceAll(collectionId, orderedBoardIds), which locks the collection row, drops the boards that left and writes every position in one transaction. GroupSessionBoardsRepository and GroupSessionCollectionsRepository (Sessions) mirror it for a session's ad-hoc boards and its linked collections, each with replaceAll(sessionId, orderedIds) locking the session row; the collections link turns its unique index into ConflictError(COLLECTION_ALREADY_LINKED). AnnouncementReadsRepository (Announcements) holds an account's read marks and only writes: writeMany as a set union keeping each mark's first readAt; the marks are read by the announcements read repository's withReadMark() projection, a left join of the query's reader. No owning port carries a link method any more. The only other replaceAll is UsagePerseveranceWindowsRepository.replaceAll, a whole-set write on a snapshot table, which is the same verb for the same reason; it is ratified in Bespoke by design.

Public links

Sessions and collections share a public link: a code, an enabled flag, a public content payload. getPublicLink and updatePublicContent read and write the same columns for both, so the Drizzle layer implements them once, in a helper each repository binds to its table. There is no PublicLinksRepository<T>: the helper lives in the database package, the ports stay on their entity.

Where the tree stands. Both ports carry updatePublicContent, taking a PublicContentPatch from the sessions module; GroupSessionsReadRepository also carries getPublicLink, while a collection's link is read from its entity once its owner is checked. They are the ratified extras of the canonical aliases: getPublicLink on GroupSessionsReadRepository, updatePublicContent on DealCollectionsRepository and GroupSessionsRepository; the public page of a code is a canonical read on both, a publiclyVisible() query with the publicPage() projection. Their single SQL implementation is the database package's drizzle/public-links.ts, which the deal collections and group sessions repositories call with their own table.

Bespoke by design

A port keeps a non-canonical surface only when a primitive cannot express it. The criterion is one of:

• an atomic state transition in SQL: a conditional update whose predicate and write must be one statement, because a read-then-write would race;
• an atomic bounded counter: an increment that refuses past a cap, decided in the database;
• a multi-table read with no primitive equivalent: one result assembled from several tables where a projection over one table cannot do;
• an atomic snapshot replacement: a derived table whose rows for a key (a day, a period, the whole set) are computed from the raw sources and swapped in one transaction, so a reader never sees a partial snapshot. The computation's reads ride on the same port when they are temporal aggregations no other consumer shares.

The ratified exceptions:

The list is revisited only when another port meets a criterion, never because a canonical shape feels awkward: the awkwardness usually means a service method is missing.

A ranked or random pick is not one of the criteria. The public player's next-deal draw ranks the pool by level closeness, language and the presence of a review, then breaks ties with random(), and keeps one row. It is a findMany whose query carries a DealSort and a one-row window (DealQuery.nextPick), not a pickNext method on the port: the sort is a read concern like any other order, and the Drizzle implementation still ranks on the scalar columns of the board index before hydrating the chosen code from the deals view, so the plan of the draw is the plan of the former bespoke method. The fallback without exclusions is a second call, decided by DealService.pickNext.

A conditional update is not bespoke by itself. Detaching an account from its identity, or re-attaching the detached account that carries an email, is one UPDATE … WHERE whose predicate and write are a single statement, which update(criteria, patch) already is. The identity upsert is an update followed, when it matched nothing, by a writeOne, and the table's unique keys refuse the second of two racing inserts. Accounts therefore keep no named method. Billing customers follow the same shape: binding a subject is an update of its tombstoned binding, then, when it matched nothing, a writeOne that keeps a subject already bound, then a read of the binding that stands; a live binding wins, so the loser of a race has nothing to retry.

Where the tree stands. No port is pending: every non-canonical port is ratified in the table above. The unit-of-work ports (BrandAssetsUnitOfWork, DiffusionDebitUnitOfWork, SubscriptionReconciliationUnitOfWork) are transaction scopes, not repositories, and stay as they are.

Lockstep: contracts and fakes

Every change to a port's contract, a primitive's signature, a projection's result or a criteria's where shape, migrates in the same pull request:

• the in-memory fakes of Testing (@workspace/training/testing), which carry the database's semantics for the consumers' tests;
• the API test application in apps/api/__tests__/helpers/test-app.ts, which wires the services the integration tests run against.

The integration tests of the API are the oracle of a repository refactor: their assertions do not change when a port moves to the canonical surface. The admin's unit tests of its actions and parsers play the same role on its side.