Migration: Replace remaining 308 JsonQueryBuilder usages with typed delegates

[teetangh]

Context

PR #105 (fix/prisma-v040-migration) migrates the backend to prisma_flutter_connector v0.4.0. The v0.4.0 release (teetangh/prisma-flutter-connector#34) fixes 11 issues including:

• Rebrand: Familiarise → Elluminar #24: @default(uuid/cuid) auto-generation via gen_random_uuid()
• Business Registration & Payment KYC #25: StringFilter/WhereInput serialization (explicitToJson: true)
• fix: align Flutter entities, models, and enums with Prisma schema #33: groupBy() method in generated delegates
• DateTime fix: _normalizeForJson in delegates converts DateTime → ISO8601 strings before fromJson
• Scale Readiness Assessment & Prioritized Roadmap (3-6 Month Timeline) #29: Auto-generated schema_registry.g.dart
• [Migration] Update Dart Frog backend for BetterAuth schema compatibility #30: Nested writes for 1:N/1:1 relations
• feat: Implement Novu notification system for Flutter #32: M2M in relationPath deep filters

What's done in PR #105:

• 12 manual Uuid().v4() removals across 8 repos
• Dashboard: in-Dart _groupByStatus → SQL GROUP BY
• Appointment: nested include workaround cleanup
• Schema registry: defaultValue: 'uuid()' on all ID fields
• Code regenerated with v0.4.0 generator (347 files)
• Integration-tested: POST/GET feedback, dashboard stats, consultants (nested includes + computed), domains

What remains: 30 files with 308 JsonQueryBuilder usages that should be migrated to typed delegates where possible.

Remaining Files (sorted by complexity)

Tier 1: Simple CRUD — Direct typed delegate replacement (~8 files, ~30 usages)

File	Usages	Notes
feedback_repository.dart	1	createFeedback still uses JQB for data map; getFeedbackByUserId already on delegate
recording_repository.dart	1	Already mostly on delegates
maintenance_repository.dart	1	Simple findFirst
announcement_repository.dart	1	Simple findMany
waitlist_repository.dart	4	Basic CRUD
account_repository.dart	4	Basic CRUD
payout_account_repository.dart	5	Basic CRUD
consultee_profile_repository.dart	5	Basic CRUD + update

Tier 2: Medium complexity — Some advanced features (~12 files, ~100 usages)

File	Usages	Notes
refund_repository.dart	5	Includes, transactions
trial_repository.dart	6	Includes, status filtering
dispute_repository.dart	6	Includes, pagination
consultant_profile_repository.dart	6	Includes, computed fields
consultant_verification_repository.dart	6	Transactions, includes
verification_repository.dart	7	Token-based lookups
meeting_session_repository.dart	7	Includes, transactions
session_repository.dart	7	Auth token queries
user_repository.dart	9	Includes, nested profile access
review_repository.dart	10	Includes, computed fields, pagination
collaborator_repository.dart	8	Deep includes (3-level)
domain_repository.dart	8	ComputedField.count, includes

Tier 3: Complex — Keep JsonQueryBuilder for advanced queries (~10 files, ~178 usages)

File	Usages	Notes
appointment_repository.dart	86	Transactions, FilterOperators.relationPath, ComputedFields, conflict checking, raw SQL
dashboard_repository.dart	23	Already migrated groupBy; remaining: _calculateTotalSpent (raw aggregate), consultant dashboard
plan_repository.dart	18	4 plan types × CRUD, cross-plan queries
support_ticket_repository.dart	14	Pagination, includes, response creation
programs_repository.dart	13	Class/webinar management, status updates
slot_repository.dart	12	Availability, conflict detection, FilterOperators
checkout_repository.dart	12	Payment flow, transactions, discount codes
consultant_explore_repository.dart	11	ComputedField.min/first, FilterOperators.relationPath, pagination
referral_repository.dart	8	Transactions, counter updates

Technical Implementation Plan

For Tier 1 (simple repos):

// BEFORE (JsonQueryBuilder):
final query = JsonQueryBuilder()
    .model('Waitlist')
    .action(QueryAction.findMany)
    .where({'userId': userId})
    .orderBy({'createdAt': 'desc'})
    .build();
final results = await executeQueryAsMaps(query);

// AFTER (typed delegate):
final results = await _prisma.waitlist.findMany(
  where: WaitlistWhereInput(userId: StringFilter(equals: userId)),
  orderBy: WaitlistOrderByInput(createdAt: SortOrder.desc),
);
return results.map((w) => w.toJson()).toList();

For Tier 2 (medium repos):

• Convert simple findMany/findFirst/create/update/delete methods to delegates
• Keep JQB for methods that use include(), ComputedField, or FilterOperators
• Each method should be tested via curl after conversion

For Tier 3 (complex repos):

• Do NOT convert methods using: ComputedField, FilterOperators.relationPath, executeInTransaction with multiple queries, raw SQL
• DO convert simple CRUD helper methods within these files
• The appointment_repository.dart (86 usages) is the largest and should be done last

Prerequisites

1. Ensure prisma_flutter_connector v0.4.0 is published on pub.dev
2. Switch pubspec.yaml from local path to ^0.4.0
3. Run scripts/regenerate-build.sh --prisma after switching

Testing approach

For each migrated file:

1. dart analyze — zero errors
2. Start dart_frog server: dart build/bin/server.dart
3. Curl each affected endpoint with a valid JWT
4. Verify response matches pre-migration behavior

JWT token generation (for testing):

dart run tool/gen_test_token.dart  # outputs JWT for test_intg_cbj_cnt user
USER_ID=test_intg_cbj_cte dart run tool/gen_token_for.dart  # for consultee user

Acceptance Criteria

• All Tier 1 repos migrated to typed delegates
• All Tier 2 repos: simple methods migrated, complex methods documented
• Tier 3 repos: simple CRUD methods migrated where safe
• dart analyze passes with zero errors
• Integration tests pass for all migrated endpoints
• Final JsonQueryBuilder count < 200 (from current 308)

[teetangh]

Progress Update (2026-03-28)

Done

• ✅ Connector v0.5.1 published (zero buf.write, full code_builder architecture)
• ✅ Tier 1 partial: feedback_repository, recording_repository converted to typed delegates
• ✅ _prisma (PrismaClient) injected into ALL 9 Tier 2 repos + DatabaseClient updated
• ✅ Safe minor dependency upgrades across frontend + backend
• ✅ Branch: fix/jqb-to-typed-delegates (pushed, ready for continuation)

Current: 303 JQB usages across 28 files (was 307/30)

Ready to convert (repos with _prisma injected):

• refund (5), dispute (6), verification (7), meeting_session (7), session (7), user (9), review (10), collaborator (8), domain (8)
• Plus plan (18, 0 advanced features), consultant_profile (6, 0 advanced features) — fully convertible

Must stay on JQB (advanced features):

• appointment (86 JQB, 45 advanced) — ComputedField, relationPath, raw SQL
• consultant_explore (11 JQB, 13 advanced) — ComputedField, relationPath
• slot (12 JQB, 10 advanced) — FilterOperators, transactions

[teetangh]

the honest comparison:

What Typed Delegates Give You

Compile-time safety:
- Field names are checked at compile time — UserWhereInput(emial: ...) won't compile (typo caught)
- Filter types are enforced — you can't pass an IntFilter where a StringFilter is expected
- Create/Update inputs know which fields are required vs optional
- OrderBy only allows sortable fields

Developer experience:
- IDE autocomplete for all fields and methods
- Refactoring support — rename a field and the compiler tells you every usage
- No magic strings for model names ('User' vs _prisma.user)

What You Lose vs JsonQueryBuilder

This is the critical part most people miss:

┌────────────────────────────────┬─────────────────┬──────────────────┐
│            Feature             │ Typed Delegates │ JsonQueryBuilder │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ Simple CRUD                    │ Yes             │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ include() for relations        │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ ComputedField (subqueries)     │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ FilterOperators.relationPath() │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ selectFields()                 │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ includeRequired() (INNER JOIN) │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ Custom raw SQL in transactions │ No              │ Yes              │
├────────────────────────────────┼─────────────────┼──────────────────┤
│ distinct()                     │ No              │ Yes              │
└────────────────────────────────┴─────────────────┴──────────────────┘

Typed delegates are a SUBSET, not a superset. They only cover findUnique, findFirst, findMany,
create, createMany, update, updateMany, delete, deleteMany, count, and groupBy — all without
relation includes or advanced filtering.

Compared to TypeScript Prisma

TypeScript Prisma Client gives you everything type-safe — includes, nested writes, relation
filters, all with full IntelliSense. The Prisma Flutter Connector's typed delegates are much more
limited. You get TypeScript-Prisma-level safety for basic CRUD, but the moment you need:

- include: { consultantProfile: { include: { user: true } } }
- where: { posts: { some: { title: { contains: 'test' } } } }
- Computed fields, distinct, raw SQL

...you're back to JsonQueryBuilder with runtime string keys.

The Practical Reality

In the familiarise_mobile backend:
- ~120 JQB usages are simple CRUD that CAN be typed delegates (compile-time safe)
- ~180 JQB usages use advanced features (includes, computed fields, relation paths) that MUST stay
on JsonQueryBuilder (runtime strings)

So the realistic end state is a hybrid — typed delegates for simple operations, JsonQueryBuilder
for anything involving relations or advanced queries. This is why the migration target is reducing
from 307 to ~120-150, not to zero.

Is It Worth It?

For simple CRUD — yes, absolutely. A typo in 'consultantProfileId' silently returns empty results
with JQB but fails at compile time with typed delegates.

For complex queries — no alternative exists yet. The connector would need to add include() support
to delegates (which means generating relation-aware return types) to close the gap with TypeScript
Prisma. That's a significant feature not yet built.

[teetangh]

Update from the schema-sync branch (feat/schema-sync-mvp):

• The hand-maintained schema_registry_builder.dart (2,529 lines) is gone — the registry is now generated by prisma_flutter_connector:generate (v0.6.0 adds @map/@@map parsing), so the worst JQB hazard (stale registry) is eliminated.
• A schema-diff audit of all remaining hand-written JQB usage (~290 calls, ~50 models) found and fixed the silent breaks introduced by the web schema sync (collaborator tables merged, consultantShare→consultantSharePaise, Refund/Dispute.amount→amountPaise, cancellationReason now an enum).
• Direction (per Kaustav): the ORM/typed delegates are the destination — new code must use PrismaClient delegates, and remaining JQB call sites should migrate when touched. Where the connector lacks a capability (nested writes, first-class groupBy), improve the connector instead of dropping to JQB/raw SQL: see teetangh/prisma-flutter-connector issues.