Architecture: unify orm + sql into a tiered, RPC-ready data layer

[gabeklein]

Goal

Ship one coherent data layer as a pillar of the Expressive framework: the "just works" database for an expressive/dev app, hard-agnostic at the base, with a smooth opt-in gradient from simple CRUD up to synthesized, type-safe SQL. Users (and agents) should never need to leave TypeScript to talk to the database, and mistakes should fail loud and early - at model definition or query build time, not at runtime in production.

Today the repo contains two overlapping generations:

• packages/sql (+ sqlite/postgres) - the mature line: Query factory (Query(where => ...)), Builder/Generator SQL synthesis, dialect adapters, schema DDL + validation. Deep test coverage.
• packages/orm - the newer entity layer (published as @expressive/orm@0.12.1, proven in the IFCP client app): functional field factories, active-record instances, identity map, lazy relations, and a deliberately tiny 4-method Type.Connection port (get/insert/update/remove with [field, op, value] constraint tuples). No SQL anywhere in the package - storage-agnostic by design.

They are not competitors; they solve different layers. This plan unifies them.

Architecture

One model definition

orm's model surface wins (proven in production): class X extends Type with functional fields (str, num, bool, date, json, uuid, one, get), variadic config (orNull, optional, { column }), async converters. The Query factory is re-pointed to consume orm's Type.fields reflection instead of sql's parallel Table/Field class hierarchy. One import graph for models; both layers read the same metadata.

Tiered Connection - pay for what you need

• Tier 1 (CRUD port): the existing 4-method interface. Serializable by construction, implementable by anything - Postgres, SQLite, a REST service, or an RPC agent.
• Tier 2 (SQL port): tier 1 + query(sql, params) / prepared statements. Required by the Query factory. Constructing a Query against a tier-1-only connection throws immediately with a clear message.

Dialect generators live inside connection packages (@expressive/postgres, @expressive/sqlite with the runtime driver-fallback engine). A CRUD-only app never loads a SQL compiler.

The usage gradient

1. Query objects - User.get({ age: greaterThan(21) }). Plain data, RPC-transportable.
2. Query factory - Query(where => ...) for joins, aggregates, reports. Typed result shapes inferred from the return value. Server-side, tier 2.
3. Template fragments - fn`...`` micro escape hatch inside the Query factory for the last 5%, still parameterized and typed. No whole-query raw SQL rung: if a user is writing SQL by hand, the library is not earning its keep.

RPC seam: bundle the entity layer, proxy the port

For the expressive/dev magic-import architecture (client imports Table classes directly), the seam is Type.connection, not the class:

• Model modules are isomorphic (declarative field metadata, no secrets) and get bundled into the client for real.
• The dev tooling swaps Type.connection per environment: real SQL connection server-side, a fetch-backed tier-1 connection client-side. The 4-method port is the wire protocol.
• This mirrors how expressive-rpc already handles custom Error classes: regenerate the real class locally, transport only identity/data, proxy only I/O.
• Client benefits for free: insert validation fails before the network hop, the identity map becomes a client-side cache, and the lazy-relation throw-a-promise getter is de facto React Suspense integration.
• The Query factory stays server-side inside exported async functions, which the RPC layer already proxies. Behavior proxies through functions; data flows through the port.
• Security lives at the port: 4 verbs x tables x constraint tuples, enforceable per-table (PostgREST/Supabase model).

Rules this imposes:

• Model modules must not import server-only resources; connection binding moves out of the model module and is injected per environment.
• Model instance methods may only touch the port; side-effectful hooks (onDelete etc.) are enforced server-side at the port handler. Should be checked loudly by tooling.

Known cost (accepted for now): the identity map becomes a per-client cache with no cross-client invalidation; the port is the natural future home for subscriptions.

Evidence base

The IFCP dogfood app (real client project) validates both ends:

• ~20 production models exercise the full orm surface (relations, lazy loading, cascaded deletes, column renames, json/uuid).
• Its hand-written postgres.ts adapter is exactly a tier-1 connection (with warts the port should absorb: ordering smuggled through constraints as ["field","asc"], an IS NOT NULL special case marked // TODO: Fix this on the ORM side, array/ANY($n) handling, ~120 lines of pg boilerplate every consumer would otherwise rewrite).
• Its ten report/*.ts files of raw SQL (LEFT JOIN LATERAL, TRIM(CONCAT(...)), aggregates, conditional predicates, hand-maintained result interfaces nothing verifies) are the exact workload the Query factory must absorb to close the gradient. The recurring "latest related row per parent" LATERAL pattern suggests a first-class relation concept (e.g. latest(ClaimStatus)) alongside get()/one().

Work plan

1. Formalize the tier-1 wire protocol. First-class order (not fake constraints), value-less ops (IS NULL / IS NOT NULL), defined array/IN semantics, count, limit/offset. Documented and versioned - this is the framework's data seam.
2. Port the Query factory to orm's field metadata. Spike first: single-table select through Query(where => ...) reading Type.fields. This is the load-bearing bet; it will surface impedance mismatches (async converters, id conventions, relation metadata) early.
3. Ship first-party connections. @expressive/postgres (pg pool + PGlite for dev) and @expressive/sqlite (existing 5-driver engine fallback) as tier-2 implementations of the port, mining the current sql dialect packages for generators, schema validation, and lifecycle code.
4. Schema DDL + sync from Type.fields. Dev boots PGlite/bun:sqlite and creates tables from models with zero config; production validates against the live schema instead.
5. Extend the Query factory toward the report workload. Aggregates + GROUP BY, string functions, OFFSET, subqueries/LATERAL, conditional predicate skipping (the Where.skip pattern), latest() relation.
6. RPC connection agent. Fetch-backed tier-1 Type.Connection + the dev-tooling swap of Type.connection, with per-table policy hooks server-side.

Items 1-3 are the architecture; 4-6 can trail. packages/sql goes into maintenance as a parts donor and is retired once 2-3 land. Package naming (@expressive/orm is the likely shipped name) stays TBD.

[gabeklein]

Direction decision: orm is the trunk, sql is the parts donor

Unification direction is grow packages/orm, not reshape packages/sql:

• orm's public API is the invariant (production-proven via IFCP, published as @expressive/orm@0.12.1 under the long-term name; npm continuity preserved).
• sql's invariant is its Builder/Generator engine, which is protected by ~90 SQL-string snapshot tests - those snapshots port with the engine and act as the correctness oracle for the transplant. The reverse direction (teaching sql orm's API) has no such oracle.
• The engine-to-field-metadata rewiring lands on sql's core in either direction; growing orm pays it once, and all churn happens in dead-end code rather than shipped code.

Transplant map:

sql source	fate
query/ (Query, Builder, Value, Computed)	moves to orm behind a shim reading Type.fields
connection/Generator.ts + dialect generators	move into tier-2 connection packages
sqlite engine.ts, schema gen/validation	move into connection packages
type/Table.ts, type/Field.ts, columns/*	replaced by orm's Type/Field/fields
connection/Connection.ts	replaced by the tiered port
tests	query/generator snapshots port as oracle; Table/column tests retire

Reprioritization: adapters before Query

Shipped tier-1 connections are the top priority and are independent of the Query transplant (they only implement the 4-method port; no SQL compiler involved). The Query work is the harder half - SQL emission ports cleanly under snapshots, but reconciling sql's type-level generics (Nullable/Optional, dialect type augmentation) with orm's Infer-tuple typing is real design work and should not block day-to-day orm adoption.

Revised order:

1. Wire-protocol cleanup (unchanged - the contract the adapters implement)
2. First-party tier-1 connections: @expressive/sqlite over the existing 5-driver engine fallback, @expressive/postgres over pg + PGlite; constraint translation absorbs the warts of IFCP's hand-rolled adapter (order, IS NULL, IN/ANY, count)
3. Schema DDL + sync from Type.fields (makes the dev experience zero-config)
4. Query factory transplant onto orm field metadata (spike first)
5. Query extensions for the report workload
6. RPC connection agent

Rationale: immediate goal is using orm in new apps without carrying the ~240-line hand-written adapter baggage the IFCP project needed.