PostgreSQL Database Schema

30+ tables with strict multi-tenancy (tenant_id on all tenant tables), indexes, and optional RLS policies.

platform_admins

Super admin users for the SaaS platform

7 columns
Column Type Notes
id UUID PK gen_random_uuid()
email TEXT UNIQUE NOT NULL Login identifier
password_hash TEXT NOT NULL bcrypt hashed
totp_secret TEXT TOTP MFA encrypted
role TEXT NOT NULL 'SUPER_ADMIN' | 'SUPPORT'
last_login_at TIMESTAMPTZ Track activity
created_at TIMESTAMPTZ DEFAULT NOW()

tenants

All registered Gau Seva organisations

12 columns
Column Type Notes
id UUID PK Tenant unique ID
slug TEXT UNIQUE NOT NULL Subdomain slug (sgmm)
name TEXT NOT NULL Organisation name
status TEXT NOT NULL ONBOARDING|TRIAL|ACTIVE|GRACE|READ_ONLY|SUSPENDED|TERMINATED
plan_id UUID FK → saas_plans.id Current plan
subdomain TEXT UNIQUE trust1.gaudaan.in
custom_domain TEXT Optional custom domain
branding_config JSONB Colors, logo, fonts
settings JSONB Feature flags, limits
trial_ends_at TIMESTAMPTZ 14-day trial expiry
grace_ends_at TIMESTAMPTZ 7-day grace period end
created_at TIMESTAMPTZ DEFAULT NOW()

saas_plans

Subscription tiers: Starter, Standard, Premium, Enterprise

7 columns
Column Type Notes
id UUID PK
name TEXT NOT NULL 'Starter'|'Standard'|'Premium'|'Enterprise'
price_monthly NUMERIC(10,2) ₹999 / ₹2499 / ₹4999
price_yearly NUMERIC(10,2) 15% discount
limits JSONB max_admins, max_donors, modules[]
features JSONB Feature flags array
is_active BOOLEAN DEFAULT true

tenant_subscriptions

Active SaaS subscriptions per tenant

9 columns
Column Type Notes
id UUID PK
tenant_id UUID FK → tenants.id Tenant reference
plan_id UUID FK → saas_plans.id Current plan
status TEXT 'ACTIVE'|'PAST_DUE'|'CANCELLED'
current_period_start TIMESTAMPTZ Billing period start
current_period_end TIMESTAMPTZ Next renewal date
cancel_at_period_end BOOLEAN DEFAULT false
proration_credit NUMERIC(10,2) Prorated amount on upgrade
add_ons JSONB Array of active add-ons

platform_invoices

Monthly invoices for each tenant

11 columns
Column Type Notes
id UUID PK
tenant_id UUID FK → tenants.id
subscription_id UUID FK
invoice_number TEXT UNIQUE 'INV-2025-0248'
amount NUMERIC(10,2) Total amount
tax_amount NUMERIC(10,2) GST 18%
status TEXT 'DRAFT'|'PENDING'|'PAID'|'OVERDUE'|'FAILED'
due_date TIMESTAMPTZ 7 days from issue
paid_at TIMESTAMPTZ Payment timestamp
pdf_url TEXT S3 URL of invoice PDF
attempts INT DEFAULT 0 Retry count

users

App donors/users per tenant

11 columns
Column Type Notes
id UUID PK
tenant_id UUID FK → tenants.id NOT NULL 🔑 Multi-tenancy key
phone TEXT Primary identifier
email TEXT Optional
name TEXT NOT NULL Full name
pan_number TEXT Tax receipt eligibility
address JSONB For 80G certificate
profile_photo_url TEXT S3 URL
fcm_tokens TEXT[] Push notification tokens
gau_coins_balance INT DEFAULT 0 Current balance
created_at TIMESTAMPTZ DEFAULT NOW()

admin_users

Tenant admin staff with role assignments

9 columns
Column Type Notes
id UUID PK
tenant_id UUID FK → tenants.id NOT NULL 🔑 Multi-tenancy key
email TEXT NOT NULL Login email
password_hash TEXT NOT NULL bcrypt
role TEXT NOT NULL TENANT_ADMIN|FINANCE_MANAGER|CONTENT_MANAGER
permissions JSONB Granular permission overrides
totp_secret TEXT MFA secret (encrypted)
is_active BOOLEAN DEFAULT true
last_login_at TIMESTAMPTZ

trust_profile

Legal and operational trust information

12 columns
Column Type Notes
id UUID PK
tenant_id UUID UNIQUE FK → tenants.id 🔑 1:1 with tenant
registration_number TEXT Trust reg number
pan_number TEXT Trust PAN
certificate_12a TEXT 12A registration
certificate_80g TEXT 80G certificate
fcra_number TEXT Optional FCRA
bank_account_name TEXT
bank_account_number_encrypted TEXT AES-256 encrypted
bank_ifsc TEXT IFSC code
bank_name TEXT
visibility_flags JSONB Which fields are public

causes

Donation campaigns and causes per tenant

12 columns
id UUID PK
tenant_id UUID FK NOT NULL 🔑 Multi-tenancy
title TEXT NOT NULL Cause title
description TEXT Rich text
category TEXT 'gau_seva'|'medical'|'panchgavya'|'goshala'
target_amount NUMERIC(12,2) Fundraising goal
collected_amount NUMERIC(12,2) DEFAULT 0 Running total
is_featured BOOLEAN Show on home screen
donation_options JSONB Preset amounts array
image_url TEXT S3 URL
is_active BOOLEAN DEFAULT true
ends_at TIMESTAMPTZ Campaign end date

transactions

All payment transactions

15 columns
id UUID PK
tenant_id UUID FK NOT NULL 🔑 Multi-tenancy
user_id UUID FK → users.id Donor
cause_id UUID FK → causes.id What they donated to
amount NUMERIC(12,2) NOT NULL Donation amount
coins_used INT DEFAULT 0 GauCoins redeemed
net_amount NUMERIC(12,2) amount - coins_value
gateway TEXT 'razorpay'|'payu'|'custom'
gateway_order_id TEXT External order ID
gateway_payment_id TEXT External payment ID
status TEXT 'PENDING'|'CAPTURED'|'FAILED'|'REFUNDED'
idempotency_key TEXT UNIQUE Prevent duplicates
receipt_pdf_url TEXT S3 URL after generation
is_anonymous BOOLEAN DEFAULT false
metadata JSONB Gateway response raw data

recurring_plans

Active recurring donation mandates

10 columns
id UUID PK
tenant_id UUID FK NOT NULL 🔑 Multi-tenancy
user_id UUID FK → users.id Donor
cause_id UUID FK Target cause
amount NUMERIC(12,2) NOT NULL Monthly amount
frequency TEXT 'MONTHLY'|'QUARTERLY'|'YEARLY'
mandate_id TEXT Gateway mandate ID
status TEXT 'ACTIVE'|'PAUSED'|'CANCELLED'|'COMPLETED'
next_charge_at TIMESTAMPTZ Upcoming charge date
total_charged INT DEFAULT 0 Number of charges

payment_gateways

BYO gateway config per tenant (encrypted)

9 columns
id UUID PK
tenant_id UUID FK NOT NULL 🔑 Multi-tenancy
provider TEXT NOT NULL 'razorpay'|'payu'|'custom'
key_id_encrypted TEXT AES-256-GCM encrypted
key_secret_encrypted TEXT AES-256-GCM encrypted
webhook_secret_encrypted TEXT AES-256-GCM encrypted
mode TEXT DEFAULT live 'test'|'live'
is_active BOOLEAN DEFAULT false
config JSONB Extra gateway-specific config

processed_webhooks

Idempotency table for webhook deduplication

7 columns
id UUID PK
tenant_id UUID FK 🔑 Multi-tenancy
gateway TEXT NOT NULL Gateway name
event_id TEXT NOT NULL Gateway event unique ID
event_type TEXT e.g. payment.captured
processed_at TIMESTAMPTZ DEFAULT NOW()
UNIQUE (tenant_id, gateway, event_id) Prevents double-processing
cows
Individual cows for adoption program
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
adoptions
Cow sponsorship records
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
membership_plans
Membership tier definitions
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
memberships
Active donor memberships
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
volunteers
Volunteer registrations
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
events
Trust events (online/offline)
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
event_registrations
Event RSVP records
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
gallery_albums
Photo/video album groupings
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
gallery_media
Individual media files
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
gau_mahima_content
CMS articles, videos, audio
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
trustees
Board of trustee profiles
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
gau_coin_transactions
Coin earn/redeem ledger
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
notifications
Notification history per user
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...
compliance_documents
Document vault per tenant
tenant_id
id UUID PK, tenant_id UUID NOT NULL, ...

Critical Indexes

-- Tenant resolution CREATE UNIQUE INDEX idx_tenants_slug ON tenants(slug); CREATE INDEX idx_tenants_status ON tenants(status); -- Users per tenant CREATE INDEX idx_users_tenant_id ON users(tenant_id); CREATE UNIQUE INDEX idx_users_tenant_phone ON users(tenant_id, phone); -- Transactions (hot queries) CREATE INDEX idx_txn_tenant_id ON transactions(tenant_id); CREATE INDEX idx_txn_user_id ON transactions(tenant_id, user_id); CREATE INDEX idx_txn_status ON transactions(tenant_id, status); CREATE INDEX idx_txn_created ON transactions(tenant_id, created_at DESC); CREATE UNIQUE INDEX idx_txn_idempotency ON transactions(idempotency_key); -- Causes CREATE INDEX idx_causes_tenant ON causes(tenant_id, is_active); -- Recurring plans CREATE INDEX idx_recurring_next ON recurring_plans(next_charge_at) WHERE status = 'ACTIVE'; -- Webhook idempotency CREATE UNIQUE INDEX idx_webhooks_dedup ON processed_webhooks(tenant_id, gateway, event_id); -- Platform invoices CREATE INDEX idx_invoices_tenant ON platform_invoices(tenant_id); CREATE INDEX idx_invoices_status ON platform_invoices(status, due_date); -- Audit logs (write-heavy) CREATE INDEX idx_audit_tenant ON audit_logs(tenant_id, created_at DESC); CREATE INDEX idx_audit_actor ON audit_logs(actor_id, created_at DESC);

Row Level Security (RLS)

-- Enable RLS on all tenant tables ALTER TABLE users ENABLE ROW LEVEL SECURITY; ALTER TABLE transactions ENABLE ROW LEVEL SECURITY; ALTER TABLE causes ENABLE ROW LEVEL SECURITY; -- (repeat for all tenant tables) -- Create policy: tenant isolation CREATE POLICY tenant_isolation ON users USING (tenant_id = current_setting('app.current_tenant_id')::UUID); CREATE POLICY tenant_isolation ON transactions USING (tenant_id = current_setting('app.current_tenant_id')::UUID); -- Set tenant context per request (in middleware) SET LOCAL app.current_tenant_id = '$tenantId';

Migration Strategy

001
create_platform_tables.sql
tenants, plans, subscriptions, invoices
002
create_tenant_core.sql
users, admin_users, trust_profile
003
create_donations.sql
causes, transactions, recurring, gateways
004
create_features.sql
cows, events, gallery, membership, volunteers
005
create_compliance.sql
audit_logs, compliance_docs, notifications
006
create_indexes.sql
All indexes and unique constraints
007
enable_rls.sql
Row Level Security policies
008
seed_plans.sql
Default SaaS plans seeding