Architectural Patterns

This document catalogues the recurring design patterns used in db-semantic-planner. Each entry covers intent, structure, a concrete example, naming conventions, and decision criteria for applying the pattern to new code.

---

Table of Contents

1. Handler Dispatch
2. Immutable Fluent Builder
3. Visitor Dispatcher
4. Strategy Pattern — Include Strategies
5. Ports and Adapters
6. 3-Layer AST Pipeline
7. CompilerFns Coordinator
8. MutationBuilderBase Template Method

---

1. Handler Dispatch

Intent

Decouple the SQL compiler from the logic of each individual WHERE operator or expression kind. A dispatch key belongs to a handler object; the compiler looks up and dispatches to the correct handler at compile time. Adding a WHERE operator or expression type normally does not touch the compiler core; INCLUDE strategies are a closed collection.

Structure

Role	File
Handler interfaces	packages/adapter-pgsql/src/handlers/types.ts
Registry + lookup	packages/adapter-pgsql/src/handlers/index.ts
WHERE handlers	packages/adapter-pgsql/src/handlers/where/
Expression handlers	packages/adapter-pgsql/src/handlers/expression/
Include handlers	packages/adapter-pgsql/src/handlers/include/

Three independent registries exist, one per handler family:

WhereHandler      { readonly operators: readonly string[]; compile(decision, ctx, state, dispatch): Node }
ExpressionHandler { readonly types: readonly string[]; compile(decision, ctx, state): Node }
IncludeHandler    { readonly strategy: IncludeHandlerStrategy; compile(decision, ctx, state): IncludeResult }

Registration:

// doctest: skip — API signature reference (TypeScript function signatures, not executable code)
registerWhereHandler(handler: WhereHandler): void   // throws on duplicate
registerExpressionHandler(handler: ExpressionHandler): void

INCLUDE is not an extension surface: strategies outside INCLUDE_STRATEGIES are invalid, and the initializer already installs every allowed strategy. Extend INCLUDE by changing INCLUDE_STRATEGIES and allIncludeHandlers together.

Lookup (throws if missing):

// doctest: skip — API signature reference (TypeScript function signatures, not executable code)
getWhereHandler(operator: string): WhereHandler
getExpressionHandler(type: string): ExpressionHandler
getIncludeHandler(strategy: IncludeHandlerStrategy): IncludeHandler

Example

Adding a WHERE handler for a new, unregistered operator:

// doctest: skip — illustrative fragment (registerWhereHandler is an internal adapter-pgsql API, not in doctest preamble)
// packages/adapter-pgsql/src/handlers/where/pattern.ts
const matchesHandler: WhereHandler = {
  operators: ['matches'],
  compile(decision, ctx, state, dispatch) {
    // return PostgreSQL AST node
  },
};

// packages/adapter-pgsql/src/handlers/where/index.ts
export const simpleWhereHandlers = Object.freeze([
  // existing handlers
  matchesHandler,
]);

The central WHERE family initializer in handlers/index.ts installs allWhereHandlers lazily. The WHERE dispatcher resolves aliases, then calls getWhereHandler(operator).compile(...) — no switch statements.

Convention

• Handler files live in handlers/{where,expression,include}/
• Handler modules export handler objects, not classes
• Each sub-directory index.ts exports its top-level all*Handlers collection frozen; the matching family initializer in handlers/index.ts installs it lazily
• Direct bulk registration was removed so the central initializer can install each family transactionally and recover from failures
• Handler interfaces are in handlers/types.ts — never inline types in handler files
• WHERE dispatch calls ensureHandlersRegistered() before lookup; expression and INCLUDE compilation call their matching ensure*Registered() functions before lookup

When to use

Use this pattern for WHERE operators and expression types expected to grow over time. INCLUDE strategies use the same dispatch shape but are deliberately closed; add one only by updating both INCLUDE_STRATEGIES and allIncludeHandlers. Do NOT use a switch statement in the compiler itself.

---

2. Immutable Fluent Builder

Intent

Provide a chainable, type-safe API for constructing query and mutation intents without mutating shared state. Every method call returns a new builder instance with updated state — the original is unchanged. The builder materializes to SQL or execution only through its terminal methods: dump() compiles without executing, and execution methods such as all() or execute() run the compiled statement.

Structure

Each builder's exact set of terminal methods is part of its public type; read it there:

Builder	Public type
Queries	QueryBuilder — packages/core/src/dx/query-builder-types.ts (implemented by the internal QueryBuilderImpl)
Set operations	SetOperationBuilder — packages/core/src/dx/set-operation-builder.ts
Mutations	InsertBuilder, UpdateBuilder, DeleteBuilder, UpsertBuilder — packages/core/src/dx/mutation-builders.ts
CTEs	CteQueryBuilder — packages/core/src/dx/cte-builder.ts

Invariant enforced by buildIntent(): the builder collects state into an Intent object only when materialization is requested. State is stored as readonly fields; each method spreads the current baseOpts into a new constructor call.

Example

// Each step produces a new builder — nothing mutated
const result = orm
  .select('posts')
  .where(eq('published', false))
  .orderBy('title', 'desc')
  .limit(20)
  .dump();

// InsertBuilder — same pattern
const inserted = await orm
  .insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .returning(['id', 'createdAt'])
  .dump();

Convention

• Every fluent method returns this constructor type (not this) — enables subclass chaining
• Builder state is passed through constructor options (MutationBaseOpts, etc.), never via property mutation
• MutationBuilderBase declares buildIntent() as protected abstract; QueryBuilderImpl.buildIntent() is internal/package-visible so CteQueryBuilder can read query intent without executing
• dump() is always available without an adapter; execution methods require an adapter

When to use

Use for any user-facing API that constructs a compound declarative artifact (a query, mutation, CTE, set operation). Do not accumulate state in a mutable object that users pass around.

---

3. Visitor Dispatcher

Intent

Keep the Chevrotain CST visitor class as a thin dispatcher — one method per grammar rule, each delegating immediately to a domain module. All real logic lives in visit-*.ts files, not in the visitor class. This makes each domain independently testable and prevents the visitor from becoming a 2000-line monolith.

Structure

Role	Location
Dispatcher class	packages/nql/src/semantic/visitor.ts — NqlCstVisitor
Domain modules	packages/nql/src/semantic/visit-query.ts, visit-expression.ts, visit-literal.ts, visit-mutation.ts, visit-cte.ts, etc.
Public entry point	cstToAst(cst): { ast: NqlProgram; warnings: NqlWarning[] }

NqlCstVisitor holds a single VisitFn (this.v = (node) => this.visit(node)) and passes it to every domain function so they can recurse without importing the visitor class.

// doctest: skip — illustrative source code fragment (project-internal types BaseCstVisitor/VisitFn/CstContext not in doctest preamble)
// visitor.ts — thin dispatcher
export class NqlCstVisitor extends BaseCstVisitor {
  private readonly v: VisitFn;

  query(ctx: CstContext) {
    return visitQuery(ctx, this.v);   // delegate immediately
  }

  whereClause(ctx: CstContext) {
    return visitWhereClause(ctx, this.v);
  }
  // ... one line per grammar rule
}

// doctest: skip — illustrative source code fragment (project-internal types CstContext/VisitFn/NqlQuery not in doctest preamble)
// visit-query.ts — domain logic
export function visitQuery(ctx: CstContext, visit: VisitFn): NqlQuery {
  // real implementation here
}

Example

Adding a new grammar rule havingClause:

1. Add havingClause(ctx) { return visitHavingClause(ctx, this.v); } to NqlCstVisitor
2. Create visit-having.ts with export function visitHavingClause(ctx, visit): NqlHaving { ... }
3. Import and call from visitor.ts — no other changes

Note: Chevrotain requires a stub method even for rules handled inside another visitor method (anySuffix stub in visitor.ts is a documented example of this).

Convention

• One visit-*.ts file per domain (query structure, expression, literal, mutation, CTE)
• Every exported function signature: visitXxx(ctx: CstContext, visit: VisitFn, ...extras): NqlXxx
• Warnings are passed as an extra parameter when a visitor may emit them (e.g., visitJsonAccessExpr)
• The NqlCstVisitor singleton is created once at module load: export const nqlVisitor = new NqlCstVisitor()

When to use

Use when a grammar-driven visitor would exceed ~50 lines of real logic. Add a new visit-*.ts module rather than adding logic to visitor.ts. The visitor file must remain a pure dispatcher.

---

4. Strategy Pattern — Include Strategies

Intent

The planner must choose how to fetch related data for each IncludeIntent without the core logic knowing about PostgreSQL-specific capabilities. The strategy is an enum value on the IncludeIntent; the adapter dispatches to the matching IncludeHandler at compile time.

Structure

Role	Location
Strategy type	packages/types/src/model-ir.ts — IncludeStrategy
Strategy selection and capability validation	packages/core/src/planner.ts — determineFlatIncludeStrategy(), determineIncludeStrategy() and selectNestedOutputStrategy(); private validateStrategy() shares membership/capability validation, validateIncludeStrategy() accepts planner input including auto, and validateResolvedIncludeStrategy() validates resolved adapter decisions
Handler dispatch	packages/adapter-pgsql/src/handlers/index.ts — getIncludeHandler()
Concrete handlers	packages/adapter-pgsql/src/handlers/include/cte.ts, join.ts, json-agg.ts, lateral.ts (shared.ts supplies shared utilities)
Dialect capabilities	packages/types/src/dialects.ts — DialectCapabilities

Strategies:

Value	SQL form	Best for
'join'	LEFT JOIN	to-one relations (belongsTo, hasOne)
'lateral'	CROSS JOIN LATERAL	to-many with LIMIT
'json_agg'	JSON subquery aggregation	to-many, no row explosion (PostgreSQL default)
'cte'	WITH RECURSIVE	recursive / hierarchical trees
'auto'	planner decides	default — resolved before compilation

Example

Runtime include planning:

const plan = orm.select('users').include('posts', { limit: 10 }).plan();
// The planner records the concrete include strategy in plan.decisions.

Auto-resolution (planner, planner.ts):

recursive → 'cte' (requires supportsRecursiveCTE; explicit join refused; default ignored)
non-recursive → explicit include.join → relation hint → applicable default → shape selection
flat shape (determineFlatIncludeStrategy) → direct/nested per-parent limit ? 'lateral' : 'join'
nested shape (selectNestedOutputStrategy) → supportsJsonAgg ? 'json_agg' : 'join'

Flat output runs through determineFlatIncludeStrategy(); nested output runs through selectNestedOutputStrategy() after authority resolution. selectNestedOutputStrategy() does not inspect relation cardinality, so to-one and to-many relations share the nested selection. For every non-recursive include, explicit include.join takes precedence, then the relation includeStrategy hint, then an applicable defaultIncludeStrategy, then shape selection; explicit join conflicts with concrete hints other than join (json_agg, lateral, cte) and is refused, while a plan-level default only fills the gap.

Convention

• 'auto' is always resolved to a concrete strategy before PlanReport is emitted — it never reaches the compiler
• The adapter dispatch keys are join, lateral, json_agg, and cte
• INCLUDE dispatch is closed: a new dispatchable handler strategy requires coordinated updates to INCLUDE_STRATEGIES and allIncludeHandlers.
• Query-level include options such as join and limit influence strategy resolution; the planner validates the resolved strategy against DialectCapabilities

When to use

Use the strategy pattern for any decision that has multiple valid implementations and where the choice is delegated to the planner (core) while the implementation lives in the adapter. The planner may inspect strategy values to select and validate them against dialect capabilities; SQL emission belongs in adapter handlers.

---

5. Ports and Adapters

Intent

Keep packages/core completely database-agnostic. The core defines the Adapter port (interface); adapter packages implement it. This means the entire query planning and DX layer can be unit-tested without a database, and alternative adapters (MySQL, SQLite) can be added without touching core.

Structure

packages/types/src/adapter.ts      ← Port: Adapter interface hierarchy
packages/core/src/dx/              ← Uses Adapter, never imports adapter-pgsql
packages/adapter-pgsql/src/        ← Implements Adapter<DB>

Adapter interface hierarchy (composition via extends; each interface's members are declared in packages/types/src/adapter.ts):

BaseAdapter
  └─ CompilingAdapter
  └─ ExecutingAdapter
  └─ StreamingAdapter
  └─ IntrospectingAdapter
  └─ TransactionalAdapter
  └─ RawSqlAdapter

TableDDLGeneratorAdapter (does not extend BaseAdapter)

Adapter<DB> extends the BaseAdapter-derived interfaces and TableDDLGeneratorAdapter
  ← full capability union

Injection:

const orm = createOrm({
  model: schema,
  adapter: createPgAdapter(pool),   // <-- inject at edge
});

Example

Testing core logic without a database:

// doctest: skip — illustrative pseudo-code fragment (createMockAdapter from test-utils.ts and vi.fn() are not in doctest preamble)
// core unit tests use createMockAdapter() from test-utils.ts
// which implements only the Adapter methods needed for the test
const adapter = createMockAdapter({ compile: vi.fn().mockReturnValue(...) });
const orm = createOrm({ model: schema, adapter });

Convention

• packages/core/src/** MUST NOT contain any import from packages/adapter-pgsql
• Adapter interface is the only coupling point — core never calls PgAdapter directly
• connectionless mode: createPgCompileOnlyAdapter() — no Pool required for CLI/tooling, planning, compilation, and dumps; database operations refuse at runtime
• Schema-scoping: adapter.withSchema(name) returns a new scoped adapter — core calls this, adapter implements it

When to use

Any new capability that requires database interaction must be expressed as a method on one of the *Adapter interfaces in packages/types/src/adapter.ts, then implemented in adapter-pgsql. Core code calls the interface method — it never calls pg directly.

---

6. 3-Layer AST Pipeline

Intent

Separate concerns across three distinct AST representations for queries, each adding a layer of resolution. NQL input passes through the NQL AST and IntentAST before planning and compilation; the fluent query builder emits IntentAST directly. Fluent mutation builders also emit IntentAST directly, then compile it through the adapter without a planner layer.

Structure

Layer 0: User input
         NQL string  ──or──  fluent builder API
              │                        │
              ▼                        ▼
Layer 1: NQL AST (packages/nql/src/parser/ast.ts)
         NqlProgram → NqlQuery → NqlExpression → ...
         (concrete syntax, close to what the user wrote)
              │
              ▼  NqlCompiler (compile-query.ts, compile-expression.ts, ...)
Layer 2: IntentAST (packages/types/src/intent/)
         QueryIntent / MutationIntent / IncludeIntent / WhereIntent / SelectIntent
         (semantic intent, DB-agnostic)
              │
              ▼  Planner (packages/core/src/planner.ts)
Layer 3: PlanReport (packages/types/src/planner.ts)
         { decisions: PlanDecision[], warnings: PlanWarning[], intent: QueryIntent }
         (resolved decisions with strategy choices and reasoning)
              │
              ▼  Adapter compiler (packages/adapter-pgsql/src/compiler.ts)
Layer 4: SQL + Parameters
         { sql: string, parameters: unknown[] }

Layer 1 is produced only when NQL is used. The fluent builder API emits Layer 2 directly.

Key types per layer

Layer	Key type	Package
NQL AST	NqlProgram, NqlQuery, NqlExpression	@dbsp/nql
IntentAST	QueryIntent, MutationIntent, IncludeIntent	@dbsp/types
PlanReport	PlanReport, PlanDecision, PlanWarning	@dbsp/types
Compiled	CompiledQuery	@dbsp/types

Example

Observability — query dump() exposes the compiled output and, when present, the plan:

const { plan, sql, params } = await orm.select('users').where(eq('active', true)).dump();
// plan.decisions — every strategy choice with reasoning
// sql            — final parameterized SQL
// params         — bound parameter values

Dump carries { plan?, sql, params, meta?, sequence? }; it does not carry the NQL AST or IntentAST. MutationDump carries { sql, parameters, intent, meta?, sequence? } and has no plan field.

Convention

• IntentAST is the canonical exchange format between core and adapter — never pass NQL AST to the adapter
• PlanReport.intent preserves the original QueryIntent for debugging
• PlanDecision records: type, choice, context, reasoning, alternatives[] — never omit reasoning
• Mutations bypass the planner (no PlanReport) — they go IntentAST → adapter compiler directly

When to use

When adding a new query feature: define the *Intent type in packages/types first, then implement the builder method (Layer 2), the planner decision (Layer 3), and the compiler handler (Layer 4) independently. Tests can be written for each layer without wiring the whole pipeline.

---

7. CompilerFns Coordinator

Intent

Break circular import cycles between NQL compiler modules (compile-query ↔ compile-select ↔ compile-expression) by passing function references explicitly rather than importing them. The NqlCompiler class acts as the wiring point — it constructs the CompilerFns bag and passes it down to every domain module.

Structure

Role	File
Interface	packages/nql/src/compiler/types.ts — CompilerFns
Wiring (coordinator)	packages/nql/src/compiler/index.ts — NqlCompiler constructor
Consumers	compile-query.ts, compile-select.ts, compile-expression.ts, compile-mutation.ts, compile-cte.ts

// doctest: skip — illustrative source code fragment (project-internal types NqlQuery/CompilerContext/QueryIntent/SetOperationIntent not in doctest preamble)
// types.ts
export interface CompilerFns {
  compileQuery(query: NqlQuery, ctx: CompilerContext): QueryIntent | SetOperationIntent;
  compileSelectClause(clause: NqlSelectClause, ctx: CompilerContext, fns: CompilerFns): SelectIntent;
  compileExpression(expr: NqlExpression, ctx: CompilerContext, fns: CompilerFns, ...): WhereIntent;
}

// doctest: skip — illustrative source code fragment (constructor body referencing `this` outside a class context)
// NqlCompiler constructor — wires the functions once
this.fns = {
  compileQuery:       (query, ctx)        => compileQuery(query, ctx, this.fns),
  compileSelectClause:(clause, ctx, fns)  => compileSelectClause(clause, ctx, fns),
  compileExpression:  (...args)           => compileExpression(...args),
};

Every domain function receives fns as a parameter and calls fns.compileQuery(...) rather than importing compileQuery directly.

Example

A new compiler module that needs to recursively compile sub-expressions:

// doctest: skip — illustrative source code fragment (project-internal types NqlWindowClause/CompilerContext/CompilerFns/WindowIntent not in doctest preamble)
// compile-window.ts
export function compileWindowClause(
  clause: NqlWindowClause,
  ctx: CompilerContext,
  fns: CompilerFns,       // <-- receive, don't import
): WindowIntent {
  const partitionExprs = clause.partition.map(e => fns.compileExpression(e, ctx, fns));
  // ...
}

Convention

• CompilerFns contains only the three cross-cutting functions that cause circular dependencies
• Every new compiler module that needs recursion must accept fns: CompilerFns as a parameter — never import sibling compiler modules directly
• NqlCompiler is the single wiring point; createCompiler() is its factory
• CompilerContext (immutable) and CompilerFns are always passed together

When to use

Use this pattern whenever two or more modules in the same compiler layer need to call each other. Passing function references through CompilerFns instead of direct imports eliminates the circular dependency without resorting to dynamic require() or lazy imports.

---

8. MutationBuilderBase Template Method

Intent

Share the dump() and execute() terminal methods across all four mutation builders (InsertBuilder, UpdateBuilder, DeleteBuilder, UpsertBuilder) while allowing each subclass to define its own intent construction and compilation logic. The abstract base provides the skeleton; subclasses fill in the variable steps.

Structure

Role	Location
Abstract base	packages/core/src/dx/mutation-builders.ts — MutationBuilderBase<T, TIntent>
Concrete builders	InsertBuilder, UpdateBuilder, DeleteBuilder, UpsertBuilder in the same file
Dump type	MutationDump interface

Template method skeleton (on MutationBuilderBase):

dump()      → buildIntent() → compileIntent(adapter, intent, options) → MutationDump
execute()   → buildIntent() → compileIntent(adapter, intent, options) → adapter.execute(...)

Subclasses implement the two variable steps, buildIntent() and compileIntent(); the base supplies dump(), which compiles without executing and returns a MutationDump, and execute(), which compiles and then executes through the adapter. Their exact signatures are declared on MutationBuilderBase.

Example

InsertBuilder overrides the two abstract methods:

// doctest: skip — illustrative source code fragment (class method body with TypeScript types, not standalone executable code)
protected buildIntent(): InsertIntent {
  if (this.valuesData.length === 0) {
    throw new InvalidOperationError('insert', 'No values provided for insert');
  }
  return { type: 'insert', table: this.table, values: this.valuesData };
}

protected compileIntent(adapter: Adapter, intent: InsertIntent, options?: CompileOptions): CompiledQuery {
  return compileMutationIntent(adapter, intent, options);
}

Adding a new mutation type (e.g., MergeBuilder) requires only implementing buildIntent() and compileIntent() — dump() and execute() are inherited.

Convention

• All state for a mutation builder is stored in readonly properties set via the constructor
• baseOpts (spread pattern) is used to propagate existing state into new instances from fluent methods
• Every subclass's compileIntent calls shared compileMutationIntent(adapter, intent, options), which dispatches by intent.type to the corresponding adapter.compileXxx() method
• MutationDump is the common return type for all dump() calls — { sql, parameters, intent, meta?, sequence? }
• Mutations do NOT go through the planner — there is no PlanReport for mutations

When to use

Use MutationBuilderBase whenever adding a new DML operation type that follows the build → compile → execute lifecycle. Do not duplicate dump() / execute() logic in a standalone builder class.

---

Pattern Interaction Map

User NQL string
  │  Visitor Dispatcher (Pattern 3) — thin dispatch to visit-*.ts modules
  ▼
NQL AST
  │  CompilerFns Coordinator (Pattern 7) — wires compile-query/select/expression
  ▼
IntentAST
  │  Strategy Pattern (Pattern 4) — planner selects IncludeStrategy
  ▼
PlanReport
  │  Ports and Adapters (Pattern 5) — Adapter interface, not pg directly
  │  Handler Dispatch (Pattern 1) — operator/kind → handler.compile()
  ▼
SQL + Parameters

User fluent API
  │  Immutable Fluent Builder (Pattern 2) — select/where/orderBy chain
  ▼
Query IntentAST → (same pipeline from IntentAST above)

User fluent mutation API
  │  MutationBuilderBase (Pattern 8) — insert/update/delete/upsert template
  ▼
MutationIntentAST
  │  compileMutationIntent() → adapter.compileInsert/compileInsertFrom/
  │  compileUpdate/compileBatchUpdate/compileDelete/compileUpsert/compileUpsertFrom
  ▼
SQL + Parameters

All layers: 3-Layer AST Pipeline (Pattern 6) governs how layers relate