Supabase Database Integration

OnlyAutomator uses Supabase as its database infrastructure, providing a robust PostgreSQL database with additional features like real-time subscriptions, authentication, and storage.

For specific API endpoints to manage accounts in the database, please refer to the Accounts API operations:

Database Overview

The database schema is organized by functional areas, providing clear separation of concerns and optimized data access patterns. Database Schema Overview Core Tables Relationship

Database Schema

Below is a detailed SQL representation of the main tables in the OnlyAutomator database.

Core Tables

The core tables manage user accounts and basic information.

users

Stores primary user information.

CREATE TABLE users (
  id UUID REFERENCES auth.users PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  avatar_url TEXT,
  metadata JSONB,
  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
);

-- Automatically create a profile entry when a new user signs up
CREATE FUNCTION public.handle_new_user() 
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, email, name)
  VALUES (new.id, new.email, new.raw_user_meta_data->>'name');
  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();

accounts

Stores OnlyFans creator accounts linked to users.

CREATE TABLE accounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  username TEXT NOT NULL,
  description TEXT,
  web_cookies JSONB,
  web_local_storage JSONB,
  web_session_storage JSONB,
  user_agent TEXT,
  last_scraped_at TIMESTAMP WITH TIME ZONE,
  is_subscribed BOOLEAN DEFAULT FALSE,
  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
);

-- Row Level Security for accounts
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own accounts" ON accounts
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own accounts" ON accounts
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own accounts" ON accounts
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own accounts" ON accounts
  FOR DELETE USING (auth.uid() = user_id);

customers

Stores Stripe customer information for billing.

CREATE TABLE customers (
  id UUID REFERENCES users(id) PRIMARY KEY,
  stripe_customer_id TEXT,
  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
);

-- Row Level Security for customers
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own customer data" ON customers
  FOR SELECT USING (auth.uid() = id);

Fan Management Tables

Fan Management Tables

Tables that store information about creator fans and their interactions.

fans

Stores basic information about OnlyFans subscribers.

CREATE TABLE fans (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  username TEXT NOT NULL,
  name TEXT,
  avatar_url TEXT,
  header_url TEXT,
  is_favorite BOOLEAN DEFAULT FALSE,
  last_active TIMESTAMP WITH TIME ZONE,
  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,
  UNIQUE(account_id, username)
);

-- Row Level Security for fans
ALTER TABLE fans ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans through accounts" ON fans
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

fans_service

Stores detailed service information about fans.

CREATE TABLE fans_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  subscription_tier TEXT,
  subscription_price DECIMAL(10, 2),
  is_active BOOLEAN DEFAULT TRUE,
  status TEXT, 
  first_subscription_date TIMESTAMP WITH TIME ZONE,
  last_subscription_date TIMESTAMP WITH TIME ZONE,
  next_billing_date TIMESTAMP WITH TIME ZONE,
  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,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_service
ALTER TABLE fans_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_service through accounts" ON fans_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

fans_statistics_service

Stores financial statistics related to fans.

CREATE TABLE fans_statistics_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  current_spend DECIMAL(10, 2) DEFAULT 0,
  lifetime_spend DECIMAL(10, 2) DEFAULT 0,
  tips_amount DECIMAL(10, 2) DEFAULT 0,
  tips_count INTEGER DEFAULT 0,
  paid_posts_amount DECIMAL(10, 2) DEFAULT 0,
  paid_posts_count INTEGER DEFAULT 0,
  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,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_statistics_service
ALTER TABLE fans_statistics_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_statistics_service through accounts" ON fans_statistics_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_statistics_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

fans_message_statistics_service

Stores messaging statistics related to fans.

CREATE TABLE fans_message_statistics_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  messages_received INTEGER DEFAULT 0,
  messages_sent INTEGER DEFAULT 0,
  last_message_date TIMESTAMP WITH TIME ZONE,
  response_rate DECIMAL(5, 2) DEFAULT 0,
  average_response_time INTEGER, -- in minutes
  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,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_message_statistics_service
ALTER TABLE fans_message_statistics_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_message_statistics_service through accounts" ON fans_message_statistics_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_message_statistics_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

Subscription Management Tables

Subscription Management

Tables that manage user subscriptions to OnlyAutomator’s premium features.

products

Stores information about available products.

CREATE TABLE products (
  id TEXT PRIMARY KEY,
  active BOOLEAN,
  name TEXT,
  description TEXT,
  image TEXT,
  metadata JSONB,
  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
);

-- Allow public read access to products
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to products" ON products
  FOR SELECT USING (true);

prices

Defines the pricing structure for products.

CREATE TABLE prices (
  id TEXT PRIMARY KEY,
  product_id TEXT REFERENCES products(id),
  active BOOLEAN,
  description TEXT,
  unit_amount INTEGER,
  currency TEXT,
  type TEXT,
  interval TEXT,
  interval_count INTEGER,
  trial_period_days INTEGER,
  metadata JSONB,
  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
);

-- Allow public read access to prices
ALTER TABLE prices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to prices" ON prices
  FOR SELECT USING (true);

subscriptions

Tracks user subscriptions.

CREATE TABLE subscriptions (
  id TEXT PRIMARY KEY,
  user_id UUID REFERENCES users(id) NOT NULL,
  status TEXT,
  metadata JSONB,
  price_id TEXT REFERENCES prices(id),
  quantity INTEGER,
  cancel_at_period_end BOOLEAN,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  ended_at TIMESTAMP WITH TIME ZONE,
  cancel_at TIMESTAMP WITH TIME ZONE,
  canceled_at TIMESTAMP WITH TIME ZONE,
  trial_start TIMESTAMP WITH TIME ZONE,
  trial_end TIMESTAMP WITH TIME ZONE
);

-- Row Level Security for subscriptions
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own subscriptions" ON subscriptions
  FOR SELECT USING (auth.uid() = user_id);

Content Management Tables

Content Management

Tables that organize user content such as message templates and automation scripts.

folders

Organizes user content into folders.

CREATE TABLE folders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  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
);

-- Row Level Security for folders
ALTER TABLE folders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own folders" ON folders
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own folders" ON folders
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own folders" ON folders
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own folders" ON folders
  FOR DELETE USING (auth.uid() = user_id);

scripts

Stores user-created scripts.

CREATE TABLE scripts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  content TEXT NOT NULL,
  is_template BOOLEAN DEFAULT FALSE,
  variables JSONB,
  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
);

-- Row Level Security for scripts
ALTER TABLE scripts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own scripts" ON scripts
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own scripts" ON scripts
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own scripts" ON scripts
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own scripts" ON scripts
  FOR DELETE USING (auth.uid() = user_id);

emojis

Stores emoji data for user communications.

CREATE TABLE emojis (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  symbol TEXT NOT NULL,
  category TEXT,
  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
);

-- Row Level Security for emojis
ALTER TABLE emojis ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own emojis" ON emojis
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own emojis" ON emojis
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own emojis" ON emojis
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own emojis" ON emojis
  FOR DELETE USING (auth.uid() = user_id);

System Tables

Tables that support system operations.

email_tracking

Tracks email delivery and engagement.

CREATE TABLE email_tracking (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  email_id TEXT NOT NULL,
  campaign_id TEXT,
  sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  opened_at TIMESTAMP WITH TIME ZONE,
  clicked_at TIMESTAMP WITH TIME ZONE,
  click_count INTEGER DEFAULT 0,
  links_clicked JSONB,
  device_info JSONB,
  is_bounced BOOLEAN DEFAULT FALSE,
  bounce_reason TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Row Level Security for email_tracking
ALTER TABLE email_tracking ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own email tracking data" ON email_tracking
  FOR SELECT USING (auth.uid() = user_id);

Client Integration

Connect to the Supabase database in your application:

import { createClient } from '@supabase/supabase-js';
import { Database } from './types_db';

// Create a single supabase client for interacting with your database
const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Example query: get user accounts
async function getUserAccounts(userId: string) {
    const { data, error } = await supabase
      .from('accounts')
      .select('*')
      .eq('user_id', userId);
      
  if (error) {
    console.error('Error fetching accounts:', error);
    return null;
  }
  
  return data;
}

Real-time Subscriptions

Supabase provides real-time data subscriptions:

// Subscribe to changes in the accounts table for a specific user
const accountsSubscription = supabase
  .channel('table-db-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'accounts',
      filter: `user_id=eq.${user.id}`
    },
    (payload) => {
      console.log('Change received!', payload);
      // Update UI or state based on the change
      updateAccounts(payload.new);
    }
  )
  .subscribe();

// Don't forget to unsubscribe when component unmounts
useEffect(() => {
  return () => {
    supabase.removeChannel(accountsSubscription);
  };
}, []);

Row-Level Security

Supabase enforces Row-Level Security (RLS) to ensure data access control:

All tables have RLS enabled
Policies restrict access to the user’s own data
API calls automatically include the user’s token for authentication

This ensures that users can only access their own data, even when using client-side queries.

Getting Started

Platform Components

Data Management

Development

Guides

Supabase Database Integration

Database Overview

Database Schema

Core Tables

users

accounts

customers

Fan Management Tables

fans

fans_service

fans_statistics_service

fans_message_statistics_service

Subscription Management

products

prices

subscriptions

Content Management

folders

scripts

tags

emojis

System Tables

email_tracking

Client Integration

Real-time Subscriptions

Row-Level Security

Getting Started

Platform Components

Data Management

Development

Guides

​Database Overview

​Database Schema

​Core Tables

​users

​accounts

​customers

​Fan Management Tables

​fans

​fans_service

​fans_statistics_service

​fans_message_statistics_service

​Subscription Management

​products

​prices

​subscriptions

​Content Management

​folders

​scripts

​tags

​emojis

​System Tables

​email_tracking

​Client Integration

​Real-time Subscriptions

​Row-Level Security

Database Overview

Database Schema

Core Tables

users

accounts

customers

Fan Management Tables

fans

fans_service

fans_statistics_service

fans_message_statistics_service

Subscription Management

products

prices

subscriptions

Content Management

folders

scripts

tags

emojis

System Tables

email_tracking

Client Integration

Real-time Subscriptions

Row-Level Security