Database (Drizzle ORM)

This package provides a Drizzle ORM implementation of our database schema. Drizzle is a TypeScript ORM that offers excellent type safety and performance.

Why You Need This

Having a typed schema and migrations in one place prevents data drift and lets you access tables with autocompletion. For background on our monorepo design see Monorepo Foundations.

Features

Complete schema definitions mirroring our legacy Prisma schema.
Type-safe query building with Drizzle ORM.
Zod validation schemas generated for all models, located in @repo/database/zod.
Relation definitions for all tables, enabling easy data fetching across related entities.

Usage

Basic Usage

Querying and inserting data is straightforward with Drizzle's API.

import { db, users } from '@repo/database';

// Query all users
const allUsers = await db.select().from(users);

// Insert a new user
const newUser = await db.insert(users).values({
  id: 'user_123', // Usually auto-generated if using defaultRandom()
  name: 'John Doe',
  email: 'john@example.com',
  emailVerified: true, // Assuming verification status
  createdAt: new Date(),
  updatedAt: new Date(),
  // ... other required fields based on the schema
})
.returning(); // Returns the inserted user data

console.log(newUser);

Querying with Relations

Drizzle makes it easy to query related data using the with property in db.query.

import { db, users, blogPosts } from '@repo/database';
import { eq } from 'drizzle-orm';

// Query a specific user and their blog posts
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.email, 'john@example.com'),
  with: {
    // Assuming 'posts' is the relation name defined in usersRelations
    // Check `packages/database/src/schema/user.ts` for exact relation names
    // This example assumes a relation named 'posts' exists on the user schema
    // If not, this specific query needs adjustment based on actual relations.
    // Example: If the relation is from posts to users, query posts instead.
  },
});

console.log(userWithPosts);

Note: The exact relation name (posts in this example) depends on how relations are defined in your schema files (e.g., usersRelations in user.ts). Adapt the query based on your specific relation definitions.

Using Zod Validation

Leverage the automatically generated Zod schemas for robust data validation before database operations.

import { insertUserSchema } from '@repo/database/zod';

// Example user input (e.g., from an API request)
const userInput = {
  name: 'Jane Doe',
  email: 'jane@example.com',
  // ... other fields
};

try {
  // Validate the input against the Zod schema
  // Note: This assumes insertUserSchema doesn't require fields like id, createdAt, etc.
  // Adjust validation based on the specific schema requirements.
  const validatedData = insertUserSchema.parse(userInput);

  // Proceed with database insertion using validatedData
  // const newUser = await db.insert(users).values({
  //   ...validatedData,
  //   // Add any other required fields not in userInput if necessary
  //   emailVerified: false, // Example default
  //   createdAt: new Date(),
  //   updatedAt: new Date(),
  // }).returning();

  console.log("User data validated successfully:", validatedData);

} catch (error) {
  // Handle validation errors (e.g., return a 400 response)
  console.error("Validation failed:", error);
}

Commands

The @repo/database package includes several pnpm scripts for database management:

pnpm --filter @repo/database db:generate: Generate SQL migration files based on schema changes in src/schema.
pnpm --filter @repo/database db:migrate: Apply pending migrations to the database. This is the standard way to update the DB.
pnpm --filter @repo/database db:push: (Use with caution!) Push schema changes directly to the database, bypassing migrations. Useful for rapid prototyping but not recommended for production or team environments.
pnpm --filter @repo/database db:studio: Open Drizzle Studio, a GUI tool to view and interact with your database.
pnpm --filter @repo/database db:check: Check if the database schema is synchronized with the Drizzle schema definitions.

Refer to the Development Workflow > Database Workflow documentation for more details on the migration process.

Schema Structure

The database schema is organized into files within packages/database/src/schema/ based on functional domains:

user.ts: User authentication, profiles, sessions, accounts, passkeys.
organizations.ts: Organizations, members, invitations, teams.
api.ts: API keys, usage records, usage limits.
products.ts: Product definitions, pricing information.
customer.ts: Customer billing information (links users to payment providers).
payments.ts: Payment events, accounts, subscriptions, transactions.
coupons.ts: Discount coupons.
blog.ts: Blog posts, categories, tags, comments, assets, analytics, subscribers.
support.ts: Support tickets and messages.
notifications.ts: Push notifications, device tokens, user preferences.
mobile_app.ts: Tables specific to the mobile application (e.g., offline sync).
feature_flags.ts: Feature flag definitions and product associations.
waitlist.ts: Waitlist entries and referral tracking.
roles.ts: Defines PostgreSQL roles used in RLS policies.
index.ts: Exports all schemas, relations, enums, and types.

Database Schema Diagrams

Below are Entity-Relationship Diagrams (ERDs) illustrating the relationships within each schema domain.

Authentication & Users (`user.ts`)

Loading diagram...

This diagram shows the core user authentication tables. The user table holds profile information. It relates to session for tracking login state, account for linking external providers or credentials, passkey for WebAuthn credentials, and verification for processes like email confirmation.

Organizations (`organizations.ts`)

Loading diagram...

This diagram illustrates the multi-tenancy structure. An organization represents a workspace or company. user entities are linked to organizations via the member table, which assigns roles. invitation manages invites to join an organization or specific team.

API Management (`api.ts`)

Loading diagram...

This section details the tables for managing API access and usage. apikey stores API keys associated with users or organizations. usage_record logs individual API calls or resource consumption linked to a key. usage_limit defines quotas or rate limits for users/organizations.

Products (`products.ts`) & Pricing (`prices` in `products.ts`)

Loading diagram...

This diagram shows the product catalog structure. The product table defines the core offerings, while the price table holds specific pricing details (like currency, amount, billing period) linked to a product. It also shows the link to feature_flag via the product_feature_flag join table.

Customer Billing (`customer.ts`) & Subscriptions (`subscription` in `customer.ts`)

Loading diagram...

This diagram focuses on the customer billing relationship. The customer table links a user to their payment provider details. The subscription table tracks a customer's active or past subscriptions to specific product and price plans.

Payment Processing (`payments.ts`)

Loading diagram...

This diagram covers the lower-level payment processing tables. payment_account links a user to payment provider customer details. payment_subscription tracks provider-specific subscription data. payment_transaction logs individual charges or invoice lines. payment_event serves as an immutable log of incoming webhooks from payment providers.

Coupons (`coupons.ts`)

Loading diagram...

This diagram shows the coupon table, used to store discount coupon details, including their provider ID, discount type (amount or percentage), duration, validity, and redemption limits.

Blog & Content (`blog.ts`)

Loading diagram...

This diagram details the blog system. blog_post holds the main content and metadata. blog_category and blog_tag provide organization through many-to-many relationships. blog_comment allows user interaction. blog_asset manages uploaded media. blog_post_audit tracks changes.

Support & Tickets (`support.ts`)

Loading diagram...

This diagram outlines the support ticketing system. Users create ticket entries. Communication within a ticket is stored in the message table, linked to both the ticket and the user who sent it. Tickets can be assigned to specific users (support agents).

Notifications (`notifications.ts`)

Loading diagram...

This diagram shows the notification system tables. push_notification stores individual notification details sent to users. device_token registers user devices (mobile/web) for receiving push notifications. notification_preference allows users to control their notification settings.

Mobile App (`mobile_app.ts`)

Loading diagram...

This diagram represents tables specific to mobile app functionality. The offline_sync table is designed to queue database changes made offline on a mobile device, allowing them to be synchronized with the server when connectivity is restored.

Feature Flags (`feature_flags.ts`)

Loading diagram...

This diagram shows the feature flagging mechanism. feature_flag defines available flags. The product_feature_flag table links these flags to specific product entries, enabling features based on the purchased product.

Waitlist (`waitlist.ts`)

Loading diagram...

This diagram describes the waitlist system. The waitlist_entry table captures signups, including email, name, status, and optional referral information (linking back to another waitlist_entry via referred_by). Entries can be linked to a user once they are invited or sign up.

Row-Level Security (RLS)

The database schema includes Row-Level Security (RLS) policies defined using pgPolicy in the schema files (e.g., src/schema/user.ts). These policies restrict data access based on the authenticated user's role and ID.

The `rlsDb` Client Helper

To facilitate querying with RLS, the @repo/database package exports a special client helper named rlsDb (defined in packages/database/src/index.ts). This helper is crucial when you need to perform database operations that must respect the permissions of the currently authenticated user.

How it Works:

You call rlsDb.rls(userId, userRole, callback). You must provide the current user's ID and their role (e.g., 'user', 'admin', 'support').
The .rls() method initiates a database transaction.
Inside this transaction, it automatically sets the LOCAL ROLE to the appropriate PostgreSQL role (authenticated, admin, or support) based on the userRole you provided.
It also sets the request.jwt.claims session variable, embedding the userId and mapped role. This is commonly used by RLS policies, especially those leveraging authUid from drizzle-orm/supabase.
Your callback function is then executed. This function receives the transaction client (tx) as its argument.
Crucially, all database operations within your callback that need to be RLS-aware must use this tx object. Any operations performed using the global db object will bypass the RLS settings established by .rls().

Usage Example

rlsDb is typically used in backend API routes or server functions where you have access to the authenticated user's ID and role.

import { rlsDb, eq, schema } from "@repo/database"; // Assuming schema is exported
import { type User } from "@repo/database/schema/user";

// Example within a Hono API route handler
async function getUserProfileSecurely(
  currentUserId: string,
  currentUserRole: string, // e.g., 'user', 'admin'
  requestedUserId: string
): Promise<User | null> {
  try {
    // Use rlsDb.rls() to ensure RLS policies are applied
    const userProfile = await rlsDb.rls(
      currentUserId,   // ID of the user making the request
      currentUserRole, // Role of the user making the request
      async (tx) => {
        // --- Use the 'tx' object for all queries inside this callback ---

        // This query respects the RLS policies defined in `schema/user.ts`
        // based on the currentUserId and currentUserRole set by rlsDb.rls().
        const result = await tx.query.user.findFirst({
          where: eq(schema.user.id, requestedUserId),
        });
        return result;
      }
    );

    return userProfile ?? null;

  } catch (error) {
    console.error("Error fetching user profile with RLS:", error);
    // Handle potential errors (e.g., invalid role, DB connection issues)
    return null;
  }
}

// --- Example Scenarios ---

// Scenario 1: Regular user ('user-123', role 'user') requests their own profile.
// RLS Policy Example: `pgPolicy('users_view_own_policy', { using: eq(table.id, authUid) })`
// Result: The query inside rlsDb.rls() succeeds, returning user-123's profile.
// const ownProfile = await getUserProfileSecurely('user-123', 'user', 'user-123');

// Scenario 2: Regular user ('user-123', role 'user') requests another user's profile ('user-456').
// RLS Policy Example: The 'users_view_own_policy' fails.
// Result: The query inside rlsDb.rls() returns no data (or `undefined`), resulting in `null`.
// const otherProfile = await getUserProfileSecurely('user-123', 'user', 'user-456');

// Scenario 3: Admin user ('admin-789', role 'admin') requests any user's profile ('user-123').
// RLS Policy Example: `pgPolicy('users_view_admin_policy', { to: adminRole, using: sql`true` })`
// Result: The query inside rlsDb.rls() succeeds, returning user-123's profile.
// const profileByAdmin = await getUserProfileSecurely('admin-789', 'admin', 'user-123');

Key Points for RLS:

Use rlsDb.rls() whenever database operations must respect the current user's specific permissions as defined by RLS policies.
Always provide the correct userId and userRole (matching roles defined in schema/user.ts or mapped in rlsDb).
Perform all database operations needing RLS protection using the tx object provided within the rlsDb.rls() callback.
RLS policies defined with pgPolicy in the schema files (src/schema/*) are the source of truth for access control and are automatically enforced within the .rls() transaction.
The standard db object typically operates with higher privileges (like the database's service role) and bypasses user-specific RLS policies. Use it only when RLS constraints should not apply (e.g., internal system operations).

Transactions

For operations requiring atomicity (all succeed or all fail together), use Drizzle's standard transaction mechanism with the db client (or the tx client if within an RLS context):

import { db, users, accounts, eq } from "@repo/database";

try {
  await db.transaction(async (tx) => {
    // Perform multiple operations within the transaction using 'tx'
    const updatedUser = await tx.update(users)
      .set({ name: "Updated Name" })
      .where(eq(users.id, 'user-id-to-update'))
      .returning();

    const deletedAccount = await tx.delete(accounts)
      .where(eq(accounts.userId, 'user-id-to-update'))
      .returning();

    // If any operation within this block fails, the entire transaction rolls back.
  });
  console.log("Transaction successful!");
} catch (error) {
  console.error("Transaction failed:", error);
  // Handle the error appropriately
}

Database (Drizzle ORM)

Database (Drizzle ORM)

Why You Need This

Features

Usage

Basic Usage

Querying with Relations

Using Zod Validation

Commands

Schema Structure

Database Schema Diagrams

Authentication & Users (user.ts)

Organizations (organizations.ts)

API Management (api.ts)

Products (products.ts) & Pricing (prices in products.ts)

Customer Billing (customer.ts) & Subscriptions (subscription in customer.ts)

Payment Processing (payments.ts)

Coupons (coupons.ts)

Blog & Content (blog.ts)

Support & Tickets (support.ts)

Notifications (notifications.ts)

Mobile App (mobile_app.ts)

Feature Flags (feature_flags.ts)

Waitlist (waitlist.ts)

Row-Level Security (RLS)

The rlsDb Client Helper

Usage Example

Transactions

Authentication & Users (`user.ts`)

Organizations (`organizations.ts`)

API Management (`api.ts`)

Products (`products.ts`) & Pricing (`prices` in `products.ts`)

Customer Billing (`customer.ts`) & Subscriptions (`subscription` in `customer.ts`)

Payment Processing (`payments.ts`)

Coupons (`coupons.ts`)

Blog & Content (`blog.ts`)

Support & Tickets (`support.ts`)

Notifications (`notifications.ts`)

Mobile App (`mobile_app.ts`)

Feature Flags (`feature_flags.ts`)

Waitlist (`waitlist.ts`)

The `rlsDb` Client Helper