Seed dorm data from buildings.json to backend #124

Closed
opened 2026-10-26 17:36:23 +00:00 by gostmeaper · 0 comments
Member

Overall Objective

This blocks a lot so we need this done quickly. Plz only do if u can hammer this out immediately.

Move all 26 buildings out of apps/frontend/src/data/buildings.json and into Postgres, with a repeatable seed script.

Compared to the Building interface in apps/frontend/src/data/buildingTypes.ts, the table is missing:

  • laundry (location and details)
  • accessibility (wheelchair, service animal, ground floor rooms, strobe alarm)
  • atmosphere (socialness, noise level, both used by scoring.ts)
  • gym (available and details)
  • genderHousing
  • commonAreas.hasLounge as a boolean, we only have lounge_description
  • floor plans, which are a structured array of link, description, category, virtualTourLink and are not the same thing as photo_gallery
  • a stable string slug. buildings.json keys on "etower" and "fairfax", and every frontend URL uses it. The table only has an integer serial primary key.

So this issue is two things: expand the schema, then seed it.

Suggested Approach

  1. Decide field by field what becomes a real column and what stays local:
    • Anything scoring.ts filters or ranks on must be a real column so filtering can move into SQL later: AC level, laundry location, bathroom types, room types, socialness, noise level, gender housing, and the accessibility booleans.
    • Photo galleries and floor plans are display-only nested arrays. Keep them as json() but type them with .$type<GalleryImage[]>() and .$type<FloorPlan[]>(). docs/database-schema.md specifically warns that untyped json columns come back as unknown.
  2. The TS enums (RoomType, BathroomType, ACLevel, LaundryLocation, KitchenScope, GenderHousing) are numeric. Numeric enums are a poor fit for a database, since 0 only means RoomType.TradSingle until somebody reorders the enum. Convert to pgEnum with string values and change buildingTypes.ts to string enums in the same PR so the two sides cannot disagree.
  3. Add slug: text("slug").notNull().unique(). Keep the integer id as the primary key; the slug is what URLs and the seed script key on.
  4. Generate and commit the migration (deno task db:generate, then deno task db:migrate to verify). Export Dorm and NewDorm from the schema file using $inferSelect and $inferInsert; docs/database-schema.md has the exact snippet and nobody has implemented it yet.
  5. Write apps/backend/scripts/seed-dorms.ts. Model the script shape on apps/backend/scripts/generate-openapi.ts and the database connection on apps/backend/src/db/migrations.ts. Read buildings.json with a relative new URL(..., import.meta.url) rather than adding a cross-app workspace dependency.

Help & Resources

### Overall Objective _**This blocks a lot so we need this done quickly. Plz only do if u can hammer this out immediately.**_ Move all 26 buildings out of `apps/frontend/src/data/buildings.json` and into Postgres, with a repeatable seed script. Compared to the `Building` interface in `apps/frontend/src/data/buildingTypes.ts`, the table is missing: - `laundry` (location and details) - `accessibility` (wheelchair, service animal, ground floor rooms, strobe alarm) - `atmosphere` (socialness, noise level, both used by `scoring.ts`) - `gym` (available and details) - `genderHousing` - `commonAreas.hasLounge` as a boolean, we only have `lounge_description` - floor plans, which are a structured array of `link`, `description`, `category`, `virtualTourLink` and are not the same thing as `photo_gallery` - a stable string slug. `buildings.json` keys on `"etower"` and `"fairfax"`, and every frontend URL uses it. The table only has an integer serial primary key. So this issue is two things: expand the schema, then seed it. ### Suggested Approach 1. Decide field by field what becomes a real column and what stays local: - Anything `scoring.ts` filters or ranks on **must** be a real column so filtering can move into SQL later: AC level, laundry location, bathroom types, room types, socialness, noise level, gender housing, and the accessibility booleans. - Photo galleries and floor plans are display-only nested arrays. Keep them as `json()` but **type them** with `.$type<GalleryImage[]>()` and `.$type<FloorPlan[]>()`. `docs/database-schema.md` specifically warns that untyped json columns come back as `unknown`. 2. The TS enums (`RoomType`, `BathroomType`, `ACLevel`, `LaundryLocation`, `KitchenScope`, `GenderHousing`) are numeric. Numeric enums are a poor fit for a database, since `0` only means `RoomType.TradSingle` until somebody reorders the enum. Convert to `pgEnum` with string values and change `buildingTypes.ts` to string enums in the same PR so the two sides cannot disagree. 3. Add `slug`: `text("slug").notNull().unique()`. Keep the integer `id` as the primary key; the slug is what URLs and the seed script key on. 4. Generate and commit the migration (`deno task db:generate`, then `deno task db:migrate` to verify). Export `Dorm` and `NewDorm` from the schema file using `$inferSelect` and `$inferInsert`; `docs/database-schema.md` has the exact snippet and nobody has implemented it yet. 5. Write `apps/backend/scripts/seed-dorms.ts`. Model the script shape on `apps/backend/scripts/generate-openapi.ts` and the database connection on `apps/backend/src/db/migrations.ts`. Read `buildings.json` with a relative `new URL(..., import.meta.url)` rather than adding a cross-app workspace dependency. ### Help & Resources - [README](../../ScottyLabs/housing/src/branch/main/README.md) - [Contributing Guidelines](../../ScottyLabs/housing/src/branch/main/docs/CONTRIBUTING.md)
Sign in to join this conversation.
No milestone
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
ScottyLabs/housing#124
No description provided.