Database

Postgres via Supabase with RLS on workspace tables so a stray client query still cannot cross tenants if you set policies correctly.

Workspace scoping

Every row that belongs to a tenant includes a workspace_id column. Your application code must always filter by workspace_id, and your RLS policies enforce it as a second layer.

Migrations

Run these in the Supabase SQL Editor in order:

#	File	What it creates
1	`supabase/001_schema.sql`	Core tables, enums, indexes, triggers
2	`supabase/002_rls.sql`	Row-Level Security policies and helper functions
3	`supabase/003_soft_delete_and_dashboard.sql`	Soft delete column, partial indexes, `daily_audit_counts()`
4	`supabase/004_onboarding.sql`	Onboarding `setup_complete` flag
5	`supabase/005_tags_comments_2fa.sql`	Tags, comments, 2FA enforcement
6	`supabase/006_remaining_features.sql`	API keys, webhooks, bookmarks, saved views, custom fields, attachments, scheduled actions, IP allowlist, login history, notifications

See Supabase Setup for the full setup guide.

Adding a new workspace-scoped table

Follow these steps to add a new table that integrates with the existing patterns:

1. Create the table

CREATE TABLE IF NOT EXISTS public.your_entity (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  -- your columns here
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ  -- optional: for soft delete
);

2. Add indexes

CREATE INDEX your_entity_workspace_idx
  ON public.your_entity(workspace_id, created_at DESC)
  WHERE deleted_at IS NULL;

3. Add the auto-update trigger

CREATE TRIGGER your_entity_updated_at
  BEFORE UPDATE ON public.your_entity
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

4. Enable RLS

ALTER TABLE public.your_entity ENABLE ROW LEVEL SECURITY;

CREATE POLICY "your_entity_select" ON public.your_entity
  FOR SELECT USING (is_workspace_member(workspace_id));

CREATE POLICY "your_entity_insert" ON public.your_entity
  FOR INSERT WITH CHECK (is_workspace_member(workspace_id));

CREATE POLICY "your_entity_update" ON public.your_entity
  FOR UPDATE USING (is_workspace_member(workspace_id));

CREATE POLICY "your_entity_delete" ON public.your_entity
  FOR DELETE USING (workspace_role(workspace_id) IN ('OWNER', 'ADMIN'));

5. Create server actions

Follow the authenticate -> authorize -> validate -> write -> audit pattern in your server actions.

RLS helper functions

These functions are defined in 002_rls.sql and used in all RLS policies:

Function	Returns	Purpose
`is_workspace_member(workspace_id)`	boolean	Checks if the current user is a member of the workspace
`workspace_role(workspace_id)`	text	Returns the current user's role in the workspace

Usage in policies

-- Allow all workspace members to read
CREATE POLICY "select" ON public.your_table
  FOR SELECT USING (is_workspace_member(workspace_id));

-- Allow only OWNER and ADMIN to delete
CREATE POLICY "delete" ON public.your_table
  FOR DELETE USING (workspace_role(workspace_id) IN ('OWNER', 'ADMIN'));

Supabase client strategy

SaaSForge Core uses two types of Supabase clients:

Client	When to use	RLS behavior
`createClient()`	Auth operations (sign-in, sign-up)	Respects RLS
`createAdminClient()`	All server actions after auth/authz	Bypasses RLS

Server actions use createAdminClient() (service role key) after verifying auth via requireUser() and authorization via requireRole(). This ensures queries succeed regardless of cookie forwarding issues in Next.js server actions.

Common "RLS blocked" symptoms

Symptom	Fix
Inserts fail with permission errors	Verify `002_rls.sql` was run and policies exist
Selects return empty data	Check the user is a workspace member
Service role queries work but user queries don't	Ensure RLS policies reference `auth.uid()` correctly
Newly created tables return nothing	Add RLS policies using `is_workspace_member()`

Tips for working with the database

Always scope by workspace_id -- never query across workspaces
Use deleted_at for soft delete -- avoid hard deletes where possible
Always call insertAuditLog() -- keep the audit trail complete
Use JSONB for flexible data -- custom_fields, metadata, options are all JSONB
Create partial indexes -- they significantly improve query performance for filtered data