18 KiB
18 KiB
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 | 01 | execute | 1 |
|
true |
|
|
Purpose: This is the foundation that all subsequent plans depend on. Without the schema changes, userId columns, and middleware resolution, no service or route can be updated to scope data per-user.
Output: Updated schema.ts with pgTable imports and users table, migration generated, auth middleware resolving userId, test helper returning { db, 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/STATE.md @.planning/phases/16-multi-user-data-model/16-CONTEXT.md @.planning/phases/16-multi-user-data-model/16-RESEARCH.md @src/db/schema.ts @src/db/seed.ts @src/server/middleware/auth.ts @src/server/services/auth.service.ts @src/server/services/oauth.service.ts @src/server/index.ts @tests/helpers/db.tsFrom src/db/schema.ts (current - uses sqliteTable, must switch to pgTable):
- categories: id, name (unique), icon, createdAt
- items: id, name, weightGrams, priceCents, categoryId, notes, productUrl, imageFilename, imageSourceUrl, quantity, createdAt, updatedAt
- threads: id, name, status, resolvedCandidateId, categoryId, createdAt, updatedAt
- threadCandidates: id, threadId, name, weightGrams, priceCents, categoryId, notes, productUrl, imageFilename, imageSourceUrl, status, pros, cons, sortOrder, createdAt, updatedAt
- setups: id, name, createdAt, updatedAt
- setupItems: id, setupId, itemId, classification
- settings: key (PK), value
- apiKeys: id, name, keyHash, keyPrefix, createdAt
- oauthClients: id, clientId, clientName, redirectUris, createdAt
- oauthCodes: id, code, clientId, codeChallenge, codeChallengeMethod, redirectUri, expiresAt, used
- oauthTokens: id, accessTokenHash, refreshTokenHash, clientId, expiresAt, refreshExpiresAt, createdAt
From src/server/services/auth.service.ts:
export async function verifyApiKey(db: Db, rawKey: string): Promise<boolean>
export async function createApiKey(db: Db, name: string)
export async function listApiKeys(db: Db)
export async function deleteApiKey(db: Db, id: number)
From src/server/services/oauth.service.ts:
export async function verifyAccessToken(db: Db, token: string): Promise<boolean>
2. Add the new `users` table per D-01:
```typescript
export const users = pgTable("users", {
id: serial("id").primaryKey(),
logtoSub: text("logto_sub").notNull().unique(),
createdAt: timestamp("created_at").defaultNow().notNull(),
});
```
3. Add `userId` column (integer, NOT NULL, FK to users.id) to these tables per D-04:
- `items`: `userId: integer("user_id").notNull().references(() => users.id)`
- `categories`: `userId: integer("user_id").notNull().references(() => users.id)`
- `threads`: `userId: integer("user_id").notNull().references(() => users.id)`
- `setups`: `userId: integer("user_id").notNull().references(() => users.id)`
- `apiKeys`: `userId: integer("user_id").notNull().references(() => users.id)` per D-07
- `oauthTokens`: `userId: integer("user_id").notNull().references(() => users.id)` (per Research open question 2)
4. Per D-05, change `categories` unique constraint from `name` alone to composite `(userId, name)`:
```typescript
export const categories = pgTable("categories", {
id: serial("id").primaryKey(),
name: text("name").notNull(),
icon: text("icon").notNull().default("package"),
userId: integer("user_id").notNull().references(() => users.id),
createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => [
unique().on(table.userId, table.name),
]);
```
5. Per D-06, change `settings` PK from `key` alone to composite `(userId, key)`:
```typescript
export const settings = pgTable("settings", {
userId: integer("user_id").notNull().references(() => users.id),
key: text("key").notNull(),
value: text("value").notNull(),
}, (table) => [
primaryKey({ columns: [table.userId, table.key] }),
]);
```
6. Per D-08, do NOT add userId to `threadCandidates` or `setupItems` (they inherit ownership via parent FK).
7. Update `src/db/seed.ts`: The `seedDefaults()` function currently seeds a global Uncategorized category. Since categories now require userId, this global seed no longer works. Change `seedDefaults()` to be a no-op or remove the category seeding entirely (per-user Uncategorized will be created lazily or on first login per D-12). The function can remain as an empty function for now:
```typescript
export async function seedDefaults() {
// Per-user default categories are created on first login (Phase 16)
}
```
8. Run `bun run db:generate` to generate the new Drizzle migration into `drizzle-pg/`. Then verify the generated SQL includes the users table, userId columns, composite constraints, and FK relationships.
IMPORTANT: The schema.ts file MUST use pg-core imports (`pgTable`, `serial`, `text`, `timestamp`, `integer`, `doublePrecision`, `unique`, `primaryKey`). The existing `drizzle-pg/` migration directory already has PostgreSQL DDL from Phase 14.
2. **Update `verifyAccessToken` in `src/server/services/oauth.service.ts`**:
Change return type from `Promise<boolean>` to `Promise<{ userId: number } | null>`. Select `userId` from the oauthTokens record and return `{ userId: record.userId }` on success, `null` on failure. Also update `createTokens` to accept and store `userId`.
3. **Create `getOrCreateUser` function** in `src/server/services/auth.service.ts` per D-01:
```typescript
export async function getOrCreateUser(db: Db, logtoSub: string): Promise<{ id: number }> {
const [user] = await db
.insert(users)
.values({ logtoSub })
.onConflictDoUpdate({
target: users.logtoSub,
set: { logtoSub },
})
.returning({ id: users.id });
return user;
}
```
4. **Create `getOrCreateUncategorized` helper** in `src/server/services/category.service.ts` (or auth.service.ts):
```typescript
export async function getOrCreateUncategorized(db: Db, userId: number): Promise<number> {
const [existing] = await db
.select({ id: categories.id })
.from(categories)
.where(and(eq(categories.userId, userId), eq(categories.name, "Uncategorized")));
if (existing) return existing.id;
const [created] = await db
.insert(categories)
.values({ name: "Uncategorized", icon: "package", userId })
.returning({ id: categories.id });
return created.id;
}
```
Place this in `category.service.ts` since it's category-related.
5. **Rewrite `requireAuth` in `src/server/middleware/auth.ts`** per D-03/D-10:
- For API key auth: call `verifyApiKey(db, apiKey)` which now returns `{ userId } | null`. On success, `c.set("userId", result.userId)` and call `next()`.
- For OAuth Bearer: call `verifyAccessToken(db, token)` which now returns `{ userId } | null`. On success, `c.set("userId", result.userId)`.
- For OIDC session: call `getAuth(c)` for the sub claim, then `getOrCreateUser(db, auth.sub)` to get the local userId. Then call `getOrCreateUncategorized(db, user.id)` to ensure the user has a default category. Set `c.set("userId", user.id)`.
- Import `getOrCreateUser` from auth.service and `getOrCreateUncategorized` from category.service.
6. **Update auth middleware configuration in `src/server/index.ts`** per Research pitfall 2:
Change the `/api/*` middleware from:
```typescript
if (c.req.method === "GET") return next();
```
to apply `requireAuth` to ALL methods on data routes (remove the GET bypass). This ensures userId is always available on context for read operations. Keep the `/api/auth` bypass and add a bypass for `/api/health`.
The new middleware block should be:
```typescript
app.use("/api/*", async (c, next) => {
if (c.req.path.startsWith("/api/auth")) return next();
if (c.req.path === "/api/health") return next();
return requireAuth(c, next);
});
```
The updated `createTestDb` should look like:
```typescript
export async function createTestDb() {
const db = drizzle({ schema });
await migrate(db, { migrationsFolder: "./drizzle-pg" });
const [user] = await db
.insert(schema.users)
.values({ logtoSub: "test-user-sub" })
.returning();
await db
.insert(schema.categories)
.values({ name: "Uncategorized", icon: "package", userId: user.id });
return { db, userId: user.id };
}
```
IMPORTANT: This changes the return type of createTestDb from `db` to `{ db, userId }`. All existing test files that call `createTestDb()` will need updating in Plan 04 to destructure the result.
<success_criteria>
- Schema uses pg-core imports exclusively (no sqlite-core)
- users table with id, logtoSub (unique), createdAt defined
- userId FK column present on items, categories, threads, setups, settings, apiKeys, oauthTokens
- categories: composite unique on (userId, name)
- settings: composite PK on (userId, key)
- requireAuth resolves userId for API key, Bearer token, and OIDC session
- verifyApiKey and verifyAccessToken return { userId } | null
- Test helper returns { db, userId } with seeded user
- All API routes require auth (no GET bypass)
- Drizzle migration generated in drizzle-pg/ </success_criteria>