Database Schema

Complete documentation of Mail Assist's database structure, including table schemas, relationships, and security policies implemented in Supabase.

Supabase PostgreSQL

Mail Assist uses Supabase's managed PostgreSQL database with Row Level Security (RLS) for data protection and automatic scaling capabilities.

Database Overview

The Mail Assist database consists of two main tables that handle user profiles and email history, with built-in security policies and foreign key relationships.

Database Statistics

Main Tables

Security Policies

Foreign Key Relationship

Profiles Table

The profiles table stores user account information and credit balances. It's linked to Supabase Auth's auth.users table for authentication.

Schema Definition

profiles-table.sql

CREATE TABLE profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE,
  email TEXT,
  credits INTEGER DEFAULT 300,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  PRIMARY KEY (id)
);

Column Details

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, REFERENCES auth.users	User's unique identifier from Supabase Auth
email	TEXT	-	User's email address (synced from auth)
credits	INTEGER	DEFAULT 300	Available email credits for the user
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Account creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last profile update timestamp

Relationships

Parent: auth.users - One-to-one relationship with Supabase Auth
Child: user_mails - One-to-many relationship for email history

Cascade Delete

When a user is deleted from auth.users, their profile and all associated emails are automatically deleted due to the CASCADE constraint.

User Mails Table

The user_mails table stores the complete history of all emails sent by users, including content, metadata, and credit usage information.

Schema Definition

user-mails-table.sql

CREATE TABLE user_mails (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  content TEXT NOT NULL,
  mail_type TEXT NOT NULL CHECK (mail_type IN ('custom', 'template')),
  credits_used INTEGER NOT NULL,
  sent_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

Column Details

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, DEFAULT gen_random_uuid()	Unique identifier for each email record
user_id	UUID	REFERENCES profiles(id)	Foreign key linking to the sender's profile
recipient_email	TEXT	NOT NULL	Email address of the recipient
subject	TEXT	NOT NULL	Subject line of the email
content	TEXT	NOT NULL	Full HTML content of the email
mail_type	TEXT	CHECK ('custom', 'template')	Type of email sent (custom or template)
credits_used	INTEGER	NOT NULL	Number of credits deducted for this email
sent_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Timestamp when the email was sent

Indexes

Recommended indexes for optimal query performance:

indexes.sql

-- Index for user email history queries
CREATE INDEX idx_user_mails_user_id_sent_at 
  ON user_mails(user_id, sent_at DESC);

indexes.sql

-- Index for email type filtering
CREATE INDEX idx_user_mails_mail_type 
  ON user_mails(mail_type);

indexes.sql

-- Index for recipient email searches
CREATE INDEX idx_user_mails_recipient 
  ON user_mails(recipient_email);

Row Level Security (RLS)

Both tables implement Row Level Security to ensure users can only access their own data. This provides database-level security that complements application-level authentication.

Security Policies

rls-policies.sql

-- Enable RLS on both tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_mails ENABLE ROW LEVEL SECURITY;

profiles-policies.sql

-- Profiles table policies
CREATE POLICY "Users can view own profile" 
  ON profiles FOR SELECT 
  USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" 
  ON profiles FOR UPDATE 
  USING (auth.uid() = id);

CREATE POLICY "Users can insert own profile" 
  ON profiles FOR INSERT 
  WITH CHECK (auth.uid() = id);

user-mails-policies.sql

-- User mails table policies
CREATE POLICY "Users can view own mails" 
  ON user_mails FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own mails" 
  ON user_mails FOR INSERT 
  WITH CHECK (auth.uid() = user_id);

Automatic Security

With RLS enabled, users automatically can only see and modify their own data, even if there's a bug in the application code. This provides an additional layer of security.

Database Functions

Custom PostgreSQL functions to handle common operations efficiently at the database level.

Credit Deduction Function

credit-functions.sql

-- Function declaration
CREATE OR REPLACE FUNCTION deduct_credits_and_log_email(
  p_user_id UUID,
  p_recipient_email TEXT,
  p_subject TEXT,
  p_content TEXT,
  p_mail_type TEXT,
  p_credits_needed INTEGER
) RETURNS JSON AS $$

credit-functions.sql

-- Variable declaration
DECLARE
  current_credits INTEGER;
  new_credits INTEGER;
  result JSON;

credit-functions.sql

-- Get current credits with row lock
BEGIN
  SELECT credits INTO current_credits 
  FROM profiles 
  WHERE id = p_user_id 
  FOR UPDATE;

credit-functions.sql

-- Check credit balance
IF current_credits < p_credits_needed THEN
  RETURN json_build_object(
    'success', false, 
    'error', 'Insufficient credits',
    'current_credits', current_credits
  );
END IF;

credit-functions.sql

-- Calculate new balance and update
new_credits := current_credits - p_credits_needed;
  
UPDATE profiles 
SET credits = new_credits, updated_at = NOW() 
WHERE id = p_user_id;

credit-functions.sql

-- Log the email
INSERT INTO user_mails (
  user_id, recipient_email, subject, content, 
  mail_type, credits_used
) VALUES (
  p_user_id, p_recipient_email, p_subject, p_content,
  p_mail_type, p_credits_needed
);

credit-functions.sql

-- Return success
RETURN json_build_object(
  'success', true,
  'credits_used', p_credits_needed,
  'remaining_credits', new_credits
);

credit-functions.sql

-- Error handling
EXCEPTION WHEN OTHERS THEN
  RETURN json_build_object(
    'success', false,
    'error', SQLERRM
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Complete Database Setup

Script for initializing the Mail Assist database from scratch.

complete-setup.sql

-- 1. Create profiles table
CREATE TABLE IF NOT EXISTS profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE,
  email TEXT,
  credits INTEGER DEFAULT 300,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  PRIMARY KEY (id)
);

complete-setup.sql

-- 2. Create user_mails table
CREATE TABLE IF NOT EXISTS user_mails (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  content TEXT NOT NULL,
  mail_type TEXT NOT NULL CHECK (mail_type IN ('custom', 'template')),
  credits_used INTEGER NOT NULL,
  sent_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

complete-setup.sql

-- 3. Create indexes
CREATE INDEX IF NOT EXISTS idx_user_mails_user_id_sent_at 
  ON user_mails(user_id, sent_at DESC);
  
CREATE INDEX IF NOT EXISTS idx_user_mails_mail_type 
  ON user_mails(mail_type);
  
CREATE INDEX IF NOT EXISTS idx_user_mails_recipient 
  ON user_mails(recipient_email);

complete-setup.sql

-- 4. Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_mails ENABLE ROW LEVEL SECURITY;

complete-setup.sql

-- 5. Create RLS policies
-- Profiles policies
CREATE POLICY "Users can view own profile" ON profiles 
  FOR SELECT USING (auth.uid() = id);
  
CREATE POLICY "Users can update own profile" ON profiles 
  FOR UPDATE USING (auth.uid() = id);
  
CREATE POLICY "Users can insert own profile" ON profiles 
  FOR INSERT WITH CHECK (auth.uid() = id);

complete-setup.sql

-- User mails policies
CREATE POLICY "Users can view own mails" ON user_mails 
  FOR SELECT USING (auth.uid() = user_id);
  
CREATE POLICY "Users can insert own mails" ON user_mails 
  FOR INSERT WITH CHECK (auth.uid() = user_id);

complete-setup.sql

-- 6. Create trigger for profile creation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

Backup and Restore

Best practices for backing up and restoring your Mail Assist database.

Backup Strategy

Automated Backups - Supabase provides automatic daily backups
Point-in-Time Recovery - Available for Pro plans and above
Manual Exports - Export specific tables or entire database
Schema Versioning - Track database schema changes

backup-commands.sh

# Export database schema
pg_dump --schema-only --no-owner --no-privileges \
  "postgresql://user:pass@host:port/db" > schema.sql

backup-commands.sh

# Export data only
pg_dump --data-only --no-owner --no-privileges \
  "postgresql://user:pass@host:port/db" > data.sql

backup-commands.sh

# Export specific table
pg_dump --table=user_mails --no-owner --no-privileges \
  "postgresql://user:pass@host:port/db" > user_mails.sql

Supabase Backups

Supabase automatically handles backups for you, but it's good practice to maintain your own schema files for version control and disaster recovery.

Performance Optimization

Tips and techniques for optimizing database performance as your Mail Assist application scales.

Query Optimization

Use Indexes - Ensure proper indexing on frequently queried columns
Limit Results - Always use pagination for large datasets
Select Specific Columns - Avoid SELECT * in production queries
Use Prepared Statements - Leverage Supabase's query caching

optimized-queries.sql

-- Optimized query for email history with pagination
SELECT id, recipient_email, subject, mail_type, credits_used, sent_at
FROM user_mails 
WHERE user_id = $1 
ORDER BY sent_at DESC 
LIMIT $2 OFFSET $3;

optimized-queries.sql

-- Optimized query for credit balance
SELECT credits 
FROM profiles 
WHERE id = $1;

optimized-queries.sql

-- Optimized query for email statistics
SELECT 
  mail_type,
  COUNT(*) as count,
  SUM(credits_used) as total_credits
FROM user_mails 
WHERE user_id = $1 
  AND sent_at >= $2 
GROUP BY mail_type;

Database Monitoring

Monitor these key metrics to ensure optimal database performance:

Query Performance - Track slow queries and optimize them
Connection Pool - Monitor connection usage
Storage Usage - Track database size growth
Index Usage - Ensure indexes are being utilized

Database Setup Complete

You now have a comprehensive understanding of Mail Assist's database structure. The schema is designed for scalability, security, and performance.