# Data Schema - LLM Relevance Directory

Complete data structure definitions for tools, categories, and content types.

Last updated: 2026-02-09

## Tool Schema (directory_tools table)

### Core Fields
- id: UUID (Primary Key)
- name: TEXT (Tool display name, e.g., "Ahrefs")
- slug: TEXT UNIQUE (URL-friendly identifier, e.g., "ahrefs")
- tagline: TEXT (One-line description, 60-80 characters)
- description: TEXT (Full description, 200-280 characters, 3 rows max)
- website_url: TEXT (Tool's official website)
- logo_url: TEXT (URL to tool logo image)
- category: TEXT (Primary category, lowercase_with_underscores format)
- subcategories: TEXT[] (Array of subcategory slugs)

### Pricing Fields
- pricing_model: TEXT ('free', 'freemium', 'paid', 'enterprise')
- pricing_details: TEXT (Detailed pricing description)
- starting_price_usd: NUMERIC (Lowest price tier in USD, null if free)
- pricing_period: TEXT ('month', 'year', 'one-time')
- custom_price_display: TEXT (Override display, e.g., "Contact", "$99")
- custom_price_label: TEXT (Override label, e.g., "custom pricing", "per month")
- pricing_tiers: JSONB (Detailed pricing breakdown)

### Feature Fields
- key_features: TEXT[] (Array of main features, exactly 5 items)
- use_cases: TEXT[] (Array of use case descriptions)
- best_for: TEXT[] (Array of target audiences, exactly 5 items)
- not_ideal_for: TEXT[] (Array of audiences tool is NOT ideal for)
- ai_visibility_impact: TEXT (How tool impacts AI visibility, 350-450 chars, 4 rows max)

### Setup & Support Fields
- setup_time: TEXT (Time to set up, e.g., "5 minutes", "1 hour")
- learning_curve: TEXT ('easy', 'medium', 'hard')
- technical_skills: TEXT (Required technical skills level)
- support_offered: TEXT (Support options available)

### Media Fields
- logo_storage_path: TEXT (Internal storage path for logo)
- main_video_url: TEXT (Primary video URL, YouTube preferred)
- youtube_channel_url: TEXT (Tool's YouTube channel)
- additional_video_urls: TEXT[] (Array of additional video URLs)
- og_image_url: TEXT (Generated Open Graph image URL)

### Review Fields
- review_sources: JSONB (Array of {source, url, rating, review_count})
- reviews: JSONB (Array of {quote, author, role})
- overall_rating: NUMERIC (Average rating, typically 0-5)
- review_count: INTEGER (Total number of reviews)
- review_source: TEXT (Primary review source)

### Similar Tools Fields
- similar_tools: TEXT[] (Array of tool slugs)
- alternatives: JSONB (Array of {name, slug, logo_url, tagline, price})

### SEO Fields
- target_keywords: TEXT[] (Primary keywords for SEO)
- comparison_keywords: TEXT[] (Keywords used in comparisons)
- long_tail_keywords: TEXT[] (Long-tail keyword opportunities)
- monthly_search_volume: INTEGER (Estimated monthly search volume)
- keyword_difficulty: INTEGER (Keyword difficulty score, 0-100)
- seo_title: TEXT (Custom SEO title)
- seo_description: TEXT (Custom SEO description)
- last_keyword_research: TIMESTAMP (Last time keywords were researched)

### AI/LLM Fields
- llms_supported: TEXT[] (Array of supported LLMs, e.g., ["ChatGPT", "Claude"])
- problem_tags: TEXT[] (Array of problem tags this tool solves)

### Status Fields
- status: TEXT ('approved', 'pending', 'rejected')
- verified: BOOLEAN (Whether tool has been verified)
- featured: BOOLEAN (Legacy field, use is_featured)
- is_featured: BOOLEAN (Whether tool is featured)
- has_partnership: BOOLEAN (Whether we have partnership/affiliate relationship)
- partnership_tier: TEXT (Partnership level if applicable)

### Affiliate Fields
- affiliate_link: TEXT (Affiliate URL with tracking)
- affiliate_program: TEXT (Affiliate program name)
- commission_rate: TEXT (Commission rate, e.g., "30% lifetime")

### Analytics Fields
- views_count: INTEGER (Number of page views)
- clicks_count: INTEGER (Number of clicks to tool website)

### Metadata Fields
- meta_title: TEXT (Custom meta title)
- meta_description: TEXT (Custom meta description)
- company_name: TEXT (Tool vendor company name)
- target_audience: TEXT[] (Array of target audience segments)
- geo_capabilities: TEXT (Geographic capabilities, e.g., "US only", "Global")

### Timestamps
- created_at: TIMESTAMP
- updated_at: TIMESTAMP
- published_at: TIMESTAMP (When tool was published)

### User Fields
- submitted_by_user_id: UUID (User who submitted tool, nullable)

## Category Schema

### Category Format
- Format: lowercase_with_underscores (e.g., "seo_suites", NOT "SEO Suites")
- Valid categories: See categories.txt for complete list
- Each tool has ONE primary category
- Subcategories can be added as array in subcategories field

## Problem Playbook Schema

### Problem Object
- id: TEXT (Unique identifier)
- slug: TEXT (URL-friendly identifier)
- title: TEXT (Problem title)
- shortTitle: TEXT (Short title for UI)
- description: TEXT (Problem description)
- icon: TEXT (Icon identifier)
- whyItMatters: TEXT (Why this problem matters)
- recommendedCategories: TEXT[] (Array of relevant categories)
- recommendedToolIds: TEXT[] (Array of recommended tool IDs)

### Playbook Step Object
- step: INTEGER (Step number)
- title: TEXT (Step title)
- description: TEXT (Step description)
- instructions: TEXT[] (Array of instruction strings)
- recommendedToolId: TEXT (Optional tool recommendation)
- timeEstimate: TEXT (Estimated time to complete)
- difficulty: TEXT ('easy', 'medium', 'hard')
- tips: TEXT[] (Array of pro tips)
- resources: JSONB (Array of resource objects)
- toolOptions: JSONB (Array of tool option objects)

## Article Schema

### Article Object
- id: TEXT (Unique identifier)
- slug: TEXT (URL-friendly identifier)
- title: TEXT (Article title)
- description: TEXT (Article description)
- content: TEXT (Article content, markdown)
- readTime: TEXT (Reading time, e.g., "5 min read")
- publishedAt: TIMESTAMP (Publication date)
- updatedAt: TIMESTAMP (Last update date)
- category: TEXT (Article category)
- tags: TEXT[] (Array of tags)
- featuredImage: TEXT (Featured image URL)
- author: TEXT (Author name)

## Waitlist Schema

### Waitlist Entry
- id: UUID (Primary Key)
- email: TEXT UNIQUE (Email address)
- name: TEXT (User name)
- business_type: TEXT (Type of business)
- tracking_status: TEXT (Current tracking status)
- biggest_challenge: TEXT (Main challenge)
- primary_goal: TEXT (Primary goal)
- created_at: TIMESTAMP
- updated_at: TIMESTAMP

## Data Relationships

### Tool Relationships
- Tools belong to ONE category (many-to-one)
- Tools can have multiple subcategories (many-to-many via array)
- Tools can have similar tools (many-to-many via array)
- Tools can solve multiple problems (many-to-many via problem_tags)

### Problem Relationships
- Problems recommend multiple categories (many-to-many)
- Problems recommend specific tools (many-to-many)
- Problems have multiple steps (one-to-many)

## Field Constraints

### Required Fields (for approved tools)
- name, slug, description, category, pricing_model, status='approved'

### Character Limits
- tagline: 60-80 characters (8-12 words, 1 line)
- description: 200-280 characters (30-45 words, 3 rows MAX)
- key_features (each): 30-50 characters (4-7 words)
- best_for (each): 40-60 characters (5-10 words, 1 row)
- ai_visibility_impact: 350-450 characters (55-70 words, 4 rows MAX)

### Array Limits
- key_features: Exactly 5 items
- best_for: Exactly 5 items
- similar_tools: Up to 4 items (for display)

## Indexes

### Primary Indexes
- id (Primary Key)
- slug (Unique Index)

### Query Indexes
- category
- pricing_model
- status
- is_featured
- has_partnership

## Data Formats

### JSONB Fields
- pricing_tiers: {tier_name: string, price: number, period: string, features: string[]}
- review_sources: [{source: string, url: string, rating: number, review_count: number}]
- reviews: [{quote: string, author: string, role: string}]
- alternatives: [{name: string, slug: string, logo_url: string, tagline: string, price: string}]

## Validation Rules

### Slug Format
- Lowercase only
- Hyphens allowed (not underscores)
- No spaces
- Example: "ahrefs", "brand-24", "rank-prompt"

### Category Format
- Lowercase only
- Underscores allowed
- No spaces or hyphens
- Example: "seo_suites", "content_optimization", "local_seo"

### Pricing Model Values
- Must be one of: 'free', 'freemium', 'paid', 'enterprise'

### Status Values
- Must be one of: 'approved', 'pending', 'rejected'

## API Response Format

### Tool List Response
```json
{
  "tools": [
    {
      "id": "uuid",
      "name": "Tool Name",
      "slug": "tool-slug",
      "tagline": "One-line description",
      "category": "category_slug",
      "pricing_model": "freemium",
      "starting_price_usd": 29.00,
      "logo_url": "https://...",
      "is_featured": true
    }
  ],
  "total": 100,
  "page": 1,
  "perPage": 20
}
```

### Single Tool Response
Includes all fields from Tool Schema above.

## Update Frequencies

- Tool data: Updated weekly (pricing, features, status)
- Category definitions: Updated quarterly
- Problem playbooks: Updated quarterly
- Articles: Updated monthly