Tracer Bullet: TanStack DB + ElectricSQL Integration

[imharrisonking]

Goal

Validate the TanStack DB + ElectricSQL integration pattern by establishing an end-to-end data sync pipeline from the Neon Postgres database through Electric Cloud to the TanStack Start web application. This tracer bullet establishes the architectural pattern for real-time, local-first data synchronization that will be used for all future application tables (projects, floor plans, AI conversations).

Minimal Scope

Single table sync only - The user table is used to validate the integration pattern. This provides:

• Minimal schema complexity (existing table, no migrations needed)
• Immediate validation of auth-aware shape proxy pattern
• Clear pattern for adding future tables

What's included:

• tRPC setup for type-safe mutations
• Electric shape proxy routes with session authentication
• TanStack DB collection with Electric sync
• Transaction ID generation for optimistic update confirmation

What's NOT included:

• Additional application tables (projects, floor_plans, etc.)
• Advanced error handling
• Conflict resolution strategies
• Offline-first patterns beyond basic Electric sync
• UI components consuming the collections

Acceptance Criteria

• Dependencies installed (@tanstack/react-db, @tanstack/electric-db-collection, @electric-sql/client, @trpc/server, @trpc/client)
• Infrastructure updated - SyncEngine resource linked to web app in infra/web.ts
• Electric proxy utility created at packages/web/src/lib/electric-proxy.ts
• tRPC server setup created at packages/web/src/lib/trpc.ts with generateTxId helper
• tRPC client created at packages/web/src/lib/trpc-client.ts
• Users tRPC router created at packages/web/src/lib/trpc/users.ts
• tRPC route handler created at packages/web/src/routes/api/trpc/$.ts
• Users shape proxy route created at packages/web/src/routes/api/users.ts
• Zod schemas added to packages/core/src/auth/auth.sql.ts (selectUserSchema, updateUserSchema)
• TanStack DB collection created at packages/web/src/lib/collections.ts with startSync: true
• TypeScript compilation passes (npm run typecheck)
• Manual verification: Authenticated user can see their profile data synced via Electric

Related Feature

This tracer bullet unlocks the implementation of:

• Phase 02: Electric SQL Integration (from docs/STATUS.md)
• Future tables: projects, floor_plans, AI conversations

Additional Context

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                      Browser (Client)                           │
│                                                                 │
│  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐      │
│  │ TanStack DB  │    │   Electric   │    │    tRPC      │      │
│  │  Collection  │───▶│ Shape Client │    │   Client     │      │
│  └──────────────┘    └──────────────┘    └──────────────┘      │
│         │                   │                    │              │
└─────────│───────────────────│────────────────────│──────────────┘
          │                   │                    │
          │    Session Cookie │                    │
          │                   ▼                    ▼
┌─────────│───────────────────────────────────────────────────────┐
│         │           Server (TanStack Start on Lambda)           │
│         │                                                       │
│         │    ┌────────────────────────────────────────────────┐ │
│         │    │              Shape Proxy Routes                │ │
│         │    │              (/api/users, etc)                 │ │
│         │    │                                               │ │
│         │    │  1. Validate session (better-auth)            │ │
│         │    │  2. Add WHERE user_id = current_user          │ │
│         │    │  3. Forward to Electric Cloud                 │ │
│         │    └────────────────────────────────────────────────┘ │
│         │                         │                             │
│         │    ┌────────────────────│────────────────────────────┐│
│         │    │  tRPC Router       ▼                            ││
│         └───▶│  - Validates session                           ││
│              │  - Checks ownership before mutations            ││
│              │  - Returns txid for sync                        ││
│              └─────────────────────────────────────────────────┘│
│                                   │                              │
└───────────────────────────────────│──────────────────────────────┘
                                    │
                   ┌────────────────┴────────────────┐
                   ▼                                 ▼
            ┌──────────┐                      ┌──────────┐
            │ Electric │                      │  Neon    │
            │  Cloud   │◀────────────────────▶│ Postgres │
            └──────────┘                      └──────────┘

Key Decisions

Decision	Choice	Rationale
Schema location	packages/core	Consistent with existing auth schema pattern
Mutation API	tRPC	Type-safe mutations with transaction ID generation
Electric config	SST Resource.SyncEngine	Uses existing infrastructure setup
User visibility	Own profile only	Privacy-first, simpler filtering

File Changes Summary

packages/
├── core/
│   └── src/auth/
│       └── auth.sql.ts             # MODIFY: Add Zod schemas
│
├── web/
│   ├── package.json                # MODIFY: Add TanStack DB + tRPC deps
│   └── src/
│       ├── lib/
│       │   ├── electric-proxy.ts   # NEW: Electric URL handling + proxy
│       │   ├── trpc.ts             # NEW: Server-side tRPC setup
│       │   ├── trpc-client.ts      # NEW: Client-side tRPC client
│       │   └── collections.ts      # NEW: TanStack DB collections
│       │
│       └── routes/api/
│           ├── trpc/$.ts           # NEW: tRPC route handler
│           └── users.ts            # NEW: Electric shape proxy

infra/
└── web.ts                          # MODIFY: Link SyncEngine resource

Pattern for Adding Future Tables

When adding new tables (projects, floor_plans, etc.):

1. Define schema in packages/core/src/[domain]/[domain].sql.ts
2. Add Zod schemas using createSelectSchema, createInsertSchema
3. Create shape proxy route at packages/web/src/routes/api/[table].ts
4. Create tRPC router at packages/web/src/lib/trpc/[table].ts
5. Create collection in packages/web/src/lib/collections.ts
6. Wire router into packages/web/src/routes/api/trpc/$.ts

References

• TanStack DB Installation Docs
• TanStack DB Quick Start
• ElectricSQL TanStack Starter Example
• ElectricSQL Auth Guide

---

From the Pragmatic Programmer: "Tracer bullets let you validate your aim early. Build a tiny slice first, then expand."