Expand the dorm table so it can hold everything `buildings.json` holds #98

New issue

Open

opened 2026-09-18 06:40:36 +00:00 by gostmeaper · 0 comments

gostmeaper commented

2026-09-18 06:40:36 +00:00

Member

Overall Objective

Currently all of our building data lives in apps/frontend/src/data/buildings.json, but it shouldn't be in the frontend in the first place. This data should be able to be held in the backend. The first step to moving this data to the backend is making sure the postgres db is able to hold the data. So with this issue we will expland the schema and types in the db.

For reference, it might be helpful to look at the Building interface in apps/frontend/src/data/buildingTypes.ts, provided to the app through BuildingContext.

We want that data in Postgres and served by the backend. The blocker is that the dorm table in apps/backend/src/db/schema.ts is much thinner than Building. It's missing:

laundry (location + details)
accessibility (wheelchair, service animal, ground floor rooms, strobe alarm)
atmosphere (socialness and noise level, both used by scoring.ts for filtering)
gym (available + details)
genderHousing
commonAreas.hasLounge as a boolean (we only have lounge_description)
floor plans, which are a structured array (link, description, category, virtualTourLink) and not the same thing as photo_gallery
a stable string slug: buildings.json keys on "etower", "mudge" etc., and every frontend URL is /building/:id using that slug. The table only has an integer serial PK.

This issue is only the schema and the types. No route, no seeding, no frontend changes. Please don't try to do everything at once.

Suggested Approach

Read apps/frontend/src/data/buildingTypes.ts first. That interface is the target shape. Also read docs/data-codebook.md, which documents every field and what it means.
Decide, field by field, what becomes a real column and what stays JSON. Rough guidance:
- Anything scoring.ts filters or sorts on must be a real column so we can push filtering into SQL later: AC level, laundry location, bathroom types, room types, socialness, noise level, and the accessibility booleans.
- Photo galleries and floor plans are display-only nested arrays. Keep them as json() columns, but type them with .$type<GalleryImage[]>() / .$type<FloorPlan[]>() so they aren't unknown downstream.
The Typescript enums in buildingTypes.ts (RoomType, BathroomType, ACLevel, LaundryLocation, KitchenScope, GenderHousing) are numeric enums. Generally, numeric enums are a bad fit for a database. 0 means RoomType.TradSingle only as long as nobody reorders the enum. Convert them to pgEnum with string values ("trad_single", "communal", "central", and so on).
Add a slug column, text("slug").notNull().unique(), holding "etower", "mudge", etc. Keep the integer id as the PK; the slug is what URLs and the seed script key on.
Generate and commit the migration (deno task db:generate, then deno task db:migrate to verify).
Export Dorm / NewDorm types from the schema file using $inferSelect / $inferInsert.
Post the proposed column list in the team channel before you write the migration. This table is about to have like five issues depending on it and so we should as a team decide if this is correct.

Help & Resources

### Overall Objective Currently all of our building data lives in `apps/frontend/src/data/buildings.json`, but it shouldn't be in the frontend in the first place. This data should be able to be held in the backend. The first step to moving this data to the backend is making sure the postgres db is able to hold the data. So with this issue we will expland the schema and types in the db. For reference, it might be helpful to look at the `Building` interface in `apps/frontend/src/data/buildingTypes.ts`, provided to the app through `BuildingContext`. We want that data in Postgres and served by the backend. The blocker is that the `dorm` table in `apps/backend/src/db/schema.ts` is much thinner than `Building`. It's missing: - `laundry` (location + details) - `accessibility` (wheelchair, service animal, ground floor rooms, strobe alarm) - `atmosphere` (socialness and noise level, both used by `scoring.ts` for filtering) - `gym` (available + details) - `genderHousing` - `commonAreas.hasLounge` as a boolean (we only have `lounge_description`) - floor plans, which are a structured array (`link`, `description`, `category`, `virtualTourLink`) and not the same thing as `photo_gallery` - a stable string slug: `buildings.json` keys on `"etower"`, `"mudge"` etc., and every frontend URL is `/building/:id` using that slug. The table only has an integer `serial` PK. This issue is **only the schema and the types**. No route, no seeding, no frontend changes. Please don't try to do everything at once. ### Suggested Approach 1. Read `apps/frontend/src/data/buildingTypes.ts` first. That interface is the target shape. Also read `docs/data-codebook.md`, which documents every field and what it means. 2. Decide, field by field, what becomes a real column and what stays JSON. Rough guidance: - Anything `scoring.ts` filters or sorts on **must** be a real column so we can push filtering into SQL later: AC level, laundry location, bathroom types, room types, socialness, noise level, and the accessibility booleans. - Photo galleries and floor plans are display-only nested arrays. Keep them as `json()` columns, but **type them** with `.$type<GalleryImage[]>()` / `.$type<FloorPlan[]>()` so they aren't unknown downstream. 3. The Typescript enums in `buildingTypes.ts` (`RoomType`, `BathroomType`, `ACLevel`, `LaundryLocation`, `KitchenScope`, `GenderHousing`) are numeric enums. Generally, numeric enums are a bad fit for a database. `0` means `RoomType.TradSingle` only as long as nobody reorders the enum. Convert them to `pgEnum` with string values (`"trad_single"`, `"communal"`, `"central"`, and so on). 4. Add a `slug` column, `text("slug").notNull().unique()`, holding `"etower"`, `"mudge"`, etc. Keep the integer `id` as the PK; the slug is what URLs and the seed script key on. 5. Generate and commit the migration (`deno task db:generate`, then `deno task db:migrate` to verify). 6. Export `Dorm` / `NewDorm` types from the schema file using `$inferSelect` / `$inferInsert`. 7. Post the proposed column list in the team channel *before* you write the migration. This table is about to have like five issues depending on it and so we should as a team decide if this is correct. ### Help & Resources - [README](../../ScottyLabs/housing/src/branch/main/README.md) - [Contributing Guidelines](../../ScottyLabs/housing/src/branch/main/docs/CONTRIBUTING.md) - [Database schema doc](../../ScottyLabs/housing/src/branch/main/docs/database-schema.md) - [Building data codebook](../../ScottyLabs/housing/src/branch/main/docs/data-codebook.md) - [Drizzle: column types](https://orm.drizzle.team/docs/column-types/pg) - [Drizzle: `$type` on json columns](https://orm.drizzle.team/docs/column-types/pg#json)

gostmeaper added the

labels

2026-09-18 06:40:41 +00:00

gostmeaper added this to the CMU Housing Development project

2026-09-18 06:40:44 +00:00