# Deployment and DevOps Standards for Supabase
This document outlines the deployment and DevOps standards for Supabase projects. It aims to provide development teams and AI coding assistants with a comprehensive guide to building, deploying, and maintaining Supabase applications in a professional and efficient manner. These standards prioritize maintainability, performance, security, and scalability, adhering to modern DevOps practices.
## 1. Infrastructure as Code (IaC)
### 1.1 Standard: Define infrastructure using code.
* **Do This:** Use tools like Terraform, Pulumi, or AWS CloudFormation to define your Supabase infrastructure.
* **Don't Do This:** Manually configure infrastructure through the Supabase dashboard or cloud provider consoles.
**Why:** IaC allows for version control, automated deployments, and reproducible environments. It reduces the risk of human error and makes it easier to scale and manage your infrastructure.
Supabase provides excellent documentation, examples, and tutorials for using these tools for project management.
**Example (Terraform):**
"""terraform
# main.tf
terraform {
required_providers {
supabase = {
source = "supabase/supabase"
version = "~> 0.1.0" #specify the Supabase provider version here
}
}
}
provider "supabase" {
supabase_url = var.supabase_url
supabase_key = var.supabase_key
}
#Create a Database branch
resource "supabase_database_branch" "main" {
name = "main"
}
#Example function using edge functions
resource "supabase_function" "hello_world" {
name = "hello-world"
entrypoint = "index.ts"
runtime = "deno-1.x"
project_id = var.supabase_project_id #get this from Supabase Project Settings
database_branch = supabase_database_branch.main.name
content = file("./supabase/functions/hello-world/index.ts")
}
output "function_url" {
value = supabase_function.hello_world.url
}
"""
**Anti-pattern:** Manual infrastructure changes lead to inconsistencies between environments and make disaster recovery difficult.
### 1.2 Standard: Version control IaC configurations.
* **Do This:** Store your Terraform configurations, Pulumi programs, or CloudFormation templates in a version control system like Git.
* **Don't Do This:** Keep infrastructure configurations on local machines or shared drives.
**Why:** Version control provides a history of changes, allows for collaboration, and makes it easy to revert to previous configurations if necessary.
**Example (Git):**
"""bash
git init
git add .
git commit -m "Initial commit of Terraform configuration"
git push origin main
"""
**Anti-pattern:** Lack of version control for IaC can lead to configuration drift and make it difficult to track changes or roll back to previous states.
## 2. Continuous Integration and Continuous Deployment (CI/CD)
### 2.1 Standard: Automate build, test, and deployment processes.
* **Do This:** Use CI/CD pipelines to automate the build, test, and deployment of your Supabase applications and infrastructure. Tools like GitHub Actions, GitLab CI, CircleCI, or Jenkins are recommended.
* **Don't Do This:** Manually build, test, and deploy your applications.
**Why:** CI/CD automates repetitive tasks, reduces the risk of human error, and enables faster and more frequent releases.
**Example (GitHub Actions):**
"""yaml
# .github/workflows/deploy.yml
name: Deploy to Production
on:
push:
branches:
- main #Or your production branch
jobs:
deploy:
runs-on: ubuntu-latest
steps:
- name: Checkout code
uses: actions/checkout@v3
- name: Setup Node.js
uses: actions/setup-node@v3
with:
node-version: '18'
- name: Install dependencies
run: npm ci
- name: Run tests
run: npm test
- name: Build application
run: npm run build
#Example deploy step using the Supabase CLI deploying Edge Functions
- name: Deploy to Supabase
uses: supabase/setup-cli@v1
with:
version: latest # Or pin the version
- name: Supabase deploy
run: supabase functions deploy --project-id $SUPABASE_PROJECT_ID --api-key $SUPABASE_API_KEY
env:
SUPABASE_PROJECT_ID: ${{ secrets.SUPABASE_PROJECT_ID }}
SUPABASE_API_KEY: ${{ secrets.SUPABASE_API_KEY }}
#Example deploy step using the Supabase CLI deploying Storage
- name: Deploy Supabase Storage
run: supabase storage upload "path/to/your/storage/directory" --project-id $SUPABASE_PROJECT_ID --api-key $SUPABASE_API_KEY
env:
SUPABASE_PROJECT_ID: ${{ secrets.SUPABASE_PROJECT_ID }}
SUPABASE_API_KEY: ${{ secrets.SUPABASE_API_KEY }}
"""
**Anti-pattern:** Manual deployments are error-prone, time-consuming, and make it difficult to maintain consistent releases.
### 2.2 Standard: Implement automated testing.
* **Do This:** Include unit tests, integration tests, and end-to-end tests in your CI/CD pipeline. Use testing frameworks appropriate for your chosen language (e.g., Jest for JavaScript).
* **Don't Do This:** Deploy code without adequate testing.
**Why:** Automated testing helps catch bugs early, reduces the risk of regressions, and improves the overall quality of your code.
**Example (Jest):**
"""javascript
// Example unit test
test('adds 1 + 2 to equal 3', () => {
expect(1 + 2).toBe(3);
});
"""
**Anti-pattern:** Insufficient testing leads to unstable releases and increases the risk of introducing bugs into production.
### 2.3 Standard: Use environment variables for configuration.
* **Do This:** Store configuration values (e.g., database connection strings, API keys) in environment variables. Use tools like ".env" files (for local development) and secrets management solutions (for production). In CI/CD pipelines, manage secrets through the CI/CD provider's secret management feature
* **Don't Do This:** Hardcode configuration values in your code or configuration files.
**Why:** Environment variables make it easy to configure your application for different environments (e.g., development, staging, production) without modifying the code. They also prevent sensitive information from being exposed in your codebase.
**Example (.env):**
"""
DATABASE_URL=postgres://user:password@host:port/database
API_KEY=your_api_key
"""
**Example (Accessing environment variables in JavaScript):**
"""javascript
const databaseUrl = process.env.DATABASE_URL;
const apiKey = process.env.API_KEY;
"""
**Anti-pattern:** Hardcoding configuration values makes it difficult to manage different environments and exposes sensitive information.
## 3. Environment Management
### 3.1 Standard: Use multiple environments (development, staging, production).
* **Do This:** Set up separate environments for development, staging, and production. Use a CI/CD pipeline to deploy code to each environment.
* **Don't Do This:** Deploy directly to production without testing in a staging environment.
**Why:** Multiple environments allow you to test changes in a controlled environment before deploying them to production, reducing the risk of downtime or data loss.
**Example (CI/CD pipeline):**
1. Code changes are pushed to a feature branch.
2. The CI/CD pipeline runs unit tests and integration tests.
3. If the tests pass, the code is deployed to the development environment.
4. After testing in the development environment, the code is merged into the staging branch.
5. The CI/CD pipeline deploys the code to the staging environment.
6. After UAT (User Acceptance Testing) in the staging environment, the code is merged into the main branch.
7. The CI/CD pipeline deploys the code to the production environment.
**Anti-pattern:** Deploying directly to production without proper testing increases the risk of introducing bugs and disrupting users.
### 3.2 Standard: Maintain consistency between environments.
* **Do This:** Use IaC tools to ensure that your environments are configured consistently. Use seed data or database migrations to keep the database schema and data in sync across environments.
* **Don't Do This:** Allow environments to drift out of sync, as this can lead to unexpected behavior in production.
**Why:** Consistent environments make it easier to test changes and reduce the risk of environment-specific bugs.
**Example (Database migrations - using Supabase CLI) :**
"""bash
# Create a new migration
supabase db diff --schema public
#Apply migrations (locally or in CI/CD)
supabase db push
"""
**Anti-pattern:** Inconsistent environments make it difficult to test changes and can lead to unexpected behavior in production.
## 4. Monitoring and Logging
### 4.1 Standard: Implement comprehensive monitoring.
* **Do This:** Use monitoring tools to track the performance and health of your Supabase applications and infrastructure. Monitor metrics such as CPU usage, memory usage, network traffic, and database query performance. Supabase provides built in monitoring features, as well as integration with tools like Prometheus and Grafana.
* **Don't Do This:** Rely solely on logs for troubleshooting issues.
**Why:** Monitoring allows you to proactively identify and address performance bottlenecks and potential issues before they impact users.
**Example (Prometheus and Grafana):**
1. Configure your Supabase application to expose metrics in Prometheus format.
2. Install and configure Prometheus to scrape these metrics.
3. Install and configure Grafana to visualize the metrics.
**Anti-pattern:** Lack of monitoring makes it difficult to identify and address performance bottlenecks or potential issues.
### 4.2 Standard: Centralize logging.
* **Do This:** Use a centralized logging system (e.g., ELK stack, Splunk, Datadog) to collect and analyze logs from your Supabase applications and infrastructure.
* **Don't Do This:** Rely on individual log files on servers.
**Why:** Centralized logging makes it easier to search, analyze, and correlate logs from different sources, helping you troubleshoot issues more effectively.
**Example (ELK Stack):**
1. Configure your application to send logs to Elasticsearch.
2. Install and configure Logstash to process and enrich the logs.
3. Install and configure Kibana to visualize the logs.
**Anti-pattern:** Fragmented logging makes it difficult to troubleshoot issues and can lead to important information being missed.
### 4.3 Standard: Implement alerting.
* **Do This:** Set up alerts to notify you when critical events occur, such as high error rates, slow response times, or security breaches.
* **Don't Do This:** Manually monitor logs and metrics for potential issues.
**Why:** Alerting allows you to respond quickly to critical events and minimize the impact on users.
**Example (Setting alerts in Grafana):**
Grafana allows you to set alerts based on metric thresholds. You can configure alerts to send notifications via email, Slack, or other channels.
**Anti-pattern:** Lack of alerting can lead to delayed responses to critical events, resulting in downtime or data loss.
## 5. Security
### 5.1 Standard: Follow the principle of least privilege.
* **Do This:** Grant users and applications only the minimum necessary permissions to access resources.
* **Don't Do This:** Grant broad or unrestricted access to resources.
**Why:** The principle of least privilege reduces the impact of security breaches by limiting the scope of potential damage. Supabase provides fine grained access control features on the row level, as well as database roles that should be followed.
**Example (Postgres Row Level Security):**
"""sql
-- Enable Row Level Security on the "todos" table
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
-- Create a policy that allows users to only access their own todos
CREATE POLICY "Users can only access their own todos"
ON todos
FOR SELECT
USING (auth.uid() = user_id);
"""
**Anti-pattern:** Granting broad access increases the risk of unauthorized access and data breaches.
### 5.2 Standard: Regularly update dependencies.
* **Do This:** Keep your dependencies up to date to patch security vulnerabilities. Use tools like "npm audit" or "yarn audit" to identify and fix vulnerabilities in your Node.js projects. Also monitor Supabase releases to use the latest and safest version.
* **Don't Do This:** Use outdated dependencies that are known to be vulnerable.
**Why:** Regularly updating dependencies helps protect your applications from known security vulnerabilities.
**Example (npm audit):**
"""bash
npm audit
npm audit fix
"""
**Anti-pattern:** Using outdated dependencies increases the risk of security breaches.
### 5.3 Standard: Secure API keys and secrets.
* **Do This:** Store API keys and secrets securely using environment variables and secrets management solutions. Avoid committing secrets to your codebase.
* **Don't Do This:** Hardcode API keys or secrets in your code or configuration files.
**Why:** Securely storing API keys and secrets helps prevent unauthorized access to your resources.
**Anti-pattern:** Exposing API keys or secrets in your codebase can lead to unauthorized access and resource exploitation.
## 6. Scalability
### 6.1 Standard: Design for horizontal scalability.
* **Do This:** Design your Supabase applications to be horizontally scalable, so you can easily add more resources to handle increased traffic. Use stateless application servers and a scalable database.
* **Don't Do This:** Design applications that are tightly coupled to a single server.
**Why:** Horizontal scalability allows you to scale your application linearly as demand increases, without requiring significant architectural changes. Supabase's architecture allows for scaling the Postgres database.
**Anti-pattern:** Designing applications for vertical scalability (i.e., increasing the resources on a single server) can become expensive and limit your ability to handle increased traffic.
### 6.2 Standard: Implement caching.
* **Do This:** Use caching to reduce the load on your database and improve application performance. Use caching strategies such as in-memory caching (e.g., Redis, Memcached) or HTTP caching (e.g., Cloudflare CDN).
* **Don't Do This:** Overload your database with frequent queries for the same data.
**Why:** Caching reduces the latency of retrieving data and improves the overall performance of your application.
**Example (Redis caching in a Node.js application):**
"""javascript
const redis = require('redis');
const client = redis.createClient();
client.on('error', err => console.log('Redis Client Error', err));
await client.connect();
async function getData(key) {
const cachedData = await client.get(key);
if (cachedData) {
return JSON.parse(cachedData);
}
const data = await fetchFromDatabase(key);
await client.set(key, JSON.stringify(data), {
EX: 60, // Cache for 60 seconds
NX: true // Only set if the key doesn't exist
});
return data;
}
"""
**Anti-pattern:** Lack of caching can lead to slow application performance and increased load on your database.
## 7. Backups and Disaster Recovery
### 7.1 Standard: Implement regular backups.
* **Do This:** Configure regular backups of your Supabase database and other critical data. Store backups in a separate location from your production environment.
* **Don't Do This:** Rely on manual backups or store backups in the same location as your production environment.
**Why:** Regular backups allow you to restore your data in the event of a disaster or data loss. Supabase provides backup and restore features.
**Anti-pattern:** Lack of backups can lead to permanent data loss in the event of a disaster.
### 7.2 Standard: Test your disaster recovery plan.
* **Do This:** Regularly test your disaster recovery plan to ensure that you can restore your data and resume operations in a timely manner.
* **Don't Do This:** Assume that your disaster recovery plan will work without testing it.
**Why:** Testing your disaster recovery plan helps identify potential issues and ensures that you can recover from a disaster effectively.
**Anti-pattern:** Lack of testing can lead to unexpected problems during a disaster recovery and delay your ability to resume operations.
## 8. Supabase Specific Considerations
### 8.1 Standard: Understand Supabase CLI and its Role
* **Do This:** Use the Supabase CLI to manage your local development environment, database migrations, and function deployments.
* **Don't Do This:** Manually manage these tasks, as it can lead to inconsistencies and errors.
**Why:** CLI provides a standardized way of interacting with your Supabase project.
### 8.2 Standard: Leverage Edge Functions Effectively
* **Do This:** Use Supabase Edge Functions for server-side logic that requires low latency, such as authentication, authorization, and data transformations.
* **Don't Do This:** Overuse Edge Functions for complex business logic, as they have limitations in terms of execution time and resources. Opt instead for more traditional backend services for these use cases.
**Why:** Edge Functions provide a serverless environment closer to your users.
### 8.3 Standard: Security Considerations for Supabase Storage
* **Do This:** Implement proper access control policies on Supabase Storage to protect uploaded files from unauthorized access.
* **Don't Do This:** Leave Supabase Storage buckets publicly accessible, as it can lead to data breaches.
**Why:** Supabase Storage stores all of your user's assets, so maintaining its security is vital.
### 8.4 Standard: Database Migrations with Supabase
* **Do This:** Use the Supabase CLI to generate and manage database migrations. This ensures that your schema changes are tracked and applied consistently across environments.
* **Don't Do This:** Manually alter your database schema without creating migrations.
* **Do This:** Review and Version Control automatically generated migrations for best practices.
**Why:** Having database migrations guarantees consistency by helping you evolve and manage the database over time.
By adhering to these deployment and DevOps standards, development teams can build, deploy, and maintain Supabase applications that are reliable, scalable, and secure. This will result in more efficient development processes and a better user experience.
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', }, }) }) ```