Spec 0008 — SQL projection extension

Status: superseded in scope by spec 0022 (2026-10-04). Persistence ports and adapter conformance replace this proposed codec-only projection. The boundary remains: no generated SQL, migrations, CRUD repositories, or transactions. This research does not authorize a mono-web migration, a SQL rewrite, or a production action.

Decision and evidenceCopy Anchor Link

Add a codec-only SqlProjection extension to the existing @PersistentModel syntax. It can emit opt-in typed SqlSchema wrappers for named, importable request/result schemas. It must not emit SQL statements, migrations, a CRUD repository, transactions, authorization, or domain commands. The SQL adapter still owns joins, predicates, ordering, locks, revisions, receipt writes, and failure translation. If this boundary cannot replace maintained code without changing behavior, omit the generated wrapper rather than create a second abstraction.

The source snapshot is mono-web f433ea906c9f9af5134c7316d54c78d8c859038f in mw (clean). The Profile baseline is 18 files / 2,355 physical lines, including profile/elements.ts (docs/research/profile-baseline.md; spec 0004 §4–5). This proposal follows rank 5 of spec 0004 §3. Source locations below are relative to that mono-web worktree unless prefixed with effx/.

The installed mono-web package is Effect 4.0.0-rc.116. Its SQL example imports Model from effect/unstable/schema and SqlSchema/SqlModel from effect/unstable/sql (node_modules/effect/ai-docs/src/40_sql/10_basics.ts:9-11,18-46,96-110). The files are node_modules/effect/src/unstable/schema/Model.ts, src/unstable/sql/SqlSchema.ts, and src/unstable/sql/SqlModel.ts; no src/unstable/sql/Model.ts exists. The installed effx Effect version is 4.0.0, whose corresponding imports are effect/schema and effect/sql (effx/node_modules/effect/src/{schema/Model,sql/SqlSchema,sql/index}.ts). Generator imports must follow the target application's installed version, not copy mono-web's rc import paths.

Model.Class has six variants: select (the class itself), insert, update, json, jsonCreate, and jsonUpdate (rc source node_modules/effect/src/unstable/schema/Model.ts:27-38). SqlSchema.findAll, findNonEmpty, findOne, findOneOption, and void encode the request, call a supplied execute, then decode the result. findOne takes the first row and fails on zero rows; it does not enforce exactly one. There is no SqlSchema.single in rc.116 (node_modules/effect/src/unstable/sql/SqlSchema.ts:20-172). SqlModel.makeRepository generates INSERT/UPDATE/SELECT/DELETE statements and fixed CRUD behavior, so it is outside this extension (node_modules/effect/src/unstable/sql/SqlModel.ts:24-100,128-216).

Census: 39 constructor calls, nine filesCopy Anchor Link

This is a syntactic count of SqlSchema.findAll/findOneOption/void constructors under packages/database/src: 29 / 9 / 1 = 39. It is not a count of SQL statements, services, or removable lines. Classification means:

• A (3): a complete existing Effect Model variant plus its table identifies the row codec. The adapter still owns the SQL statement.
• B (33): the adapter must own its statement, but the request/result codecs can reference existing schemas or named projections. An inline schema needs an importable owner before generation; moving it is not a line saving.
• C (3): an operation-dependent or deliberately unknown payload has bespoke decoding/semantics. A generic row codec would erase the decision point.

In particular, the receipt page uses a cursor and a separately decoded settlement (receipt/projections.ts:121-231). The team-application outbox and command receipt retain Schema.Unknown until delivery or operation-dependent replay (team-application/{outbox,postgres}.ts:155-172,242-288). These three C cases must stay outside generated wrappers. Even the three A statements retain scoped predicates, ordering, and locks (organization/mailing-lists-postgres.ts:76-91, schools/administration.ts:64-90).

Source contractCopy Anchor Link

Extend the existing @PersistentModel options (and Model.persistent(schema, options) builder) with these fields. This is source-expression notation: Symbol and Schema mean importable values, not TypeScript types.

{
  table: string;                   // existing; identity/provenance, not a SQL template
  primaryKey: readonly string[];   // model field names, including composite keys
  variantsUsed: readonly ("select" | "insert" | "update" |
    "json" | "jsonCreate" | "jsonUpdate")[];
  projections: Readonly<Record<string, {
    statement?: Symbol;           // exported statement callback, never SQL text
    request: Schema;              // exported schema or an existing Model variant
    result: Schema;               // exported schema or an existing Model variant
    cardinality: "all" | "nonEmpty" | "one" | "oneOption" | "void";
  }>>;
}

schema is already supplied by the decorated class or the builder's first argument (effx/packages/runtime/src/{Annotation,builder,decorators}.ts; effx/packages/compiler/src/extensions/core.ts:301-357). The table field remains the one source of table identity. primaryKey names fields of the model's select variant. variantsUsed limits which existing Model variants a named projection can reference. Neither field licenses the generator to infer an INSERT, UPDATE, SELECT, or PATCH statement. cardinality is explicit because SqlSchema has distinct empty-result behavior. For void, result is Schema.Void and no result decoder runs. A statement symbol must be importable and supply the callback that SqlSchema expects. Inline lambdas, closure values, and raw SQL text are rejected.

For Profile, put the persistence declaration in a database-side builder module that imports the domain models. Do not put database projection/statement imports on PersonProfile or PersonContactProfile in packages/domain/src/profile/schema.ts: that would invert mono-web's domain → database dependency (packages/domain/src/profile/schema.ts:99-169, mono-web AGENTS.md#boundary-practices). The builder and class decorator must lower to the same IR. A statement imported by generated sql.ts cannot itself import sql.ts. Keep the author-owned statement module separate from the caller. Transaction-bound code passes a callback bound to its current DatabaseOperations instead of letting generated code start or discover a transaction.

ProfileLive continues to provide Database and Organization explicitly. Generated codecs cannot replace that Layer (packages/database/src/profile/postgres-layer.ts:1-42).

IR and validationCopy Anchor Link

Use the existing IR Extension node, not a second Model definition: { _tag: "Extension", extension: "sql-projection", tag: "SqlProjection", id: "ext:sql-projection/<model>", data }. Its single ExtensionOf edge points from the extension to its owning Model, with qualifier SqlProjection. Retain the existing Model node, PersistsAs, ViewOf, and Focus meanings (effx/packages/ir/src/{Node,Edge}.ts, effx/packages/compiler/src/extensions/core.ts:319-355).

The JSON-shaped data contains table, primaryKey, sorted variantsUsed, and sorted projection names. Each projection contains its explicit cardinality, request/result as SchemaRef or a { model: StableId, variant } selector, and optional statement as SymbolRef. It contains no TypeScript ts.* object, runtime Schema, function, or SQL text (effx/packages/ir/src/Refs.ts:4-20, effx/packages/compiler/src/Collected.ts:9-17, ADR 0004). References to static Model.insert or Model.json resolve to the imported model plus that variant, not to a second hand-written schema. Add graph-visible Schema nodes for referenced schema IDs. Validate one owner model, unique projection names, model-field primary keys, supported variants/cardinalities, and importable references. The generated TypeScript check enforces statement callback signatures: the current IR has only SymbolRef, not arbitrary function types. Reject malformed or ambiguous declarations before emission. Do not guess cardinality, column spelling, or SQL behavior.

Generated sql.tsCopy Anchor Link

For each named eligible projection, emit an ordinary typed factory around the matching SqlSchema.findAll/findNonEmpty/findOne/findOneOption/void constructor. It imports the declared request/result schema or the existing Model variant. It binds either an explicitly exported statement callback or an adapter-supplied execute(encodedRequest) callback. This preserves Req["Type"] input, encoded SQL parameters, typed result, SchemaError, statement error E, and required services R from the installed constructor (node_modules/effect/src/unstable/sql/SqlSchema.ts:33-49,65-105,115-172). Emit no query when no named projection is declared.

Resolve SQL and Model import paths against the target Effect version. If the target lacks these modules, report a diagnostic instead of emitting rc.116 imports blindly.

The generated module must contain no SQL literal, table interpolation, joins, row lock, migration, SqlModel.makeRepository, Effect.run*, or new database/service Layer. It does not translate domain errors. The adapter retains those conversions, absence semantics, transaction timing, record revisions, receipts, and all hand-written statements. In particular, Model.update is a schema variant, never a partial HTTP PATCH or an automatic business command (packages/domain/src/profile/schema.ts:100-163, mono-web AGENTS.md#boundary-practices).

Profile source-line decisionCopy Anchor Link

packages/database/src/profile/postgres.ts has 727 physical lines and zero SqlSchema.* constructors. Nine typed raw sql<T> calls start at lines 76, 95, 247, 335, 358, 378, 399, 434 and 682. One untyped receipt INSERT occupies lines 517–544. This extension can safely remove 0 existing lines now. It only wraps supplied SQL with SqlSchema decoding, while Profile already uses Model variants directly for rows. Table and primary-key metadata cannot remove statements, transactions, or bespoke projections.

Measured conditional candidates are 52 nonblank lines, not a reduction claim:

Plain SqlSchema does not preserve Profile's strict decoding. Its result decode calls pass no parse option (node_modules/effect/src/unstable/sql/SqlSchema.ts:33-48,115-166), while Profile passes { onExcessProperty: "error" } (profile/postgres.ts:48-70). The installed decoder defaults to "ignore" and strips extra keys (node_modules/effect/src/SchemaAST.ts:455-465). Do not delete the two strict decoders unless a later design preserves this behavior and its typed failure mapping. Likewise, moving three interfaces into hand-written annotations is relocation, not code removal. The six decode-call lines 85, 104, 368, 388, 421 and 456 need rewrites rather than deletion. Joined snapshot/HTTP revision, receipt replay, two revision fences, directory pagination, and lock order remain in their adapter (profile/postgres.ts:243-301,331-462,509-727).

Falsifiers before freezing or implementingCopy Anchor Link

1. IR equivalence: equivalent decorator and builder declarations produce byte-identical normalized IR/semantic hashes. Unresolved or duplicate model/projection references produce diagnostics, never best-effort output.
2. Codec fidelity: round-trip the declared request/result variants through the generated wrapper. Reject extra Profile row keys as today. Preserve branded IDs, null contact rows, Schema.Unknown receipt payload timing, and typed decode failures. A default permissive SqlSchema result decoder fails this check for current Profile reads.
3. Ownership: generated code cannot open a transaction or import a concrete database Layer. SQL and its row locks remain hand-written. The generated file must not contain SQL string/template text or a migration.
4. Cardinality and error channel: zero rows differ correctly for all, one, and oneOption; one must not pretend to enforce exactly-one. Statement errors and service requirements remain visible in the type of each generated function.
5. Migration parity: the unchanged just e2e profile browser/PostgreSQL journey and focused Profile HTTP/database tests pass against any later integrated output. Recheck strict malformed rows, stale revisions, replay, and actor confinement at the exact migration revision. Gate 0's earlier mono-web pass does not prove this generated path (docs/research/profile-baseline.md).
6. Measured leverage: after replacing a real caller, count maintained source (including handwritten adapter declarations) and generated source separately against the 2,355-line Profile baseline. If Profile still safely removes zero lines, do not claim SQL-extension code reduction or use this extension as Profile Gate 3 proof.

No effx implementation or mono-web source change is part of this proposed spec. Research used file inspection only; no build, lint, tests, PostgreSQL execution, or browser run was done for this proposal.