Supabase Database Views: Simplify Complex Queries

-- Simple view: User profiles with stats
create view user_profiles_with_stats as
select
  u.id,
  u.email,
  u.created_at,
  count(distinct p.id) as post_count,
  count(distinct c.id) as comment_count,
  count(distinct f.follower_id) as followers_count,
  count(distinct ff.following_id) as following_count
from auth.users u
left join posts p on p.user_id = u.id
left join comments c on c.user_id = u.id
left join follows f on f.following_id = u.id
left join follows ff on ff.follower_id = u.id
group by u.id, u.email, u.created_at;

-- View with complex business logic: Active subscriptions
create view active_subscriptions as
select
  s.id as subscription_id,
  s.user_id,
  u.email,
  s.status,
  s.current_period_end,
  pp.name as plan_name,
  pp.amount as plan_amount,
  pp.interval,
  case
    when s.trial_end > now() then 'trial'
    when s.cancel_at_period_end then 'canceling'
    when s.status = 'active' then 'active'
    else 'inactive'
  end as computed_status,
  s.current_period_end - now() as time_until_renewal
from subscriptions s
join auth.users u on u.id = s.user_id
join pricing_plans pp on pp.id = s.price_id
where s.status in ('active', 'trialing');

-- View with aggregations: Project statistics
create view project_stats as
select
  p.id as project_id,
  p.name as project_name,
  p.workspace_id,
  count(t.id) as total_tasks,
  count(t.id) filter (where t.status = 'todo') as todo_tasks,
  count(t.id) filter (where t.status = 'in_progress') as in_progress_tasks,
  count(t.id) filter (where t.status = 'done') as completed_tasks,
  round(avg(case when t.status = 'done' then 100 else 0 end)) as completion_percentage,
  count(distinct t.assigned_to) as team_members_count,
  sum(te.duration_minutes) as total_time_minutes
from projects p
left join tasks t on t.project_id = p.id
left join time_entries te on te.task_id = t.id
group by p.id, p.name, p.workspace_id;

-- View with JSON aggregation: Posts with comments
create view posts_with_comments as
select
  p.id,
  p.title,
  p.content,
  p.user_id,
  p.created_at,
  p.likes_count,
  jsonb_build_object(
    'id', u.id,
    'username', u.username,
    'avatar_url', u.avatar_url
  ) as author,
  coalesce(
    jsonb_agg(
      jsonb_build_object(
        'id', c.id,
        'content', c.content,
        'user_id', c.user_id,
        'created_at', c.created_at
      )
      order by c.created_at desc
    ) filter (where c.id is not null),
    '[]'::jsonb
  ) as comments
from posts p
join profiles u on u.id = p.user_id
left join comments c on c.post_id = p.id
group by p.id, p.title, p.content, p.user_id, p.created_at, p.likes_count, u.id, u.username, u.avatar_url;

-- Recursive view: Comment threads
create view comment_threads as
with recursive thread as (
  -- Base case: top-level comments
  select
    id,
    post_id,
    parent_id,
    content,
    user_id,
    created_at,
    0 as depth,
    array[id] as path
  from comments
  where parent_id is null
  
  union all
  
  -- Recursive case: replies
  select
    c.id,
    c.post_id,
    c.parent_id,
    c.content,
    c.user_id,
    c.created_at,
    t.depth + 1,
    t.path || c.id
  from comments c
  join thread t on c.parent_id = t.id
  where t.depth < 10 -- Limit recursion depth
)
select * from thread;

-- Materialized view: Dashboard analytics
create materialized view dashboard_analytics as
select
  date_trunc('day', created_at) as date,
  count(*) as total_signups,
  count(*) filter (where created_at >= now() - interval '7 days') as signups_last_7_days,
  count(distinct id) as total_users,
  count(*) filter (
    where id in (
      select user_id from posts
      where created_at >= now() - interval '30 days'
    )
  ) as active_users_30_days
from auth.users
group by date_trunc('day', created_at)
order by date desc;

-- Create unique index for concurrent refresh
create unique index on dashboard_analytics(date);

-- Materialized view: Sales summary
create materialized view sales_summary as
select
  date_trunc('month', created_at) as month,
  count(*) as order_count,
  sum(total) as total_revenue,
  avg(total) as average_order_value,
  count(distinct customer_id) as unique_customers,
  sum(total) filter (where status = 'paid') as paid_revenue,
  sum(total) filter (where status = 'refunded') as refunded_amount
from orders
group by date_trunc('month', created_at)
order by month desc;

-- Materialized view: Popular content
create materialized view popular_content as
select
  p.id,
  p.title,
  p.slug,
  p.user_id,
  u.username as author_username,
  p.likes_count,
  p.comments_count,
  p.created_at,
  -- Engagement score
  (p.likes_count * 2 + p.comments_count * 3 + p.shares_count * 5) as engagement_score,
  -- Time-weighted score
  (p.likes_count * 2 + p.comments_count * 3) /
  (extract(epoch from (now() - p.created_at)) / 3600 + 2) as trending_score
from posts p
join profiles u on u.id = p.user_id
where p.created_at > now() - interval '30 days'
order by trending_score desc
limit 100;

-- Manual refresh
refresh materialized view dashboard_analytics;

-- Concurrent refresh (doesn't lock reads, requires unique index)
refresh materialized view concurrently dashboard_analytics;

-- Automatic refresh with trigger
create or replace function refresh_dashboard_analytics()
returns trigger as $$
begin
  refresh materialized view concurrently dashboard_analytics;
  return null;
end;
$$ language plpgsql;

create trigger refresh_analytics_on_user_insert
  after insert on auth.users
  for each statement
  execute function refresh_dashboard_analytics();

-- Scheduled refresh with pg_cron (if enabled)
-- Install pg_cron extension
create extension if not exists pg_cron;

-- Schedule refresh every hour
select cron.schedule(
  'refresh-analytics',
  '0 * * * *', -- Every hour at minute 0
  $$refresh materialized view concurrently dashboard_analytics$$
);

-- Schedule refresh every day at midnight
select cron.schedule(
  'refresh-popular-content',
  '0 0 * * *',
  $$refresh materialized view concurrently popular_content$$
);

-- Edge Function for manual refresh
-- supabase/functions/refresh-views/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

serve(async (req) => {
  try {
    const { view } = await req.json()

    // Validate view name
    const allowedViews = [
      'dashboard_analytics',
      'sales_summary',
      'popular_content'
    ]

    if (!allowedViews.includes(view)) {
      return new Response(
        JSON.stringify({ error: 'Invalid view name' }),
        { status: 400 }
      )
    }

    // Refresh materialized view
    const { error } = await supabase.rpc('refresh_materialized_view', {
      view_name: view
    })

    if (error) throw error

    return new Response(
      JSON.stringify({ message: `View ${view} refreshed successfully` })
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500 }
    )
  }
})

-- Function to refresh materialized view
create or replace function refresh_materialized_view(view_name text)
returns void as $$
begin
  execute format('refresh materialized view concurrently %I', view_name);
end;
$$ language plpgsql security definer;

-- Security barrier view: Only show user's own data
create view my_subscriptions with (security_barrier = true) as
select
  s.*,
  pp.name as plan_name,
  pp.amount,
  pp.interval
from subscriptions s
join pricing_plans pp on pp.id = s.price_id
where s.user_id = auth.uid();

-- Enable RLS on view
alter view my_subscriptions set (security_invoker = true);

-- View with filtered sensitive data
create view public_user_profiles as
select
  id,
  username,
  full_name,
  avatar_url,
  bio,
  created_at,
  -- Exclude email and sensitive fields
  followers_count,
  following_count,
  posts_count
from profiles
where is_private = false; -- Only public profiles

-- View for team members only
create view team_workspace_data as
select
  w.id as workspace_id,
  w.name as workspace_name,
  p.id as project_id,
  p.name as project_name,
  t.id as task_id,
  t.title as task_title,
  t.status,
  t.assigned_to
from workspaces w
join projects p on p.workspace_id = w.id
join tasks t on t.project_id = p.id
where exists (
  select 1 from workspace_members wm
  where wm.workspace_id = w.id
  and wm.user_id = auth.uid()
);

-- Updatable view with instead-of triggers
create view editable_posts as
select
  p.id,
  p.title,
  p.content,
  p.status,
  p.user_id
from posts p
where p.user_id = auth.uid();

-- Make view updatable with rules
create rule update_editable_posts as
  on update to editable_posts
  do instead
    update posts
    set
      title = new.title,
      content = new.content,
      status = new.status,
      updated_at = now()
    where id = old.id
    and user_id = auth.uid();

create rule delete_editable_posts as
  on delete to editable_posts
  do instead
    delete from posts
    where id = old.id
    and user_id = auth.uid();

-- View with row-level security policy
create view workspace_analytics as
select
  w.id as workspace_id,
  w.name,
  count(distinct p.id) as project_count,
  count(distinct t.id) as task_count,
  count(distinct wm.user_id) as member_count,
  sum(te.duration_minutes) as total_time_tracked
from workspaces w
left join projects p on p.workspace_id = w.id
left join tasks t on t.project_id = p.id
left join workspace_members wm on wm.workspace_id = w.id
left join time_entries te on te.task_id = t.id
group by w.id, w.name;

-- RLS policy on view
alter table workspace_analytics enable row level security;

create policy "Users can view analytics for their workspaces"
  on workspace_analytics for select
  using (
    exists (
      select 1 from workspace_members
      where workspace_id = workspace_analytics.workspace_id
      and user_id = auth.uid()
    )
  );

// Query views like regular tables
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(url, key)

// Query standard view
const { data: profiles } = await supabase
  .from('user_profiles_with_stats')
  .select('*')
  .order('followers_count', { ascending: false })
  .limit(10)

// Query materialized view
const { data: analytics } = await supabase
  .from('dashboard_analytics')
  .select('*')
  .order('date', { ascending: false })
  .limit(30)

// Query view with filters
const { data: activeSubscriptions } = await supabase
  .from('active_subscriptions')
  .select('*')
  .eq('user_id', userId)

// Query view with aggregations
const { data: projectStats } = await supabase
  .from('project_stats')
  .select('*')
  .eq('workspace_id', workspaceId)
  .gte('completion_percentage', 50)

// hooks/useAnalytics.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface Analytics {
  date: string
  total_signups: number
  signups_last_7_days: number
  active_users_30_days: number
}

export function useAnalytics() {
  const [analytics, setAnalytics] = useState<Analytics[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadAnalytics()
  }, [])

  async function loadAnalytics() {
    const { data } = await supabase
      .from('dashboard_analytics')
      .select('*')
      .order('date', { ascending: false })
      .limit(30)

    if (data) {
      setAnalytics(data)
    }
    setLoading(false)
  }

  async function refreshView() {
    // Call Edge Function to refresh materialized view
    const { data: { session } } = await supabase.auth.getSession()
    
    await fetch(
      `${process.env.NEXT_PUBLIC_SUPABASE_URL}/functions/v1/refresh-views`,
      {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          Authorization: `Bearer ${session?.access_token}`,
        },
        body: JSON.stringify({ view: 'dashboard_analytics' }),
      }
    )

    // Reload data
    await loadAnalytics()
  }

  return { analytics, loading, refreshView }
}

// components/AnalyticsDashboard.tsx
import { useAnalytics } from '@/hooks/useAnalytics'
import { format } from 'date-fns'

export function AnalyticsDashboard() {
  const { analytics, loading, refreshView } = useAnalytics()

  if (loading) return <div>Loading...</div>

  return (
    <div className="space-y-6">
      <div className="flex justify-between items-center">
        <h2 className="text-2xl font-bold">Analytics</h2>
        <button
          onClick={refreshView}
          className="px-4 py-2 bg-blue-500 text-white rounded"
        >
          Refresh Data
        </button>
      </div>

      <div className="grid grid-cols-3 gap-4">
        {analytics.slice(0, 1).map((day) => (
          <div key={day.date} className="bg-white p-6 rounded-lg shadow">
            <h3 className="text-sm text-gray-500 mb-2">Total Signups</h3>
            <p className="text-3xl font-bold">{day.total_signups}</p>
          </div>
        ))}
      </div>

      <div className="bg-white p-6 rounded-lg shadow">
        <h3 className="text-lg font-semibold mb-4">Signups Over Time</h3>
        <div className="space-y-2">
          {analytics.map((day) => (
            <div key={day.date} className="flex justify-between">
              <span>{format(new Date(day.date), 'MMM d, yyyy')}</span>
              <span className="font-semibold">{day.total_signups}</span>
            </div>
          ))}
        </div>
      </div>
    </div>
  )
}

-- Add indexes on view's underlying tables
create index idx_posts_user_created on posts(user_id, created_at desc);
create index idx_posts_created on posts(created_at desc);
create index idx_comments_post on comments(post_id);
create index idx_follows_following on follows(following_id);

-- Analyze query plan for view
explain analyze
select * from user_profiles_with_stats
where followers_count > 100;

-- Create covering index for view queries
create index idx_posts_view_covering on posts(user_id, created_at, likes_count, comments_count);

-- Partial index for filtered views
create index idx_active_subscriptions on subscriptions(user_id)
where status in ('active', 'trialing');

-- Optimize materialized view storage
alter materialized view dashboard_analytics set (autovacuum_enabled = true);

-- View with conditional aggregation (more efficient)
create view task_stats_optimized as
select
  project_id,
  count(*) as total_tasks,
  sum(case when status = 'todo' then 1 else 0 end) as todo_tasks,
  sum(case when status = 'in_progress' then 1 else 0 end) as in_progress_tasks,
  sum(case when status = 'done' then 1 else 0 end) as completed_tasks
from tasks
group by project_id;

-- Incremental materialized view refresh
-- Track changes for incremental refresh
create table view_refresh_log (
  id serial primary key,
  view_name text not null,
  last_refresh timestamp with time zone default now(),
  rows_affected int
);

-- Function for incremental refresh
create or replace function incremental_refresh_popular_content()
returns void as $$
declare
  last_refresh timestamp;
  affected_rows int;
begin
  -- Get last refresh time
  select last_refresh into last_refresh
  from view_refresh_log
  where view_name = 'popular_content'
  order by last_refresh desc
  limit 1;

  if last_refresh is null then
    last_refresh := now() - interval '1 day';
  end if;

  -- Delete old entries
  delete from popular_content_data
  where post_id in (
    select id from posts
    where updated_at > last_refresh
  );

  -- Insert updated entries
  insert into popular_content_data
  select * from popular_content_view
  where post_id in (
    select id from posts
    where updated_at > last_refresh
  );

  get diagnostics affected_rows = row_count;

  -- Log refresh
  insert into view_refresh_log (view_name, rows_affected)
  values ('popular_content', affected_rows);
end;
$$ language plpgsql;