Heena - Database Schema

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)

Overview

SPN Platform

GST Processor

Heena Jewellers

Mahalaxmi Traders

Heena - Database Schema

Migrations

Database Engine Settings

Master Tables

`clients`

`karigars`

`polishers`

`bank_accounts`

`bullion_accounts`

Transaction Tables

Order Tables

`client_orders`

`takadas`

Order Draft Tables

`order_drafts`

`order_draft_entries`

​Migrations

​Database Engine Settings

​Master Tables

​clients

​karigars

​polishers

​bank_accounts

​bullion_accounts

​Transaction Tables

​Order Tables

​client_orders

​takadas

​Order Draft Tables

​order_drafts

​order_draft_entries

Migrations

Database Engine Settings

Master Tables

`clients`

`karigars`

`polishers`

`bank_accounts`

`bullion_accounts`

Transaction Tables

Order Tables

`client_orders`

`takadas`

Order Draft Tables

`order_drafts`

`order_draft_entries`