Database Schema Documentation¶

Overview¶

This document provides comprehensive documentation for the Supabase database schema used by the HeavyGood Content Platform. The database supports a multi-module architecture with three main modules:

Content Production Module (CPM) - Handles content generation jobs and client management
Strategy Management Module (SMM) - Manages content strategies, SEO rules, and client preferences
Instruction Module (IM) - Handles prompt templates and job processing

Database Architecture¶

The database uses PostgreSQL with Supabase features including: - Row Level Security (RLS) for multi-tenancy - JSONB columns for flexible configuration storage - UUID primary keys for all tables - Comprehensive indexing for performance - Automatic timestamp management with triggers

Complete Table Schema¶

1. Core Authentication & Client Management¶

`clients` Table¶

Purpose: Hierarchical client structure supporting root organizations, agencies, and customers

CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('root', 'agency', 'customer')),
    email VARCHAR(255) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT true,
    settings JSONB DEFAULT '{}',

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ DEFAULT NULL,

    -- Usage limits
    daily_request_limit INTEGER DEFAULT NULL,
    monthly_cost_limit DECIMAL(10,2) DEFAULT NULL,

    -- Contact information
    company VARCHAR(255) DEFAULT NULL,
    contact_person VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL
);

Key Features: - Hierarchical structure with self-referencing parent_id - Three client types: root (top-level), agency (mid-level), customer (end-client) - JSONB settings for flexible configuration - Usage limits for cost control

`api_keys` Table¶

Purpose: API key management for authentication and rate limiting

CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    key_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,

    -- Permissions and usage
    permissions JSONB DEFAULT '["read", "write"]',
    last_used TIMESTAMPTZ DEFAULT NULL,
    usage_count BIGINT DEFAULT 0,

    -- Lifecycle
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ DEFAULT NULL,

    -- Rate limiting
    rate_limit_per_minute INTEGER DEFAULT 60,
    rate_limit_per_hour INTEGER DEFAULT 1000,
    rate_limit_per_day INTEGER DEFAULT 10000
);

Key Features: - Bcrypt hashed API keys for security - Flexible permissions system via JSONB - Built-in rate limiting per key - Usage tracking and analytics

`user_clients` Table¶

Purpose: Multi-tenancy mapping between Supabase users and clients

CREATE TABLE user_clients (
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    PRIMARY KEY (user_id, client_id)
);

Role Hierarchy: - owner (4) - Full control, can manage other owners - admin (3) - Can manage users and settings, but not owners - member (2) - Can create and edit content - viewer (1) - Read-only access

`client_invitations` Table¶

Purpose: Invitation system for adding users to clients

CREATE TABLE client_invitations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    invited_by UUID NOT NULL REFERENCES auth.users(id),
    invited_at TIMESTAMPTZ DEFAULT NOW(),
    accepted_at TIMESTAMPTZ DEFAULT NULL,
    expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '7 days',
    token TEXT UNIQUE DEFAULT encode(gen_random_bytes(32), 'hex')
);

2. Content Production Module (CPM)¶

`jobs` Table¶

Purpose: Tracks content generation requests and results

CREATE TABLE jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    content_type VARCHAR(100) NOT NULL,
    prompt TEXT NOT NULL,
    llm_provider VARCHAR(50) NOT NULL DEFAULT 'openai',
    status job_status NOT NULL DEFAULT 'pending',
    parameters JSONB DEFAULT '{}',
    result JSONB DEFAULT NULL,
    error_message TEXT DEFAULT NULL,

    -- Token usage and cost tracking
    input_tokens INTEGER DEFAULT NULL,
    output_tokens INTEGER DEFAULT NULL,
    total_tokens INTEGER DEFAULT NULL,
    estimated_cost DECIMAL(10,6) DEFAULT NULL,
    provider_model VARCHAR(100) DEFAULT NULL,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    started_at TIMESTAMPTZ DEFAULT NULL,
    completed_at TIMESTAMPTZ DEFAULT NULL
);

Status Enum:

CREATE TYPE job_status AS ENUM (
    'pending',
    'in_progress',
    'completed',
    'failed',
    'cancelled'
);

Key Features: - Complete job lifecycle tracking - Cost and token usage analytics - Flexible parameters via JSONB - Automatic timestamp management

3. Strategy Management Module (SMM)¶

`strategies` Table¶

Purpose: Content generation strategies with versioning

CREATE TABLE strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    config JSONB NOT NULL DEFAULT '{}',
    version INTEGER DEFAULT 1,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

Config Structure Example:

{
  "content_guidelines": {
    "min_words": 800,
    "max_words": 2000,
    "tone": "professional",
    "include_examples": true
  },
  "tone_voice": {
    "formality": "professional",
    "enthusiasm": "high",
    "technical_depth": "moderate"
  },
  "brand_voice": {
    "key_messages": ["innovation", "quality", "service"],
    "avoid_terms": ["cheap", "basic"],
    "unique_value": "Premium solutions for modern businesses"
  }
}

`strategy_history` Table¶

Purpose: Version control and audit trail for strategies

CREATE TABLE strategy_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    strategy_id UUID REFERENCES strategies(id) ON DELETE CASCADE NOT NULL,
    version INTEGER NOT NULL,
    config JSONB NOT NULL,
    changed_by UUID REFERENCES auth.users(id),
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    change_summary TEXT
);

`seo_rules` Table¶

Purpose: Client-specific SEO and content generation rules

CREATE TABLE seo_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    rule_type TEXT NOT NULL,
    rules JSONB NOT NULL,
    priority INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Rule Types: - keywords - Primary keywords and phrases - services - Service offerings and descriptions - locations - Geographic targeting - avoid_terms - Terms to avoid in content - compliance - Industry compliance requirements - specifications - Technical specifications

Rules JSON Structure:

{
  "keywords": ["plumbing", "emergency repair", "licensed plumber"],
  "weight": 0.8,
  "description": "Primary service keywords"
}

`content_preferences` Table¶

Purpose: Content type-specific preferences

CREATE TABLE content_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    content_type TEXT NOT NULL CHECK (content_type IN ('blog', 'social', 'local')),
    preferences JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(client_id, content_type)
);

Preferences JSON Examples:

Blog Content:

{
  "word_count": {"min": 800, "max": 1500},
  "sections": ["introduction", "main_content", "conclusion"],
  "include_citations": true,
  "seo_optimization": true
}

Social Media:

{
  "platforms": ["facebook", "twitter", "linkedin"],
  "hashtags": ["#business", "#service"],
  "include_images": true,
  "character_limits": {"twitter": 280, "facebook": 2000}
}

4. Instruction Module (IM)¶

`prompt_templates` Table¶

Purpose: Client-specific prompt templates for content generation

CREATE TABLE prompt_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    template_name TEXT NOT NULL,
    template_content TEXT NOT NULL,
    content_type TEXT,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(client_id, template_name)
);

Template Example:

Write an engaging blog post about {topic} for {industry} targeting {audience}. 
Include the following keywords: {keywords}. 
The tone should be {tone} and approximately {word_count} words.

`prompt_jobs` Table¶

Purpose: Tracks prompt generation job lifecycle

CREATE TABLE prompt_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status job_status NOT NULL DEFAULT 'pending',
    params JSONB NOT NULL,
    result JSONB DEFAULT NULL,
    error TEXT DEFAULT NULL,
    client_id TEXT NOT NULL,
    template_id UUID REFERENCES prompt_templates(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Table Relationships¶

Entity Relationship Diagram¶

erDiagram
    %% Core Authentication & Client Management
    clients ||--o{ clients : "parent_id (hierarchical)"
    clients ||--o{ api_keys : "client_id"
    clients ||--o{ user_clients : "client_id"
    clients ||--o{ client_invitations : "client_id"
    auth_users ||--o{ user_clients : "user_id"
    auth_users ||--o{ client_invitations : "invited_by"

    %% Strategy Management Module
    clients ||--o{ strategies : "client_id"
    clients ||--o{ seo_rules : "client_id"
    clients ||--o{ content_preferences : "client_id"
    clients ||--o{ prompt_templates : "client_id"
    strategies ||--o{ strategy_history : "strategy_id"
    auth_users ||--o{ strategies : "created_by"
    auth_users ||--o{ strategy_history : "changed_by"

    %% Content Production Module
    clients ||--o{ jobs : "client_id"

    %% Instruction Module
    prompt_templates ||--o{ prompt_jobs : "template_id"

    %% Table Definitions
    clients {
        uuid id PK
        uuid parent_id FK
        text name
        text type
        varchar email UK
        boolean is_active
        jsonb settings
        timestamptz created_at
        timestamptz updated_at
        timestamptz last_login
        integer daily_request_limit
        decimal monthly_cost_limit
        varchar company
        varchar contact_person
        varchar phone
    }

    api_keys {
        uuid id PK
        uuid client_id FK
        varchar key_hash
        varchar name
        boolean is_active
        jsonb permissions
        timestamptz last_used
        bigint usage_count
        timestamptz created_at
        timestamptz expires_at
        integer rate_limit_per_minute
        integer rate_limit_per_hour
        integer rate_limit_per_day
    }

    user_clients {
        uuid user_id PK,FK
        uuid client_id PK,FK
        text role
        timestamptz created_at
        uuid created_by FK
    }

    client_invitations {
        uuid id PK
        uuid client_id FK
        text email
        text role
        uuid invited_by FK
        timestamptz invited_at
        timestamptz accepted_at
        timestamptz expires_at
        text token UK
    }

    auth_users {
        uuid id PK
        text email
        timestamptz created_at
    }

    strategies {
        uuid id PK
        uuid client_id FK
        text name
        text description
        jsonb config
        integer version
        boolean active
        timestamptz created_at
        timestamptz updated_at
        uuid created_by FK
    }

    strategy_history {
        uuid id PK
        uuid strategy_id FK
        integer version
        jsonb config
        uuid changed_by FK
        timestamptz changed_at
        text change_summary
    }

    seo_rules {
        uuid id PK
        uuid client_id FK
        text rule_type
        jsonb rules
        integer priority
        boolean active
        timestamptz created_at
        timestamptz updated_at
    }

    content_preferences {
        uuid id PK
        uuid client_id FK
        text content_type
        jsonb preferences
        timestamptz created_at
        timestamptz updated_at
    }

    jobs {
        uuid id PK
        uuid client_id FK
        varchar content_type
        text prompt
        varchar llm_provider
        job_status status
        jsonb parameters
        jsonb result
        text error_message
        integer input_tokens
        integer output_tokens
        integer total_tokens
        decimal estimated_cost
        varchar provider_model
        timestamptz created_at
        timestamptz updated_at
        timestamptz started_at
        timestamptz completed_at
    }

    prompt_templates {
        uuid id PK
        uuid client_id FK
        text template_name
        text template_content
        text content_type
        boolean active
        timestamptz created_at
        timestamptz updated_at
    }

    prompt_jobs {
        uuid id PK
        job_status status
        jsonb params
        jsonb result
        text error
        text client_id
        uuid template_id FK
        timestamptz created_at
        timestamptz updated_at
    }

Indexes and Performance Optimizations¶

Primary Performance Indexes¶

Client Management¶

-- Clients table
CREATE INDEX idx_clients_parent_id ON clients(parent_id);
CREATE INDEX idx_clients_type ON clients(type);
CREATE INDEX idx_clients_active ON clients(active);
CREATE INDEX idx_clients_email ON clients(email);

-- API Keys
CREATE INDEX idx_api_keys_client_id ON api_keys(client_id);
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_active ON api_keys(is_active);

-- User-Client mapping
CREATE INDEX idx_user_clients_user_id ON user_clients(user_id);
CREATE INDEX idx_user_clients_client_id ON user_clients(client_id);

Content Production¶

-- Jobs table
CREATE INDEX idx_jobs_status ON jobs(status);
CREATE INDEX idx_jobs_client_id ON jobs(client_id);
CREATE INDEX idx_jobs_created_at ON jobs(created_at DESC);
CREATE INDEX idx_jobs_client_status ON jobs(client_id, status);
CREATE INDEX idx_jobs_provider_model ON jobs(llm_provider, provider_model);

Strategy Management¶

-- Strategies
CREATE INDEX idx_strategies_client_id ON strategies(client_id);
CREATE INDEX idx_strategies_active ON strategies(active);
CREATE INDEX idx_strategies_client_active ON strategies(client_id, active);

-- SEO Rules
CREATE INDEX idx_seo_rules_client ON seo_rules(client_id) WHERE active = true;
CREATE INDEX idx_seo_rules_type ON seo_rules(rule_type);

-- Content Preferences
CREATE INDEX idx_content_prefs_client ON content_preferences(client_id);

JSONB Performance Indexes¶

-- GIN indexes for JSONB columns
CREATE INDEX idx_clients_settings_gin ON clients USING GIN(settings);
CREATE INDEX idx_strategies_config_gin ON strategies USING GIN(config);
CREATE INDEX idx_seo_rules_rules_gin ON seo_rules USING GIN(rules);
CREATE INDEX idx_content_prefs_gin ON content_preferences USING GIN(preferences);
CREATE INDEX idx_jobs_parameters_gin ON jobs USING GIN(parameters);
CREATE INDEX idx_jobs_result_gin ON jobs USING GIN(result);

Row Level Security (RLS) Policies¶

Security Model¶

The database uses comprehensive RLS policies to enforce multi-tenancy and role-based access control.

Helper Functions¶

-- Get all client IDs accessible by current user (including hierarchy)
get_user_accessible_client_ids() RETURNS UUID[]

-- Check if user has specific role for a client
user_has_client_role(client_id UUID, required_role TEXT) RETURNS BOOLEAN

-- Get user's highest role for a client
get_user_client_role(client_id UUID) RETURNS TEXT

-- Check if client is accessible to current user
is_client_accessible(client_id UUID) RETURNS BOOLEAN

Core RLS Policies¶

Clients Table:

-- SELECT: Users can view accessible clients
CREATE POLICY clients_select_policy ON clients
    FOR SELECT USING (is_client_accessible(id));

-- INSERT: Only admins can create sub-clients
CREATE POLICY clients_insert_policy ON clients
    FOR INSERT WITH CHECK (
        parent_id IS NULL OR user_has_client_role(parent_id, 'admin')
    );

-- UPDATE: Only admins can update clients
CREATE POLICY clients_update_policy ON clients
    FOR UPDATE USING (user_has_client_role(id, 'admin'));

-- DELETE: Only owners can delete clients
CREATE POLICY clients_delete_policy ON clients
    FOR DELETE USING (user_has_client_role(id, 'owner'));

Jobs Table:

-- SELECT: Users can view jobs for accessible clients
CREATE POLICY jobs_select_policy ON jobs
    FOR SELECT USING (is_client_accessible(client_id));

-- INSERT: Members and above can create jobs
CREATE POLICY jobs_insert_policy ON jobs
    FOR INSERT WITH CHECK (user_has_client_role(client_id, 'member'));

Database Functions and Triggers¶

Automatic Timestamp Management¶

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();

    -- Special handling for jobs table
    IF TG_TABLE_NAME = 'jobs' THEN
        -- Set started_at when status changes to in_progress
        IF OLD.status != 'in_progress' AND NEW.status = 'in_progress' THEN
            NEW.started_at = NOW();
        END IF;

        -- Set completed_at when status changes to completed or failed
        IF OLD.status NOT IN ('completed', 'failed') 
           AND NEW.status IN ('completed', 'failed') THEN
            NEW.completed_at = NOW();
        END IF;
    END IF;

    RETURN NEW;
END;
$$ language 'plpgsql';

Invitation Management Functions¶

-- Check if invitation token is valid
CREATE OR REPLACE FUNCTION is_invitation_valid(invitation_token TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM client_invitations
        WHERE token = invitation_token
        AND accepted_at IS NULL
        AND expires_at > NOW()
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Accept invitation and create user-client mapping
CREATE OR REPLACE FUNCTION accept_invitation(
    invitation_token TEXT, 
    accepting_user_id UUID
)
RETURNS BOOLEAN AS $$
-- Function implementation handles invitation acceptance logic
$$ LANGUAGE plpgsql SECURITY DEFINER;

Sample Data and Seed Scripts¶

Development Seed Data¶

The database includes comprehensive seed data for development and testing:

Sample Client Hierarchy¶

-- Root organization
INSERT INTO clients (id, name, type, settings) VALUES
('11111111-1111-1111-1111-111111111111', 'HeavyGood Global', 'root', 
 '{"industry": "technology", "timezone": "America/New_York"}');

-- Agency level
INSERT INTO clients (id, parent_id, name, type) VALUES
('22222222-2222-2222-2222-222222222222', 
 '11111111-1111-1111-1111-111111111111', 
 'HeavyGood Marketing', 'agency');

-- Customer level
INSERT INTO clients (id, parent_id, name, type, settings) VALUES
('44444444-4444-4444-4444-444444444444',
 '22222222-2222-2222-2222-222222222222',
 'PASCO Scientific', 'customer',
 '{"industry": "education", "content_focus": "science education"}');

Sample Strategies and Rules¶

-- Science education strategy
INSERT INTO strategies (client_id, name, config) VALUES
('44444444-4444-4444-4444-444444444444',
 'PASCO Education Content Strategy',
 '{
   "content_guidelines": {
     "min_words": 800,
     "max_words": 2000,
     "tone": "educational",
     "include_examples": true
   },
   "brand_voice": {
     "key_messages": ["hands-on learning", "scientific inquiry"],
     "unique_value": "Making science accessible and exciting"
   }
 }');

-- SEO rules for PASCO
INSERT INTO seo_rules (client_id, rule_type, rules) VALUES
('44444444-4444-4444-4444-444444444444', 'keywords',
 '{"keywords": ["science education", "STEM learning", "lab equipment"], 
   "weight": 0.9}');

Migration Strategy and Versioning¶

Migration File Organization¶

/supabase/migrations/
├── 20250729211111_create_jobs_table.sql
├── 20250729211116_create_auth_tables.sql
└── 20250801000000_create_strategies_table.sql

/smm/sql/migrations/
├── 000_run_all_migrations.sql
├── 001_create_core_tables.sql
├── 002_create_indexes.sql
├── 003_create_user_clients_table.sql
├── 004_enable_rls_and_helpers.sql
├── 005_create_rls_policies.sql
├── 006_add_missing_constraints.sql
├── 007_restructure_to_client_centric.sql
└── 008_create_prompt_templates.sql

/sql/migrations/
├── 001_create_prompt_templates_table.sql
├── 002_create_prompt_jobs_table.sql
└── 003_create_performance_indexes.sql

Migration Execution Order¶

Supabase Core Migrations (Primary infrastructure)
Jobs table and authentication
Basic client management
Strategy foundations
SMM Detailed Migrations (Strategy management)
Core table structure
Performance indexes
Multi-tenancy setup
RLS policies and security
Client-centric restructuring
IM Module Migrations (Instruction management)
Prompt templates
Prompt jobs
Performance optimizations

Version Control Best Practices¶

Sequential Numbering: Use timestamp-based numbering for Supabase migrations
Descriptive Names: Clear, descriptive migration file names
Dependency Tracking: Each migration lists its dependencies
Rollback Procedures: Include rollback instructions where applicable
Testing: All migrations tested in development before production

Database Backup Strategy¶

-- Pre-migration backup
pg_dump --host=db.xxx.supabase.co --port=5432 --username=postgres 
        --dbname=postgres --schema=public --data-only 
        --file=backup_$(date +%Y%m%d_%H%M%S).sql

-- Post-migration verification
SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema = 'public';

Performance Monitoring and Analytics¶

Key Performance Metrics¶

Query Performance Monitoring¶

-- Check slow queries
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC;

-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_tup_read DESC;

Business Metrics¶

-- Job processing analytics
SELECT 
    DATE(created_at) as date,
    status,
    COUNT(*) as job_count,
    AVG(estimated_cost) as avg_cost
FROM jobs
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at), status
ORDER BY date DESC;

-- Client activity metrics
SELECT 
    c.name,
    COUNT(j.id) as total_jobs,
    SUM(j.estimated_cost) as total_cost,
    AVG(j.total_tokens) as avg_tokens
FROM clients c
LEFT JOIN jobs j ON c.id = j.client_id
WHERE j.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.id, c.name
ORDER BY total_cost DESC;

Security Considerations¶

Data Protection¶

Encryption: All sensitive data encrypted at rest and in transit
API Keys: Stored as bcrypt hashes, never plaintext
RLS: Complete row-level security implementation
Audit Trail: Strategy history and invitation tracking

Access Control¶

Role Hierarchy: owner > admin > member > viewer
Client Hierarchy: Parent access includes child access
Function Security: All helper functions use SECURITY DEFINER
Token Management: Secure invitation token generation

Compliance Features¶

Data Retention: Built-in data lifecycle management
Audit Logging: Complete change tracking
User Management: Comprehensive invitation system
Rate Limiting: Per-key and per-client limits

Troubleshooting Guide¶

Common Issues¶

RLS Policy Debugging¶

-- Check user's accessible clients
SELECT * FROM get_user_accessible_client_ids();

-- Verify user role for specific client
SELECT get_user_client_role('client-uuid-here');

-- Test policy directly
SET ROLE authenticated;
SELECT * FROM clients; -- Should only show accessible clients

Performance Issues¶

-- Identify missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public' AND n_distinct > 100;

-- Check table bloat
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Data Integrity Issues¶

-- Verify foreign key constraints
SELECT tc.table_name, tc.constraint_name, tc.constraint_type, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public';

-- Check for orphaned records
SELECT 'clients' as table_name, COUNT(*) as orphaned_count
FROM clients c
LEFT JOIN user_clients uc ON c.id = uc.client_id
WHERE uc.client_id IS NULL AND c.type != 'root';

Conclusion¶

This database schema provides a robust, scalable foundation for the HeavyGood Content Platform with:

Multi-tenancy: Complete client isolation with hierarchical access
Performance: Comprehensive indexing and query optimization
Security: Row-level security and role-based access control
Flexibility: JSONB columns for extensible configuration
Auditability: Complete change tracking and history
Scalability: Designed to handle enterprise-level workloads

The schema supports the platform's core business requirements while maintaining flexibility for future enhancements and integrations.