supabase

What this skill does

# Supabase Development Expert

You are an expert in Supabase development, including Edge Functions, database schema management, migrations, PostgreSQL functions, and Row Level Security (RLS) policies. This skill provides comprehensive guidelines for all aspects of Supabase development.

## 1. Supabase Edge Functions

Generate **high-quality Supabase Edge Functions** using TypeScript and Deno runtime.

### 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 dependencies. 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:[email protected]`.
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/[email protected]/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.

### Edge Function Examples

#### 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' }}
	);
});
```

#### Using Node Built-in APIs

```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

```tsx
import express from "npm:[email protected]";

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',
			},
		},
	);
});
```

---

## 2. Database Schema Management (Declarative)

### Mandatory Instructions for Declarative Schema Management

#### 1. Exclusive Use of Declarative Schema

- **All database schema modifications must be defined within `.sql` files located in the `supabase/schemas/` directory.**
- **Do NOT** create or modify files directly in the `supabase/migrations/` directory unless the modification is about the known caveats below. Migration files are to be generated automatically through the CLI.

#### 2. Schema Declaration

- For each database entity (e.g., tables, views, functions), create or update a corresponding `.sql` file in the `supabase/schemas/` directory
- Ensure that each `.sql` file accurately represents the desired final state of the entity

#### 3. Migration Generation

- Before generating migrations, **stop the local Supabase development environment**
  ```bash
  supabase stop
  ```
- Generate migration files by diffing the declared schema against the current database state
  ```bash
  supabase db diff -f <migration_name>
  ```
  Replace `<migration_name>` with a descriptive name for the migration

#### 4. Schema File Organization

- Schema files are executed in lexicographic order. To manage dependencies (e.g., foreign keys), name files to ensure correct execution order
- When adding new columns, append them to the end of the table definition to prevent unnecessary diffs

#### 5. Rollback Procedures

- To revert changes:
  - Manually update the relevant `.sql` files in `supabase/schemas/` to reflect the desired state
  - Generate a new migration file capturing the rollback
    ```bash
    supabase db diff -f <rollback_migration_name>
    ```
  - Review the generated migration file carefully to avoid unintentional data loss

#### 6. Known Caveats

The migra diff tool used for generating schema diff is capable of tracking most database changes. However, there are edge cases where it can fail.

If you need to use any of the entities below, remember to add them through versioned migrations instead:

**Data manipulation language**
- DML statements such as insert, update, delete, etc., are not captured by schema diff

**View ownership**
- view owner and grants
- security invoker on views
- materialized views
- doesn't recreate views when altering column type

**RLS policies**
- alter policy statements
- column privileges

**Other entities**
- schema privileges are not tracked because each schema is diffed separately
- comments are not tracked
- partitions are not tracked
- alter publication ... add table ...
- create domain statements are ignored
- grant statements are duplicated from default privileges

**Non-compliance with these instructions may lead to inconsistent database states and is strictly prohibited.**

---

## 3. PostgreSQL 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.
- Alwa