codexmachina
registry/nextjs-postgres-clerk-crm

CRM on Next.js 16 (App Router), Postgres (Neon) and Clerk

verified 2026-07-15next16.2.9postgres3.4.9@clerk/nextjs7.5.15@neondatabase/serverless1.1.0

Type-checked against the real SDKs, migration applied to a live Postgres (Neon), connection clients load-tested, then tracked for upstream drift and re-verified when it moves. How we verify

request path
Browserrequest
fetch
Next.js 16 (App Router)routing + proxy
verify
Clerksession
query
Postgres (Neon)pooled

session validation runs in server components and route handlers, not at the edge

What you're getting

Next.js 16 (App Router)

Next.js 16 App Router — file-based routing, server components, and the Edge proxy (Next 16's renamed middleware).

Postgres (Neon)

Postgres on Neon via Drizzle ORM and the postgres-js driver.

Clerk

Clerk — hosted identity (sign-in UI, sessions, user management) mounted via middleware + provider.

CRM

Sales CRM — companies, contacts, named pipelines, deal-stage tracking, and an append-only activity trail.

Setup

bun add next react react-dom drizzle-orm postgres @clerk/nextjs
DATABASE_URLNeon pooled (-pooler) connection string
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY
CLERK_SECRET_KEY
CLERK_WEBHOOK_SECRETsvix secret that verifies Clerk webhook signatures

Apply the schema with bunx drizzle-kit push

Initialization

Database client

src/lib/db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

// Neon pooled endpoint = PgBouncer transaction mode → prepared statements off.
// ponytail: single module-level client; the serverless runtime + PgBouncer do
// the pooling, so no custom pool/globalThis singleton dance needed.
const client = postgres(process.env.DATABASE_URL!, { prepare: false });

export const db = drizzle({ client });

// ponytail: Clerk is hosted — set NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY and
// CLERK_SECRET_KEY in the env. The publishable key is read client-side by
// <ClerkProvider>; the secret key is read server-side by clerkMiddleware().
// Both are picked up from the environment automatically — no wiring needed.
// Clerk proxy for Next.js 16 (App Router) (Next 16 renamed middleware.ts → proxy.ts; clerkMiddleware is still the helper).
import { clerkMiddleware, createRouteMatcher } from "@clerk/nextjs/server";

// ponytail: guard the SaaS app surface; widen the matcher per app-type.
const isProtectedRoute = createRouteMatcher([
  "/dashboard(.*)",
  "/settings(.*)",
]);

export default clerkMiddleware(async (auth, request) => {
  // auth.protect() bounces logged-out users to Clerk's hosted sign-in.
  if (isProtectedRoute(request)) {
    await auth.protect();
  }
});

export const config = {
  // Clerk's documented matcher: skip Next internals + static files unless
  // referenced in search params, and always run on API/tRPC routes.
  matcher: [
    "/((?!_next|[^?]*\\.(?:html?|css|js(?!on)|jpe?g|webp|png|gif|svg|ttf|woff2?|ico|csv|docx?|xlsx?|zip|webmanifest)).*)",
    "/(api|trpc)(.*)",
  ],
};
// Root layout — <ClerkProvider> is required for Next.js 16 (App Router).
import { ClerkProvider } from "@clerk/nextjs";
import type { ReactNode } from "react";

export default function RootLayout({ children }: { children: ReactNode }) {
  return (
    <ClerkProvider>
      <html lang="en">
        <body>{children}</body>
      </html>
    </ClerkProvider>
  );
}

Sales CRM schema: companies, contacts, pipelines, deals & activities

Companies & contacts

account records (companies de-duped on domain) and the contacts that belong to them, with nullable company FK to support unattached leads

Pipelines & deals

named sales pipelines and the opportunities (deals) moving through them, each carrying a contact FK, an owner FK to Better Auth user, and amount stored as integer cents

Deal stage tracking

stage column on deals enforced by CHECK constraint with an index that powers pipeline-board grouping by stage

Activity interaction log

append-only calls/emails/notes logged against a deal by a Better Auth actor, forming the per-deal audit timeline

src/db/schema.ts
// === file: src/db/schema.ts ===
import { relations, sql } from "drizzle-orm";
import {
  bigint,
  check,
  index,
  pgTable,
  text,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";
// Better Auth owns identity; we only reference its `user` table by id.
import { user } from "./auth-schema";

export type DealStage = "lead" | "qualified" | "won" | "lost";
export type ActivityType = "call" | "email" | "note";

/** Account record: the organization a set of contacts belongs to. */
export const companies = pgTable(
  "companies",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    name: text("name").notNull(),
    // Primary web domain — opaque key we de-dupe accounts on.
    domain: text("domain"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [index("idx_company_domain").on(t.domain)],
);

/** A person we sell to. Company is nullable (a lead can exist before we know
 *  their employer); owner is the Better Auth user accountable for the contact. */
export const contacts = pgTable(
  "contacts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    // Nullable: unattached leads get a company once qualified.
    companyId: uuid("company_id").references(() => companies.id, {
      onDelete: "set null",
    }),
    email: text("email").notNull(),
    name: text("name"),
    // Better Auth's user.id is text — match it, don't recast.
    ownerId: text("owner_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index("idx_contact_company").on(t.companyId),
    index("idx_contact_owner").on(t.ownerId),
  ],
);

/** Named sales pipeline deals move through (e.g. Inbound, Enterprise). */
export const pipelines = pgTable("pipelines", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

/** An open/closed opportunity against a contact, in a pipeline, owned by a
 *  Better Auth user. amount in integer cents (no float money). */
export const deals = pgTable(
  "deals",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    contactId: uuid("contact_id")
      .notNull()
      .references(() => contacts.id, { onDelete: "cascade" }),
    pipelineId: uuid("pipeline_id")
      .notNull()
      .references(() => pipelines.id),
    ownerId: text("owner_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    // ponytail: money as integer cents — no float, no numeric type churn.
    amountCents: bigint("amount_cents", { mode: "number" })
      .notNull()
      .default(0),
    stage: text("stage").$type<DealStage>().notNull().default("lead"),
    closeDate: timestamp("close_date", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    // Drives the pipeline board "deals grouped by stage" query.
    index("idx_deal_stage").on(t.stage),
    index("idx_deal_contact").on(t.contactId),
    check(
      "deals_stage_check",
      sql`${t.stage} in ('lead','qualified','won','lost')`,
    ),
  ],
);

/** Append-only interaction trail against a deal, logged by a Better Auth user. */
export const activities = pgTable(
  "activities",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    dealId: uuid("deal_id")
      .notNull()
      .references(() => deals.id, { onDelete: "cascade" }),
    // Who logged the interaction.
    actorId: text("actor_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    type: text("type").$type<ActivityType>().notNull().default("note"),
    body: text("body"),
    occurredAt: timestamp("occurred_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    // Drives the per-deal timeline (most-recent-first).
    index("idx_activity_deal_time").on(t.dealId, t.occurredAt),
    check(
      "activities_type_check",
      sql`${t.type} in ('call','email','note')`,
    ),
  ],
);

export const companiesRelations = relations(companies, ({ many }) => ({
  contacts: many(contacts),
}));

export const contactsRelations = relations(contacts, ({ one, many }) => ({
  company: one(companies, {
    fields: [contacts.companyId],
    references: [companies.id],
  }),
  owner: one(user, { fields: [contacts.ownerId], references: [user.id] }),
  deals: many(deals),
}));

export const pipelinesRelations = relations(pipelines, ({ many }) => ({
  deals: many(deals),
}));

export const dealsRelations = relations(deals, ({ one, many }) => ({
  contact: one(contacts, {
    fields: [deals.contactId],
    references: [contacts.id],
  }),
  pipeline: one(pipelines, {
    fields: [deals.pipelineId],
    references: [pipelines.id],
  }),
  owner: one(user, { fields: [deals.ownerId], references: [user.id] }),
  activities: many(activities),
}));

export const activitiesRelations = relations(activities, ({ one }) => ({
  deal: one(deals, { fields: [activities.dealId], references: [deals.id] }),
  actor: one(user, { fields: [activities.actorId], references: [user.id] }),
}));

Verified identity sync (Clerk)

✓ Clerk users sync into a local user table idempotently: duplicate, out-of-order, and concurrent webhooks converge to one correct row. Replayed against a live database.
src/db/auth-schema.ts
// === file: src/db/auth-schema.ts ===
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";

// Local mirror of Clerk identity — the FK target app-type schemas reference as user.
// id = Clerk's user id, so existing user_id foreign keys resolve once the sync runs.
// This IS the auth-schema slot for Clerk cells: the SaaS schema's ./auth-schema FK
// target (src/db/auth-schema.ts) resolves here, same slot Better Auth's generated file fills.
export const user = pgTable("user", {
  id: text("id").primaryKey(), // = Clerk user id
  email: text("email"),
  firstName: text("first_name"),
  lastName: text("last_name"),
  imageUrl: text("image_url"),
  updatedAt: timestamp("updated_at", { withTimezone: true }), // staleness key (Clerk updated_at)
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

import { and, eq, isNull, lt, or } from "drizzle-orm";
import { user } from "@/db/auth-schema";

export type ClerkUserEvent = {
  type: string;
  data: {
    id: string;
    email_addresses?: { email_address: string }[];
    first_name?: string | null;
    last_name?: string | null;
    image_url?: string | null;
    updated_at?: number;
  };
};

// Idempotent + concurrency-safe sync of a Clerk user into the local user table.
// Keyed on id (= Clerk id, the PK); the staleness guard lives in the UPDATE WHERE so
// a late/older event cannot clobber newer state. user.deleted removes the row.
export async function recordClerkEvent(
  // ponytail: loosely typed Drizzle client so the emitted core stays portable.
  db: any,
  event: ClerkUserEvent,
): Promise<{ changed: boolean }> {
  const d = event.data;
  if (event.type === "user.deleted") {
    const deleted = await db.delete(user).where(eq(user.id, d.id)).returning({ id: user.id });
    return { changed: deleted.length > 0 };
  }

  const email = d.email_addresses?.[0]?.email_address ?? null;
  const eventAt = new Date(d.updated_at ?? 0);
  const fields = {
    email,
    firstName: d.first_name ?? null,
    lastName: d.last_name ?? null,
    imageUrl: d.image_url ?? null,
    updatedAt: eventAt,
  };

  const updated = await db
    .update(user)
    .set(fields)
    .where(and(eq(user.id, d.id), or(isNull(user.updatedAt), lt(user.updatedAt, eventAt))))
    .returning({ id: user.id });
  if (updated.length > 0) return { changed: true };

  const [existing] = await db.select({ id: user.id }).from(user).where(eq(user.id, d.id)).limit(1);
  if (existing) return { changed: false };

  const inserted = await db
    .insert(user)
    .values({ id: d.id, ...fields })
    .onConflictDoNothing({ target: user.id })
    .returning({ id: user.id });
  return { changed: inserted.length > 0 };
}
// Clerk identity webhook for Next.js 16 (App Router). Clerk webhooks are svix — verify the
// signature, then hand the event to the idempotent recordClerkEvent.
import { Webhook } from "svix";
import { db } from "@/lib/db";
import { recordClerkEvent, type ClerkUserEvent } from "@/lib/identity/record";

const USER_EVENTS = new Set(["user.created", "user.updated", "user.deleted"]);

export async function POST(request: Request): Promise<Response> {
  const secret = process.env.CLERK_WEBHOOK_SECRET;
  if (!secret) return Response.json({ error: "Server misconfigured" }, { status: 500 });

  const raw = await request.text();
  const headers = Object.fromEntries(request.headers.entries());

  let event: ClerkUserEvent;
  try {
    event = new Webhook(secret).verify(raw, headers) as ClerkUserEvent;
  } catch {
    return Response.json({ error: "Invalid signature" }, { status: 403 });
  }

  if (USER_EVENTS.has(event.type)) await recordClerkEvent(db, event);
  return Response.json({ ok: true });
}

Deploy targets

✓ The right DB client for where you deploy: load-tested with concurrent queries against a live database. Edge needs the HTTP driver (no TCP); serverless needs a tiny pool.
src/lib/db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

// Serverless: one connection per (short-lived) instance; Neon's pooler multiplexes.
export const sql = postgres(process.env.DATABASE_URL!, { prepare: false, max: 1 });
export const db = drizzle(sql);

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

// Long-running process: a real, reused pool. Still prepare:false on the pooled endpoint.
export const sql = postgres(process.env.DATABASE_URL!, { prepare: false, max: 10, idle_timeout: 20 });
export const db = drizzle(sql);

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";

// Edge/Workers have NO TCP sockets, so postgres-js cannot run here. Neon's HTTP
// driver speaks Postgres over fetch — the only client that works on Workers.
export const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

Decisions and compatibility

note

Auth runs in proxy.ts (Next 16's renamed middleware) on the Edge runtime: it gates on the session cookie's presence only — full session validation happens in Server Components and route handlers, not in the proxy.

note

prepare: false is mandatory — Neon's pooled endpoint is PgBouncer in transaction mode, where server-side prepared statements break across the pool.

note

Drizzle is paired here (not Prisma): Prisma's prepared-statement reliance is incompatible with transaction-mode pooling.

note

Hosted: Clerk owns identity and does NOT create a local `user` table. Store `clerk_user_id` as text without a foreign key, or sync Clerk users into a local table via webhook before relying on FKs to `user`.

note

Deal stage is text + CHECK ('lead','qualified','won','lost') on the deals table — new stages ship without an ALTER TYPE migration; the idx_deal_stage index drives the pipeline board's 'deals grouped by stage' query.

note

activities is append-only (no updates, no deletes cascaded from deal): the per-deal timeline is always a raw log, never a mutated summary, queried via idx_activity_deal_time (deal_id, occurred_at).

caveat

Clerk is a hosted identity provider and does not create a local `user` table. This schema's foreign keys to `user` assume a local identity table (as Better Auth provides). With Clerk, store `clerk_user_id` as a text column without a foreign key, or sync Clerk users into a local `users` table via webhook before relying on these FKs.