SPARC/docs/DATABASE_MODE.md

Database Storage and Caching

This document explains how SPARC uses PostgreSQL for storing LLM messages, enabling response caching and analytics.

Overview

SPARC stores all LLM interactions in PostgreSQL, providing:

• Response Caching: Avoid redundant API calls for previously analyzed patents
• Analytics: Track usage patterns, token consumption, and analysis history
• Persistence: Maintain analysis history across sessions

SPARC supports two cache modes:

1. Cache Mode (default, USE_CACHE=true): Check database for cached responses before making API calls
2. Fresh Mode (USE_CACHE=false): Always make fresh API calls (still stores results in database)

Setup

1. Start the Database

Use docker-compose to start the PostgreSQL database:

docker-compose up -d postgres

This will start a PostgreSQL instance accessible at localhost:5432.

2. Initialize the Database Schema

Run the initialization script to create the necessary tables:

python scripts/init_database.py

This creates the llm_messages table and indexes for efficient querying.

3. Configure Environment Variables

Create a .env file (or copy from .env.example):

cp .env.example .env

Edit .env and set:

# Database connection (required)
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/sparc

# Cache mode: use cached responses when available
USE_CACHE=true

# API key for fresh LLM calls
OPENROUTER_API_KEY=your_openrouter_key_here

Usage

Running with Cache Mode (Default)

Set USE_CACHE=true in your .env file, then run the application normally:

python main.py

The application will:

• Check the database for cached responses matching the request
• If found, return the cached response (no API call)
• If not found, make an API call and store the response for future use

Running with Fresh Mode

Set USE_CACHE=false in your .env file to always get fresh responses:

python main.py

The application will:

• Always send messages to OpenRouter for real LLM responses
• Store all responses in the database
• Useful when you need the latest analysis or want to refresh cached data

Viewing Analytics

View Message Statistics

python scripts/view_analytics.py

Options:

• --days N: Analyze messages from the last N days (default: 30)

Example output:

SPARC Analytics - Last 30 days
======================================================================

Total Messages: 45

Messages by Company:
  nvidia: 25
  intel: 12
  amd: 8

Messages by Analysis Type:
  portfolio: 30
  single_patent: 15

======================================================================

View Stored Messages

python scripts/view_messages.py

Options:

• --company COMPANY: Filter by company name
• --type TYPE: Filter by analysis type (single_patent or portfolio)
• --limit N: Maximum number of messages to display (default: 10)

Examples:

# View last 10 messages
python scripts/view_messages.py

# View all messages for nvidia
python scripts/view_messages.py --company nvidia --limit 100

# View portfolio analyses only
python scripts/view_messages.py --type portfolio

Database Schema

llm_messages Table

Column	Type	Description
id	SERIAL	Primary key
timestamp	TIMESTAMP	When the message was created
company_name	VARCHAR(255)	Company being analyzed
analysis_type	VARCHAR(50)	Type of analysis (single_patent, portfolio)
model	VARCHAR(100)	LLM model identifier
prompt	TEXT	The full prompt sent to the LLM
response	TEXT	The response from the LLM
metadata	JSONB	Additional metadata (patent IDs, content length, etc.)
token_usage	JSONB	Token usage statistics (when available)
created_at	TIMESTAMP	Record creation timestamp

Indexes

• idx_messages_timestamp: Speeds up time-based queries
• idx_messages_company: Speeds up company-specific queries

Docker Compose

The included docker-compose.yml provides:

1. PostgreSQL Database:

   • Image: postgres:16-alpine
   • Port: 5432
   • Credentials: postgres/postgres
   • Database: sparc
   • Persistent storage via volume

2. Application Container (optional):

   • Builds from Dockerfile
   • Connects to PostgreSQL
   • Mounts current directory

Start Services

# Start just the database
docker-compose up -d postgres

# Start everything
docker-compose up -d

# View logs
docker-compose logs -f

# Stop services
docker-compose down

# Stop and remove volumes (WARNING: deletes data)
docker-compose down -v

Toggling Between Modes

You can easily switch between modes by changing the USE_CACHE environment variable:

Quick Toggle (temporary)

# Run with caching enabled
USE_CACHE=true python main.py

# Run with fresh API calls
USE_CACHE=false python main.py

Persistent Toggle

Edit your .env file:

# Use cached responses when available (recommended for most use)
USE_CACHE=true

# Always make fresh API calls
USE_CACHE=false

Use Cases

Cost Optimization with Caching

Cache mode reduces API costs by reusing previous analysis results:

USE_CACHE=true python main.py

If the same company/patent combination was analyzed before, the cached response is returned instantly.

Fresh Analysis

When you need the latest LLM analysis (e.g., after model updates):

USE_CACHE=false python main.py

Collecting Usage Analytics

The database stores all interactions, enabling analytics on:

• Which companies are analyzed most frequently
• Types of analyses performed
• Token usage and costs over time
• Response caching hit rates

Development and Debugging

Database storage is useful for:

• Reviewing actual prompts sent to the LLM
• Analyzing response patterns
• Debugging the full pipeline end-to-end
• Understanding token usage patterns

Troubleshooting

Connection Refused

If you get "connection refused" errors:

1. Ensure PostgreSQL is running: docker-compose ps
2. Check the DATABASE_URL in your .env file
3. Wait for the database to be healthy: docker-compose logs postgres

Schema Not Found

If you get "relation does not exist" errors:

1. Run the initialization script: python scripts/init_database.py
2. Verify tables were created: docker-compose exec postgres psql -U postgres -d sparc -c "\dt"

Permission Denied

If you get permission errors:

1. Check your DATABASE_URL credentials match docker-compose.yml
2. Ensure the database container is running: docker-compose up -d postgres

Advanced Usage

Direct Database Access

You can access the database directly using psql:

docker-compose exec postgres psql -U postgres -d sparc

Example queries:

-- View all messages
SELECT id, company_name, analysis_type, timestamp FROM llm_messages ORDER BY timestamp DESC LIMIT 10;

-- Count messages by company
SELECT company_name, COUNT(*) FROM llm_messages GROUP BY company_name;

-- View recent prompts
SELECT prompt FROM llm_messages ORDER BY timestamp DESC LIMIT 5;

Programmatic Access

You can use the DatabaseClient directly in your code:

from SPARC.database import DatabaseClient
from SPARC import config

db = DatabaseClient(config.database_url)

# Get messages
messages = db.get_messages(company_name="nvidia", limit=10)

# Get analytics
analytics = db.get_analytics(days=7)

# Store a custom message
db.store_message(
    prompt="test prompt",
    response="test response",
    company_name="test",
    analysis_type="custom"
)