Database Schema & Architecture

Entity Relationship Overview

profiles

Extended user information beyond Supabase auth, including display preferences and social links.

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE NOT NULL,
  display_name TEXT,
  bio TEXT,
  avatar_url TEXT,
  social_links JSONB DEFAULT '{}',
  website_url TEXT,
  location TEXT,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes for performance
CREATE INDEX idx_profiles_username ON profiles(username);
CREATE INDEX idx_profiles_created_at ON profiles(created_at);

pages

User-created pages with customizable layouts, styling, and metadata configurations.

CREATE TABLE pages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  slug TEXT NOT NULL,
  description TEXT,
  style TEXT DEFAULT 'default',
  layout TEXT DEFAULT 'stack',
  spacing TEXT DEFAULT 'normal',
  padding TEXT DEFAULT 'normal',
  max_width TEXT DEFAULT 'default',
  metadata JSONB DEFAULT '{}',
  is_public BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(profile_id, slug)
);

-- Indexes
CREATE INDEX idx_pages_profile_id ON pages(profile_id);
CREATE INDEX idx_pages_slug ON pages(slug);
CREATE INDEX idx_pages_public ON pages(is_public) WHERE is_public = true;

content_blocks

Flexible content storage with JSONB for type-specific data and ordering support.

CREATE TABLE content_blocks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  content JSONB NOT NULL DEFAULT '{}',
  order_index INTEGER NOT NULL DEFAULT 0,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes for performance
CREATE INDEX idx_content_blocks_page_id ON content_blocks(page_id);
CREATE INDEX idx_content_blocks_order ON content_blocks(page_id, order_index);
CREATE INDEX idx_content_blocks_type ON content_blocks(type);

-- GIN index for JSONB content searching
CREATE INDEX idx_content_blocks_content ON content_blocks USING GIN(content);

Role-Based Access Control

Comprehensive permission system supporting subscription tiers, special roles, and granular permissions.

CREATE TABLE roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT UNIQUE NOT NULL,
  description TEXT,
  role_type TEXT NOT NULL, -- 'permission', 'subscription', 'special'
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Default roles
INSERT INTO roles (name, description, role_type) VALUES
('admin', 'Complete application control', 'permission'),
('moderator', 'Content moderation', 'permission'),
('basic', 'Free tier', 'subscription'),
('turbo', 'Turbo subscription', 'subscription'),
('summit', 'Summit subscription', 'subscription'),
('alpha', 'Alpha program', 'special'),
('beta', 'Beta program', 'special');

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  expires_at TIMESTAMPTZ, -- For subscription roles
  UNIQUE (user_id, role_id)
);

-- Indexes
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
CREATE INDEX idx_user_roles_expires ON user_roles(expires_at) WHERE expires_at IS NOT NULL;

Resource Limits

Subscription-based limits for pages, blocks, and content size by role.

CREATE TABLE resource_limits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  resource_type TEXT NOT NULL, -- 'page', 'block', 'storage'
  resource_subtype TEXT, -- 'image_block', 'video_block', etc.
  max_count INTEGER DEFAULT -1, -- -1 = unlimited
  max_size INTEGER DEFAULT -1, -- in KB, -1 = unlimited
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Example limits
INSERT INTO resource_limits (role_id, resource_type, resource_subtype, max_count, max_size) 
SELECT id, 'page', NULL, 3, -1 FROM roles WHERE name = 'basic';

INSERT INTO resource_limits (role_id, resource_type, resource_subtype, max_count, max_size)
SELECT id, 'block', 'image_block', 5, 1024 FROM roles WHERE name = 'basic';

Security Policies

PostgreSQL RLS policies ensure users can only access their own data and appropriate public content.

-- Users can read all public profiles
CREATE POLICY "Public profiles are viewable by everyone"
  ON profiles FOR SELECT
  USING (true);

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

-- Public pages are viewable by everyone
CREATE POLICY "Public pages are viewable by everyone"
  ON pages FOR SELECT
  USING (is_public = true);

-- Users can access their own pages (public or private)
CREATE POLICY "Users can access own pages"
  ON pages FOR ALL
  USING (profile_id IN (
    SELECT id FROM profiles WHERE id = auth.uid()
  ));

-- Blocks inherit visibility from their page
CREATE POLICY "Blocks follow page visibility"
  ON content_blocks FOR SELECT
  USING (
    page_id IN (
      SELECT id FROM pages 
      WHERE is_public = true
      OR profile_id = auth.uid()
    )
  );

-- Only page owners can modify blocks
CREATE POLICY "Users can modify own page blocks"
  ON content_blocks FOR ALL
  USING (
    page_id IN (
      SELECT id FROM pages WHERE profile_id = auth.uid()
    )
  );

Custom Functions

PostgreSQL functions for complex queries and business logic.

CREATE OR REPLACE FUNCTION get_user_roles(user_id UUID)
RETURNS TABLE(role_name TEXT, role_type TEXT, expires_at TIMESTAMPTZ)
LANGUAGE sql SECURITY DEFINER
AS $$
  SELECT r.name, r.role_type, ur.expires_at
  FROM user_roles ur
  JOIN roles r ON ur.role_id = r.id
  WHERE ur.user_id = $1
  AND (ur.expires_at IS NULL OR ur.expires_at > now());
$$;

CREATE OR REPLACE FUNCTION get_subscription_level(user_id UUID)
RETURNS TEXT
LANGUAGE sql SECURITY DEFINER
AS $$
  SELECT COALESCE(
    (SELECT r.name FROM user_roles ur
     JOIN roles r ON ur.role_id = r.id
     WHERE ur.user_id = $1 
     AND r.role_type = 'subscription'
     AND (ur.expires_at IS NULL OR ur.expires_at > now())
     ORDER BY 
       CASE r.name
         WHEN 'lifetime_summit' THEN 5
         WHEN 'lifetime_turbo' THEN 4
         WHEN 'summit' THEN 3
         WHEN 'turbo' THEN 2
         WHEN 'basic' THEN 1
         ELSE 0
       END DESC
     LIMIT 1),
    'basic'
  );
$$;

Triggers

-- Function to update timestamps
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

-- Apply to all tables with updated_at
CREATE TRIGGER profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION handle_updated_at();