Database Schema

Overview

Novaqy Care Connect uses a PostgreSQL database hosted on Supabase with dual-region setup (US East + India Mumbai) for optimal global performance.

Tables

Core Tables

1. users

Stores customer account information with GDPR compliance.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  full_name TEXT NOT NULL,
  phone TEXT,
  country_code TEXT DEFAULT 'US',
  status user_status DEFAULT 'active',
  stripe_customer_id TEXT UNIQUE,
  razorpay_customer_id TEXT UNIQUE,
  preferences JSONB DEFAULT '{}',
  data_export_requested BOOLEAN DEFAULT false,
  data_deletion_requested BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Key Fields: - status: Enum (active, suspended, churned, deleted) - preferences: JSONB for flexible user settings - data_export_requested: GDPR compliance flag - data_deletion_requested: GDPR compliance flag

2. plans

Subscription plan definitions with regional pricing.

CREATE TABLE plans (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  price_usd DECIMAL(10,2) NOT NULL,
  price_cad DECIMAL(10,2),
  billing_period TEXT DEFAULT 'monthly',
  features JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT true,
  ach_discount_percent DECIMAL(5,2) DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Key Fields: - slug: URL-friendly identifier (e.g., 'essential', 'premium') - features: JSONB array of feature descriptions - ach_discount_percent: Discount for ACH payment method

3. subscriptions

Active and historical subscription records.

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE RESTRICT,
  status subscription_status DEFAULT 'active',
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancel_at_period_end BOOLEAN DEFAULT false,
  canceled_at TIMESTAMPTZ,
  stripe_subscription_id TEXT UNIQUE,
  razorpay_subscription_id TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Status Values: - active: Currently active subscription - past_due: Payment failed, grace period - canceled: User canceled - paused: Temporarily paused - expired: Ended naturally

4. payments

Payment transaction records with full reconciliation.

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  subscription_id UUID REFERENCES subscriptions(id),
  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'USD',
  status payment_status DEFAULT 'pending',
  payment_method TEXT,
  payment_gateway TEXT NOT NULL,
  gateway_payment_id TEXT UNIQUE,
  gateway_order_id TEXT,
  invoice_url TEXT,
  failure_reason TEXT,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Payment Gateways: - razorpay: India/International payments - stripe: US payments (future)

5. refunds

Refund requests and processing.

CREATE TABLE refunds (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_id UUID REFERENCES payments(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL,
  reason refund_reason NOT NULL,
  reason_details TEXT,
  status TEXT DEFAULT 'pending',
  processed_at TIMESTAMPTZ,
  gateway_refund_id TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Refund Reasons: - customer_request - duplicate_charge - fraudulent - service_issue - other

Marketing Tables

6. leads

Lead capture from forms and landing pages.

CREATE TABLE leads (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT NOT NULL,
  full_name TEXT,
  phone TEXT,
  source TEXT,
  utm_campaign TEXT,
  utm_medium TEXT,
  utm_source TEXT,
  landing_page TEXT,
  status lead_status DEFAULT 'new',
  converted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Lead Status: - new: Just captured - contacted: Sales team reached out - qualified: Meets criteria - converted: Became customer - lost: Did not convert

7. geo_pages

Location-based landing pages for SEO.

CREATE TABLE geo_pages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  country TEXT NOT NULL,
  state TEXT NOT NULL,
  city TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  meta_title TEXT NOT NULL,
  meta_description TEXT NOT NULL,
  content JSONB DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Example: /locations/us/ny/new-york

Operations Tables

8. customer_logs

Audit trail for customer actions.

CREATE TABLE customer_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  action TEXT NOT NULL,
  details JSONB DEFAULT '{}',
  ip_address TEXT,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Common Actions: - login, logout, payment_made, subscription_changed, data_exported

9. sessions

User session tracking with consent.

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id),
  session_token TEXT UNIQUE NOT NULL,
  ip_address TEXT,
  user_agent TEXT,
  session_type session_type DEFAULT 'web',
  consent_analytics BOOLEAN DEFAULT false,
  consent_marketing BOOLEAN DEFAULT false,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

10. chargeback_evidence

Dispute protection documentation.

CREATE TABLE chargeback_evidence (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_id UUID REFERENCES payments(id) ON DELETE CASCADE,
  evidence_type TEXT NOT NULL,
  evidence_data JSONB NOT NULL,
  submitted_at TIMESTAMPTZ,
  outcome TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

11. webhooks_log

Integration webhook monitoring.

CREATE TABLE webhooks_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  source TEXT NOT NULL,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT DEFAULT 'received',
  processed_at TIMESTAMPTZ,
  error_message TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Indexes

Performance Indexes

-- Email lookups
CREATE INDEX idx_users_email ON users(email);

-- Payment queries
CREATE INDEX idx_payments_user_id ON payments(user_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_created_at ON payments(created_at DESC);

-- Subscription queries
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

-- Lead attribution
CREATE INDEX idx_leads_source ON leads(source);
CREATE INDEX idx_leads_status ON leads(status);
CREATE INDEX idx_leads_created_at ON leads(created_at DESC);

-- Full-text search
CREATE INDEX idx_users_full_name_gin ON users USING gin(to_tsvector('english', full_name));
CREATE INDEX idx_geo_pages_city_gin ON geo_pages USING gin(to_tsvector('english', city));

Functions

1. update_updated_at()

Automatically updates updated_at timestamp on row changes.

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. calculate_subscription_end_date()

Calculates subscription end date based on billing period.

CREATE OR REPLACE FUNCTION calculate_subscription_end_date(
  start_date TIMESTAMPTZ,
  billing_period TEXT
)
RETURNS TIMESTAMPTZ AS $$
BEGIN
  RETURN CASE
    WHEN billing_period = 'monthly' THEN start_date + INTERVAL '1 month'
    WHEN billing_period = 'quarterly' THEN start_date + INTERVAL '3 months'
    WHEN billing_period = 'yearly' THEN start_date + INTERVAL '1 year'
    ELSE start_date + INTERVAL '1 month'
  END;
END;
$$ LANGUAGE plpgsql;

Row-Level Security (RLS)

Users Table

-- Users can read their own data
CREATE POLICY "Users can view own data"
  ON users FOR SELECT
  USING (auth.uid() = id);

-- Users can update their own data
CREATE POLICY "Users can update own data"
  ON users FOR UPDATE
  USING (auth.uid() = id);

Payments Table

-- Users can view their own payments
CREATE POLICY "Users can view own payments"
  ON payments FOR SELECT
  USING (auth.uid() = user_id);

-- Service role can do everything
CREATE POLICY "Service role full access"
  ON payments FOR ALL
  USING (auth.role() = 'service_role');

Leads Table

-- Anyone can insert leads (public forms)
CREATE POLICY "Public can insert leads"
  ON leads FOR INSERT
  WITH CHECK (true);

-- Only authenticated users can read
CREATE POLICY "Authenticated users can read leads"
  ON leads FOR SELECT
  USING (auth.role() = 'authenticated');

Migrations

All migrations are in supabase/migrations/:

20251123000001_enhanced_schema.sql - Base tables and indexes
20251123000002_rls_policies.sql - Security policies
20251123000003_seed_data.sql - Initial plans and geo pages

Apply with:

npm run supabase:migrate

Backup Strategy

Automated daily backups via Supabase
Point-in-time recovery available for Pro plan
Manual backups before major migrations

Monitoring

Query performance via Supabase Dashboard
Table sizes monitored for growth
RLS policy hits tracked for security audits