Database Schema

Complete database schema definitions using Drizzle ORM with PostgreSQL

Overview

The database schema is built with Drizzle ORM and PostgreSQL. All tables are defined in lib/db/schema.ts with full TypeScript type inference.

Tables

users

Stores user account information including authentication credentials and profile data.

id · serial · required

Primary key, auto-incrementing integer

name · varchar(100)

User’s display name

email · varchar(255) · required

User’s email address. Must be unique and not null.

passwordHash · text · required

Hashed password for authentication

role · varchar(20) · default: "member" · required

User role in the system

createdAt · timestamp · default: "now()" · required

Timestamp when the user account was created

updatedAt · timestamp · default: "now()" · required

Timestamp when the user account was last updated

deletedAt · timestamp

Soft delete timestamp. Null if account is active.

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: text('password_hash').notNull(),
  role: varchar('role', { length: 20 }).notNull().default('member'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
  deletedAt: timestamp('deleted_at'),
});

teams

Stores team/organization information including Stripe subscription data.

id · serial · required

Primary key, auto-incrementing integer

name · varchar(100) · required

Team name

createdAt · timestamp · default: "now()" · required

Timestamp when the team was created

updatedAt · timestamp · default: "now()" · required

Timestamp when the team was last updated

stripeCustomerId · text

Stripe customer ID. Must be unique if set.

stripeSubscriptionId · text

Stripe subscription ID. Must be unique if set.

stripeProductId · text

Stripe product ID for the current subscription

planName · varchar(50)

Name of the current subscription plan

subscriptionStatus · varchar(20)

Current status of the subscription (e.g., ‘active’, ‘canceled’, ‘past_due’)

export const teams = pgTable('teams', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
  stripeCustomerId: text('stripe_customer_id').unique(),
  stripeSubscriptionId: text('stripe_subscription_id').unique(),
  stripeProductId: text('stripe_product_id'),
  planName: varchar('plan_name', { length: 50 }),
  subscriptionStatus: varchar('subscription_status', { length: 20 }),
});

teamMembers

Junction table linking users to teams with role information.

id · serial · required

Primary key, auto-incrementing integer

userId · integer · required

Foreign key reference to users.id

teamId · integer · required

Foreign key reference to teams.id

role · varchar(50) · required

User’s role within the team

joinedAt · timestamp · default: "now()" · required

Timestamp when the user joined the team

export const teamMembers = pgTable('team_members', {
  id: serial('id').primaryKey(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id),
  teamId: integer('team_id')
    .notNull()
    .references(() => teams.id),
  role: varchar('role', { length: 50 }).notNull(),
  joinedAt: timestamp('joined_at').notNull().defaultNow(),
});

activityLogs

Audit log tracking user and team activities.

id · serial · required

Primary key, auto-incrementing integer

teamId · integer · required

Foreign key reference to teams.id

userId · integer

Foreign key reference to users.id. Nullable for system actions.

action · text · required

Description of the action performed (see ActivityType enum)

timestamp · timestamp · default: "now()" · required

Timestamp when the action occurred

ipAddress · varchar(45)

IP address from which the action was performed (supports IPv4 and IPv6)

export const activityLogs = pgTable('activity_logs', {
  id: serial('id').primaryKey(),
  teamId: integer('team_id')
    .notNull()
    .references(() => teams.id),
  userId: integer('user_id').references(() => users.id),
  action: text('action').notNull(),
  timestamp: timestamp('timestamp').notNull().defaultNow(),
  ipAddress: varchar('ip_address', { length: 45 }),
});

invitations

Stores pending team invitations.

id · serial · required

Primary key, auto-incrementing integer

teamId · integer · required

Foreign key reference to teams.id

email · varchar(255) · required

Email address of the invited user

role · varchar(50) · required

Role the invited user will have upon accepting

invitedBy · integer · required

Foreign key reference to users.id of the user who sent the invitation

invitedAt · timestamp · default: "now()" · required

Timestamp when the invitation was sent

status · varchar(20) · default: "pending" · required

Invitation status (e.g., ‘pending’, ‘accepted’, ‘declined’)

export const invitations = pgTable('invitations', {
  id: serial('id').primaryKey(),
  teamId: integer('team_id')
    .notNull()
    .references(() => teams.id),
  email: varchar('email', { length: 255 }).notNull(),
  role: varchar('role', { length: 50 }).notNull(),
  invitedBy: integer('invited_by')
    .notNull()
    .references(() => users.id),
  invitedAt: timestamp('invited_at').notNull().defaultNow(),
  status: varchar('status', { length: 20 }).notNull().default('pending'),
});

TypeScript Types

Select Types

Inferred types for reading from the database.

User · object

Inferred from users table using $inferSelect

Show fields

id · number

name · string | null

email · string

passwordHash · string

role · string

createdAt · Date

updatedAt · Date

deletedAt · Date | null

Team · object

Inferred from teams table using $inferSelect

Show fields

id · number

name · string

createdAt · Date

updatedAt · Date

stripeCustomerId · string | null

stripeSubscriptionId · string | null

stripeProductId · string | null

planName · string | null

subscriptionStatus · string | null

TeamMember · object

Inferred from teamMembers table using $inferSelect

Show fields

id · number

userId · number

teamId · number

role · string

joinedAt · Date

ActivityLog · object

Inferred from activityLogs table using $inferSelect

Show fields

id · number

teamId · number

userId · number | null

action · string

timestamp · Date

ipAddress · string | null

Invitation · object

Inferred from invitations table using $inferSelect

Show fields

id · number

teamId · number

email · string

role · string

invitedBy · number

invitedAt · Date

status · string

export type User = typeof users.$inferSelect;
export type Team = typeof teams.$inferSelect;
export type TeamMember = typeof teamMembers.$inferSelect;
export type ActivityLog = typeof activityLogs.$inferSelect;
export type Invitation = typeof invitations.$inferSelect;

Insert Types

Inferred types for inserting into the database.

NewUser · object

Inferred from users table using $inferInsert. Auto-generated fields are optional.

NewTeam · object

Inferred from teams table using $inferInsert. Auto-generated fields are optional.

NewTeamMember · object

Inferred from teamMembers table using $inferInsert. Auto-generated fields are optional.

NewActivityLog · object

Inferred from activityLogs table using $inferInsert. Auto-generated fields are optional.

NewInvitation · object

Inferred from invitations table using $inferInsert. Auto-generated fields are optional.

export type NewUser = typeof users.$inferInsert;
export type NewTeam = typeof teams.$inferInsert;
export type NewTeamMember = typeof teamMembers.$inferInsert;
export type NewActivityLog = typeof activityLogs.$inferInsert;
export type NewInvitation = typeof invitations.$inferInsert;

Composite Types

TeamDataWithMembers · object

Extended team type with nested member data

Show structure

...Team · Team

All fields from the Team type

teamMembers · array

Array of team members with user information

Show item structure

...TeamMember · TeamMember

All fields from the TeamMember type

user · object

Show fields

id · number

name · string | null

email · string

export type TeamDataWithMembers = Team & {
  teamMembers: (TeamMember & {
    user: Pick<User, 'id' | 'name' | 'email'>;
  })[];
};

Enums

ActivityType

Defines the possible activity types for audit logging.

SIGN_UP · string

User account creation

SIGN_IN · string

User authentication

SIGN_OUT · string

User logout

UPDATE_PASSWORD · string

Password change

DELETE_ACCOUNT · string

Account deletion

UPDATE_ACCOUNT · string

Account information update

CREATE_TEAM · string

New team creation

REMOVE_TEAM_MEMBER · string

Team member removal

INVITE_TEAM_MEMBER · string

Team invitation sent

ACCEPT_INVITATION · string

Team invitation accepted

export enum ActivityType {
  SIGN_UP = 'SIGN_UP',
  SIGN_IN = 'SIGN_IN',
  SIGN_OUT = 'SIGN_OUT',
  UPDATE_PASSWORD = 'UPDATE_PASSWORD',
  DELETE_ACCOUNT = 'DELETE_ACCOUNT',
  UPDATE_ACCOUNT = 'UPDATE_ACCOUNT',
  CREATE_TEAM = 'CREATE_TEAM',
  REMOVE_TEAM_MEMBER = 'REMOVE_TEAM_MEMBER',
  INVITE_TEAM_MEMBER = 'INVITE_TEAM_MEMBER',
  ACCEPT_INVITATION = 'ACCEPT_INVITATION',
}

Relations

Drizzle ORM relations define how tables connect to each other for efficient querying.

Teams Relations

export const teamsRelations = relations(teams, ({ many }) => ({
  teamMembers: many(teamMembers),
  activityLogs: many(activityLogs),
  invitations: many(invitations),
}));

Users Relations

export const usersRelations = relations(users, ({ many }) => ({
  teamMembers: many(teamMembers),
  invitationsSent: many(invitations),
}));

Team Members Relations

export const teamMembersRelations = relations(teamMembers, ({ one }) => ({
  user: one(users, {
    fields: [teamMembers.userId],
    references: [users.id],
  }),
  team: one(teams, {
    fields: [teamMembers.teamId],
    references: [teams.id],
  }),
}));

Invitations Relations

export const invitationsRelations = relations(invitations, ({ one }) => ({
  team: one(teams, {
    fields: [invitations.teamId],
    references: [teams.id],
  }),
  invitedBy: one(users, {
    fields: [invitations.invitedBy],
    references: [users.id],
  }),
}));

Activity Logs Relations

export const activityLogsRelations = relations(activityLogs, ({ one }) => ({
  team: one(teams, {
    fields: [activityLogs.teamId],
    references: [teams.id],
  }),
  user: one(users, {
    fields: [activityLogs.userId],
    references: [users.id],
  }),
}));