Supabase Database

Quick Start

When working with Supabase:

• Always use Supabase client from @/lib/supabase/server or @/lib/supabase/client

• Never make queries without filtering by tenant_id in multitenant tables

• Always enable RLS on tables and include tenant verification in policies

• Use prepared parameters (Supabase client methods, never string concatenation)

• Use migrations in supabase/migrations/ for schema changes

Key Files

• src/lib/supabase/

• Supabase client utilities

• src/lib/integrations/supabase/

• Supabase integration

• supabase/migrations/

• Database migrations

• supabase/functions/

• Edge functions

• src/lib/auth/enterprise-rls-utils.ts

• RLS utilities

Common Patterns

Basic Query with Tenant

import { createClient } from '@/lib/supabase/server'; import { getTenantFromRequest } from '@/lib/tenant/tenant-service';

const supabase = createClient(); const tenant = await getTenantFromRequest(request);

const { data, error } = await supabase .from('products') .select('id, name, price, category_id') .eq('tenant_id', tenant.id) .eq('active', true) .order('created_at', { ascending: false }) .limit(20);

if (error) { console.error('Query error:', error); return NextResponse.json({ error: error.message }, { status: 500 }); }

Insert with Tenant

const { data, error } = await supabase .from('products') .insert({ name: 'Pintura Blanca', price: 5000, tenant_id: tenant.id, category_id: categoryId, active: true, }) .select() .single();

Update with Tenant

const { data, error } = await supabase .from('products') .update({ price: 5500 }) .eq('id', productId) .eq('tenant_id', tenant.id) .select() .single();

RLS Policy Example

-- Enable RLS ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Policy for tenant isolation CREATE POLICY "tenant_isolation_products" ON products FOR ALL USING ( tenant_id = ( SELECT id FROM tenants WHERE slug = current_setting('app.tenant_slug', true) ) );

-- Policy for public read (if needed) CREATE POLICY "public_read_products" ON products FOR SELECT USING (active = true);

Using RLS Utils

import { executeWithRLS } from '@/lib/auth/enterprise-rls-utils';

const result = await executeWithRLS( enterpriseContext, async (client, rlsContext) => { return await client .from('products') .select('*') .eq('tenant_id', rlsContext.tenantId); } );

Migration Template

-- Migration: add_new_column_to_products -- Created: 2026-01-23

BEGIN;

-- Add new column ALTER TABLE products ADD COLUMN IF NOT EXISTS new_field VARCHAR(255);

-- Create index if needed CREATE INDEX IF NOT EXISTS idx_products_new_field ON products(new_field) WHERE new_field IS NOT NULL;

-- Update RLS policy if needed -- (Add to existing policy or create new one)

COMMIT;

Best Practices

• Always filter by tenant_id in multitenant tables

• Use .select() with specific fields instead of *

• Enable RLS on all tables

• Use migrations for all schema changes

• Test migrations in development first

• Use transactions for multiple related operations

• Index frequently queried fields (tenant_id, foreign keys)

Commands

Create new migration

supabase migration new migration_name

Apply migrations

supabase db push

Reset database (development)

supabase db reset

Generate TypeScript types

supabase gen types typescript --local > src/types/database.ts