Database

Overview

The application uses PostgreSQL with two main tables:

leads: incoming lead queue and retry state
calls: one record per outbound call attempt

db.py creates and migrates the schema at startup. It is safe to call init_db() repeatedly.

`leads` Table

CREATE TABLE IF NOT EXISTS leads (
    id            SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    phone         TEXT NOT NULL,
    source        TEXT NOT NULL,
    email         TEXT,
    product_name  TEXT,
    city          TEXT,
    state         TEXT,
    query_message TEXT,
    status        TEXT NOT NULL DEFAULT 'new',
    retry_after   TIMESTAMPTZ,
    retry_count   INT NOT NULL DEFAULT 0,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Lead statuses:

Status	Meaning
`new`	Ready to be called, unless `retry_after` is in the future
`dispatched`	A call attempt was successfully started
`failed`	Invalid lead or retry limit reached

`calls` Table

CREATE TABLE IF NOT EXISTS calls (
    id                SERIAL PRIMARY KEY,
    lead_id           INT REFERENCES leads(id) ON DELETE SET NULL,
    name              TEXT,
    phone             TEXT NOT NULL,
    source            TEXT,
    qualification     JSONB,
    status            TEXT NOT NULL DEFAULT 'calling',
    failure_reason    TEXT,
    call_room         TEXT,
    call_started_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    call_ended_at     TIMESTAMPTZ,
    duration_seconds  INT,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Call statuses:

Status	Meaning
`calling`	SIP call has been initiated or is active
`called`	Agent saved qualification JSON
`pushed`	Talking Shops accepted the CRM task
`failed`	The call attempt did not produce a successful CRM-ready result

Qualification JSON

The exact JSON differs by agent mode. Sarvam mode stores broader machinery qualification:

{
  "machine_interest": "paper plate machine",
  "location": "Kolkata",
  "budget": "Rs. 10 lakhs",
  "first_product": "paper plates",
  "units_per_day": "5000",
  "local_demand": "yes",
  "new_or_expand": "new business",
  "partnership": "with partner",
  "operators_needed": "2",
  "own_brand": "yes",
  "seriousness": "ready to start soon",
  "factory_visit": "interested",
  "video_demo": "yes"
}

OpenAI Realtime mode stores a tighter Ignitech schema:

{
  "product_interest": "carbon cleaning",
  "location": "Kolkata",
  "budget": "10-25 lakhs"
}

Only non-null values are stored.

Important Helpers

Function	Purpose
`insert_lead()`	Adds an incoming lead
`get_new_leads()`	Gets retry-ready `new` leads
`set_lead_dispatched()`	Marks lead as dispatched after SIP trigger succeeds
`schedule_lead_retry()`	Sets retry time and retry count
`create_call()`	Creates a call attempt row
`save_qualification()`	Saves JSON and marks call `called`
`set_call_failed()`	Marks call failed with reason and duration
`fail_stale_call_records()`	Recovers stuck `calling` rows
`set_call_pushed()`	Marks CRM-pushed calls

Duration Tracking

For called, pushed, and failed terminal records, duration is calculated from:

call_ended_at - call_started_at

The minutes dashboard uses LEGACY_SUCCESS_CALL_ESTIMATE_MINUTES for older successful calls that do not have complete duration fields.

Getting Started

Architecture

Integrations

Operations

Overview

`leads` Table

`calls` Table

Qualification JSON

Important Helpers

Duration Tracking

​Overview

​leads Table

​calls Table

​Qualification JSON

​Important Helpers

​Duration Tracking

Overview

`leads` Table

`calls` Table

Qualification JSON

Important Helpers

Duration Tracking