Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

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 slug UK
        string code
        string name
        enum_array housing_classes
        string image_url
        string_array close_buildings
        numeric latitude
        numeric longitude
        string location_note
        enum_array room_types
        enum_array bathroom_types
        string bathroom_details
        enum ac_level
        string ac_details
        enum kitchen_scope
        string kitchen_description
        enum laundry_location
        string laundry_details
        boolean has_lounge
        string lounge_description
        boolean gym_available
        string gym_details
        enum gender_housing
        boolean wheelchair_accessible
        boolean service_animal_friendly
        boolean ground_floor_rooms
        boolean strobe_alarm
        string_array tags
        json photo_gallery
        json floor_plans
        timestamp updated_at
    }
    campus_building {
        int id PK
        string slug UK
        string code
        string name
        string subtitle
        numeric latitude
        numeric longitude
    }
    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_bathroom_quality
        int rating_common_space_quality
        int rating_noise
        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 six tables that actually exist in the Drizzle schema currently (user, user_preferences, roommate_profile, dorm, campus_building, 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.

ValueMeaning
searchingUser is actively looking for a roommate.
committedUser has locked in a roommate/room situation.
inactiveUser is not currently participating in roommate matching.

Building enums

Backing types for the dorm columns below. Each one mirrors the string enum of the same name in apps/frontend/src/data/buildingTypes.ts, and the values must stay identical in both places (deno task db:seed rejects any building whose value is not in the Postgres enum). What each value means is documented in data-codebook.md.

Postgres enumUsed byValues
room_typedorm.room_types (array)trad_single, trad_double, trad_triple, semi_suite_single, semi_suite_double, semi_suite_triple, semi_suite_double_as_triple, semi_suite_quad, apartment_triple, studio_apartment_single, studio_apartment_double, studio_apartment_triple
bathroom_typedorm.bathroom_types (array)communal, shared_suite, private
ac_leveldorm.ac_levelnone, by_necessity, window, central
laundry_locationdorm.laundry_locationnone, basement, each_floor, in_unit
kitchen_scopedorm.kitchen_scopenone, shared, in_unit
gender_housingdorm.gender_housingco_ed, women_only, men_only, gender_inclusive
housing_classdorm.housing_classes (array)first_year, upperclass

Tables

user

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

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key.
andrew_idtextYesCMU AndrewID for the account.
created_timetimestampYesWhen the account was created.
nametextYesDisplay name.
oidc_subjecttextYesSubject claim from the OIDC identity provider (CMU SSO), used to link the login to this row.

user_preferences

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

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key.
user_idintegerNo (FK -> user.id)Owning user.
accommodationstext[]YesList of accessibility/accommodation needs.
cooking_frequencyintegerYesSelf-reported frequency scale (e.g. times per week).
goalstext[]YesFree-text goals for housing/roommate search.
gym_frequencyintegerYesSelf-reported frequency scale.
majortextYesAcademic major.
needs_alone_timeintegerYesSelf-reported scale of how much alone time is needed.
preferred_amenitiestext[]YesDesired building/room amenities.
preferred_gender_housingtextYesPreferred gender composition for housing.
productive_around_othersintegerYesSelf-reported scale of productivity with others present.
social_frequencyintegerYesSelf-reported social activity scale.
updated_attimestampYesLast time preferences were edited.
yeartextYesClass year (e.g. Freshman, Sophomore).

roommate_profile

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

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key.
user_idintegerNo (FK -> user.id)Owning user.
alcoholbooleanYesWhether the user drinks alcohol.
assigned_sextextYesAssigned sex, used for housing-eligibility matching.
bathroom_preferencetextYesPreferred bathroom arrangement.
committedbooleanYesWhether the user has already committed to a roommate.
drugsbooleanYesWhether the user uses drugs.
extrasjsonYesFree-form additional profile data not modeled as columns.
intended_majortextYesIntended/declared major shown on the profile.
is_visiblebooleanYesWhether the profile is visible in roommate search.
morning_prep_timetextYesHow long the user takes to get ready in the morning.
neatnessintegerYesSelf-reported tidiness scale.
party_frequencyintegerYesSelf-reported partying frequency scale.
preferred_roommate_schooltextYesPreferred school/college affiliation of a roommate.
preferred_shower_timetextYesPreferred time of day to shower.
pronounstextYesUser’s pronouns.
schooltextYesUser’s own school/college affiliation.
sleep_timetextYesTypical bedtime.
snoresbooleanYesWhether the user snores.
social_energyintegerYesSelf-reported social energy scale.
statusroommate_status enumYesOne of searching, committed, inactive.
updated_attimestampYesLast time the profile was edited.
volume_preferenceintegerYesPreferred noise/volume level scale.
wake_timetextYesTypical wake-up time.
where_fromtextYesHometown/origin.

dorm

Reference data for CMU residence halls, shared across all users (not tied to a user_id). One row per entry in apps/frontend/src/data/buildings.json, loaded by migration 0009_dorm_data and by deno task db:seed (see Seeding dorm data). The frontend reads these rows through GET /api/buildings, which nests the columns into the Building shape from data-codebook.md and attaches each dorm’s review summary (reviews.count and the four rating averages).

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key. Other tables reference this.
slugtextNo (unique)Stable URL-safe identifier, the id string from buildings.json (e.g. etower). Frontend URLs depend on it.
ac_detailstextYesDescription of air conditioning setup.
ac_levelac_levelNoLevel of air conditioning.
bathroom_detailstextYesDescription of bathroom facilities.
bathroom_typesbathroom_type[]NoEvery bathroom style the building offers.
close_buildingstext[]YesSlugs of the nearest academic or non-residential campus buildings (see campus_building). Not a foreign key.
codetextNoShort label shown on the map marker (e.g. MOE). Not unique: a dorm can share its code with a campus building (Resnik).
floor_plansjsonNo (default [])Array of FloorPlan (link, description, category, optional virtualTourLink).
gender_housinggender_housingNoGender housing policy.
ground_floor_roomsbooleanNoWhether the building has ground-floor rooms.
gym_availablebooleanNoWhether there is a gym.
gym_detailstextYesDescription of the gym.
has_loungebooleanNoWhether the building has a lounge.
housing_classeshousing_class[]NoWhich students the building houses: first-year, upperclass, or both.
image_urltextYesPrimary/cover image for the dorm.
kitchen_descriptiontextYesDescription of kitchen facilities.
kitchen_scopekitchen_scopeNoWho has access to a kitchen.
latitudenumericYesGeographic latitude.
laundry_detailstextYesDescription of laundry facilities.
laundry_locationlaundry_locationNoWhere laundry is located.
location_notetextYesFreeform note about the building’s location.
longitudenumericYesGeographic longitude.
lounge_descriptiontextYesDescription of lounge/common space.
nametextNoDorm name.
photo_galleryjsonYesArray of GalleryImage (link, description).
room_typesroom_type[]NoRoom configurations offered.
service_animal_friendlybooleanNoAccessibility: service animals allowed.
strobe_alarmbooleanNoAccessibility: strobe fire alarms.
tagstext[]YesFreeform editorial tags (editorialTags in buildings.json).
updated_attimestampYesLast time the dorm record was edited.
wheelchair_accessiblebooleanNoAccessibility: wheelchair accessible.

campus_building

Reference data for academic and other non-residential buildings, shared across all users. One row per entry in apps/frontend/src/data/campusBuildings.json, loaded by migration 0008_building_data and by deno task db:seed, and served by GET /api/campus-buildings. They are the map’s origin picker, the “Distance from” filter options and the targets of dorm.close_buildings.

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key.
slugtextNo (unique)Stable URL-safe identifier, the id string from campusBuildings.json (e.g. baker-hall).
codetextNoShort label shown on the map marker.
latitudenumericNoGeographic latitude (typed as number).
longitudenumericNoGeographic longitude (typed as number).
nametextNoBuilding name.
subtitletextYesDepartment or function, shown under the name in the map’s origin picker.

review

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

ColumnDB typeNullableNotes
idserialNo (PK)Auto-incrementing primary key.
dorm_idintegerNo (FK -> dorm.id)Dorm being reviewed.
user_idintegerNo (FK -> user.id)Author of the review.
bodytextYesFree-text review content, trimmed. The API requires it and allows up to 5000 characters.
lived_termtextYesThe reviewer’s year of school while living there: first-year, sophomore, junior, senior or graduate.
lived_yeartextYesSchool year the reviewer lived there, written like 2024/2025.
rating_amenitiesintegerYesAmenities rating.
rating_bathroom_qualityintegerYesBathroom quality rating. Null on reviews written before the question was added.
rating_common_space_qualityintegerYesCommon space quality rating. Null on reviews written before the question was added.
rating_noiseintegerYesNoise rating, 1 to 5, where 5 is quiet. Null on reviews written before the question was added.
rating_overallintegerYesOverall rating.
rating_room_qualityintegerYesRoom quality rating.
submitted_attimestampYesWhen the review was submitted.

The columns are nullable for older rows, but the review API only saves a review when every field is present and valid: ratings are whole numbers from 1 to 5, and lived_term and lived_year use the values above. The schemas are zod schemas in apps/backend/src/routes/review-input.ts. The database does not enforce these rules, so a row written outside the API can hold anything. Editing a review in the app replaces every field with valid values.

The average of each rating over a dorm’s reviews is returned as reviews.averages in GET /api/buildings, and the building filters rank on those averages, so they follow what students report. The averages are calculated on every request and ignore reviews that have no value for a rating.

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
  • dorm.close_buildings lists campus_building.slug values. It is a plain text[], so the database does not enforce it; deno task db:seed checks it.

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.

Seeding dorm data

apps/backend/scripts/seed-dorms.ts copies every building in apps/frontend/src/data/buildings.json into the dorm table and every entry in campusBuildings.json into the campus_building table. Run it from apps/backend after the migrations have been applied:

deno task db:migrate
deno task db:seed

It is safe to re-run: rows are upserted on slug, so existing dorms are updated in place and keep their id (reviews point at it). Rows that were removed from the JSON files are not deleted. The script validates every enum value against the Postgres enums, and that every closeBuildings entry is a listed campus building, and stops without writing anything if one is wrong.

The migrations carry this data too, so a new database (a local one, a PR preview) comes up populated without running the seed. 0008_building_data inserts the campus buildings and backfills dorm.code and dorm.housing_classes. 0009_dorm_data upserts the 26 dorms from buildings.json as it stood when it was written. It matches on slug, so an existing row keeps its id (reviews point at it) and takes the JSON values, and a dorm that is not in the file is left alone. Migrations run once, when the server starts. A later change to a building (or a new one) reaches a database only through deno task db:seed or a new data migration, because the database is what the app reads.

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). Dorm and NewDorm are already exported from schema.ts (using $inferSelect / $inferInsert). The others aren’t hand-written anywhere yet, but adding them alongside the table definitions 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;
  slug: string;
  acDetails: string | null;
  acLevel: ACLevel; // "none" | "by_necessity" | "window" | "central"
  bathroomDetails: string | null;
  bathroomTypes: BathroomType[];
  closeBuildings: string[] | null;
  floorPlans: FloorPlan[];
  genderHousing: GenderHousing;
  groundFloorRooms: boolean;
  gymAvailable: boolean;
  gymDetails: string | null;
  hasLounge: boolean;
  imageUrl: string | null;
  kitchenDescription: string | null;
  kitchenScope: KitchenScope;
  latitude: number | null; // numeric with { mode: "number" }
  laundryDetails: string | null;
  laundryLocation: LaundryLocation;
  locationNote: string | null;
  longitude: number | null;
  loungeDescription: string | null;
  name: string;
  photoGallery: GalleryImage[] | null; // json
  roomTypes: RoomType[];
  serviceAnimalFriendly: boolean;
  strobeAlarm: boolean;
  tags: string[] | null;
  updatedAt: Date | null;
  wheelchairAccessible: boolean;
};

type Review = {
  id: number;
  dormId: number;
  userId: number;
  body: string | null;
  livedTerm: string | null;
  livedYear: string | null;
  ratingAmenities: number | null;
  ratingBathroomQuality: number | null;
  ratingCommonSpaceQuality: number | null;
  ratingNoise: 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 are typed as string by default, because Drizzle/postgres-js don’t coerce them, to avoid floating-point precision loss. dorm.latitude and dorm.longitude opt in to { mode: "number" }, so they come back as number.
  • json columns type as unknown unless you supply a generic (json("extras").$type<MyShape>()), so cast/validate before use. dorm.photo_gallery and dorm.floor_plans are typed with $type<GalleryImage[]>() and $type<FloorPlan[]>(); roommate_profile.extras is still unknown.
  • Almost every non-PK, non-FK column is nullable today. The exceptions are the dorm building fields (slug, code, name, housing_classes, the enum and boolean columns, floor_plans) and every campus_building column except subtitle, which are NOT NULL because every entry in the JSON files has them. Treat every other profile/preference/review field as optional when rendering the frontend.