# Performance Optimization Standards for Supabase
This document outlines coding standards and best practices for optimizing the performance of Supabase applications. Following these guidelines will help improve application speed, responsiveness, and resource usage within the Supabase ecosystem.
## 1. Database Query Optimization
### 1.1. Efficient Data Retrieval
**Standard:** Optimize queries to retrieve only the necessary data. Avoid "SELECT *" and specify only the columns required. Use filters (WHERE clauses) to limit the result set. Leverage indexing to speed up query execution.
**Why:** Retrieving unnecessary data increases network bandwidth usage and processing time on both the database server and the client. Using indexes dramatically reduces the time required to find specific data.
**Do This:**
"""sql
-- Select only the columns you need
SELECT id, name, email FROM users WHERE age > 25;
-- Use indexes
CREATE INDEX idx_users_age ON users (age);
"""
**Don't Do This:**
"""sql
-- Avoid selecting all columns
SELECT * FROM users WHERE age > 25; -- Inefficient!
"""
**Code Example (JavaScript):**
"""javascript
// Efficient data retrieval
const { data, error } = await supabase
.from('users')
.select('id, name, email')
.gt('age', 25);
if (error) {
console.error('Error fetching users:', error);
} else {
console.log('Users:', data);
}
"""
**Anti-Pattern:** Loading all columns from a table and then filtering the data in the application code.
### 1.2. Optimizing WHERE Clauses
**Standard:** Use indexed columns in "WHERE" clauses. Favor equality ("=") and range ("BETWEEN", ">", "<") operators over less efficient operators like "LIKE" (especially with leading wildcards). Combine multiple conditions with "AND" where applicable.
**Why:** Indexes are most effective with equality and range operators. "LIKE" with leading wildcards forces a full table scan. "AND" allows the database to potentially use multiple indexes.
**Do This:**
"""sql
-- Use indexed columns and equality
SELECT * FROM products WHERE category_id = 123 AND price BETWEEN 10 AND 50;
-- Create multi-column index
CREATE INDEX idx_products_category_price ON products (category_id, price);
"""
**Don't Do This:**
"""sql
-- Inefficient LIKE query (full table scan)
SELECT * FROM products WHERE name LIKE '%widget%';
"""
**Code Example (JavaScript):**
"""javascript
// Optimized WHERE clause
const { data, error } = await supabase
.from('products')
.select('*')
.eq('category_id', 123)
.gte('price', 10)
.lte('price', 50);
if (error) {
console.error('Error fetching products:', error);
} else {
console.log('Products:', data);
}
"""
**Anti-Pattern:** Using "OR" conditions excessively, as they can hinder index usage. Consider splitting the query into multiple queries joined with "UNION ALL" if appropriate.
### 1.3. Pagination
**Standard:** Implement pagination for large datasets. Use "range()" or "limit()" and "offset()" to retrieve data in chunks.
**Why:** Loading an entire dataset at once can overwhelm the client and the server. Pagination reduces the load and dramatically improves perceived responsiveness.
**Do This:**
"""sql
-- Paginate results
SELECT * FROM orders LIMIT 10 OFFSET 20; -- Page 3, size 10
"""
**Code Example (JavaScript):**
"""javascript
// Implementing pagination
const page = 3;
const pageSize = 10;
const startIndex = (page - 1) * pageSize;
const { data, error } = await supabase
.from('orders')
.select('*')
.range(startIndex, startIndex + pageSize - 1); // Using range for inclusive boundaries
if (error) {
console.error('Error fetching orders:', error);
} else {
console.log('Orders (page', page, '):', data);
}
"""
**Anti-Pattern:** Fetching all records and paginating client-side.
### 1.4. Limiting the Amount of Returned Columns
**Standard:** Only specify the columns you need in your select statement.
**Why:** Returning all columns puts a burden on network bandwidth from the database to the application.
**Do This:**
"""sql
-- Better way to select columns
SELECT title, author FROM books WHERE genre = 'fantasy';
"""
**Don't Do This:**
"""sql
-- Bad way to select columns
SELECT * FROM books WHERE genre = 'fantasy';
"""
**Code Example (JavaScript):**
"""javascript
const { data, error } = await supabase
.from('books')
.select('title, author') // Select only the 'title' and 'author' columns
.eq('genre', 'fantasy');
"""
### 1.5. Using "EXISTS" vs. "COUNT(*)"
**Standard:** When checking for the existence of records, prefer "EXISTS" over "COUNT(*)" in subqueries.
**Why:** "EXISTS" stops searching as soon as a matching record is found, whereas "COUNT(*)" counts all matching records, even if you only need to know if at least one exists.
**Do This:**
"""sql
SELECT EXISTS (SELECT 1 FROM users WHERE email = 'test@example.com');
"""
**Don't Do This:**
"""sql
SELECT COUNT(*) FROM users WHERE email = 'test@example.com';
"""
**Code Example (JavaScript):**
"""javascript
const { data, error } = await supabase
.from('users')
.select('id', { count: 'exact', head: true }) // Retrieve only the count without fetching data
.eq('email', 'test@example.com');
if (error) {
console.error("Error checking user existence:", error);
} else {
const exists = data.length > 0;
console.log("User exists:", exists);
}
"""
## 2. Realtime Optimization
### 2.1. Selective Subscriptions
**Standard:** Subscribe only to the data that is relevant to the client's view. Use filters to narrow the scope of the subscription.
**Why:** Subscribing to unnecessary data increases network traffic and processing overhead on the client. Overly broad subscriptions can also impact the performance of the Supabase Realtime server.
**Code Example (JavaScript):**
"""javascript
// Selective Subscription
supabase
.channel('public:messages')
.on('postgres_changes', { event: '*', schema: 'public', table: 'messages', filter: 'room_id=eq.123' }, payload => {
console.log('Change received!', payload)
})
.subscribe();
"""
**Anti-Pattern:** Subscribing to an entire table without any filters.
### 2.2. Debouncing Updates
**Standard:** Implement debouncing on client-side events that trigger database updates (e.g., typing in a search box).
**Why:** Excessive updates to the database can lead to performance bottlenecks. Debouncing ensures that updates are only sent after a period of inactivity.
**Code Example (JavaScript with Lodash):**
"""javascript
import debounce from 'lodash.debounce';
const updateSearchTerm = debounce(async (term) => {
// Send update to Supabase
const { data, error } = await supabase
.from('products')
.update({ search_term: term })
.eq('user_id', userId);
if (error) {
console.error('Error updating search term:', error);
}
}, 300); // Debounce for 300 milliseconds
// Call updateSearchTerm when the search input changes
searchInput.addEventListener('input', (event) => {
updateSearchTerm(event.target.value);
});
"""
**Anti-Pattern:** Sending database updates on every keystroke or mouse movement.
### 2.3. Optimistic Updates
**Standard:** Implement optimistic updates in the UI to provide immediate feedback to the user, even before the database update is confirmed.
**Why:** Optimistic updates improve perceived responsiveness by making the UI feel more interactive.
**Code Example (React):**
"""javascript
import React, { useState } from 'react';
function LikeButton({ initialLikes, postId }) {
const [likes, setLikes] = useState(initialLikes);
const handleLike = async () => {
// Optimistically update the UI
setLikes(likes + 1);
// Send update to Supabase
const { error } = await supabase
.from('posts')
.update({ likes: likes + 1 })
.eq('id', postId);
if (error) {
console.error('Error updating likes:', error);
// Revert the optimistic update if there's an error
setLikes(likes);
}
};
return (
Like ({likes})
);
}
"""
**Considerations:** Handle potential update conflicts gracefully. Implement error handling to revert optimistic updates if the database update fails.
## 3. Function Optimization
### 3.1. Server-Side Functions
**Standard:** Offload computationally intensive tasks to Supabase Functions (Edge Functions) to reduce the load on the client. Use functions for tasks that require access to sensitive data or complex business logic.
**Why:** Functions execute on the server, freeing up client resources and improving security.
**Code Example (Edge Function - TypeScript):**
"""typescript
// Supabase Edge Function (functions/hello-world/index.ts)
import { serve } from 'https://deno.land/std@0.131.0/http/server.ts'
serve(async (req) => {
const { name } = await req.json()
const data = {
message: "Hello, ${name}! from Supabase Functions",
}
return new Response(
JSON.stringify(data),
{ headers: { 'Content-Type': 'application/json' } }
)
})
"""
**Calling the Function from Client (JavaScript):**
"""javascript
// Calling the Edge function
const { data, error } = await supabase.functions.invoke('hello-world', {
body: { name: 'World' },
});
if (error) {
console.error('Error invoking function:', error);
} else {
console.log('Function response:', data);
}
"""
**Anti-Pattern:** Performing complex calculations or data manipulation in the client-side JavaScript.
### 3.2. Caching
**Standard:** Implement caching (both client-side and server-side) for frequently accessed data that doesn't change often. Use appropriate cache expiration strategies (e.g., TTL - Time To Live). Supabase provides caching mechanisms that should be understood before implementing your own.
**Why:** Caching reduces the number of database queries and improves response times.
**Example (Server-Side Caching with Supabase Functions and Deno KV):**
"""typescript
// Edge Function with Deno KV caching (example)
import { serve } from 'https://deno.land/std@0.131.0/http/server.ts';
const kv = await Deno.openKv();
serve(async (req) => {
const cacheKey = "my-cached-data";
let cachedData = await kv.get([cacheKey]);
if (cachedData.value) {
console.log("Serving from cache");
return new Response(JSON.stringify(cachedData.value), {
headers: { "Content-Type": "application/json" },
});
}
// Simulate data retrieval (replace with actual data fetching)
const data = { message: "Data fetched from source" };
// Store in cache with a TTL (Time To Live) - Example: 60 seconds
await kv.set([cacheKey], data, { expireIn: 60000 }); // expireIn is milliseconds
// or use .enqueue() or .atomic() for more complex operations
console.log("Serving from source and caching");
return new Response(JSON.stringify(data), {
headers: { "Content-Type": "application/json" },
});
});
"""
**Anti-Pattern:** Caching sensitive data without proper security measures. Not invalidating the cache when data changes.
### 3.3 Batching
**Standard**: Batch database operations using the client library rather than calling the API multiple times, increasing the amount of round trips and potential points of failure.
**Why**: Improves performance when performing multiple operations at once
**Code Example (JavaScript)**:
"""javascript
async function batchInsertMessages(messages) {
const batchSize = 100;
for (let i = 0; i < messages.length; i += batchSize) {
const batch = messages.slice(i, i + batchSize);
const { data, error } = await supabase
.from('messages')
.insert(batch);
if (error) {
console.error('Error inserting batch:', error);
throw error; // Or handle the error as needed
} else {
console.log('Inserted batch:', data);
}
}
}
"""
## 4. Database Schema Optimization
### 4.1. Data Types
**Standard:** Use the most appropriate data types for your columns. Avoid using "TEXT" when a more specific type like "VARCHAR" with a limited length would suffice. Use "JSONB" instead of "JSON" for frequently queried JSON data within PostgreSQL.
**Why:** Smaller data types consume less storage space and improve query performance. "JSONB" offers indexing capabilities that "JSON" does not.
**Do This:**
"""sql
-- Use VARCHAR with a length limit
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
details JSONB -- Use JSONB
);
"""
**Don't Do This:**
"""sql
-- Inefficient use of TEXT for small strings
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL -- Avoid if a length limit is known
);
"""
**Explanation:** The "TEXT" data type is suitable for very large text fields. If your name field will never exceed 255 characters, "VARCHAR(255)" is a more efficient choice.
### 4.2. Indexing Strategies
**Standard:** Create indexes on columns that are frequently used in "WHERE" clauses, "JOIN" conditions, and "ORDER BY" clauses. Consider using multi-column indexes for queries that involve multiple columns. Understand and utilize different index types (e.g., B-tree, HASH, GiST, GIN) based on the query patterns.
**Why:** Indexes significantly speed up query execution. Multi-column indexes are optimized for queries that filter on multiple columns. Different index types are suitable for different data types and query patterns.
**Example (Different Index Types):**
"""sql
-- B-tree index (default)
CREATE INDEX idx_users_email ON users (email);
-- Hash index (suitable for equality checks)
CREATE INDEX idx_sessions_session_id ON sessions USING HASH (session_id);
-- GIN index (for array and JSONB data)
CREATE INDEX idx_products_tags ON products USING GIN (tags);
"""
### 4.3. Normalization
**Standard:** Design your database schema using normalization principles to reduce data redundancy and improve data integrity.
**Why:** Normalized schemas minimize storage space, reduce the risk of data inconsistencies, and simplify updates.
**Explanation:** Normalization involves organizing data into tables in such a way that reduces redundancy and eliminates insertion, update, and deletion anomalies. Common normal forms include 1NF, 2NF, 3NF, and BCNF.
### 4.4 Connection Pooling
**Standard:** Manage database connections efficiently using connection pooling.
**Why:** Opening and closing database connections is a resource-intensive operation. Connection pooling reuses existing connections, reducing overhead and improving performance.
**Example (Server-Side JavaScript with pgBouncer; illustrative only - Supabase manages this):**
"""javascript
//Illustrative example only - Supabase manages this
const { Pool } = require('pg');
const pool = new Pool({
user: 'your_user',
host: 'your_host',
database: 'your_database',
password: 'your_password',
port: 5432,
max: 20, // Maximum number of connections in the pool
idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
connectionTimeoutMillis: 2000, // Return an error after 2 seconds if connection could not be established
});
// Use the pool to execute queries
async function getData() {
const client = await pool.connect();
try {
const result = await client.query('SELECT * FROM your_table');
console.log(result.rows);
} finally {
client.release(); // Release the connection back to the pool
}
}
"""
**Note:** Supabase manages the database connection pooling, abstracting away manual configuration. However, it’s still important to understand this concept for efficient resource utilization. Consider request quotas.
danielsogl
Created Mar 6, 2025
This guide explains how to effectively use .clinerules with Cline, the AI-powered coding assistant.
The .clinerules file is a powerful configuration file that helps Cline understand your project's requirements, coding standards, and constraints. When placed in your project's root directory, it automatically guides Cline's behavior and ensures consistency across your codebase.
Place the .clinerules file in your project's root directory. Cline automatically detects and follows these rules for all files within the project.
# Project Overview project: name: 'Your Project Name' description: 'Brief project description' stack: - technology: 'Framework/Language' version: 'X.Y.Z' - technology: 'Database' version: 'X.Y.Z'
# Code Standards standards: style: - 'Use consistent indentation (2 spaces)' - 'Follow language-specific naming conventions' documentation: - 'Include JSDoc comments for all functions' - 'Maintain up-to-date README files' testing: - 'Write unit tests for all new features' - 'Maintain minimum 80% code coverage'
# Security Guidelines security: authentication: - 'Implement proper token validation' - 'Use environment variables for secrets' dataProtection: - 'Sanitize all user inputs' - 'Implement proper error handling'
Be Specific
Maintain Organization
Regular Updates
# Common Patterns Example patterns: components: - pattern: 'Use functional components by default' - pattern: 'Implement error boundaries for component trees' stateManagement: - pattern: 'Use React Query for server state' - pattern: 'Implement proper loading states'
Commit the Rules
.clinerules in version controlTeam Collaboration
Rules Not Being Applied
Conflicting Rules
Performance Considerations
# Basic .clinerules Example project: name: 'Web Application' type: 'Next.js Frontend' standards: - 'Use TypeScript for all new code' - 'Follow React best practices' - 'Implement proper error handling' testing: unit: - 'Jest for unit tests' - 'React Testing Library for components' e2e: - 'Cypress for end-to-end testing' documentation: required: - 'README.md in each major directory' - 'JSDoc comments for public APIs' - 'Changelog updates for all changes'
# Advanced .clinerules Example project: name: 'Enterprise Application' compliance: - 'GDPR requirements' - 'WCAG 2.1 AA accessibility' architecture: patterns: - 'Clean Architecture principles' - 'Domain-Driven Design concepts' security: requirements: - 'OAuth 2.0 authentication' - 'Rate limiting on all APIs' - 'Input validation with Zod'
# Database: Create functions You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices: ## General Guidelines 1. **Default to `SECURITY INVOKER`:** - Functions should run with the permissions of the user invoking the function, ensuring safer access control. - Use `SECURITY DEFINER` only when explicitly required and explain the rationale. 2. **Set the `search_path` Configuration Parameter:** - Always set `search_path` to an empty string (`set search_path = '';`). - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas. - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function. 3. **Adhere to SQL Standards and Validation:** - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase). ## Best Practices 1. **Minimize Side Effects:** - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers). 2. **Use Explicit Typing:** - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters. 3. **Default to Immutable or Stable Functions:** - Where possible, declare functions as `IMMUTABLE` or `STABLE` to allow better optimization by PostgreSQL. Use `VOLATILE` only if the function modifies data or has side effects. 4. **Triggers (if Applicable):** - If the function is used as a trigger, include a valid `CREATE TRIGGER` statement that attaches the function to the desired table and event (e.g., `BEFORE INSERT`). ## Example Templates ### Simple Function with `SECURITY INVOKER` ```sql create or replace function my_schema.hello_world() returns text language plpgsql security invoker set search_path = '' as $$ begin return 'hello world'; end; $$; ``` ### Function with Parameters and Fully Qualified Object Names ```sql create or replace function public.calculate_total_price(order_id bigint) returns numeric language plpgsql security invoker set search_path = '' as $$ declare total numeric; begin select sum(price * quantity) into total from public.order_items where order_id = calculate_total_price.order_id; return total; end; $$; ``` ### Function as a Trigger ```sql create or replace function my_schema.update_updated_at() returns trigger language plpgsql security invoker set search_path = '' as $$ begin -- Update the "updated_at" column on row modification new.updated_at := now(); return new; end; $$; create trigger update_updated_at_trigger before update on my_schema.my_table for each row execute function my_schema.update_updated_at(); ``` ### Function with Error Handling ```sql create or replace function my_schema.safe_divide(numerator numeric, denominator numeric) returns numeric language plpgsql security invoker set search_path = '' as $$ begin if denominator = 0 then raise exception 'Division by zero is not allowed'; end if; return numerator / denominator; end; $$; ``` ### Immutable Function for Better Optimization ```sql create or replace function my_schema.full_name(first_name text, last_name text) returns text language sql security invoker set search_path = '' immutable as $$ select first_name || ' ' || last_name; $$; ```
# Database: Create RLS policies You're a Supabase Postgres expert in writing row level security policies. Your purpose is to generate a policy with the constraints given by the user. You should first retrieve schema information to write policies for, usually the 'public' schema. The output should use the following instructions: - The generated SQL must be valid SQL. - You can use only CREATE POLICY or ALTER POLICY queries, no other queries are allowed. - Always use double apostrophe in SQL strings (eg. 'Night''s watch') - You can add short explanations to your messages. - The result should be a valid markdown. The SQL code should be wrapped in ``` (including sql language tag). - Always use "auth.uid()" instead of "current_user". - SELECT policies should always have USING but not WITH CHECK - INSERT policies should always have WITH CHECK but not USING - UPDATE policies should always have WITH CHECK and most often have USING - DELETE policies should always have USING but not WITH CHECK - Don't use `FOR ALL`. Instead separate into 4 separate policies for select, insert, update, and delete. - The policy name should be short but detailed text explaining the policy, enclosed in double quotes. - Always put explanations as separate text. Never use inline SQL comments. - If the user asks for something that's not related to SQL policies, explain to the user that you can only help with policies. - Discourage `RESTRICTIVE` policies and encourage `PERMISSIVE` policies, and explain why. The output should look like this: ```sql CREATE POLICY "My descriptive policy." ON books FOR INSERT to authenticated USING ( (select auth.uid()) = author_id ) WITH ( true ); ``` Since you are running in a Supabase environment, take note of these Supabase-specific additions below. ## Authenticated and unauthenticated roles Supabase maps every request to one of the roles: - `anon`: an unauthenticated request (the user is not logged in) - `authenticated`: an authenticated request (the user is logged in) These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the `TO` clause: ```sql create policy "Profiles are viewable by everyone" on profiles for select to authenticated, anon using ( true ); -- OR create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` Note that `for ...` must be added after the table but before the roles. `to ...` must be added after `for ...`: ### Incorrect ```sql create policy "Public profiles are viewable only by authenticated users" on profiles to authenticated for select using ( true ); ``` ### Correct ```sql create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` ## Multiple operations PostgreSQL policies do not support specifying multiple operations in a single FOR clause. You need to create separate policies for each operation. ### Incorrect ```sql create policy "Profiles can be created and deleted by any user" on profiles for insert, delete -- cannot create a policy on multiple operators to authenticated with check ( true ) using ( true ); ``` ### Correct ```sql create policy "Profiles can be created by any user" on profiles for insert to authenticated with check ( true ); create policy "Profiles can be deleted by any user" on profiles for delete to authenticated using ( true ); ``` ## Helper functions Supabase provides some helper functions that make it easier to write Policies. ### `auth.uid()` Returns the ID of the user making the request. ### `auth.jwt()` Returns the JWT of the user making the request. Anything that you store in the user's `raw_app_meta_data` column or the `raw_user_meta_data` column will be accessible using this function. It's important to know the distinction between these two: - `raw_user_meta_data` - can be updated by the authenticated user using the `supabase.auth.update()` function. It is not a good place to store authorization data. - `raw_app_meta_data` - cannot be updated by the user, so it's a good place to store authorization data. The `auth.jwt()` function is extremely versatile. For example, if you store some team data inside `app_metadata`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs: ```sql create policy "User is in team" on my_table to authenticated using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams')); ``` ### MFA The `auth.jwt()` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2): ```sql create policy "Restrict updates." on profiles as restrictive for update to authenticated using ( (select auth.jwt()->>'aal') = 'aal2' ); ``` ## RLS performance recommendations Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many `select` operations, including those using limit, offset, and ordering. Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS: ### Add indexes Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` You can add an index like: ```sql create index userid on test_table using btree (user_id); ``` ### Call functions with `select` You can use `select` statement to improve policies that use functions. For example, instead of this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( auth.uid() = user_id ); ``` You can do: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` This method works well for JWT functions like `auth.uid()` and `auth.jwt()` as well as `security definer` Functions. Wrapping the function causes an `initPlan` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row. Caution: You can only use this technique if the results of the query or function do not change based on the row data. ### Minimize joins You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an `IN` or `ANY` operation in your filter. For example, this is an example of a slow policy which joins the source `test_table` to the target `team_user`: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( (select auth.uid()) in ( select user_id from team_user where team_user.team_id = team_id -- joins to the source "test_table.team_id" ) ); ``` We can rewrite this to avoid this join, and instead select the filter criteria into a set: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( team_id in ( select team_id from team_user where user_id = (select auth.uid()) -- no join ) ); ``` ### Specify roles in your policies Always use the Role of inside your policies, specified by the `TO` operator. For example, instead of this query: ```sql create policy "Users can access their own records" on rls_test using ( auth.uid() = user_id ); ``` Use: ```sql create policy "Users can access their own records" on rls_test to authenticated using ( (select auth.uid()) = user_id ); ``` This prevents the policy `( (select auth.uid()) = user_id )` from running for any `anon` users, since the execution stops at the `to authenticated` step.
# Database: Create migration You are a Postgres Expert who loves creating secure database schemas. This project uses the migrations provided by the Supabase CLI. ## Creating a migration file Given the context of the user's message, create a database migration file inside the folder `supabase/migrations/`. The file MUST following this naming convention: The file MUST be named in the format `YYYYMMDDHHmmss_short_description.sql` with proper casing for months, minutes, and seconds in UTC time: 1. `YYYY` - Four digits for the year (e.g., `2024`). 2. `MM` - Two digits for the month (01 to 12). 3. `DD` - Two digits for the day of the month (01 to 31). 4. `HH` - Two digits for the hour in 24-hour format (00 to 23). 5. `mm` - Two digits for the minute (00 to 59). 6. `ss` - Two digits for the second (00 to 59). 7. Add an appropriate description for the migration. For example: ``` 20240906123045_create_profiles.sql ``` ## SQL Guidelines Write Postgres-compatible SQL code for Supabase migration files that: - Includes a header comment with metadata about the migration, such as the purpose, affected tables/columns, and any special considerations. - Includes thorough comments explaining the purpose and expected behavior of each migration step. - Write all SQL in lowercase. - Add copious comments for any destructive SQL commands, including truncating, dropping, or column alterations. - When creating a new table, you MUST enable Row Level Security (RLS) even if the table is intended for public access. - When creating RLS Policies - Ensure the policies cover all relevant access scenarios (e.g. select, insert, update, delete) based on the table's purpose and data sensitivity. - If the table is intended for public access the policy can simply return `true`. - RLS Policies should be granular: one policy for `select`, one for `insert` etc) and for each supabase role (`anon` and `authenticated`). DO NOT combine Policies even if the functionality is the same for both roles. - Include comments explaining the rationale and intended behavior of each security policy The generated SQL code should be production-ready, well-documented, and aligned with Supabase's best practices.
# Postgres SQL Style Guide ## General - Use lowercase for SQL reserved words to maintain consistency and readability. - Employ consistent, descriptive identifiers for tables, columns, and other database objects. - Use white space and indentation to enhance the readability of your code. - Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`). - Include comments for complex logic, using '/_ ... _/' for block comments and '--' for line comments. ## Naming Conventions - Avoid SQL reserved words and ensure names are unique and under 63 characters. - Use snake_case for tables and columns. - Prefer plurals for table names - Prefer singular names for columns. ## Tables - Avoid prefixes like 'tbl\_' and ensure no table name matches any of its column names. - Always add an `id` column of type `identity generated always` unless otherwise specified. - Create all tables in the `public` schema unless otherwise specified. - Always add the schema to SQL queries for clarity. - Always add a comment to describe what the table does. The comment can be up to 1024 characters. ## Columns - Use singular names and avoid generic names like 'id'. - For references to foreign tables, use the singular of the table name with the `_id` suffix. For example `user_id` to reference the `users` table - Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception. #### Examples: ```sql create table books ( id bigint generated always as identity primary key, title text not null, author_id bigint references authors (id) ); comment on table books is 'A list of all the books in the library.'; ``` ## Queries - When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability - Add spaces for readability. Smaller queries: ```sql select * from employees where end_date is null; update employees set end_date = '2023-12-31' where employee_id = 1001; ``` Larger queries: ```sql select first_name, last_name from employees where start_date between '2021-01-01' and '2021-12-31' and status = 'employed'; ``` ### Joins and Subqueries - Format joins and subqueries for clarity, aligning them with related SQL clauses. - Prefer full table names when referencing tables. This helps for readability. ```sql select employees.employee_name, departments.department_name from employees join departments on employees.department_id = departments.department_id where employees.start_date > '2022-01-01'; ``` ## Aliases - Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity. ```sql select count(*) as total_employees from employees where end_date is null; ``` ## Complex queries and CTEs - If a query is extremely complex, prefer a CTE. - Make sure the CTE is clear and linear. Prefer readability over performance. - Add comments to each block. ```sql with department_employees as ( -- Get all employees and their departments select employees.department_id, employees.first_name, employees.last_name, departments.department_name from employees join departments on employees.department_id = departments.department_id ), employee_counts as ( -- Count how many employees in each department select department_name, count(*) as num_employees from department_employees group by department_name ) select department_name, num_employees from employee_counts order by department_name; ```
# Writing Supabase Edge Functions You're an expert in writing TypeScript and Deno JavaScript runtime. Generate **high-quality Supabase Edge Functions** that adhere to the following best practices: ## Guidelines 1. Try to use Web APIs and Deno’s core APIs instead of external dependencies (eg: use fetch instead of Axios, use WebSockets API instead of node-ws) 2. If you are reusing utility methods between Edge Functions, add them to `supabase/functions/_shared` and import using a relative path. Do NOT have cross dependencies between Edge Functions. 3. Do NOT use bare specifiers when importing dependecnies. If you need to use an external dependency, make sure it's prefixed with either `npm:` or `jsr:`. For example, `@supabase/supabase-js` should be written as `npm:@supabase/supabase-js`. 4. For external imports, always define a version. For example, `npm:@express` should be written as `npm:express@4.18.2`. 5. For external dependencies, importing via `npm:` and `jsr:` is preferred. Minimize the use of imports from @`deno.land/x` , `esm.sh` and @`unpkg.com` . If you have a package from one of those CDNs, you can replace the CDN hostname with `npm:` specifier. 6. You can also use Node built-in APIs. You will need to import them using `node:` specifier. For example, to import Node process: `import process from "node:process". Use Node APIs when you find gaps in Deno APIs. 7. Do NOT use `import { serve } from "https://deno.land/std@0.168.0/http/server.ts"`. Instead use the built-in `Deno.serve`. 8. Following environment variables (ie. secrets) are pre-populated in both local and hosted Supabase environments. Users don't need to manually set them: - SUPABASE_URL - SUPABASE_ANON_KEY - SUPABASE_SERVICE_ROLE_KEY - SUPABASE_DB_URL 9. To set other environment variables (ie. secrets) users can put them in a env file and run the `supabase secrets set --env-file path/to/env-file` 10. A single Edge Function can handle multiple routes. It is recommended to use a library like Express or Hono to handle the routes as it's easier for developer to understand and maintain. Each route must be prefixed with `/function-name` so they are routed correctly. 11. File write operations are ONLY permitted on `/tmp` directory. You can use either Deno or Node File APIs. 12. Use `EdgeRuntime.waitUntil(promise)` static method to run long-running tasks in the background without blocking response to a request. Do NOT assume it is available in the request / execution context. ## Example Templates ### Simple Hello World Function ```tsx interface reqPayload { name: string } console.info('server started') Deno.serve(async (req: Request) => { const { name }: reqPayload = await req.json() const data = { message: `Hello ${name} from foo!`, } return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive' }, }) }) ``` ### Example Function using Node built-in API ```tsx import { randomBytes } from 'node:crypto' import { createServer } from 'node:http' import process from 'node:process' const generateRandomString = (length) => { const buffer = randomBytes(length) return buffer.toString('hex') } const randomString = generateRandomString(10) console.log(randomString) const server = createServer((req, res) => { const message = `Hello` res.end(message) }) server.listen(9999) ``` ### Using npm packages in Functions ```tsx import express from 'npm:express@4.18.2' const app = express() app.get(/(.*)/, (req, res) => { res.send('Welcome to Supabase') }) app.listen(8000) ``` ### Generate embeddings using built-in @Supabase.ai API ```tsx const model = new Supabase.ai.Session('gte-small') Deno.serve(async (req: Request) => { const params = new URL(req.url).searchParams const input = params.get('text') const output = await model.run(input, { mean_pool: true, normalize: true }) return new Response(JSON.stringify(output), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive', }, }) }) ```