Database Schema (PostgreSQL)
All timestamps TIMESTAMPTZ default now(). IDs are UUID (gen_random_uuid()).
users
| column |
type |
notes |
| id |
UUID PK |
|
| phone |
TEXT UNIQUE NOT NULL |
E.164, login identity |
| name |
TEXT |
|
| tz |
TEXT default 'Asia/Kolkata' |
for correct firing times |
| created_at / updated_at |
TIMESTAMPTZ |
|
reminders (one engine, 3 types)
| column |
type |
notes |
| id |
UUID PK |
|
| user_id |
UUID FK users |
indexed |
| type |
TEXT CHECK in ('habit','medicine','expiry') |
|
| title |
TEXT NOT NULL |
"Paani", "BP tablet", "Doodh expiry" |
| icon |
TEXT |
optional emoji/key |
| times |
TEXT[] |
HH:MM list (habit/medicine), e.g. {'08:00','21:00'} |
| days |
INT[] |
weekdays 0-6 for habit (null = daily) |
| expiry_date |
DATE |
for type=expiry |
| notify_days_before |
INT default 3 |
expiry lead time |
| whatsapp |
BOOLEAN default false |
Pro: also send to WhatsApp |
| family_alert |
BOOLEAN default false |
Pro: miss escalates to family |
| active |
BOOLEAN default true |
|
| created_at / updated_at |
TIMESTAMPTZ |
|
| deleted_at |
TIMESTAMPTZ NULL |
soft delete |
Index: (user_id, active), (type, expiry_date).
reminder_logs (done/missed + streaks)
| column |
type |
notes |
| id |
UUID PK |
|
| reminder_id |
UUID FK |
indexed |
| user_id |
UUID FK |
|
| due_at |
TIMESTAMPTZ |
the scheduled instant |
| status |
TEXT CHECK in ('done','missed','skipped') |
|
| acted_at |
TIMESTAMPTZ |
when user tapped |
| Index: (reminder_id, due_at) UNIQUE — one log per occurrence (idempotent). |
|
|
grocery_lists / grocery_items
lists: id, user_id, name, created_at. (Free = 1 list.)
items: id, list_id, user_id, text, qty, checked BOOLEAN default false, created_at, deleted_at.
Index: (list_id) where deleted_at is null.
family_links (Pro)
| column |
type |
notes |
| id |
UUID PK |
|
| user_id |
UUID FK |
owner |
| name |
TEXT |
"Beta", "Papa" |
| phone |
TEXT NOT NULL |
family member (gets WA alerts) |
| created_at |
TIMESTAMPTZ |
|
subscriptions
| column |
type |
notes |
| user_id |
UUID PK FK users |
one row per user |
| plan |
TEXT CHECK in ('free','pro') default 'free' |
|
| status |
TEXT |
'active','trialing','past_due','canceled' |
| trial_end |
TIMESTAMPTZ |
|
| current_period_end |
TIMESTAMPTZ |
|
| rzp_customer_id / rzp_subscription_id |
TEXT |
Razorpay refs |
| updated_at |
TIMESTAMPTZ |
|
otp_codes, wa_templates, sync_ops
Same as Udhaar Khata (hashed OTP w/ expiry+lockout; central WhatsApp template store;
idempotent offline sync). See WHATSAPP + sync notes.
admins
| column |
type |
notes |
| id |
UUID PK |
|
| email |
TEXT UNIQUE |
|
| pass_hash |
TEXT |
scrypt |
| created_at |
TIMESTAMPTZ |
|
| (Admin panel login; separate from user auth.) |
|
|