Working with Supabase PostgreSQL in your module. Multi-tenant queries, Row Level Security, and the useModuleApi hook.

Database & Data Access

All modules share a single Supabase PostgreSQL database. Data isolation between tenants is enforced through Row Level Security (RLS) and the organization_id pattern.

useModuleApi Hook

The useModuleApi hook provides tenant-scoped CRUD operations:

import { useModuleApi } from '@/hooks/useModuleApi';
 
export default function ItemsPage() {
  const { query, insert, update, remove, organizationId } = useModuleApi();
 
  // SELECT — auto-filters by organization_id
  async function loadItems() {
    const { data, error } = await query('mymodule_items')
      .select('*')
      .order('created_at', { ascending: false });
  }
 
  // INSERT — auto-adds organization_id
  async function addItem(name: string) {
    await insert('mymodule_items', { name });
  }
 
  // UPDATE — scoped to current tenant
  async function updateItem(id: string, name: string) {
    await update('mymodule_items', { name }, { id });
  }
 
  // DELETE — scoped to current tenant
  async function deleteItem(id: string) {
    await remove('mymodule_items', { id });
  }
}

Hook API Reference

Method	Signature	Description
`query`	`(table: string) => PostgrestFilterBuilder`	Start a SELECT query with automatic `organization_id` filter
`insert`	`(table: string, data: Record) => Promise`	Insert with auto `organization_id`
`update`	`(table: string, data: Record, filters: Record) => Promise`	Update scoped to tenant
`remove`	`(table: string, filters: Record) => Promise`	Delete scoped to tenant
`supabase`	`SupabaseClient`	Raw Supabase client (for advanced queries)
`organizationId`	`string \| null`	Current tenant UUID

Creating Your Own Tables

Naming Convention

Prefix all your tables with your module slug to avoid conflicts:

{module_slug}_{table_name}

Examples:

myanalytics_reports
myanalytics_dashboards
myanalytics_settings

SQL Migration

-- Create your table
CREATE TABLE myanalytics_reports (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id),
  title TEXT NOT NULL,
  data JSONB DEFAULT '{}',
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
 
-- Create index for tenant queries
CREATE INDEX idx_myanalytics_reports_org
  ON myanalytics_reports(organization_id);
 
-- Enable Row Level Security
ALTER TABLE myanalytics_reports ENABLE ROW LEVEL SECURITY;
 
-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
  ON myanalytics_reports
  FOR ALL
  USING (
    organization_id IN (
      SELECT organization_id FROM user_organizations
      WHERE user_id = auth.uid()
    )
  );

Always enable RLS on your tables. Without RLS, any authenticated user could access data from other organizations. The marketplace review will check for this.

Row Level Security (RLS)

RLS policies control which rows each user can access. The pattern for modules:

-- Users can only access their organization's data
CREATE POLICY "org_access" ON your_table
  FOR ALL
  USING (
    organization_id IN (
      SELECT organization_id FROM user_organizations
      WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM user_organizations
      WHERE user_id = auth.uid()
    )
  );

Common RLS Patterns

Read-only for regular users, write for admins:

CREATE POLICY "read_all" ON your_table
  FOR SELECT USING (true);
 
CREATE POLICY "write_admin" ON your_table
  FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM user_organizations
      WHERE user_id = auth.uid()
        AND organization_id = your_table.organization_id
        AND role IN ('admin', 'owner')
    )
  );

User can only see their own records:

CREATE POLICY "own_records" ON your_table
  FOR ALL
  USING (created_by = auth.uid());

Using React Query

For complex data fetching, combine useModuleApi with TanStack React Query:

import { useQuery, useMutation, useQueryClient } from '@tanstack/react-query';
import { useModuleApi } from '@/hooks/useModuleApi';
 
export function useReports() {
  const { query, insert } = useModuleApi();
  const queryClient = useQueryClient();
 
  const reports = useQuery({
    queryKey: ['reports'],
    queryFn: async () => {
      const { data, error } = await query('myanalytics_reports')
        .select('*')
        .order('created_at', { ascending: false });
      if (error) throw error;
      return data;
    },
  });
 
  const createReport = useMutation({
    mutationFn: (title: string) => insert('myanalytics_reports', { title }),
    onSuccess: () => queryClient.invalidateQueries({ queryKey: ['reports'] }),
  });
 
  return { reports, createReport };
}

Direct Supabase Client

For advanced queries not covered by useModuleApi, access the Supabase client directly:

const { supabase, organizationId } = useModuleApi();
 
// Complex query with joins
const { data } = await supabase
  .from('myanalytics_reports')
  .select(`
    *,
    created_by_user:auth.users(email, raw_user_meta_data)
  `)
  .eq('organization_id', organizationId)
  .gte('created_at', '2026-01-01')
  .order('created_at', { ascending: false })
  .limit(50);
 
// Supabase realtime subscription
const channel = supabase
  .channel('reports')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'myanalytics_reports',
    filter: `organization_id=eq.${organizationId}`,
  }, (payload) => {
    console.log('New report:', payload.new);
  })
  .subscribe();

Database & Data Access

On this page