Supasheet.

Complete Example

Step-by-step guide to creating a full feature in Supasheet

Overview

This guide shows you how to create a complete feature in Supasheet from scratch. We'll build a Task Management system with:

  • CRUD operations for tasks
  • Dashboard widgets (cards and tables)
  • Charts for visualization
  • Reports for data analysis
  • Audit logging
  • File attachments

This example is based on the actual tasks migration in the Supasheet source code.

The Complete Workflow

Step 1: Create Custom Types

First, create any custom enum types your table needs:

-- supabase/migrations/YYYYMMDDHHMMSS_tasks_types.sql

CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'completed', 'archived');
CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'urgent');

Step 2: Add Permissions to the System

Add permission values for ALL operations you'll need:

-- Permissions for the table (CRUD operations)
ALTER TYPE supasheet.app_permission ADD VALUE 'public.tasks:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.tasks:insert';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.tasks:update';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.tasks:delete';

-- Permissions for views
ALTER TYPE supasheet.app_permission ADD VALUE 'public.user_tasks:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_report:select';

-- Permissions for dashboard widgets
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_summary:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_completion_rate:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.tasks_by_status:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_urgent_count:select';

ALTER TYPE supasheet.app_permission ADD VALUE IF NOT EXISTS 'public.task_list_simple:select';
ALTER TYPE supasheet.app_permission ADD VALUE IF NOT EXISTS 'public.active_tasks_simple:select';
ALTER TYPE supasheet.app_permission ADD VALUE IF NOT EXISTS 'public.task_list_detailed:select';
ALTER TYPE supasheet.app_permission ADD VALUE IF NOT EXISTS 'public.task_analytics_detailed:select';

-- Permissions for charts
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_trend_area:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_priority_bar:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_completion_line:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_status_pie:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'public.task_metrics_radar:select';

IMPORTANT: You must add permissions for EVERY table, view, dashboard, chart, and report you create. Without these permissions, users won't be able to access the data even if they have the right role.

Step 3: Create the Table

Create your main table with proper structure:

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
    title VARCHAR(500) NOT NULL,
    description RICH_TEXT,
    status task_status DEFAULT 'pending',
    priority task_priority DEFAULT 'medium',
    cover FILE,

    -- User association
    account_id UUID DEFAULT auth.uid() REFERENCES supasheet.accounts(id) ON DELETE CASCADE,

    -- Dates
    due_date TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Organization
    tags TEXT[],
    is_important BOOLEAN DEFAULT false,

    -- Progress tracking
    completion PERCENTAGE,
    duration DURATION,

    -- File tracking
    attachments FILE,

    -- Customization
    color COLOR,
    notes TEXT,

    -- Audit fields
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Table comment with UI configuration
COMMENT ON TABLE public.tasks IS
'{"icon": "ListTodo", "display": "block", "query": {"sort": [{"id":"title","desc":false}], "filter": [{"id":"color","value":"#f59e0b","variant":"text","operator":"ilike"}], "join": [{"table":"accounts","on":"account_id","columns":["name","email"]}]}, "items": [{"id":"sheet","name":"Sheet View","type":"sheet"}, {"id":"status","name":"Tasks By Status","type":"kanban","group":"status","title":"title","description":"description","date":"created_at","badge":"priority"}, {"id":"priority","name":"Tasks By Priority","type":"kanban","group":"priority","title":"title","description":"description","date":"created_at","badge":"status"}, {"id":"calendar","name":"Calendar View","type":"calendar", "title": "title", "startDate": "created_at", "endDate": "due_date", "badge": "status"}, {"id":"gallery","name":"Gallery View","type":"gallery","cover":"cover","title":"title","description":"description","badge":"status"}]}';

-- Column comments for file types
COMMENT ON COLUMN tasks.cover IS '{"accept":"image/*"}';
COMMENT ON COLUMN tasks.attachments IS '{"accept":"*"}';

-- Create indexes
CREATE INDEX idx_tasks_account_id ON public.tasks (account_id);
CREATE INDEX idx_tasks_status ON public.tasks (status);
CREATE INDEX idx_tasks_priority ON public.tasks (priority);

Supasheet uses custom domain types like FILE, RICH_TEXT, PERCENTAGE, DURATION, and COLOR for enhanced UI rendering. The table comment contains JSON metadata that configures the UI display, views, and query options.

Step 4: Set Up Row Level Security (RLS)

Enable RLS and create policies:

-- Revoke default permissions
REVOKE ALL ON TABLE public.tasks FROM authenticated, service_role;

-- Grant basic permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.tasks TO authenticated;

-- Enable RLS
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY tasks_select ON public.tasks
    FOR SELECT
    TO authenticated
    USING (account_id = auth.uid() AND supasheet.has_permission('public.tasks:select'));

CREATE POLICY tasks_insert ON public.tasks
    FOR INSERT
    TO authenticated
    WITH CHECK (account_id = auth.uid() AND supasheet.has_permission('public.tasks:insert'));

CREATE POLICY tasks_update ON public.tasks
    FOR UPDATE
    TO authenticated
    USING (account_id = auth.uid() AND supasheet.has_permission('public.tasks:update'))
    WITH CHECK (account_id = auth.uid() AND supasheet.has_permission('public.tasks:update'));

CREATE POLICY tasks_delete ON public.tasks
    FOR DELETE
    TO authenticated
    USING (account_id = auth.uid() AND supasheet.has_permission('public.tasks:delete'));

Step 5: Create Views (Optional)

Create views for enhanced querying:

-- Create a view with joined data
CREATE OR REPLACE VIEW public.user_tasks
WITH(security_invoker = true) AS
SELECT
    a.name AS account_name,
    t.*
FROM tasks t
JOIN supasheet.accounts a ON t.account_id = a.id;

COMMENT ON VIEW public.user_tasks IS '{"icon": "UserCheck"}';

-- Grant permissions on view
REVOKE ALL ON public.user_tasks FROM authenticated, service_role;
GRANT SELECT ON public.user_tasks TO authenticated;

Step 6: Create Dashboard Widgets

Dashboard widgets come in different types. Create views for each widget type you need:

Card Type 1: Simple Metric Card

CREATE OR REPLACE VIEW public.task_summary AS
SELECT
    COUNT(*) AS value,
    'list-todo' AS icon,
    'active tasks' AS label
FROM tasks t
WHERE t.status != 'completed';

REVOKE ALL ON public.task_summary FROM authenticated, service_role;
GRANT SELECT ON public.task_summary TO authenticated;

COMMENT ON VIEW public.task_summary IS '{"type": "dashboard_widget", "name": "Task Summary", "description": "Summary of active tasks", "widget_type": "card_1"}';

Card Type 2: Split Layout Card

CREATE OR REPLACE VIEW public.task_completion_rate AS
SELECT
    COUNT(*) FILTER (WHERE status = 'completed') AS primary,
    COUNT(*) FILTER (WHERE status != 'completed') AS secondary,
    'Completed' AS primary_label,
    'Active' AS secondary_label
FROM tasks t;

REVOKE ALL ON public.task_completion_rate FROM authenticated, service_role;
GRANT SELECT ON public.task_completion_rate TO authenticated;

COMMENT ON VIEW public.task_completion_rate IS '{"type": "dashboard_widget", "name": "Task Completion Rate", "description": "Completed vs Active tasks", "widget_type": "card_2"}';

Card Type 3: Value and Percent Card

CREATE OR REPLACE VIEW public.tasks_by_status AS
SELECT
    COUNT(*) FILTER (WHERE status = 'completed') AS value,
    CASE
        WHEN COUNT(*) > 0
        THEN ROUND((COUNT(*) FILTER (WHERE status = 'completed')::NUMERIC / COUNT(*)::NUMERIC) * 100, 1)
        ELSE 0
    END AS percent
FROM tasks t;

REVOKE ALL ON public.tasks_by_status FROM authenticated, service_role;
GRANT SELECT ON public.tasks_by_status TO authenticated;

COMMENT ON VIEW public.tasks_by_status IS '{"type": "dashboard_widget", "name": "Tasks by Status", "description": "Completed tasks stats", "widget_type": "card_3"}';

Card Type 4: Progress Card

CREATE OR REPLACE VIEW public.task_urgent_count AS
SELECT
    COUNT(*) FILTER (WHERE status != 'completed' AND priority IN ('high', 'urgent')) AS current,
    COUNT(*) FILTER (WHERE status != 'completed') AS total,
    JSON_BUILD_ARRAY(
        JSON_BUILD_OBJECT('label', 'Urgent', 'value', COUNT(*) FILTER (WHERE priority = 'urgent' AND status != 'completed')),
        JSON_BUILD_OBJECT('label', 'High', 'value', COUNT(*) FILTER (WHERE priority = 'high' AND status != 'completed')),
        JSON_BUILD_OBJECT('label', 'Overdue', 'value', COUNT(*) FILTER (WHERE due_date < CURRENT_TIMESTAMP AND status != 'completed'))
    ) AS segments
FROM tasks;

REVOKE ALL ON public.task_urgent_count FROM authenticated, service_role;
GRANT SELECT ON public.task_urgent_count TO authenticated;

COMMENT ON VIEW public.task_urgent_count IS '{"type": "dashboard_widget", "name": "Task Urgent Count", "description": "High priority tasks", "widget_type": "card_4"}';

Table Type 1: Simple Table (2-3 columns)

CREATE OR REPLACE VIEW public.task_list_simple AS
SELECT
    title,
    status,
    priority,
    completion
FROM tasks
ORDER BY created_at DESC
LIMIT 10;

REVOKE ALL ON public.task_list_simple FROM authenticated, service_role;
GRANT SELECT ON public.task_list_simple TO authenticated;

COMMENT ON VIEW public.task_list_simple IS '{"type": "dashboard_widget", "name": "Recent Tasks", "description": "Latest tasks in the system", "widget_type": "table_1"}';

Table Type 2: Detailed Table (4-5 columns)

CREATE OR REPLACE VIEW public.task_list_detailed AS
SELECT
    title,
    status,
    priority,
    completion,
    duration,
    TO_CHAR(created_at, 'MM/DD HH24:MI') AS created,
    CASE
        WHEN due_date < CURRENT_TIMESTAMP AND status != 'completed' THEN 'Overdue'
        WHEN due_date IS NULL THEN '-'
        ELSE TO_CHAR(due_date, 'MM/DD')
    END AS due
FROM tasks
ORDER BY created_at DESC
LIMIT 10;

REVOKE ALL ON public.task_list_detailed FROM authenticated, service_role;
GRANT SELECT ON public.task_list_detailed TO authenticated;

COMMENT ON VIEW public.task_list_detailed IS '{"type": "dashboard_widget", "name": "Task Overview", "description": "Detailed task listing", "widget_type": "table_2"}';

Step 7: Create Charts

Create views for different chart types:

Area Chart

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);

REVOKE ALL ON public.task_trend_area FROM authenticated, service_role;
GRANT SELECT ON public.task_trend_area TO authenticated;

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"}';

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;

REVOKE ALL ON public.task_priority_bar FROM authenticated, service_role;
GRANT SELECT ON public.task_priority_bar TO authenticated;

COMMENT ON VIEW public.task_priority_bar IS '{"type": "chart", "name": "Task Priority Bar", "description": "Tasks grouped by priority level", "chart_type": "bar"}';

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);

REVOKE ALL ON public.task_completion_line FROM authenticated, service_role;
GRANT SELECT ON public.task_completion_line TO authenticated;

COMMENT ON VIEW public.task_completion_line IS '{"type": "chart", "name": "Task Completion Line", "description": "Daily task completion over 2 weeks", "chart_type": "line"}';

Pie Chart

CREATE OR REPLACE VIEW public.task_status_pie AS
SELECT
    status AS label,
    COUNT(*) AS value
FROM tasks
GROUP BY status;

REVOKE ALL ON public.task_status_pie FROM authenticated, service_role;
GRANT SELECT ON public.task_status_pie TO authenticated;

COMMENT ON VIEW public.task_status_pie IS '{"type": "chart", "name": "Task Status Pie", "description": "Current task status breakdown", "chart_type": "pie"}';

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;

REVOKE ALL ON public.task_metrics_radar FROM authenticated, service_role;
GRANT SELECT ON public.task_metrics_radar TO authenticated;

COMMENT ON VIEW public.task_metrics_radar IS '{"type": "chart", "name": "Task Metrics Radar", "description": "Task metrics across priorities", "chart_type": "radar"}';

Step 8: Create Reports

Create report views:

CREATE OR REPLACE VIEW public.task_report
WITH(security_invoker = true) AS
SELECT
    a.name AS account_name,
    t.*
FROM tasks t
JOIN supasheet.accounts a ON t.account_id = a.id;

REVOKE ALL ON public.task_report FROM authenticated, service_role;
GRANT SELECT ON public.task_report TO authenticated;

COMMENT ON VIEW public.task_report IS '{"type": "report", "name": "Task Summary", "description": "Summary of active tasks"}';

Step 9: Set Up Audit Logging

Add triggers to automatically track changes:

CREATE TRIGGER audit_tasks_insert
    AFTER INSERT
    ON public.tasks
    FOR EACH ROW
EXECUTE FUNCTION supasheet.audit_trigger_function();

CREATE TRIGGER audit_tasks_update
    AFTER UPDATE
    ON public.tasks
    FOR EACH ROW
EXECUTE FUNCTION supasheet.audit_trigger_function();

CREATE TRIGGER audit_tasks_delete
    BEFORE DELETE
    ON public.tasks
    FOR EACH ROW
EXECUTE FUNCTION supasheet.audit_trigger_function();

Step 10: Grant Permissions to Roles

Grant all permissions to appropriate roles:

-- CRUD permissions for tasks
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.tasks:select');
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.tasks:insert');
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.tasks:update');
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.tasks:delete');

-- View permissions
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.user_tasks:select');
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.task_report:select');
INSERT INTO supasheet.role_permissions (role, permission) VALUES ('user', 'public.task_summary:select');

-- Dashboard widget permissions
INSERT INTO supasheet.role_permissions (role, permission) VALUES
    ('user', 'public.task_completion_rate:select'),
    ('user', 'public.tasks_by_status:select'),
    ('user', 'public.task_urgent_count:select');

INSERT INTO supasheet.role_permissions (role, permission) VALUES
    ('user', 'public.task_list_simple:select'),
    ('user', 'public.active_tasks_simple:select'),
    ('user', 'public.task_list_detailed:select'),
    ('user', 'public.task_analytics_detailed:select');

-- Chart permissions
INSERT INTO supasheet.role_permissions (role, permission) VALUES
    ('user', 'public.task_trend_area:select'),
    ('user', 'public.task_priority_bar:select'),
    ('user', 'public.task_completion_line:select'),
    ('user', 'public.task_status_pie:select'),
    ('user', 'public.task_metrics_radar:select');

Key Takeaways

  1. Permissions First: Always add permissions to supasheet.app_permission enum before creating resources
  2. RLS is Required: Every table must have RLS policies that check permissions using supasheet.has_permission()
  3. Revoke Then Grant: Always revoke all permissions first, then explicitly grant what's needed
  4. JSON Comments for Metadata: Use JSON comments on views to identify dashboards, charts, and reports (no separate tables or schemas)
  5. Grant to Roles: Assign permissions to roles in supasheet.role_permissions table
  6. Security Invoker: Use with(security_invoker = true) for views to respect RLS policies
  7. Audit Everything: Add audit triggers for INSERT, UPDATE, and DELETE operations

Complete Migration Template

Here's a complete migration template you can use:

-- 1. Create types
create type [your_type] as enum ('value1', 'value2');

-- 2. Add all permissions
alter type supasheet.app_permission add value 'public.[table]:select';
alter type supasheet.app_permission add value 'public.[table]:insert';
alter type supasheet.app_permission add value 'public.[table]:update';
alter type supasheet.app_permission add value 'public.[table]:delete';
-- ... add permissions for views, dashboards, charts, reports

-- 3. Create table
CREATE TABLE [table] (...);

-- 4. Enable RLS and create policies
alter table [table] enable row level security;
revoke all on table [table] from authenticated, service_role;
grant select, insert, update, delete on table [table] to authenticated;
create policy [table]_select on [table] for select ...;
-- ... other policies

-- 5. Create views
create or replace view public.vw_[table] ...;

-- 6. Create dashboard widget views with metadata
create or replace view public.[widget_name] ...;
comment on view public.[widget_name] is '{"type": "dashboard_widget", "name": "...", "description": "...", "widget_type": "card_1"}';

-- 7. Create chart views with metadata
create or replace view public.[chart_name] ...;
comment on view public.[chart_name] is '{"type": "chart", "name": "...", "description": "...", "chart_type": "line"}';

-- 8. Create report views with metadata
create or replace view public.[report_name] ...;
comment on view public.[report_name] is '{"type": "report", "name": "...", "description": "..."}';

-- 9. Add audit triggers
CREATE TRIGGER audit_[table]_insert AFTER INSERT ON [table] FOR EACH ROW EXECUTE FUNCTION supasheet.audit_trigger_function();
CREATE TRIGGER audit_[table]_update AFTER UPDATE ON [table] FOR EACH ROW EXECUTE FUNCTION supasheet.audit_trigger_function();
CREATE TRIGGER audit_[table]_delete BEFORE DELETE ON [table] FOR EACH ROW EXECUTE FUNCTION supasheet.audit_trigger_function();

-- 13. Grant permissions to roles
insert into supasheet.role_permissions (role, permission) values
    ('user', 'public.[table]:select'),
    ('user', 'public.[table]:insert');
    -- ... all other permissions

-- 10. Update meta tables (IMPORTANT!)
INSERT INTO supasheet.columns SELECT * FROM supasheet.generate_columns('public')
ON CONFLICT (id) DO UPDATE SET schema = EXCLUDED.schema, table_id = EXCLUDED.table_id, name = EXCLUDED.name;

INSERT INTO supasheet.tables SELECT * FROM supasheet.generate_tables('public')
ON CONFLICT (id) DO UPDATE SET schema = EXCLUDED.schema, name = EXCLUDED.name, rls_enabled = EXCLUDED.rls_enabled;

INSERT INTO supasheet.views SELECT * FROM supasheet.generate_views('public')
ON CONFLICT (id) DO UPDATE SET schema = EXCLUDED.schema, name = EXCLUDED.name, comment = EXCLUDED.comment;

-- Views include all types (regular, dashboard widgets, charts, reports)
-- The system identifies them by the JSON comment metadata

Next Steps

Authorization

Role-based permissions and Row Level Security

Basic

Automatically generated interfaces from your database tables

On this page

OverviewThe Complete WorkflowStep 1: Create Custom TypesStep 2: Add Permissions to the SystemStep 3: Create the TableStep 4: Set Up Row Level Security (RLS)Step 5: Create Views (Optional)Step 6: Create Dashboard WidgetsCard Type 1: Simple Metric CardCard Type 2: Split Layout CardCard Type 3: Value and Percent CardCard Type 4: Progress CardTable Type 1: Simple Table (2-3 columns)Table Type 2: Detailed Table (4-5 columns)Step 7: Create ChartsArea ChartBar ChartLine ChartPie ChartRadar ChartStep 8: Create ReportsStep 9: Set Up Audit LoggingStep 10: Grant Permissions to RolesKey TakeawaysComplete Migration TemplateNext Steps