GearBox/.planning/phases/11-candidate-ranking/11-01-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
11-candidate-ranking	01	execute	1		src/db/schema.ts tests/helpers/db.ts src/server/services/thread.service.ts src/server/routes/threads.ts src/shared/schemas.ts src/shared/types.ts tests/services/thread.service.test.ts tests/routes/threads.test.ts	true	RANK-01 RANK-04 RANK-05	truths artifacts key_links Candidates returned from getThreadWithCandidates are ordered by sort_order ascending Calling reorderCandidates with a new ID sequence updates sort_order values to match that sequence PATCH /api/threads/:id/candidates/reorder returns 200 and persists new order reorderCandidates returns error when thread status is not active New candidates created via createCandidate are appended to end of rank (highest sort_order + 1000) path provides contains src/db/schema.ts sortOrder REAL column on threadCandidates sortOrder path provides contains src/shared/schemas.ts reorderCandidatesSchema Zod validator reorderCandidatesSchema path provides contains src/shared/types.ts ReorderCandidates type ReorderCandidates path provides exports src/server/services/thread.service.ts reorderCandidates function + ORDER BY sort_order + createCandidate sort_order appending reorderCandidates path provides contains src/server/routes/threads.ts PATCH /:id/candidates/reorder endpoint candidates/reorder path provides contains tests/helpers/db.ts sort_order column in CREATE TABLE thread_candidates sort_order from to via pattern src/server/routes/threads.ts src/server/services/thread.service.ts reorderCandidates(db, threadId, orderedIds) reorderCandidates from to via pattern src/server/routes/threads.ts src/shared/schemas.ts zValidator with reorderCandidatesSchema reorderCandidatesSchema from to via pattern src/server/services/thread.service.ts src/db/schema.ts threadCandidates.sortOrder in ORDER BY and UPDATE threadCandidates.sortOrder

Add sort_order column to thread_candidates, implement reorder service and API endpoint, and update candidate ordering throughout the backend.

Purpose: Provides the persistence layer for drag-to-reorder ranking (RANK-01, RANK-04) and enforces the resolved-thread guard (RANK-05). The frontend plan (11-02) depends on this. Output: Working PATCH /api/threads/:id/candidates/reorder endpoint, sort_order-based ordering in getThreadWithCandidates, sort_order appending in createCandidate, full test coverage.

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

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/phases/11-candidate-ranking/11-CONTEXT.md @.planning/phases/11-candidate-ranking/11-RESEARCH.md

From src/db/schema.ts (threadCandidates table — add sortOrder here):

export const threadCandidates = sqliteTable("thread_candidates", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  threadId: integer("thread_id").notNull().references(() => threads.id, { onDelete: "cascade" }),
  name: text("name").notNull(),
  weightGrams: real("weight_grams"),
  priceCents: integer("price_cents"),
  categoryId: integer("category_id").notNull().references(() => categories.id),
  notes: text("notes"),
  productUrl: text("product_url"),
  imageFilename: text("image_filename"),
  status: text("status").notNull().default("researching"),
  pros: text("pros"),
  cons: text("cons"),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

From src/server/services/thread.service.ts (key functions to modify):

type Db = typeof prodDb;
export function getThreadWithCandidates(db: Db, threadId: number) // add .orderBy(threadCandidates.sortOrder)
export function createCandidate(db: Db, threadId: number, data: ...) // add sort_order = max + 1000
export function resolveThread(db: Db, threadId: number, candidateId: number) // existing status check pattern to reuse

From src/shared/schemas.ts (existing patterns):

export const createCandidateSchema = z.object({ ... });
export const resolveThreadSchema = z.object({ candidateId: z.number().int().positive() });

From src/shared/types.ts (add new type):

export type ResolveThread = z.infer<typeof resolveThreadSchema>;
// Add: export type ReorderCandidates = z.infer<typeof reorderCandidatesSchema>;

From src/server/routes/threads.ts (route pattern):

type Env = { Variables: { db?: any } };
const app = new Hono<Env>();
// Pattern: app.post("/:id/resolve", zValidator("json", resolveThreadSchema), (c) => { ... });

From src/client/lib/api.ts:

export async function apiPatch<T>(url: string, body: unknown): Promise<T>;

From tests/helpers/db.ts (thread_candidates CREATE TABLE — add sort_order):

CREATE TABLE thread_candidates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  thread_id INTEGER NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  weight_grams REAL,
  price_cents INTEGER,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  notes TEXT,
  product_url TEXT,
  image_filename TEXT,
  status TEXT NOT NULL DEFAULT 'researching',
  pros TEXT,
  cons TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)

Task 1: Schema, migration, service layer, and tests for sort_order + reorder src/db/schema.ts, tests/helpers/db.ts, src/server/services/thread.service.ts, src/shared/schemas.ts, src/shared/types.ts, tests/services/thread.service.test.ts - Test: getThreadWithCandidates returns candidates ordered by sort_order ascending (create 3 candidates with different sort_orders, verify order) - Test: reorderCandidates(db, threadId, [id3, id1, id2]) updates sort_order so querying returns [id3, id1, id2] - Test: reorderCandidates returns { success: false, error } when thread status is "resolved" - Test: createCandidate assigns sort_order = max existing sort_order + 1000 (first candidate gets 1000, second gets 2000) - Test: reorderCandidates returns { success: false } when thread does not exist 1. **Schema** (`src/db/schema.ts`): Add `sortOrder: real("sort_order").notNull().default(0)` to the `threadCandidates` table definition.

2. **Migration**: Run `bun run db:generate` to produce the Drizzle migration SQL. Then apply it with `bun run db:push`. After applying, run a data backfill to space existing candidates:
   ```sql
   UPDATE thread_candidates SET sort_order = (
     SELECT (ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY created_at)) * 1000
     FROM thread_candidates AS tc2 WHERE tc2.id = thread_candidates.id
   );
   ```
   Execute this backfill via the Drizzle migration custom SQL or a small script.

3. **Test helper** (`tests/helpers/db.ts`): Add `sort_order REAL NOT NULL DEFAULT 0` to the CREATE TABLE thread_candidates statement (after the `cons TEXT` line, before `created_at`).

4. **Zod schema** (`src/shared/schemas.ts`): Add:
   ```typescript
   export const reorderCandidatesSchema = z.object({
     orderedIds: z.array(z.number().int().positive()).min(1),
   });
   ```

5. **Types** (`src/shared/types.ts`): Add import of `reorderCandidatesSchema` and:
   ```typescript
   export type ReorderCandidates = z.infer<typeof reorderCandidatesSchema>;
   ```

6. **Service** (`src/server/services/thread.service.ts`):
   - In `getThreadWithCandidates`: Add `.orderBy(threadCandidates.sortOrder)` to the candidateList query (after `.where()`).
   - In `createCandidate`: Before inserting, query `MAX(sort_order)` from threadCandidates where threadId matches. Set `sortOrder: (maxRow?.maxOrder ?? 0) + 1000` in the `.values()` call. Use `sql<number>` template for the MAX query.
   - Add new exported function `reorderCandidates(db, threadId, orderedIds)`:
     - Wrap in `db.transaction()`.
     - Verify thread exists and `status === "active"` (return `{ success: false, error: "Thread not active" }` if not).
     - Loop through `orderedIds`, UPDATE each candidate's `sortOrder` to `(index + 1) * 1000`.
     - Return `{ success: true }`.

7. **Tests** (`tests/services/thread.service.test.ts`):
   - Import `reorderCandidates` from the service.
   - Add a new `describe("reorderCandidates", () => { ... })` block with the behavior tests listed above.
   - Add test for `getThreadWithCandidates` ordering by sort_order (create candidates, set different sort_orders manually via db, verify order).
   - Add test for `createCandidate` sort_order appending.

bun test tests/services/thread.service.test.ts All existing thread service tests pass (28+) plus 5+ new tests for reorderCandidates, sort_order ordering, sort_order appending. sortOrder column exists in schema with REAL type. Task 2: PATCH reorder route + route tests src/server/routes/threads.ts, tests/routes/threads.test.ts - Test: PATCH /api/threads/:id/candidates/reorder with valid orderedIds returns 200 + { success: true } - Test: After PATCH reorder, GET /api/threads/:id returns candidates in the new order - Test: PATCH /api/threads/:id/candidates/reorder on a resolved thread returns 400 - Test: PATCH /api/threads/:id/candidates/reorder with empty body returns 400 (Zod validation) 1. **Route** (`src/server/routes/threads.ts`): - Import `reorderCandidatesSchema` from `../../shared/schemas.ts`. - Import `reorderCandidates` from `../services/thread.service.ts`. - Add PATCH route BEFORE the resolution route (to avoid param conflicts): ```typescript app.patch( "/:id/candidates/reorder", zValidator("json", reorderCandidatesSchema), (c) => { const db = c.get("db"); const threadId = Number(c.req.param("id")); const { orderedIds } = c.req.valid("json"); const result = reorderCandidates(db, threadId, orderedIds); if (!result.success) return c.json({ error: result.error }, 400); return c.json({ success: true }); }, ); ```

2. **Route tests** (`tests/routes/threads.test.ts`):
   - Add a new `describe("PATCH /api/threads/:id/candidates/reorder", () => { ... })` block.
   - Test: Create a thread with 3 candidates via API, PATCH reorder with reversed IDs, GET thread and verify candidates array is in the new order.
   - Test: Resolve a thread, then PATCH reorder returns 400.
   - Test: PATCH with invalid body (empty orderedIds array or missing field) returns 400.

bun test tests/routes/threads.test.ts PATCH /api/threads/:id/candidates/reorder returns 200 on active thread + persists order. Returns 400 on resolved thread. All existing route tests still pass. ```bash # Full test suite — all existing + new tests green bun test

Verify sort_order column exists in schema

grep -n "sortOrder" src/db/schema.ts

Verify reorder endpoint registered

grep -n "candidates/reorder" src/server/routes/threads.ts

Verify test helper updated

grep -n "sort_order" tests/helpers/db.ts

</verification>

<success_criteria>
- sort_order REAL column added to threadCandidates schema and test helper
- getThreadWithCandidates returns candidates sorted by sort_order ascending
- createCandidate appends new candidates at max sort_order + 1000
- reorderCandidates service function updates sort_order in transaction, rejects resolved threads
- PATCH /api/threads/:id/candidates/reorder validated with Zod, returns 200/400 correctly
- All existing tests pass with zero regressions + 8+ new tests
</success_criteria>

<output>
After completion, create `.planning/phases/11-candidate-ranking/11-01-SUMMARY.md`
</output>