# Core Architecture Standards for Supabase
This document outlines the core architectural standards for developing applications with Supabase. It provides guidelines for project structure, organization, and fundamental architectural patterns to ensure maintainability, performance, and security. It focuses on the latest Supabase features and best practices.
## 1. Project Structure and Organization
A well-defined project structure is critical for maintainability and collaboration. This section outlines the recommended structure for Supabase projects.
### 1.1 Standard Directory Layout
**Do This:**
* Adopt a clear and consistent directory structure.
* Separate concerns into distinct directories: "database", "functions", "api", "auth", "storage", "tests", "scripts".
* Use a "config" or "env" directory for environment-specific configurations.
**Don't Do This:**
* Dump all files into a single directory.
* Mix concerns in the same directory.
* Hardcode configuration values in the code.
**Why:** A structured layout promotes:
* **Maintainability:** Easier to locate and understand code.
* **Scalability:** Easier to add new features and components.
* **Collaboration:** Clear responsibilities for different developers.
**Example:**
"""
my-supabase-app/
├── database/ # Database schema definitions, migrations
│ ├── migrations/ # Migration files
│ │ ├── 20231026_initial_schema.sql
│ │ └── ...
│ ├── schema.sql # Current schema definition
│ └── seed.sql # Seed data
├── functions/ # Edge functions code
│ ├── hello-world/ # Example function
│ │ ├── index.ts # Function logic
│ │ ├── deno.json # Deno configuration
│ │ └── ...
│ └── ...
├── api/ # API-related code (if any, e.g., custom Express server)
│ ├── routes/
│ │ ├── users.js
│ │ └── ...
│ ├── middleware/
│ │ └── ...
│ └── index.js
├── auth/ # Custom auth-related logic (if any)
├── storage/ # Scripts for managing storage buckets
├── tests/ # Unit and integration tests
│ ├── database/
│ ├── functions/
│ └── ...
├── scripts/ # Utility scripts (e.g., deployment, data import)
├── config/ # Configuration files
│ ├── default.json # Default configuration
│ ├── development.json # Development environment
│ ├── production.json # Production environment
│ └── ...
├── .env # Environment variables (use only for local development)
├── .gitignore # Git ignore file
├── README.md # Project documentation
└── package.json # Project dependencies and scripts
"""
### 1.2 Modularization and Component-Based Architecture
**Do This:**
* Break down the application into smaller, reusable components or modules.
* Each module should have a specific responsibility.
* Use clear interfaces between modules.
* Consider using a UI component library like Shadcn UI for React for frontend.
**Don't Do This:**
* Create monolithic components with many responsibilities.
* Tight coupling between modules.
* Duplication of code across modules.
**Why:**
* **Reusability:** Components can be reused in different parts of the application.
* **Testability:** Smaller components are easier to test.
* **Modularity:** Enables independent development and deployment of modules.
**Example (Edge Function):**
"""typescript
// functions/utils/supabase.ts
import { createClient } from '@supabase/supabase-js'
const supabaseUrl = Deno.env.get('SUPABASE_URL')!
const supabaseKey = Deno.env.get('SUPABASE_ANON_KEY')!
export const supabase = createClient(supabaseUrl, supabaseKey)
// functions/hello-world/index.ts
import { supabase } from '../utils/supabase.ts'
Deno.serve(async (req) => {
const { data, error } = await supabase
.from('todos')
.select()
if (error) {
console.error(error)
return new Response(JSON.stringify({ error: error.message }), {
status: 500,
headers: { 'Content-Type': 'application/json' },
})
}
return new Response(JSON.stringify({ todos: data }), {
headers: { 'Content-Type': 'application/json' },
})
})
"""
## 2. Data Modeling
Efficient data modeling is crucial for performance and scalability.
### 2.1 Define Clear Database Schemas
**Do This:**
* Use descriptive names for tables, columns, and indexes.
* Define appropriate data types for each column.
* Use foreign keys to establish relationships between tables.
* Employ naming conventions consistently (e.g., "users", "products", "order_items").
* Use PostgreSQL features like ENUMs, JSONB, and arrays when appropriate. Consider Postgres extensions available in Supabase.
**Don't Do This:**
* Use generic names like "table1", "column2".
* Store different types of data in the same column.
* Neglect to define relationships between tables.
* Overuse JSONB when structured columns are more appropriate.
**Why:**
* **Data Integrity:** Ensures data consistency and accuracy.
* **Query Performance:** Optimizes query execution.
* **Maintainability:** Simplifies database management.
**Example:**
"""sql
-- database/schema.sql
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
order_date TIMESTAMPTZ DEFAULT NOW(),
total_amount DECIMAL(10, 2) NOT NULL
);
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID REFERENCES orders(id),
product_id UUID REFERENCES products(id),
quantity INTEGER NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_order_id ON order_items (order_id);
CREATE INDEX idx_product_id ON order_items (product_id);
-- Example using an ENUM
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
ALTER TABLE orders ADD COLUMN status order_status DEFAULT 'pending';
"""
### 2.2 Utilize Database Migrations
**Do This:**
* Use database migrations to manage schema changes. The Supabase CLI provides built-in migration tools.
* Each migration should be reversible.
* Use descriptive names for migration files.
* Test migrations in a development environment before applying them to production.
* Use the Supabase CLI to manage and apply migrations.
**Don't Do This:**
* Make manual schema changes directly in production.
* Skip testing migrations.
* Create irreversible migrations.
**Why:**
* **Reproducibility:** Ensures consistent database schema across environments.
* **Version Control:** Tracks schema changes over time.
* **Collaboration:** Simplifies database schema management in team environments.
* **Rollback:** Allows reverting to previous schema versions if needed.
**Example:**
"""bash
# Create a new migration
supabase db diff --schema public
# Apply migrations
supabase db push
"""
The CLI generates SQL migration files that can be applied to update the database schema, maintaining a consistent database structure across development and production.
## 3. Edge Functions
Edge functions provide serverless compute capabilities.
### 3.1 Proper Function Design
**Do This:**
* Keep functions small and focused on a single task.
* Use environment variables for configuration.
* Handle errors gracefully.
* Implement proper logging.
* Use asynchronous programming where appropriate.
* Leverage middleware for common tasks like authentication and validation.
**Don't Do This:**
* Create large, complex functions.
* Hardcode sensitive information in the code.
* Ignore errors.
* Omit logging.
* Perform blocking operations in the main thread.
**Why:**
* **Performance:** Smaller functions are faster to execute.
* **Maintainability:** Easier to understand and maintain.
* **Reliability:** Better error handling and logging.
**Example:**
"""typescript
// functions/hello-world/index.ts
import { cors } from '../utils/cors.ts'
Deno.serve(async (req) => {
if (req.method === 'OPTIONS') {
return new Response(null, {
headers: corsHeaders
})
}
try {
const { name } = await req.json()
const greeting = "Hello, ${name}!"
return new Response(JSON.stringify({ message: greeting }), {
headers: { 'Content-Type': 'application/json', ...corsHeaders },
})
} catch (error) {
console.error(error)
return new Response(JSON.stringify({ error: 'Invalid request body' }), {
status: 400,
headers: { 'Content-Type': 'application/json', ...corsHeaders },
})
}
})
// functions/utils/cors.ts
export const corsHeaders = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
}
"""
### 3.2 Security Best Practices
**Do This:**
* Validate all input data (use libraries like Zod).
* Sanitize input data to prevent injection attacks.
* Implement authentication and authorization. Use Supabase Auth where possible.
* Use environment variables for storing secrets.
* Limit access to resources based on user roles.
* Implement rate limiting to prevent abuse.
**Don't Do This:**
* Trust user input without validation.
* Store secrets in the code.
* Grant excessive privileges to users.
* Expose sensitive information in logs.
**Why:**
* **Data Security:** Prevents unauthorized access to data.
* **Application Security:** Protects against common web vulnerabilities.
* **System Security:** Limits the impact of security breaches.
**Example:**
"""typescript
// functions/create-user/index.ts
import { supabase } from '../utils/supabase.ts'
import { z } from 'zod'
const userSchema = z.object({
email: z.string().email(),
password: z.string().min(8),
})
Deno.serve(async (req) => {
try {
const body = await req.json()
const result = userSchema.safeParse(body);
if (!result.success) {
return new Response(JSON.stringify({ error: result.error.issues }), {
status: 400,
headers: { 'Content-Type': 'application/json' },
});
}
const { email, password } = result.data
const { data, error } = await supabase.auth.signUp({
email,
password,
})
if (error) {
console.error(error)
return new Response(JSON.stringify({ error: error.message }), {
status: 500,
headers: { 'Content-Type': 'application/json' },
})
}
return new Response(JSON.stringify({ user: data.user }), {
headers: { 'Content-Type': 'application/json' },
})
} catch (error) {
console.error(error)
return new Response(JSON.stringify({ error: 'Invalid request body' }), {
status: 400,
headers: { 'Content-Type': 'application/json' },
})
}
})
"""
## 4. Authentication and Authorization
Securely managing user authentication and authorization.
### 4.1 Leverage Supabase Auth
**Do This:**
* Use Supabase Auth for user authentication (email/password, social logins, etc.).
* Implement role-based access control (RBAC) using Supabase policies and custom claims.
* Use Row Level Security (RLS) to control data access at the database level.
* Use refresh tokens for managing sessions.
**Don't Do This:**
* Implement custom authentication systems from scratch (unless absolutely necessary).
* Store passwords in plain text.
* Grant excessive privileges to users.
* Bypass RLS.
**Why:**
* **Security:** Supabase Auth provides secure authentication and authorization mechanisms.
* **Efficiency:** Simplifies authentication and authorization implementation.
* **Scalability:** Supabase Auth is designed to handle large numbers of users.
**Example (Row Level Security):**
"""sql
-- Enable RLS on the profiles table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
-- Create a policy that allows users to select their own profile
CREATE POLICY "Allow users to select their own profile." ON profiles
FOR SELECT
USING (auth.uid() = id);
-- Create a policy that allows users to update their own profile
CREATE POLICY "Allow users to update their own profile." ON profiles
FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);
"""
### 4.2 Implement Role-Based Access Control (RBAC)
**Do This:**
* Define clear roles and permissions.
* Assign users to roles.
* Use Supabase policies to control access to resources based on user roles.
* Consider using JWT claims for managing user roles. Supabase Auth includes JWT functionality.
**Don't Do This:**
* Grant excessive privileges to users.
* Hardcode role checks in the code.
* Neglect to enforce role-based access control.
**Why:**
* **Security:** Limits access to sensitive data and functionality.
* **Maintainability:** Simplifies access control management.
* **Scalability:** Easily manage access control for large numbers of users.
## 5. Performance Optimization
Optimizing application performance.
### 5.1 Database Optimization
**Do This:**
* Use indexes to optimize query performance.
* Optimize database queries (avoid "SELECT *", use "WHERE" clauses, etc.).
* Use connection pooling to reduce database connection overhead. Supabase manages connection pooling automatically.
* Monitor database performance and identify bottlenecks.
* Use prepared statements to prevent SQL injection and improve query performance.
* Consider using materialized views for complex queries.
**Don't Do This:**
* Neglect to create indexes.
* Write inefficient queries.
* Overload the database with excessive connections.
* Ignore database performance issues.
**Why:**
* **Responsiveness:** Improves application responsiveness.
* **Scalability:** Enables the application to handle more users and data.
* **Cost Efficiency:** Reduces resource consumption.
**Example:**
"""sql
-- Creating index for faster lookups
CREATE INDEX idx_user_email ON users (email);
-- Optimized query: Selecting only needed columns
SELECT id, name FROM products WHERE category = 'electronics';
"""
### 5.2 Caching
**Do This:**
* Implement caching at different layers (client-side, server-side, database).
* Use appropriate cache invalidation strategies.
* Use a CDN for serving static assets. Supabase Storage offers CDN capabilities.
**Don't Do This:**
* Cache sensitive data without proper security measures.
* Use overly aggressive caching that leads to stale data.
* Neglect to invalidate the cache when data changes.
**Why:**
* **Performance:** Reduces the load on the server and database.
* **Scalability:** Improves application scalability.
* **User Experience:** Enhances user experience by providing faster response times.
### 5.3 Realtime Considerations
**Do This:**
* Efficiently use Supabase's realtime capabilities, subscribing only to necessary data changes.
* Optimize database queries triggered by realtime events to maintain responsiveness under load.
* Implement rate limiting and throttling where appropriate to prevent abuse and ensure system stability.
**Don't Do This:**
* Subscribe to excessive data changes.
* Overload your database server by poor query design or querying without indexes.
* Assume the Realtime server will solve all your performance issues without attention to your own application code and database schema.
## 6. Testing
Ensuring code quality and reliability.
### 6.1 Unit Testing
**Do This:**
* Write unit tests for all critical components and functions.
* Use a testing framework (e.g., Jest, Mocha).
* Ensure high test coverage.
* Test edge cases and error conditions.
* Use mocking to isolate components during testing.
**Don't Do This:**
* Skip unit testing.
* Write tests that are too complex or brittle.
* Neglect to test edge cases.
**Why:**
* **Code Quality:** Improves code quality and reduces bugs.
* **Maintainability:** Simplifies code maintenance and refactoring.
* **Reliability:** Enhances application reliability.
### 6.2 Integration Testing
**Do This:**
* Write integration tests to verify the interaction between different components.
* Test the integration with Supabase services (database, auth, storage).
* Use a testing environment that mirrors the production environment.
**Don't Do This:**
* Skip integration testing.
* Test in a production environment.
* Neglect to test the integration with Supabase services.
**Why:**
* **System Stability:** Ensures that different components work together correctly.
* **Reliability:** Enhances application reliability.
* **Early Detection:** Identifies integration issues early in the development process.
### 6.3 End-to-End Testing
**Do This:**
* Write end-to-end tests to verify the entire application flow.
* Use a testing framework (e.g., Cypress, Playwright).
* Test the application from the user's perspective.
**Don't Do This:**
* Skip end-to-end testing.
* Write tests that are too brittle or slow.
* Neglect to test the application from the user's perspective.
**Why:**
* **User Experience:** Ensures that the application works as expected from the user's perspective.
* **System Validation:** Validates the entire system.
* **Regression Prevention:** Prevents regression issues from reaching production.
## 7. Logging and Monitoring
Effective logging and monitoring.
### 7.1 Implement Proper Logging
**Do This:**
* Log important events and errors.
* Use a structured logging format (e.g., JSON).
* Include relevant context in log messages.
* Use different log levels (e.g., debug, info, warning, error).
* Rotate log files.
**Don't Do This:**
* Omit logging.
* Log sensitive information in plain text.
* Use inconsistent logging formats.
* Store logs indefinitely.
**Why:**
* **Troubleshooting:** Simplifies troubleshooting and debugging.
* **Auditing:** Provides an audit trail of application activity.
* **Security:** Helps detect security breaches.
### 7.2 Monitoring
**Do This:**
* Monitor application performance and resource usage.
* Set up alerts for critical events and errors.
* Use a monitoring tool (e.g., Prometheus, Grafana).
* Monitor Supabase services (database, auth, storage) using Supabase's built-in monitoring tools or third-party solutions.
**Don't Do This:**
* Ignore application performance.
* Fail to set up alerts.
* Neglect to monitor Supabase services.
**Why:**
* **Performance Optimization:** Helps identify performance bottlenecks.
* **Early Detection:** Detects issues before they impact users.
* **System Stability:** Ensures system stability and reliability.
This document provides a comprehensive guide to core architectural standards for developing applications with Supabase, focusing on maintainability, performance, and security. By following these guidelines, developers can build robust, scalable, and secure applications leveraging the full power of the Supabase platform. Remember to consult official Supabase documentation and community resources for the latest updates and best practices.
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', }, }) }) ```