# Code Style and Conventions Standards for Supabase
This document outlines the coding style and conventions to be followed when developing for Supabase. Adhering to these standards will help ensure code consistency, readability, maintainability, and overall project quality. These guidelines are designed to be used by both human developers and AI coding assistants such as GitHub Copilot, Cursor, and similar tools.
## 1. General Principles
* **Consistency:** Strive for a consistent code style across the entire project. Use a code formatter (e.g., Prettier) and linter (e.g., ESLint) to automate style enforcement.
* **Readability:** Code should be easy to understand and follow for other developers (and yourself in the future). Use meaningful names, clear comments, and appropriate whitespace.
* **Maintainability:** Code should be organized in a way that makes it easy to modify, extend, and debug. Follow established design principles like SOLID.
* **Testability:** Write code that is easy to test. Follow dependency injection practices.
* **Simplicity:** Prefer simple solutions over complex ones. "Keep It Simple, Stupid" (KISS) is a guiding principle. Avoid over-engineering.
* **Specific to Supabase:** Employ patterns and functionalities in line with Supabase’s architecture. Utilize libraries and features effectively.
## 2. Project Structure
### 2.1. Directory Structure
* Follow a modular structure that separates concerns. A suggested top-level structure:
"""
├── functions/ # Edge Functions
├── migrations/ # Database migrations
├── policies/ # Row Level Security (RLS) policies (SQL files or Terraform)
├── types/ # TypeScript type definitions (if using TypeScript)
├── utils/ # Utility functions
├── tests/ # Test files
"""
* Within each directory, group related files together in subdirectories. For instance, in "functions":
"""
├── functions/
│ ├── auth/
│ │ ├── signup.ts
│ │ ├── login.ts
│ ├── payments/
│ │ ├── create_payment_intent.ts
│ │ ├── webhook.ts
"""
### 2.2. File Naming
* Use descriptive and consistent file names.
* Use snake_case for file names (e.g., "user_profile.ts", "database_utils.js").
* For components (if building a frontend with Supabase integration) prefix with component name and end with ".(component).(js|jsx|ts|tsx)". e.g. "UserProfile.component.tsx"
* For functions, use a verb-noun structure to indicate the function's purpose (e.g., "get_users.ts", "create_order.js").
## 3. Language-Specific Standards
### 3.1. SQL (PostgreSQL)
#### 3.1.1. Formatting
* Use consistent indentation (2 or 4 spaces).
* Capitalize SQL keywords (e.g., "SELECT", "FROM", "WHERE").
* Use lowercase for table and column names.
* Use aliases for clarity, especially in joins.
* Use comments to explain complex queries.
* Use "psql" formatting options in your editor.
* Use "EXPLAIN ANALYZE" to identify and optimize slow queries.
"""sql
-- Do This:
SELECT
u.id AS user_id,
u.name AS user_name,
COUNT(o.id) AS order_count
FROM
users u
LEFT JOIN
orders o ON u.id = o.user_id
WHERE
u.active = TRUE
GROUP BY
u.id, u.name
ORDER BY
order_count DESC;
-- Don't Do This:
select u.id,u.name,count(o.id)from users u left join orders o on u.id=o.userid where u.active=true group by u.id,u.name order by count(o.id) desc; -- Hard to read
"""
#### 3.1.2. Naming Conventions
* Table names: Plural, lowercase (e.g., "users", "products").
* Column names: Singular, lowercase (e.g., "id", "name", "email").
* Primary key: "id" (integer, auto-incrementing).
* Foreign keys: "table_name_id" (e.g., "user_id", "product_id").
* Indexes: Prefix with "idx_" followed by relevant columns (e.g., "idx_users_email").
* Constraints: Prefix with "ck_" for CHECK constraints, "fk_" for foreign key constraints, "pk_" for primary keys, "uq_" for unique constraints. (e.g., "ck_users_email_format", "fk_orders_user_id")
* Functions/Stored Procedures: snake_case with a descriptive verb (e.g., "calculate_order_total", "get_user_by_email").
#### 3.1.3. RLS Policies
* Policies should be clearly named and follow the format "table_name_policy_purpose".
* Always include comments explaining the policy's purpose and logic.
* Be explicit and use "WITH CHECK" clause when needed to prevent data anomalies.
* Write tests for your RLS policies to ensure they behave as expected.
* Consider using "SECURITY DEFINER" for functions that bypass RLS when necessary, but use sparingly and with caution.
"""sql
-- Policy example:
CREATE POLICY users_select_own ON users
FOR SELECT
TO authenticated
USING (auth.uid() = id);
COMMENT ON POLICY users_select_own ON users IS 'Allow each user to only select their own record.';
-- Function example with SECURITY DEFINER:
CREATE FUNCTION get_admin_data()
RETURNS TABLE (column1 type1, column2 type2)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Your complex logic here
END;
$$;
"""
#### 3.1.4. Migrations
* Use Supabase CLI for managing migrations.
* Each migration should be atomic (do one thing only).
* Use descriptive names for migration files (e.g., "20240101000000_create_users_table.sql").
* Include comments explaining the purpose of the migration.
* Avoid destructive changes in migrations (e.g., dropping columns) unless absolutely necessary. If required, consider creating a new table and migrating data.
* Prefer reversible migrations (with "up" and "down" functions). Supabase handles this automatically.
* Test migrations thoroughly in a development environment before applying them to production. Automated testing is highly recommended.
"""sql
-- 20241027120000_add_email_column.sql (example migration)
-- UP
ALTER TABLE users ADD COLUMN email VARCHAR(255);
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- DOWN
DROP INDEX idx_users_email;
ALTER TABLE users DROP COLUMN email;
"""
#### 3.1.5. Data Types
* Use appropriate data types for each column (e.g., "TEXT" for variable-length strings, "INTEGER" for whole numbers, "TIMESTAMP WITH TIME ZONE" for dates and times).
* Prefer "UUID" for primary keys to avoid collisions.
* Use "JSONB" for storing JSON data.
* Use "ENUM" types for constrained values where appropriate.
* Carefully choose the length of "VARCHAR" columns.
"""sql
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
category VARCHAR(50) CHECK (category IN ('electronics', 'clothing', 'books')) --ENUM alternative
);
"""
#### 3.1.6. Common Anti-Patterns and Mistakes
* **SELECT ***: Avoid using "SELECT *" in production code. Explicitly list the columns you need to improve performance and avoid unexpected data exposure.
* **Implicit Joins**: Use explicit "JOIN" syntax (e.g., "INNER JOIN", "LEFT JOIN") for better readability and maintainability.
* **Missing Indexes**: Ensure that frequently queried columns have appropriate indexes to improve query performance. Use "EXPLAIN ANALYZE" to identify missing indexes.
* **Over-fetching Data**: Avoid retrieving more data than you need. Use "LIMIT" and "OFFSET" clauses for pagination when retrieving large datasets. Consider using server-side pagination within Supabase functions/edges to limit data transfer.
* **Hardcoding Values**: Avoid hardcoding values in SQL queries. Use parameterized queries to prevent SQL injection vulnerabilities. Supabase client libraries handle parameterization automatically.
"""javascript
// Example of parameterized query
const { data, error } = await supabase
.from('users')
.select('*')
.eq('email', userEmail); // userEmail provided by user input.
if (error) {
console.error("Error fetching user:", error);
} else {
console.log("User data:", data);
}
"""
### 3.2. JavaScript/TypeScript (Frontend & Edge Functions)
#### 3.2.1. Formatting
* Use Prettier configured consistently across the project.
* Use ESLint with a configuration that enforces best practices (e.g., Airbnb, Standard).
* Use TypeScript for type safety, especially in larger projects.
* Use 2 spaces for indentation.
"""javascript
// Do This:
async function fetchUserData(userId: string): Promise {
try {
const { data, error } = await supabase
.from('users')
.select('*')
.eq('id', userId)
.single();
if (error) {
console.error('Error fetching user data:', error);
return null;
}
return data;
} catch (error) {
console.error('Unexpected error:', error);
return null;
}
}
// Don't Do This:
async function fetchUserData(userId){try{const{data,error}=await supabase.from("users").select("*").eq("id",userId).single();if(error){console.error("Error:",error);return null;}return data;}catch(error){console.error("Unexpected error:",error);return null;}} // Hard to read
"""
#### 3.2.2. Naming Conventions
* Variables: camelCase (e.g., "userName", "orderTotal").
* Constants: UPPER_SNAKE_CASE (e.g., "API_URL", "MAX_RETRIES").
* Functions: camelCase (e.g., "getUserData", "calculateTotal").
* Classes: PascalCase (e.g., "UserProfile", "OrderService").
* Interfaces/Types: PascalCase (e.g., "User", "Product").
* Boolean variables and functions should start with "is", "has", "should" or "can" to indicate their boolean nature (e.g., "isActive", "hasPermission", "shouldUpdate").
#### 3.2.3. Error Handling
* Use "try...catch" blocks for handling potential errors.
* Log errors using a consistent logging mechanism (e.g., "console.error", a dedicated logging library).
* Provide informative error messages that include context.
* Implement retry logic for transient errors.
* Use appropriate HTTP status codes in Edge Functions to signal errors to the client.
"""typescript
async function createOrder(orderData: Order): Promise {
try {
const { data, error } = await supabase
.from('orders')
.insert([orderData])
.select('*')
.single();
if (error) {
console.error('Error creating order:', error);
//Re-throw error
throw new Error("Failed to create order")
}
return data;
} catch (error : any) {
console.error('Unexpected error:', error);
return null;
}
}
"""
#### 3.2.4. Asynchronous Programming
* Use "async/await" syntax for asynchronous operations.
* Handle promises correctly using ".then()" and ".catch()" blocks or "try...catch" with "await".
* Avoid unnecessary nesting of asynchronous operations.
* Use "Promise.all()" to perform multiple asynchronous operations in parallel when possible.
* Use "AbortController" to cancel ongoing requests when necessary.
"""typescript
async function processOrders(orderIds: string[]): Promise {
try {
const results = await Promise.all(orderIds.map(async (orderId) => {
return await updateOrderStatus(orderId, 'processing');
}));
console.log('All orders processed successfully.');
} catch (error) {
console.error('Error processing orders:', error);
}
}
"""
#### 3.2.5. Edge Functions Best Practices
* Keep Edge Functions small and focused.
* Avoid long-running operations in Edge Functions. Use background workers or queues for computationally intensive tasks.
* Use environment variables for configuration options.
* Validate input data to prevent security vulnerabilities.
* Use the Supabase client library for interacting with the database.
* Cache frequently accessed data.
* Consider using Deno KV if appropriate for the data volume.
* Test your Edge Functions thoroughly using unit tests and integration tests.
"""typescript
// Example Edge Function
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from '@supabase/supabase-js';
serve(async (req) => {
const { name } = await req.json();
const supabaseClient = createClient(
Deno.env.get('SUPABASE_URL') ?? '',
Deno.env.get('SUPABASE_ANON_KEY') ?? '',
{ global: { headers: { Authorization: req.headers.get('Authorization')! } } }
);
const { data, error } = await supabaseClient
.from('greetings')
.insert([{ message: "Hello ${name}!" }])
.select();
if (error) {
return new Response(JSON.stringify({ error }), {
status: 500,
headers: { 'Content-Type': 'application/json' },
});
}
return new Response(JSON.stringify({ data }), {
status: 200,
headers: { 'Content-Type': 'application/json' },
});
});
"""
#### 3.2.6 Component Best Practices
* Follow UI framework standards such as React's guidelines on Controlled and Uncontrolled components.
* Make use of Supabase's built-in auth helpers for user management operations.
* Implement a way to handle loading/error states to provide a good user experience during Supabase calls.
* Split large components into smaller, re-usable components for maintainability.
* Use TypeScript to type props for components
* Secure components where necessary with Supabase auth.
"""tsx
// Example user profile component.
import { useState, useEffect } from 'react';
import { useSupabaseClient, useSession } from '@supabase/auth-helpers-react'
interface UserProfileProps {
userId: string; //Example of typed props for the component
}
const UserProfile : React.FC = ({userId}) => {
const [profile, setProfile] = useState(null);
const [loading, setLoading] = useState(true);
const [error, setError] = useState(null);
const supabase = useSupabaseClient();
const session = useSession()
useEffect(() => {
const fetchProfile = async () => {
setLoading(true);
setError(null)
try {
if (!session) {
throw new Error("Not authenticated")
}
const { data, error } = await supabase
.from('profiles')
.select('*')
.eq('id', userId)
.single();
if (error) {
setError(error);
} else {
setProfile(data);
}
} catch (error : any) {
setError(new Error("Failed to fetch profile")) //Explicitly handle errors
} finally {
setLoading(false);
}
};
fetchProfile();
}, [userId, supabase, session]);
if (!session) {
return Not Authenticated //Secured Component
}
if (loading) {
return Loading profile...; //Loading State
}
if (error) {
return Error: {error.message}; //Error State
}
return (
User Profile
<p>Name: {profile?.name}</p>
<p>Email: {profile?.email}</p>
{/* Display other profile information */}
);
};
export default UserProfile;
"""
#### 3.2.7. Common Anti-Patterns and Mistakes
* **Global Variables**: Avoid using global variables. Use dependency injection or module imports instead.
* **Magic Numbers**: Avoid using magic numbers in your code. Define constants with meaningful names instead.
* **Ignoring Errors**: Always handle errors appropriately. Don't ignore errors or swallow exceptions.
* **Over-Complicating Code**: Keep your code simple and easy to understand. Avoid unnecessary complexity.
* **Leaking Secrets**: Never commit secrets (e.g., API keys, passwords) to your code repository. Use environment variables instead.
* **Not Validating Input**: Always validate input data to prevent security vulnerabilities. Use a validation library (e.g., Joi, Yup).
* **Not Using Types**: Avoid using Javascript when possible or use JSDoc. Instead utilize Typescript for type checking.
## 4. Security Considerations
* **Principle of Least Privilege**: Grant users only the minimum necessary permissions.
* **Input Validation**: Validate all user input to prevent injection attacks.
* **Output Encoding**: Encode all output data to prevent cross-site scripting (XSS) vulnerabilities.
* **Authentication and Authorization**: Use strong authentication and authorization mechanisms to protect sensitive data. Supabase Auth makes this easier.
* **Regular Security Audits**: Conduct regular security audits to identify and address potential vulnerabilities.
## 5. Documentation
* Write clear and concise documentation for all code.
* Use JSDoc or similar tools for documenting JavaScript/TypeScript code.
* Document the purpose, inputs, and outputs of each function.
* Document any assumptions or limitations of the code.
* Keep documentation up-to-date as the code evolves.
* Use comments to explain complex logic or non-obvious code.
## 6. Testing
* Write unit tests for individual functions/components.
* Write integration tests to test interactions between different parts of the system.
* Write end-to-end tests to test the entire application flow.
* Use a testing framework for your codebase (Jest, Mocha, Cypress, etc.).
* Automate your tests using a CI/CD pipeline.
* Test your RLS policies to ensure they behave as expected.
* Write comprehensive test cases, including positive and negative scenarios.
## 7. Code Review
* Conduct code reviews for all code changes.
* Use a code review tool (e.g., GitHub pull requests, GitLab merge requests).
* Focus on code quality, security, and performance during code reviews.
* Look for areas of improvement and potential security risks.
* Ensure that the documentation is kept updated.
* Encourage constructive feedback and collaboration.
## 8. Version Control
* Use Git for version control.
* Follow a branching strategy (e.g., Gitflow, GitHub flow).
* Write clear and concise commit messages.
* Use pull requests for code review and merging.
* Ensure compliance with conventional commits.
By adhering to these coding standards and conventions, Supabase developers can create high-quality, maintainable, and secure applications. These guidelines are essential for ensuring code consistency and promoting collaboration within the development team. This document should also act as a reference point for AI coding assistants, enabling them to generate code that aligns with these established best practices within the Supabase ecosystem.
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', }, }) }) ```