Feature/308 kysely dynamic queries #309

2026-09-04T17:45:39-05:00

bermudalamb commented

2026-09-04 17:45:39 -05:00

No description provided.

bermudalamb added 5 commits 2026-09-04 17:45:39 -05:00

docs(db): design converting the two dynamic queries to Kysely (#308 ) 12e1616392

The work #305 made possible and deliberately did not do. These are the two queries the builder was ever wanted for: admin.ts and items.ts both splice a run-time-composed where clause into query text, and they are the only places S2077 has a real point after #294 hoisted the seven fixed-shape queries into named constants. They are safe today, and itemFilters.ts spells out why in sixteen lines — which is the problem, because a property that takes sixteen lines to explain is one an edit can quietly break.

All four call sites convert rather than only the two flagged ones. The by-id constants carry no hotspot and are already safe, but they are built by interpolating the same projection strings the list queries use, so converting only the list queries would leave itemSelect.ts holding a Kysely builder and a raw string that must produce an identical projection — two spellings to keep in step by hand where the file's own header already warns about one.

The filter builder returns an array of expressions rather than taking a query builder and returning it filtered, because the two callers do different things with the result: the storefront prepends its own not-pending clause and the admin route does not. A function that owned the builder would have to be told about that difference. startIndex disappears with the splicing it existed for.

The aggregate subqueries become jsonArrayFrom, which emits the same coalesce(json_agg(agg), '[]') they hand-write today. That is the second thing this buys and it may matter more than the first: pool.query<T> asserts a shape TypeScript never checks against the SQL, which is why itemSelect.ts's header says the selects and their types are kept in step by hand and the integration suite is the only thing that catches a drop. Afterwards that is a compile error.

The row types stay hand-written and exported rather than being inferred from the query. Inference would be tidier and is refused on purpose: these are the shape the frontend reads, and a type that silently becomes whatever the query happens to return is a contract that can change without anyone deciding to.

The two invariant tests survive and get stronger. They currently inspect the clause strings the function returns; afterwards they compile the expressions and assert on the SQL Kysely actually emits, with the hostile value appearing in the parameters and nowhere else — the same claim tested against the real artefact instead of an intermediate one.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

docs(db): plan converting the item queries to Kysely (#308 ) 3248ac656e

One task, and that is a decision rather than a shortcut. itemSelect.ts, itemFilters.ts, both routes and the unit test file are coupled — the exports the routes call are the ones being replaced, and #298 put the test file under a tsconfig that type-checks it, so any partial commit is a red build.

Every line of it was verified by probe against the real generated schema before it was written, not sketched. The projections type-check, jsonArrayFrom correlates through whereRef, the mixed array of sql templates and builder expressions composes under eb.and, and the emitted SQL is quoted in the steps so a wrong result is caught at the step that produces it rather than three steps later. The probe also settled the question the spec left open with a fallback: the row type is assignable to the hand-written contract, so no cast is needed.

The two invariant tests are rewritten rather than ported. They used to read the clause strings the builder returned; they now compile the expressions against the same items-and-categories shape the real queries use and assert on the SQL Kysely emits, with the hostile value present in the parameters and absent from the text. Building a narrower query in the helper would have needed a cast, and a cast in that test would be testing the cast.

The step that verifies the conversion is the one that runs the integration suite unedited. Those tests are the contract — same JSON, same ordering, same statuses — so the plan says plainly that a test needing an edit means the query changed behaviour and the query is what to fix.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

refactor(db): build the item queries through Kysely (#308 ) ec1020891f

The two queries the builder was ever wanted for. #294 removed interpolation from seven sites by hoisting each fixed-shape query into a named constant; these two genuinely composed their WHERE at run time and could not be fixed that way, which is why they are the last S2077 hotspots. They were safe, and itemFilters.ts spent sixteen lines explaining why — that the clause fragments are literals, that the only things interpolated into them are placeholder indices, and that every value goes onto params. That argument was correct and it was still an argument, guarded by a comment and two tests, on a route reachable without signing in.

All four call sites moved rather than only the two flagged ones. The by-id constants carried no hotspot, but they were built by interpolating the same projection strings the list queries used, so converting only the list queries would have left itemSelect.ts holding a Kysely builder and a raw string that had to produce an identical projection — two spellings to keep in step by hand where the file's own header already warned about one.

The second thing this buys may matter more than the first. pool.query<T> asserts a shape TypeScript never checks against the SQL, which is why that header said the selects and their row types are kept in step by hand and the integration suite was the only thing that caught a drop. The projections are built with jsonArrayFrom now, which emits the same coalesce(json_agg(agg), '[]') they hand-wrote, so the row type follows from the projection and a dropped column is a compile error.

The row types stay hand-written and exported rather than being inferred from the query. Inference would be tidier and is refused deliberately: these are the shape the frontend reads, and a type that silently becomes whatever the query happens to return is a contract that can change without anyone deciding to change it.

The two invariant tests survive and got stronger. They used to inspect the clause strings the builder returned; they now compile the expressions and assert on the SQL Kysely actually emits, with the hostile value appearing in the parameters and nowhere else — the same claim, tested against the real artefact instead of an intermediate one.

Closes #308

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

fix(db): narrow the status column instead of asserting the row (#308 ) 306db81966

`.$castTo<AdminItemRow>()` / `.$castTo<PublicItemRow>()` at the two list-query call sites replaced the entire result type with an assertion rather than narrowing the one column that actually disagreed, which meant a projection that silently lost a column would still compile — exactly the failure mode this task exists to close, and the opposite of what the commit body claims.

Fixed at the source instead: `itemSelect.ts` now defines `ItemsWithStatus`/`ItemDB`, narrowing `items.status` from the schema mirror's `Generated<string>` (a CHECK-constrained text column, so `kysely-codegen` has no literal union to give it) to `Generated<ItemStatus>`, and builds `ItemContext`, `adminItemQuery()` and `publicItemQuery()` from an `itemDb` typed with `ItemDB` instead of `db`/`DB`. Both `$castTo` calls and their comments are gone; the `AdminItemRow[]` / `PublicItemRow[]` annotations at the two call sites now check for real. Verified by temporarily dropping a column from `adminItemQuery`'s projection: the `AdminItemRow[]` assignment failed to compile as expected, confirming the guarantee actually holds.

Also corrected two now-false statements left over from the conversion: `db-kysely/CONVENTIONS.md`'s worked-example section said `buildItemFilterSql` was "still raw `pg`" and that converting it "would put a second copy of a live function in `src/` that nothing calls" — both untrue since #308 shipped it as `itemFilterExpressions`. And two doc comments in `itemSelect.ts` still named the deleted `PUBLIC_ITEM_SELECT`/`ADMIN_ITEM_SELECT` constants instead of the functions that replaced them.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

test(filters): merge the duplicate itemFilters import (#308 )

SonarQube Analysis / sonarqube (pull_request) Successful in 25m39s

Details

Linting / lint (pull_request) Successful in 3m8s

Details

abe8ac8184

backend/tests/unit/itemFilters.test.ts had two separate import statements from ../../src/itemFilters; merged into one, with nothing else in the file changed. A companion fix to backend/src/routes/items.ts — reading the by-id route's id with the existing readId helper instead of Number(req.params.id), to close the leniency Number() introduced toward inputs like '5.0', '1e2' and '0x10' — was tried and then reverted, because backend/tests/integration/errorHandling.integration.test.ts deliberately drives that exact route with a non-numeric id to prove that asyncRoute plus the error middleware turn a rejected handler into a 500 rather than hanging the request, and readId's stricter parse would answer 404 before that mechanism ever runs, leaving the test green while silently deleting the coverage it exists for; the route now carries a comment explaining why Number() stays and pointing at #307 for giving that test another trigger before making the switch.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

bermudalamb merged commit 3cbf73ee87 into main

2026-09-04 17:47:42 -05:00

bermudalamb deleted branch feature/308-kysely-dynamic-queries

2026-09-04 17:47:42 -05:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: bermudalamb/redefined-designs#309