feat: convert InsightsBookingService to use Prisma.sql raw queries

Hi there 👋

⛅ Tech	🌤️ Feat
4 medium, 1 low	3 ok, 4 mitigated

This PR converts the InsightsBookingService integration tests from Prisma object-notation expectations to direct Prisma.sql template-literal comparisons, following the pattern previously established in the InsightsRoutingService tests. The functional core of the conversion is sound: all user-controlled inputs (userId, orgId, teamId, teamIds, filter ids) are safely parameterized via Prisma.sql ${param} interpolation with no string concatenation (no SQL injection), the authorization gating (isOrgOwnerOrAdmin, NOTHING_CONDITION = 1=0) is preserved, AND/OR precedence and identifier quoting are correct, and the blast radius is limited to the integration test itself.

However, the review surfaces several non-blocking robustness/consistency concerns that warrant attention before merge: (1) the stated "same patterns as InsightsRoutingService" basis does not actually exist in the current tree — insightsRouting.ts still uses Prisma object notation, so the two sibling services now diverge; (2) the org-scope SQL assertion depends on unguaranteed row ordering from findAllByParentId/findAllByTeamIds and may fail intermittently; (3) the ADMIN role is never positively exercised and the caching tests were removed, leaving the Prisma.Sql cache behavior untested; (4) the final $queryRaw test relies on denormalization triggers being present in the test database and on exact single-row equality, making it environment-sensitive; and (5) the scope:"user" branch still requires org owner/admin (pre-existing behavior, worth confirming as intended). No functional or security bug was confirmed in the service logic.

---

Worth checking

• 🟡 packages/lib/server/service/insightsBooking.ts (L43)
  The stated basis of this conversion — matching the InsightsRoutingService integration tests — does not exist in the tree: insightsRouting.ts still uses Prisma object notation (NOTHING = { id: -1 }, findMany with Prisma.RoutingFormResponseDenormalizedWhereInput, returning { AND: conditions } objects). This PR converts InsightsBookingService to raw Prisma.Sql (NOTHING_CONDITION = Prisma.sql`1=0`, getBaseConditions()), so the two sibling services now have different authorization-condition contracts and testing conventions. Reconcile the consistency premise: confirm RoutingService is converted in a separate MR (and link it) or document that the services are intentionally diverging.
• 🟡 packages/lib/server/service/__tests__/insightsBooking.integration-test.ts (L348)
  The org-scope SQL assertion expects an exact, ordered Prisma.sql array for teamIds ([org.id, team.id, team2.id, team3.id] from findAllByParentId) and userIds ([user.id, user2.id, user3.id] from findAllByTeamIds then Set). Neither findAllByParentId (team.ts findMany with no orderBy) nor findAllByTeamIds (membership.ts findMany with no orderBy) guarantees row order, so the .toEqual(Prisma.sql ...) comparison is fragile and can fail intermittently on seed-order changes, extra pre-existing data, or a different query plan. The author could not run the tests, so this was never verified. Make the service build these conditions deterministically (e.g., sort teamIds/uniqueUserIds) or make the test order-independent. The team-scope test is fine (single team/user).
• 🟡 packages/lib/server/service/__tests__/insightsBooking.integration-test.ts (L437)
  The ADMIN role is never positively exercised: all positive authz tests use MembershipRole.OWNER and the only denial test uses MEMBER, so a regression in the ADMIN branch of isOrgOwnerOrAdmin would go undetected. Additionally, the diff deletes the entire "Caching" describe block, even though the service still caches via cachedAuthConditions/cachedFilterConditions — the new Prisma.Sql cache behavior (especially that null from getFilterConditions is cached distinctly from "not yet built") is now untested. Add an ADMIN-role positive case (e.g., a team/org-scope test with orgRole: ADMIN) and restore a minimal caching test asserting second calls return the same Prisma.sql instance/equality without re-querying.
• 🟡 packages/lib/server/service/__tests__/insightsBooking.integration-test.ts (L482)
  The $queryRaw test executes SQL against the BookingTimeStatusDenormalized view backed by BookingDenormalized, which is only populated by DB triggers installed by migrations (20250502130843_setup_triggers_for_booking_denormalized / 20250520143014_booking_denormalized_backfill). The test seeds via prisma.booking.create() and asserts exactly [{ id: userBooking.id }]. This only holds if (1) the test DB has those trigger migrations applied (otherwise the view is empty and the assertion fails as []) and (2) no residual matching row exists in a shared DB (the exact single-row toEqual is sensitive to leftovers matching userId = X AND teamId IS NULL AND (eventTypeId = E OR eventParentId = E)). Confirm the test DB applies these migrations and consider asserting results "contains the userBooking id" (e.g., expect(results.map(r => r.id)).toContain(userBooking.id)) instead of exact single-row equality unless a clean/isolated DB is guaranteed.

Small things (take or leave)

• 🔵 packages/lib/server/service/insightsBooking.ts (L114)
  For scope === "user", the branch is only reachable after isOrgOwnerOrAdmin(...) returns true, so a plain team member (even one requesting only their own user-scoped bookings) receives NOTHING_CONDITION. This is carried over from the pre-conversion implementation (not introduced here) and the "user scope" test therefore has to seed the actor as OWNER to reach the branch. This may be intentional, but it is an unusual authorization model for a "user" scope; confirm it is intended and that org ADMINS can reach it (per the ADMIN-role finding). Flagging so it is not mistaken for new behavior.

---

✅ Feature-level checklist

Looks good

• ✅ Replace Prisma object notation expectations with direct Prisma.sql template literal comparisons.
  All authorization and filter condition tests now assert directly against Prisma.sql templates instead of inspecting .strings/.values or object notation.
• ✅ Add NOTHING_CONDITION = Prisma.sql`1=0` constant for consistency.
  The constant is present and used consistently to deny access (including the scope:"user" denial for non-owner/admin members).
• ✅ Check parameter binding — ensure all user inputs in the SQL templates are properly parameterized (no injection risks).
  All user-controlled inputs are passed via Prisma.sql ${param} interpolation with no string interpolation into SQL text. No injection risk found.

Partially covered

• ⚠️ Modify the final integration test to use $queryRaw for actual database querying.
  The test uses $queryRaw, but it relies on denormalization trigger migrations being applied in the test DB and on exact single-row equality, making it environment-sensitive. Recommend a "contains" assertion unless a clean/isolated DB is guaranteed.
• ⚠️ Verify the tests actually pass (run integration tests manually) — TZ=UTC yarn test ...
  The author encountered test-runner configuration issues and could not run the tests. The org-scope assertion depends on unguaranteed row ordering and the $queryRaw test on trigger presence, so unverified execution leaves real risk of intermittent or environment-dependent failures.
• ⚠️ Verify authorization security — test with different user roles (owner, admin, member) to ensure no privilege escalation.
  Parameter binding is safe (no injection) and the authz gate is preserved, but the ADMIN branch of isOrgOwnerOrAdmin is never positively exercised — only OWNER (positive) and MEMBER (denial) are tested. A regression in the ADMIN path would go undetected.
• ⚠️ Test edge cases — empty teams, missing users, null values, boundary conditions.
  Deny-when-empty behavior is logically preserved, but caching behavior (including null-distinct cache semantics) is now untested after the caching describe block was removed, and the coverage of edge cases is reduced as a result.