Database Codebook

Our project uses PostGres SQL DB to serve our frontend with data storage and migrations. This document serves as a guide, or codebook, on what each value and data point is from our DB.

Schema is defined with Drizzle ORM in apps/backend/src/db/schema.ts. Drizzle generates TypeScript types from these table definitions, so the shapes below stay in sync with the code as long as the schema file is the source of truth.

Schema Diagram

Below is the working diagram made for our database schema which is also additionally produced in Lucid Chart.

erDiagram
    user {
        int id PK
        string name
        string andrew_id
        string oidc_subject
        timestamp created_time
    }
    user_preferences {
        int id PK
        int user_id FK
        string preferred_gender_housing
        string year
        string major
        int cooking_frequency
        int gym_frequency
        int productive_around_others
        int needs_alone_time
        int social_frequency
        string_array goals
        string_array accommodations
        string_array preferred_amenities
        timestamp updated_at
    }
    roommate_profile {
        int id PK
        int user_id FK
        boolean is_visible
        enum status
        boolean committed
        string where_from
        string school
        string intended_major
        string preferred_roommate_school
        string assigned_sex
        string pronouns
        string bathroom_preference
        string wake_time
        string sleep_time
        boolean snores
        string morning_prep_time
        string preferred_shower_time
        int neatness
        int volume_preference
        int social_energy
        int party_frequency
        boolean alcohol
        boolean drugs
        json extras
        timestamp updated_at
    }
    dorm {
        int id PK
        string name
        string image_url
        string_array close_buildings
        boolean has_ac
        string ac_details
        string kitchen_description
        string lounge_description
        string bathroom_type
        string bathroom_details
        string_array room_types
        string_array tags
        json photo_gallery
        numeric latitude
        numeric longitude
        timestamp updated_at
    }
    review {
        int id PK
        int user_id FK
        int dorm_id FK
        string body
        int rating_overall
        int rating_amenities
        int rating_room_quality
        int rating_atmosphere
        string lived_year
        string lived_term
        timestamp submitted_at
    }
    connection {
        int id PK
        int user_id FK
        string provider
        string handle
    }
    group {
        string id PK
        timestamp created_time
    }
    membership {
        int id PK
        string group_id FK
        int user_id FK
        enum role
    }
    invitation {
        string id PK
        int sender_id FK
        int receiver_id FK
        string group_id FK
        string message
        enum status
    }
 
    user ||--o| user_preferences : has
    user ||--o| roommate_profile : has
    user ||--o{ review : writes
    dorm ||--o{ review : "reviewed in"
    user ||--o{ connection : has
    user ||--o{ membership : has
    group ||--o{ membership : has
    group ||--o{ invitation : "scoped to"
    user ||--o{ invitation : sends
    user ||--o{ invitation : receives

Note: connection, group, membership, and invitation are not yet defined in apps/backend/src/db/schema.ts or in this documentation as they will be made and used when we create the Roomies.live OpenAPI. They’re included above to match the current lucid chart diagram, but the Tables and TypeScript types sections below only cover the five tables that actually exist in the Drizzle schema currently (user, user_preferences, roommate_profile, dorm, review). Once those four tables are added to schema.ts, this doc will be updated with their column/type details too.

Enums

roommate_status

Backing type for roommate_profile.status.

Tables

user

Core account record. Every other table hangs off of user.id.

user_preferences

One-to-one extension of user holding lifestyle/roommate-matching preferences.

roommate_profile

One-to-one extension of user holding the public-facing roommate-matching profile.

dorm

Reference data for CMU residence halls, shared across all users (not tied to a user_id).

review

User-submitted reviews of a dorm. Many-to-one against both user and dorm.

Relationships

• user (1) -> (1) user_preferences via user_preferences.user_id
• user (1) -> (1) roommate_profile via roommate_profile.user_id
• user (1) -> (many) review via review.user_id
• dorm (1) -> (many) review via review.dorm_id

No relations() helpers are defined in schema.ts yet, so joins are written manually with Drizzle’s query builder rather than the relational query API.

TypeScript types

Each table is a pgTable object, which Drizzle can turn into select (row-as-read) and insert (row-as-write) types via InferSelectModel / InferInsertModel (or the $inferSelect / $inferInsert shorthand). These aren’t hand-written anywhere yet, but adding them alongside the table definitions in schema.ts gives the rest of the app compile-time types for free:

import type { InferInsertModel, InferSelectModel } from "drizzle-orm";
import { userTable, userPreferencesTable, roommateProfileTable, dormTable, reviewTable } from "./schema.ts";

export type User = InferSelectModel<typeof userTable>;
export type NewUser = InferInsertModel<typeof userTable>;

export type UserPreferences = InferSelectModel<typeof userPreferencesTable>;
export type NewUserPreferences = InferInsertModel<typeof userPreferencesTable>;

export type RoommateProfile = InferSelectModel<typeof roommateProfileTable>;
export type NewRoommateProfile = InferInsertModel<typeof roommateProfileTable>;

export type Dorm = InferSelectModel<typeof dormTable>;
export type NewDorm = InferInsertModel<typeof dormTable>;

export type Review = InferSelectModel<typeof reviewTable>;
export type NewReview = InferInsertModel<typeof reviewTable>;

Resulting shapes (all nullable DB columns become T | null in the select type; serial/nullable columns become optional in the insert type):

type User = {
  id: number;
  andrewId: string | null;
  createdTime: Date | null;
  name: string | null;
  oidcSubject: string | null;
};

type UserPreferences = {
  id: number;
  userId: number;
  accommodations: string[] | null;
  cookingFrequency: number | null;
  goals: string[] | null;
  gymFrequency: number | null;
  major: string | null;
  needsAloneTime: number | null;
  preferredAmenities: string[] | null;
  preferredGenderHousing: string | null;
  productiveAroundOthers: number | null;
  socialFrequency: number | null;
  updatedAt: Date | null;
  year: string | null;
};

type RoommateProfile = {
  id: number;
  userId: number;
  alcohol: boolean | null;
  assignedSex: string | null;
  bathroomPreference: string | null;
  committed: boolean | null;
  drugs: boolean | null;
  extras: unknown | null; // json
  intendedMajor: string | null;
  isVisible: boolean | null;
  morningPrepTime: string | null;
  neatness: number | null;
  partyFrequency: number | null;
  preferredRoommateSchool: string | null;
  preferredShowerTime: string | null;
  pronouns: string | null;
  school: string | null;
  sleepTime: string | null;
  snores: boolean | null;
  socialEnergy: number | null;
  status: "searching" | "committed" | "inactive" | null;
  updatedAt: Date | null;
  volumePreference: number | null;
  wakeTime: string | null;
  whereFrom: string | null;
};

type Dorm = {
  id: number;
  acDetails: string | null;
  bathroomDetails: string | null;
  bathroomType: string | null;
  closeBuildings: string[] | null;
  hasAc: boolean | null;
  imageUrl: string | null;
  kitchenDescription: string | null;
  latitude: string | null; // numeric columns come back as strings from postgres-js
  longitude: string | null;
  loungeDescription: string | null;
  name: string | null;
  photoGallery: unknown | null; // json
  roomTypes: string[] | null;
  tags: string[] | null;
  updatedAt: Date | null;
};

type Review = {
  id: number;
  dormId: number;
  userId: number;
  body: string | null;
  livedTerm: string | null;
  livedYear: string | null;
  ratingAmenities: number | null;
  ratingAtmosphere: number | null;
  ratingOverall: number | null;
  ratingRoomQuality: number | null;
  submittedAt: Date | null;
};

A couple of notes worth knowing when consuming these types on the frontend:

• numeric columns (dorm.latitude, dorm.longitude) are typed as string, not number. Drizzle/postgres-js don’t coerce them, to avoid floating-point precision loss. Parse with Number() before doing math.
• json columns (roommate_profile.extras, dorm.photo_gallery) type as unknown unless you supply a generic (json("extras").$type<MyShape>()), so cast/validate before use.
• Almost every non-PK, non-FK column is nullable today. None of the table definitions use .notNull() except for the foreign key columns. Treat every profile/preference/dorm/review field as optional when rendering the frontend.