Charts

Overview

Charts in Supasheet are created by defining SQL views in the public schema with JSON metadata in comments. To display a chart, you need to:

Add permission value to supasheet.app_permission enum (if needed)
Create a view in the public schema
Add JSON metadata comment with type: "chart" and chart_type
Grant permissions on the view (revoke all, then grant select)
Grant :select permission to roles via supasheet.role_permissions

Chart Types

Supasheet supports these chart types:

area - Area chart for trends over time
bar - Bar chart for categorical comparisons
line - Line chart for time-series data
pie - Pie chart for distribution visualization
radar - Radar chart for multi-metric comparison

Creating Charts

Area Chart

-- Step 1: Add permission to enum (if not exists)
alter type supasheet.app_permission add value if not exists 'public.task_trend_area:select';

-- Step 2: Create view
create or replace view public.task_trend_area as
select
  to_char(date_trunc('day', created_at), 'Mon DD') as date,
  count(*) filter (where status = 'completed') as completed,
  count(*) filter (where status = 'pending') as pending,
  count(*) filter (where status = 'in_progress') as active
from tasks
where created_at >= current_date - interval '7 days'
group by date_trunc('day', created_at)
order by date_trunc('day', created_at);

-- Step 3: Grant permissions
revoke all on public.task_trend_area from authenticated, service_role;
grant select on public.task_trend_area to authenticated;

-- Step 4: Add metadata comment
comment on view public.task_trend_area is '{"type": "chart", "name": "Task Trend Area", "description": "Task creation trend over last 7 days", "chart_type": "area"}';

-- Step 5: Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_trend_area:select');

Bar Chart

-- Create view for bar chart
create or replace view public.task_priority_bar as
select
  priority as label,
  count(*) as total,
  count(*) filter (where status = 'completed') as completed
from tasks
group by priority
order by
  case priority
    when 'urgent' then 1
    when 'high' then 2
    when 'medium' then 3
    when 'low' then 4
  end;

-- Grant permissions
revoke all on public.task_priority_bar from authenticated, service_role;
grant select on public.task_priority_bar to authenticated;

-- Add metadata comment
comment on view public.task_priority_bar is '{"type": "chart", "name": "Task Priority Bar", "description": "Tasks grouped by priority level", "chart_type": "bar"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_priority_bar:select');

Line Chart

-- Create view for line chart
create or replace view public.task_completion_line as
select
  to_char(date_trunc('day', created_at), 'Mon DD') as date,
  count(*) as created,
  count(*) filter (where status = 'completed') as completed
from tasks
where created_at >= current_date - interval '14 days'
group by date_trunc('day', created_at)
order by date_trunc('day', created_at);

-- Grant permissions
revoke all on public.task_completion_line from authenticated, service_role;
grant select on public.task_completion_line to authenticated;

-- Add metadata comment
comment on view public.task_completion_line is '{"type": "chart", "name": "Task Completion Line", "description": "Daily task completion over 2 weeks", "chart_type": "line"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_completion_line:select');

Pie Chart

-- Create view for pie chart
create or replace view public.task_status_pie as
select
  status as label,
  count(*) as value
from tasks
group by status;

-- Grant permissions
revoke all on public.task_status_pie from authenticated, service_role;
grant select on public.task_status_pie to authenticated;

-- Add metadata comment
comment on view public.task_status_pie is '{"type": "chart", "name": "Task Status Pie", "description": "Current task status breakdown", "chart_type": "pie"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_status_pie:select');

Radar Chart

-- Create view for radar chart
create or replace view public.task_metrics_radar as
select
  priority as metric,
  count(*) as total,
  count(*) filter (where status = 'completed') as completed,
  count(*) filter (where due_date < current_timestamp and status != 'completed') as overdue
from tasks
group by priority;

-- Grant permissions
revoke all on public.task_metrics_radar from authenticated, service_role;
grant select on public.task_metrics_radar to authenticated;

-- Add metadata comment
comment on view public.task_metrics_radar is '{"type": "chart", "name": "Task Metrics Radar", "description": "Task metrics across priorities", "chart_type": "radar"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_metrics_radar:select');

Chart Types

Single Metric

CREATE VIEW public.total_revenue AS
SELECT
  SUM(total) as value,
  'Total Revenue' as label
FROM orders;

Multi-Series Line Chart

CREATE VIEW public.revenue_vs_cost AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue,
  SUM(cost) as cost,
  SUM(total - cost) as profit
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY date
ORDER BY date;

Stacked Bar Chart

CREATE VIEW public.orders_by_status_daily AS
SELECT
  DATE(created_at) as date,
  COUNT(*) FILTER (WHERE status = 'pending') as pending,
  COUNT(*) FILTER (WHERE status = 'processing') as processing,
  COUNT(*) FILTER (WHERE status = 'completed') as completed
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY date
ORDER BY date;

Horizontal Bar Chart

CREATE VIEW public.top_customers AS
SELECT
  u.name as customer,
  SUM(o.total) as lifetime_value
FROM orders o
JOIN users u ON o.customer_id = u.id
GROUP BY u.id, u.name
ORDER BY lifetime_value DESC
LIMIT 10;

Advanced Charts

Funnel Chart

CREATE VIEW public.sales_funnel AS
SELECT
  'Visitors' as stage,
  (SELECT COUNT(*) FROM page_views) as count,
  1 as order_index
UNION ALL
SELECT
  'Sign Ups' as stage,
  (SELECT COUNT(*) FROM users) as count,
  2 as order_index
UNION ALL
SELECT
  'Trial Started' as stage,
  (SELECT COUNT(*) FROM subscriptions WHERE status = 'trial') as count,
  3 as order_index
UNION ALL
SELECT
  'Paid Customers' as stage,
  (SELECT COUNT(*) FROM subscriptions WHERE status = 'active') as count,
  4 as order_index
ORDER BY order_index;

Heatmap

CREATE VIEW public.sales_heatmap AS
SELECT
  EXTRACT(HOUR FROM created_at) as hour,
  TO_CHAR(created_at, 'Day') as day,
  COUNT(*) as orders
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY hour, day;

Comparison Chart

CREATE VIEW public.yoy_comparison AS
SELECT
  DATE_TRUNC('month', created_at) as month,
  SUM(total) FILTER (WHERE EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM NOW())) as this_year,
  SUM(total) FILTER (WHERE EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM NOW()) - 1) as last_year
FROM orders
WHERE created_at >= NOW() - INTERVAL '2 years'
GROUP BY month
ORDER BY month;

Gauge Chart

For progress or percentage metrics:

CREATE VIEW public.goal_progress AS
SELECT
  (SELECT SUM(total) FROM orders WHERE EXTRACT(MONTH FROM created_at) = EXTRACT(MONTH FROM NOW())) as current,
  100000 as goal,
  ROUND(
    (SELECT SUM(total) FROM orders WHERE EXTRACT(MONTH FROM created_at) = EXTRACT(MONTH FROM NOW()))::NUMERIC / 100000 * 100,
    2
  ) as percentage;

User-Specific Charts

Show personalized data using RLS:

-- Sales rep performance
CREATE VIEW public.my_sales_trend AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue
FROM orders
WHERE sales_rep_id = auth.uid()
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY date
ORDER BY date;

-- Customer spending
CREATE VIEW public.my_spending AS
SELECT
  DATE_TRUNC('month', created_at) as month,
  SUM(total) as spent
FROM orders
WHERE customer_id = auth.uid()
GROUP BY month
ORDER BY month DESC
LIMIT 12;

Dynamic Filtering

Add date range parameters:

CREATE VIEW public.flexible_revenue AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue
FROM orders
WHERE created_at >= COALESCE(
  (current_setting('app.start_date', true))::DATE,
  NOW() - INTERVAL '30 days'
)
AND created_at <= COALESCE(
  (current_setting('app.end_date', true))::DATE,
  NOW()
)
GROUP BY date
ORDER BY date;

-- Use with:
-- SET app.start_date = '2024-01-01';
-- SET app.end_date = '2024-01-31';

Chart Metadata

Add descriptions and formatting hints:

CREATE VIEW public.monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) as month,
  SUM(total) as revenue,
  'currency' as format,  -- Hint for number formatting
  'line' as chart_type   -- Suggested chart type
FROM orders
GROUP BY month
ORDER BY month DESC
LIMIT 12;

COMMENT ON VIEW public.monthly_revenue IS
  'Monthly revenue trend for the last 12 months';

Performance Optimization

Materialized Views

For complex or slow queries:

CREATE MATERIALIZED VIEW public.expensive_chart AS
SELECT
  category,
  subcategory,
  COUNT(*) as count,
  SUM(revenue) as total_revenue,
  AVG(revenue) as avg_revenue
FROM large_fact_table
JOIN dimension_table USING (id)
GROUP BY category, subcategory;

-- Create index
CREATE INDEX ON public.expensive_chart(category);

-- Refresh periodically
REFRESH MATERIALIZED VIEW public.expensive_chart;

Incremental Updates

-- Only update recent data
CREATE MATERIALIZED VIEW public.recent_metrics AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY date;

-- Refresh more frequently (every hour)
CREATE OR REPLACE FUNCTION refresh_recent_charts()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW public.recent_metrics;
END;
$$ LANGUAGE plpgsql;

Indexes

Add indexes on frequently queried columns:

-- Index for date filtering
CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

-- Index for grouping
CREATE INDEX orders_status_idx ON orders(status);

-- Composite index
CREATE INDEX orders_date_status_idx ON orders(created_at, status);

Complete Charts Example

-- Revenue trend
CREATE VIEW public.revenue_trend AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue,
  COUNT(*) as orders,
  AVG(total) as avg_order_value
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY date
ORDER BY date;

-- Category breakdown
CREATE VIEW public.category_sales AS
SELECT
  p.category,
  COUNT(oi.id) as items_sold,
  SUM(oi.quantity * oi.price) as revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.category
ORDER BY revenue DESC;

-- Customer segments
CREATE VIEW public.customer_distribution AS
SELECT
  CASE
    WHEN order_count >= 10 THEN 'Frequent (10+)'
    WHEN order_count >= 5 THEN 'Regular (5-9)'
    WHEN order_count >= 2 THEN 'Occasional (2-4)'
    ELSE 'One-time'
  END as segment,
  COUNT(*) as customers
FROM (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  GROUP BY customer_id
) customer_orders
GROUP BY segment;

-- Growth metrics
CREATE VIEW public.user_growth AS
SELECT
  DATE(created_at) as date,
  COUNT(*) OVER (ORDER BY DATE(created_at)) as cumulative_users,
  COUNT(*) as new_users
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY date
ORDER BY date;

-- Product performance
CREATE VIEW public.top_products AS
SELECT
  p.name,
  SUM(oi.quantity) as units_sold,
  SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10;

Chart Permissions

Control access to sensitive data:

-- Public charts (no RLS needed)
CREATE VIEW public.public_stats AS
SELECT
  COUNT(*) as total_users,
  COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days') as new_users_week
FROM users;

-- Role-based access
CREATE VIEW public.admin_revenue AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND (
    (SELECT role FROM users WHERE id = auth.uid()) = 'admin'
    OR sales_rep_id = auth.uid()
  )
GROUP BY date;

Next Steps

Dashboards - Combine charts into dashboards
Reports - Generate detailed reports
Database Schema - Learn more about the charts schema

Charts

On this page