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

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.

Start by reading packages/orm's Type.Connection and field metadata alongside packages/sql's Query factory and dialect packages. Use work-plan items 1-3 to narrow the first milestone, beginning with a single-table Query over Type.fields. Done should include a documented tier-1 protocol, a working metadata-based query path, and first-party connection implementations.