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

# Heena - Database Schema

> Tables, relationships, and migration guide

## Migrations

Migrations are managed with **Alembic** from the `backend/` directory.

```bash theme={null}
# Apply all migrations
alembic upgrade head

# Create a new migration
alembic revision --autogenerate -m "description"

# Downgrade one step
alembic downgrade -1
```

The initial migration (`30b88a230f5d_initial_tables`) creates all tables.

### Database Engine Settings

| Setting              | Value                 |
| -------------------- | --------------------- |
| Driver               | psycopg2              |
| Pool size            | 5                     |
| Max overflow         | 10                    |
| Pool timeout         | 30 seconds            |
| Connection recycling | 1800 seconds (30 min) |
| Pre-ping             | Enabled               |

***

## Master Tables

### `clients`

| Column            | Type       | Description                       |
| ----------------- | ---------- | --------------------------------- |
| `id`              | integer PK | Auto-increment                    |
| `name`            | varchar    | Client name                       |
| `cash_balance`    | float      | Running cash balance              |
| `crystal_balance` | float      | Running crystal (jewelry) balance |

### `karigars`

| Column            | Type       | Description             |
| ----------------- | ---------- | ----------------------- |
| `id`              | integer PK | Auto-increment          |
| `name`            | varchar    | Karigar name            |
| `cash_balance`    | float      | Running cash balance    |
| `crystal_balance` | float      | Running crystal balance |

### `polishers`

| Column | Type       | Description                        |
| ------ | ---------- | ---------------------------------- |
| `id`   | integer PK | Auto-increment                     |
| `name` | varchar    | Polisher name                      |
| `type` | integer    | `0`=Regular, `1`=Meena, `2`=Rodium |

### `bank_accounts`

| Column    | Type       | Description     |
| --------- | ---------- | --------------- |
| `id`      | integer PK | Auto-increment  |
| `name`    | varchar    | Bank name       |
| `balance` | float      | Current balance |

### `bullion_accounts`

| Column | Type       | Description         |
| ------ | ---------- | ------------------- |
| `id`   | integer PK | Auto-increment      |
| `name` | varchar    | Bullion dealer name |

***

## Transaction Tables

Each party (client, karigar, polisher, bank, bullion) follows the same three-table pattern:

**`{party}_ledger_rows`**

| Column          | Type       | Description                |
| --------------- | ---------- | -------------------------- |
| `id`            | integer PK | Row ID                     |
| `{party}_id`    | integer FK | Foreign key to party table |
| `date`          | date       | Transaction date           |
| `serial_number` | integer    | Display order              |

**`{party}_ledger_in_section_entries`**

| Column   | Type       | Description                                 |
| -------- | ---------- | ------------------------------------------- |
| `id`     | integer PK | Entry ID                                    |
| `row_id` | integer FK | Foreign key to ledger row                   |
| Various  | —          | Party-specific fields (weight, fine%, etc.) |

**`{party}_ledger_out_section_entries`** — Same structure as IN entries, for outgoing transactions.

***

## Order Tables

### `client_orders`

| Column          | Type       | Description          |
| --------------- | ---------- | -------------------- |
| `id`            | integer PK | Auto-increment       |
| `client_id`     | integer FK | References `clients` |
| `slip_number`   | varchar    | Order slip number    |
| `date`          | date       | Order date           |
| `delivery_date` | date       | Expected delivery    |
| `items`         | JSON       | Order items array    |
| `making_charge` | float      | Making charges       |

### `takadas`

| Column            | Type       | Description                |
| ----------------- | ---------- | -------------------------- |
| `id`              | integer PK | Auto-increment             |
| `client_order_id` | integer FK | References `client_orders` |
| `date`            | date       | Takada date                |
| `items`           | JSON       | Items delivered            |

***

## Order Draft Tables

### `order_drafts`

| Column         | Type       | Description                        |
| -------------- | ---------- | ---------------------------------- |
| `id`           | integer PK | Auto-increment                     |
| `created_at`   | timestamp  | When WhatsApp message was received |
| `phone_number` | varchar    | Sender's phone number              |
| `raw_message`  | text       | Original WhatsApp message          |

### `order_draft_entries`

| Column        | Type       | Description                   |
| ------------- | ---------- | ----------------------------- |
| `id`          | integer PK | Auto-increment                |
| `draft_id`    | integer FK | References `order_drafts`     |
| `item_name`   | varchar    | Parsed item name              |
| `weight`      | float      | Parsed weight                 |
| `quantity`    | integer    | Parsed quantity               |
| `client_name` | varchar    | Parsed client name            |
| `slip_number` | varchar    | Assigned slip number          |
| `client_id`   | integer FK | Resolved client ID (nullable) |