gistfile1.txt

Short version: use TanStack Start for the full‑stack shell + server functions, TanStack Query for talking to your existing DB APIs, and TanStack DB as your in‑browser “mini database” that keeps everything normalized, reactive, and crazy fast.

I’ll break it down into:
	1.	Mental model: what each piece should do
	2.	Suggested architecture / folder structure
	3.	How to wire TanStack Start ⇄ DB (with stored procedures)
	4.	How to wire TanStack Query + TanStack DB
	5.	How this helps for your call transcript grading domain
	6.	Specific tricks to make the app feel “instant”

⸻

1. Mental model for this stack
	•	Existing SQL DB + stored procedures
This is the “source of truth” where all the gnarly joins and grading logic live. You don’t try to mirror its schema 1:1 on the client – you just call into it via a Node DB client / driver.
	•	TanStack Start
A full‑stack React framework built on TanStack Router with SSR/streaming, server routes, and server functions. Start is explicitly designed to work with “any database provider” by calling your DB client from server functions or routes.  
	•	TanStack Query
Handles network I/O, caching, retries, background refresh, SSR dehydration/hydration, etc. It’s the “how do I fetch” layer integrated with TanStack Router’s loaders.  
	•	TanStack DB
A client‑side embedded database layer on top of TanStack Query:
	•	You define collections that sync via existing queries (REST/tRPC/whatever).
	•	You read with live queries (useLiveQuery) that join/filter across collections and only recompute diffs via differential dataflow.
	•	You write with optimistic transactions that auto‑rollback on error.  

Think of it like:

DB (Postgres/SQL/etc)
→ server functions in TanStack Start
→ TanStack Query calls those server functions
→ TanStack DB wraps those queries into normalized collections
→ React components just say “give me all calls assigned to me, graded < 80, sorted by newest” with useLiveQuery.

⸻

2. Overall architecture / layering

One opinionated but clean layout:

src/
  server/
    db/
      client.ts          // raw DB connection (pg, mssql, etc)
      calls.ts           // "repositories" that call stored procs
      grades.ts
      rubrics.ts
    api/
      calls.ts           // server functions / routes that wrap the repos
      grades.ts
  db/                     // TanStack DB collections (client-side)
    queryClient.ts        // TanStack QueryClient factory
    collections/
      calls.ts
      grades.ts
      rubrics.ts
  routes/
    calls.index.tsx       // call queue
    calls.$callId.tsx     // call detail + transcript grading
    agents.$agentId.tsx   // agent performance dashboard
  router.tsx              // createRouter + SSR hydration wiring

Key ideas
	•	All DB access is in src/server/db/**/*.ts
	•	These files know about stored procedures and joins.
	•	Everything else gets simple typed functions like listOpenCallsForUser(userId) or saveGrade(input).
	•	Server functions / server routes in src/server/api
	•	These expose your DB access functions to the client in a narrow, ergonomic API:
getCallQueue, getCallDetail, submitGrade, etc.  
	•	TanStack Query + DB are created in router.tsx and put in router context
	•	The router already has hooks for dehydration/hydration, making SSR seamless with Query and DB.  
	•	Components only talk to collections via useLiveQuery
	•	They don’t worry about “which query key” or how to filter; they just express the view they want.

⸻

3. Talking to the existing DB (with stored procedures)

Because your schema and stored procedures are created externally, you treat the DB as a black box:

3.1 DB client

src/server/db/client.ts (pseudo‑code):

// Example with pg – swap for your driver
import { Pool } from 'pg'

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
})

export async function callStoredProc<T>(
  name: string,
  params: unknown[] = [],
): Promise<T[]> {
  // pattern depends on your DB; Postgres here
  const res = await pool.query<T>(`SELECT * FROM ${name}(${params
    .map((_, i) => `$${i + 1}`)
    .join(', ')})`, params)

  return res.rows
}

3.2 Repository functions per domain

Example: src/server/db/calls.ts

import { callStoredProc } from './client'

// Defined based on whatever SPs you have:
export async function listOpenCallsForAgent(agentId: string) {
  return callStoredProc<CallQueueRow>('sp_get_open_calls_for_agent', [agentId])
}

export async function getCallDetail(callId: string) {
  // Could call one SP that already does all the joins:
  return callStoredProc<CallDetailRow>('sp_get_call_detail', [callId])
}

export async function saveCallGrade(input: SaveGradeInput) {
  return callStoredProc('sp_save_call_grade', [
    input.callId,
    input.overallScore,
    input.criterionScoresJson, // whatever your SP expects
    input.graderId,
  ])
}

The types (CallQueueRow, CallDetailRow, SaveGradeInput) you can either:
	•	Define by hand; or
	•	Generate via an ORM/codegen tool pointing at your existing DB (e.g. Drizzle introspection, Prisma db pull, Kysely codegen). You don’t have to let them own migrations – they can just be used for type generation.  

⸻

4. TanStack Start + TanStack Query + TanStack DB

4.1 QueryClient & collections in the router

src/router.tsx (simplified):

import { createRouter } from '@tanstack/react-router'
import { QueryClient, dehydrate, hydrate } from '@tanstack/react-query'
import { QueryClientProvider } from '@tanstack/react-query'
import { createCollection } from '@tanstack/react-db'
import { queryCollectionOptions } from '@tanstack/query-db-collection'

import * as api from './server/api' // your server functions

export function createAppRouter() {
  const queryClient = new QueryClient()

  // One collection for each “UI-level” resource:
  const callsCollection = createCollection(
    queryCollectionOptions({
      id: 'calls',
      queryKey: ['calls'],
      queryClient,
      getKey: (row) => row.callId,
      queryFn: async () => api.getCallQueue(), // uses stored proc under the hood
    }),
  )

  const gradesCollection = createCollection(
    queryCollectionOptions({
      id: 'grades',
      queryKey: ['grades'],
      queryClient,
      getKey: (row) => row.gradeId,
      queryFn: async () => api.getGradesForUser(), // or per agent
      // onInsert/onUpdate will call your save SPs
    }),
  )

  const router = createRouter({
    routeTree,
    context: {
      queryClient,
      collections: { callsCollection, gradesCollection },
    },
    dehydrate: () => ({
      queryClientState: dehydrate(queryClient),
    }),
    hydrate: (dehydrated) => {
      hydrate(queryClient, dehydrated.queryClientState)
    },
    Wrap: ({ children }) => (
      <QueryClientProvider client={queryClient}>{children}</QueryClientProvider>
    ),
  })

  return router
}

This matches the recommended pattern in the external data loading guide: create the QueryClient in createRouter, dehydrate/hydrate it, and optionally stick it on router context so loaders & components can use it.  

4.2 Collections as the single source of truth

TanStack DB’s query collections are designed exactly for this pattern: they use TanStack Query under the hood, keep a normalized local collection store, and give you live queries + optimistic writes with automatic rollback.  

So instead of:

const calls = useQuery({ queryKey: ['calls'], queryFn: fetchCalls })
const myOpenCalls = calls.data.filter(/* ... */)

you do:

import { useLiveQuery } from '@tanstack/react-db'
import { eq } from '@tanstack/db'

function MyQueue() {
  const { data: calls } = useLiveQuery((q) =>
    q
      .from({ call: callsCollection })
      .where(({ call }) => eq(call.assignedTo, currentUserId))
      .where(({ call }) => eq(call.status, 'open'))
      .orderBy(({ call }) => call.createdAt, 'desc'),
  )

  // calls is already a filtered, sorted view; DB keeps it incremental
  return <CallList calls={calls} />
}

TanStack DB uses differential dataflow so only the differences are computed when data changes, which is exactly what makes dashboards and queues feel “instant” even with large datasets.  

4.3 Mutations: optimistic grading

In your gradesCollection you can use onInsert / onUpdate to call your stored procedures:

const gradesCollection = createCollection(
  queryCollectionOptions({
    id: 'grades',
    queryKey: ['grades'],
    queryClient,
    getKey: (row) => row.gradeId,
    queryFn: api.getGradesForUser,
    onInsert: async ({ transaction }) => {
      // Each mutation.modified is a grade the user created optimistically
      const newGrades = transaction.mutations.map((m) => m.modified)
      await api.saveGrades(newGrades) // calls SPs

      // Return { refetch: false } if you trust the payload & don’t want refetch
      return { refetch: false }
    },
    onUpdate: async ({ transaction }) => {
      const updates = transaction.mutations.map((m) => m.modified)
      await api.updateGrades(updates)
      return { refetch: false }
    },
  }),
)

Then in the UI:

function GradeSlider({ callId }) {
  const { data: [grade] = [] } = useLiveQuery((q) =>
    q
      .from({ g: gradesCollection })
      .where(({ g }) => eq(g.callId, callId)),
  )

  const onChangeScore = (score: number) => {
    if (!grade) {
      gradesCollection.insert({
        gradeId: crypto.randomUUID(),
        callId,
        score,
        status: 'draft',
        // ...
      })
    } else {
      gradesCollection.update(grade.gradeId, { score })
    }
  }

  // UI stays in sync optimistically while SP writes happen in background
}

You get:
	•	Optimistic UI for grading without boilerplate.
	•	Automatic rollback if the stored procedure fails.
	•	All other views that depend on that grade update instantly because they also read via useLiveQuery.

⸻

5. Applying this to your call transcript grading domain

Let’s map this to your actual concepts: calls, transcripts, grades, rubrics, agents.

5.1 What collections to create?

Instead of all 19 tables, start with a UI‑level subset:
	•	callQueueCollection – rows shaped for the queue view
	•	callId, agentName, customerName, createdAt, status, latestScore, etc.
	•	Backed by a stored procedure sp_get_call_queue_for_user(userId).
	•	callGradeCollection – single grade per call (current grader)
	•	gradeId, callId, overallScore, status, etc.
	•	criterionScoreCollection – optional, if you want live joins between scores and rubrics.
	•	rubricCollection – rubric + criteria definitions used to render the grading UI.
	•	callTranscriptCollection – either:
	•	“One row per call with full transcript text (or pointer)” OR
	•	“One row per transcript segment” (for fine‑grained streaming / virtualization).

You can still use your stored procedures to produce view‑optimized rows that join multiple underlying tables. TanStack DB doesn’t care if the data is normalized against the real DB; it just wants a stable id and consistent shape.

5.2 Example: Call queue route

src/routes/calls.index.tsx:

import { createFileRoute } from '@tanstack/react-router'
import { queryClient } from '../db/queryClient'
import { callsCollection } from '../db/collections/calls'
import { useLiveQuery } from '@tanstack/react-db'
import { eq } from '@tanstack/db'

export const Route = createFileRoute('/calls')({
  // Ensure the base data is loaded on navigation (SSR + cache):
  loader: () => callsCollection.utils.refetch(), // or queryClient.ensureQueryData(...)

  component: CallsPage,
})

function CallsPage() {
  const currentUserId = useCurrentUserId()

  const { data: myOpenCalls } = useLiveQuery((q) =>
    q
      .from({ call: callsCollection })
      .where(({ call }) => eq(call.assignedTo, currentUserId))
      .where(({ call }) => eq(call.status, 'open'))
      .orderBy(({ call }) => call.createdAt, 'desc'),
  )

  return (
    <div>
      <h1>My grading queue</h1>
      <CallList calls={myOpenCalls} />
    </div>
  )
}

5.3 Example: Call detail route

src/routes/calls.$callId.tsx:
	•	Loader: preloads call detail, transcript, grade in parallel using either router loaders + Query or by calling utils.refetch() on the relevant collections.  
	•	Component: uses useLiveQuery to join across collections:

const { data } = useLiveQuery((q) =>
  q
    .from({
      call: callQueueCollection,
      grade: callGradeCollection,
      transcript: callTranscriptCollection,
    })
    .where(({ call }) => eq(call.callId, callId))
    .where(({ transcript }) => eq(transcript.callId, callId))
    .where(({ grade }) => eq(grade.callId, callId))
    .select(({ call, grade, transcript }) => ({
      call,
      grade,
      transcript,
    })),
)

Because this is all local, switching between calls or adjusting scores can feel basically instant.

⸻

6. Making the app feel really snappy

Here’s the “performance recipe” using the tools you chose:

6.1 Load broad, reuse locally

Follow the TanStack DB blog’s “Option C”: load broader, normalized collections once and then reuse via live queries instead of firing view‑specific APIs for every screen.  
	•	Load “all my open calls” once.
	•	Use different useLiveQuery filters for:
	•	“Calls with missing grade”
	•	“Calls below passing score”
	•	“Calls graded this week”
	•	All those views share one collection and update in sub‑millisecond time when anything changes.

6.2 SSR + route loaders + prefetch
	•	For main routes (queue, call detail), use TanStack Router loaders so the first paint already has data.  
	•	Use Link prefetch / router.preloadRoute so hovering a call in the queue preloads its detail route and data.

6.3 Optimistic grading UX
	•	Use gradesCollection.insert / update for score changes.
	•	UI updates immediately.
	•	Stored procedures run via onInsert/onUpdate.
	•	Automatic rollback if something fails.  

For a reviewer, this feels like “sliders change numbers instantly and dashboards react without spinners”.

6.4 Handle heavy transcripts smartly

Transcripts can be big. You can:
	•	Show call meta + grade pane immediately (fast loader).
	•	Load transcript via a deferred loader or secondary collection and show a skeleton while it streams.  
	•	Use TanStack Virtual to virtualize long transcripts if you show them as segments.  

6.5 Keep the backend close
	•	Deploy TanStack Start in the same region as your DB to minimize round‑trip latency.
	•	Use connection pooling via your driver or provider. (If you ever move the DB, Neon/Convex/Prisma Postgres are explicitly recommended for Start and come with pooling built in.  )

⸻

7. Implementation checklist

If I were setting this up for you, the concrete steps would be:
	1.	Scaffold a TanStack Start app and add:
	•	@tanstack/react-query
	•	@tanstack/react-db
	•	@tanstack/query-db-collection  
	2.	Create a DB client & repository layer in src/server/db that wraps your stored procedures with typed functions.
	3.	Add server functions / server routes in src/server/api that call the repos and encode your access rules (auth, multi‑tenant, etc).
	4.	Set up QueryClient + collections in router.tsx, pass them via router context, and wire up SSR hydration.
	5.	Define a small set of UI‑level collections (calls, grades, rubrics, transcripts) rather than mirroring all 19 tables.
	6.	Build routes using loaders + useLiveQuery:
	•	Queue route: filtered view over callsCollection.
	•	Detail route: join over calls + grades + transcript collections.
	7.	Add optimistic onInsert / onUpdate handlers on collections that map to your grading stored procedures.
	8.	Later, if needed, add:
	•	Predicate push‑down (via meta and parseLoadSubsetOptions) when you want backend filtering to match DB indices.  
	•	WebSocket/SignalR/Electric style incremental updates using collection.utils.writeInsert/writeUpdate for near real‑time dashboards.  

⸻

If you’d like, next step we can zoom into one concrete route (say, “My grading queue”) and I can sketch the full path end‑to‑end: stored procedure → server fn → query collection → useLiveQuery UI.