Complete Example

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: Create a Replica View for `supasheet.users`

This step is required, not optional. The table you're about to create lives in public, but the canonical user table is supasheet.users — a different schema. PostgREST cannot embed a foreign key across schemas, so without this replica, the query.join you'll add to the table's own metadata (Step 4) and the user_tasks/task_report views (Steps 6 and 9) will silently fail to resolve. See Cross-Schema Joins for why.

Create a same-named view in public that mirrors supasheet.users, before anything else references it:

create or replace view public.users
with (security_invoker = true) as
select * from supasheet.users;

revoke all on public.users from authenticated, service_role;
grant select on public.users to authenticated;

security_invoker = true makes the view enforce RLS as the querying user, not the view owner. This is the exact same pattern used by every bundled example schema (desk.users, crm.users, store.users, …) — it's how supasheet.users gets referenced from any other schema, every time.

The foreign key you'll add in the next step still points straight at supasheet.users(id) — a foreign key can only target a real table, never a view. Only query.join / fields.lookups / view definitions need to go through public.users instead.

Step 3: Plan the Grants You'll Need

There's no permission enum to register up front — access is just Postgres GRANTs applied directly to each resource once it exists. It's still worth planning the full list before you start, since every table, view, dashboard, chart, and report needs at least one grant to appear in the UI at all:

Table: tasks (select, insert, update, delete)
Views: user_tasks, task_report (select)
Dashboard widgets: task_summary, task_completion_rate, tasks_by_status, task_urgent_count, task_list_simple, active_tasks_simple, task_list_detailed, task_analytics_detailed (select)
Charts: task_trend_area, task_priority_bar, task_completion_line, task_status_pie, task_metrics_radar (select)

IMPORTANT: Every table, view, dashboard, chart, and report you create needs an explicit grant to at least one role (Step 5 for the table, Step 11 for everything else). Without a grant, has_table_privilege() returns false for every role and the resource never appears in the UI, no matter what role a user has.

Step 4: 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
    user_id UUID DEFAULT auth.uid() REFERENCES supasheet.users(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","operator":"ilike"}], "join": [{"table":"users","on":"user_id","columns":["name","email"]}]}, "views": [{"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", "start_date": "created_at", "end_date": "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_user_id ON public.tasks (user_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 5: Grant Access and Set Up Row Level Security (RLS)

Revoke the default grants, grant exactly what each role needs, enable RLS, then add row-level policies:

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

-- Grant CRUD to the roles that should have it
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.tasks TO "user";
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.tasks TO "x-admin";

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

-- Create RLS policies — the grant above already decided *whether* a role
-- can attempt an operation; RLS decides *which rows*. Here, every task is
-- owned by a single user, so ownership is the only row-level rule needed.
CREATE POLICY tasks_select ON public.tasks
    FOR SELECT
    TO authenticated
    USING (user_id = auth.uid());

CREATE POLICY tasks_insert ON public.tasks
    FOR INSERT
    TO authenticated
    WITH CHECK (user_id = auth.uid());

CREATE POLICY tasks_update ON public.tasks
    FOR UPDATE
    TO authenticated
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

CREATE POLICY tasks_delete ON public.tasks
    FOR DELETE
    TO authenticated
    USING (user_id = auth.uid());

If every authenticated user should see every row regardless of ownership, use USING (true) instead — that's the default in the bundled example schemas (supabase/demo.sql, supabase/examples/*.sql) for tables that aren't per-user-owned. Reach for an ownership condition like the one above only when rows genuinely belong to individual users.

Step 6: Create Views (Optional)

Create views for enhanced querying — note this joins public.users (the replica from Step 2), not supasheet.users directly:

-- 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 public.users a ON t.user_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 7: 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 8: 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 9: Create Reports

Create report views — again joining public.users, not supasheet.users directly:

CREATE OR REPLACE VIEW public.task_report
WITH(security_invoker = true) AS
SELECT
    a.name AS account_name,
    t.*
FROM tasks t
JOIN public.users a ON t.user_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 10: 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 11: Verify Access

There's no separate "grant permissions to roles" step — every GRANT in Steps 5–9 already is the access control. This step is just a sanity check that every resource is reachable by the roles you expect:

-- Should return true for "user" on everything a signed-up end-user needs
select has_table_privilege('user', 'public.tasks', 'select');
select has_table_privilege('user', 'public.tasks', 'insert');
select has_table_privilege('user', 'public.user_tasks', 'select');
select has_table_privilege('user', 'public.task_summary', 'select');
select has_table_privilege('user', 'public.task_trend_area', 'select');
select has_table_privilege('user', 'public.task_report', 'select');

If any of these return false and you expected true, go back to the resource's GRANT statement in Steps 5–9 — that's the only place access is decided. There's no permissions table to check instead.

Step 12: Refresh the Meta Layer

supasheet.tables, supasheet.columns, and supasheet.views are materialized views — nothing refreshes them automatically. Finish every migration with:

select supasheet.refresh_metadata();

Skip this and none of the tables, views, dashboard widgets, charts, or reports you just created will show up in the UI, even though the SQL objects and permissions exist.

Key Takeaways

Replica Views Are Required, Not Optional: If your table lives outside supasheet and references supasheet.users (or any table in another schema) via a foreign key, create a same-named replica view in your own schema before anything tries to join/embed it — query.join, fields.lookups, and hand-written view joins all need the target in the same schema to resolve. Skipping this doesn't degrade gracefully — the join silently fails to appear.
No Permission Enum: There's nothing to register before creating resources — access is a direct GRANT on the resource itself, to the specific native Postgres role(s) that should have it
RLS is Required: Every table must have RLS policies — once the grant has decided whether a role can attempt an operation, RLS decides which rows; using (true) is the correct default when there's no row-level rule beyond the grant
Revoke Then Grant: Always revoke all default grants first, then explicitly grant what's needed to each role by name
JSON Comments for Metadata: Use JSON comments on views to identify dashboards, charts, and reports (no separate tables or schemas)
Grant to Roles Directly: A role's access is exactly what GRANT gives it — there's no role_permissions table to seed separately
Security Invoker: Use with(security_invoker = true) for views to respect RLS policies
Audit Everything: Add audit triggers for INSERT, UPDATE, and DELETE operations
Refresh the Meta Layer: Always end a migration with select supasheet.refresh_metadata(); — the meta tables are materialized views, not live introspection

Complete Migration Template

Here's a complete migration template you can use:

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

-- 2. Create replica views for any cross-schema table you'll reference
--    (REQUIRED whenever [table] references supasheet.users or any table
--    outside its own schema — see Step 2 above for why)
create or replace view public.users
with (security_invoker = true) as
select * from supasheet.users;
revoke all on public.users from authenticated, service_role;
grant select on public.users to authenticated;

-- 3. Create table (foreign keys still point at the real table, e.g.
--    `references supasheet.users(id)` — never at the replica view)
CREATE TABLE [table] (...);

-- 4. Revoke default grants, grant exactly what each role needs, enable RLS
revoke all on table [table] from authenticated, service_role;
grant select, insert, update, delete on table [table] to "user";
grant select, insert, update, delete on table [table] to "x-admin";
alter table [table] enable row level security;
create policy [table]_select on [table] for select to authenticated using (true);
-- ... other policies (using (true), or a real row-level condition if rows
-- are per-user-owned — see Authorization)

-- 5. Create views (join the replica view, e.g. public.users, not the
--    cross-schema table directly). Each needs its own grant, to the
--    roles that should see it:
create or replace view public.vw_[table] ...;
revoke all on public.vw_[table] from authenticated, service_role;
grant select on public.vw_[table] to "user";

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

-- 7. Create chart views with metadata (grant + comment each)
create or replace view public.[chart_name] ...;
grant select on public.[chart_name] to "user";
comment on view public.[chart_name] is '{"type": "chart", "name": "...", "description": "...", "chart_type": "line"}';

-- 8. Create report views with metadata (grant + comment each)
create or replace view public.[report_name] ...;
grant select on public.[report_name] to "user";
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();

-- 10. Refresh the meta layer (IMPORTANT!)
-- supasheet.tables/columns/views/materialized_views are materialized views,
-- not live introspection — nothing updates them until you call this.
select supasheet.refresh_metadata();

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

Next Steps

Authorization - Understanding native roles, grants, and RLS
Dashboards - Learn more about widget types
Charts - Explore chart types in detail
Reports - Advanced report features
Audit Logs - Viewing and filtering audit logs

Complete Example

On this page