Supabase Agent Skills

The supabase-postgres-best-practices skill categorizes Postgres optimization and safety rules into eight distinct sections. Rules are identified by their filename prefix. Use these categories to understand the impact and focus of specific best practice recommendations:

• Query Performance (query): Focuses on slow queries, missing indexes, and inefficient query plans. (Impact: CRITICAL)
• Connection Management (conn): Covers connection pooling, limits, and serverless strategies. (Impact: CRITICAL)
• Security & RLS (security): Covers Row-Level Security (RLS) policies, privilege management, and authentication patterns. (Impact: CRITICAL)
• Schema Design (schema): Covers table design, index strategies, partitioning, and data type selection. (Impact: HIGH)
• Concurrency & Locking (lock): Covers transaction management, isolation levels, deadlock prevention, and lock contention. (Impact: MEDIUM-HIGH)
• Data Access Patterns (data): Covers N+1 query elimination, batch operations, cursor-based pagination, and efficient fetching. (Impact: MEDIUM)
• Monitoring & Diagnostics (monitor): Covers pg_stat_statements, EXPLAIN ANALYZE, and metrics collection. (Impact: LOW-MEDIUM)
• Advanced Features (advanced): Covers Full-text search, JSONB optimization, PostGIS, and extensions. (Impact: LOW)

Implement RLS following these best practices to avoid common pitfalls:

• Views and RLS: In Postgres 15+, use CREATE VIEW ... WITH (security_invoker = true) to ensure views respect RLS. For older versions, revoke access from anon/authenticated roles or use a private schema.
• UPDATE requires SELECT: An UPDATE policy requires a corresponding SELECT policy. Without SELECT access, updates will silently return 0 rows.
• Use TO instead of auth.role(): The auth.role() function is deprecated. Specify the target role directly using the TO clause (e.g., TO authenticated or TO anon).
• Avoid BOLA/IDOR: Using TO authenticated only checks the role, not the specific user. Always combine it with an ownership predicate in the USING clause.
• UPDATE policies: Must include both USING and WITH CHECK to prevent users from reassigning rows to other users.
• SECURITY DEFINER functions: These bypass RLS. Avoid using them to fix permission errors. If required, keep them in a non-exposed schema, include an auth.uid() check in the body, and use SECURITY INVOKER where possible.

Correct RLS Patterns

Authorization with ownership (Correct):

create policy "example" on table_name for select
to authenticated
using ( (select auth.uid()) = user_id );

Secure UPDATE (Correct):

create policy "example" on table_name for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

When working with Supabase, follow these fundamental principles to ensure reliability and security:

1. Verify against current documentation: Supabase features and API signatures change frequently. Always check https://supabase.com/changelog.md for breaking-change tags before implementing new features.
2. Verify your work: Always run a test query after implementing a fix to confirm it works.
3. Recover from errors: If an approach fails 2-3 times, stop and reconsider. Check logs and documentation rather than retrying the same command.
4. Expose tables to the Data API: Newly created tables might not be automatically exposed via the REST API. You may need to explicitly grant access to anon and authenticated roles via SQL GRANT commands. Note that this is separate from Row Level Security (RLS).
5. Enable RLS in exposed schemas: Always enable RLS on every table in exposed schemas (like public). For private schemas, use RLS as defense in depth.
6. Security Checklist: Always review security implications for auth, RLS, views, storage, and user data (see specific security records for details).

The supabase-postgres-best-practices skill is a comprehensive guide for Postgres performance optimization and schema design. You should load this skill BEFORE performing any of the following tasks:

Schema & Data Definition

• Creating or altering tables and columns (including choosing column types).
• Schema design and migrations.
• Declarative schema files.
• Creating indexes, triggers, database functions, queues, and scheduled jobs (pg_cron, pgmq).
• Implementing vector/semantic search (pgvector).
• Restoring dumps (pg_restore) or importing data.

Security & Logic

• Writing Row-Level Security (RLS) policies and verification tests.

Troubleshooting & Optimization

• Diagnosing slow queries, high CPU, or timeouts.
• Analyzing EXPLAIN plans.
• Investigating connection exhaustion, locking, or bloat.
• Debugging data visibility issues (e.g., rows visible to the wrong user or tenant).

Advisory locks allow you to coordinate application logic (like ensuring only one process runs a specific task) without the overhead of creating dummy rows or using row-level locks. Instead of locking a physical row in a table, you lock an abstract identifier.

There are two main types of advisory locks in PostgreSQL:

1. Session-level locks: These persist for the duration of the database connection. You must manually release them using pg_advisory_unlock or they will be released when the session disconnects.
2. Transaction-level locks: These are automatically released when the current transaction commits or rolls back.

-- Session-level advisory lock (released on disconnect or unlock)
select pg_advisory_lock(hashtext('report_generator'));
-- ... do exclusive work ...
select pg_advisory_unlock(hashtext('report_generator'));

-- Transaction-level lock (released on commit/rollback)
begin;
select pg_advisory_xact_lock(hashtext('daily_report'));
-- ... do work ...
commit;  -- Lock automatically released