# PostgreSQL + Multi-Repository Implementation Plan

> **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

**Goal:** Replace localStorage chat persistence with PostgreSQL, add multi-repository management (path/zip/git), and persist full agent state for session continuity.

**Architecture:** PostgreSQL stores repositories and chat sessions with full JSONB state. API routes handle CRUD. Frontend adds a repo selector dropdown in the sidebar header and a management modal. The agent route reads `source_path` from the selected repository's DB row.

**Tech Stack:** PostgreSQL 16, `pg` (node-postgres), Next.js 15 API routes, React 19, Tailwind CSS

---

### Task 1: Install Dependencies and Configure PostgreSQL

**Files:**
- Modify: `package.json`
- Modify: `next.config.ts`

**Step 1: Install pg**

Run: `npm install pg && npm install -D @types/pg`

**Step 2: Add pg to serverExternalPackages in `next.config.ts`**

```typescript
// next.config.ts
import type { NextConfig } from "next";

const nextConfig: NextConfig = {
  output: "standalone",
  serverExternalPackages: [
    "@anthropic-ai/claude-agent-sdk",
    "tree-sitter",
    "tree-sitter-java",
    "@msgpack/msgpack",
    "pg",
  ],
};

export default nextConfig;
```

**Step 3: Commit**

```bash
git add package.json package-lock.json next.config.ts
git commit -m "feat: add pg dependency for PostgreSQL support"
```

---

### Task 2: Create Database Client with Auto-Migration

**Files:**
- Create: `src/lib/db.ts`

**Step 1: Create `src/lib/db.ts`**

This module exports a connection pool and auto-creates tables on first use. The `DATABASE_URL` env var configures the connection (defaults to local dev).

```typescript
import { Pool } from "pg";

const pool = new Pool({
  connectionString:
    process.env.DATABASE_URL ||
    "postgresql://repopilot:repopilot@localhost:5432/repopilot",
  max: 10,
});

let migrated = false;

const SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS repositories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  source_type VARCHAR(20) NOT NULL CHECK (source_type IN ('path', 'zip', 'git')),
  source_path VARCHAR(1024) NOT NULL,
  source_origin VARCHAR(1024),
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS chat_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  thread_id VARCHAR(255) NOT NULL,
  title VARCHAR(255) DEFAULT 'New Chat',
  messages JSONB NOT NULL DEFAULT '[]'::jsonb,
  agui_history JSONB NOT NULL DEFAULT '[]'::jsonb,
  analysis_state JSONB,
  pending_interrupt JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sessions_repository ON chat_sessions(repository_id);
CREATE INDEX IF NOT EXISTS idx_sessions_updated ON chat_sessions(updated_at DESC);
`;

export async function getDb(): Promise<Pool> {
  if (!migrated) {
    await pool.query(SCHEMA_SQL);
    migrated = true;
  }
  return pool;
}
```

**Step 2: Commit**

```bash
git add src/lib/db.ts
git commit -m "feat: add PostgreSQL client with auto-migration"
```

---

### Task 3: Repository API Routes

**Files:**
- Create: `src/app/api/repositories/route.ts`
- Create: `src/app/api/repositories/[id]/route.ts`

**Step 1: Create `src/app/api/repositories/route.ts`** (GET list, POST create)

Handles three source types:
- `path`: validates directory exists on server filesystem
- `zip`: receives base64-encoded zip data, extracts to `{REPOS_PATH}/{uuid}`
- `git`: clones repo URL to `{REPOS_PATH}/{uuid}` using `git clone`

```typescript
import { NextRequest, NextResponse } from "next/server";
import { getDb } from "@/lib/db";
import { existsSync, mkdirSync, writeFileSync } from "node:fs";
import { join } from "node:path";
import { execSync } from "node:child_process";
import { randomUUID } from "node:crypto";

const REPOS_PATH = process.env.REPOS_PATH || "./repos";

export async function GET() {
  const db = await getDb();
  const { rows } = await db.query(
    "SELECT id, name, source_type, source_path, source_origin, description, created_at, updated_at FROM repositories ORDER BY updated_at DESC"
  );
  return NextResponse.json(rows);
}

export async function POST(request: NextRequest) {
  const body = await request.json();
  const { name, source_type, description } = body as {
    name: string;
    source_type: "path" | "zip" | "git";
    description?: string;
    source_path?: string;   // for 'path' type
    git_url?: string;       // for 'git' type
    zip_base64?: string;    // for 'zip' type
    zip_filename?: string;  // for 'zip' type
  };

  if (!name || !source_type) {
    return NextResponse.json({ error: "name and source_type required" }, { status: 400 });
  }

  let sourcePath: string;
  let sourceOrigin: string;

  if (source_type === "path") {
    sourcePath = body.source_path;
    sourceOrigin = sourcePath;
    if (!sourcePath || !existsSync(sourcePath)) {
      return NextResponse.json({ error: "Directory does not exist" }, { status: 400 });
    }
  } else if (source_type === "zip") {
    if (!body.zip_base64) {
      return NextResponse.json({ error: "zip_base64 required" }, { status: 400 });
    }
    const repoId = randomUUID();
    const repoDir = join(REPOS_PATH, repoId);
    mkdirSync(repoDir, { recursive: true });
    const zipPath = join(repoDir, "__upload.zip");
    writeFileSync(zipPath, Buffer.from(body.zip_base64, "base64"));
    try {
      execSync(`unzip -o "${zipPath}" -d "${repoDir}"`, { timeout: 120_000 });
      execSync(`rm "${zipPath}"`);
    } catch (err) {
      return NextResponse.json(
        { error: `Failed to extract zip: ${err instanceof Error ? err.message : err}` },
        { status: 500 }
      );
    }
    sourcePath = repoDir;
    sourceOrigin = body.zip_filename || "uploaded.zip";
  } else if (source_type === "git") {
    if (!body.git_url) {
      return NextResponse.json({ error: "git_url required" }, { status: 400 });
    }
    const repoId = randomUUID();
    const repoDir = join(REPOS_PATH, repoId);
    mkdirSync(REPOS_PATH, { recursive: true });
    try {
      execSync(`git clone --depth 1 "${body.git_url}" "${repoDir}"`, {
        timeout: 300_000,
        stdio: "pipe",
      });
    } catch (err) {
      return NextResponse.json(
        { error: `Failed to clone: ${err instanceof Error ? err.message : err}` },
        { status: 500 }
      );
    }
    sourcePath = repoDir;
    sourceOrigin = body.git_url;
  } else {
    return NextResponse.json({ error: "Invalid source_type" }, { status: 400 });
  }

  const db = await getDb();
  const { rows } = await db.query(
    `INSERT INTO repositories (name, source_type, source_path, source_origin, description)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [name, source_type, sourcePath, sourceOrigin, description || null]
  );
  return NextResponse.json(rows[0], { status: 201 });
}
```

**Step 2: Create `src/app/api/repositories/[id]/route.ts`** (GET, DELETE)

```typescript
import { NextRequest, NextResponse } from "next/server";
import { getDb } from "@/lib/db";
import { rmSync, existsSync } from "node:fs";

export async function GET(
  _request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params;
  const db = await getDb();
  const { rows } = await db.query("SELECT * FROM repositories WHERE id = $1", [id]);
  if (rows.length === 0) {
    return NextResponse.json({ error: "Not found" }, { status: 404 });
  }
  return NextResponse.json(rows[0]);
}

export async function DELETE(
  _request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params;
  const db = await getDb();
  const { rows } = await db.query("SELECT * FROM repositories WHERE id = $1", [id]);
  if (rows.length === 0) {
    return NextResponse.json({ error: "Not found" }, { status: 404 });
  }
  const repo = rows[0];
  // Delete managed repos (zip/git) from disk
  const REPOS_PATH = process.env.REPOS_PATH || "./repos";
  if (
    (repo.source_type === "zip" || repo.source_type === "git") &&
    repo.source_path.startsWith(REPOS_PATH) &&
    existsSync(repo.source_path)
  ) {
    rmSync(repo.source_path, { recursive: true, force: true });
  }
  await db.query("DELETE FROM repositories WHERE id = $1", [id]);
  return NextResponse.json({ ok: true });
}
```

**Step 3: Commit**

```bash
git add src/app/api/repositories/
git commit -m "feat: add repository CRUD API routes (path/zip/git)"
```

---

### Task 4: Session API Routes

**Files:**
- Create: `src/app/api/sessions/route.ts`
- Create: `src/app/api/sessions/[id]/route.ts`

**Step 1: Create `src/app/api/sessions/route.ts`** (GET list, POST create)

```typescript
import { NextRequest, NextResponse } from "next/server";
import { getDb } from "@/lib/db";
import { randomUUID } from "node:crypto";

export async function GET(request: NextRequest) {
  const repositoryId = request.nextUrl.searchParams.get("repositoryId");
  if (!repositoryId) {
    return NextResponse.json({ error: "repositoryId required" }, { status: 400 });
  }
  const db = await getDb();
  const { rows } = await db.query(
    `SELECT id, repository_id, thread_id, title, created_at, updated_at
     FROM chat_sessions
     WHERE repository_id = $1
     ORDER BY updated_at DESC`,
    [repositoryId]
  );
  return NextResponse.json(rows);
}

export async function POST(request: NextRequest) {
  const { repositoryId } = (await request.json()) as { repositoryId: string };
  if (!repositoryId) {
    return NextResponse.json({ error: "repositoryId required" }, { status: 400 });
  }
  const db = await getDb();
  // Verify repo exists
  const { rows: repos } = await db.query(
    "SELECT id FROM repositories WHERE id = $1",
    [repositoryId]
  );
  if (repos.length === 0) {
    return NextResponse.json({ error: "Repository not found" }, { status: 404 });
  }
  const threadId = randomUUID();
  const { rows } = await db.query(
    `INSERT INTO chat_sessions (repository_id, thread_id, title)
     VALUES ($1, $2, 'New Chat')
     RETURNING *`,
    [repositoryId, threadId]
  );
  return NextResponse.json(rows[0], { status: 201 });
}
```

**Step 2: Create `src/app/api/sessions/[id]/route.ts`** (GET, PUT, DELETE)

```typescript
import { NextRequest, NextResponse } from "next/server";
import { getDb } from "@/lib/db";

export async function GET(
  _request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params;
  const db = await getDb();
  const { rows } = await db.query("SELECT * FROM chat_sessions WHERE id = $1", [id]);
  if (rows.length === 0) {
    return NextResponse.json({ error: "Not found" }, { status: 404 });
  }
  return NextResponse.json(rows[0]);
}

export async function PUT(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params;
  const body = await request.json();
  const { title, messages, agui_history, analysis_state, pending_interrupt } = body as {
    title?: string;
    messages?: unknown;
    agui_history?: unknown;
    analysis_state?: unknown;
    pending_interrupt?: unknown;
  };

  const db = await getDb();
  const sets: string[] = ["updated_at = NOW()"];
  const vals: unknown[] = [];
  let idx = 1;

  if (title !== undefined) { sets.push(`title = $${idx}`); vals.push(title); idx++; }
  if (messages !== undefined) { sets.push(`messages = $${idx}`); vals.push(JSON.stringify(messages)); idx++; }
  if (agui_history !== undefined) { sets.push(`agui_history = $${idx}`); vals.push(JSON.stringify(agui_history)); idx++; }
  if (analysis_state !== undefined) { sets.push(`analysis_state = $${idx}`); vals.push(JSON.stringify(analysis_state)); idx++; }
  if (pending_interrupt !== undefined) { sets.push(`pending_interrupt = $${idx}`); vals.push(JSON.stringify(pending_interrupt)); idx++; }

  vals.push(id);
  const { rows } = await db.query(
    `UPDATE chat_sessions SET ${sets.join(", ")} WHERE id = $${idx} RETURNING *`,
    vals
  );
  if (rows.length === 0) {
    return NextResponse.json({ error: "Not found" }, { status: 404 });
  }
  return NextResponse.json(rows[0]);
}

export async function DELETE(
  _request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params;
  const db = await getDb();
  await db.query("DELETE FROM chat_sessions WHERE id = $1", [id]);
  return NextResponse.json({ ok: true });
}
```

**Step 3: Commit**

```bash
git add src/app/api/sessions/
git commit -m "feat: add session CRUD API routes with full state persistence"
```

---

### Task 5: Update Agent Route to Accept repositoryId

**Files:**
- Modify: `src/app/api/agent/route.ts`
- Modify: `src/server/agent.ts`
- Modify: `src/lib/agui-types.ts`

**Step 1: Add `repositoryId` to `RunAgentInput` in `src/lib/agui-types.ts`**

Add the field to the interface:

```typescript
export interface RunAgentInput {
  threadId: string;
  runId: string;
  messages: AguiMessage[];
  tools: unknown[];
  context: unknown[];
  state: Record<string, unknown>;
  forwardedProps: Record<string, unknown>;
  repositoryId?: string;  // NEW
}
```

**Step 2: Update `src/server/agent.ts` to resolve source_path from DB**

Replace the hardcoded `sourcePath` line:

```typescript
// OLD:
const sourcePath = process.env.SOURCE_PATH || process.env.WORKSPACE_ROOT || "/workspace";

// NEW:
let sourcePath = process.env.SOURCE_PATH || process.env.WORKSPACE_ROOT || "/workspace";
if (inputData.repositoryId) {
  const { getDb } = await import("@/lib/db");
  const db = await getDb();
  const { rows } = await db.query(
    "SELECT source_path FROM repositories WHERE id = $1",
    [inputData.repositoryId]
  );
  if (rows.length > 0) {
    sourcePath = rows[0].source_path;
  }
}
```

**Step 3: Commit**

```bash
git add src/lib/agui-types.ts src/server/agent.ts
git commit -m "feat: agent resolves source_path from repository DB row"
```

---

### Task 6: Replace localStorage Sessions with API Calls

**Files:**
- Rewrite: `src/lib/sessions.ts` (remove localStorage, add API functions)
- Rewrite: `src/hooks/useSessionManager.ts` (use API calls)

**Step 1: Rewrite `src/lib/sessions.ts`**

Remove all localStorage code. Keep types and utility functions (deriveTitle, groupByDate). Add API fetch helpers.

```typescript
import type { AguiMessage } from "@/lib/agui-types";
import type { ChatMessage } from "@/lib/chat-types";
import type { AnalysisStateSnapshot, InterruptPayload } from "@/lib/chat-types";

export interface ChatSession {
  id: string;
  repository_id: string;
  thread_id: string;
  title: string;
  messages: ChatMessage[];
  agui_history: AguiMessage[];
  analysis_state: AnalysisStateSnapshot;
  pending_interrupt: InterruptPayload | null;
  created_at: string;
  updated_at: string;
}

export interface SessionSummary {
  id: string;
  title: string;
  updated_at: string;
}

// --- API helpers ---

export async function fetchSessions(repositoryId: string): Promise<SessionSummary[]> {
  const res = await fetch(`/api/sessions?repositoryId=${repositoryId}`);
  if (!res.ok) return [];
  return res.json();
}

export async function fetchSession(id: string): Promise<ChatSession | null> {
  const res = await fetch(`/api/sessions/${id}`);
  if (!res.ok) return null;
  return res.json();
}

export async function createSessionApi(repositoryId: string): Promise<ChatSession> {
  const res = await fetch("/api/sessions", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ repositoryId }),
  });
  return res.json();
}

export async function updateSessionApi(
  id: string,
  data: {
    title?: string;
    messages?: ChatMessage[];
    agui_history?: AguiMessage[];
    analysis_state?: AnalysisStateSnapshot;
    pending_interrupt?: InterruptPayload | null;
  },
): Promise<void> {
  await fetch(`/api/sessions/${id}`, {
    method: "PUT",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(data),
  });
}

export async function deleteSessionApi(id: string): Promise<void> {
  await fetch(`/api/sessions/${id}`, { method: "DELETE" });
}

// --- Utilities (kept from original) ---

export function deriveTitle(messages: ChatMessage[]): string {
  const firstUser = messages.find((m) => m.role === "user");
  if (!firstUser) return "New Chat";
  const textBlock = firstUser.blocks.find((b) => b.type === "text");
  if (!textBlock || textBlock.type !== "text") return "New Chat";
  const text = textBlock.text.trim();
  if (text.length <= 40) return text;
  return text.slice(0, 37) + "...";
}

interface DateGroup {
  label: string;
  sessions: SessionSummary[];
}

export function groupByDate(sessions: SessionSummary[]): DateGroup[] {
  const now = new Date();
  const todayStart = new Date(now.getFullYear(), now.getMonth(), now.getDate()).getTime();
  const yesterdayStart = todayStart - 86400000;
  const weekStart = todayStart - 7 * 86400000;

  const groups: Record<string, SessionSummary[]> = {
    Today: [],
    Yesterday: [],
    "Previous 7 Days": [],
    Older: [],
  };

  const sorted = [...sessions].sort(
    (a, b) => new Date(b.updated_at).getTime() - new Date(a.updated_at).getTime(),
  );

  for (const s of sorted) {
    const ts = new Date(s.updated_at).getTime();
    if (ts >= todayStart) groups["Today"].push(s);
    else if (ts >= yesterdayStart) groups["Yesterday"].push(s);
    else if (ts >= weekStart) groups["Previous 7 Days"].push(s);
    else groups["Older"].push(s);
  }

  return Object.entries(groups)
    .filter(([, list]) => list.length > 0)
    .map(([label, list]) => ({ label, sessions: list }));
}
```

**Step 2: Rewrite `src/hooks/useSessionManager.ts`**

Replace localStorage calls with API fetch calls. Add `repositoryId` parameter. No truncation of tool results.

```typescript
"use client";

import { useState, useCallback, useRef, useEffect } from "react";
import type { AguiMessage } from "@/lib/agui-types";
import type { ChatMessage, AnalysisStateSnapshot, InterruptPayload } from "@/lib/chat-types";
import {
  fetchSessions,
  fetchSession,
  createSessionApi,
  updateSessionApi,
  deleteSessionApi,
  deriveTitle,
  groupByDate,
  type SessionSummary,
  type ChatSession,
} from "@/lib/sessions";

export interface UseSessionManagerReturn {
  sessions: SessionSummary[];
  activeSessionId: string | null;
  activeSession: ChatSession | null;
  sidebarOpen: boolean;
  repositoryId: string | null;
  setRepositoryId: (id: string) => void;
  createNewSession: () => Promise<string>;
  switchSession: (id: string) => Promise<void>;
  deleteSessionById: (id: string) => Promise<void>;
  toggleSidebar: () => void;
  persistMessages: (
    messages: ChatMessage[],
    aguiHistory: AguiMessage[],
    analysisState?: AnalysisStateSnapshot,
    pendingInterrupt?: InterruptPayload | null,
  ) => void;
  groupedSessions: ReturnType<typeof groupByDate>;
  refreshSessions: () => Promise<void>;
}

export function useSessionManager(): UseSessionManagerReturn {
  const [sessions, setSessions] = useState<SessionSummary[]>([]);
  const [activeSessionId, setActiveSessionId] = useState<string | null>(null);
  const [activeSession, setActiveSession] = useState<ChatSession | null>(null);
  const [sidebarOpen, setSidebarOpen] = useState(true);
  const [repositoryId, setRepositoryIdState] = useState<string | null>(null);
  const debounceRef = useRef<ReturnType<typeof setTimeout> | null>(null);

  const refreshSessionsForRepo = useCallback(async (repoId: string) => {
    const list = await fetchSessions(repoId);
    setSessions(list);
    return list;
  }, []);

  const setRepositoryId = useCallback(
    (id: string) => {
      setRepositoryIdState(id);
      setActiveSessionId(null);
      setActiveSession(null);
      // Sessions will be loaded by the effect below
    },
    [],
  );

  // Load sessions when repositoryId changes
  useEffect(() => {
    if (!repositoryId) return;
    let cancelled = false;
    (async () => {
      const list = await refreshSessionsForRepo(repositoryId);
      if (cancelled) return;
      if (list.length > 0) {
        const session = await fetchSession(list[0].id);
        if (!cancelled && session) {
          setActiveSessionId(session.id);
          setActiveSession(session);
        }
      }
    })();
    return () => { cancelled = true; };
  }, [repositoryId, refreshSessionsForRepo]);

  const createNewSession = useCallback(async (): Promise<string> => {
    if (!repositoryId) throw new Error("No repository selected");
    const session = await createSessionApi(repositoryId);
    setActiveSessionId(session.id);
    setActiveSession(session);
    await refreshSessionsForRepo(repositoryId);
    return session.id;
  }, [repositoryId, refreshSessionsForRepo]);

  const switchSession = useCallback(
    async (id: string) => {
      if (id === activeSessionId) return;
      const session = await fetchSession(id);
      if (!session) return;
      setActiveSessionId(id);
      setActiveSession(session);
    },
    [activeSessionId],
  );

  const deleteSessionById = useCallback(
    async (id: string) => {
      await deleteSessionApi(id);
      if (!repositoryId) return;
      const list = await refreshSessionsForRepo(repositoryId);
      if (id === activeSessionId) {
        if (list.length > 0) {
          const session = await fetchSession(list[0].id);
          setActiveSessionId(session?.id ?? null);
          setActiveSession(session);
        } else {
          setActiveSessionId(null);
          setActiveSession(null);
        }
      }
    },
    [activeSessionId, repositoryId, refreshSessionsForRepo],
  );

  const toggleSidebar = useCallback(() => {
    setSidebarOpen((prev) => !prev);
  }, []);

  const persistMessages = useCallback(
    (
      messages: ChatMessage[],
      aguiHistory: AguiMessage[],
      analysisState?: AnalysisStateSnapshot,
      pendingInterrupt?: InterruptPayload | null,
    ) => {
      if (!activeSessionId) return;
      if (debounceRef.current) clearTimeout(debounceRef.current);
      debounceRef.current = setTimeout(() => {
        const title = deriveTitle(messages);
        updateSessionApi(activeSessionId, {
          title,
          messages,
          agui_history: aguiHistory,
          analysis_state: analysisState ?? null,
          pending_interrupt: pendingInterrupt ?? null,
        }).then(() => {
          if (repositoryId) refreshSessionsForRepo(repositoryId);
        });
      }, 500);
    },
    [activeSessionId, repositoryId, refreshSessionsForRepo],
  );

  const groupedSessions = groupByDate(sessions);

  const refreshSessions = useCallback(async () => {
    if (repositoryId) await refreshSessionsForRepo(repositoryId);
  }, [repositoryId, refreshSessionsForRepo]);

  return {
    sessions,
    activeSessionId,
    activeSession,
    sidebarOpen,
    repositoryId,
    setRepositoryId,
    createNewSession,
    switchSession,
    deleteSessionById,
    toggleSidebar,
    persistMessages,
    groupedSessions,
    refreshSessions,
  };
}
```

**Step 3: Commit**

```bash
git add src/lib/sessions.ts src/hooks/useSessionManager.ts
git commit -m "feat: replace localStorage sessions with PostgreSQL API calls"
```

---

### Task 7: Repository Types and Hooks

**Files:**
- Create: `src/hooks/useRepositories.ts`

**Step 1: Create `src/hooks/useRepositories.ts`**

```typescript
"use client";

import { useState, useCallback, useEffect } from "react";

export interface Repository {
  id: string;
  name: string;
  source_type: "path" | "zip" | "git";
  source_path: string;
  source_origin: string;
  description: string | null;
  created_at: string;
  updated_at: string;
}

export function useRepositories() {
  const [repositories, setRepositories] = useState<Repository[]>([]);
  const [loading, setLoading] = useState(true);

  const refresh = useCallback(async () => {
    try {
      const res = await fetch("/api/repositories");
      if (res.ok) setRepositories(await res.json());
    } finally {
      setLoading(false);
    }
  }, []);

  useEffect(() => { refresh(); }, [refresh]);

  const addRepository = useCallback(
    async (data: {
      name: string;
      source_type: "path" | "zip" | "git";
      description?: string;
      source_path?: string;
      git_url?: string;
      zip_base64?: string;
      zip_filename?: string;
    }) => {
      const res = await fetch("/api/repositories", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(data),
      });
      if (!res.ok) {
        const err = await res.json();
        throw new Error(err.error || "Failed to create repository");
      }
      const repo = await res.json();
      await refresh();
      return repo as Repository;
    },
    [refresh],
  );

  const deleteRepository = useCallback(
    async (id: string) => {
      await fetch(`/api/repositories/${id}`, { method: "DELETE" });
      await refresh();
    },
    [refresh],
  );

  return { repositories, loading, refresh, addRepository, deleteRepository };
}
```

**Step 2: Commit**

```bash
git add src/hooks/useRepositories.ts
git commit -m "feat: add useRepositories hook"
```

---

### Task 8: Repository Management Modal Component

**Files:**
- Create: `src/components/RepositoryModal.tsx`

**Step 1: Create `src/components/RepositoryModal.tsx`**

A modal with tabs for Path / Upload Zip / Clone Git. Shows existing repos list with delete buttons.

```typescript
"use client";

import { useState, useRef } from "react";
import type { Repository } from "@/hooks/useRepositories";

interface RepositoryModalProps {
  repositories: Repository[];
  onAdd: (data: {
    name: string;
    source_type: "path" | "zip" | "git";
    description?: string;
    source_path?: string;
    git_url?: string;
    zip_base64?: string;
    zip_filename?: string;
  }) => Promise<Repository>;
  onDelete: (id: string) => Promise<void>;
  onClose: () => void;
}

type Tab = "path" | "zip" | "git";

export function RepositoryModal({ repositories, onAdd, onDelete, onClose }: RepositoryModalProps) {
  const [tab, setTab] = useState<Tab>("path");
  const [name, setName] = useState("");
  const [description, setDescription] = useState("");
  const [path, setPath] = useState("");
  const [gitUrl, setGitUrl] = useState("");
  const [submitting, setSubmitting] = useState(false);
  const [error, setError] = useState<string | null>(null);
  const [deleteConfirm, setDeleteConfirm] = useState<string | null>(null);
  const fileRef = useRef<HTMLInputElement>(null);

  const resetForm = () => {
    setName(""); setDescription(""); setPath(""); setGitUrl("");
    setError(null);
    if (fileRef.current) fileRef.current.value = "";
  };

  const handleSubmit = async () => {
    if (!name.trim()) { setError("Name is required"); return; }
    setSubmitting(true);
    setError(null);
    try {
      if (tab === "path") {
        if (!path.trim()) { setError("Path is required"); setSubmitting(false); return; }
        await onAdd({ name, source_type: "path", description, source_path: path });
      } else if (tab === "zip") {
        const file = fileRef.current?.files?.[0];
        if (!file) { setError("Select a zip file"); setSubmitting(false); return; }
        const buf = await file.arrayBuffer();
        const base64 = btoa(String.fromCharCode(...new Uint8Array(buf)));
        await onAdd({ name, source_type: "zip", description, zip_base64: base64, zip_filename: file.name });
      } else {
        if (!gitUrl.trim()) { setError("Git URL is required"); setSubmitting(false); return; }
        await onAdd({ name, source_type: "git", description, git_url: gitUrl });
      }
      resetForm();
    } catch (err) {
      setError(err instanceof Error ? err.message : "Failed");
    } finally {
      setSubmitting(false);
    }
  };

  const handleDelete = async (id: string) => {
    await onDelete(id);
    setDeleteConfirm(null);
  };

  const SOURCE_TYPE_LABELS: Record<string, string> = { path: "Path", zip: "Zip", git: "Git" };

  return (
    <div className="fixed inset-0 z-50 flex items-center justify-center bg-black/50" onClick={onClose}>
      <div className="bg-rp-surface border border-rp-border rounded-2xl w-full max-w-lg max-h-[80vh] overflow-y-auto shadow-2xl" onClick={e => e.stopPropagation()}>
        <div className="flex items-center justify-between px-5 py-4 border-b border-rp-border/50">
          <h2 className="text-rp-text font-semibold text-r-sm">Manage Repositories</h2>
          <button onClick={onClose} className="text-rp-muted hover:text-rp-text p-1">
            <svg width="18" height="18" viewBox="0 0 18 18" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round"><path d="M5 5L13 13M13 5L5 13"/></svg>
          </button>
        </div>

        {/* Existing repos */}
        {repositories.length > 0 && (
          <div className="px-5 py-3 border-b border-rp-border/30">
            <p className="text-rp-muted text-r-2xs uppercase tracking-wider mb-2">Repositories</p>
            <div className="space-y-1">
              {repositories.map(r => (
                <div key={r.id} className="flex items-center justify-between px-3 py-2 rounded-lg hover:bg-rp-surface-alt group">
                  <div className="min-w-0">
                    <div className="flex items-center gap-2">
                      <span className="text-rp-text text-r-sm truncate">{r.name}</span>
                      <span className="text-r-2xs px-1.5 py-0.5 bg-rp-border/30 rounded text-rp-muted">{SOURCE_TYPE_LABELS[r.source_type]}</span>
                    </div>
                    <p className="text-rp-muted text-r-2xs truncate">{r.source_origin}</p>
                  </div>
                  {deleteConfirm === r.id ? (
                    <div className="flex gap-1 shrink-0">
                      <button onClick={() => handleDelete(r.id)} className="text-r-2xs px-2 py-1 bg-red-600/20 text-red-400 rounded hover:bg-red-600/30">Delete</button>
                      <button onClick={() => setDeleteConfirm(null)} className="text-r-2xs px-2 py-1 text-rp-muted rounded hover:bg-rp-border/30">Cancel</button>
                    </div>
                  ) : (
                    <button
                      onClick={() => setDeleteConfirm(r.id)}
                      className="opacity-0 group-hover:opacity-100 text-rp-muted hover:text-red-400 p-1 transition-opacity shrink-0"
                    >
                      <svg width="14" height="14" viewBox="0 0 14 14" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round"><path d="M2 4h10M5 4V3a1 1 0 011-1h2a1 1 0 011 1v1M9 6v5M5 6v5M3 4l.5 7.5a1 1 0 001 .5h5a1 1 0 001-.5L11 4"/></svg>
                    </button>
                  )}
                </div>
              ))}
            </div>
          </div>
        )}

        {/* Add new */}
        <div className="px-5 py-4">
          <p className="text-rp-muted text-r-2xs uppercase tracking-wider mb-3">Add Repository</p>

          {/* Tabs */}
          <div className="flex gap-1 mb-4 bg-rp-surface-alt rounded-lg p-1">
            {(["path", "zip", "git"] as Tab[]).map(t => (
              <button
                key={t}
                onClick={() => { setTab(t); setError(null); }}
                className={`flex-1 py-1.5 text-r-xs rounded-md transition-colors ${tab === t ? "bg-rp-surface text-rp-text shadow-sm" : "text-rp-muted hover:text-rp-text"}`}
              >
                {t === "path" ? "Local Path" : t === "zip" ? "Upload Zip" : "Git Clone"}
              </button>
            ))}
          </div>

          <div className="space-y-3">
            <input
              placeholder="Repository name"
              value={name}
              onChange={e => setName(e.target.value)}
              className="w-full px-3 py-2 bg-rp-surface-alt border border-rp-border/50 rounded-lg text-rp-text text-r-sm placeholder:text-rp-muted/50 focus:outline-none focus:border-rp-accent"
            />
            <input
              placeholder="Description (optional)"
              value={description}
              onChange={e => setDescription(e.target.value)}
              className="w-full px-3 py-2 bg-rp-surface-alt border border-rp-border/50 rounded-lg text-rp-text text-r-sm placeholder:text-rp-muted/50 focus:outline-none focus:border-rp-accent"
            />

            {tab === "path" && (
              <input
                placeholder="/path/to/repository"
                value={path}
                onChange={e => setPath(e.target.value)}
                className="w-full px-3 py-2 bg-rp-surface-alt border border-rp-border/50 rounded-lg text-rp-text text-r-sm font-mono placeholder:text-rp-muted/50 focus:outline-none focus:border-rp-accent"
              />
            )}

            {tab === "zip" && (
              <input ref={fileRef} type="file" accept=".zip" className="w-full text-r-sm text-rp-muted file:mr-3 file:py-1.5 file:px-3 file:rounded-lg file:border file:border-rp-border/50 file:bg-rp-surface-alt file:text-rp-text file:text-r-xs" />
            )}

            {tab === "git" && (
              <input
                placeholder="https://github.com/user/repo.git"
                value={gitUrl}
                onChange={e => setGitUrl(e.target.value)}
                className="w-full px-3 py-2 bg-rp-surface-alt border border-rp-border/50 rounded-lg text-rp-text text-r-sm font-mono placeholder:text-rp-muted/50 focus:outline-none focus:border-rp-accent"
              />
            )}

            {error && <p className="text-red-400 text-r-xs">{error}</p>}

            <button
              onClick={handleSubmit}
              disabled={submitting}
              className="w-full py-2.5 bg-rp-accent text-white text-r-sm font-medium rounded-lg hover:bg-rp-accent/90 disabled:opacity-50 transition-colors"
            >
              {submitting ? "Adding..." : "Add Repository"}
            </button>
          </div>
        </div>
      </div>
    </div>
  );
}
```

**Step 2: Commit**

```bash
git add src/components/RepositoryModal.tsx
git commit -m "feat: add repository management modal component"
```

---

### Task 9: Add Repository Selector to Sidebar

**Files:**
- Create: `src/components/sidebar/RepositorySelector.tsx`
- Modify: `src/components/sidebar/ChatSidebar.tsx`

**Step 1: Create `src/components/sidebar/RepositorySelector.tsx`**

```typescript
"use client";

import type { Repository } from "@/hooks/useRepositories";

interface RepositorySelectorProps {
  repositories: Repository[];
  selectedId: string | null;
  onSelect: (id: string) => void;
  onManage: () => void;
}

export function RepositorySelector({ repositories, selectedId, onSelect, onManage }: RepositorySelectorProps) {
  return (
    <div className="flex items-center gap-1.5">
      <select
        value={selectedId || ""}
        onChange={e => onSelect(e.target.value)}
        className="flex-1 min-w-0 px-2.5 py-1.5 bg-rp-surface border border-rp-border/50 rounded-lg text-rp-text text-r-xs truncate focus:outline-none focus:border-rp-accent appearance-none cursor-pointer"
      >
        <option value="" disabled>Select repository...</option>
        {repositories.map(r => (
          <option key={r.id} value={r.id}>{r.name}</option>
        ))}
      </select>
      <button
        onClick={onManage}
        className="shrink-0 p-1.5 text-rp-muted hover:text-rp-text hover:bg-rp-surface rounded-lg transition-colors"
        title="Manage repositories"
      >
        <svg width="16" height="16" viewBox="0 0 16 16" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round">
          <circle cx="8" cy="8" r="2.5"/>
          <path d="M8 1.5v2M8 12.5v2M1.5 8h2M12.5 8h2M3.1 3.1l1.4 1.4M11.5 11.5l1.4 1.4M3.1 12.9l1.4-1.4M11.5 4.5l1.4-1.4"/>
        </svg>
      </button>
    </div>
  );
}
```

**Step 2: Update `src/components/sidebar/ChatSidebar.tsx`**

Add `RepositorySelector` above the "New chat" button. Accept new props for repositories.

```typescript
"use client";

import { ConversationGroup } from "./ConversationGroup";
import { RepositorySelector } from "./RepositorySelector";
import type { groupByDate } from "@/lib/sessions";
import type { Repository } from "@/hooks/useRepositories";

interface ChatSidebarProps {
  groupedSessions: ReturnType<typeof groupByDate>;
  activeSessionId: string | null;
  onSelectSession: (id: string) => void;
  onDeleteSession: (id: string) => void;
  onNewSession: () => void;
  onClose?: () => void;
  // Repository props
  repositories: Repository[];
  selectedRepositoryId: string | null;
  onSelectRepository: (id: string) => void;
  onManageRepositories: () => void;
}

export function ChatSidebar({
  groupedSessions,
  activeSessionId,
  onSelectSession,
  onDeleteSession,
  onNewSession,
  onClose,
  repositories,
  selectedRepositoryId,
  onSelectRepository,
  onManageRepositories,
}: ChatSidebarProps) {
  return (
    <>
      <div className="hidden max-md:block sidebar-overlay" onClick={onClose} />
      <aside className="w-64 h-full bg-rp-surface-alt border-r border-rp-border/50 flex flex-col sidebar-enter max-md:sidebar-panel">
        {/* Repository selector */}
        <div className="p-3 pb-0 shrink-0">
          <RepositorySelector
            repositories={repositories}
            selectedId={selectedRepositoryId}
            onSelect={onSelectRepository}
            onManage={onManageRepositories}
          />
        </div>

        <div className="p-3 shrink-0">
          <button
            onClick={onNewSession}
            disabled={!selectedRepositoryId}
            className="w-full flex items-center gap-2 px-3 py-2.5 text-r-sm text-rp-muted border border-rp-border/60 rounded-xl hover:bg-rp-surface hover:text-rp-text transition-colors disabled:opacity-40 disabled:cursor-not-allowed"
          >
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round"><path d="M8 3V13M3 8H13" /></svg>
            <span>New chat</span>
          </button>
        </div>

        <div className="flex-1 overflow-y-auto px-2 pb-3">
          {!selectedRepositoryId ? (
            <div className="px-3 py-4 text-r-xs text-rp-muted text-center">Select a repository to start</div>
          ) : groupedSessions.length === 0 ? (
            <div className="px-3 py-4 text-r-xs text-rp-muted text-center">No conversations yet</div>
          ) : (
            groupedSessions.map((group) => (
              <ConversationGroup
                key={group.label}
                label={group.label}
                sessions={group.sessions}
                activeSessionId={activeSessionId}
                onSelect={onSelectSession}
                onDelete={onDeleteSession}
              />
            ))
          )}
        </div>
      </aside>
    </>
  );
}
```

**Step 3: Commit**

```bash
git add src/components/sidebar/RepositorySelector.tsx src/components/sidebar/ChatSidebar.tsx
git commit -m "feat: add repository selector to chat sidebar"
```

---

### Task 10: Update ChatApp and useAgent for Repository Integration

**Files:**
- Modify: `src/components/ChatApp.tsx`
- Modify: `src/hooks/useAgent.ts`

**Step 1: Update `src/hooks/useAgent.ts`**

Add `repositoryId` to `UseAgentOptions` and include it in `RunAgentInput`.

In `useAgent`, add:
```typescript
export interface UseAgentOptions {
  onMessagesChange?: (
    messages: ChatMessage[],
    history: AguiMessage[],
  ) => void;
  repositoryId?: string | null;  // NEW
}
```

In `sendMessage`, include repositoryId in the input:
```typescript
const input: RunAgentInput = {
  threadId: threadIdRef.current,
  runId: crypto.randomUUID(),
  messages: historyRef.current,
  tools: [],
  context: [],
  state: {},
  forwardedProps: {},
  repositoryId: options.repositoryId || undefined,  // NEW
};
```

**Step 2: Update `src/components/ChatApp.tsx`**

Integrate `useRepositories`, pass repo state to sidebar, show `RepositoryModal`.

```typescript
"use client";

import { useState, useEffect, useRef } from "react";
import { useAgent } from "@/hooks/useAgent";
import { useSessionManager } from "@/hooks/useSessionManager";
import { useRepositories } from "@/hooks/useRepositories";
import { ChatView } from "@/components/ChatView";
import { ChatSidebar } from "@/components/sidebar/ChatSidebar";
import { RepositoryModal } from "@/components/RepositoryModal";

export default function ChatApp() {
  const { repositories, addRepository, deleteRepository } = useRepositories();
  const sessionManager = useSessionManager();
  const [showRepoModal, setShowRepoModal] = useState(false);

  const {
    messages,
    isStreaming,
    error,
    pendingInterrupt,
    analysisState,
    sendMessage,
    resolveInterrupt,
    resetSession,
  } = useAgent({
    onMessagesChange: sessionManager.persistMessages,
    repositoryId: sessionManager.repositoryId,
  });

  const initializedRef = useRef(false);

  // Auto-select first repo if none selected
  useEffect(() => {
    if (!sessionManager.repositoryId && repositories.length > 0) {
      sessionManager.setRepositoryId(repositories[0].id);
    }
  }, [repositories, sessionManager.repositoryId, sessionManager]);

  useEffect(() => {
    const session = sessionManager.activeSession;
    if (!session) return;
    resetSession({
      messages: session.messages,
      history: session.agui_history,
      threadId: session.thread_id,
    });
    initializedRef.current = true;
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [sessionManager.activeSessionId]);

  return (
    <div className="flex h-full bg-rp-bg">
      {sessionManager.sidebarOpen && (
        <ChatSidebar
          groupedSessions={sessionManager.groupedSessions}
          activeSessionId={sessionManager.activeSessionId}
          onSelectSession={sessionManager.switchSession}
          onDeleteSession={sessionManager.deleteSessionById}
          onNewSession={sessionManager.createNewSession}
          onClose={sessionManager.toggleSidebar}
          repositories={repositories}
          selectedRepositoryId={sessionManager.repositoryId}
          onSelectRepository={sessionManager.setRepositoryId}
          onManageRepositories={() => setShowRepoModal(true)}
        />
      )}

      <div className="flex flex-col flex-1 min-w-0">
        <div className="h-10 flex items-center px-3 shrink-0">
          <button
            onClick={sessionManager.toggleSidebar}
            className="text-rp-muted hover:text-rp-text transition-colors p-1.5 rounded-lg hover:bg-rp-surface"
            title={sessionManager.sidebarOpen ? "Close sidebar" : "Open sidebar"}
          >
            <SidebarIcon />
          </button>
        </div>

        <div className="flex-1 min-h-0">
          {sessionManager.repositoryId ? (
            <ChatView
              messages={messages}
              isStreaming={isStreaming}
              error={error}
              pendingInterrupt={pendingInterrupt}
              analysisState={analysisState}
              onSend={sendMessage}
              onResolveInterrupt={resolveInterrupt}
            />
          ) : (
            <div className="flex items-center justify-center h-full">
              <div className="text-center">
                <p className="text-rp-muted text-r-sm mb-3">No repository selected</p>
                <button
                  onClick={() => setShowRepoModal(true)}
                  className="px-4 py-2 bg-rp-accent text-white text-r-sm rounded-lg hover:bg-rp-accent/90 transition-colors"
                >
                  Add Repository
                </button>
              </div>
            </div>
          )}
        </div>
      </div>

      {showRepoModal && (
        <RepositoryModal
          repositories={repositories}
          onAdd={addRepository}
          onDelete={deleteRepository}
          onClose={() => setShowRepoModal(false)}
        />
      )}
    </div>
  );
}

function SidebarIcon() {
  return (
    <svg width="18" height="18" viewBox="0 0 18 18" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round">
      <rect x="2" y="3" width="14" height="12" rx="2" />
      <path d="M7 3V15" />
    </svg>
  );
}
```

**Step 3: Commit**

```bash
git add src/hooks/useAgent.ts src/components/ChatApp.tsx
git commit -m "feat: integrate repository selection into ChatApp and agent"
```

---

### Task 11: Update Docker Compose and Dockerfile

**Files:**
- Modify: `docker-compose.yml`
- Modify: `Dockerfile`

**Step 1: Update `docker-compose.yml`**

```yaml
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: repopilot
      POSTGRES_USER: repopilot
      POSTGRES_PASSWORD: repopilot
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  repopilot:
    build: .
    ports:
      - "3000:3000"
    depends_on:
      - postgres
    environment:
      - LITELLM_PROXY_URL=http://host.docker.internal:4141
      - LLM_MODEL=gpt-5-mini
      - ANTHROPIC_API_KEY=dummy
      - DATABASE_URL=postgresql://repopilot:repopilot@postgres:5432/repopilot
      - REPOS_PATH=/repos
    volumes:
      - repos:/repos
    extra_hosts:
      - "host.docker.internal:host-gateway"

volumes:
  pgdata:
  repos:
```

**Step 2: Update `Dockerfile` entrypoint**

Add `DATABASE_URL`, `REPOS_PATH` to the entrypoint env vars. Add `git` and `unzip` to the runner stage.

In runner stage, after the `RUN npm install -g` line, add:

```dockerfile
RUN apt-get update && \
    apt-get install -y --no-install-recommends git unzip && \
    rm -rf /var/lib/apt/lists/*
```

In the entrypoint script, add:

```sh
export DATABASE_URL="${DATABASE_URL:-postgresql://repopilot:repopilot@postgres:5432/repopilot}"
export REPOS_PATH="${REPOS_PATH:-/repos}"
mkdir -p "$REPOS_PATH"
```

And add to the echo block:

```sh
echo "  Database:     $DATABASE_URL"
echo "  Repos path:   $REPOS_PATH"
```

**Step 3: Commit**

```bash
git add docker-compose.yml Dockerfile
git commit -m "feat: add PostgreSQL service and git/unzip to Docker setup"
```

---

### Task 12: Update ConversationItem for New Session Types

**Files:**
- Modify: `src/components/sidebar/ConversationItem.tsx`

**Step 1: Check and update ConversationItem**

The `SessionSummary` type changed from `{ updatedAt: number }` to `{ updated_at: string }`. Update the ConversationItem component if it references `updatedAt` anywhere.

Read the file, find any references to `updatedAt` and change them to `updated_at`. The title field stays the same.

**Step 2: Commit**

```bash
git add src/components/sidebar/ConversationItem.tsx
git commit -m "fix: update ConversationItem for new session types"
```

---

### Task 13: Build Verification and Final Commit

**Step 1: Run TypeScript check**

Run: `npx tsc --noEmit`
Expected: No errors

**Step 2: Run Next.js build**

Run: `npm run build`
Expected: Build succeeds with all routes

**Step 3: Fix any compilation errors found in steps 1-2**

**Step 4: Final commit if any fixes were needed**

```bash
git add -A
git commit -m "fix: resolve build issues from PostgreSQL integration"
```

---

## Task Dependency Graph

```
Task 1 (deps) → Task 2 (db client) → Task 3 (repo API) → Task 4 (session API)
                                                                     ↓
Task 7 (useRepositories) → Task 8 (modal) → Task 9 (sidebar) → Task 10 (ChatApp integration)
                                                                     ↓
Task 5 (agent route) ─────────────────────────────────────→ Task 13 (build verify)
Task 6 (sessions rewrite) ────────────────────────────────→ Task 13
Task 11 (Docker) ─────────────────────────────────────────→ Task 13
Task 12 (ConversationItem) ───────────────────────────────→ Task 13
```

Tasks 3, 4, 5, 6, 7, 8, 11, 12 can partially run in parallel after Task 2 completes.