Database Architecture

SavvySolve uses Neon serverless Postgres as its database, accessed through Drizzle ORM. This combination provides the scalability needed for a DoorDash-style on-demand platform while maintaining complete type safety from database to frontend.

Why Neon + Drizzle?

The PRD specifies Neon for its serverless architecture—the database scales automatically and charges per-use rather than per-hour. This matters for an on-demand platform where traffic is unpredictable. A quiet Tuesday morning shouldn't cost the same as a busy Saturday afternoon.

Drizzle was chosen over heavier ORMs like Prisma for its lightweight footprint and true SQL transparency. Every query you write maps directly to SQL, making performance predictable and debugging straightforward.

Connection Strategy

Serverless environments present a unique challenge: each request might spin up a new execution context, potentially exhausting database connections. Neon solves this with a connection pooler that sits between your application and the database.

lib/db/connection.ts

import {
  neon,
  neonConfig,
  Pool,
  type PoolClient,
} from "@neondatabase/serverless";

// Configure Neon for serverless environments
neonConfig.fetchConnectionCache = true;

// HTTP-based SQL query function (lowest latency for single queries)
export const sql = neon(process.env.DATABASE_URL!);

// WebSocket-based connection pool (for transactions)
export const pool = new Pool({ connectionString: process.env.DATABASE_URL });

The codebase provides two connection modes. The sql function uses HTTP to execute single queries with minimal latency—perfect for simple reads. The pool provides WebSocket-based connections needed for transactions where multiple queries must succeed or fail together.

Environment Configuration

Two connection strings serve different purposes. The pooled connection (DATABASE_URL) routes through Neon's connection pooler and is used for all application queries. The unpooled connection (DATABASE_URL_UNPOOLED) bypasses the pooler and is required for running migrations, since DDL statements need direct database access.

.env.local

# Pooled connection for application queries
DATABASE_URL=postgresql://user:pass@ep-xxx-pooler.region.aws.neon.tech/db

# Direct connection for migrations only
DATABASE_URL_UNPOOLED=postgresql://user:pass@ep-xxx.region.aws.neon.tech/db

The pooler endpoint includes -pooler in the hostname. Simply remove it to get the direct connection string.

Drizzle Configuration

Drizzle Kit handles schema migrations. The configuration points to schema files and specifies the output directory for generated SQL migrations.

drizzle.config.ts

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env.local" });

export default defineConfig({
  schema: "./lib/db/schema/*",
  out: "./lib/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL_UNPOOLED!,
  },
  verbose: true,
  strict: true,
});

Note that migrations use the unpooled connection. This is intentional—migration DDL statements require session-level features that connection poolers don't support.

Schema Design

The schema reflects the DoorDash-style model described in the PRD: customers submit tickets, solvers claim them, sessions track the work, and various logs capture communication history.

Core Entities

The schema centers on four primary tables that model the support workflow:

lib/db/schema/users.ts

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  clerkId: varchar("clerk_id", { length: 255 }).notNull().unique(),
  email: varchar("email", { length: 255 }).notNull(),
  name: varchar("name", { length: 255 }),
  phone: varchar("phone", { length: 50 }),
  role: userRoleEnum("role").notNull().default("customer"),
  stripeCustomerId: varchar("stripe_customer_id", { length: 255 }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

Users sync from Clerk via webhook—the clerkId links the database record to Clerk's identity. The role enum distinguishes customers, solvers, and admins.

Solvers extend users with business-specific data:

lib/db/schema/solvers.ts

export const solvers = pgTable("solvers", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().unique().references(() => users.id),
  type: solverTypeEnum("type").notNull().default("general"),
  status: solverStatusEnum("status").notNull().default("onboarding"),
  stripeConnectId: varchar("stripe_connect_id", { length: 255 }),
  revenueSharePercent: integer("revenue_share_percent").notNull().default(70),
  stats: jsonb("stats").$type<SolverStats>(),
  googleEmail: varchar("google_email", { length: 255 }),
});

The stats field uses JSONB to store aggregate metrics without requiring schema changes as we add new statistics.

Tickets and Sessions

Tickets represent customer requests. Notably, ticket creation doesn't require authentication—the PRD emphasizes "friction-free entry" where customers can request help immediately without creating an account.

lib/db/schema/tickets.ts

export const tickets = pgTable("tickets", {
  id: uuid("id").primaryKey().defaultRandom(),
  customerInfo: jsonb("customer_info").$type<CustomerInfo>().notNull(),
  description: text("description").notNull(),
  deviceType: deviceTypeEnum("device_type").notNull(),
  urgency: urgencyEnum("urgency").notNull().default("medium"),
  status: ticketStatusEnum("status").notNull().default("pending"),
  solverId: uuid("solver_id").references(() => solvers.id),
  claimedAt: timestamp("claimed_at", { withTimezone: true }),
});

Customer contact information is stored as JSONB directly on the ticket rather than requiring a user account. This supports the flow where someone lands on the marketing page, fills out the intake form, and gets help within minutes—all without signing up.

Sessions link tickets to solvers and track the actual work:

lib/db/schema/sessions.ts

export const sessions = pgTable("sessions", {
  id: uuid("id").primaryKey().defaultRandom(),
  ticketId: uuid("ticket_id").notNull().references(() => tickets.id),
  solverId: uuid("solver_id").notNull().references(() => solvers.id),
  duration: integer("duration"), // seconds
  tier: ticketTierEnum("tier").notNull().default("standard"),
  pricing: jsonb("pricing").$type<SessionPricing>(),
  earnings: integer("earnings"), // cents
  paymentInfo: jsonb("payment_info").$type<PaymentInfo>(),
  startedAt: timestamp("started_at", { withTimezone: true }),
  endedAt: timestamp("ended_at", { withTimezone: true }),
});

Communication Logs

Three tables capture the omnichannel communication described in the PRD:

session_messages: Real-time chat messages within a session
call_logs: Telnyx voice call metadata and recordings
sms_logs: Telnyx SMS messages for notifications and async communication

Each log type stores call and message metadata with flat columns for efficient querying.

Ratings

The ratings table stores customer feedback for completed sessions:

lib/db/schema/ratings.ts

export const ratings = pgTable("ratings", {
  id: uuid("id").primaryKey().defaultRandom(),
  sessionId: uuid("session_id")
    .notNull()
    .unique()
    .references(() => sessions.id),
  solverId: uuid("solver_id")
    .notNull()
    .references(() => solvers.id),
  rating: integer("rating").notNull(), // 1-5 stars
  comment: text("comment"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

Key design decisions:

One rating per session: The unique() constraint on sessionId ensures customers can only rate a session once
Denormalized solverId: While solverId could be derived from the session, storing it directly enables efficient queries for solver rating summaries
Optional comment: Customers can optionally leave written feedback alongside their star rating

When a rating is submitted, the solver's aggregate stats are automatically updated in the solvers.stats JSONB field:

// In the ratings router after inserting a rating
async function updateSolverRating(solverId: string): Promise<void> {
  const result = await db
    .select({
      averageRating: sql<number>`AVG(${ratings.rating})::numeric(3,2)`,
      totalRatings: sql<number>`COUNT(*)`,
    })
    .from(ratings)
    .where(eq(ratings.solverId, solverId));

  // Update solver.stats.averageRating and totalRatings
}

This denormalization trades storage for query speed—displaying a solver's rating in queue listings doesn't require aggregating all their ratings on every request.

Enums

PostgreSQL enums enforce valid values at the database level:

lib/db/schema/enums.ts

export const ticketStatusEnum = pgEnum("ticket_status", [
  "pending",     // Awaiting solver
  "claimed",     // Solver assigned, session not started
  "in_progress", // Active session
  "completed",   // Session finished
  "cancelled",   // Cancelled by customer or solver
  "refunded",    // Payment refunded
]);

export const urgencyEnum = pgEnum("urgency", [
  "low",      // Can wait
  "medium",   // Somewhat urgent
  "high",     // Very urgent
  "critical", // Emergency
]);

Using database-level enums rather than application-level constants catches invalid data before it enters the database.

Relations

Drizzle relations enable the query builder to traverse relationships:

lib/db/schema/relations.ts

export const sessionsRelations = relations(sessions, ({ one, many }) => ({
  ticket: one(tickets, {
    fields: [sessions.ticketId],
    references: [tickets.id],
  }),
  solver: one(solvers, {
    fields: [sessions.solverId],
    references: [solvers.id],
  }),
  messages: many(sessionMessages),
  callLogs: many(callLogs),
  rating: one(ratings),
}));

export const ratingsRelations = relations(ratings, ({ one }) => ({
  session: one(sessions, {
    fields: [ratings.sessionId],
    references: [sessions.id],
  }),
  solver: one(solvers, {
    fields: [ratings.solverId],
    references: [solvers.id],
  }),
}));

This enables queries like:

const sessionWithDetails = await db.query.sessions.findFirst({
  where: eq(sessions.id, sessionId),
  with: {
    ticket: true,
    solver: { with: { user: true } },
    messages: true,
    rating: true,
  },
});

// Get solver's ratings with session details
const solverRatings = await db.query.ratings.findMany({
  where: eq(ratings.solverId, solverId),
  with: {
    session: { with: { ticket: true } },
  },
  orderBy: [desc(ratings.createdAt)],
  limit: 10,
});

Database Client

The main database client combines Drizzle with the Neon HTTP driver:

lib/db/index.ts

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

Import db anywhere to run type-safe queries:

import { db, tickets, eq } from "@/lib/db";

const pendingTickets = await db
  .select()
  .from(tickets)
  .where(eq(tickets.status, "pending"));

Migrations

Drizzle Kit generates SQL migrations from schema changes:

# Generate migration after schema changes
bun run db:generate

# Apply migrations to database
bun run db:migrate

# Push schema directly (development only)
bun run db:push

# Open Drizzle Studio for visual exploration
bun run db:studio

Migrations are stored in lib/db/migrations/ and should be committed to version control. Each migration is a SQL file that can be reviewed before applying.

Seed Data

A seed script populates the database with realistic test data:

bun run db:seed

This creates:

1 admin user
2 solver users with profiles
3 customer users
5 tickets in various states
1 completed session with messages and rating

The seed script is idempotent for the seed data but will fail if run twice due to unique constraints. Clear the database first if re-seeding:

TRUNCATE users, solvers, tickets, sessions, session_messages, ratings CASCADE;

Transaction Support

For operations that must succeed or fail together, use the transaction helper:

lib/db/connection.ts

export async function withTransaction<T>(
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Use this when creating related records that must all exist:

await withTransaction(async (client) => {
  await client.query("INSERT INTO sessions ...");
  await client.query("UPDATE tickets SET status = 'in_progress' ...");
});

File Structure

lib/db/
├── index.ts              # Main db client export
├── connection.ts         # Neon connection utilities
├── migrations/           # Generated SQL migrations
│   ├── 0000_*.sql
│   └── meta/
├── schema/
│   ├── index.ts          # Barrel export
│   ├── enums.ts          # PostgreSQL enums
│   ├── users.ts          # Users table
│   ├── solvers.ts        # Solvers table
│   ├── tickets.ts        # Tickets table
│   ├── sessions.ts       # Sessions table
│   ├── session-messages.ts
│   ├── call-logs.ts
│   ├── sms-logs.ts
│   ├── ratings.ts        # Customer feedback for sessions
│   ├── screen-share-sessions.ts
│   └── relations.ts      # Drizzle relations
scripts/
└── seed.ts               # Database seed script

tRPC API Layer - How the API layer uses the database
Clerk Integration - User sync webhook that populates the users table

Database Architecture

On this page