> ## Documentation Index
> Fetch the complete documentation index at: https://docs.spn.wtf/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> PostgreSQL schema, helper functions, statuses, and stored qualification data.

## 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

```sql theme={null}
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

```sql theme={null}
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:

```json theme={null}
{
  "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:

```json theme={null}
{
  "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:

```text theme={null}
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.