🎙️ task - feat(types): type json_build_object / json_agg subqueries by their selected shape

[ehm-a-beaver]

🦫🎙️ dispatch to foreman

💧 task enqueued
   ├─ priority = ?
   ├─ yieldage = ?
   └─ leverage = ?

title
feat(types): type json_build_object / json_agg subqueries by their selected shape
description

.what

json_build_object(...) and json_agg(json_build_object(...)) expressions are currently typed
as an opaque bag:

export type SqlFunctionJsonBuildObjectOutput = Record<string, any> | null;

so a hydrated view column lands as:

home_station_geocode: SqlFunctionJsonBuildObjectOutput;  // solo
certificates: SqlFunctionJsonBuildObjectOutput;          // json_agg — an ARRAY, typed as an object

this asks for those expressions to be typed by the shape they actually select.

.why

Record<string, any> is assignable to every type, so it silently defeats the compiler for any
consumer that reads a nested column. downstream, sql-dao-generator currently papers over this
with a type assertion in every generated cast:

// solo — asserts the RAW row type onto a json bag
agent: castAgentFromDatabaseObject(dbObject.agent as SqlQueryFindAgentByIdOutput)

// array — asserts an ARRAY type onto an object type, then calls .map on it
media: (dbObject.media as SqlQueryFindSmsMediaByIdOutput[]).map(castSmsMediaFromDatabaseObject)

both assertions are false, and the second is false about its cardinality as well as its
element type. observed live in ahbode/svc-notifications
(src/access/daos/smsDao/castFromDatabaseObject.ts:25,32,36) and reproducible in
sql-dao-generator's own example project fixture.

with a real type here, that as can be deleted outright and nested reads become
compiler-checked.

.note — no dependency, and no rush

ehmpathy/sql-dao-generator repairs its half independently (a truthful generated json-shape
type, plus boundary casts that make the declared runtime type true). that work does not
block on this issue and ships without it.

what this issue would add is the piece sql-dao-generator cannot reach from where it sits:
compile-time enforcement rather than a runtime guarantee. filed as a handoff so the
knowledge is not lost, not as a blocker.

.scope sketch (advisory — the HOW is yours)

two shapes to type, and the second is the one already wrong today:

expression	today	desired
json_build_object(k, v, ...)	Record<string, any> | null	an object type of the selected keys
json_agg(json_build_object(...))	Record<string, any> | null	an array of that object type

one fact worth a note regardless of how far the types go: a timestamptz inside
json_build_object serializes to an ISO-8601 string, and a bigint/numeric to a json
number — so the inner field types differ from the raw column types of the same columns. a
faithful type would reflect the json serialization, not the table's.

.context

• origin: ehmpathy/sql-dao-generator issue chore(deps): bump ajv from 6.12.0 to 6.12.6 #61 and the vision route
  .behavior/v2026_08_02.fix-truthful-runtime-types
• the Record<string, any> declaration is honest about what is not yet known — this issue is
  about closure of that gap, not a complaint about the current output

---

dispatched by beaver 🦫 on behalf of the sql-dao-generator route