Database Schema

Our application uses PostgreSQL with Drizzle ORM for type-safe database operations. Here's an overview of the main database schemas and their corresponding TypeScript types.

Core Schemas

Users & Authentication

  • users - Core user information and profiles
  • auth - Authentication-related tables and sessions

Workspaces

  • workspaces - Workspace configuration and settings
  • workspaceMembers - Workspace membership and roles
  • invitations - Pending workspace invitations

Permissions & Access Control

  • permissions - RBAC permissions definitions
  • roles - User roles and capabilities

Feature Schemas

Notifications

  • notifications - User notifications
  • userNotificationSettings - Notification preferences

Billing & Subscriptions

  • subscriptions - Workspace subscription details
  • purchases - Transaction records

Integration

  • githubInvitations - GitHub integration invites
  • items - Generic items storage

Type Definitions

The following TypeScript types are automatically inferred from the schema:

// 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. This modular approach allows for:

  • Better code organization and maintainability
  • Easier schema updates and migrations
  • Clear separation of concerns
  • Type-safe database operations