# PostgreSQL + Multi-Repository Management Design

## Overview

Replace localStorage-based chat persistence with PostgreSQL. Add multi-repository management so users can add repositories (via path, zip upload, or git clone) and maintain independent chat sessions per repository. Full agent state is persisted and restored so sessions resume with complete context.

## Database Schema

```sql
CREATE TABLE 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 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 '[]',
  agui_history JSONB NOT NULL DEFAULT '[]',
  analysis_state JSONB,
  pending_interrupt JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

Tables are auto-created on first DB connection (no manual migration step).

## Agent State Persistence

All agent state is stored without truncation:

- **messages**: Full ChatMessage[] including all blocks (text, tool calls with nested subAgentToolCalls, errors, interrupts)
- **agui_history**: Complete AguiMessage[] sent to agent SDK for context restoration on resume
- **thread_id**: Agent thread identity for session continuity
- **analysis_state**: AnalysisStateSnapshot
- **pending_interrupt**: InterruptPayload if agent was awaiting approval

The current localStorage 2000-char tool result truncation is removed. PostgreSQL JSONB handles large payloads.

## Repository Management

### Source Types

- **Path**: User provides an existing filesystem directory. Validated on creation.
- **Zip upload**: Multipart upload to `/api/repositories`, extracted to `{REPOS_PATH}/{uuid}/`.
- **Git clone**: User provides a Git URL, cloned to `{REPOS_PATH}/{uuid}/`.

### Storage

Managed repos directory: `REPOS_PATH` env var (default: `/repos` in Docker, `./repos` locally). Each uploaded/cloned repo gets a UUID subdirectory. Path-based repos reference their original location.

## API Routes

```
GET    /api/repositories                  List all repositories
POST   /api/repositories                  Create repository (path, zip, or git)
GET    /api/repositories/[id]             Get repository details
DELETE /api/repositories/[id]             Delete repository (and managed files)

GET    /api/sessions?repositoryId=X       List sessions for a repository
POST   /api/sessions                      Create session (requires repositoryId)
GET    /api/sessions/[id]                 Get session with full state
PUT    /api/sessions/[id]                 Update session (messages, state, title)
DELETE /api/sessions/[id]                 Delete session

POST   /api/agent                         Existing SSE endpoint, now includes repositoryId
```

## UI Changes

### Sidebar Header
- Repository dropdown selector at the top of the chat sidebar
- Gear icon next to dropdown opens repository management modal
- Selecting a repo filters sessions to that repository

### Repository Management Modal
- List of existing repositories with name, type badge, source info
- "Add Repository" with three tabs: Path / Upload Zip / Clone Git
- Delete button per repository (with confirmation)

### Chat Sidebar (below repo selector)
- Unchanged structure (grouped sessions, new chat button)
- Sessions filtered to the currently selected repository

### Agent Integration
- Agent route reads `source_path` from the selected repository's DB row
- Agent `cwd` set to the repository's `source_path` instead of global `SOURCE_PATH`

## Docker Compose

```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:
      - DATABASE_URL=postgresql://repopilot:repopilot@postgres:5432/repopilot
      - REPOS_PATH=/repos
    volumes:
      - repos:/repos

volumes:
  pgdata:
  repos:
```

## Data Flow Changes

```
BEFORE:
  ChatView → useAgent → useSessionManager → localStorage

AFTER:
  ChatView → useAgent → useSessionManager → /api/sessions → PostgreSQL
  Sidebar repo selector → /api/repositories → PostgreSQL
  Agent route → reads repo source_path from PostgreSQL
```

## Dependencies

- `pg` (node-postgres) for PostgreSQL client
- `@types/pg` for TypeScript types
- `busboy` or Next.js built-in formData for zip upload parsing
- `git` CLI (available in Docker image) for git clone