Instruction file imported from Wamocon/geburtstagspilot (.github/instructions/supabase.instructions.md). Copyright stays with the author.

Supabase Integration Instructions

Client Setup

• Use @supabase/ssr for Next.js App Router integration.
• Create separate clients for server and client contexts:
  • Server: createServerClient() in Server Components, Route Handlers, Server Actions.
  • Client: createBrowserClient() in Client Components only.
• Never expose SUPABASE_SERVICE_ROLE_KEY to the browser - it bypasses RLS.

Schema Awareness

• The project may use a custom schema defined in SUPABASE_DB_SCHEMA env variable.
• When creating the Supabase client, pass the schema option:

  const supabase = createClient(url, key, {
    db: { schema: process.env.SUPABASE_DB_SCHEMA || 'public' }
  });

Row-Level Security (RLS)

• Always enable RLS on every table.
• Write policies that match your auth patterns (e.g. auth.uid() = user_id).
• Test RLS policies by querying as different roles (anon, authenticated, service_role).

Migrations

• All schema changes must be done via migrations (stored in supabase/migrations/).
• Use Supabase MCP tools for creating/managing migrations.
• Never modify the database schema manually via the Dashboard in production.

Data

• Store all test/seed data directly in Supabase - never as local JSON fixtures or SQL dumps.

Multi-Schema Setup on Hosted Supabase (via MCP)

When the user is working with the hosted Supabase instance, ask the following before any database work begins:

Step 1 - Ask the User

"Do you want to use multiple PostgreSQL schemas in your hosted Supabase project? (e.g. public, app, reporting)"

• If no: proceed with the default public schema.
• If yes: proceed with the multi-schema setup flow below.

Step 2 - Collect Schema Requirements

Ask:

1. "How many custom schemas do you want to create?"
2. "What are the names of the schemas?" (collect as a list)

Do not proceed until you have explicit schema names from the user.

Step 3 - Identify the Correct Supabase Project via MCP

• Use the Supabase MCP tools to list all available projects.
• Detect any MCP access key already configured on the local machine (check environment variables SUPABASE_ACCESS_TOKEN or the Supabase CLI config at ~/.supabase/access-token).
• Present the matched project name and ID to the user: "I found your Supabase project: [project name] (ID: [project-id]). I will make changes to this project only."
• Do not touch any other project. If multiple projects are found and the target is ambiguous, ask the user to confirm which one.

Step 4 - Create Schemas via MCP

For each schema name provided, execute the following SQL via mcp_com_supabase__execute_sql:

CREATE SCHEMA IF NOT EXISTS "<schema_name>";

After creation, immediately grant the necessary permissions:

-- Allow the authenticated role to use the schema
GRANT USAGE ON SCHEMA "<schema_name>" TO authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA "<schema_name>" TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA "<schema_name>" GRANT ALL ON TABLES TO authenticated;

-- Allow the anon role (for public/unauthenticated access if needed)
GRANT USAGE ON SCHEMA "<schema_name>" TO anon;
GRANT SELECT ON ALL TABLES IN SCHEMA "<schema_name>" TO anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA "<schema_name>" GRANT SELECT ON TABLES TO anon;

-- Allow the service_role full access
GRANT ALL ON SCHEMA "<schema_name>" TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA "<schema_name>" TO service_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA "<schema_name>" GRANT ALL ON TABLES TO service_role;

Step 5 - Expose Schema via Supabase API

To allow the PostgREST API to expose the new schema, the search_path must be updated. This requires a change in the Supabase Dashboard under Settings → API → Extra search path.

• If MCP supports this setting: apply it automatically.
• If MCP cannot apply this: inform the user with exact manual steps:

  "Please go to your Supabase Dashboard → Settings → API → Extra search path, and add <schema_name> to the list. Then click Save."

Step 6 - Update Environment Variables

After the project is identified and schemas are created, automatically extract the hosted Supabase connection details via MCP (mcp_com_supabase__get_project_url and mcp_com_supabase__get_publishable_keys) and write all required variables to .env.local:

# Hosted Supabase - Production
NEXT_PUBLIC_SUPABASE_URL=<project API URL>
NEXT_PUBLIC_SUPABASE_ANON_KEY=<project anon key>
SUPABASE_SERVICE_ROLE_KEY=<project service role key>
SUPABASE_DB_SCHEMA=<primary_schema_name>
# Additional schemas (reference only - not set as runtime variable)
# SUPABASE_SCHEMA_<NAME>=<schema_name>

Rules:

• Check for existing entries before writing - update in place, never duplicate.
• SUPABASE_SERVICE_ROLE_KEY must never have the NEXT_PUBLIC_ prefix.
• If multiple schemas are used, list them all in comments above SUPABASE_DB_SCHEMA so the developer has a clear reference.
• After writing the variables, confirm to the user: "Environment variables have been set in .env.local. Your app is now pointed at the hosted Supabase project [project name]."

Verify the Connection

Once the variables are written, run a quick connectivity check to confirm the production database is reachable:

1. Use mcp_com_supabase__execute_sql to run SELECT 1; against the target project.
2. If successful: confirm "Connection to hosted Supabase verified. The production database is responding correctly."
3. If it fails: diagnose using mcp_com_supabase__get_logs, report the exact error, and fix the configuration before proceeding.

Step 7 - Handle Missing MCP Token

If the MCP access token is not found locally:

1. Inform the user: "No Supabase MCP access token was found. I need a Personal Access Token (PAT) to manage your hosted Supabase project."
2. Ask: "Please provide your Supabase Personal Access Token. You can generate one at: https://supabase.com/dashboard/account/tokens"
3. Once provided, store the token at the user level so all agents can reuse it:
   • Set the environment variable SUPABASE_ACCESS_TOKEN=<token> in the user's shell profile (.bashrc, .zshrc, or Windows user environment variables).
   • Confirm: "Token stored. All agents can now access Supabase via MCP using this token."
4. Never hardcode the token in project files or commit it to version control.