Supabase Agent Skills

https://github.com/supabase/agent-skills
Admin
Supabase Agent Skills provides a collection of instructions, scripts, and resources in a...
Tokens:29,705
Snippets:269
Trust Score:10
License:MIT
Update:7 months ago
Show doc for...
Tokens:
Raw
### Install and Build Supabase Postgres Best Practices Skill

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/README.md

Commands to install dependencies, validate existing references, and build the AGENTS.md file for the Supabase Postgres Best Practices skill.

```bash
# From repository root
npm install

# Validate existing references
npm run validate

# Build AGENTS.md
npm run build
```

--------------------------------

### SQL Table and Index Definition Example

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/_contributing.md

Demonstrates how to include table definitions and index creation for clarity in Postgres references. This ensures examples are self-contained and runnable, providing necessary context for understanding index performance.

```sql
-- Include table definition when needed for clarity
CREATE TABLE users (
  id bigint PRIMARY KEY,
  email text NOT NULL,
  deleted_at timestamptz
);

-- Now show the index
CREATE INDEX users_active_email_idx ON users(email) WHERE deleted_at IS NULL;
```

--------------------------------

### Install Project Tools and Dependencies with Mise

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Installs necessary tools like Node.js and project dependencies using the 'mise' version manager. Ensure 'mise' is installed before running these commands.

```bash
mise install        # Install Node.js (version defined in mise.toml)

```

```bash
mise run install    # Install all npm dependencies

```

--------------------------------

### Example Incorrect and Correct SQL for Postgres Optimization

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/README.md

Demonstrates a common SQL anti-pattern and its optimized alternative, including explanations for both. This structure is used within reference files.

```sql
-- Comment explaining what's wrong
[Bad SQL example]

-- Comment explaining why this is better
[Good SQL example]
```

--------------------------------

### Reference File Format with Examples

Source: https://context7.com/supabase/agent-skills/llms.txt

Illustrates the format of reference files used within skills. Each file contains YAML frontmatter detailing impact and tags, followed by markdown content with 'Incorrect' and 'Correct' code examples.

```markdown
---
title: Add Indexes on WHERE and JOIN Columns
impact: CRITICAL
impactDescription: 100-1000x faster queries on large tables
tags: indexes, performance, sequential-scan, query-optimization
---

## Add Indexes on WHERE and JOIN Columns

Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.

**Incorrect (sequential scan on large table):**

```sql
-- No index on customer_id causes full table scan
select * from orders where customer_id = 123;

-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)
```

**Correct (index scan):**

```sql
-- Create index on frequently filtered column
create index orders_customer_id_idx on orders (customer_id);

select * from orders where customer_id = 123;

-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
```

Reference: [Query Optimization](https://supabase.com/docs/guides/database/query-optimization)

```

--------------------------------

### Create a New Postgres Best Practice Reference

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/README.md

Steps to create a new reference file by copying a template, filling in content, and then validating and building the skill.

```bash
# Copy the template
cp references/_template.md references/query-your-reference-name.md

# Validate and build
npm run validate
npm run build
```

--------------------------------

### SQL Formatting Standards Example

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/_contributing.md

Illustrates the recommended SQL formatting for Postgres references, emphasizing lowercase keywords, clear indentation, and concurrent index creation. This promotes readability and consistency.

```sql
-- Use lowercase keywords, clear formatting
CREATE INDEX CONCURRENTLY users_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;
```

--------------------------------

### Application Code Batch Query Pattern Example (TypeScript)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/_contributing.md

Presents the corrected version of the N+1 query problem using TypeScript, demonstrating how to batch queries for efficiency. This example shows fetching related data in a single, optimized query.

```typescript
const posts = await db.query("SELECT * FROM posts WHERE user_id = ANY($1)", [
  userIds,
]);
```

--------------------------------

### SQL: Index Type Usage Examples

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-index-types.md

Provides SQL examples for creating indexes using different PostgreSQL index types: B-tree for standard comparisons, GIN for arrays and JSONB, GiST for geometric data, BRIN for large time-series tables, and Hash for equality checks.

```sql
-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL
create index users_created_idx on users (created_at);

-- GIN: arrays, JSONB, full-text search
create index posts_tags_idx on posts using gin (tags);

-- GiST: geometric data, range types, nearest-neighbor (KNN) queries
create index locations_idx on places using gist (location);

-- BRIN: large time-series tables (10-100x smaller)
create index events_time_idx on events using brin (created_at);

-- Hash: equality-only (slightly faster than B-tree for =)
create index sessions_token_idx on sessions using hash (token);
```

--------------------------------

### Application Code N+1 Query Pattern Example (TypeScript)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/_contributing.md

Shows an example of the N+1 query problem in application code using TypeScript, where individual queries are made within a loop. This highlights an inefficient pattern that should be refactored.

```typescript
for (const user of users) {
  const posts = await db.query("SELECT * FROM posts WHERE user_id = $1", [
    user.id,
  ]);
}
```

--------------------------------

### Install Supabase Agent Skills

Source: https://context7.com/supabase/agent-skills/llms.txt

Installs Supabase agent skills using the npx CLI tool or as Claude Code plugins. This command adds the agent skills to your local environment for use by AI coding agents.

```bash
# Install skills via npx (Agent Skills standard)
npx skills add supabase/agent-skills

# Alternative: Install as Claude Code plugin
/plugin marketplace add supabase/agent-skills
/plugin install postgres-best-practices@supabase-agent-skills
```

--------------------------------

### Configure LLM Evaluation Environment Variables

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Sets up the environment for Large Language Model (LLM) evaluations by copying an example .env file and instructing the user to add API keys. This file is automatically loaded by 'mise'.

```bash
cp packages/evals/.env.example packages/evals/.env
# Edit packages/evals/.env with your ANTHROPIC_API_KEY and OPENAI_API_KEY

```

--------------------------------

### JavaScript Caching Implementation Example

Source: https://context7.com/supabase/agent-skills/llms.txt

This JavaScript code snippet demonstrates a common pattern for implementing caching to improve database query performance. It shows how to check a cache before querying a database and how to store results in the cache with a Time-To-Live (TTL).

```javascript
// Fetches from database every time
const data = await db.query("SELECT * FROM users");
```

```javascript
// Check cache first, then database
const cached = await cache.get("users");
if (cached) return cached;
const data = await db.query("SELECT * FROM users");
await cache.set("users", data, { ttl: 300 });
```

--------------------------------

### Create Reference File for Caching

Source: https://context7.com/supabase/agent-skills/llms.txt

This bash command creates a specific reference file (perf-caching.md) within the 'references' directory. It includes frontmatter detailing the title, impact, and tags, followed by markdown content explaining caching implementation with JavaScript code examples.

```bash
cat > skills/my-new-skill/references/perf-caching.md << 'EOF'
---
title: Implement Caching for Repeated Queries
impact: CRITICAL
impactDescription: 10-100x faster response times
tags: caching, performance
---

## Implement Caching for Repeated Queries

Explanation of why caching matters.

**Incorrect (no caching):**

```javascript
// Fetches from database every time
const data = await db.query("SELECT * FROM users");
```

**Correct (with caching):**

```javascript
// Check cache first, then database
const cached = await cache.get("users");
if (cached) return cached;
const data = await db.query("SELECT * FROM users");
await cache.set("users", data, { ttl: 300 });
```
EOF
```

--------------------------------

### CLI Commands for Skill Management

Source: https://context7.com/supabase/agent-skills/llms.txt

Provides essential CLI commands for managing Supabase agent skills. Includes commands for installing dependencies, building skills, validating skills, and performing code checks and tests.

```bash
# Install dependencies
mise install                     # Install tool versions (Node.js)
npm install                      # Install npm dependencies

# Build all skills
npm run build
# Output:
# Found 1 skill(s): supabase-postgres-best-practices
# [supabase-postgres-best-practices] Building AGENTS.md...
#   Generated: skills/supabase-postgres-best-practices/AGENTS.md
#   Created symlink: CLAUDE.md -> AGENTS.md
# Done!

# Validate all skills
npm run validate
# Output:
# Found 1 skill(s): supabase-postgres-best-practices
# [supabase-postgres-best-practices] Validating...
#   Total: 30 | Valid: 30 | Invalid: 0
# All validations passed!

# Format and lint code
npm run check

# Run tests
npm run test

```

--------------------------------

### Parse Reference File with parseRuleFile()

Source: https://context7.com/supabase/agent-skills/llms.txt

Parses a reference markdown file into structured Rule data, including frontmatter, examples, and validation information. It takes the file path and a section map as input and returns a detailed rule object.

```typescript
import { parseRuleFile } from "./parser.js";

const sectionMap = { query: 1, conn: 2, security: 3, schema: 4 };
const result = parseRuleFile("references/query-missing-indexes.md", sectionMap);

// Returns:
// {
//   success: true,
//   rule: {
//     id: "",
//     title: "Add Indexes on WHERE and JOIN Columns",
//     section: 1,
//     impact: "CRITICAL",
//     impactDescription: "100-1000x faster queries on large tables",
//     explanation: "Queries filtering or joining on unindexed columns cause full table scans...",
//     examples: [
//       { label: "Incorrect", description: "sequential scan on large table", code: "...", language: "sql" },
//       { label: "Correct", description: "index scan", code: "...", language: "sql" }
//     ],
//     references: ["https://supabase.com/docs/guides/database/query-optimization"],
//     tags: ["indexes", "performance", "sequential-scan", "query-optimization"]
//   },
//   errors: [],
//   warnings: []
// }
```

--------------------------------

### Reference File Markdown Structure

Source: https://github.com/supabase/agent-skills/blob/main/CLAUDE.md

This markdown structure is used for reference files that extend Supabase agent skills. It includes metadata like title, impact, and tags, followed by a detailed explanation. It also shows examples of correct and incorrect code formatting within the documentation.

```markdown
---
title: Action-Oriented Title
impact: CRITICAL|HIGH|MEDIUM-HIGH|MEDIUM|LOW-MEDIUM|LOW
impactDescription: Quantified benefit
tags: keywords
---

## Title

1-2 sentence explanation.

**Incorrect:** ```sql -- bad example ```

**Correct:** ```sql -- good example ```
```

--------------------------------

### Diagnose Slow SQL Queries with EXPLAIN ANALYZE

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/monitor-explain-analyze.md

This SQL snippet demonstrates how to use the EXPLAIN ANALYZE command to execute a query and analyze its actual performance. It helps identify bottlenecks by showing timings, buffer usage, and filter effectiveness, guiding users to optimize queries, often by adding indexes.

```sql
explain (analyze, buffers, format text)
select * from orders where customer_id = 123 and status = 'pending';
```

--------------------------------

### Validate Reference File with validateRuleFile()

Source: https://context7.com/supabase/agent-skills/llms.txt

Validates a reference file against predefined rules, checking for required fields, proper example structure, and impact levels. It returns a validation result object indicating success or failure with detailed error and warning messages.

```typescript
import { validateRuleFile } from "./validate.js";

const sectionMap = { query: 1, conn: 2, security: 3, schema: 4 };
const result = validateRuleFile("references/query-missing-indexes.md", sectionMap);

// Returns:
// {
//   valid: true,
//   errors: [],
//   warnings: []
// }

// Example with validation failures:
const badResult = validateRuleFile("references/incomplete-rule.md", sectionMap);
// Returns:
// {
//   valid: false,
//   errors: [
//     "Missing or empty title",
//     "Missing examples (need at least one bad and one good example)"
//   ],
//   warnings: ["Explanation is shorter than 50 characters"]
// }
```

--------------------------------

### Correct Short Transaction Example (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/lock-short-transactions.md

Illustrates the correct approach by performing external operations (like API calls) outside the transaction scope. The transaction is then kept minimal, only acquiring locks for the essential data update, thus reducing contention.

```sql
-- Validate data and call APIs outside transaction
-- Application: response = await paymentAPI.charge(...)

-- Only hold lock for the actual update
begin;
update orders
set status = 'paid', payment_id = $1
where id = $2 and status = 'pending'
returning *;
commit;  -- Lock held for milliseconds
```

--------------------------------

### SQL: Incorrect RLS Policy Example

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-rls-performance.md

Highlights an inefficient RLS policy pattern where a function (`auth.uid()`) is called for every row processed. This can lead to severe performance degradation on large tables due to repeated function executions.

```sql
create policy orders_policy on orders
  using (auth.uid() = user_id);
```

--------------------------------

### SQL: Add Index for RLS Policy Performance

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-rls-performance.md

Provides an example of creating a database index on a column frequently used in RLS policies. Indexing columns like `user_id` significantly speeds up RLS query performance by allowing the database to quickly locate relevant rows.

```sql
create index orders_user_id_idx on orders (user_id);
```

--------------------------------

### Create SKILL.md with Frontmatter

Source: https://context7.com/supabase/agent-skills/llms.txt

This bash command creates the main SKILL.md file for a new skill. It uses a heredoc to define the frontmatter, including the skill's name, description, license, author, version, and categories with impact levels.

```bash
cat > skills/my-new-skill/SKILL.md << 'EOF'
---
name: my-new-skill
description: Description of what this skill does. Use when working with X or optimizing Y.
license: MIT
metadata:
  author: your-name
  version: "1.0.0"
---

# My New Skill

## When to Apply

Reference these guidelines when:
- Working with specific technology
- Implementing common patterns

## Categories

| Priority | Category | Impact | Prefix |
|----------|----------|--------|--------|
| 1 | Performance | CRITICAL | `perf-` |
| 2 | Security | HIGH | `sec-` |
EOF
```

--------------------------------

### Create _sections.md for Skill References

Source: https://context7.com/supabase/agent-skills/llms.txt

This bash command creates the _sections.md file, which defines the different sections within the skill's references. It includes impact, description, and category information for each section, aiding in organization and retrieval.

```bash
cat > skills/my-new-skill/references/_sections.md << 'EOF'
## 1. Performance (perf)
**Impact:** CRITICAL
**Description:** Performance optimization patterns.

## 2. Security (sec)
**Impact:** HIGH
**Description:** Security best practices.
EOF
```

--------------------------------

### Create Table and Query JSONB Without Index (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/advanced-jsonb-indexing.md

Demonstrates the creation of a table with a JSONB column and illustrates inefficient queries that result in full table scans due to the absence of appropriate indexes.

```sql
create table products (
  id bigint primary key,
  attributes jsonb
);

-- Full table scan for every query
select * from products where attributes @> '{"color": "red"}';
select * from products where attributes->>'brand' = 'Nike';
```

--------------------------------

### Create Table with Appropriate Data Types (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-data-types.md

Illustrates a SQL 'create table' statement using appropriate data types for improved storage efficiency, future-proofing, and accurate data handling. This includes using 'bigint' for IDs, 'text' for strings, 'timestamptz' for timestamps, 'boolean' for flags, and 'numeric' for prices.

```sql
create table users (
  id bigint generated always as identity primary key,  -- 9 quintillion max
  email text,                     -- No artificial limit, same performance as varchar
  created_at timestamptz,         -- Always store timezone-aware timestamps
  is_active boolean default true, -- 1 byte vs variable string length
  price numeric(10,2)             -- Exact decimal arithmetic
);
```

--------------------------------

### Incorrect Long Transaction Example (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/lock-short-transactions.md

Demonstrates an incorrect pattern where a transaction is held open for an extended period, including an external API call. This can lead to significant lock contention, blocking other queries on the affected rows.

```sql
begin;
select * from orders where id = 1 for update;  -- Lock acquired

-- Application makes HTTP call to payment API (2-5 seconds)
-- Other queries on this row are blocked!

update orders set status = 'paid' where id = 1;
commit;  -- Lock held for entire duration
```

--------------------------------

### Create GIN Index for JSONB Containment Queries (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/advanced-jsonb-indexing.md

Shows how to create a GIN index on a JSONB column to efficiently handle containment queries using operators like @>. It also demonstrates creating an expression index for faster lookups of specific keys within the JSONB data.

```sql
-- GIN index for containment operators (@>, ?, ?&, ?|)
create index products_attrs_gin on products using gin (attributes);

-- Now containment queries use the index
select * from products where attributes @> '{"color": "red"}';

-- For specific key lookups, use expression index
create index products_brand_idx on products ((attributes->>'brand'));
select * from products where attributes->>'brand' = 'Nike';
```

--------------------------------

### Create Table with Incorrect Data Types (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-data-types.md

Demonstrates a SQL 'create table' statement with suboptimal data type choices that can lead to storage inefficiencies, potential overflows, and incorrect data representation.

```sql
create table users (
  id int,                    -- Will overflow at 2.1 billion
  email varchar(255),        -- Unnecessary length limit
  created_at timestamp,      -- Missing timezone info
  is_active varchar(5),      -- String for boolean
  price varchar(20)          -- String for numeric
);
```

--------------------------------

### Create Composite Index for Multi-Column Queries (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-composite-indexes.md

Demonstrates the creation of composite indexes in SQL for optimizing queries that filter on multiple columns. It shows the incorrect method using separate indexes and the correct method using a single composite index, highlighting the performance benefits. The correct column order (equality checks first, range checks last) is crucial for efficiency.

```sql
-- Incorrect (separate indexes require bitmap scan)
-- Two separate indexes
create index orders_status_idx on orders (status);
create index orders_created_idx on orders (created_at);

-- Query must combine both indexes (slower)
select * from orders where status = 'pending' and created_at > '2024-01-01';
```

```sql
-- Correct (composite index)
-- Single composite index (leftmost column first for equality checks)
create index orders_status_created_idx on orders (status, created_at);

-- Query uses one efficient index scan
select * from orders where status = 'pending' and created_at > '2024-01-01';
```

```sql
-- Column order matters - place equality columns first, range columns last:
-- Good: status (=) before created_at (>)
create index idx on orders (status, created_at);

-- Works for: WHERE status = 'pending'
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)
```

--------------------------------

### SQL COPY Command for Bulk Loading

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-batch-inserts.md

Presents the most efficient method for large data imports using the SQL COPY command. It can load data directly from a file (e.g., CSV) or standard input, offering the highest performance for bulk data operations.

```sql
-- COPY is fastest for bulk loading
copy events (user_id, action, created_at)
from '/path/to/data.csv'
with (format csv, header true);

-- Or from stdin in application
copy events (user_id, action) from stdin with (format csv);
1,click
1,view
2,click
\.
```

--------------------------------

### Validate and Build Skill Package

Source: https://context7.com/supabase/agent-skills/llms.txt

These bash commands execute the npm scripts to validate the skill's structure and content, and then build the skill package. This ensures the skill conforms to the Supabase Agent Skills standard before deployment.

```bash
npm run validate
npm run build
```

--------------------------------

### Build Skill with buildSkill()

Source: https://context7.com/supabase/agent-skills/llms.txt

Compiles a skill's SKILL.md and reference files into an AGENTS.md navigation file and creates a CLAUDE.md symlink. This function is essential for generating the discoverable documentation package for AI agents.

```typescript
import { buildSkill } from "./build.js";
import { getSkillPaths } from "./config.js";

// Build a specific skill
const paths = getSkillPaths("supabase-postgres-best-practices");
buildSkill(paths);

// Generated AGENTS.md structure:
// # Supabase Postgres Best Practices
//
// ## Structure
// ```
supabase-postgres-best-practices/
//   SKILL.md       # Main skill file - read this first
//   AGENTS.md      # This navigation guide
//   CLAUDE.md      # Symlink to AGENTS.md
//   references/    # Detailed reference files
// ```
//
// ## Usage
// 1. Read `SKILL.md` for the main skill instructions
// 2. Browse `references/` for detailed documentation on specific topics
// 3. Reference files are loaded on-demand - read only what you need

```

--------------------------------

### SQL Data Type Guidelines

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-data-types.md

Provides concise SQL guidelines for selecting optimal data types, emphasizing the use of 'bigint' for IDs, 'text' for strings, 'timestamptz' for time, 'numeric' for monetary values, and 'text' or 'enum' types for enumerated values.

```sql
-- IDs: use bigint, not int (future-proofing)
-- Strings: use text, not varchar(n) unless constraint needed
-- Time: use timestamptz, not timestamp
-- Money: use numeric, not float (precision matters)
-- Enums: use text with check constraint or create enum type
```

--------------------------------

### Create Partitioned Table by Range (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-partitioning.md

Illustrates how to create a partitioned table in PostgreSQL using a range-based strategy on a timestamp column. This method allows queries to target specific partitions, drastically improving performance. It also enables instant deletion of old data by dropping partitions.

```sql
create table events (
  id bigint generated always as identity,
  created_at timestamptz not null,
  data jsonb
) partition by range (created_at);

-- Create partitions for each month
create table events_2024_01 partition of events
  for values from ('2024-01-01') to ('2024-02-01');

create table events_2024_02 partition of events
  for values from ('2024-02-01') to ('2024-03-01');

-- Queries only scan relevant partitions
select * from events where created_at > '2024-01-15';  -- Only scans events_2024_01+

-- Drop old data instantly
drop table events_2023_01;  -- Instant vs DELETE taking hours
```

--------------------------------

### Create Directory Structure for a New Skill

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Creates the necessary directory structure for a new agent skill, including a 'references' subdirectory.

```bash
mkdir -p skills/my-skill/references

```

--------------------------------

### Show Current Max Connections

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-limits.md

Displays the current value of the `max_connections` setting in the database. This is useful for understanding the existing configuration before making changes.

```sql
show max_connections;
```

--------------------------------

### Choose JSONB GIN Index Operator Class (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/advanced-jsonb-indexing.md

Explains and demonstrates the creation of GIN indexes using different operator classes ('jsonb_ops' and 'jsonb_path_ops') for JSONB columns. 'jsonb_ops' supports all operators but results in a larger index, while 'jsonb_path_ops' is optimized for the '@>' operator and yields a smaller index.

```sql
-- jsonb_ops (default): supports all operators, larger index
create index idx1 on products using gin (attributes);

-- jsonb_path_ops: only @> operator, but 2-3x smaller index
create index idx2 on products using gin (attributes jsonb_path_ops);
```

--------------------------------

### Discover Skills with discoverSkills()

Source: https://context7.com/supabase/agent-skills/llms.txt

Discovers all valid skills within the 'skills/' directory by scanning for directories containing a SKILL.md file. It returns an array of skill names. The `getSkillPaths` function retrieves detailed path information for a specific skill.

```typescript
import { discoverSkills, getSkillPaths } from "./config.js";

// Discover all available skills
const skills = discoverSkills();
// Returns: ["supabase-postgres-best-practices"]

// Get paths for a specific skill
const paths = getSkillPaths("supabase-postgres-best-practices");
// Returns:
// {
//   name: "supabase-postgres-best-practices",
//   skillDir: "/path/to/skills/supabase-postgres-best-practices",
//   referencesDir: "/path/to/skills/supabase-postgres-best-practices/references",
//   agentsOutput: "/path/to/skills/supabase-postgres-best-practices/AGENTS.md",
//   claudeSymlink: "/path/to/skills/supabase-postgres-best-practices/CLAUDE.md",
//   skillFile: "/path/to/skills/supabase-postgres-best-practices/SKILL.md"
// }
```

--------------------------------

### Create Single Large Table (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-partitioning.md

Demonstrates the structure of a single, unpartitioned large table. This approach can lead to slow queries and lengthy maintenance operations as the table grows. It serves as a baseline for comparison with partitioned tables.

```sql
create table events (
  id bigint generated always as identity,
  created_at timestamptz,
  data jsonb
);

-- 500M rows, queries scan everything
select * from events where created_at > '2024-01-01';  -- Slow
vacuum events;  -- Takes hours, locks table
```

--------------------------------

### SQL: Eliminate N+1 Queries with Batch Loading

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-n-plus-one.md

Demonstrates how to refactor inefficient N+1 SQL queries into a single, optimized batch query. This method reduces database round trips by using the `ANY` operator with an array of IDs or by employing a `LEFT JOIN`.

```sql
-- Incorrect (N+1 queries):
-- First query: get all users
select id from users where active = true;  -- Returns 100 IDs

-- Then N queries, one per user
select * from orders where user_id = 1;
select * from orders where user_id = 2;
select * from orders where user_id = 3;
-- ... 97 more queries!

-- Total: 101 round trips to database

-- Correct (single batch query):
-- Collect IDs and query once with ANY
select * from orders where user_id = any(array[1, 2, 3, ...]);

-- Or use JOIN instead of loop
select u.id, u.name, o.*
from users u
left join orders o on o.user_id = u.id
where u.active = true;

-- Total: 1 round trip
```

```sql
-- Application pattern:
-- Instead of looping in application code:
-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)

-- Pass array parameter:
select * from orders where user_id = any($1::bigint[]);
-- Application passes: [1, 2, 3, 4, 5, ...]
```

--------------------------------

### Define Skill Metadata in SKILL.md

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Defines the metadata for a new skill using YAML frontmatter in the 'SKILL.md' file. This includes the skill's name, description, license, and authoring information.

```yaml
---
name: my-skill
description: Brief description of what this skill does and when to use it.
license: MIT
metadata:
  author: your-org
  version: "1.0.0"
  organization: Your Org
  date: January 2026
  abstract: Detailed description of this skill for the compiled AGENTS.md.
---

# My Skill

Instructions for agents using this skill.

## References

- https://example.com/docs

```

--------------------------------

### Define Reference Categories in _sections.md

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Organizes references within a skill into categories using markdown. Each category has a title, impact level (HIGH/MEDIUM), and a description.

```markdown
## 1. First Category (first)
**Impact:** HIGH
**Description:** What this category covers.

## 2. Second Category (second)
**Impact:** MEDIUM
**Description:** What this category covers.

```

--------------------------------

### SKILL.md Frontmatter Schema Definition

Source: https://context7.com/supabase/agent-skills/llms.txt

Defines the YAML frontmatter structure for a SKILL.md file, including required fields like 'name' and 'description', and optional metadata. This schema is used to describe a skill's purpose and context.

```yaml
---
name: supabase-postgres-best-practices
description: Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
license: MIT
metadata:
  author: supabase
  version: "1.1.0"
  organization: Supabase
  date: January 2026
  abstract: Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres.
---

# Supabase Postgres Best Practices

## When to Apply

Reference these guidelines when:
- Writing SQL queries or designing schemas
- Implementing indexes or query optimization
- Reviewing database performance issues

## Rule Categories by Priority

| Priority | Category | Impact | Prefix |
|----------|----------|--------|--------|
| 1 | Query Performance | CRITICAL | `query-` |
| 2 | Connection Management | CRITICAL | `conn-` |
| 3 | Security & RLS | CRITICAL | `security-` |

```

--------------------------------

### Batch SQL INSERT Statements (Faster)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-batch-inserts.md

Illustrates the optimized approach of using batch SQL INSERT statements to insert multiple rows in a single command. This significantly reduces network round trips and transaction overhead, leading to a 10-50x performance improvement for bulk inserts compared to individual statements.

```sql
-- Multiple rows in single statement
insert into events (user_id, action) values
  (1, 'click'),
  (1, 'view'),
  (2, 'click'),
  -- ... up to ~1000 rows per batch
  (999, 'view');

-- One round trip for 1000 rows
```

--------------------------------

### Multi-Column Cursor Pagination (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-pagination.md

Shows how to implement cursor pagination when sorting by multiple columns. The cursor must include all columns used in the ORDER BY clause to ensure correct ordering and performance.

```sql
select * from products
where (created_at, id) > ('2024-01-15 10:00:00', 12345)
order by created_at, id
limit 20;
```

--------------------------------

### Individual SQL INSERT Statements (Slow)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-batch-inserts.md

Demonstrates the inefficient method of inserting data row by row using individual SQL INSERT statements. This approach incurs high overhead due to separate transactions and network round trips for each insert, making it unsuitable for bulk operations.

```sql
-- Each insert is a separate transaction and round trip
insert into events (user_id, action) values (1, 'click');
insert into events (user_id, action) values (1, 'view');
insert into events (user_id, action) values (2, 'click');
-- ... 1000 more individual inserts

-- 1000 inserts = 1000 round trips = slow
```

--------------------------------

### Section Definitions (_sections.md) Structure

Source: https://context7.com/supabase/agent-skills/llms.txt

Defines rule categories used in skills, including their priority, impact, description, and associated prefix. This file helps organize and categorize different types of rules within a skill.

```markdown
## 1. Query Performance (query)
**Impact:** CRITICAL
**Description:** Slow queries, missing indexes, inefficient query plans.

## 2. Connection Management (conn)
**Impact:** CRITICAL
**Description:** Connection pooling, limits, and serverless strategies.

## 3. Security & RLS (security)
**Impact:** CRITICAL
**Description:** Row-Level Security policies, privilege management, and authentication patterns.

## 4. Schema Design (schema)
**Impact:** HIGH
**Description:** Table design, index strategies, partitioning, and data type selection.

## 5. Concurrency & Locking (lock)
**Impact:** MEDIUM-HIGH
**Description:** Transaction management, isolation levels, deadlock prevention.

## 6. Data Access Patterns (data)
**Impact:** MEDIUM
**Description:** N+1 query elimination, batch operations, cursor-based pagination.

## 7. Monitoring & Diagnostics (monitor)
**Impact:** LOW-MEDIUM
**Description:** Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection.

## 8. Advanced Features (advanced)
**Impact:** LOW
**Description:** Full-text search, JSONB optimization, PostGIS, extensions.

```

--------------------------------

### Create Index for WHERE Clause - SQL

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-missing-indexes.md

This snippet demonstrates how to create an index on a column frequently used in WHERE clauses to optimize query performance. It shows the SQL command to create the index and how a subsequent query benefits from it, avoiding a sequential scan on large tables.

```sql
-- No index on customer_id causes full table scan
select * from orders where customer_id = 123;

-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)

-- Create index on frequently filtered column
create index orders_customer_id_idx on orders (customer_id);

select * from orders where customer_id = 123;

-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
```

--------------------------------

### Create Partial Index (Correct)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-partial-indexes.md

This SQL snippet illustrates creating a partial index on the 'users' table, specifically for active users (where deleted_at is null). This results in a smaller, more targeted index that directly matches the query's filter condition.

```sql
create index users_active_email_idx on users (email)
where deleted_at is null;
```

--------------------------------

### Correct SQL Permissions Grant (Supabase)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-privileges.md

This SQL snippet illustrates the correct way to apply the principle of least privilege in Supabase. It defines roles with minimal, specific grants for read-only and write operations on particular tables and sequences, enhancing security.

```sql
-- Create role with no default privileges
create role app_readonly nologin;

-- Grant only SELECT on specific tables
grant usage on schema public to app_readonly;
grant select on public.products, public.categories to app_readonly;

-- Create role for writes with limited scope
create role app_writer nologin;
grant usage on schema public to app_writer;
grant select, insert, update on public.orders to app_writer;
grant usage on sequence orders_id_seq to app_writer;
-- No DELETE permission

-- Login role inherits from these
create role app_user login password 'xxx';
grant app_writer to app_user;
```

--------------------------------

### Monitor PostgreSQL Connections (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-pooling.md

This SQL snippet demonstrates how to check the current number of active connections to a PostgreSQL database. It's useful for diagnosing connection exhaustion issues, especially when comparing scenarios with and without connection pooling.

```sql
select count(*) from pg_stat_activity;
```

--------------------------------

### SQL: Optimize JSONB Containment with GIN Index

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-index-types.md

Demonstrates the incorrect use of a B-tree index for JSONB containment queries, leading to full table scans. It then shows the correct approach using a GIN index, which optimizes containment operators like '@>'.

```sql
-- B-tree cannot optimize containment operators
create index products_attrs_idx on products (attributes);
select * from products where attributes @> '{"color": "red"}';
-- Full table scan - B-tree doesn't support @> operator

-- GIN supports @>, ?, ?&, ?| operators
create index products_attrs_idx on products using gin (attributes);
select * from products where attributes @> '{"color": "red"}';
```

--------------------------------

### Optimal Primary Key Strategies (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-primary-keys.md

Presents optimal primary key strategies in SQL, recommending 'bigint generated always as identity' for sequential IDs and UUIDv7 or time-prefixed IDs for distributed systems to avoid index fragmentation.

```sql
-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)
create table users (
  id bigint generated always as identity primary key
);

-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)
-- Requires pg_uuidv7 extension: create extension pg_uuidv7;
create table orders (
  id uuid default uuid_generate_v7() primary key  -- Time-ordered, no fragmentation
);

-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)
create table events (
  id text default concat(
    to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),
    gen_random_uuid()::text
  ) primary key
);
```

--------------------------------

### SQL: Insert-or-Ignore with ON CONFLICT

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-upsert.md

Shows how to implement an insert-or-ignore pattern using `INSERT ... ON CONFLICT DO NOTHING` in SQL. This is useful when you only want to insert a row if it doesn't already exist, without performing any update if a conflict is detected. It relies on a unique constraint or primary key for conflict detection.

```sql
insert into page_views (page_id, user_id)
values (1, 123)
on conflict (page_id, user_id) do nothing;
```

--------------------------------

### Create Full Index (Incorrect)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-partial-indexes.md

This SQL snippet demonstrates creating a full index on the 'users' table. This index includes all rows, even those that might be irrelevant for common queries, leading to larger index size and potentially slower performance.

```sql
create index users_email_idx on users (email);
```

--------------------------------

### Monitor Connection Activity

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-limits.md

Queries the `pg_stat_activity` view to count active database connections grouped by their current state. This helps in understanding connection patterns and identifying potential bottlenecks.

```sql
select count(*), state from pg_stat_activity group by state;
```

--------------------------------

### Interpreting EXPLAIN ANALYZE Output for SQL Performance

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/monitor-explain-analyze.md

This SQL snippet provides key indicators within the EXPLAIN ANALYZE output that help diagnose slow query performance. It highlights common issues such as sequential scans on large tables, high row removal by filters, excessive buffer reads, and inefficient join strategies, suggesting potential optimizations.

```sql
-- Seq Scan on large tables = missing index
-- Rows Removed by Filter = poor selectivity or missing index
-- Buffers: read >> hit = data not cached, needs more memory
-- Nested Loop with high loops = consider different join strategy
-- Sort Method: external merge = work_mem too low
```

--------------------------------

### Run Pre-Flight Checks for Pull Requests

Source: https://github.com/supabase/agent-skills/blob/main/CONTRIBUTING.md

Executes a series of checks to ensure code quality, linting, validation, and build processes are successful before submitting a pull request. These commands must all complete without errors.

```bash
mise install       # Ensure correct Node.js version

```

```bash
mise run check     # Format and lint (auto-fix)

```

```bash
mise run validate  # Check reference format and structure

```

```bash
mise run build     # Generate AGENTS.md from references

```

--------------------------------

### Create Partial Index for Non-Null SKU

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-partial-indexes.md

This SQL snippet shows another practical application of partial indexes, indexing only products with non-null SKUs. This is useful when queries frequently filter for products that have a SKU assigned.

```sql
create index products_sku_idx on products (sku)
where sku is not null;
```

--------------------------------

### Set Max Connections and Work Mem

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-limits.md

Configures the maximum number of concurrent connections and the memory allocated for work in the database. These settings are crucial for resource management and performance tuning, especially on systems with limited RAM.

```sql
alter system set max_connections = 100;
alter system set work_mem = '8MB';
```

--------------------------------

### Enable and Query pg_stat_statements Extension

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/monitor-pg-stat-statements.md

This SQL snippet demonstrates how to enable the pg_stat_statements extension, query for the slowest and most frequent queries, and reset statistics. It helps in identifying performance bottlenecks by analyzing query execution patterns.

```sql
create extension if not exists pg_stat_statements;

-- Find slowest queries by total time
select
  calls,
  round(total_exec_time::numeric, 2) as total_time_ms,
  round(mean_exec_time::numeric, 2) as mean_time_ms,
  query
from pg_stat_statements
order by total_exec_time desc
limit 10;

-- Find most frequent queries
select calls, query
from pg_stat_statements
order by calls desc
limit 10;

-- Reset statistics after optimization
select pg_stat_statements_reset();
```

--------------------------------

### Correct Cursor/Keyset Pagination (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/data-pagination.md

Illustrates cursor-based pagination, which provides consistent O(1) performance regardless of page depth. It uses a 'where' clause with the last seen ID to fetch subsequent rows.

```sql
select * from products order by id limit 20;
-- Application stores last_id = 20

select * from products where id > 20 order by id limit 20;
-- Uses index, always fast regardless of page depth

select * from products where id > 199980 order by id limit 20;
```

--------------------------------

### SQL: Apply RLS Policy Using Security Definer Function

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-rls-performance.md

Illustrates how to apply an RLS policy that utilizes a previously defined `security definer` function. This approach ensures efficient, indexed lookups for complex security conditions, bypassing per-row checks.

```sql
create policy team_orders_policy on orders
  using ((select is_team_member(team_id)));
```

--------------------------------

### Disabling Prepared Statements in Drivers (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-prepared-statements.md

Shows how to configure common database drivers to disable the automatic use of prepared statements, which can help avoid conflicts in pooled environments when direct control is needed.

```sql
-- Many drivers use prepared statements by default
-- Node.js pg: { prepare: false } to disable
-- JDBC: prepareThreshold=0 to disable
```

--------------------------------

### Create Index for JOIN Clause - SQL

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-missing-indexes.md

This snippet illustrates the correct way to add an index for columns used in JOIN operations. It emphasizes indexing the foreign key side of the relationship to ensure efficient joins between tables, preventing performance degradation on large datasets.

```sql
-- Index the referencing column
create index orders_customer_id_idx on orders (customer_id);

select c.name, o.total
from customers c
join orders o on o.customer_id = c.id;
```

--------------------------------

### SQL: Create Security Definer Function for RLS

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-rls-performance.md

Shows how to create a `security definer` function in SQL to perform complex RLS checks. This function runs with elevated privileges, allowing efficient lookups without per-row RLS overhead, and is then used within an RLS policy.

```sql
create or replace function is_team_member(team_id bigint)
returns boolean
language sql
security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.team_members
    where team_id = $1 and user_id = (select auth.uid())
  );
$$;
```

--------------------------------

### SQL: Optimize RLS Policy with Subquery

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/security-rls-performance.md

Demonstrates an optimized RLS policy using a subquery to call `auth.uid()` once, improving performance on large tables compared to calling it for every row. This pattern is crucial for efficient security checks.

```sql
create policy orders_policy on orders
  using ((select auth.uid()) = user_id);
```

--------------------------------

### SQL: Detecting and Enabling Deadlock Logging

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/lock-deadlock-prevention.md

This SQL snippet provides commands to manage deadlock detection and logging in PostgreSQL. It shows how to query `pg_stat_database` to check for recent deadlocks and how to enable deadlock logging and set a deadlock timeout using `SET` commands.

```sql
-- Check for recent deadlocks
select * from pg_stat_database where deadlocks > 0;

-- Enable deadlock logging
set log_lock_waits = on;
set deadlock_timeout = '1s';

```

--------------------------------

### Create Indexed Foreign Key in SQL

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/schema-foreign-key-indexes.md

This SQL snippet demonstrates how to correctly create a table with a foreign key constraint and then add an index to that foreign key column. Indexing the foreign key column is crucial for optimizing JOIN operations and cascade deletions.

```sql
create table orders (
  id bigint generated always as identity primary key,
  customer_id bigint references customers(id) on delete cascade,
  total numeric(10,2)
);

-- Always index the FK column
create index orders_customer_id_idx on orders (customer_id);

-- Now JOINs and cascades are fast
select * from orders where customer_id = 123;  -- Index Scan
delete from customers where id = 123;          -- Uses index, fast cascade
```

--------------------------------

### Correct Prepared Statement Usage with Pooling (SQL)

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/conn-prepared-statements.md

Presents three correct methods for using prepared statements with connection pooling: using unnamed statements, explicitly deallocating named statements after use in transaction mode, and utilizing session mode pooling where connections are held for the entire session.

```sql
-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)
-- The query is prepared and executed in a single protocol message

-- Option 2: Deallocate after use in transaction mode
prepare get_user as select * from users where id = $1;
execute get_user(123);
deallocate get_user;

-- Option 3: Use session mode pooling (port 5432 vs 6543)
-- Connection is held for entire session, prepared statements persist
```

--------------------------------

### SQL: Implement tsvector for Full-Text Search

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/advanced-full-text-search.md

Demonstrates adding a tsvector column, creating a GIN index, and performing fast full-text searches using tsvector and tsquery. This method is significantly faster than LIKE pattern matching and supports ranking.

```sql
-- Add tsvector column and index
alter table articles add column search_vector tsvector
  generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;

create index articles_search_idx on articles using gin (search_vector);

-- Fast full-text search
select * from articles
where search_vector @@ to_tsquery('english', 'postgresql & performance');

-- With ranking
select *, ts_rank(search_vector, query) as rank
from articles, to_tsquery('english', 'postgresql') query
where search_vector @@ query
order by rank desc;

-- Search multiple terms:
-- AND: both terms required
to_tsquery('postgresql & performance')

-- OR: either term
to_tsquery('postgresql | mysql')

-- Prefix matching
to_tsquery('post:*')
```

--------------------------------

### Create Partial Index for Pending Orders

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/query-partial-indexes.md

This SQL snippet demonstrates a common use case for partial indexes: indexing only pending orders based on their 'created_at' timestamp. This is beneficial as the status of orders rarely changes once completed.

```sql
create index orders_pending_idx on orders (created_at)
where status = 'pending';
```

--------------------------------

### Analyze Tables and Columns in PostgreSQL

Source: https://github.com/supabase/agent-skills/blob/main/skills/supabase-postgres-best-practices/references/monitor-vacuum-analyze.md

Manually analyze entire tables or specific columns to update statistics. This is crucial after significant data modifications to ensure the query planner makes optimal decisions. The `pg_stat_user_tables` view can be queried to check the last analysis times.

```sql
analyze orders;

analyze orders (status, created_at);

select
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
from pg_stat_user_tables
order by last_analyze nulls first;
```
Tokens:
Raw