Database Schema

KitRocket uses Drizzle ORM with Supabase Postgres. All schema files live in src/db/schema/.

Tables overview

Table	File	Purpose
users	src/db/schema/users.ts	User accounts
sessions	src/db/schema/sessions.ts	Active sessions
accounts	src/db/schema/accounts.ts	OAuth provider links
subscriptions	src/db/schema/subscriptions.ts	Payment subscriptions
usage	src/db/schema/usage.ts	AI token tracking (Pro)

users

Stores user accounts. Created on registration (email/password or OAuth).

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

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  emailVerified: timestamp("email_verified"),
  avatar: text("avatar"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Nullable	Description
id	text	No	Primary key (nanoid or uuid)
email	text	No	Unique email address
name	text	No	Display name
email_verified	timestamp	Yes	When email was verified
avatar	text	Yes	Avatar image URL
created_at	timestamp	No	Account creation time
updated_at	timestamp	No	Last profile update

sessions

Stores active user sessions. Managed automatically by Better Auth.

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

export const sessions = pgTable("sessions", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  token: text("token").notNull().unique(),
  expiresAt: timestamp("expires_at").notNull(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Column	Type	Nullable	Description
id	text	No	Primary key
user_id	text	No	Foreign key to users
token	text	No	Unique session token
expires_at	timestamp	No	Session expiration time
ip_address	text	Yes	Client IP address
user_agent	text	Yes	Client user agent string
created_at	timestamp	No	Session creation time

Sessions are cleaned up by Better Auth when they expire.

accounts

Links OAuth providers to user accounts. A single user can have multiple accounts (e.g., Google + GitHub + email/password).

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

export const accounts = pgTable("accounts", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  provider: text("provider").notNull(),
  providerAccountId: text("provider_account_id").notNull(),
  accessToken: text("access_token"),
  refreshToken: text("refresh_token"),
  expiresAt: timestamp("expires_at"),
  password: text("password"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Column	Type	Nullable	Description
id	text	No	Primary key
user_id	text	No	Foreign key to users
provider	text	No	Provider name: "google", "github", "credential"
provider_account_id	text	No	ID from the OAuth provider
access_token	text	Yes	OAuth access token
refresh_token	text	Yes	OAuth refresh token
expires_at	timestamp	Yes	Token expiration time
password	text	Yes	Hashed password (email/password accounts only)
created_at	timestamp	No	Account link creation time

subscriptions

Tracks payment subscriptions. Updated via DodoPayments webhooks.

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

export const subscriptions = pgTable("subscriptions", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  plan: text("plan").notNull(),
  status: text("status").notNull().default("active"),
  dodoCustomerId: text("dodo_customer_id"),
  dodoSubscriptionId: text("dodo_subscription_id"),
  currentPeriodEnd: timestamp("current_period_end"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Column	Type	Nullable	Description
id	text	No	Primary key
user_id	text	No	Foreign key to users
plan	text	No	Plan name: "starter" or "pro"
status	text	No	Status: "active", "cancelled", "past_due", "trialing"
dodo_customer_id	text	Yes	DodoPayments customer ID
dodo_subscription_id	text	Yes	DodoPayments subscription ID
current_period_end	timestamp	Yes	End of current billing period
created_at	timestamp	No	Subscription creation time
updated_at	timestamp	No	Last status change

usage (Pro)

Tracks AI token usage per user. Used for rate limiting and usage-based billing.

import { pgTable, text, integer, timestamp } from "drizzle-orm/pg-core";
import { users } from "./users";

export const usage = pgTable("usage", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  tokensUsed: integer("tokens_used").notNull(),
  model: text("model").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Column	Type	Nullable	Description
id	text	No	Primary key
user_id	text	No	Foreign key to users
tokens_used	integer	No	Total tokens (input + output)
model	text	No	AI model used (e.g., "claude-sonnet-4-20250514")
created_at	timestamp	No	When the usage occurred

Entity relationships

users
  |-- sessions (one-to-many)      A user can have multiple active sessions
  |-- accounts (one-to-many)      A user can link multiple OAuth providers
  |-- subscriptions (one-to-one)  A user has one active subscription (or none)
  |-- usage (one-to-many)         A user has many AI usage records (Pro)

All foreign keys use ON DELETE CASCADE — deleting a user removes all associated records.

Common queries

Get user with subscription

const result = await db
  .select()
  .from(users)
  .leftJoin(subscriptions, eq(users.id, subscriptions.userId))
  .where(eq(users.id, userId));

Get monthly token usage

const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

const result = await db
  .select({ total: sql<number>`sum(${usage.tokensUsed})` })
  .from(usage)
  .where(
    and(
      eq(usage.userId, userId),
      gte(usage.createdAt, thirtyDaysAgo)
    )
  );

Database commands

# Push schema to database (development)
pnpm db:push

# Generate a migration file
pnpm db:generate

# Apply pending migrations
pnpm db:migrate

# Open Drizzle Studio (visual database browser)
pnpm db:studio

Drizzle Studio opens a web UI at https://local.drizzle.studio where you can browse tables, run queries, and edit records.