Database Schema

Overview

Haggle uses Supabase (managed PostgreSQL) for persistent storage. The schema is minimal by design - only service providers are persisted, while job sessions remain in-memory.

The database uses PostgreSQL-specific features like BIGSERIAL, NUMERIC, and Row Level Security (RLS).

Schema Diagram

Tables

providers

Stores service provider information discovered via AI search and negotiation outcomes.

CREATE TABLE IF NOT EXISTS providers (
    id BIGSERIAL PRIMARY KEY,
    service_provider TEXT NOT NULL,
    phone_number TEXT,
    context_answers TEXT,
    house_address TEXT,
    zip_code TEXT,
    max_price NUMERIC(10, 2),
    job_id TEXT NOT NULL,
    minimum_quote NUMERIC(10, 2),
    problem TEXT,
    negotiated_price NUMERIC(10, 2),
    call_status TEXT,
    call_transcript TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Column Reference

BIGSERIAL

required

Auto-incrementing primary key. Uses BIGSERIAL for high-volume inserts.

service_provider

TEXT

required

Business name of the service provider (e.g., “Reliable Plumbing Services”).

May contain trailing asterisks from search results. Backend strips these before calling.

phone_number

TEXT

Provider’s phone number in various formats:

(408) 555-0101
408-555-0101
408.555.0101

Extracted via regex: r'$?\d{3}$?[-.\s]?\d{3}[-.\s]?\d{4}'

context_answers

TEXT

Formatted paragraph of user’s answers to clarifying questions.Example:

What is the specific issue with your toilet? The toilet is constantly running. 
Is the toilet running constantly or leaking? Yes, water runs non-stop. 
How old is your toilet? About 10 years old.

Used in AI agent prompt for context-aware negotiation.

house_address

TEXT

Full address provided by user (e.g., “123 Main St, San Jose, CA 95126”).Not used for search (zip_code is used instead), but stored for record-keeping.

zip_code

TEXT

5-digit ZIP code used for provider search.

Indexed for fast filtering by location.

max_price

NUMERIC(10, 2)

Maximum budget user is willing to pay. Used by AI agent during negotiation.Example values:

250.00 (user set $250 limit)
NULL (“no_limit” option selected)

job_id

TEXT

required

UUID linking provider to the originating job session.Format: UUID v4 (e.g., 550e8400-e29b-41d4-a716-446655440000)

Jobs are NOT stored in the database - only in memory. This field is for grouping providers.

minimum_quote

NUMERIC(10, 2)

Initial price estimate from provider (if obtained during search).

Currently unused - reserved for future feature where providers submit quotes.

problem

TEXT

AI-formatted problem statement in second person.Examples:

"your toilet needs to be fixed"
"your lawn needs to be mowed"
"your faucet is leaking"

Generated by format_problem_statement() in services/grok_llm.py

negotiated_price

NUMERIC(10, 2)

Final agreed-upon price after AI negotiation.Extraction logic:

negotiated_price = await extract_negotiated_price(transcript)
# Uses Grok LLM to parse conversation transcript

NULL if no agreement reached.

call_status

TEXT

Current status of the negotiation call.Valid values:

pending - Not yet called
in_progress - Currently on call
completed - Call finished with agreement
failed - Call finished without agreement

call_transcript

TEXT

Full conversation transcript from the AI agent call.Format:

[USER]: Yes, hello?
[ASSISTANT]: Hi, is this Reliable Plumbing Services?
[USER]: Yes, this is Mike.
[ASSISTANT]: Hi Mike, I have a toilet that needs to be fixed...
...

Captured real-time from Grok Realtime API events:

conversation.item.input_audio_transcription.completed (user)
response.audio_transcript.done (assistant)

created_at

TIMESTAMPTZ

Timestamp when provider record was created (defaults to NOW()).Uses TIMESTAMP WITH TIME ZONE for timezone-aware storage.

Python Model

The Provider class in db/models.py maps to the database table:

class Provider:
    def __init__(
        self,
        id: Optional[int] = None,
        service_provider: Optional[str] = None,
        phone_number: Optional[str] = None,
        context_answers: Optional[str] = None,
        house_address: Optional[str] = None,
        zip_code: Optional[str] = None,
        max_price: Optional[float] = None,
        job_id: Optional[str] = None,
        minimum_quote: Optional[float] = None,
        problem: Optional[str] = None,
        negotiated_price: Optional[float] = None,
        call_status: Optional[str] = None,
        call_transcript: Optional[str] = None
    ):
        # Initialize all fields...

Database Operations

Common Queries

Get all providers for a job

SELECT * FROM providers 
WHERE job_id = '550e8400-e29b-41d4-a716-446655440000'
ORDER BY created_at ASC;

Python equivalent:

response = supabase.table("providers").select("*").eq("job_id", job_id).execute()
providers = [Provider.from_dict(item) for item in response.data]

Get providers by location

SELECT * FROM providers 
WHERE zip_code = '95126'
ORDER BY created_at DESC;

Uses index: idx_providers_zip_code

Get completed negotiations under budget

SELECT service_provider, phone_number, negotiated_price 
FROM providers 
WHERE job_id = '550e8400-...'
  AND call_status = 'completed'
  AND negotiated_price <= max_price
ORDER BY negotiated_price ASC;

Finds successful negotiations within budget, sorted by price.

Update call status after negotiation

UPDATE providers 
SET 
  call_status = 'completed',
  negotiated_price = 175.00,
  call_transcript = '[USER]: Hello...'
WHERE id = 1
RETURNING *;

Python equivalent:

update_data = {
    "call_status": "completed",
    "negotiated_price": 175.0,
    "call_transcript": transcript_text
}
response = supabase.table("providers").update(update_data).eq("id", provider_id).execute()

Migration Files

The database schema is managed through SQL migration files:

Initial Schema
Call Fields
Updates

File: supabase_migration.sqlCreates the base providers table with core fields:

id, service_provider, phone_number
context_answers, house_address, zip_code
max_price, job_id, minimum_quote, problem
created_at

Run in: Supabase SQL Editor

File: supabase_migration_call_fields.sqlAdds voice call tracking fields:

ALTER TABLE providers
ADD COLUMN negotiated_price NUMERIC(10, 2),
ADD COLUMN call_status TEXT,
ADD COLUMN call_transcript TEXT;

File: supabase_migration_update.sqlContains any schema updates or fixes applied after initial deployment.

Data Lifecycle

Provider Discovery

OpenAI Search finds providers → Parsed → Inserted into providers table with call_status = 'pending'

Call Initiation

Backend updates call_status = 'in_progress' when Twilio call connects

Negotiation

Real-time transcript captured but not yet saved to DB

Call Completion

Extract negotiated price from transcript using LLM
Update call_status = 'completed' or 'failed'
Save negotiated_price and full call_transcript

Frontend Polling

Frontend polls /api/providers/{job_id}/status every 2-3 seconds to update UI with latest call results

Performance Optimization

Indexing Strategy

All frequently queried columns are indexed:

job_id (most common query)
zip_code (location filtering)
service_provider (text search)
house_address (address lookup)

Data Types

BIGSERIAL for high-volume inserts
NUMERIC(10, 2) for precise money values
TEXT for unlimited string storage
TIMESTAMPTZ for timezone handling

Connection Pooling

Supabase automatically handles connection pooling:

Max connections: 100+ (varies by plan)
Idle timeout: 10 minutes
Python client reuses connections

Minimal Writes

Only 2 write operations per provider:

INSERT on discovery
UPDATE after call completion

Jobs are never written to DB (in-memory only)

Security

Row Level Security (RLS)

The current RLS policy allows all operations for anonymous users. Change this in production!

Current Policy (Development)

CREATE POLICY "Allow all operations for anon users" ON providers
    FOR ALL
    USING (true)
    WITH CHECK (true);

Recommended production policies:

CREATE POLICY "Allow read for anon users" ON providers
    FOR SELECT
    USING (true);

Data Privacy

PII Storage

Stored PII:

House addresses
Phone numbers (providers, not users)
Call transcripts (may contain personal info)

Recommendations:

Encrypt call_transcript at application level
Hash or tokenize addresses
Implement data retention policy (delete after 30 days)
Add GDPR compliance endpoints (data export, deletion)

API Key Security

Supabase keys are stored in environment variables:

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")  # Anon key for client-side

Best practices:

Use service_role key only on backend
Rotate keys periodically
Never commit keys to Git
Use secret management (AWS Secrets Manager, etc.)

Backup & Recovery

Supabase provides automatic daily backups:

Automatic Backups

Free tier: 7 days of backups
Pro tier: 30 days of backups
Point-in-time recovery (PITR) on Pro+

Manual Exports

Export via Supabase dashboard or pg_dump:

pg_dump -h db.your-project.supabase.co \
        -U postgres \
        -d postgres \
        -t providers \
        > providers_backup.sql

Restoration

Restore from SQL file:

psql -h db.your-project.supabase.co \
     -U postgres \
     -d postgres \
     < providers_backup.sql

Future Schema Extensions

Users Table

Track user accounts and job history:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE providers ADD COLUMN user_id UUID REFERENCES users(id);

Jobs Table

Persist job sessions to database:

CREATE TABLE jobs (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    original_query TEXT,
    task TEXT,
    zip_code TEXT,
    status TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Make job_id a foreign key
ALTER TABLE providers 
ADD CONSTRAINT fk_job 
FOREIGN KEY (job_id) REFERENCES jobs(id);

Call Analytics

Track negotiation metrics:

CREATE TABLE call_analytics (
    id BIGSERIAL PRIMARY KEY,
    provider_id BIGINT REFERENCES providers(id),
    call_duration_seconds INT,
    negotiation_rounds INT,
    initial_quote NUMERIC(10, 2),
    final_price NUMERIC(10, 2),
    discount_percentage NUMERIC(5, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Get Started

Core Features

Guides

Platform

Database Schema

Overview

Schema Diagram

Tables

providers

Column Reference

Python Model

Database Operations

Common Queries

Migration Files

Data Lifecycle

Performance Optimization

Indexing Strategy

Data Types

Connection Pooling

Minimal Writes

Security

Row Level Security (RLS)

Data Privacy

Backup & Recovery

Future Schema Extensions

Build docs developers (and LLMs) love

Get Started

Core Features

Guides

Platform

​Overview

​Schema Diagram

​Tables

​providers

​Column Reference

​Python Model

​Database Operations

​Common Queries

​Migration Files

​Data Lifecycle

​Performance Optimization

Indexing Strategy

Data Types

Connection Pooling

Minimal Writes

​Security

​Row Level Security (RLS)

​Data Privacy

​Backup & Recovery

​Future Schema Extensions

Build docs developers (and LLMs) love

Overview

Schema Diagram

Tables

providers

Column Reference

Python Model

Database Operations

Common Queries

Migration Files

Data Lifecycle

Performance Optimization

Security

Row Level Security (RLS)

Data Privacy

Backup & Recovery

Future Schema Extensions