Database Schema

The schema is TypeScript. lib/db/schema.ts is the canonical entry point — drizzle.config.ts points drizzle-kit at it — and it re-exports lib/db/auth-schema.ts. Your own tables go in lib/db/schema.ts.

lib/db/auth-schema.ts is generated by bun run auth:schema from the Better Auth config in lib/auth.ts. Changes to the shape of the database are expressed as SQL migrations under drizzle/.

export default defineConfig({
    schema: "./lib/db/schema.ts",
    out: "./drizzle",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL!,
    },
});

The schema is convex/schema.ts, which spreads in the tables from convex/betterAuth/authSchema.ts and layers additional indexes on top. Tables are declared with defineTable and v.* validators, and indexes with .index("by_x", [...]).

The auth tables live in your main Convex database rather than behind a separate Convex Component, so your functions can reference users, sessions and organizations directly with no component layer in between. The Better Auth integration is kitcn, imported as kitcn/auth.

import { defineSchema } from "convex/server";
import { tables } from "./betterAuth/authSchema";

export default defineSchema({
    ...tables,
    session: tables.session.index("userId_expiresAt", ["userId", "expiresAt"]),
});

The Drizzle client in lib/db/index.ts is created with the full schema, so relational queries are available on db.query.*:

import { eq } from "drizzle-orm";
import { db } from "@/lib/db";
import { user } from "@/lib/db/schema";

export async function getUser(userId: string) {
    return await db.query.user.findFirst({
        where: eq(user.id, userId),
    });
}

Outside the app, any Postgres client works — psql, or a GUI — pointed at DATABASE_URL.

Because the auth tables are in the main database, query them like any other table. Rows are keyed by the Convex document id:

import { v } from "convex/values";
import { query } from "./_generated/server";

export const getUser = query({
    args: { userId: v.id("user") },
    handler: async (ctx, args) => {
        return await ctx.db.get(args.userId);
    },
});

To look up by a field instead, use one of the declared indexes rather than .filter(), which scans the table:

const row = await ctx.db
    .query("user")
    .withIndex("email_name", (q) => q.eq("email", args.email))
    .first();

Outside the app, browse and edit rows in the Convex dashboard's data browser.

Add your tables to lib/db/schema.ts, next to the re-export of ./auth-schema — never to auth-schema.ts itself, which is overwritten on every regeneration.

import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { user } from "./auth-schema";

export * from "./auth-schema";

export const project = pgTable("project", {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    ownerId: text("owner_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    organizationId: text("organization_id"),
    createdAt: timestamp("created_at", { withTimezone: true, mode: "date" })
        .defaultNow()
        .notNull(),
});

Then generate and apply a migration — see Applying Schema Changes.

Add your tables to convex/schema.ts, alongside the spread auth tables:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";
import { tables } from "./betterAuth/authSchema";

export default defineSchema({
    ...tables,

    projects: defineTable({
        name: v.string(),
        description: v.optional(v.string()),
        ownerId: v.id("user"),
        organizationId: v.optional(v.id("organization")),
        createdAt: v.number(),
    })
        .index("by_owner", ["ownerId"])
        .index("by_organization", ["organizationId"]),
});

Two steps: generate a migration from the schema, then apply it.

bun run db:generate      # drizzle-kit generate — writes a SQL migration into drizzle/
bun run db:migrate       # drizzle-kit migrate — applies pending migrations to DATABASE_URL

Migration files are always scaffolded by the Drizzle CLI and never created by hand. Hand-written SQL bodies are permitted, but only for DDL that drizzle-kit cannot express — triggers and functions — and only via:

bunx drizzle-kit generate --custom --name=member_owner_guard

That writes the journal entry and an empty .sql file for you to fill in. Two of the five migrations shipped in drizzle/ are exactly that: trigger DDL enforcing that an organization can never be left with zero owners.

set -a && . ./.env.local && set +a && bunx drizzle-kit migrate

After changing the Better Auth config in lib/auth.ts, regenerate the auth tables before generating the migration:

bun run auth:schema
bun run db:generate
bun run db:migrate

The header of lib/db/auth-schema.ts lists compound indexes marked port: that have to be re-applied after each regeneration — do that before db:generate, or the migration will drop them.

There is no separate migrate step. bun convex dev pushes schema changes to your development deployment as you save them, and convex deploy pushes them to production as part of the deploy.

convex/betterAuth/authSchema.ts is generated — regenerate it with bun scripts/generate-auth-schema.ts after changing the Better Auth config in convex/betterAuth/auth.ts. Keep your extra indexes in convex/schema.ts, layered onto the spread tables, so a regeneration cannot wipe them.

Declare a relation next to the table, then pull the related row in one query with with:

export const projectRelations = relations(project, ({ one }) => ({
    owner: one(user, {
        fields: [project.ownerId],
        references: [user.id],
    }),
}));

const row = await db.query.project.findFirst({
    where: eq(project.id, projectId),
    with: { owner: true },
});

Convex has no join. Fetch the document, then fetch what it references:

export const getProjectWithOwner = query({
    args: { projectId: v.id("projects") },
    handler: async (ctx, args) => {
        const project = await ctx.db.get(args.projectId);
        if (!project) return null;

        const owner = await ctx.db.get(project.ownerId);

        return { ...project, owner };
    },
});

• Drizzle ORM Documentation
• drizzle-kit migrations

• Convex Schema Documentation
• Convex Components — why the auth tables are not one
• kitcn — the Better Auth + Convex integration