Database Schema

Schema Organisation

Supasheet uses PostgreSQL schemas to separate concerns. You build your application in your own schemas (one per domain) and let Supasheet introspect them through its supasheet meta schema.

Your Application Schemas

Create one schema per domain. The example seed files in supabase/examples/ follow this pattern (desk, crm, hr, finance, store, blog, inventory, manufacturing, procurement, quality, lms, hostel).

CREATE SCHEMA IF NOT EXISTS desk;

CREATE TABLE desk.tasks (
  id          UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title       TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  -- Always reference supasheet.users for user relationships
  user_id     UUID REFERENCES supasheet.users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

The supasheet Schema

The internal schema that powers everything. Created by the migrations in supabase/migrations/.

Refreshing the Meta Layer

supasheet.tables, supasheet.columns, supasheet.views, and supasheet.materialized_views are materialized views — they're a cached snapshot of your schema, not a live introspection query. There are no event triggers keeping them in sync automatically. After any DDL change — CREATE/ALTER/DROP TABLE, VIEW, or MATERIALIZED VIEW, a schema change, an enum change (ALTER TYPE ... ADD VALUE), or a COMMENT ON ... — you must refresh them yourself:

select supasheet.refresh_metadata();

refresh_metadata() is defined in 00000000000000_meta.sql and simply re-runs REFRESH MATERIALIZED VIEW for all four meta views:

create or replace function supasheet.refresh_metadata () returns void as $$
BEGIN
    REFRESH MATERIALIZED VIEW supasheet.tables;
    REFRESH MATERIALIZED VIEW supasheet.columns;
    REFRESH MATERIALIZED VIEW supasheet.views;
    REFRESH MATERIALIZED VIEW supasheet.materialized_views;
END;
$$ LANGUAGE plpgsql;

EXECUTE on this function is revoked from anon/authenticated/service_role by default — only the database owner can call it. In practice that means running it from the Supabase SQL Editor, the CLI (supabase db reset / db push re-run all migrations including the final refresh materialized view calls in 00000000000000_meta.sql, so a fresh migration run is always in sync), or as the last statement in your own migration file.

Together with the permission-aware functions supasheet.get_schemas(), get_tables(), get_views(), get_columns(), and get_related_tables(), these cached meta views are how the UI discovers what to render.

Feature Views via JSON Comments

Dashboards, charts, reports, and templates are not separate tables — they are views whose comment identifies them by type. Supasheet ships discovery functions that filter supasheet.views for each type:

Example

-- A pie chart of task statuses
CREATE VIEW desk.task_status_pie AS
SELECT
  status::text AS label,
  COUNT(*)::int AS value
FROM desk.tasks
GROUP BY status;

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

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

INSERT INTO supasheet.role_permissions (role, permission) VALUES
  ('user', 'desk.task_status_pie:select');

-- Required after creating the view and setting its comment
select supasheet.refresh_metadata();

Exposing Schemas via PostgREST

For Supasheet to query a schema through the publishable key, it must be exposed in supabase/config.toml:

[api]
schemas = ["public", "supasheet", "desk", "crm", "hr"]
extra_search_path = ["public", "extensions", "supasheet", "desk", "crm", "hr"]

After editing the config:

# Local
npx supabase stop && npx supabase start

# Cloud — set "Exposed schemas" in Project Settings → Data API

Next Steps

• Authorization — Grant permissions per schema/table
• Resource Basics — Build CRUD on top of your tables
• Charts — Build a chart from a view
• Reports — Build a report from a view
• Templates — Reuse preset data via templates