LogoBoring Template

Database Schema

Overview of the database schema and types used in the application.

This document provides an overview of the database schemas and types used throughout the application. All database types are automatically inferred using Drizzle's $inferSelect utility, ensuring type safety across the application.

Core Schemas

Users & Authentication

users

Core user information and profiles

auth

Authentication-related tables and sessions
server/drizzle/schema/users.ts
export const users = pgTable("user", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name"),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("email_verified", { mode: "date" }),
  image: text("image"),
  password: text("password"),
  stripeCustomerId: text("stripe_customer_id"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});
 
export const sessions = pgTable("session", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expiresAt: timestamp("expires_at", { mode: "date" }).notNull(),
});

Workspaces

workspaces

Workspace configuration and settings

workspaceMembers

Workspace membership and roles

invitations

Pending workspace invitations
server/drizzle/schema/workspaces.ts
export const workspaces = pgTable("workspace", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  logo: text("logo"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  ownerId: text("owner_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});
 
export const workspaceMembers = pgTable(
  "workspace_member",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id")
      .notNull()
      .references(() => workspaces.id, { onDelete: "cascade" }),
    roleId: text("role_id")
      .notNull()
      .references(() => roles.id),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at"),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.workspaceId] }),
  })
);
 
export const invitations = pgTable("invitation", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  email: text("email").notNull(),
  workspaceId: text("workspace_id")
    .notNull()
    .references(() => workspaces.id, { onDelete: "cascade" }),
  roleId: text("role_id")
    .notNull()
    .references(() => roles.id),
  token: text("token").notNull(),
  pending: boolean("pending").notNull().default(true),
  expired: boolean("expired").notNull().default(false),
  invitedBy: text("invited_by").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  acceptedAt: timestamp("accepted_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});

Permissions & Access Control

permissions

RBAC permissions definitions

roles

User roles and capabilities
server/drizzle/schema/permissions.ts
export const permissions = pgTable("permission", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull().unique(),
  description: text("description"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});
 
export const roles = pgTable("role", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull().unique(),
  description: text("description"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});
 
export const rolePermissions = pgTable(
  "role_permission",
  {
    roleId: text("role_id")
      .notNull()
      .references(() => roles.id, { onDelete: "cascade" }),
    permissionId: text("permission_id")
      .notNull()
      .references(() => permissions.id, { onDelete: "cascade" }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.roleId, t.permissionId] }),
  })
);

Feature Schemas

Notifications

notifications

User notifications

userNotificationSettings

Notification preferences
server/drizzle/schema/notifications.ts
export const notifications = pgTable("notification", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  message: text("message"),
  type: text("type").notNull(),
  read: boolean("read").notNull().default(false),
  link: text("link"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});
 
export const userNotificationSettings = pgTable("user_notification_setting", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  email: boolean("email").notNull().default(true),
  push: boolean("push").notNull().default(true),
  inApp: boolean("in_app").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});

Billing & Subscriptions

subscriptions

Workspace subscription details
server/drizzle/schema/subscriptions.ts
export const subscriptions = pgTable("subscription", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  workspaceId: text("workspace_id")
    .notNull()
    .references(() => workspaces.id, { onDelete: "cascade" }),
  status: text("status").notNull(),
  priceId: text("price_id").notNull(),
  planId: text("plan_id").notNull(),
  currentPeriodStart: timestamp("current_period_start").notNull(),
  currentPeriodEnd: timestamp("current_period_end").notNull(),
  cancelAt: timestamp("cancel_at"),
  canceledAt: timestamp("canceled_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});

Integration

items

Generic items storage
server/drizzle/schema/items.ts
export const items = pgTable("item", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  description: text("description"),
  status: text("status").notNull().default("todo"),
  tags: text("tags").array(),
  dueDate: timestamp("due_date", { mode: "date" }),
  creatorId: text("creator_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  workspaceId: text("workspace_id")
    .notNull()
    .references(() => workspaces.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
});

Type Definitions

The following TypeScript types are automatically inferred from the schema, ensuring type safety throughout the application.

Inferred Types

server/drizzle/types.ts
// Core Types
type UserType = typeof users.$inferSelect;
type WorkspaceType = typeof workspaces.$inferSelect;
type MemberType = typeof workspaceMembers.$inferSelect;
 
// Feature Types
type NotificationType = typeof notifications.$inferSelect;
type SubscriptionType = typeof subscriptions.$inferSelect;
type InvitationType = typeof invitations.$inferSelect;
type PermissionType = typeof permissions.$inferSelect;
type ItemType = typeof items.$inferSelect;

Schema Organization

All schema definitions are organized in separate files under server/drizzle/schema/ and exported through a central index.ts file.

File Structure

server/
└── drizzle/
    ├── migrations/        # Generated migrations
    ├── schema/            # Schema definitions
    │   ├── users.ts
    │   ├── workspaces.ts
    │   ├── permissions.ts
    │   ├── notifications.ts
    │   ├── subscriptions.ts
    │   ├── items.ts
    │   └── index.ts       # Central export file
    ├── types.ts           # Type definitions
    ├── config.ts          # Database configuration
    └── client.ts          # Database client initialization

This modular approach allows for:

  1. Better code organization and maintainability
  2. Easier schema updates and migrations
  3. Clear separation of concerns
  4. Type-safe database operations

Key Features

Type Safety

🔄 Full TypeScript integration with Drizzle ORM

Schema Migrations

📊 Automated migration generation and application

Query Building

🔍 Type-safe query building with autocompletion

Validation

🛡️ Runtime validation with Zod integration

Database Operations

Query Examples

Example: Type-safe queries
// Get a user by ID
const getUser = async (userId: string) => {
  return await db.query.users.findFirst({
    where: eq(users.id, userId),
  });
};
 
// Get workspace members with related user info
const getWorkspaceMembers = async (workspaceId: string) => {
  return await db.query.workspaceMembers.findMany({
    where: eq(workspaceMembers.workspaceId, workspaceId),
    with: {
      user: {
        columns: {
          id: true,
          name: true,
          email: true,
          image: true,
        },
      },
      role: true,
    },
  });
};

Relational Queries

Example: Working with relations
// Define relations
export const userRelations = relations(users, ({ many }) => ({
  workspaces: many(workspaceMembers),
  notifications: many(notifications),
}));
 
export const workspaceRelations = relations(workspaces, ({ one, many }) => ({
  owner: one(users, {
    fields: [workspaces.ownerId],
    references: [users.id],
  }),
  members: many(workspaceMembers),
  items: many(items),
}));
 
// Query with relations
const getUserWithWorkspaces = async (userId: string) => {
  return await db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      workspaces: {
        with: {
          workspace: true,
        },
      },
    },
  });
};

Schema Migrations

When adding new tables or modifying existing ones, make sure to: 1. Update the schema files 2. Generate and run migrations 3. Update type definitions if needed

Migration Commands

Terminal
# Generate new migrations
npm run db:generate
 
# Apply migrations to the database
npm run db:migrate
 
# Check migration status
npm run db:status

Migration Process

  1. Update your schema files with new tables or columns
  2. Run npm run db:generate to create a new migration file
  3. Review the generated SQL in the migration file
  4. Apply the migration with npm run db:migrate
  5. Update any affected type definitions or queries

Best Practices

Schema Design

  • Use UUIDs for primary keys
  • Include created_at and updated_at timestamps
  • Define explicit foreign key relationships
  • Use appropriate PostgreSQL column types

Type Safety

  • Use inferred types from schema definitions
  • Create explicit type definitions for complex objects
  • Validate user input with Zod before database operations
  • Avoid manual type assertions when possible

Query Construction

  • Use the query builder for type-safe queries
  • Implement pagination for large data sets
  • Use transactions for related operations
  • Handle database errors appropriately

Additional Resources

Drizzle ORM Documentation

Complete documentation for Drizzle ORM

PostgreSQL Documentation

PostgreSQL database documentation

TypeScript Types

See the tRPC documentation for information on using these types with the API

tRPC

Type-safe API calls with end-to-end TypeScript safety using tRPC and React Query.

Utility Functions

A collection of utility functions for common tasks like styling, routing, encryption, and string manipulation.

On this page