Database Schema

Complete database schema reference for FTB Hustle including all tables, fields, and relationships

Database Schema

FTB Hustle uses PostgreSQL with Drizzle ORM for type-safe database operations. The schema is defined in lib/schema.ts and includes tables for users, opportunities, mentorships, toolkits, and more.

Overview

The database schema is organized into the following categories:

• Authentication & Users: Core user management and authentication
• Opportunities & Internships: Job and opportunity listings
• Mentorship: Mentor profiles and related data
• Toolkits & Monetization: Premium content and payment processing
• Community: User interactions, feedback, and content
• Tracking: Application tracking and calendar events

Enums

The schema defines several enums for type safety:

// User roles
export const userRoleEnum = pgEnum("user_role", ["user", "member", "admin"]);

// User persona types
export const personaEnum = pgEnum("persona_type", ["student", "society"]);

// Opportunity types
export const opportunityTypeEnum = pgEnum("opportunity_type", [
  "hackathon",
  "grant",
  "competition",
  "ideathon",
]);

// Toolkit content types
export const toolkitContentItemTypeEnum = pgEnum("toolkit_content_item_type", [
  "article",
  "video",
]);

// Ungatekeep post tags
export const ungatekeepTagEnum = pgEnum("ungatekeep_tag", [
  "announcement",
  "company_experience",
  "resources",
]);

Tables

Relationships Diagram

user (central table)
├── session (cascade)
├── account (cascade)
├── userOnboardingProfiles (cascade)
├── mentors (cascade)
├── opportunities (cascade)
│   ├── comments (cascade)
│   └── bookmarks (cascade)
├── internships (cascade)
├── comments (cascade)
├── bookmarks (cascade)
├── tasks (cascade)
├── feedback (set null)
├── toolkits (cascade)
│   ├── toolkitContentItems (cascade)
│   │   └── userToolkitProgress (cascade)
│   ├── userToolkits (cascade)
│   └── userToolkitProgress (cascade)
├── userToolkits (cascade)
│   └── coupons (set null)
├── userToolkitProgress (cascade)
├── ungatekeepPosts (cascade)
├── newsletterSubscribers (cascade)
├── trackerItems (cascade)
└── trackerEvents (cascade)

Key Features

Soft Deletes

Several tables implement soft deletes using the deletedAt timestamp:

• user
• opportunities
• internships

Soft-deleted records remain in the database but should be filtered out in queries.

Cascade Deletes

Most foreign key relationships use onDelete: "cascade" to automatically clean up related records when a parent record is deleted.

Array Fields

PostgreSQL array types are used for:

• Tags and interests (text[])
• Images (text[])
• Upvoter IDs (text[])
• Highlights and features (text[])

Unique Constraints

Several tables enforce unique constraints:

• user.email
• session.token
• tags.name
• coupons.code
• waitlist.email
• newsletterSubscribers.email
• bookmarks (userId + opportunityId)
• userOnboardingProfiles (userId)
• userToolkitProgress (userId + contentItemId)
• trackerItems (userId + oppId)
• trackerEvents (userId + title + date)

Schema Location

The complete schema is defined in:

lib/schema.ts

Migrations are stored in:

migrations/

For information on managing migrations, see the Migration Management guide.