Heena - Database Schema

Tables, relationships, and migration guide

Migrations

Migrations are managed with Alembic from the backend/ directory.

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