Grepai Storage Postgres

# GrepAI Storage with PostgreSQL

This skill covers using PostgreSQL with the pgvector extension as the storage backend for GrepAI.

## When to Use This Skill

- Team environments with shared index
- Large codebases (10K+ files)
- Need concurrent access
- Integration with existing PostgreSQL infrastructure

## Prerequisites

1. PostgreSQL 14+ with pgvector extension
2. Database user with create table permissions
3. Network access to PostgreSQL server

## Advantages

| Benefit | Description |
|---------|-------------|
| 👥 **Team sharing** | Multiple users can access same index |
| 📏 **Scalable** | Handles large codebases |
| 🔄 **Concurrent** | Multiple simultaneous searches |
| 💾 **Persistent** | Data survives machine restarts |
| 🔧 **Familiar** | Standard database tooling |

## Setting Up PostgreSQL with pgvector

### Option 1: Docker (Recommended for Development)

```bash
# Run PostgreSQL with pgvector
docker run -d \
  --name grepai-postgres \
  -e POSTGRES_USER=grepai \
  -e POSTGRES_PASSWORD=grepai \
  -e POSTGRES_DB=grepai \
  -p 5432:5432 \
  pgvector/pgvector:pg16
```

### Option 2: Install on Existing PostgreSQL

```bash
# Install pgvector extension (Ubuntu/Debian)
sudo apt install postgresql-16-pgvector

# Or compile from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install
```

Then enable the extension:

```sql
-- Connect to your database
CREATE EXTENSION IF NOT EXISTS vector;
```

### Option 3: Managed Services

- **Supabase:** pgvector included by default
- **Neon:** pgvector available
- **AWS RDS:** Install pgvector extension
- **Azure Database:** pgvector available

## Configuration

### Basic Configuration

```yaml
# .grepai/config.yaml
store:
  backend: postgres
  postgres:
    dsn: postgres://user:password@localhost:5432/grepai
```

### With Environment Variable

```yaml
store:
  backend: postgres
  postgres:
    dsn: ${DATABASE_URL}
```

Set the environment variable:
```bash
export DATABASE_URL="postgres://user:password@localhost:5432/grepai"
```

### Full DSN Options

```yaml
store:
  backend: postgres
  postgres:
    dsn: postgres://user:password@host:5432/database?sslmode=require
```

DSN components:
- `user`: Database username
- `password`: Database password
- `host`: Server hostname or IP
- `5432`: Port (default: 5432)
- `database`: Database name
- `sslmode`: SSL mode (disable, require, verify-full)

## SSL Modes

| Mode | Description | Use Case |
|------|-------------|----------|
| `disable` | No SSL | Local development |
| `require` | SSL required | Production |
| `verify-full` | SSL + verify certificate | High security |

```yaml
# Production with SSL
store:
  backend: postgres
  postgres:
    dsn: postgres://user:pass@prod.db.com:5432/grepai?sslmode=require
```

## Database Schema

GrepAI automatically creates these tables:

```sql
-- Vector embeddings table
CREATE TABLE IF NOT EXISTS embeddings (
    id SERIAL PRIMARY KEY,
    file_path TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    content TEXT NOT NULL,
    start_line INTEGER,
    end_line INTEGER,
    embedding vector(768),  -- Dimension matches your model
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(file_path, chunk_index)
);

-- Index for vector similarity search
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);
```

## Verifying Setup

### Check pgvector Extension

```sql
-- Connect to database
psql -U grepai -d grepai

-- Check extension is installed
SELECT * FROM pg_extension WHERE extname = 'vector';

-- Check GrepAI tables exist (after first grepai watch)
\dt
```

### Test Connection from GrepAI

```bash
# Check status
grepai status

# Should show PostgreSQL backend info
```

## Performance Tuning

### PostgreSQL Configuration

For better vector search performance:

```sql
-- Increase work memory for vector