Multi-Tenancy Architecture

A²D implements enterprise-grade multi-tenancy with complete data isolation between organizations using PostgreSQL Row Level Security.

Overview

Multi-tenancy allows multiple organizations to use A²D while keeping their data completely separate and secure.

Key Benefits:

• Complete Isolation: Organizations cannot see each other’s data
• Database-Level Security: Enforced at PostgreSQL level, not application
• Zero Configuration: Automatic filtering with no code changes
• Scalable: Supports unlimited organizations efficiently

How Multi-Tenancy Works

Organization-Based Isolation

Every piece of data in A²D belongs to exactly one organization:

Data Organization:

• Each organization has unique ID (UUID)
• Every data table includes organization_id column
• Users can belong to multiple organizations via the organization_members table (with a role: owner or member)
• A “current organization” is stored in a cookie so the app and RLS scope data to the selected org when relevant
• RLS policies filter all queries so users only see data from organizations they are members of

Organization membership and switching

• Users can be members of multiple organizations. Each membership has a role: owner (tenant admin) or member.
• The app remembers your current organization (e.g. via a cookie). All data and actions in the UI are scoped to that organization.
• Current organization is stored in a cookie so the app and API scope data to the selected org; when the user has multiple orgs, the sidebar shows an organization switcher (dropdown with org name and role). Selecting another org switches context and refreshes the page so you see that org’s data.
• Platform admins (configured via environment) have access to the Admin area across the platform; organization owners have full control within their organization (e.g. settings, members, AI access). See access control and documentation for details.

Database Schema Design

Organization ID Column

Every table includes an organization_id foreign key:

CREATE TABLE mcp_servers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  name TEXT NOT NULL,
  description TEXT,
  type TEXT NOT NULL,
  -- ... other columns ...
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Index for fast filtering
CREATE INDEX idx_mcp_servers_organization 
ON mcp_servers(organization_id);

All Multi-Tenant Tables:

• users
• mcp_servers
• mcp_tools
• mcp_resources
• mcp_prompts
• agent_cards (same pattern as mcp_servers: organization_id = get_user_organization_id())
• agent_skills (access via card ownership: card must belong to user’s organization)
• design_rules
• openapi_specs

Row Level Security (RLS)

RLS is PostgreSQL’s built-in feature for automatic row-level data filtering.

What is RLS?

-- Enable RLS on table
ALTER TABLE mcp_servers ENABLE ROW LEVEL SECURITY;
 
-- Create policy for SELECT (membership-based: user must be in organization_members)
CREATE POLICY "Users can view servers in orgs they belong to"
ON mcp_servers
FOR SELECT
USING (organization_id IN (SELECT get_user_organization_ids()));

How RLS Works

A²D uses membership-based RLS: the database identifies the user by auth.uid() (Supabase Auth) and applies policies using helper functions that read from the organization_members table.

Query Execution Flow:

1. User authenticates → Receives JWT from Supabase Auth (user id only).
2. User makes query → Application sends query to database.
3. RLS evaluates policies → Helper get_user_organization_ids() returns all organization IDs the user belongs to (from organization_members). Policies allow access only to rows whose organization_id is in that set (and, for owner-only actions, get_user_org_role(organization_id) = 'owner').
4. Filtered results returned → Only data from organizations the user is a member of.

The current organization in the app (which org’s data is shown in the UI) is stored in a cookie (mcp_mock_current_organization_id). API routes resolve the current org from that cookie and membership; RLS still allows access to data in any org the user is a member of.

Example:

// User's query (no filtering needed in code!)
const { data } = await supabase
  .from('mcp_servers')
  .select('*')
 
// Returns only servers from orgs the user is a member of
// (RLS uses get_user_organization_ids())

-- RLS effectively restricts to user's orgs
SELECT * FROM mcp_servers 
WHERE organization_id IN (SELECT get_user_organization_ids());

Complete RLS Policies

A²D implements comprehensive policies using membership-based helpers. The exact policy set is defined in the Supabase migrations; the pattern is summarized below.

SELECT Policy

CREATE POLICY "select_own_orgs" 
ON mcp_servers
FOR SELECT 
USING (organization_id IN (SELECT get_user_organization_ids()));

Effect: Users can only read rows in organizations they belong to (per organization_members). Visibility and editing are by organization membership only; no per-asset share tables (e.g. asset_shares) are used for access control. Policies use organization_id IN (SELECT get_user_organization_ids()) for SELECT, UPDATE, and DELETE on assets and their child tables.

INSERT / UPDATE / DELETE

For tables like mcp_servers, INSERT/UPDATE/DELETE typically use the same membership check: organization_id IN (SELECT get_user_organization_ids()). For tables that only owners may modify (e.g. organization_members, organization_invites), policies also require get_user_org_role(organization_id) = 'owner'.

User Sign-Up Flow

When a new user signs up, A²D creates an organization and links the user:

Process:

1. Create Auth User: Supabase Auth creates account
2. Create Organization: New organization record with provided name
3. Link User: User profile linked to organization via organization_id; user is also added to organization_members as owner for that org.
4. Session Started: User logged in; current org is set (cookie) so the app and API scope to that organization.

Why Service Role?

Security: Service role is only used server-side in API routes, never exposed to browser.

Data Isolation Examples

User Can Only See Own Organization

Scenario: Two users from different organizations query servers

const supabase = createClient() // Alice's JWT
const { data } = await supabase
  .from('mcp_servers')
  .select('*')
 
// Returns only Acme Corp servers (5 servers)
// RLS filters by organizations Alice is a member of

const supabase = createClient() // Charlie's JWT
const { data } = await supabase
  .from('mcp_servers')
  .select('*')
 
// Returns only TechCo servers (8 servers)
// Different membership, different results

User Cannot Insert for Other Organizations

Scenario: Alice tries to create a server for TechCo

const { data, error } = await supabase
  .from('mcp_servers')
  .insert({
    name: 'Sneaky Server',
    organization_id: 'techco-org-id' // ❌ Not Alice's org
  })
 
// RLS blocks this insert
// Error: "Row violates row-level security policy"

Supabase Client Types

A²D uses three types of Supabase clients with different security contexts:

Browser Client (RLS Enforced)

Usage: Client-side components

import { createClient } from '@/lib/supabase/client'
 
const supabase = createClient()
// Uses: Anonymous key + user JWT from cookie
// RLS: Enforced (user's organization only)

Characteristics:

• Runs in browser
• User-specific queries
• RLS protects all operations
• Most common client type

Server Client (RLS Enforced)

Usage: Server Components and Server Actions

import { createClient } from '@/lib/supabase/server'
 
const supabase = await createClient()
// Uses: Anonymous key + user JWT from server-side cookie
// RLS: Enforced (user's organization only)

Characteristics:

• Runs on server
• User-specific queries
• RLS protects all operations
• Used for SSR and actions

Service Client (RLS Bypassed)

Usage: Public API routes and admin operations

import { createServiceClient } from '@/lib/supabase/service'
 
const supabase = createServiceClient()
// Uses: Service role key (admin access)
// RLS: Bypassed (full database access)
// ⚠️ Use with caution!

When to Use Service Client:

• User signup (before JWT exists)
• Public MCP endpoints (stateless, server-specific access)
• Admin operations (with manual org filtering)

When NOT to Use:

• Regular user operations
• Data queries on behalf of users
• Any operation that should respect RLS

Organization Management

Organization Structure

interface Organization {
  id: string           // UUID primary key
  name: string         // Organization display name
  created_at: string   // Creation timestamp
  updated_at: string   // Last update timestamp
}

Creating Organizations

Organizations are created during user signup:

// API route with service role client
const { data: org, error } = await supabase
  .from('organizations')
  .insert({
    name: organizationName
  })
  .select()
  .single()
 
if (error) throw new Error('Failed to create organization')
 
// Link user to organization
await supabase.from('users').insert({
  id: authUserId,
  email: userEmail,
  organization_id: org.id
})

Org details and member management:

• Org details: Name, ID, and created date are shown in Settings → Organization (or via GET /api/org).
• For org owners only: In Settings → Organization, owners see Invite to organization (email and role; invite link is generated), Pending invites (copy link, revoke), and Members (change role, toggle AI access per member, generate password reset link, remove member). New members can accept an invite via the link (e.g. /accept-invite?token=...). See Organization & Members for the user-facing guide.

Security Benefits

Database-Level Enforcement

RLS operates at the PostgreSQL level, not in application code:

Benefits:

• Cannot be bypassed by application bugs
• Always enforced for every query
• Consistent across all database clients
• Auditable via PostgreSQL logs
• Zero-trust model: Database doesn’t trust application

Defense in Depth

Even buggy code is protected:

// Buggy code that forgot to filter
const { data } = await supabase
  .from('mcp_servers')
  .select('*')
  // Developer forgot: .eq('organization_id', userOrgId)
 
// ✅ RLS still protects!
// Only returns user's organization data

Attack Prevention

Common attacks are automatically prevented:

Testing Multi-Tenancy

Test Data Isolation

Setup: Create two test accounts in different organizations

// Test User A (Org A)
const supabaseA = createClientWithToken(tokenA)
const { data: serversA } = await supabaseA
  .from('mcp_servers')
  .select('*')
 
console.log(serversA.length) // e.g., 5
 
// Test User B (Org B)
const supabaseB = createClientWithToken(tokenB)
const { data: serversB } = await supabaseB
  .from('mcp_servers')
  .select('*')
 
console.log(serversB.length) // e.g., 3 (different count)

Expected: Each user sees only their organization’s data

Test Cross-Organization Blocking

Attempt: Try to access another organization’s resource

// User A knows a server ID from Org B
const serverBId = 'uuid-from-org-b'
 
const { data, error } = await supabaseA
  .from('mcp_servers')
  .select('*')
  .eq('id', serverBId)
  .single()
 
// Expected: Returns null or error
// RLS blocks access to Org B's server
console.log(data) // null

Performance Considerations

RLS Performance Impact

Overhead: ~5-10ms per query (minimal)

Why Fast:

• Simple equality checks
• Indexed organization_id columns
• Compiled query plans
• PostgreSQL optimizations

Optimization Strategies

-- Already applied in migrations
CREATE INDEX idx_mcp_servers_organization 
ON mcp_servers(organization_id);

-- Good: Simple membership check (used in A²D)
USING (organization_id IN (SELECT get_user_organization_ids()))
 
-- Bad: Complex joins or subqueries
USING (organization_id IN (
  SELECT org_id FROM complex_view WHERE ...
))

-- Automatic with Supabase client
-- PostgreSQL caches query plans

Best Practices:

• Keep RLS policies simple (equality checks)
• Index all foreign keys
• Use connection pooling (Supabase handles this)
• Monitor slow queries in Supabase dashboard

Troubleshooting

”Row violates row-level security policy”

Cause: The operation is not allowed by RLS for this user. Common cases: (1) the user is not in organization_members for the organization in question; (2) the action requires owner role but the user is a member (e.g. inserting/updating org members or invites).

Solution:

• Ensure the user has a row in organization_members for the org they are acting on.
• For owner-only operations (e.g. inviting users, changing roles), the current user must have role = 'owner' for that org.
• In the app, the current organization is set via a cookie; ensure the cookie matches an org the user belongs to. Do not assume the JWT contains organization_id; use the app’s current-org resolution (cookie + membership) instead.

”No rows returned” unexpectedly

Causes:

1. User has no rows in organization_members, or the current org (cookie) is not one of their orgs.
2. RLS policies not configured correctly.
3. Data doesn’t exist for that org.

Debug Steps:

• Verify the user has at least one row in organization_members: SELECT * FROM organization_members WHERE user_id = auth.uid();
• In the app, ensure the current org cookie matches one of those orgs (e.g. switch org in the sidebar).
• In Supabase SQL editor (as a superuser), you can call get_user_organization_ids() to see which org IDs the current user can access (when run in a session with that user’s JWT).
• Check if RLS is enabled: SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND rowsecurity = true;
• To inspect data regardless of RLS, use the service role client (server-side only) and filter by organization_id manually.

Cannot query data after signup

Cause: User may not yet be in organization_members, or the current org cookie is missing/wrong.

Solution:

1. Check that the user has a row in organization_members (signup should create one as owner for the new org).
2. Ensure the organization was created and the user was linked (e.g. users.organization_id and organization_members).
3. Sign out and sign in again so the app can set the current org cookie correctly.

Best Practices

DO ✅

• Use browser/server clients for user operations
• Rely on RLS for data isolation (don’t manually filter)
• Index organization_id on all multi-tenant tables
• Test with multiple orgs during development
• Use service client sparingly and only when necessary
• Monitor RLS performance via Supabase dashboard

DON’T ❌

• Don’t manually filter by organization_id (RLS does it)
• Don’t use service client for regular user queries
• Don’t bypass RLS in application code
• Don’t assume JWT contains organization_id — current org is determined by cookie and membership
• Don’t share JWT tokens between users
• Don’t modify organization_id after creation (immutable)

Advanced Topics

Adding Users to Existing Organizations

Invitations and organization member management are implemented.

• Invitations: An org owner sends an invite (email and role) from Settings → Organization. The system returns an invite link (e.g. .../accept-invite?token=...). The invitee opens the link, signs up or logs in, and accepts the invite; they are added to the org with the chosen role. Owners can revoke pending invites from the same Settings tab.
• Members: Listed in Settings → Organization. Owners can change a member’s role (owner/member), toggle AI access per member, generate a password reset link for a member, or remove a member (assets in that org transfer to the owner as per implementation).
• Platform admins (env allowlist): Can list all organizations and users (Admin → Organizations, Admin → Users), reset passwords, and delete users. This is separate from org-level owner actions.

See Organization & Members for the user-facing guide.

Transferring Data Between Organizations

Organization-Level Settings

Design rules are an example of organization-level configuration. RLS allows access to rows in any org the user is a member of (e.g. organization_id IN (SELECT get_user_organization_ids())). The app uses the current org (cookie) to decide which org’s settings to show or update.

All users in the organization share the same design rules for that org.

Related Documentation

• Multi-Tenancy (this page) - Overall architecture and data isolation
• Database Schema - Complete schema with all multi-tenant tables
• Authentication - User signup and authentication
• Security - Security troubleshooting

Questions? See Database Problems or open an issue.