WEB_SEO_DIRECT_QUERIES.md

Web SEO Direct Table Queries

This document outlines the direct table queries used in the Web SEO dashboard frontend, replacing RPC functions with Supabase query builder calls.

Updated Implementation

All hooks now use direct table queries instead of RPC functions, making the code more maintainable and easier to debug.

1. useWebSeoOverview(websiteId: string)

Fetches overview metrics for a specific website by querying multiple tables directly.

Queries:

• web_keyword_ranks - for keywords count
• web_page_metrics - for traffic sum
• web_audit_summary - for audit score
• web_backlink_summary - for referring domains

Implementation:

// Get latest dates for each table
const [kwDateResult, pagesDateResult, blDateResult, auditDateResult] = await Promise.all([
  supabase.from('web_keyword_ranks').select('date').eq('website_id', websiteId).order('date', { ascending: false }).limit(1),
  supabase.from('web_page_metrics').select('date').eq('website_id', websiteId).order('date', { ascending: false }).limit(1),
  supabase.from('web_backlink_summary').select('date').eq('website_id', websiteId).order('date', { ascending: false }).limit(1),
  supabase.from('web_audit_summary').select('date').eq('website_id', websiteId).order('date', { ascending: false }).limit(1)
]);

// Get data for each metric using the latest dates
const [keywordsResult, trafficResult, auditResult, backlinksResult] = await Promise.all([
  // Keywords count
  kwDate ? supabase.from('web_keyword_ranks').select('id', { count: 'exact' }).eq('website_id', websiteId).eq('date', kwDate) : { data: [], count: 0 },
  // Traffic sum
  pagesDate ? supabase.from('web_page_metrics').select('est_traffic').eq('website_id', websiteId).eq('date', pagesDate) : { data: [] },
  // Audit score
  auditDate ? supabase.from('web_audit_summary').select('score').eq('website_id', websiteId).eq('date', auditDate).single() : { data: null },
  // Referring domains
  blDate ? supabase.from('web_backlink_summary').select('ref_domains').eq('website_id', websiteId).eq('date', blDate).single() : { data: null }
]);

2. useWebTopPages(websiteId: string, limit: number)

Fetches top landing pages for a specific website.

Query:

// Get latest date
const { data: latestDateData } = await supabase
  .from('web_page_metrics')
  .select('date')
  .eq('website_id', websiteId)
  .order('date', { ascending: false })
  .limit(1);

// Get top pages with join to web_pages table
const { data, error } = await supabase
  .from('web_page_metrics')
  .select(`
    page_id,
    est_traffic,
    organic_keywords,
    web_pages!inner(url)
  `)
  .eq('website_id', websiteId)
  .eq('date', latestDate)
  .order('est_traffic', { ascending: false })
  .limit(limit);

3. useWebKeywords(websiteId: string, limit: number)

Fetches top keywords with position changes for a specific website.

Queries:

// Get latest and previous dates
const { data: latestDateData } = await supabase
  .from('web_keyword_ranks')
  .select('date')
  .eq('website_id', websiteId)
  .order('date', { ascending: false })
  .limit(1);

const { data: prevDateData } = await supabase
  .from('web_keyword_ranks')
  .select('date')
  .eq('website_id', websiteId)
  .lt('date', latestDate)
  .order('date', { ascending: false })
  .limit(1);

// Get current keywords with join to web_keywords table
const { data: currentData } = await supabase
  .from('web_keyword_ranks')
  .select(`
    keyword_id,
    position,
    volume,
    est_traffic,
    date,
    web_keywords!inner(keyword)
  `)
  .eq('website_id', websiteId)
  .eq('date', latestDate)
  .order('est_traffic', { ascending: false })
  .limit(limit);

// Get previous positions for comparison
const { data: prevData } = await supabase
  .from('web_keyword_ranks')
  .select('keyword_id, position')
  .eq('website_id', websiteId)
  .eq('date', prevDate);

4. useWebCompetitors(websiteId: string, limit: number)

Fetches competitors for a specific website.

Query:

// Get latest date
const { data: latestDateData } = await supabase
  .from('web_competitor_domain_rollup')
  .select('date')
  .eq('website_id', websiteId)
  .order('date', { ascending: false })
  .limit(1);

// Get competitors data
const { data, error } = await supabase
  .from('web_competitor_domain_rollup')
  .select('competitor_domain, keywords_sum, est_traffic_sum, visibility_max')
  .eq('website_id', websiteId)
  .eq('date', latestDate)
  .order('est_traffic_sum', { ascending: false })
  .limit(limit);

5. useWebAuditSummary(websiteId: string)

Fetches audit summary for a specific website.

Query:

const { data, error } = await supabase
  .from('web_audit_summary')
  .select('score, pages_crawled, high_issues, medium_issues, low_issues, date')
  .eq('website_id', websiteId)
  .order('date', { ascending: false })
  .limit(1)
  .single();

6. useWebAuditIssues(websiteId: string)

Fetches audit issues for a specific website.

Query:

// Get latest date
const { data: latestDateData } = await supabase
  .from('web_audit_issues')
  .select('date')
  .eq('website_id', websiteId)
  .order('date', { ascending: false })
  .limit(1);

// Get audit issues
const { data, error } = await supabase
  .from('web_audit_issues')
  .select('id, issue_type, priority, pages_affected, date')
  .eq('website_id', websiteId)
  .eq('date', latestDate)
  .order('priority', { ascending: true })
  .order('pages_affected', { ascending: false });

7. useWebsites(clientId: string)

Fetches websites for a specific client.

Query:

const { data, error } = await supabase
  .from('websites')
  .select('id, domain_clean')
  .eq('client_id', clientId)
  .order('created_at', { ascending: true });

Benefits of Direct Queries

1. No RPC Functions Required - Eliminates the need to create and maintain database functions
2. Better Error Handling - Direct access to Supabase error messages
3. Easier Debugging - Can see exactly what queries are being executed
4. Type Safety - Better TypeScript integration with Supabase client
5. Flexibility - Easy to modify queries without database changes
6. Performance Monitoring - Built-in query performance tracking

Table Relationships

The queries rely on the following table relationships:

• web_keyword_ranks → web_keywords (via keyword_id)
• web_page_metrics → web_pages (via page_id)
• All tables → websites (via website_id)
• All tables → clients (via client_id)

Required Table Structures

The implementation expects these tables to exist with the following key fields:

web_keyword_ranks

• website_id, keyword_id, date, position, volume, est_traffic

web_page_metrics

• website_id, page_id, date, est_traffic, organic_keywords

web_backlink_summary

• website_id, date, ref_domains

web_audit_summary

• website_id, date, score, pages_crawled, high_issues, medium_issues, low_issues

web_audit_issues

• website_id, date, issue_type, priority, pages_affected

web_competitor_domain_rollup

• website_id, date, competitor_domain, keywords_sum, est_traffic_sum, visibility_max

web_keywords

• id, keyword

web_pages

• id, url

websites

• id, client_id, domain_clean, created_at

Error Handling

All queries include proper error handling with:

• Logging of errors with context
• Graceful fallbacks for missing data
• Type-safe error propagation
• Performance monitoring integration