Fix unique constraint on review db #90

New issue

Open

opened 2026-09-13 20:17:22 +00:00 by gostmeaper · 1 comment

gostmeaper commented

2026-09-13 20:17:22 +00:00

Member

Overall Objective

reviewTable.userId in apps/backend/src/db/schema.ts is declared with .unique(). This makes it so that the database will reject any user's second review of any dorm. The constraint is on the user.

What we actually want: a user may write one review per dorm, and many reviews across different dorms. That's a composite unique constraint on (user_id, dorm_id).

Suggested Approach

This should be a two-line schema change plus a generated migration. Since this is likely your first time working with our database I'll walk you through the whole Drizzle workflow you'll need for every other backend issue you will do in the future.

Open apps/backend/src/db/schema.ts and find reviewTable at the bottom.
Drop .unique() from the userId column definition. Leave .notNull().references(() => userTable.id) alone.
Add a composite unique constraint using Drizzle's second table argument. It looks like this:

   export const reviewTable = pgTable("review", {
     /* ...columns... */
   }, (table) => [
     unique("review_user_dorm_unique").on(table.userId, table.dormId),
   ]);

You'll need to add unique to the import list from drizzle-orm/pg-core. (copy the structure of the other imports)
4. Generate the migration: cd apps/backend then deno task db:generate. This writes a new file into apps/backend/drizzle/ (it'll be 0004_*.sql) and updates drizzle/meta/. These aren't junk, you'll want to commit all of it.
5. Apply it locally with deno task db:migrate and confirm it runs clean. If you have leftover data from experimenting, deno task db:studio opens a browser UI where you can look at the table.
6. Sanity-check by hand in db:studio or psql: insert two rows with the same user_id but different dorm_id (should succeed), then two with the same user_id and same dorm_id (should fail).

While you're in there: reviewTable is also missing a default on submittedAt. Add .defaultNow() to it in the same PR. It's the same one-line-plus-migration pattern and it saves the reviews API from having to set the timestamp by hand.

Help & Resources

### Overall Objective `reviewTable.userId` in `apps/backend/src/db/schema.ts` is declared with `.unique()`. This makes it so that the database will reject any user's second review of any dorm. The constraint is on the user. What we actually want: a user may write **one review per dorm**, and many reviews across different dorms. That's a composite unique constraint on `(user_id, dorm_id)`. ### Suggested Approach This should be a two-line schema change plus a generated migration. Since this is likely your first time working with our database I'll walk you through the whole Drizzle workflow you'll need for every other backend issue you will do in the future. 1. Open `apps/backend/src/db/schema.ts` and find `reviewTable` at the bottom. 2. Drop `.unique()` from the `userId` column definition. Leave `.notNull().references(() => userTable.id)` alone. 3. Add a composite unique constraint using Drizzle's second table argument. It looks like this: ```ts export const reviewTable = pgTable("review", { /* ...columns... */ }, (table) => [ unique("review_user_dorm_unique").on(table.userId, table.dormId), ]); ``` You'll need to add `unique` to the import list from `drizzle-orm/pg-core`. (copy the structure of the other imports) 4. Generate the migration: `cd apps/backend` then `deno task db:generate`. This writes a new file into `apps/backend/drizzle/` (it'll be `0004_*.sql`) and updates `drizzle/meta/`. These aren't junk, you'll want to commit all of it. 5. Apply it locally with `deno task db:migrate` and confirm it runs clean. If you have leftover data from experimenting, `deno task db:studio` opens a browser UI where you can look at the table. 6. Sanity-check by hand in `db:studio` or `psql`: insert two rows with the same `user_id` but different `dorm_id` (should succeed), then two with the same `user_id` and same `dorm_id` (should fail). While you're in there: `reviewTable` is also missing a default on `submittedAt`. Add `.defaultNow()` to it in the same PR. It's the same one-line-plus-migration pattern and it saves the reviews API from having to set the timestamp by hand. ### 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) - [Drizzle: unique constraints](https://orm.drizzle.team/docs/indexes-constraints#unique) - [Drizzle: migrations](https://orm.drizzle.team/docs/migrations)

gostmeaper added this to the CMU Housing Development project

2026-09-13 20:17:22 +00:00

gostmeaper added the

labels

2026-09-13 20:31:28 +00:00