GearBox/.planning/phases/16-multi-user-data-model/16-02-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
16-multi-user-data-model	02	execute	2	16-01	src/server/services/item.service.ts src/server/services/category.service.ts src/server/services/thread.service.ts src/server/services/setup.service.ts src/server/services/totals.service.ts src/server/services/csv.service.ts src/server/services/auth.service.ts	true	MULTI-01 MULTI-02 MULTI-03 MULTI-06	truths artifacts key_links Every service function that reads or writes user data accepts a userId parameter All queries filter by userId using eq(table.userId, userId) Get-by-id queries use and(eq(table.id, id), eq(table.userId, userId)) to prevent cross-user access Category operations respect composite unique (userId, name) Settings operations use composite PK (userId, key) Thread resolution creates the new item with the same userId as the thread CSV import scopes category creation and lookup to the importing user API key CRUD is scoped to the owning user path provides contains src/server/services/item.service.ts User-scoped item CRUD userId: number path provides contains src/server/services/category.service.ts User-scoped category CRUD with composite unique userId: number path provides contains src/server/services/thread.service.ts User-scoped thread + candidate CRUD + resolution userId: number path provides contains src/server/services/setup.service.ts User-scoped setup CRUD + item sync validation userId: number path provides contains src/server/services/totals.service.ts User-scoped aggregate queries userId: number path provides contains src/server/services/csv.service.ts User-scoped CSV import/export userId: number from to via pattern src/server/services/thread.service.ts src/db/schema.ts userId on insert(items) during thread resolution userId.*resolv from to via pattern src/server/services/setup.service.ts src/db/schema.ts validates item ownership before sync eq.*items.userId.*userId from to via pattern src/server/services/category.service.ts src/db/schema.ts getOrCreateUncategorized uses composite unique getOrCreateUncategorized

Add userId parameter to every service function and scope all database queries to the authenticated user.

Purpose: Services are the data access layer. Scoping them to userId is the core of multi-user data isolation (MULTI-02). Without this, routes and MCP tools have no way to enforce per-user boundaries.

Output: All 7 service files updated with userId parameter on every function, all queries filtered by userId.

<execution_context> @$HOME/.claude/get-shit-done/workflows/execute-plan.md @$HOME/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/phases/16-multi-user-data-model/16-CONTEXT.md @.planning/phases/16-multi-user-data-model/16-RESEARCH.md @.planning/phases/16-multi-user-data-model/16-01-SUMMARY.md @src/db/schema.ts @src/server/services/item.service.ts @src/server/services/category.service.ts @src/server/services/thread.service.ts @src/server/services/setup.service.ts @src/server/services/totals.service.ts @src/server/services/csv.service.ts @src/server/services/auth.service.ts

Expected new signatures (all gain userId as second param):

• item.service.ts: getAllItems(db, userId), getItemById(db, userId, id), createItem(db, userId, data), updateItem(db, userId, id, data), deleteItem(db, userId, id)
• category.service.ts: getAllCategories(db, userId), getCategoryById(db, userId, id), createCategory(db, userId, data), updateCategory(db, userId, id, data), deleteCategory(db, userId, id)
  • getOrCreateUncategorized(db, userId) already exists from Plan 01
• thread.service.ts: getAllThreads(db, userId), getThreadById(db, userId, id), createThread(db, userId, data), updateThread(db, userId, id, data), deleteThread(db, userId, id), resolveThread(db, userId, id, candidateId), addCandidate(db, userId, ...), updateCandidate(db, userId, ...), removeCandidate(db, userId, ...)
• setup.service.ts: getAllSetups(db, userId), getSetupById(db, userId, id), createSetup(db, userId, data), updateSetup(db, userId, id, data), deleteSetup(db, userId, id), syncSetupItems(db, userId, setupId, items)
• totals.service.ts: getTotals(db, userId)
• csv.service.ts: importItemsCsv(db, userId, data), exportItemsCsv(db, userId)
• auth.service.ts: createApiKey(db, userId, name), listApiKeys(db, userId), deleteApiKey(db, userId, id) — verifyApiKey already updated in Plan 01

Task 1: Update item, category, totals, and CSV services with userId scoping src/server/services/item.service.ts, src/server/services/category.service.ts, src/server/services/totals.service.ts, src/server/services/csv.service.ts src/server/services/item.service.ts, src/server/services/category.service.ts, src/server/services/totals.service.ts, src/server/services/csv.service.ts, src/db/schema.ts **item.service.ts** per D-09: - Add `userId: number` as second parameter to ALL exported functions - Remove default `db` parameter values (no more `db: Db = prodDb`) -- db is always injected - `getAllItems`: add `.where(eq(items.userId, userId))` - `getItemById`: change `.where(eq(items.id, id))` to `.where(and(eq(items.id, id), eq(items.userId, userId)))` -- CRITICAL for isolation per Research anti-pattern - `createItem`: include `userId` in the `.values({...})` insert - `updateItem`: add `eq(items.userId, userId)` to the `.where()` clause using `and()` - `deleteItem`: add `eq(items.userId, userId)` to the `.where()` clause using `and()` - Import `and` from `drizzle-orm` if not already imported

**category.service.ts** per D-05/D-09:
- Add `userId: number` as second parameter to ALL exported functions
- Remove default `db` parameter values
- `getAllCategories`: add `.where(eq(categories.userId, userId))`
- `getCategoryById`: use `and(eq(categories.id, id), eq(categories.userId, userId))`
- `createCategory`: include `userId` in the insert values
- `updateCategory`: add userId filter with `and()`
- `deleteCategory`: add userId filter with `and()`. When reassigning items to Uncategorized on delete, use `getOrCreateUncategorized(db, userId)` instead of hardcoded category ID 1. Also scope the item reassignment to only items belonging to this user.
- The `getOrCreateUncategorized` function was already created in Plan 01

**totals.service.ts** per D-09:
- Add `userId: number` parameter
- Filter all aggregate queries by userId
- This file computes weight/cost totals across the collection -- must only sum the user's items

**csv.service.ts** per D-09 and Research pitfall 7:
- Add `userId: number` parameter to `importItemsCsv` and `exportItemsCsv`
- `exportItemsCsv`: filter items query by userId
- `importItemsCsv`: 
  - Category lookup/creation must filter by userId (use `getOrCreateUncategorized` for fallback)
  - When creating new categories from CSV data, include userId
  - When creating items, include userId
  - Category name matching must be scoped to user's categories

grep -c "userId: number" src/server/services/item.service.ts && grep -c "userId: number" src/server/services/category.service.ts && grep -c "userId: number" src/server/services/totals.service.ts && grep -c "userId: number" src/server/services/csv.service.ts && grep "and(" src/server/services/item.service.ts | wc -l - Every exported function in item.service.ts has `userId: number` parameter - Every exported function in category.service.ts has `userId: number` parameter - totals.service.ts functions have `userId: number` parameter - csv.service.ts import/export functions have `userId: number` parameter - `getItemById` uses `and(eq(items.id, id), eq(items.userId, userId))` (not just eq on id) - `deleteCategory` uses `getOrCreateUncategorized(db, userId)` not hardcoded ID - `importItemsCsv` scopes category operations to userId - No `= prodDb` default parameter values remain - `and` imported from `drizzle-orm` in all files that use it Item, category, totals, and CSV services accept userId and scope all queries to the authenticated user. Get-by-id uses and() for isolation. Task 2: Update thread, setup, settings, and auth services with userId scoping src/server/services/thread.service.ts, src/server/services/setup.service.ts, src/server/services/auth.service.ts src/server/services/thread.service.ts, src/server/services/setup.service.ts, src/server/services/auth.service.ts, src/db/schema.ts **thread.service.ts** per D-09 and Research pitfall 6: - Add `userId: number` as second parameter to ALL exported functions - Remove default `db` parameter values - `getAllThreads`: add `.where(eq(threads.userId, userId))` - `getThreadById`: use `and(eq(threads.id, id), eq(threads.userId, userId))` - `createThread`: include `userId` in the insert values - `updateThread`: add userId filter - `deleteThread`: add userId filter - `resolveThread`: CRITICAL -- verify the thread belongs to the user before resolving. When creating the new item from the winning candidate, include `userId` in the `insert(items).values({...})`. Also verify the target category belongs to the user. Use `getOrCreateUncategorized(db, userId)` if category fallback is needed. - `addCandidate`: verify the parent thread belongs to the user before inserting candidate - `updateCandidate`: verify the parent thread belongs to the user (join or subquery) - `removeCandidate`: verify the parent thread belongs to the user

For candidate operations, the pattern should be:
1. Look up the thread with userId filter: `and(eq(threads.id, threadId), eq(threads.userId, userId))`
2. If thread not found, return null/throw (the thread doesn't exist for this user)
3. Proceed with candidate operation on the verified thread

**setup.service.ts** per D-09 and Research pitfall 8:
- Add `userId: number` as second parameter to ALL exported functions
- `getAllSetups`: add `.where(eq(setups.userId, userId))`
- `getSetupById`: use `and(eq(setups.id, id), eq(setups.userId, userId))`
- `createSetup`: include `userId` in insert values
- `updateSetup`: add userId filter
- `deleteSetup`: add userId filter
- `syncSetupItems`: CRITICAL -- verify the setup belongs to the user AND verify each itemId belongs to the user before inserting into setupItems. Filter the incoming item list against user-owned items:
```typescript
const userItemIds = await db.select({ id: items.id }).from(items)
  .where(and(eq(items.userId, userId), inArray(items.id, itemIds)));
// Only insert items that belong to this user
```

**auth.service.ts** per D-07:
- `createApiKey`: add `userId: number` parameter, include userId in insert values
- `listApiKeys`: add `userId: number` parameter, filter by `.where(eq(apiKeys.userId, userId))`
- `deleteApiKey`: add `userId: number` parameter, filter by `and(eq(apiKeys.id, id), eq(apiKeys.userId, userId))` to prevent deleting another user's API key
- `verifyApiKey` was already updated in Plan 01 to return `{ userId } | null`
- `getOrCreateUser` was already created in Plan 01

grep -c "userId: number" src/server/services/thread.service.ts && grep -c "userId: number" src/server/services/setup.service.ts && grep -c "userId: number" src/server/services/auth.service.ts && grep "and(" src/server/services/thread.service.ts | wc -l && grep "and(" src/server/services/setup.service.ts | wc -l - Every exported function in thread.service.ts has `userId: number` parameter - Every exported function in setup.service.ts has `userId: number` parameter - `createApiKey`, `listApiKeys`, `deleteApiKey` in auth.service.ts have `userId: number` parameter - `resolveThread` includes `userId` in the `insert(items).values({...})` call - `resolveThread` verifies thread ownership before resolving - Candidate operations (add, update, remove) verify parent thread ownership - `syncSetupItems` verifies both setup and item ownership - `getThreadById` uses `and(eq(threads.id, id), eq(threads.userId, userId))` - `getSetupById` uses `and(eq(setups.id, id), eq(setups.userId, userId))` - No `= prodDb` default parameter values remain Thread, setup, and auth services accept userId and scope all queries. Thread resolution and setup sync validate ownership. Candidate operations verify parent thread belongs to user. After all tasks complete: 1. `grep -r "userId: number" src/server/services/ | wc -l` shows userId parameter across all service files 2. `grep -r "= prodDb" src/server/services/` returns no matches (no default db params) 3. `grep -r "and(eq" src/server/services/` shows isolation on get-by-id queries 4. No service function reads or writes user-owned data without userId filtering

<success_criteria>

• All 7 service files accept userId as a parameter
• All queries filter by userId (no unscoped reads or writes)
• Get-by-id, update, and delete operations use and() to combine id + userId conditions
• Thread resolution includes userId on new item creation
• Setup item sync validates item ownership
• Category deletion uses dynamic Uncategorized lookup (not hardcoded ID)
• CSV import scopes all operations to the importing user
• API key CRUD is user-scoped </success_criteria>

After completion, create `.planning/phases/16-multi-user-data-model/16-02-SUMMARY.md`