## Codebase Patterns
- `alias(table, 'alias_name')` from `drizzle-orm/sqlite-core` enables self-joins (e.g., clients → parentClients for hierarchy)
- `sql<T>\`CASE WHEN ... THEN ... ELSE ... END\`` for conditional SELECT fields (e.g., clientName vs subClientName based on parentId)
- `or(like(col1, pattern), like(col2, pattern))` for multi-column search; SQLite LIKE on NULL columns safely returns NULL (falsy) so OR is safe
- Vite configs use `.mts` extension (not `.ts`) to avoid ESM/CJS conflict with electron-forge's externalize-deps plugin
- electron-trpc uses `exposeElectronTRPC()` in preload and `createIPCHandler({ router, windows })` in main; renderer uses `ipcLink()` from `electron-trpc/renderer`
- appRouter lives at `src/main/router/index.ts`; renderer client at `src/renderer/lib/trpc.ts`
- `@/*` path alias maps to `src/renderer/*` (configured in tsconfig.json paths)
- Drizzle ORM with better-sqlite3 (sync driver): SELECT queries MUST end with `.all()` to execute; INSERT/UPDATE/DELETE MUST end with `.run()`
- `inArray(column, values)` works with nullable columns when values is `string[]` (TypeScript covariance allows string[] → (string | null)[])
- All DB tables use `CREATE TABLE IF NOT EXISTS` for non-destructive migrations
- All IDs are UUIDs generated via `crypto.randomUUID()`
- TypeScript strict mode + noUncheckedIndexedAccess enabled; always account for possible undefined on array access
- electron-store@8 (CJS) used for app settings; use lazy init pattern `getStore()` like `getDb()` to avoid calling before app ready
- ESLint uses `eslint-import-resolver-typescript` to resolve `@/*` aliases; configured in `.eslintrc.json` under `settings.import/resolver`
- App settings (sidebar state, etc.) exposed via `settings` tRPC sub-router for type-safe renderer access
- `z.string().nullable().optional()` in tRPC inputs enables three-state semantics: undefined = don't change, null = clear, string = set value
- NewTaskDialog component at `src/renderer/components/tasks/NewTaskDialog.tsx` accepts `defaultProjectId` and `defaultStatus` props for reuse in Kanban column "+ Add" buttons
- `date-fns` is available as a transitive dependency of `react-day-picker` (shadcn/ui calendar)
- GanttChart component at `src/renderer/components/timeline/GanttChart.tsx` is reusable: accepts `defaultProjectId` to scope to a project (for US-015 inline timeline)
- AddCheckpointDialog at `src/renderer/components/timeline/AddCheckpointDialog.tsx` accepts `defaultProjectId` — hides project select when provided
- TanStack Router `validateSearch` with Zod schema for passing selected-item IDs via URL search params (e.g., `?projectId=...`)
---

## 2026-02-19 - US-002
- Installed `better-sqlite3`, `drizzle-orm` (runtime) and `@types/better-sqlite3`, `drizzle-kit` (dev)
- Created `src/main/db/schema.ts`: 5 tables (clients, projects, tasks, checkpoints, notes) with exported InferSelectModel/InferInsertModel types
- Created `src/main/db/index.ts`: `initDb()` opens/creates `adiuva.db` at `app.getPath('userData')`, runs CREATE TABLE IF NOT EXISTS (non-destructive), enables WAL mode; `getDb()` singleton accessor
- Updated `src/main/index.ts`: call `initDb()` in `app.on('ready')`
- Updated `vite.main.config.mts`: externalized `better-sqlite3`
- Updated `forge.config.ts`: added `AutoUnpackNativesPlugin`
- Added `drizzle.config.ts` for drizzle-kit CLI
- Typecheck: passes with zero errors
- **Learnings for future iterations:**
  - better-sqlite3 is CommonJS with native addon; Vite must NOT bundle it — always add to rollupOptions.external
  - The CREATE TABLE IF NOT EXISTS approach satisfies "never destructive" and works perfectly in electron without needing migration file resolution
  - electron-forge rebuilds native modules automatically on `electron-forge start`; no manual rebuild step needed
  - `app.getPath('userData')` is only available after `app.on('ready')` fires — do not call earlier
---

## 2026-02-19 - US-008
- What was implemented:
  - Full `checkpointsRouter` replacing stubs in `src/main/router/index.ts`
  - Full `notesRouter` replacing stubs in `src/main/router/index.ts`
  - Added `checkpoints` and `notes` to the schema import
  - `checkpoints.list`: optional `projectId` filter, ordered by `asc(checkpoints.date)`
  - `checkpoints.create`: inserts with UUID, createdAt=Date.now(), defaults isAiSuggested/isApproved to 0
  - `checkpoints.update`: partial set for title/date/isApproved
  - `checkpoints.delete`: deletes by id, returns `{ success: true }`
  - `notes.list`: returns `{ id, projectId, title, createdAt, updatedAt }` only — no content (performance)
  - `notes.get`: returns full record or null via `.all()[0] ?? null` pattern
  - `notes.create`: inserts with UUID, createdAt=updatedAt=Date.now()
  - `notes.update`: partial set, always sets updatedAt=Date.now() regardless of which fields changed
  - `notes.delete`: deletes by id, returns `{ success: true }`
- Files changed: `src/main/router/index.ts`, `prd.json`, `progress.txt`
- **Learnings for future iterations:**
  - `notes.update` must always set `updatedAt` — build the set object with updatedAt outside the conditional block
  - `notes.list` intentionally excludes `content` column for performance; use `notes.get` for full record
  - `checkpoints.projectId` is `.notNull()` in schema (unlike tasks.projectId which is nullable) — no null coalescing needed
---

## 2026-02-19 - US-003
- What was implemented:
  - Installed: electron-trpc, @trpc/server, @trpc/client, @trpc/react-query, @tanstack/react-query, zod
  - Created `src/main/router/index.ts` with full appRouter: stub routers for health, clients, projects, tasks, checkpoints, notes, ai
  - Updated `src/preload/index.ts` to call `exposeElectronTRPC()`
  - Updated `src/main/index.ts` to call `createIPCHandler({ router: appRouter, windows: [win] })`; `createWindow()` now returns `BrowserWindow`
  - Created `src/renderer/lib/trpc.ts` with `createTRPCReact<AppRouter>()`
  - Updated `src/renderer/index.tsx` to wrap app in `TRPCProvider` + `QueryClientProvider`
  - Updated `src/renderer/routes/index.tsx` to call `trpc.health.ping.useQuery()` and display 'tRPC IPC bridge: pong'
- Files changed: package.json, package-lock.json, prd.json, src/main/index.ts, src/main/router/index.ts (new), src/preload/index.ts, src/renderer/index.tsx, src/renderer/lib/trpc.ts (new), src/renderer/routes/index.tsx
- **Learnings for future iterations:**
  - electron-trpc `exposeElectronTRPC` is imported from `electron-trpc/main` (not a separate package)
  - `ipcLink` is imported from `electron-trpc/renderer` in the renderer process
  - `createTRPCReact<AppRouter>()` requires importing the AppRouter type from the main process router — this is a type-only import so it doesn't bundle main process code into renderer
  - The TRPCProvider must wrap QueryClientProvider (or be a sibling); both need the same queryClient instance
  - Stub routers return empty arrays or null — they will be replaced in US-005 through US-008
---

## 2026-02-19 - US-004
- What was implemented:
  - Installed: electron-store@8 (CJS-compatible, for persistent app settings), @fontsource/geist (self-hosted Geist font), eslint-import-resolver-typescript (ESLint path alias fix)
  - Created `src/main/store.ts` with lazy `getStore()` pattern using electron-store
  - Added `settings` tRPC sub-router with `getSidebarCollapsed` query and `setSidebarCollapsed` mutation
  - Updated `src/renderer/components/layout/AppShell.tsx` to: persist sidebar collapse via tRPC, add right-edge 'keep scrolling for AI' vertical label with ChevronDown icon
  - Updated `src/renderer/globals.css`: replaced Google Fonts CDN with @fontsource/geist imports (weights 400/500/600)
  - Updated `index.html`: removed Google Fonts CDN links
  - Updated `.eslintrc.json`: added eslint-import-resolver-typescript to fix @/* alias resolution (fixed all 7 pre-existing lint errors)
- Files changed: .eslintrc.json, index.html, package.json, package-lock.json, src/main/router/index.ts, src/main/store.ts (new), src/renderer/components/layout/AppShell.tsx, src/renderer/globals.css
- **Learnings for future iterations:**
  - Use electron-store@8 (not v9+) — v9+ is ESM-only and breaks with CommonJS main process
  - electron-store must NOT be initialized at module import time (before app.ready); use lazy `getStore()` like `getDb()` pattern
  - For sidebar/UI state loaded from IPC: use `localState ?? queryData ?? default` pattern to avoid flash while query resolves
  - @fontsource packages are the npm equivalent of Google Fonts — import weight-specific CSS files (e.g., `@fontsource/geist/400.css`)
  - ESLint `import/no-unresolved` requires `eslint-import-resolver-typescript` with `alwaysTryTypes: true` to resolve TypeScript path aliases
  - The `writingMode: 'vertical-rl'` + `transform: 'rotate(180deg)'` CSS pattern creates bottom-to-top text for vertical affordance labels
---

## 2026-02-19 - US-006
- What was implemented:
  - Full `projectsRouter` replacing stubs in `src/main/router/index.ts`
  - Added `and` to drizzle-orm imports
  - `projects.list`: uses `and()` with optional conditions for `clientId` filter and archived filter (defaults to active only)
  - `projects.listAll`: returns only `{ id, name }` columns for dropdown use
  - `projects.get`: `.all()` then `result[0] ?? null` pattern for nullable single-record lookup
  - `projects.create`: inserts with UUID, status='active', createdAt=Date.now()
  - `projects.update`: partial set object — only sets defined fields
  - `projects.delete`: nulls `tasks.projectId` for all tasks in the project, then deletes the project
- Files changed: `src/main/router/index.ts`, `prd.json`, `progress.txt`
- **Learnings for future iterations:**
  - `and(...conditions)` from drizzle-orm accepts `(SQL | undefined)[]` — pass `undefined` for optional conditions and drizzle filters them out automatically
  - For nullable single-record queries: use `.all()` and `result[0] ?? null` (strict mode forbids `.get()` direct null return without this pattern)
  - `and()` returns `SQL<unknown> | undefined` which `.where()` accepts directly (no extra wrapping needed)

---

## 2026-02-19 - US-005
- What was implemented:
  - Full clients tRPC router replacing stubs in `src/main/router/index.ts`
  - Added imports: `eq`, `asc`, `inArray` from `drizzle-orm`; `getDb` from `../db`; `clients`, `projects`, `tasks` from `../db/schema`
  - `clients.list`: `db.select().from(clients).orderBy(asc(clients.name)).all()`
  - `clients.create`: inserts with `crypto.randomUUID()` + `Date.now()` via `.run()`
  - `clients.update`: partial update — only sets fields that are defined in input, skips if no-op
  - `clients.delete`: checks for child clients and child projects; returns `{ error: string }` payload if any exist; otherwise deletes and returns `{ success: true }`
  - `clients.deleteWithCascade`: BFS loop collects all descendant client IDs, finds their projects, nulls `projectId` on orphaned tasks, deletes projects, then deletes all clients
- Files changed: `src/main/router/index.ts`, `prd.json`, `progress.txt`
- **Learnings for future iterations:**
  - Drizzle ORM with better-sqlite3 sync driver: SELECT must call `.all()` to get an array; INSERT/UPDATE/DELETE must call `.run()` to execute — NOT calling these causes TypeScript errors (query builder ≠ result)
  - `inArray(nullableColumn, string[])` is TypeScript-safe because `string[]` is assignable to `(string | null)[]` via covariance
  - Guard against empty arrays before using `inArray` — while `allClientIds` is never empty (starts with input.id), `projectIds` could be empty; guarded with `if (projectIds.length > 0)` block
  - `@typescript-eslint/no-non-null-assertion` is configured as a warning (not error) in this project — `queue.shift()!` is fine after a `length > 0` check
---

## 2026-02-19 - US-007
- What was implemented:
  - Full `tasksRouter` replacing stubs in `src/main/router/index.ts`
  - Added imports: `or`, `like`, `sql` from `drizzle-orm`; `alias` from `drizzle-orm/sqlite-core`
  - `tasks.list`: LEFT JOINs projects → clients → parentClients (alias for self-join); CASE WHEN for clientName/subClientName breadcrumb fields; `and()` with optional conditions for projectId/status/search; `like()` OR search on title+description; CASE expression for priority ordering
  - `tasks.create`: inserts with UUID, defaults (status='todo', priority='medium'), createdAt=Date.now()
  - `tasks.update`: partial set object — only sets defined fields
  - `tasks.delete`: deletes by id, returns `{ success: true }`
- Files changed: `src/main/router/index.ts`, `prd.json`, `progress.txt`
- **Learnings for future iterations:**
  - `alias(table, 'alias_name')` is from `drizzle-orm/sqlite-core` (NOT `drizzle-orm`) for SQLite self-joins
  - `sql<T>\`CASE WHEN ${col} IS NOT NULL THEN ${alias.col} ELSE ${col} END\`` for conditional field selection using drizzle template literals
  - `or(like(col1, pattern), like(col2, pattern))` composes safely — null columns evaluate to NULL (falsy) in WHERE
  - For priority ordering: `asc(sql\`CASE ${tasks.priority} WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 ELSE 4 END\`)` puts high priority first
---

## 2026-02-19 - US-009
- What was implemented:
  - Verified existing `ProjectSidebar` component at `src/renderer/components/projects/ProjectSidebar.tsx` satisfies all US-009 acceptance criteria
  - New Project button at top using shadcn/ui Button + `clients.create` mutation with auto-rename on success
  - Kebab context menu (DropdownMenu) with Rename, New Sub-Project, Delete actions
  - Inline rename: Input replaces label, Enter saves via `clients.update`, Escape cancels, blur saves
  - Delete: AlertDialog with two stages — initial confirm, then cascade-warn if children exist (uses `clients.delete` first, falls back to `clients.deleteWithCascade`)
  - Hierarchical tree via `buildTree()` function (parent-child via `clients.parentId`)
  - Empty state with EmptyMedia + call-to-action button
  - All mutations invalidate `clients.list` query for immediate tree refresh
  - Typecheck passes (zero errors), lint passes (1 non-null assertion warning, guarded)
- Files changed: `scripts/ralph/prd.json`, `scripts/ralph/progress.txt`
- **Learnings for future iterations:**
  - The ProjectSidebar was built as part of US-004 app shell work but the US-009 story wasn't marked as passing — always check existing code before implementing
  - `useCallback` ref pattern (`ref={callbackRef}`) is used for auto-focus + select on mount without useEffect
  - The two-stage delete flow (try simple delete first → if error, show cascade option) maps well to the backend's `clients.delete` (guards) + `clients.deleteWithCascade` (force) pattern
---

## 2026-02-19 - US-010
- What was implemented:
  - Rewrote `ProjectSidebar` from a client-hierarchy tree to a project-centric sidebar grouped by client
  - Projects grouped by `clientId` using Collapsible headers; projects without a client appear under "Internal / No Client"
  - Search input filters projects by name in real-time (auto-expands all groups when searching)
  - Show/hide archived projects via Switch toggle (queries `projects.list` with `includeArchived`)
  - Context menu per project (DropdownMenu): Edit Client (Dialog + Select to assign/change/remove client), Archive/Unarchive, Delete (AlertDialog)
  - Clicking a project sets `projectId` in search params → renders ProjectDetail placeholder in right pane
  - Active project highlighted with `bg-sidebar-accent`
  - Updated `projects.update` tRPC procedure to accept `clientId: z.string().nullable().optional()` (allows unlinking from client)
  - Created placeholder `ProjectDetail` component (full implementation deferred to US-013)
  - Installed shadcn/ui: dialog, select, switch
- Files changed: `src/renderer/components/projects/ProjectSidebar.tsx`, `src/renderer/routes/projects.tsx`, `src/renderer/components/projects/ProjectDetail.tsx` (new), `src/main/router/index.ts`, `src/renderer/components/ui/dialog.tsx` (new), `src/renderer/components/ui/select.tsx` (new), `src/renderer/components/ui/switch.tsx` (new), `scripts/ralph/prd.json`, `scripts/ralph/progress.txt`
- **Learnings for future iterations:**
  - TanStack Router `validateSearch` with Zod schema is the cleanest way to pass selected-item IDs via URL search params without creating nested routes
  - `Route.useNavigate()` returns a typed navigate fn; use `void navigate({ search: { ... } })` to avoid unhandled promise warnings
  - For project grouping, query both `projects.list` and `clients.list` separately then join in-memory via a Map — avoids complex SQL joins for display-only data
  - `projects.update` with `clientId: z.string().nullable().optional()` allows three states: undefined (don't change), null (unlink), string (assign)
  - Auto-expanding all groups during search (`effectiveExpanded` computed from grouped keys) gives a better UX than forcing users to manually expand
---

## 2026-02-20 - US-011
- What was implemented:
  - Full Global Tasks view UI at `/tasks` route
  - 4 stat cards (Total Tasks, To Do, In Progress, Completed) using shadcn/ui Card components with Lucide icons, reactively updated from unfiltered `tasks.list` query
  - Search bar with 300ms debounce using shadcn/ui Input + Search icon
  - Status filter tabs using shadcn/ui Tabs (All | To Do | In Progress | Completed)
  - "Order by" dropdown using shadcn/ui DropdownMenu (Due Date | Priority | Created Date)
  - Task rows with: shadcn/ui Checkbox (toggles todo↔done), title (bold 14px), description (muted truncated), priority Badge (HIGH=destructive, MEDIUM=secondary, LOW=outline green), due date chip (calendar icon + formatted date), breadcrumb (Client > Sub-Client > Project with ChevronRight separators), assignee (User icon + name)
  - Completed task rows have green-tinted background (`bg-green-50 border-green-200`)
  - NewTaskDialog component with: Input for title (required), Textarea for description, Select for priority/status, Popover+Calendar for due date, Select for project (from `projects.listAll`), Input for assignee
  - Installed shadcn/ui components: card, tabs, checkbox, badge, textarea, popover, calendar
- Files changed: `src/renderer/routes/tasks.tsx`, `src/renderer/components/tasks/NewTaskDialog.tsx` (new), `src/renderer/components/ui/card.tsx` (new), `src/renderer/components/ui/tabs.tsx` (new), `src/renderer/components/ui/checkbox.tsx` (new), `src/renderer/components/ui/badge.tsx` (new), `src/renderer/components/ui/textarea.tsx` (new), `src/renderer/components/ui/popover.tsx` (new), `src/renderer/components/ui/calendar.tsx` (new), `scripts/ralph/prd.json`, `scripts/ralph/progress.txt`
- **Learnings for future iterations:**
  - Use two separate `tasks.list` queries: one unfiltered `{}` for stat card counts, one with filters for the displayed list — ensures stats always reflect total counts
  - `date-fns` `format(date, 'PPP')` produces "February 20th, 2026" style dates — already installed as a dependency of react-day-picker (shadcn/ui calendar)
  - shadcn/ui Select with an empty string value (`<SelectItem value="">No project</SelectItem>`) works as a "none" option for optional fields
  - The Popover+Calendar date picker pattern is standard shadcn/ui: Popover wraps a Button trigger showing the formatted date, PopoverContent contains the Calendar
  - Electron app runs at `http://localhost:5173` in dev mode but only within the Electron BrowserWindow — Playwright browser testing requires the Electron-specific test harness, not direct URL navigation
---

## 2026-02-20 - US-012
- What was implemented:
  - Reusable `GanttChart` SVG component at `src/renderer/components/timeline/GanttChart.tsx`
  - Accepts `{ checkpoints: GanttCheckpoint[], startDate: Date, endDate: Date, onDelete? }` props
  - Custom SVG rendering: month labels on X axis, horizontal baseline `<line>`, `<circle>` dots for checkpoints positioned by date
  - Dot fill logic: dark (#171717) for future approved checkpoints, green (#16a34a) for past approved, dashed outline (#737373) for pending AI suggestions (isApproved=0)
  - Vertical red "Today" marker line at current date
  - ResizeObserver for responsive SVG width
  - foreignObject + shadcn/ui Popover on each dot click: shows title, formatted date, project name, and destructive Delete button
  - `AddCheckpointDialog` component at `src/renderer/components/timeline/AddCheckpointDialog.tsx`: title Input (required), Popover+Calendar date (required), Select project dropdown (required in global view, hidden when `defaultProjectId` provided)
  - Global Timeline route (`/timeline`) renders GanttChart with all checkpoints, project name lookup via Map from `projects.listAll`
  - Legend showing dot types, empty state message when no checkpoints
- Files changed: `src/renderer/components/timeline/GanttChart.tsx` (new), `src/renderer/components/timeline/AddCheckpointDialog.tsx` (new), `src/renderer/routes/timeline.tsx`
- **Learnings for future iterations:**
  - `foreignObject` inside SVG is the cleanest way to embed React components (like Popover) on SVG elements — set `overflow-visible` class to prevent clipping
  - Checkpoints don't have a `status` field; use `isApproved=1` + `date < now` heuristic for "completed" vs "todo" dot color
  - Date range for the Gantt is computed dynamically: 1 month before earliest date, 2 months after latest date — ensures comfortable visual padding
  - GanttChart is designed for reuse: the `defaultProjectId` prop on AddCheckpointDialog pre-selects the project and hides the dropdown (for per-project timeline in US-015)
  - `trpc.projects.listAll.useQuery(undefined, { enabled: showProjectSelect })` prevents unnecessary queries when project is already known
---
