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.

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