Database Schema (PostgreSQL)
All money in paise (BIGINT). All timestamps TIMESTAMPTZ default now().
shops
| column |
type |
notes |
| id |
UUID PK |
gen_random_uuid() |
| phone |
TEXT UNIQUE NOT NULL |
login identity (E.164) |
| shop_name |
TEXT |
editable |
| owner_name |
TEXT |
|
| created_at |
TIMESTAMPTZ |
|
| updated_at |
TIMESTAMPTZ |
|
customers
| column |
type |
notes |
| id |
UUID PK |
|
| shop_id |
UUID FK -> shops.id |
indexed |
| name |
TEXT NOT NULL |
|
| phone |
TEXT |
nullable; needed for reminders |
| note |
TEXT |
optional |
| created_at / updated_at |
TIMESTAMPTZ |
|
| deleted_at |
TIMESTAMPTZ NULL |
soft delete |
Index: (shop_id), (shop_id, name).
entries (the ledger)
| column |
type |
notes |
| id |
UUID PK |
|
| shop_id |
UUID FK |
indexed |
| customer_id |
UUID FK -> customers.id |
indexed |
| type |
TEXT CHECK (type IN ('credit','payment')) |
credit = diya, payment = liya |
| amount_paise |
BIGINT NOT NULL CHECK (amount_paise > 0) |
always positive |
| note |
TEXT |
e.g. "doodh 2L" |
| entry_date |
DATE default current_date |
|
| created_at / updated_at |
TIMESTAMPTZ |
|
| deleted_at |
TIMESTAMPTZ NULL |
soft delete |
Index: (shop_id, customer_id, created_at).
otp_codes
| column |
type |
notes |
| phone |
TEXT |
|
| code_hash |
TEXT |
hashed OTP (never store plaintext) |
| expires_at |
TIMESTAMPTZ |
5 min |
| attempts |
INT default 0 |
lock after 5 |
| created_at |
TIMESTAMPTZ |
|
reminders (audit log)
| column |
type |
notes |
| id |
UUID PK |
|
| shop_id |
UUID FK |
|
| customer_id |
UUID FK |
|
| balance_paise |
BIGINT |
balance at send time |
| channel |
TEXT |
'whatsapp' |
| status |
TEXT |
'sent' / 'failed' |
| created_at |
TIMESTAMPTZ |
|
Derived: customer balance
balance = SUM(credit) - SUM(payment) over non-deleted entries for that customer.
Compute in SQL; do not store a denormalized balance in v1 (ponytail: add a cached column
only if query gets slow).