PostgreSQL

POSTGRES_USER: openrag
POSTGRES_PASSWORD: openrag123
POSTGRES_DB: openrag_db

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    filename VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    size BIGINT,
    collection_id VARCHAR(100),
    status VARCHAR(50) DEFAULT 'uploaded',
    storage_path TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP
);

CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_collection ON documents(collection_id);

CREATE TABLE document_chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    content TEXT NOT NULL,
    vector_id VARCHAR(255),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_chunks_document ON document_chunks(document_id);
CREATE INDEX idx_chunks_vector ON document_chunks(vector_id);

CREATE TABLE queries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    query_text TEXT NOT NULL,
    response_text TEXT,
    execution_time_ms INTEGER,
    sources JSONB,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_queries_created ON queries(created_at DESC);

CREATE TABLE processing_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    job_type VARCHAR(50) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE INDEX idx_jobs_status ON processing_jobs(status);
CREATE INDEX idx_jobs_document ON processing_jobs(document_id);

CREATE TABLE collections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

postgresql://openrag:openrag123@postgres:5432/openrag_db

# Executed on first container start
docker-entrypoint-initdb.d/init.sql

sudo docker exec openrag-postgres psql -U openrag -d openrag_db -f /docker-entrypoint-initdb.d/init.sql

curl http://localhost:8000/health

sudo docker exec -it openrag-postgres psql -U openrag -d openrag_db

SELECT id, filename, status, created_at 
FROM documents 
ORDER BY created_at DESC 
LIMIT 10;

SELECT COUNT(*) as total_chunks FROM document_chunks;

SELECT 
    status, 
    COUNT(*) as count 
FROM documents 
GROUP BY status;

SELECT 
    query_text, 
    execution_time_ms,
    created_at 
FROM queries 
ORDER BY created_at DESC 
LIMIT 5;

sudo docker exec openrag-postgres pg_dump -U openrag -d openrag_db > backup.sql

sudo docker exec -i openrag-postgres psql -U openrag -d openrag_db < backup.sql

# docker-compose.yml
postgres:
  command:
    - "postgres"
    - "-c"
    - "max_connections=200"
    - "-c"
    - "shared_buffers=256MB"
    - "-c"
    - "effective_cache_size=1GB"

backend/database/migrations/
  001_initial_schema.sql
  002_add_collections.sql

sudo docker exec openrag-postgres psql -U openrag -d openrag_db -f /migrations/001_initial_schema.sql

SELECT pg_size_pretty(pg_database_size('openrag_db'));

SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'openrag_db';

# Check if PostgreSQL is running
sudo docker ps | grep postgres

# View logs
sudo docker logs openrag-postgres --tail=50

# Good
await conn.execute(
    "UPDATE documents SET status = $1::text WHERE id = $2::uuid",
    status, document_id
)

# Bad (may cause asyncpg errors)
await conn.execute(
    "UPDATE documents SET status = $1 WHERE id = $2",
    status, document_id
)