Buildstory uses Neon Postgres with Drizzle ORM. This page documents all tables, fields, enums, and relationships.
Schema Location
The authoritative schema is defined in lib/db/schema.ts.
Enums
experience_level
User’s experience level with building AI projects.
getting_started — New to building with AIbuilt_a_few — Some experience shipping AI projectsships_constantly — Regular AI builder/shipper
event_status
Lifecycle status of an event.
draft — Event created but not visible to usersopen — Registration is openactive — Event is in progressjudging — Event ended, judging in progresscomplete — Event fully complete
team_preference
User’s team status for an event.
solo — Building alonehas_team — Already has a team (closed)has_team_open — Has a team but open to more memberslooking_for_team — Looking for teammates
starting_point
Whether a project is new or continues existing work.
new — Brand new projectexisting — Continuing an existing project
commitment_level
User’s availability commitment for an event.
all_in — Full-time commitmentdaily — Daily progressnights_weekends — Part-time availabilitynot_sure — Uncertain availability
user_role
User’s role in the platform.
user — Default user rolemoderator — Can hide and ban usersadmin — Full platform access
invite_status
Status of a team invite.
pending — Awaiting responseaccepted — Invite accepteddeclined — Invite declinedrevoked — Invite revoked by sender
invite_type
Type of team invite.
direct — Direct invite to a specific user (has recipientId)link — Shareable link invite (has token)
mentor_application_status
Status of a mentor application.
pending — Awaiting reviewapproved — Application approveddeclined — Application declined
Status of a sponsorship inquiry.
pending — Awaiting reviewcontacted — Initial contact madeaccepted — Sponsorship accepteddeclined — Sponsorship declined
Core Tables
profiles
User profiles linked to Clerk authentication.
Primary key (auto-generated)
Unique username (nullable until onboarding complete)
Avatar image URL (from Clerk)
Twitter/X handle (without @)
ISO 3166-1 alpha-2 country code (uppercase)
ISO 3166-2 subdivision code
role
user_role
default:"user"
required
User’s platform role
allowInvites
boolean
default:true
required
Privacy toggle for team invites
discordCardDismissed
boolean
default:false
required
Whether user dismissed Discord CTA card
Ban timestamp (null if not banned)
Profile ID of admin who banned user
Soft-hide timestamp (null if not hidden)
Profile ID of moderator who hid user
Profile creation timestamp
Last update timestamp (auto-updated)
Show Indexes & Constraints
- Unique index on
clerkId
- Unique index on
username
type Profile = typeof profiles.$inferSelect;
type NewProfile = typeof profiles.$inferInsert;
events
Hackathons and other building events.
Primary key (auto-generated)
URL-safe unique identifier
Show Indexes & Constraints
eventRegistrations
Links profiles to events with team preferences.
Primary key (auto-generated)
Foreign key to profiles.id
User’s availability commitment
Show Indexes & Constraints
- Unique constraint on
(eventId, profileId) — one registration per user per event
projects
User-created projects.
Primary key (auto-generated)
Foreign key to profiles.id (project owner)
URL-safe unique identifier (nullable)
Whether project is new or existing
Project creation timestamp
Last update timestamp (auto-updated)
Show Indexes & Constraints
eventProjects
Junction table linking projects to events.
Primary key (auto-generated)
Foreign key to projects.id
Show Indexes & Constraints
- Unique constraint on
(eventId, projectId) — project can be submitted to an event once
teamInvites
Team invitations for projects.
Primary key (auto-generated)
Foreign key to projects.id
Foreign key to profiles.id (invite sender)
Foreign key to profiles.id (nullable for link invites)
Invite type (direct or link)
status
invite_status
default:"pending"
required
Invite status
Unique token for link-type invites (nullable)
Invite creation timestamp
Show Indexes & Constraints
- Unique index on
token
- Composite index:
(recipientId, type, status)
- Composite index:
(senderId, status)
- Composite index:
(projectId, status)
- Link invites use
crypto.randomUUID() for tokens
- Max 5 pending invites per sender (
MAX_PENDING_INVITES)
- Direct invites require recipient to have
allowInvites = true
projectMembers
Project team members.
Primary key (auto-generated)
Foreign key to projects.id
Foreign key to profiles.id
Foreign key to teamInvites.id (nullable)
Show Indexes & Constraints
- Unique constraint on
(projectId, profileId) — user can join a project once
Admin Tables
adminAuditLog
Audit log for admin actions.
Primary key (auto-generated)
Foreign key to profiles.id (admin who performed action)
Action type (e.g., “ban_user”, “set_role”)
Foreign key to profiles.id (user affected, nullable)
JSON-encoded action metadata (nullable)
ban_user — User banned (metadata: { reason })unban_user — User unbannedhide_user — User hidden from public listingsunhide_user — User unhiddenset_role — User role changed (metadata: { oldRole, newRole })delete_user — User deleted (metadata: { deletedProfileId, displayName, username, clerkId })
mentorApplications
Mentor application submissions.
Primary key (auto-generated)
Array of mentor types: design, technical, growth
status
mentor_application_status
default:"pending"
required
Application status
Foreign key to profiles.id (admin who reviewed)
Application submission timestamp
Last update timestamp (auto-updated)
Show Indexes & Constraints
Additional Tables
Sponsorship inquiry submissions.
Primary key (auto-generated)
Sponsorship offer description
status
sponsorship_inquiry_status
default:"pending"
required
Inquiry status
Foreign key to profiles.id
Inquiry submission timestamp
Last update timestamp (auto-updated)
twitchCategories
Twitch game/category tracking.
Primary key (auto-generated)
Twitch category ID (unique)
Foreign key to profiles.id
prizeDraws
Prize draw results.
Primary key (auto-generated)
Random seed used for draw
JSON array of winner data
Number of winners selected
Total eligible participants
Algorithm used for selection
Foreign key to profiles.id (admin who ran draw)
Record creation timestamp
users (Legacy)
Legacy user table (retained for compatibility).
Primary key (auto-increment)
Relationships
Profile Relations
profiles.eventRegistrations → many eventRegistrationsprofiles.projects → many projects (owned)profiles.sentInvites → many teamInvites (as sender)profiles.receivedInvites → many teamInvites (as recipient)profiles.projectMemberships → many projectMembers
Event Relations
events.eventRegistrations → many eventRegistrationsevents.eventProjects → many eventProjects
Project Relations
projects.profile → one profiles (owner)projects.eventProjects → many eventProjectsprojects.members → many projectMembersprojects.invites → many teamInvites
Admin Relations
adminAuditLog.actor → one profilesadminAuditLog.target → one profiles (nullable)mentorApplications.reviewer → one profiles (nullable)
Migration Workflow
- Modify schema in
lib/db/schema.ts
- Generate migration:
npm run db:generate
- Review generated SQL in
drizzle/migrations/
- Test against dev branch:
npm run db:migrate
- Commit migration file
- CI automatically runs migrations on production after PR merge
Never use db:push in production. All schema changes must go through migration files.