Database Schema

Schema Organization

Supasheet uses PostgreSQL schemas to organize different types of functionality. This separation makes it easy to understand what data is used for what purpose.

Core Schemas

`public` Schema

The default schema for your application tables. All tables here automatically get CRUD interfaces in Supasheet:

CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title TEXT NOT NULL,
  status TEXT DEFAULT 'pending',

  -- Always reference supasheet.accounts for user relationships
  account_id UUID REFERENCES supasheet.accounts(id) ON DELETE CASCADE,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Important: Always use supasheet.accounts(id) for user relationships, not auth.users(id). See User Management for details.

`supasheet` Schema

Contains Supasheet's internal tables and metadata:

supasheet.accounts - User accounts (auto-created on signup)
supasheet.roles - User roles (admin, user, etc.)
supasheet.role_permissions - Permission assignments
supasheet.tables - Metadata about your tables
supasheet.columns - Metadata about table columns
supasheet.views - Metadata about database views (including dashboards, charts, reports via JSON comments)

Feature Views with JSON Metadata

Instead of separate schemas, Supasheet uses JSON comments to identify special view types:

Charts

Create views in public schema with chart metadata:

-- Example: Task status distribution (pie chart)
CREATE VIEW public.task_status_pie AS
SELECT
  status::text as label,
  COUNT(*) as value
FROM tasks
GROUP BY status;

-- 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 permissions
REVOKE ALL ON public.task_status_pie FROM authenticated, service_role;
GRANT SELECT ON public.task_status_pie TO authenticated;

-- Assign permissions to roles
INSERT INTO supasheet.role_permissions (role, permission)
VALUES ('user', 'public.task_status_pie:select');

Dashboard Widgets

Create views with dashboard widget metadata:

-- Example: Active tasks card
CREATE VIEW public.task_summary AS
SELECT
  COUNT(*) as value,
  'list-todo' as icon,
  'active tasks' as label
FROM tasks
WHERE status != 'completed';

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

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

Dashboard views support different widget types: card_1, card_2, card_3, card_4, table_1, table_2.

Reports

Define views with report metadata:

-- Example: Task report with user details
CREATE VIEW public.task_report AS
SELECT
  a.name as account_name,
  t.*
FROM tasks t
JOIN supasheet.accounts a ON t.account_id = a.id;

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

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

Why Meta Tables?

Supasheet maintains metadata about your database in these tables:

supasheet.tables - Information about all tables
supasheet.columns - Information about all columns
supasheet.views - Information about all views

Schema Configuration

Schemas are exposed via PostgREST API in /supabase/config.toml:

[api]
schemas = ["public", "supasheet"]

The public schema contains all your application tables and views (including those for dashboards, charts, and reports). The system identifies special view types through JSON comment metadata.

Next Steps

CRUD Operations - Work with tables
Charts - Create visualizations
Dashboards - Build dashboards
Reports - Generate reports

Database Schema

On this page