Multi-Tenant Context Preservation Skill

Purpose

Ensures organization_id context is preserved throughout the entire request lifecycle in a multi-tenant application. Prevents data leaks between tenants (CRITICAL security requirement).

Impact: 100% prevention of tenant data leaks through systematic enforcement.

When to Use

Triggers:

• Writing new database query (SELECT, INSERT, UPDATE, DELETE)
• Creating new API endpoint
• Modifying worker logic
• Testing multi-tenant features
• Security review before PR
• Guardian agent review

Critical areas:

• Database queries (ALL must filter by organization_id)
• Worker queue payloads
• API response serialization
• Error messages (may leak data)

Multi-Tenant Architecture

Context Flow

1. User Request
   ↓ (JWT contains organization_id)
2. Middleware extracts organization_id
   ↓ (req.user.organizationId)
3. Service Layer
   ↓ (Pass organizationId to all DB calls)
4. Database Query
   ↓ (WHERE organization_id = $1)
5. RLS Policy (Defense-in-Depth)
   ↓ (PostgreSQL enforces at DB level)
6. Response (only tenant's data)

Defense-in-Depth Layers

Layer 1: Application Code (THIS SKILL)

• Explicit WHERE organization_id = $1 in queries
• Context passed through worker payloads
• Validation at API boundaries

Layer 2: RLS Policies (Database)

• PostgreSQL Row-Level Security
• Enforced even if application code fails
• See database/schema.sql

Layer 3: Tests (Verification)

• Multi-tenant isolation tests
• Prove org1 cannot see org2 data
• Run in CI/CD pipeline

Context Preservation Rules

Rule 1: Database Queries ALWAYS Filter by organization_id

❌ WRONG:

-- Missing organization_id filter - SECURITY BUG
SELECT * FROM roasts WHERE user_id = $1;

✅ CORRECT:

-- Filters by organization_id - safe
SELECT * FROM roasts
WHERE user_id = $1
  AND organization_id = $2;

Example (Node.js):

// ❌ WRONG - Missing organization_id
async function getRoasts(userId) {
  const result = await db.query(
    'SELECT * FROM roasts WHERE user_id = $1',
    [userId]
  );
  return result.rows;
}

// ✅ CORRECT - Includes organization_id
async function getRoasts(userId, organizationId) {
  const result = await db.query(
    'SELECT * FROM roasts WHERE user_id = $1 AND organization_id = $2',
    [userId, organizationId]
  );
  return result.rows;
}

Rule 2: INSERT Must Include organization_id

❌ WRONG:

// Missing organization_id in INSERT
await db.query(
  'INSERT INTO roasts (user_id, text, created_at) VALUES ($1, $2, NOW())',
  [userId, text]
);

✅ CORRECT:

// Includes organization_id in INSERT
await db.query(
  'INSERT INTO roasts (user_id, organization_id, text, created_at) VALUES ($1, $2, $3, NOW())',
  [userId, organizationId, text]
);

Rule 3: UPDATE Must Filter by organization_id

❌ WRONG:

// Missing organization_id filter - can update other tenant's data!
await db.query(
  'UPDATE roasts SET status = $1 WHERE id = $2',
  [newStatus, roastId]
);

✅ CORRECT:

// Filters by organization_id - prevents cross-tenant updates
await db.query(
  'UPDATE roasts SET status = $1 WHERE id = $2 AND organization_id = $3',
  [newStatus, roastId, organizationId]
);

Rule 4: DELETE Must Filter by organization_id

❌ WRONG:

// Missing organization_id filter - can delete other tenant's data!
await db.query(
  'DELETE FROM roasts WHERE id = $1',
  [roastId]
);

✅ CORRECT:

// Filters by organization_id - prevents cross-tenant deletions
await db.query(
  'DELETE FROM roasts WHERE id = $1 AND organization_id = $2',
  [roastId, organizationId]
);

Context Extraction

From JWT Token (API Requests)

// middleware/auth.js
async function authenticateJWT(req, res, next) {
  const token = req.headers.authorization?.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: 'UNAUTHORIZED: Token required' });
  }

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);

    // Extract organization_id from JWT payload
    req.user = {
      id: decoded.userId,
      organizationId: decoded.organizationId,  // CRITICAL: Always in JWT
      email: decoded.email
    };

    // Validate organization_id present
    if (!req.user.organizationId) {
      throw new Error('INVALID_TOKEN: Missing organization_id');
    }

    next();
  } catch (error) {
    return res.status(401).json({ error: 'UNAUTHORIZED: Invalid token' });
  }
}

From Session (Web Requests)

// middleware/session.js
function requireSession(req, res, next) {
  if (!req.session?.user) {
    return res.status(401).json({ error: 'UNAUTHORIZED: Session required' });
  }

  // Extract organization_id from session
  req.user = {
    id: req.session.user.id,
    organizationId: req.session.user.organizationId
  };

  // Validate organization_id present
  if (!req.user.organizationId) {
    logger.error('Session missing organization_id', { userId: req.user.id });
    return res.status(500).json({ error: 'INTERNAL_ERROR: Invalid session' });
  }

  next();
}

From Worker Queue Payload

// workers/AnalyzeToxicityWorker.js
class AnalyzeToxicityWorker extends BaseWorker {
  async process(job) {
    const { commentId, userId, organizationId } = job.data;

    // CRITICAL: Validate organizationId in payload
    if (!organizationId) {
      throw new Error('INVALID_JOB: Missing organizationId in payload');
    }

    // Pass organizationId to all service calls
    const comment = await this.fetchComment(commentId, organizationId);
    const toxicity = await this.analyzeToxicity(comment.text, organizationId);
    await this.saveResult(commentId, toxicity, organizationId);
  }

  async fetchComment(commentId, organizationId) {
    // CORRECT: Filters by organizationId
    const result = await db.query(
      'SELECT * FROM comments WHERE id = $1 AND organization_id = $2',
      [commentId, organizationId]
    );

    if (result.rows.length === 0) {
      throw new Error('COMMENT_NOT_FOUND: Comment does not exist or belongs to different tenant');
    }

    return result.rows[0];
  }
}

RLS Policy Alignment

Check RLS Policies in database/schema.sql

Example RLS policy:

-- database/schema.sql
CREATE POLICY roasts_tenant_isolation ON roasts
  USING (organization_id = current_setting('app.current_organization_id')::INTEGER);

ALTER TABLE roasts ENABLE ROW LEVEL SECURITY;

Set organization_id context in transaction:

async function withOrganizationContext(organizationId, callback) {
  const client = await db.connect();

  try {
    await client.query('BEGIN');

    // Set organization_id for RLS policy
    await client.query(
      'SELECT set_config($1, $2, true)',
      ['app.current_organization_id', organizationId.toString()]
    );

    const result = await callback(client);

    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

// Usage
const roasts = await withOrganizationContext(organizationId, async (client) => {
  const result = await client.query('SELECT * FROM roasts WHERE user_id = $1', [userId]);
  return result.rows;
});

Verify RLS Policy Enforces Isolation

Test RLS in psql:

-- Test as org1
SET app.current_organization_id = 1;
SELECT * FROM roasts;  -- Should only see org1's roasts

-- Test as org2
SET app.current_organization_id = 2;
SELECT * FROM roasts;  -- Should only see org2's roasts

-- Test without context (should fail or return empty)
RESET app.current_organization_id;
SELECT * FROM roasts;  -- Should be blocked by RLS

Multi-Tenant Isolation Tests

Test Template 1: Basic Isolation

// tests/integration/multi-tenant-isolation.test.js
describe('Multi-tenant isolation', () => {
  let org1, org2, user1, user2;

  beforeEach(async () => {
    // Create two separate organizations
    org1 = await createOrganization({ name: 'Org 1' });
    org2 = await createOrganization({ name: 'Org 2' });

    // Create users in each org
    user1 = await createUser({ email: '[email protected]', organizationId: org1.id });
    user2 = await createUser({ email: '[email protected]', organizationId: org2.id });
  });

  it('should not leak roasts between organizations', async () => {
    // Create roast for org1
    const roast1 = await createRoast({
      userId: user1.id,
      organizationId: org1.id,
      text: 'Org1 roast'
    });

    // Create roast for org2
    const roast2 = await createRoast({
      userId: user2.id,
      organizationId: org2.id,
      text: 'Org2 roast'
    });

    // Fetch roasts for org1 - should only see org1's roast
    const org1Roasts = await getRoasts(user1.id, org1.id);
    expect(org1Roasts).toHaveLength(1);
    expect(org1Roasts[0].text).toBe('Org1 roast');
    expect(org1Roasts[0].id).toBe(roast1.id);

    // Fetch roasts for org2 - should only see org2's roast
    const org2Roasts = await getRoasts(user2.id, org2.id);
    expect(org2Roasts).toHaveLength(1);
    expect(org2Roasts[0].text).toBe('Org2 roast');
    expect(org2Roasts[0].id).toBe(roast2.id);
  });

  it('should prevent cross-tenant updates', async () => {
    // Create roast for org1
    const roast1 = await createRoast({
      userId: user1.id,
      organizationId: org1.id,
      text: 'Original text'
    });

    // Attempt to update org1's roast using org2's context
    await expect(
      updateRoast(roast1.id, org2.id, { text: 'Hacked text' })
    ).rejects.toThrow('ROAST_NOT_FOUND');

    // Verify roast unchanged
    const roast = await getRoast(roast1.id, org1.id);
    expect(roast.text).toBe('Original text');
  });

  it('should prevent cross-tenant deletes', async () => {
    // Create roast for org1
    const roast1 = await createRoast({
      userId: user1.id,
      organizationId: org1.id,
      text: 'Important data'
    });

    // Attempt to delete org1's roast using org2's context
    await expect(
      deleteRoast(roast1.id, org2.id)
    ).rejects.toThrow('ROAST_NOT_FOUND');

    // Verify roast still exists
    const roast = await getRoast(roast1.id, org1.id);
    expect(roast).toBeDefined();
  });
});

Test Template 2: Worker Context Isolation

describe('Worker multi-tenant isolation', () => {
  it('should pass organizationId through worker queue', async () => {
    const org1 = await createOrganization();
    const comment = await createComment({ organizationId: org1.id, text: 'Test comment' });

    // Queue job
    const job = await queueService.add('analyze-toxicity', {
      commentId: comment.id,
      userId: comment.userId,
      organizationId: org1.id  // CRITICAL: Must include
    });

    // Process job
    await worker.process(job);

    // Verify result stored with correct organization_id
    const result = await getToxicityResult(comment.id, org1.id);
    expect(result.organizationId).toBe(org1.id);
  });

  it('should fail if worker job missing organizationId', async () => {
    const job = {
      data: {
        commentId: 123,
        userId: 456
        // Missing organizationId
      }
    };

    await expect(worker.process(job))
      .rejects.toThrow('INVALID_JOB: Missing organizationId');
  });
});

Test Template 3: API Endpoint Isolation

describe('API endpoint multi-tenant isolation', () => {
  it('should only return current tenant data in /api/roasts', async () => {
    const org1 = await createOrganization();
    const org2 = await createOrganization();

    const user1 = await createUser({ organizationId: org1.id });
    const user2 = await createUser({ organizationId: org2.id });

    // Create roasts for both orgs
    await createRoast({ userId: user1.id, organizationId: org1.id, text: 'Org1' });
    await createRoast({ userId: user2.id, organizationId: org2.id, text: 'Org2' });

    // Request as org1
    const token1 = generateJWT({ userId: user1.id, organizationId: org1.id });
    const response1 = await request(app)
      .get('/api/roasts')
      .set('Authorization', `Bearer ${token1}`);

    expect(response1.status).toBe(200);
    expect(response1.body.roasts).toHaveLength(1);
    expect(response1.body.roasts[0].text).toBe('Org1');

    // Request as org2
    const token2 = generateJWT({ userId: user2.id, organizationId: org2.id });
    const response2 = await request(app)
      .get('/api/roasts')
      .set('Authorization', `Bearer ${token2}`);

    expect(response2.status).toBe(200);
    expect(response2.body.roasts).toHaveLength(1);
    expect(response2.body.roasts[0].text).toBe('Org2');
  });
});

Error Message Leaks

❌ WRONG: Error Reveals Cross-Tenant Data

try {
  const roast = await getRoast(roastId, organizationId);
} catch (error) {
  // BAD: Reveals that roast exists in different org
  throw new Error(`Roast ${roastId} not found in organization ${organizationId}, found in org ${roast.actual_org_id}`);
}

✅ CORRECT: Generic Error Message

try {
  const roast = await getRoast(roastId, organizationId);

  if (!roast) {
    // GOOD: Generic message, no leak
    throw new Error('ROAST_NOT_FOUND: Roast does not exist');
  }
} catch (error) {
  // Log details internally (NOT returned to client)
  logger.warn('Roast access attempt', {
    roastId,
    requestedOrgId: organizationId,
    actualOrgId: roast?.organizationId,
    userId: req.user.id
  });

  // Return generic error to client
  throw new Error('ROAST_NOT_FOUND');
}

Pre-Commit Validation

Add Git Hook to Detect Missing organization_id

# .git/hooks/pre-commit
#!/bin/bash

# Check for SQL queries without organization_id filter
echo "🔍 Checking for queries missing organization_id filter..."

# Patterns to detect
PATTERNS=(
  "SELECT.*FROM.*WHERE.*user_id.*[^organization_id]"
  "UPDATE.*SET.*WHERE.*[^organization_id]"
  "DELETE.*FROM.*WHERE.*[^organization_id]"
)

VIOLATIONS=0

for pattern in "${PATTERNS[@]}"; do
  if git diff --cached --name-only | xargs grep -E "$pattern" 2>/dev/null; then
    echo "❌ Found query without organization_id filter!"
    VIOLATIONS=$((VIOLATIONS + 1))
  fi
done

if [ $VIOLATIONS -gt 0 ]; then
  echo ""
  echo "⚠️  BLOCKED: Queries must filter by organization_id in multi-tenant system"
  echo "See: .claude/skills/multi-tenant-context-skill.md"
  exit 1
fi

echo "✅ Multi-tenant context validation passed"
exit 0

Success Criteria

✅ Request has organization_id extracted from JWT/session/payload ✅ ALL database queries filter by organization_id ✅ INSERT includes organization_id ✅ UPDATE/DELETE filter by organization_id ✅ Workers pass organization_id through queue payload ✅ RLS policies aligned with application code ✅ Multi-tenant isolation tests pass ✅ Error messages don't leak cross-tenant data ✅ Pre-commit hook validates queries

References

• Database schema: database/schema.sql - RLS policies
• Architecture: docs/nodes/database-layer.md - Multi-tenant design
• Auth middleware: src/middleware/auth.js
• Test examples: tests/integration/multi-tenant-isolation.test.js

Related Skills

• security-audit-skill - Comprehensive security audit
• systematic-debugging-skill - Debug tenant isolation issues
• test-generation-skill - Generate isolation tests

Reglas de Oro

❌ NEVER

1. Write query without organization_id filter (except for org management tables)
2. Trust client to provide organization_id (extract from JWT)
3. Skip multi-tenant isolation tests
4. Return cross-tenant data in error messages
5. Log sensitive data that could leak between tenants

✅ ALWAYS

1. Extract organization_id from JWT/session (never from client params)
2. Filter ALL queries by organization_id
3. Include organization_id in INSERT statements
4. Pass organization_id through worker queue payloads
5. Verify RLS policies match application logic
6. Test with multiple orgs to prove isolation
7. Use generic error messages (prevent leaks)
8. Run pre-commit validation hook