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.
Primary key, auto-incrementing integer
User’s email address. Must be unique and not null.
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
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.
Primary key, auto-incrementing integer
createdAt
timestamp
default: "now()"
required
Timestamp when the team was created
updatedAt
timestamp
default: "now()"
required
Timestamp when the team was last updated
Stripe customer ID. Must be unique if set.
Stripe subscription ID. Must be unique if set.
Stripe product ID for the current subscription
Name of the current subscription plan
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.
Primary key, auto-incrementing integer
Foreign key reference to users.id
Foreign key reference to teams.id
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.
Primary key, auto-incrementing integer
Foreign key reference to teams.id
Foreign key reference to users.id. Nullable for system actions.
Description of the action performed (see ActivityType enum)
timestamp
timestamp
default: "now()"
required
Timestamp when the action occurred
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.
Primary key, auto-incrementing integer
Foreign key reference to teams.id
Email address of the invited user
Role the invited user will have upon accepting
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.
Inferred from users table using $inferSelect Inferred from teams table using $inferSelect Inferred from teamMembers table using $inferSelect Inferred from activityLogs table using $inferSelect Inferred from invitations table using $inferSelect 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.
Inferred from users table using $inferInsert. Auto-generated fields are optional.
Inferred from teams table using $inferInsert. Auto-generated fields are optional.
Inferred from teamMembers table using $inferInsert. Auto-generated fields are optional.
Inferred from activityLogs table using $inferInsert. Auto-generated fields are optional.
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 Extended team type with nested member data All fields from the Team type
Array of team members with user information All fields from the TeamMember type
export type TeamDataWithMembers = Team & { teamMembers : ( TeamMember & { user : Pick < User , 'id' | 'name' | 'email' >; })[]; };
Enums ActivityType Defines the possible activity types for audit logging.
Account information update
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 ], }), }));
