Migrations
Migrations are managed with Alembic from thebackend/ directory.
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) |