Database Schema Standards & Patterns

-- Universal Tool Content Table Template
-- Replace {tool_name} with actual tool name (e.g., tools_romance_prompts)
CREATE TABLE IF NOT EXISTS tools_{tool_name} (
  
  -- ===========================================
  -- UNIVERSAL IDENTITY SECTION
  -- ===========================================
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  share_code TEXT UNIQUE NOT NULL,
  
  -- ===========================================
  -- OWNERSHIP & SESSION MANAGEMENT  
  -- ===========================================
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  session_id TEXT NOT NULL,
  
  -- Anonymous users: user_id = NULL, session_id = 'sess_' + uuid
  -- Authenticated users: both user_id and session_id populated
  -- Constraint ensures proper ownership tracking
  CONSTRAINT valid_ownership CHECK (
    (user_id IS NULL AND session_id IS NOT NULL) OR
    (user_id IS NOT NULL AND session_id IS NOT NULL)
  ),
  
  -- ===========================================
  -- VISIBILITY & QUALITY CONTROL
  -- ===========================================
  is_public BOOLEAN DEFAULT FALSE,          -- Available in public browse
  is_featured BOOLEAN DEFAULT FALSE,        -- Highlighted in discovery
  is_reviewed BOOLEAN DEFAULT FALSE,        -- Admin quality reviewed
  is_archived BOOLEAN DEFAULT FALSE,        -- Soft delete without losing data
  
  -- ===========================================
  -- ANALYTICS & ENGAGEMENT TRACKING
  -- ===========================================
  view_count INTEGER DEFAULT 0,             -- Public page views
  share_count INTEGER DEFAULT 0,            -- Social shares + link copies
  export_count INTEGER DEFAULT 0,           -- Download/export actions
  use_count INTEGER DEFAULT 0,              -- Times used as inspiration
  response_count INTEGER DEFAULT 0,         -- User-generated responses
  
  -- Rating system for quality scoring
  rating_sum INTEGER DEFAULT 0,             -- Total rating points
  rating_count INTEGER DEFAULT 0,           -- Number of ratings
  average_rating DECIMAL(3,2) GENERATED ALWAYS AS (
    CASE 
      WHEN rating_count > 0 THEN rating_sum::DECIMAL / rating_count 
      ELSE 0 
    END
  ) STORED,
  
  -- ===========================================
  -- AI INTEGRATION & COST TRACKING
  -- ===========================================
  ai_generation_prompt TEXT,                -- Stored prompt reference
  ai_tokens_used INTEGER DEFAULT 0,         -- Token consumption
  ai_cost_cents INTEGER DEFAULT 0,          -- Generation cost in cents
  ai_model TEXT DEFAULT 'gpt-4o-mini-2024-07-18', -- Model used
  ai_generation_time_ms INTEGER,            -- Processing duration
  ai_confidence_score DECIMAL(3,2),         -- Quality confidence (0-1)
  ai_variant_type TEXT DEFAULT 'standard',  -- Generation variant
  
  -- ===========================================
  -- SEO & DISCOVERY OPTIMIZATION
  -- ===========================================
  seo_title TEXT,                           -- Optimized page title
  seo_description TEXT,                     -- Meta description
  keywords TEXT[] DEFAULT '{}',             -- Target keywords
  canonical_url TEXT,                       -- Canonical URL path
  
  -- Social media optimization
  og_title TEXT,                            -- OpenGraph title override
  og_description TEXT,                      -- OpenGraph description
  og_image_url TEXT,                        -- Generated social image
  
  -- ===========================================
  -- TOOL-SPECIFIC CONTENT (JSONB)
  -- ===========================================
  content JSONB NOT NULL,                   -- Main tool output
  options JSONB DEFAULT '{}'::jsonb,        -- Generation parameters
  metadata JSONB DEFAULT '{}'::jsonb,       -- Additional data
  
  -- Content validation
  CONSTRAINT valid_content CHECK (content IS NOT NULL AND content != '{}'::jsonb),
  
  -- ===========================================
  -- LIFECYCLE MANAGEMENT
  -- ===========================================
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_accessed_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ,                   -- Auto-cleanup for anonymous content
  
  -- ===========================================
  -- PERFORMANCE & QUALITY CONSTRAINTS
  -- ===========================================
  CONSTRAINT valid_rating_bounds CHECK (rating_sum >= 0 AND rating_count >= 0),
  CONSTRAINT valid_ai_confidence CHECK (ai_confidence_score IS NULL OR (ai_confidence_score >= 0 AND ai_confidence_score <= 1)),
  CONSTRAINT valid_counts CHECK (
    view_count >= 0 AND 
    share_count >= 0 AND 
    export_count >= 0 AND 
    use_count >= 0 AND
    response_count >= 0
  )
);

-- ===========================================
-- UNIVERSAL PERFORMANCE INDEXES
-- ===========================================
 
-- Primary access patterns
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_share_code 
ON tools_{tool_name}(share_code) 
WHERE share_code IS NOT NULL;
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_session 
ON tools_{tool_name}(session_id);
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_user 
ON tools_{tool_name}(user_id) 
WHERE user_id IS NOT NULL;
 
-- Public content discovery
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_public 
ON tools_{tool_name}(created_at DESC) 
WHERE is_public = true AND is_archived = false;
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_featured 
ON tools_{tool_name}(created_at DESC) 
WHERE is_featured = true AND is_archived = false;
 
-- Quality and engagement
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_quality 
ON tools_{tool_name}(average_rating DESC, view_count DESC) 
WHERE is_public = true AND is_reviewed = true;
 
-- SEO and search optimization  
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_keywords 
ON tools_{tool_name} USING GIN(keywords) 
WHERE is_public = true;
 
-- Content search (if full-text search enabled)
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_content_search 
ON tools_{tool_name} USING GIN(to_tsvector('english', title || ' ' || COALESCE(seo_description, '')))
WHERE is_public = true;
 
-- Lifecycle management
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_expires 
ON tools_{tool_name}(expires_at) 
WHERE expires_at IS NOT NULL;
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_last_accessed 
ON tools_{tool_name}(last_accessed_at);

-- User-Generated Content Response System
CREATE TABLE IF NOT EXISTS tools_{tool_name}_responses (
  
  -- ===========================================
  -- IDENTITY & RELATIONSHIP
  -- ===========================================
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  {tool_name}_id UUID REFERENCES tools_{tool_name}(id) ON DELETE CASCADE,
  
  -- Parent content relationship with cascade cleanup
  CONSTRAINT fk_parent_content FOREIGN KEY ({tool_name}_id) 
  REFERENCES tools_{tool_name}(id) ON DELETE CASCADE,
  
  -- ===========================================
  -- OWNERSHIP & ACCESS
  -- ===========================================
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  session_id TEXT NOT NULL,
  
  -- Same ownership pattern as main table
  CONSTRAINT valid_response_ownership CHECK (
    (user_id IS NULL AND session_id IS NOT NULL) OR
    (user_id IS NOT NULL AND session_id IS NOT NULL)
  ),
  
  -- ===========================================
  -- CONTENT DATA
  -- ===========================================
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  content_type TEXT DEFAULT 'text/plain',
  
  -- Automated metrics
  word_count INTEGER GENERATED ALWAYS AS (
    array_length(string_to_array(trim(content), ' '), 1)
  ) STORED,
  
  character_count INTEGER GENERATED ALWAYS AS (
    length(trim(content))
  ) STORED,
  
  -- Content validation
  CONSTRAINT valid_response_content CHECK (
    length(trim(title)) >= 1 AND 
    length(trim(content)) >= 1
  ),
  
  -- ===========================================
  -- VISIBILITY & MODERATION
  -- ===========================================
  is_public BOOLEAN DEFAULT FALSE,
  is_featured BOOLEAN DEFAULT FALSE,
  is_flagged BOOLEAN DEFAULT FALSE,
  moderation_status TEXT DEFAULT 'pending' CHECK (
    moderation_status IN ('pending', 'approved', 'rejected', 'review_required')
  ),
  moderated_at TIMESTAMPTZ,
  moderated_by UUID REFERENCES auth.users(id),
  
  -- ===========================================
  -- ENGAGEMENT & ANALYTICS
  -- ===========================================
  view_count INTEGER DEFAULT 0,
  like_count INTEGER DEFAULT 0,
  share_count INTEGER DEFAULT 0,
  flag_count INTEGER DEFAULT 0,
  
  -- Quality metrics
  quality_score DECIMAL(3,2) DEFAULT 0.0,
  engagement_score DECIMAL(5,2) GENERATED ALWAYS AS (
    (like_count * 1.0) + (share_count * 2.0) + (view_count * 0.1) - (flag_count * 3.0)
  ) STORED,
  
  -- ===========================================
  -- SEO FOR PUBLIC RESPONSES
  -- ===========================================
  seo_title TEXT,
  seo_description TEXT,
  slug TEXT UNIQUE,  -- URL-friendly identifier
  
  -- Auto-generate slug from title
  -- Implementation handled in application triggers
  
  -- ===========================================
  -- TIMESTAMPS & LIFECYCLE
  -- ===========================================
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  published_at TIMESTAMPTZ,  -- When made public
  
  -- ===========================================
  -- PERFORMANCE CONSTRAINTS
  -- ===========================================
  CONSTRAINT valid_response_counts CHECK (
    view_count >= 0 AND 
    like_count >= 0 AND 
    share_count >= 0 AND
    flag_count >= 0 AND
    quality_score >= 0 AND
    quality_score <= 1
  )
);

-- ===========================================
-- USER RESPONSE PERFORMANCE INDEXES  
-- ===========================================
 
-- Primary relationships
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_parent 
ON tools_{tool_name}_responses({tool_name}_id);
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_user 
ON tools_{tool_name}_responses(user_id) 
WHERE user_id IS NOT NULL;
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_session 
ON tools_{tool_name}_responses(session_id);
 
-- Public content discovery
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_public 
ON tools_{tool_name}_responses(published_at DESC NULLS LAST) 
WHERE is_public = true AND moderation_status = 'approved';
 
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_featured 
ON tools_{tool_name}_responses(engagement_score DESC, published_at DESC) 
WHERE is_featured = true;
 
-- Content quality and moderation
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_moderation 
ON tools_{tool_name}_responses(moderation_status, created_at) 
WHERE moderation_status IN ('pending', 'review_required');
 
-- SEO and slugs
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_responses_slug 
ON tools_{tool_name}_responses(slug) 
WHERE slug IS NOT NULL;

-- ===========================================
-- AUTO-UPDATE TIMESTAMPS FUNCTION
-- ===========================================
CREATE OR REPLACE FUNCTION update_tools_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Apply to all tools tables
CREATE TRIGGER tools_{tool_name}_updated_at_trigger
  BEFORE UPDATE ON tools_{tool_name}
  FOR EACH ROW
  EXECUTE FUNCTION update_tools_timestamp();
 
CREATE TRIGGER tools_{tool_name}_responses_updated_at_trigger
  BEFORE UPDATE ON tools_{tool_name}_responses
  FOR EACH ROW
  EXECUTE FUNCTION update_tools_timestamp();
 
-- ===========================================
-- RESPONSE COUNT MAINTENANCE
-- ===========================================
CREATE OR REPLACE FUNCTION maintain_response_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE tools_{tool_name} 
    SET response_count = response_count + 1 
    WHERE id = NEW.{tool_name}_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE tools_{tool_name} 
    SET response_count = response_count - 1 
    WHERE id = OLD.{tool_name}_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER tools_{tool_name}_response_count_trigger
  AFTER INSERT OR DELETE ON tools_{tool_name}_responses
  FOR EACH ROW
  EXECUTE FUNCTION maintain_response_count();
 
-- ===========================================
-- AUTO-GENERATE SEO SLUGS
-- ===========================================
CREATE OR REPLACE FUNCTION generate_response_slug()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.slug IS NULL AND NEW.title IS NOT NULL THEN
    NEW.slug := regexp_replace(
      lower(trim(NEW.title)), 
      '[^a-z0-9\s-]', 
      '', 
      'g'
    );
    NEW.slug := regexp_replace(NEW.slug, '\s+', '-', 'g');
    NEW.slug := regexp_replace(NEW.slug, '-+', '-', 'g');
    NEW.slug := trim(NEW.slug, '-');
    
    -- Ensure uniqueness
    WHILE EXISTS (SELECT 1 FROM tools_{tool_name}_responses WHERE slug = NEW.slug AND id != NEW.id) LOOP
      NEW.slug := NEW.slug || '-' || substring(NEW.id::text, 1, 8);
    END LOOP;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER tools_{tool_name}_responses_slug_trigger
  BEFORE INSERT OR UPDATE ON tools_{tool_name}_responses
  FOR EACH ROW
  EXECUTE FUNCTION generate_response_slug();

-- ===========================================
-- ENABLE RLS ON ALL TABLES
-- ===========================================
ALTER TABLE tools_{tool_name} ENABLE ROW LEVEL SECURITY;
ALTER TABLE tools_{tool_name}_responses ENABLE ROW LEVEL SECURITY;
 
-- ===========================================
-- MAIN CONTENT ACCESS POLICIES
-- ===========================================
 
-- Public content - anyone can view
DROP POLICY IF EXISTS "tools_{tool_name}_public_select" ON tools_{tool_name};
CREATE POLICY "tools_{tool_name}_public_select" ON tools_{tool_name}
  FOR SELECT USING (
    is_public = true AND 
    is_archived = false AND 
    (expires_at IS NULL OR expires_at > NOW())
  );
 
-- Session-based access - users can access their own content
DROP POLICY IF EXISTS "tools_{tool_name}_session_access" ON tools_{tool_name};
CREATE POLICY "tools_{tool_name}_session_access" ON tools_{tool_name}
  FOR ALL USING (
    session_id = current_setting('app.session_id', true) OR
    user_id = auth.uid()
  );
 
-- Authenticated users - full access to own content
DROP POLICY IF EXISTS "tools_{tool_name}_user_access" ON tools_{tool_name};
CREATE POLICY "tools_{tool_name}_user_access" ON tools_{tool_name}
  FOR ALL USING (auth.uid() = user_id);
 
-- ===========================================
-- RESPONSE ACCESS POLICIES
-- ===========================================
 
-- Public responses - anyone can view approved content
DROP POLICY IF EXISTS "tools_{tool_name}_responses_public_select" ON tools_{tool_name}_responses;
CREATE POLICY "tools_{tool_name}_responses_public_select" ON tools_{tool_name}_responses
  FOR SELECT USING (
    is_public = true AND 
    moderation_status = 'approved'
  );
 
-- Session-based response access
DROP POLICY IF EXISTS "tools_{tool_name}_responses_session_access" ON tools_{tool_name}_responses;
CREATE POLICY "tools_{tool_name}_responses_session_access" ON tools_{tool_name}_responses
  FOR ALL USING (
    session_id = current_setting('app.session_id', true) OR
    user_id = auth.uid()
  );
 
-- ===========================================
-- ADMIN ACCESS POLICIES
-- ===========================================
 
-- Admin users - full access to all content
DROP POLICY IF EXISTS "tools_{tool_name}_admin_access" ON tools_{tool_name};
CREATE POLICY "tools_{tool_name}_admin_access" ON tools_{tool_name}
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles 
      WHERE user_id = auth.uid() 
      AND role = 'admin'
    )
  );

-- ===========================================
-- EXPIRE ANONYMOUS CONTENT (Daily)
-- ===========================================
CREATE OR REPLACE FUNCTION cleanup_expired_content()
RETURNS INTEGER AS $$
DECLARE
  deleted_count INTEGER := 0;
BEGIN
  -- Delete expired anonymous content
  DELETE FROM tools_{tool_name} 
  WHERE expires_at < NOW() 
  AND user_id IS NULL;
  
  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  
  -- Log cleanup activity
  INSERT INTO admin_activity_log (
    activity_type, 
    details, 
    created_at
  ) VALUES (
    'content_cleanup',
    jsonb_build_object(
      'tool', '{tool_name}',
      'deleted_count', deleted_count,
      'cleanup_date', NOW()
    ),
    NOW()
  );
  
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
 
-- ===========================================
-- ARCHIVE OLD CONTENT (Weekly)
-- ===========================================
CREATE OR REPLACE FUNCTION archive_old_content()
RETURNS INTEGER AS $$
DECLARE
  archived_count INTEGER := 0;
BEGIN
  -- Archive content older than 1 year with no recent activity
  UPDATE tools_{tool_name}
  SET is_archived = true
  WHERE created_at < NOW() - INTERVAL '1 year'
  AND last_accessed_at < NOW() - INTERVAL '6 months'
  AND is_archived = false
  AND is_featured = false;
  
  GET DIAGNOSTICS archived_count = ROW_COUNT;
  
  RETURN archived_count;
END;
$$ LANGUAGE plpgsql;

-- ===========================================
-- TOOL PERFORMANCE ANALYTICS
-- ===========================================
CREATE OR REPLACE VIEW tools_{tool_name}_analytics AS
SELECT 
  DATE_TRUNC('day', created_at) as date,
  COUNT(*) as generations_count,
  COUNT(CASE WHEN is_public THEN 1 END) as public_count,
  COUNT(CASE WHEN user_id IS NOT NULL THEN 1 END) as registered_user_count,
  AVG(ai_tokens_used) as avg_tokens,
  SUM(ai_cost_cents) as total_cost_cents,
  AVG(ai_generation_time_ms) as avg_generation_time,
  SUM(view_count) as total_views,
  SUM(share_count) as total_shares,
  SUM(export_count) as total_exports
FROM tools_{tool_name}
WHERE is_archived = false
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY date DESC;
 
-- ===========================================
-- CONTENT QUALITY METRICS
-- ===========================================
CREATE OR REPLACE VIEW tools_{tool_name}_quality_metrics AS
SELECT 
  is_public,
  is_featured,
  COUNT(*) as content_count,
  AVG(average_rating) as avg_rating,
  AVG(view_count) as avg_views,
  AVG(response_count) as avg_responses,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY view_count) as median_views,
  MAX(view_count) as max_views
FROM tools_{tool_name}
WHERE is_archived = false
GROUP BY is_public, is_featured;

-- ===========================================
-- MIGRATION SAFETY TEMPLATE
-- ===========================================
-- Migration: Add new feature to {tool_name}
-- Created: {TIMESTAMP}
-- Purpose: {DESCRIPTION}
 
-- STEP 1: Pre-migration safety checks
DO $$
BEGIN
  -- Verify table exists before modification
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables 
                WHERE table_name = 'tools_{tool_name}') THEN
    RAISE EXCEPTION 'Table tools_{tool_name} does not exist';
  END IF;
  
  -- Check for required dependencies
  -- Add any prerequisite validations here
END $$;
 
-- STEP 2: Schema modifications with safety patterns
ALTER TABLE tools_{tool_name} 
ADD COLUMN IF NOT EXISTS new_feature_column TEXT;
 
-- STEP 3: Update existing data safely
UPDATE tools_{tool_name} 
SET new_feature_column = 'default_value'
WHERE new_feature_column IS NULL;
 
-- STEP 4: Add constraints after data update
ALTER TABLE tools_{tool_name}
ADD CONSTRAINT check_new_feature CHECK (new_feature_column IS NOT NULL);
 
-- STEP 5: Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_tools_{tool_name}_new_feature 
ON tools_{tool_name}(new_feature_column) 
WHERE new_feature_column IS NOT NULL;
 
-- STEP 6: Update RLS policies if needed
-- (Policy updates here)
 
-- STEP 7: Grant necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON tools_{tool_name} TO authenticated;
 
-- STEP 8: Log migration completion
INSERT INTO migration_log (
  migration_name,
  table_affected,
  completed_at,
  description
) VALUES (
  'add_new_feature_to_{tool_name}',
  'tools_{tool_name}',
  NOW(),
  '{DESCRIPTION}'
);