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