Query Configuration

Overview

Supasheet allows you to configure default queries for your tables using JSON metadata in table comments. This lets you set default sorting, filtering, and which already-related tables to embed by default — the behavior users see when they first open a resource. query is a display-configuration layer on top of your actual SQL schema: it never creates relationships or joins on its own, it only controls how the default list-view query is shaped once those relationships already exist (via foreign keys, in the case of join).

Configuration via Comments

Add query configuration to your table comment using the query property:

COMMENT ON TABLE tasks IS '{
  "icon": "ListTodo",
  "display": "block",
  "query": {
    "sort": [...],
    "filter": [...],
    "join": [...]
  }
}';

Default Sorting

Configure which columns should be sorted by default and in which direction.

Structure

"sort": [
  {
    "id": "column_name",
    "desc": false  // true for descending, false for ascending
  }
]

Example

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  priority TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE tasks IS '{
  "icon": "ListTodo",
  "query": {
    "sort": [
      {"id": "priority", "desc": false},
      {"id": "created_at", "desc": true}
    ]
  }
}';

This will:

Sort by priority (ascending: low → medium → high → urgent)
Then by created_at (descending: newest first)

Common Use Cases

-- Sort by creation date (newest first)
"sort": [{"id": "created_at", "desc": true}]

-- Sort by name alphabetically
"sort": [{"id": "name", "desc": false}]

-- Sort by status, then priority
"sort": [
  {"id": "status", "desc": false},
  {"id": "priority", "desc": false}
]

Default Filters

Pre-filter your data to show specific records by default.

Structure

"filter": [
  {
    "id": "column_name",
    "value": "filter_value",     // string or array of strings
    "operator": "eq"              // comparison operator — see below
  }
]

FilterRule does not have a variant field — the column's underlying data type is looked up automatically from the schema, and only the operators valid for that type are accepted. Filter rule objects are always exactly { id, value, operator }.

Operators by Column Type

Which operator values are valid depends on the target column's type. This is the exact taxonomy Supasheet's filter UI uses (src/config/filters.config.ts):

Text columns:

Operator	Meaning
`ilike`	Contains (case-insensitive)
`not.ilike`	Does not contain
`like`	Starts with / pattern match (case-sensitive)
`eq`	Is
`neq`	Is not
`is`	Is empty
`not.is`	Is not empty

Numeric columns:

Operator	Meaning
`eq`	Is
`neq`	Is not
`lt`	Is less than
`lte`	Is less than or equal to
`gt`	Is greater than
`gte`	Is greater than or equal to
`is`	Is empty
`not.is`	Is not empty

Date / timestamp columns:

Operator	Meaning
`eq`	Is
`neq`	Is not
`lt`	Is before
`gt`	Is after
`lte`	Is on or before
`gte`	Is on or after
`is`	Is empty
`not.is`	Is not empty

Select / enum columns:

Operator	Meaning
`eq`	Is
`neq`	Is not
`is`	Is empty
`not.is`	Is not empty

Multi-select / array columns:

Operator	Meaning
`in`	Has any of
`not.in`	Has none of
`is`	Is empty
`not.is`	Is not empty

Boolean columns:

Operator	Meaning
`is`	Is (true/false)
`not.is`	Is not

UUID columns:

Operator	Meaning
`eq`	Is
`neq`	Is not
`is`	Is empty
`not.is`	Is not empty

There is no between operator. To filter a range (dates, numbers), add two rules — one gte and one lte — against the same column.

Examples

-- Filter by status equals "active"
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "status",
        "value": "active",
        "operator": "eq"
      }
    ]
  }
}';

-- Filter by color contains orange
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "color",
        "value": "#f59e0b",
        "operator": "ilike"
      }
    ]
  }
}';

-- Filter by price greater than 100
COMMENT ON TABLE products IS '{
  "query": {
    "filter": [
      {
        "id": "price",
        "value": "100",
        "operator": "gt"
      }
    ]
  }
}';

-- Filter by multiple conditions
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "status",
        "value": "active",
        "operator": "eq"
      },
      {
        "id": "is_important",
        "value": "true",
        "operator": "is"
      }
    ]
  }
}';

-- Filter by date range (created in 2024 — two rules, no "between" operator)
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "created_at",
        "value": "2024-01-01",
        "operator": "gte"
      },
      {
        "id": "created_at",
        "value": "2024-12-31",
        "operator": "lte"
      }
    ]
  }
}';

-- Filter excluding a value (not equal)
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "status",
        "value": "archived",
        "operator": "neq"
      }
    ]
  }
}';

-- Filter by array column (has any of)
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "tags",
        "value": ["urgent", "bug"],
        "operator": "in"
      }
    ]
  }
}';

-- Filter by empty values
COMMENT ON TABLE tasks IS '{
  "query": {
    "filter": [
      {
        "id": "notes",
        "value": "",
        "operator": "is"
      }
    ]
  }
}';

Default Joins

query.join is a secondary, declarative layer — not the thing that makes two tables joinable in the first place. The real join always comes from a foreign key you define in SQL (REFERENCES), which is what lets PostgREST embed the related table at all (?select=*,related_table!fk_column(cols)). Once that relationship exists, Supasheet already surfaces it elsewhere automatically and independent of any metadata — FK columns render as searchable select dropdowns in forms (see Relations), and related tables show up as their own tabs on the record detail page.

What query.join controls is narrower: which of those already-possible embeds should be pulled into the resource's list/sheet view by default, and which specific columns to show from the related table, so users see them immediately without manually adding columns. Skip it and the relationship still exists and is still usable elsewhere — it just won't appear as extra columns in the default list view.

Structure

"join": [
  {
    "table": "related_table_name",
    "on": "foreign_key_column",
    "columns": ["column1", "column2"]
  }
]

Example

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  user_id UUID REFERENCES supasheet.users(id)
);

COMMENT ON TABLE tasks IS '{
  "icon": "ListTodo",
  "query": {
    "join": [
      {
        "table": "accounts",
        "on": "user_id",
        "columns": ["name", "email"]
      }
    ]
  }
}';

This tells the default list view to:

Embed the already-related accounts table via the user_id foreign key
Pull in name and email from accounts alongside the task's own columns
Display them as accounts.name and accounts.email columns

Multiple Joins

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID REFERENCES customers(id),
  product_id UUID REFERENCES products(id),
  status TEXT
);

COMMENT ON TABLE orders IS '{
  "query": {
    "join": [
      {
        "table": "customers",
        "on": "customer_id",
        "columns": ["name", "email"]
      },
      {
        "table": "products",
        "on": "product_id",
        "columns": ["name", "price"]
      }
    ]
  }
}';

Cross-Schema Joins

Schema limitation: PostgREST resolves joins/embedding within a single schema per request. You cannot directly join a table that lives in a different schema — most commonly this bites you when a table needs to reference supasheet.users (which lives in the supasheet schema) from public or any custom schema like store, desk, crm, etc.

The fix isn't a new table — it's a replica view: create a view with the same name as the table you're referencing (users, in this case) inside your own working schema, pointing at the real table with security_invoker = true so it still respects that user's RLS rather than the view owner's. This is the exact pattern used by every bundled example schema (desk.users, crm.users, store.users, etc. — see supabase/examples/*.sql) for referencing supasheet.users:

-- In your own schema (e.g. store) — not public, and not supasheet:
create or replace view store.users
with (security_invoker = true) as
select * from supasheet.users;

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

Now store.users can be joined/embedded like any ordinary table in that schema:

CREATE TABLE store.tasks (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title TEXT NOT NULL,
  user_id UUID REFERENCES supasheet.users(id)
);

COMMENT ON TABLE store.tasks IS '{
  "query": {
    "join": [
      {
        "table": "users",
        "on": "user_id",
        "columns": ["name", "email"]
      }
    ]
  }
}';

Key points:

The foreign key itself (REFERENCES supasheet.users(id)) can still point cross-schema just fine — Postgres has no problem with that. It's PostgREST's join/embed resolution (and therefore Supasheet's query.join, fields.lookups, and FK dropdown rendering) that needs the target inside the same schema.
Always use WITH (security_invoker = true) — without it, the view runs with the view owner's (usually elevated) privileges instead of the querying user's, silently bypassing RLS.
The replica view must live in the same schema as the table doing the joining, not in public by default — pick whichever schema your table is in.
Reference the view by its own name (users) in join/lookups/relationship config, not the original table's fully-qualified name.
This same limitation and fix apply to fields.lookups and FK dropdown relations described in Relations — anywhere Supasheet needs to embed a cross-schema table.

Joins respect Row Level Security policies. Users will only see joined data they have permission to access.

Default Column Selection

select restricts which columns load by default when the resource first opens — useful for wide tables where most columns aren't needed in the everyday list view.

Structure

"select": ["column1", "column2", "column3"]

Example

COMMENT ON TABLE store.products IS '{
  "icon": "Package",
  "query": {
    "select": ["name", "price", "category", "in_stock"]
  }
}';

Users can still reveal hidden columns from the column-visibility menu — select only changes what's shown (and fetched) by default, it doesn't remove access to the other columns.

Complete Example

Combining all query configuration options:

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title VARCHAR(500) NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'pending',
  priority TEXT DEFAULT 'medium',
  user_id UUID REFERENCES supasheet.users(id),
  color TEXT,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE tasks IS '{
  "icon": "ListTodo",
  "display": "block",
  "query": {
    "select": ["title", "status", "priority", "user_id", "color", "created_at"],
    "sort": [
      {"id": "title", "desc": false}
    ],
    "filter": [
      {
        "id": "color",
        "value": "#f59e0b",
        "operator": "ilike"
      }
    ],
    "join": [
      {
        "table": "accounts",
        "on": "user_id",
        "columns": ["name", "email"]
      }
    ]
  }
}';

When users first open the tasks resource, they will see:

Only the selected columns (title, status, priority, user_id, color, created_at)
Tasks sorted alphabetically by title
Filtered to show only tasks with orange color
With account name and email displayed from the joined accounts table

User Overrides

Users can always modify the query configuration through the UI:

Change sorting by clicking column headers
Add/remove filters using the filter panel
Show/hide joined columns in the column visibility menu

Their preferences are saved in local storage and persist across sessions.

Performance Considerations

Indexing

Add indexes for columns used in default queries:

-- Index for sort column
CREATE INDEX idx_tasks_title ON tasks(title);

-- Index for filter column
CREATE INDEX idx_tasks_color ON tasks(color);

-- Index for join column
CREATE INDEX idx_tasks_user_id ON tasks(user_id);

Join Performance

Only include necessary columns in joins
Ensure foreign key columns are indexed
Consider creating a view for complex joins:

CREATE VIEW tasks_with_accounts AS
SELECT
  t.*,
  a.name as account_name,
  a.email as account_email
FROM tasks t
LEFT JOIN supasheet.users a ON t.user_id = a.id;

-- Use the view as a resource instead
COMMENT ON VIEW tasks_with_accounts IS '{
  "icon": "ListTodo"
}';

Best Practices

Set sensible defaults - Choose sorting and filtering that matches the most common use case
Keep filters simple - Avoid too many default filters that might confuse users
Index everything - Always add indexes for columns used in queries
Test with data - Verify query performance with realistic data volumes
Document choices - Use the table description to explain why certain defaults are set

Next Steps

Views - Configure six view types (Sheet, Kanban, Calendar, Gallery, List, Tree)
Basic Resources - Learn about CRUD interfaces
Data Types - Use custom data types

Query Configuration

On this page