# Architecture Research: v2.0 Platform Foundation

**Domain:** Multi-user gear management and discovery platform
**Researched:** 2026-04-03
**Confidence:** HIGH

## System Overview: Integration Map

The v2.0 transformation touches every layer of the stack. This is not a feature addition -- it is a structural migration. The diagram below shows the current architecture (left) and the target architecture (right), with change annotations.

```
CURRENT (v1.x)                          TARGET (v2.0)
==============                          =============

CLIENT LAYER
+------------------------------+        +----------------------------------------+
| TanStack Router (file-based) |        | TanStack Router (file-based)           |
| Routes: /, /collection,     |        | Routes: /, /collection, /threads,      |
|   /threads, /setups, /login, |        |   /setups, /login, /settings,          |
|   /settings                  |        |   /profile/:username,      [NEW]       |
|                              |        |   /discover,               [NEW]       |
|                              |        |   /items/:id,              [NEW]       |
+------------------------------+        +----------------------------------------+
| Hooks: useItems, useThreads, |        | Hooks: same + useProfile,  [NEW]      |
|   useSetups, useCandidates,  |        |   useDiscover, useGlobalItems,         |
|   useCategories, useAuth     |        |   useReviews                           |
+------------------------------+        +----------------------------------------+
| lib/api.ts (fetch wrapper)   |        | lib/api.ts (unchanged)                 |
+------------------------------+        +----------------------------------------+

SERVER LAYER
+------------------------------+        +----------------------------------------+
| Hono app                     |        | Hono app                               |
| Middleware:                  |        | Middleware:                             |
|   db injection (c.set("db")) |        |   db injection                         |
|   requireAuth (cookie/apikey)|        |   oidcAuth (@hono/oidc-auth)  [REPLACE]|
|                              |        |   extractUser (c.set("user")) [NEW]    |
+------------------------------+        +----------------------------------------+
| Routes:                      |        | Routes:                                |
|   /api/auth/* (login/setup)  |        |   /api/auth/* (OIDC callbacks)[REPLACE]|
|   /api/items                 |        |   /api/items (scoped by user) [MODIFY] |
|   /api/categories            |        |   /api/categories (user-owned)[MODIFY] |
|   /api/threads               |        |   /api/threads (user-owned)   [MODIFY] |
|   /api/setups                |        |   /api/setups (user-owned)    [MODIFY] |
|   /api/totals                |        |   /api/totals (user-scoped)   [MODIFY] |
|   /api/images                |        |   /api/images                 [MODIFY] |
|   /api/settings              |        |   /api/settings (user-scoped) [MODIFY] |
|   /mcp                       |        |   /api/global-items           [NEW]    |
|                              |        |   /api/reviews                [NEW]    |
|                              |        |   /api/profiles               [NEW]    |
|                              |        |   /api/discover               [NEW]    |
|                              |        |   /mcp (user-scoped)          [MODIFY] |
+------------------------------+        +----------------------------------------+
| Services: item, thread,      |        | Services: same (add userId param)      |
|   setup, category, auth,     |        |   + globalItem.service         [NEW]   |
|   csv, image, totals         |        |   + review.service             [NEW]   |
|                              |        |   + profile.service            [NEW]   |
|                              |        |   + discover.service           [NEW]   |
|                              |        |   auth.service (gutted)        [REPLACE]|
+------------------------------+        +----------------------------------------+

DATABASE LAYER
+------------------------------+        +----------------------------------------+
| SQLite (bun:sqlite)          |        | PostgreSQL (postgres.js)     [REPLACE] |
| Drizzle ORM (sqlite-core)    |        | Drizzle ORM (pg-core)        [REPLACE] |
| Schema: sqliteTable          |        | Schema: pgTable              [REPLACE] |
|                              |        |                                        |
| Tables:                      |        | Tables:                                |
|   categories (no userId)     |        |   categories (+userId)       [MODIFY]  |
|   items (no userId)          |        |   items (+userId, +globalId) [MODIFY]  |
|   threads (no userId)        |        |   threads (+userId)          [MODIFY]  |
|   threadCandidates           |        |   threadCandidates           [NO CHANGE]|
|   setups (no userId)         |        |   setups (+userId,+isPublic) [MODIFY]  |
|   setupItems                 |        |   setupItems                 [NO CHANGE]|
|   settings                   |        |   settings (+userId)         [MODIFY]  |
|   users (password-based)     |        |   users (OIDC subject)       [REPLACE] |
|   sessions (local)           |        |   (removed - OIDC handles)   [DELETE]  |
|   apiKeys                    |        |   apiKeys (+userId)          [MODIFY]  |
|                              |        |   globalItems                [NEW]     |
|                              |        |   reviews                    [NEW]     |
+------------------------------+        +----------------------------------------+

IMAGE STORAGE
+------------------------------+        +----------------------------------------+
| Local filesystem (./uploads) |        | S3-compatible (MinIO)        [REPLACE] |
| serveStatic("/uploads/*")    |        | Presigned URLs               [REPLACE] |
+------------------------------+        +----------------------------------------+
```

---

## Component-by-Component Analysis

### 1. Database Migration: SQLite to PostgreSQL

**What changes:** The entire database layer switches from `bun:sqlite` + `drizzle-orm/bun-sqlite` to `postgres.js` + `drizzle-orm/postgres-js`. This is a rewrite of the schema file and the db connection module, not an automated migration.

**Why a rewrite, not a migration tool:** Drizzle uses different table constructors per dialect (`sqliteTable` vs `pgTable`), different column type imports (`drizzle-orm/sqlite-core` vs `drizzle-orm/pg-core`), and different driver bindings. There is no Drizzle utility to convert between dialects. The schema must be rewritten manually.

**Schema translation map:**

| SQLite (current) | PostgreSQL (target) | Notes |
|---|---|---|
| `sqliteTable` | `pgTable` | Import from `drizzle-orm/pg-core` |
| `integer("id").primaryKey({ autoIncrement: true })` | `serial("id").primaryKey()` | Or `integer().primaryKey().generatedAlwaysAsIdentity()` |
| `text("name")` | `varchar("name", { length: 255 })` or `text("name")` | Use `varchar` where length matters |
| `integer("price_cents")` | `integer("price_cents")` | Same |
| `real("weight_grams")` | `real("weight_grams")` or `doublePrecision("weight_grams")` | `real` maps to Postgres float4 |
| `integer("created_at", { mode: "timestamp" })` | `timestamp("created_at").defaultNow()` | Native Postgres timestamp |
| `text("status").default("active")` | `text("status").default('active')` | Same, or use Postgres `enum` |
| `.all()` | `.execute()` or collect from async iterator | Postgres queries are async |

**Connection module change:**

```typescript
// CURRENT: src/db/index.ts
import { Database } from "bun:sqlite";
import { drizzle } from "drizzle-orm/bun-sqlite";
const sqlite = new Database("gearbox.db");
export const db = drizzle(sqlite, { schema });

// TARGET: src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });
```

**Critical implication -- synchronous to async:** SQLite queries via `bun:sqlite` are synchronous (`.get()`, `.all()`, `.run()`). Postgres queries are async. Every service function that currently returns a value synchronously will need to become `async` and return a `Promise`. This cascades through routes (which are already async in Hono) but changes every service signature.

**Service layer impact example:**

```typescript
// CURRENT (synchronous)
export function getAllItems(db: Db) {
  return db.select(...).from(items).all();  // returns Item[]
}

// TARGET (async)
export async function getAllItems(db: Db) {
  return db.select(...).from(items);  // returns Promise<Item[]>
}
```

**Test infrastructure change:** The current `createTestDb()` uses in-memory SQLite. For Postgres testing, options are:
1. **PGlite** -- Postgres compiled to WASM, runs in-process like SQLite. Drop-in for tests. Drizzle supports it via `drizzle-orm/pglite`. This is the recommended approach.
2. **Testcontainers** -- Spin up a real Postgres Docker container per test suite. More realistic but slower.
3. **Shared test database** -- Single Postgres instance with schema reset between tests. Fast but not isolated.

**Recommendation:** Use PGlite for unit/integration tests (fastest, no Docker dependency, matches Postgres semantics). Use real Postgres for E2E tests.

**Data migration (one-time):** Export existing SQLite data as INSERT statements or CSV, import into Postgres. The schema shapes will be different (new columns), so this needs a custom migration script. Since this is a single-user app becoming multi-user, the existing data becomes the first user's data.

---

### 2. Multi-Tenancy: Adding userId to Entity Tables

**What changes:** Every entity table (items, categories, threads, setups, settings) gains a `userId` column as a NOT NULL foreign key to the `users` table. All queries are scoped by the authenticated user.

**Schema additions:**

```typescript
// All entity tables gain:
userId: integer("user_id").notNull().references(() => users.id),

// The users table is redesigned for OIDC:
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  externalId: varchar("external_id", { length: 255 }).notNull().unique(), // OIDC subject ID
  username: varchar("username", { length: 100 }).notNull().unique(),
  displayName: varchar("display_name", { length: 255 }),
  avatarUrl: text("avatar_url"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Sessions table: REMOVED (OIDC middleware handles sessions via JWT cookies)
// passwordHash on users: REMOVED (OIDC handles passwords)
```

**Service layer pattern change -- userId injection:**

The existing service pattern takes `(db, ...)` as first arguments. Adding userId requires passing it through from the middleware.

```typescript
// CURRENT
export function getAllItems(db: Db) {
  return db.select(...).from(items).all();
}

// TARGET -- userId as second param
export async function getAllItems(db: Db, userId: number) {
  return db.select(...).from(items).where(eq(items.userId, userId));
}
```

**Middleware provides userId:**

```typescript
// New middleware: extractUser
// Runs after OIDC auth, resolves OIDC subject to local user ID
app.use("/api/*", async (c, next) => {
  const oidcUser = c.get("oidc-user"); // from @hono/oidc-auth
  if (!oidcUser) return next(); // public read routes
  const user = await getOrCreateUser(db, oidcUser.sub, oidcUser);
  c.set("userId", user.id);
  return next();
});
```

**Route layer change -- extract userId and pass to services:**

```typescript
// CURRENT
app.get("/", (c) => {
  const db = c.get("db");
  const items = getAllItems(db);
  return c.json(items);
});

// TARGET
app.get("/", (c) => {
  const db = c.get("db");
  const userId = c.get("userId");
  const items = await getAllItems(db, userId);
  return c.json(items);
});
```

**Tables that get userId:**

| Table | userId Behavior | Notes |
|---|---|---|
| `items` | Required. User owns their items. | Also gains `globalItemId` FK (optional) |
| `categories` | Required. Users define their own categories. | Seed "Uncategorized" per user on first login |
| `threads` | Required. User owns their research threads. | |
| `setups` | Required. User owns their setups. | Also gains `isPublic` boolean |
| `settings` | Required. Per-user settings. | Change PK from `key` to `(userId, key)` |
| `apiKeys` | Required. User manages their own API keys. | |
| `threadCandidates` | No change. Scoped via thread's userId. | Accessed through parent thread |
| `setupItems` | No change. Scoped via setup's userId. | Accessed through parent setup |

**Categories: user-scoped, not global.** Each user creates their own categories. This keeps the model simple -- no shared namespace conflicts. The "Uncategorized" category is seeded per user on account creation.

---

### 3. Authentication: OIDC via External Provider

**What changes:** The entire auth system (users table with passwordHash, sessions table, login/setup routes, requireAuth middleware) is replaced with OIDC-based authentication delegated to an external provider.

**Recommended provider: Authentik** because:
- Full OIDC/OAuth2 support (needed for standard web app auth flow)
- Modern UI, actively maintained (Python/Django, 40k+ GitHub stars)
- Self-hosted, open-source (matches project constraints)
- Supports user registration, password management, MFA out of the box
- The project already plans to run Postgres (Authentik requires Postgres + Redis)
- More capable than Authelia (which is only a forward-auth proxy, not a full IdP)
- Lighter than Keycloak (~600MB vs 1GB+, simpler config)

**OIDC integration with Hono:**

Use `@hono/oidc-auth` -- official Hono third-party middleware. It handles the Authorization Code Flow, token exchange, and session cookies (as signed JWTs). No session table needed.

```typescript
// Configuration
import { oidcAuthMiddleware, getAuth } from "@hono/oidc-auth";

// Environment variables:
// OIDC_AUTH_SECRET - signing key for session JWT
// OIDC_ISSUER - Authentik issuer URL
// OIDC_CLIENT_ID - from Authentik provider config
// OIDC_CLIENT_SECRET - from Authentik provider config
// OIDC_REDIRECT_URI - callback URL

app.use("/api/*", oidcAuthMiddleware());
```

**Auth flow:**

```
User visits app -> no session cookie
  -> Redirect to Authentik login page
  -> User logs in / registers at Authentik
  -> Authentik redirects back with authorization code
  -> @hono/oidc-auth exchanges code for tokens
  -> Middleware sets signed JWT cookie (stateless session)
  -> Subsequent requests: middleware validates JWT, sets oidc-user in context
```

**What gets removed:**
- `src/server/services/auth.service.ts` -- password hashing, session CRUD (gutted; API key management stays)
- `src/server/routes/auth.ts` -- login, setup, password change routes (replaced with OIDC callbacks)
- `requireAuth` middleware -- replaced by `oidcAuthMiddleware()`
- `users.passwordHash` column -- removed
- `sessions` table -- removed entirely

**What stays:**
- API key system -- still needed for MCP and programmatic access. API keys bypass OIDC and authenticate directly. The `apiKeys` table gains a `userId` column.
- Rate limiting middleware on auth-adjacent routes.

**Public read vs authenticated write:** This pattern remains. OIDC middleware can be configured to not block GET requests:

```typescript
// Custom wrapper: require auth only for write operations
app.use("/api/*", async (c, next) => {
  if (c.req.method === "GET") return next(); // public read
  return oidcAuthMiddleware()(c, next);       // authenticated write
});
```

**User provisioning on first login:** When the OIDC callback succeeds and no local user exists for the `sub` (subject) claim, create one:

```typescript
async function getOrCreateUser(db: Db, sub: string, claims: OidcClaims) {
  let user = await db.select().from(users)
    .where(eq(users.externalId, sub)).limit(1).then(r => r[0]);

  if (!user) {
    user = await db.insert(users).values({
      externalId: sub,
      username: claims.preferred_username ?? claims.sub,
      displayName: claims.name ?? null,
      avatarUrl: claims.picture ?? null,
    }).returning().then(r => r[0]);

    // Seed default category for new user
    await db.insert(categories).values({
      name: "Uncategorized",
      icon: "package",
      userId: user.id,
    });
  }

  return user;
}
```

---

### 4. Global Item Database

**What changes:** A new `globalItems` table holds manufacturer/canonical gear data. User items can optionally link to a global item via `globalItemId` FK. Global items are not owned by any user -- they are platform-level data.

**Schema:**

```typescript
export const globalItems = pgTable("global_items", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  brand: varchar("brand", { length: 255 }),
  weightGrams: real("weight_grams"),
  priceCents: integer("price_cents"),     // MSRP
  productUrl: text("product_url"),
  imageFilename: text("image_filename"),
  categorySlug: varchar("category_slug", { length: 100 }), // platform-level category
  description: text("description"),
  specs: jsonb("specs"),                  // flexible key-value for category-specific specs
  ownerCount: integer("owner_count").default(0).notNull(),  // denormalized count
  avgRating: real("avg_rating"),          // denormalized from reviews
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Index for search
// CREATE INDEX idx_global_items_name ON global_items USING gin(to_tsvector('english', name));
```

**Relationship to user items:**

```typescript
export const items = pgTable("items", {
  // ... existing fields ...
  userId: integer("user_id").notNull().references(() => users.id),
  globalItemId: integer("global_item_id").references(() => globalItems.id), // nullable
});
```

When a user adds a global item to their collection, a user-owned `items` row is created with `globalItemId` pointing to the canonical entry. The user can override weight/price/notes locally. The global item's `ownerCount` is incremented (denormalized counter, updated via trigger or service logic).

**Data flow for global item search:**

```
User searches for gear -> GET /api/global-items?q=tent
  -> Full-text search on globalItems.name (Postgres ts_vector)
  -> Returns matches with ownerCount, avgRating
  -> User clicks "Add to Collection"
  -> POST /api/items { ...globalItemDefaults, globalItemId: 42 }
  -> Creates user item linked to global item
  -> Increment globalItems.ownerCount
```

**New service: `globalItem.service.ts`**
- `searchGlobalItems(db, query, limit, offset)` -- full-text search
- `getGlobalItem(db, id)` -- single item with aggregated stats
- `getGlobalItemOwners(db, globalItemId)` -- users who own this item (public profiles only)
- `getGlobalItemSetups(db, globalItemId)` -- public setups containing this item

**New routes: `/api/global-items`**
- `GET /` -- search/browse (public)
- `GET /:id` -- detail with stats (public)
- `GET /:id/owners` -- owner list (public)
- `GET /:id/setups` -- setups containing this item (public)

**Seeding:** Global items are initially seeded from manufacturer data (CSV import or manual entry). Users do not create global items directly -- they create user items that may or may not link to global items. A future admin interface could allow promoting frequently-created user items to global items.

---

### 5. Structured Reviews

**What changes:** Users can review global items with structured ratings -- no freeform text (as per project constraints).

**Schema:**

```typescript
export const reviews = pgTable("reviews", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id),
  globalItemId: integer("global_item_id").notNull().references(() => globalItems.id),
  overallRating: integer("overall_rating").notNull(), // 1-5
  weightRating: integer("weight_rating"),              // 1-5: "lighter than expected" to "heavier"
  durabilityRating: integer("durability_rating"),      // 1-5
  valueRating: integer("value_rating"),                // 1-5: price vs quality
  ownershipMonths: integer("ownership_months"),        // how long they've owned it
  wouldRecommend: boolean("would_recommend"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  uniqueReview: unique().on(table.userId, table.globalItemId), // one review per user per item
}));
```

**Why structured, not freeform:** The project explicitly defers freeform text until moderation infrastructure exists. Structured ratings (integer scales, boolean) require no moderation, are trivially aggregatable, and provide the "crowd-verified data" value prop.

**New service: `review.service.ts`**
- `createReview(db, userId, globalItemId, data)` -- insert or upsert
- `getReviewsForItem(db, globalItemId)` -- all reviews with user info
- `getReviewStats(db, globalItemId)` -- aggregated averages
- `getUserReview(db, userId, globalItemId)` -- single user's review

**Denormalization:** When a review is created/updated, recompute `globalItems.avgRating` from the reviews table. This avoids a JOIN on every global item list query.

---

### 6. Public Profiles and Setup Sharing

**What changes:** Users get public profile pages showing their shared setups. Setups gain an `isPublic` boolean.

**Schema changes:**

```typescript
export const setups = pgTable("setups", {
  // ... existing fields ...
  userId: integer("user_id").notNull().references(() => users.id),
  isPublic: boolean("is_public").default(false).notNull(),
  description: text("description"),  // optional setup description for public view
});
```

**New routes:**

```
GET /api/profiles/:username        -- public profile (user info + public setups)
GET /api/profiles/:username/setups -- public setups with totals
```

**New client routes:**

```
/profile/:username  -- public profile page
```

**New service: `profile.service.ts`**
- `getPublicProfile(db, username)` -- user info + public setup count + item count
- `getPublicSetups(db, userId)` -- setups where `isPublic = true` with totals

---

### 7. Discovery Feed

**What changes:** A new discovery page aggregates public content: recently shared setups, popular global items, new reviews.

**New client route: `/discover`**

**New route: `/api/discover`**
- `GET /feed` -- mixed feed of recent public activity
- `GET /popular-items` -- global items sorted by ownerCount
- `GET /recent-setups` -- recently published public setups

**New service: `discover.service.ts`**
- `getDiscoveryFeed(db, cursor, limit)` -- cursor-paginated feed
- `getPopularItems(db, limit)` -- top global items by owner count
- `getRecentPublicSetups(db, cursor, limit)` -- latest public setups

**Query pattern:** No separate "feed" table. Discovery queries are real-time aggregations over existing tables with appropriate indexes:

```sql
-- Popular items
SELECT * FROM global_items ORDER BY owner_count DESC LIMIT 20;

-- Recent public setups with user info
SELECT s.*, u.username, u.avatar_url
FROM setups s
JOIN users u ON s.user_id = u.id
WHERE s.is_public = true
ORDER BY s.updated_at DESC
LIMIT 20;
```

**Indexes needed:**
- `globalItems.ownerCount` DESC for popular items
- `setups.isPublic` + `setups.updatedAt` DESC for recent public setups
- `reviews.globalItemId` for review aggregation

---

### 8. Image Storage: Local to S3-Compatible (MinIO)

**What changes:** Images move from local filesystem (`./uploads/`) to S3-compatible object storage (MinIO, self-hosted). The image service changes from file write to S3 PUT, and image URLs change from `/uploads/filename.jpg` to presigned S3 URLs or a proxy endpoint.

**Why MinIO:** Self-hosted, S3-compatible API (use standard `@aws-sdk/client-s3`), handles multi-instance deployment, no vendor lock-in. Runs as a single Docker container alongside the app.

**New image flow:**

```
Upload:
  Client -> POST /api/images (multipart) -> image.service.ts
    -> S3 PutObject to MinIO bucket
    -> Return { filename: "uuid.jpg" }

Serve:
  Option A: Presigned GET URL (generated on demand, expires in 1h)
    -> Client receives presigned URL in item response
    -> Browser fetches directly from MinIO

  Option B: Proxy through app server
    -> GET /api/images/:filename -> S3 GetObject -> stream to client
    -> Simpler CORS, but adds server load
```

**Recommendation:** Start with Option B (proxy) for simplicity. The existing pattern of returning `imageFilename` in item responses stays the same; only the serving endpoint changes from `serveStatic("/uploads/*")` to a dynamic handler that streams from S3. Migrate to presigned URLs later if performance demands it.

**image.service.ts changes:**

```typescript
// CURRENT
await Bun.write(join(uploadsDir, filename), buffer);

// TARGET
import { S3Client, PutObjectCommand, GetObjectCommand } from "@aws-sdk/client-s3";
const s3 = new S3Client({
  endpoint: process.env.S3_ENDPOINT,      // e.g., http://minio:9000
  region: "us-east-1",
  credentials: {
    accessKeyId: process.env.S3_ACCESS_KEY!,
    secretAccessKey: process.env.S3_SECRET_KEY!,
  },
  forcePathStyle: true, // required for MinIO
});

await s3.send(new PutObjectCommand({
  Bucket: "gearbox-images",
  Key: filename,
  Body: Buffer.from(buffer),
  ContentType: contentType,
}));
```

**Migration of existing images:** One-time script uploads all files from `./uploads/` to the MinIO bucket.

---

## New vs Modified Components -- Complete Inventory

### New Files

| File | Purpose |
|------|---------|
| `src/db/schema.ts` | Full rewrite from sqlite-core to pg-core |
| `src/db/index.ts` | Postgres connection via postgres.js |
| `src/server/middleware/oidcAuth.ts` | OIDC auth middleware wrapping @hono/oidc-auth |
| `src/server/middleware/extractUser.ts` | Resolves OIDC subject to local userId, sets in context |
| `src/server/services/globalItem.service.ts` | Global item CRUD, search, stats |
| `src/server/services/review.service.ts` | Structured review CRUD, aggregation |
| `src/server/services/profile.service.ts` | Public profile queries |
| `src/server/services/discover.service.ts` | Discovery feed queries |
| `src/server/routes/globalItems.ts` | Global item endpoints |
| `src/server/routes/reviews.ts` | Review endpoints |
| `src/server/routes/profiles.ts` | Public profile endpoints |
| `src/server/routes/discover.ts` | Discovery feed endpoints |
| `src/client/routes/discover.tsx` | Discovery page |
| `src/client/routes/profile/$username.tsx` | Public profile page |
| `src/client/routes/items/$id.tsx` | Global item detail page |
| `src/client/hooks/useGlobalItems.ts` | Global item search/detail hooks |
| `src/client/hooks/useReviews.ts` | Review hooks |
| `src/client/hooks/useProfile.ts` | Profile hooks |
| `src/client/hooks/useDiscover.ts` | Discovery feed hooks |
| `src/shared/schemas.ts` | Review schemas, global item schemas (extend existing) |
| `tests/helpers/db.ts` | Rewrite to use PGlite |
| `e2e/seed.ts` | Rewrite for Postgres + new schema |
| Migration script | One-time SQLite-to-Postgres data migration |
| Image migration script | One-time upload of ./uploads/ to MinIO |

### Modified Files (Every Service + Route)

| File | What Changes |
|------|-------------|
| Every service file (`*.service.ts`) | All functions become `async`, gain `userId` parameter, queries scoped by userId |
| Every route file (`routes/*.ts`) | Extract `userId` from context, pass to services, await async service calls |
| `src/server/index.ts` | Replace auth middleware chain, add new route registrations, remove serveStatic for uploads |
| `src/shared/schemas.ts` | Add review/globalItem/profile schemas |
| `src/shared/types.ts` | Add new types from new schemas + Drizzle tables |
| `src/server/services/auth.service.ts` | Remove password/session management, keep API key management (add userId scoping) |
| `src/server/routes/auth.ts` | Replace login/setup with OIDC callback handling |
| `src/server/services/image.service.ts` | Replace filesystem writes with S3 operations |
| `src/server/routes/images.ts` | Add proxy endpoint for S3 reads, remove serveStatic |
| `src/client/hooks/useAuth.ts` | Adapt to OIDC-based auth state |
| `src/client/routes/login.tsx` | Redirect to OIDC provider instead of local login form |
| `src/server/mcp/` | All MCP tools gain userId scoping |
| All test files | Switch to PGlite, update for async services, add userId to test data |

### Removed Files

| File | Reason |
|------|--------|
| `sessions` table in schema | OIDC handles sessions |
| Password hashing logic in auth.service | OIDC handles passwords |

---

## Data Flow Changes

### Existing Flows (Modified)

```
CURRENT:
  useItems() -> GET /api/items -> getAllItems(db) -> all items in db

TARGET:
  useItems() -> GET /api/items -> getAllItems(db, userId) -> items WHERE userId = X
```

This pattern applies uniformly to items, categories, threads, setups, settings, and API keys.

### New Flows

```
Global Item Search:
  User types in search box on Discover page
    -> useGlobalItems(query) -> GET /api/global-items?q=tent
    -> searchGlobalItems(db, "tent")
    -> Postgres full-text search on global_items
    -> Return results with ownerCount, avgRating

Add Global Item to Collection:
  User clicks "Add to Collection" on global item
    -> POST /api/items { name, weightGrams, ..., globalItemId: 42 }
    -> createItem(db, userId, data) with globalItemId
    -> Increment globalItems.ownerCount
    -> Item appears in user's collection

Submit Review:
  User clicks "Rate" on a global item they own
    -> POST /api/reviews { globalItemId, overallRating, ... }
    -> createReview(db, userId, data)
    -> Recompute globalItems.avgRating
    -> Review appears on item detail page

View Public Setup:
  Visitor navigates to /profile/username
    -> useProfile(username) -> GET /api/profiles/username
    -> getPublicProfile(db, username)
    -> Return user info + public setups list
    -> Click setup -> full setup detail with items and totals

Discovery Feed:
  User visits /discover
    -> useDiscover() -> GET /api/discover/feed
    -> getDiscoveryFeed(db, cursor, limit)
    -> Mixed content: popular items, recent setups, trending reviews
    -> Cursor-based pagination for infinite scroll
```

---

## Build Order (Dependency-Aware)

The transformation has a strict dependency chain. Each phase must complete before the next can begin.

```
Phase 1: Database Migration (SQLite -> Postgres)
  ├── Rewrite src/db/schema.ts from sqliteTable to pgTable
  ├── Rewrite src/db/index.ts for postgres.js connection
  ├── Rewrite tests/helpers/db.ts for PGlite
  ├── Make all service functions async
  ├── Update all route handlers to await service calls
  ├── Generate fresh Drizzle migrations
  ├── Update e2e/seed.ts for Postgres
  ├── Fix all tests (async assertions, PGlite)
  ├── Docker compose: add Postgres container
  └── Data migration script (SQLite -> Postgres, one-time)
  DEPENDENCY: None. This is the foundation.
  RISK: Highest risk phase. Touches every file. Must be done first and fully.

Phase 2: Multi-User Data Model (userId on all tables)
  ├── Add userId column to items, categories, threads, setups, settings, apiKeys
  ├── Update users table (add externalId, remove passwordHash)
  ├── Add userId parameter to every service function
  ├── Update every route to extract userId from context and pass to services
  ├── Add user creation/lookup service (getOrCreateUser)
  ├── Seed "Uncategorized" category per user
  ├── Update all tests with userId in test data
  ├── Update MCP tools with userId scoping
  └── Drizzle migration for new columns + FK constraints
  DEPENDENCY: Phase 1 (Postgres schema must be in place)
  RISK: Medium. Mechanical but broad. Every query changes.

Phase 3: External Auth (OIDC)
  ├── Install @hono/oidc-auth
  ├── Create OIDC middleware wrapper
  ├── Create extractUser middleware
  ├── Replace requireAuth with OIDC auth chain
  ├── Remove password-based auth routes (login, setup, password change)
  ├── Remove sessions table
  ├── Update API key auth to work alongside OIDC (bypass for X-API-Key)
  ├── Update client login page to redirect to OIDC provider
  ├── Update useAuth hook for OIDC-based state
  ├── Deploy Authentik (Docker compose)
  └── Configure Authentik provider (OIDC client)
  DEPENDENCY: Phase 2 (users table with externalId must exist)
  RISK: Medium. External dependency on Authentik. Test OIDC flow thoroughly.

Phase 4: Image Storage (S3/MinIO)
  ├── Install @aws-sdk/client-s3
  ├── Update image.service.ts (S3 put instead of file write)
  ├── Add image proxy route (GET /api/images/:filename -> S3 get)
  ├── Remove serveStatic("/uploads/*")
  ├── Deploy MinIO container (Docker compose)
  ├── Migrate existing images to MinIO bucket
  └── Update image deletion to S3 DeleteObject
  DEPENDENCY: None technically, but deploy alongside Phase 1-3 infra.
  RISK: Low. Well-understood pattern. S3 SDK is mature.

Phase 5: Global Items + Reviews
  ├── Create globalItems table
  ├── Add globalItemId FK to items table
  ├── Create reviews table
  ├── Create globalItem.service.ts
  ├── Create review.service.ts
  ├── Create routes/globalItems.ts
  ├── Create routes/reviews.ts
  ├── Add Postgres full-text search index on globalItems.name
  ├── Create client hooks (useGlobalItems, useReviews)
  ├── Create global item detail page (/items/:id)
  ├── Add "Add to Collection" flow
  ├── Add review submission UI
  └── Seed initial global items (CSV import)
  DEPENDENCY: Phases 1-3 (Postgres, multi-user, auth)
  RISK: Medium. Full-text search needs Postgres-specific config.

Phase 6: Public Profiles + Discovery
  ├── Add isPublic to setups table
  ├── Create profile.service.ts
  ├── Create discover.service.ts
  ├── Create routes/profiles.ts
  ├── Create routes/discover.ts
  ├── Create /profile/:username client route
  ├── Create /discover client route
  ├── Add setup sharing toggle UI
  ├── Add discovery feed with cursor pagination
  └── Add indexes for discovery queries
  DEPENDENCY: Phase 5 (global items provide content for discovery)
  RISK: Low. Standard CRUD + queries. Design-heavy more than technically complex.
```

**Phase ordering rationale:**
1. Postgres must come first because everything else depends on it (OIDC needs async queries, multi-tenancy needs FK constraints on userId, global items need full-text search).
2. Multi-user data model before auth because the schema must support userId before auth can populate it.
3. Auth before platform features because platform features require knowing who the user is.
4. Image storage is independent but bundles well with infrastructure setup.
5. Global items before discovery because the discovery feed aggregates global item data.

---

## Patterns to Follow

### Pattern 1: userId Injection via Middleware -> Context -> Service

**What:** Middleware resolves the authenticated user and stores `userId` in Hono context. Routes extract it and pass to services. Services never access auth state directly.

**Why:** Maintains the existing testable service pattern. Services remain pure functions of `(db, userId, data)` with no HTTP awareness.

```typescript
// Middleware sets
c.set("userId", user.id);

// Route extracts
const userId = c.get("userId");
const items = await getAllItems(db, userId);

// Service uses
export async function getAllItems(db: Db, userId: number) { ... }
```

### Pattern 2: Dual Auth Path (OIDC + API Key)

**What:** OIDC handles browser sessions. API keys bypass OIDC for programmatic access (MCP, scripts). Both resolve to a userId.

```typescript
app.use("/api/*", async (c, next) => {
  if (c.req.method === "GET") return next(); // public read

  // API key takes priority (no OIDC redirect for API clients)
  const apiKey = c.req.header("X-API-Key");
  if (apiKey) {
    const userId = await verifyApiKey(db, apiKey);
    if (!userId) return c.json({ error: "Invalid API key" }, 401);
    c.set("userId", userId);
    return next();
  }

  // Fall through to OIDC
  return oidcAuthMiddleware()(c, next);
});
```

### Pattern 3: Global Item Linking (User Item -> Global Item)

**What:** User items optionally reference a global item. The user's local copy can override fields. The global item tracks aggregate stats (ownerCount, avgRating).

**Why:** Separates user-customizable data from canonical data. A user might note a different weight for their specific unit, or a sale price. The global item maintains the manufacturer spec.

### Pattern 4: Cursor-Based Pagination for Discovery

**What:** Discovery feed uses cursor-based pagination (`?cursor=<last_id>&limit=20`) instead of offset-based (`?page=2`). The cursor is the ID or timestamp of the last returned item.

**Why:** Offset pagination breaks when new content is inserted (items shift between pages). Cursor pagination is stable, performant (uses indexed seek), and natural for infinite scroll.

```typescript
export async function getRecentPublicSetups(db: Db, cursor?: number, limit = 20) {
  let query = db.select(...).from(setups)
    .where(eq(setups.isPublic, true))
    .orderBy(desc(setups.updatedAt))
    .limit(limit);

  if (cursor) {
    query = query.where(and(eq(setups.isPublic, true), lt(setups.id, cursor)));
  }

  return query;
}
```

---

## Anti-Patterns to Avoid

### Anti-Pattern 1: Shared Global Categories

**What people do:** Make categories global (shared across all users) to avoid duplication.
**Why bad:** Category naming is personal. One user's "Shelter" is another's "Tents". Shared categories create namespace conflicts and force consensus. Global items use a separate `categorySlug` for platform-level categorization.
**Do instead:** User-scoped categories. Each user creates their own. Seed "Uncategorized" per user.

### Anti-Pattern 2: Row-Level Security in Application Code

**What people do:** Add complex permission checks in every query (can this user see this item? is this setup public?).
**Why bad:** Scattered authorization logic is hard to audit and easy to miss.
**Do instead:** Consistent pattern: private data always filtered by `WHERE userId = X`. Public data (profiles, shared setups, global items) has dedicated read-only endpoints. No mixed-permission queries.

### Anti-Pattern 3: Storing OIDC Tokens in the Database

**What people do:** Save access/refresh tokens from the OIDC provider in a local sessions table.
**Why bad:** `@hono/oidc-auth` already handles this via signed JWT cookies. Storing tokens adds a session table, cleanup logic, and token refresh management that the middleware handles automatically.
**Do instead:** Let the OIDC middleware manage session state. Only store the `externalId` (OIDC subject) in the users table for local user lookup.

### Anti-Pattern 4: Migrating SQLite Data via Drizzle Migrations

**What people do:** Try to use `drizzle-kit push` or `drizzle-kit migrate` to move data from SQLite to Postgres.
**Why bad:** Drizzle migrations are DDL (schema changes), not DML (data transfer). They cannot move data between databases. The schema syntax is dialect-specific.
**Do instead:** Write a one-time migration script: read from SQLite, transform, insert into Postgres. Use Drizzle's query builder for both reads and writes.

### Anti-Pattern 5: Making All Queries Public-Aware from Day 1

**What people do:** Add `isPublic` checks to every query from the start, even before discovery features exist.
**Why bad:** Adds complexity to every query before it's needed. Multi-tenancy (userId scoping) is sufficient for phases 1-4.
**Do instead:** Add `isPublic` only when implementing profiles/discovery (Phase 6). Until then, all data is private-by-default (scoped by userId).

---

## Scalability Considerations

| Concern | At 10 users | At 1K users | At 10K users |
|---------|-------------|-------------|--------------|
| Database | Single Postgres, fine | Single Postgres, fine | Connection pooling, read replicas |
| Image storage | MinIO single instance | MinIO, fine to 1TB+ | CDN in front of MinIO |
| Global item search | Basic LIKE query | Postgres full-text search | Add search index, consider pg_trgm |
| Discovery feed | Direct queries | Add query caching (Redis) | Materialized views |
| Auth | Authentik single instance | Authentik, fine | Authentik HA setup |

---

## Sources

- [Drizzle ORM PostgreSQL docs](https://orm.drizzle.team/docs/get-started/postgresql-new) -- pg-core schema syntax, driver support
- [Drizzle ORM Migrations docs](https://orm.drizzle.team/docs/migrations) -- migration workflow
- [Drizzle same schema discussion #3396](https://github.com/drizzle-team/drizzle-orm/discussions/3396) -- confirms no cross-dialect schema sharing, PGlite recommendation
- [@hono/oidc-auth NPM](https://www.npmjs.com/package/@hono/oidc-auth) -- OIDC middleware for Hono, stateless JWT sessions
- [Authentik docs - OAuth2 provider](https://docs.goauthentik.io/add-secure-apps/providers/oauth2/) -- OIDC provider configuration
- [Authentik vs Authelia vs Keycloak 2026](https://blog.elest.io/authentik-vs-authelia-vs-keycloak-choosing-the-right-self-hosted-identity-provider-in-2026/) -- provider comparison, resource usage
- [State of Open-Source Identity 2025](https://blog.houseoffoss.com/post/the-state-of-open-source-identity-in-2025-authentik-vs-authelia-vs-keycloak-vs-zitadel) -- ecosystem landscape
- [MinIO GitHub](https://github.com/minio/minio) -- S3-compatible self-hosted object storage
- [MinIO S3 setup guide](https://oneuptime.com/blog/post/2026-01-27-minio-s3-compatible-storage/view) -- deployment and Node.js integration

---
*Architecture research for: GearBox v2.0 Platform Foundation*
*Researched: 2026-04-03*
