Seed Strategy

Guide to safely seeding data in a multi-tenant environment.

Overview

Seeding is the process of populating the database with initial or test data. In a multi-tenant system, seeding must respect tenant boundaries and use service role for operations that bypass RLS.

Seeding Principles

1. Tenant Isolation: Always specify tenant_id when seeding
2. Service Role: Use service role to bypass RLS
3. Idempotent: Scripts should be safe to run multiple times
4. Cleanup: Provide cleanup scripts for test data
5. Validation: Verify data belongs to correct tenant

Basic Seeding Pattern

1. Create Admin Client

// scripts/seedHelpers.ts
import { createClient } from '@supabase/supabase-js';

export function createAdminClient() {
  const supabaseUrl = process.env.SUPABASE_URL;
  const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;

  if (!supabaseUrl || !supabaseServiceKey) {
    throw new Error('Missing environment variables');
  }

  return createClient(supabaseUrl, supabaseServiceKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}

2. Seed Tenant Data

// scripts/seed.ts
import { createAdminClient } from './seedHelpers';

const adminClient = createAdminClient();

async function seedTenant(tenantId: string) {
  // Seed zones
  const zones = await seedZones(tenantId);
  
  // Seed inventory
  await seedInventory(tenantId, zones);
  
  // Seed shipments
  await seedShipments(tenantId, zones);
  
  // Seed users
  await seedUsers(tenantId);
}

async function seedZones(tenantId: string) {
  const zones = [
    { code: 'A1', name: 'Zone A1', tenant_id: tenantId },
    { code: 'B2', name: 'Zone B2', tenant_id: tenantId },
  ];
  
  const { data, error } = await adminClient
    .from('zones')
    .insert(zones)
    .select();
  
  if (error) throw error;
  return data;
}

Safe Environment Check

Always verify you're in a safe environment before seeding:

export function isSafeEnvironment(): boolean {
  const nodeEnv = process.env.NODE_ENV || 'development';
  const isDevelopment = nodeEnv === 'development';
  const isTest = nodeEnv === 'test';
  
  // Only allow seeding in development or test
  return isDevelopment || isTest;
}

// Usage
if (!isSafeEnvironment()) {
  console.error('Seeding is only allowed in development/test environments');
  process.exit(1);
}

Mock Data Tagging

Tag seeded data for easy cleanup:

interface MockDataOptions {
  includeCreatedBy?: boolean;
  includeMock?: boolean;
}

function tagAsMock(
  data: Record<string, any>,
  options: MockDataOptions = {}
): Record<string, any> {
  const { includeCreatedBy = true, includeMock = true } = options;
  
  return {
    ...data,
    ...(includeCreatedBy && { created_by: 'seed-script' }),
    ...(includeMock && { mock: true }),
  };
}

// Usage
const item = tagAsMock({
  tenant_id: tenantId,
  sku: 'MOCK-001',
  name: 'Mock Item',
});

Complete Seeding Example

// scripts/seed.ts
import { createAdminClient, isSafeEnvironment, tagAsMock } from './seedHelpers';

async function main() {
  if (!isSafeEnvironment()) {
    throw new Error('Seeding only allowed in development/test');
  }

  const adminClient = createAdminClient();
  
  // Get or create test tenant
  let { data: tenant } = await adminClient
    .from('tenants')
    .select('id')
    .eq('name', 'Test Tenant')
    .maybeSingle();
  
  if (!tenant) {
    const { data: newTenant } = await adminClient
      .from('tenants')
      .insert({ name: 'Test Tenant', active: true })
      .select()
      .single();
    tenant = newTenant;
  }
  
  const tenantId = tenant.id;
  
  // Seed data
  await seedZones(adminClient, tenantId);
  await seedInventory(adminClient, tenantId);
  await seedShipments(adminClient, tenantId);
  await seedUsers(adminClient, tenantId);
  
  console.log('✅ Seeding completed');
}

async function seedZones(client: SupabaseClient, tenantId: string) {
  const zones = [
    tagAsMock({ code: 'A1', name: 'Zone A1', tenant_id: tenantId }, { includeCreatedBy: false, includeMock: false }),
    tagAsMock({ code: 'B2', name: 'Zone B2', tenant_id: tenantId }, { includeCreatedBy: false, includeMock: false }),
  ];
  
  await client.from('zones').insert(zones);
}

async function seedInventory(client: SupabaseClient, tenantId: string) {
  // Get zones first
  const { data: zones } = await client
    .from('zones')
    .select('id')
    .eq('tenant_id', tenantId)
    .limit(5);
  
  if (!zones || zones.length === 0) {
    console.warn('No zones found, skipping inventory');
    return;
  }
  
  const items = Array.from({ length: 10 }, (_, i) =>
    tagAsMock({
      tenant_id: tenantId,
      sku: `ITEM-${String(i + 1).padStart(3, '0')}`,
      name: `Test Item ${i + 1}`,
      zone_id: zones[i % zones.length].id,
      quantity: Math.floor(Math.random() * 100),
    }, { includeCreatedBy: false, includeMock: false })
  );
  
  await client.from('inventory_items').insert(items);
}

main().catch(console.error);

Cleanup Strategy

Mark Mock Data

// Add mock flag to all seeded data
const mockData = {
  mock: true,
  seed_timestamp: new Date().toISOString(),
};

Cleanup Script

// scripts/clearMock.ts
async function clearMockData() {
  if (!isSafeEnvironment()) {
    throw new Error('Cleanup only allowed in development/test');
  }

  const adminClient = createAdminClient();
  
  // Delete in correct order (respect foreign keys)
  await adminClient.from('stock_movements').delete().eq('mock', true);
  await adminClient.from('pick_list_items').delete().eq('mock', true);
  await adminClient.from('pick_lists').delete().eq('mock', true);
  await adminClient.from('inventory_items').delete().eq('mock', true);
  await adminClient.from('shipments').delete().eq('mock', true);
  
  // Zones might be shared, so be careful
  // await adminClient.from('zones').delete().eq('mock', true);
  
  console.log('✅ Mock data cleared');
}

Tenant-Specific Seeding

Seed data for a specific tenant:

async function seedTenantData(tenantId: string) {
  const adminClient = createAdminClient();
  
  // Verify tenant exists
  const { data: tenant } = await adminClient
    .from('tenants')
    .select('id, name')
    .eq('id', tenantId)
    .single();
  
  if (!tenant) {
    throw new Error(`Tenant ${tenantId} not found`);
  }
  
  console.log(`Seeding data for tenant: ${tenant.name}`);
  
  // Seed tenant-specific data
  await seedZones(adminClient, tenantId);
  await seedInventory(adminClient, tenantId);
  // ...
}

Bulk Seeding

For large datasets, use batch inserts:

async function seedBulkData(tenantId: string, count: number) {
  const adminClient = createAdminClient();
  const batchSize = 1000;
  
  for (let i = 0; i < count; i += batchSize) {
    const batch = Array.from({ length: Math.min(batchSize, count - i) }, (_, j) =>
      createMockItem(tenantId, i + j)
    );
    
    await adminClient.from('inventory_items').insert(batch);
    console.log(`Inserted batch ${Math.floor(i / batchSize) + 1}`);
  }
}

Seeding Users

Create Auth Users

async function seedUsers(tenantId: string) {
  const adminClient = createAdminClient();
  
  const users = [
    { email: 'admin@tenant.com', password: 'secure123', role: 'tenant_admin' },
    { email: 'user@tenant.com', password: 'secure123', role: 'tenant_user' },
  ];
  
  for (const userData of users) {
    // Create auth user
    const { data: authUser, error: authError } = await adminClient.auth.admin.createUser({
      email: userData.email,
      password: userData.password,
      email_confirm: true,
    });
    
    if (authError) {
      console.error(`Failed to create user ${userData.email}:`, authError);
      continue;
    }
    
    // Assign to tenant
    await adminClient.from('tenant_users').insert({
      tenant_id: tenantId,
      user_id: authUser.user.id,
      role: userData.role,
    });
  }
}

Validation

Verify seeded data:

async function validateSeeding(tenantId: string) {
  const adminClient = createAdminClient();
  
  // Check zones
  const { count: zoneCount } = await adminClient
    .from('zones')
    .select('*', { count: 'exact', head: true })
    .eq('tenant_id', tenantId);
  
  // Check inventory
  const { count: itemCount } = await adminClient
    .from('inventory_items')
    .select('*', { count: 'exact', head: true })
    .eq('tenant_id', tenantId);
  
  console.log(`Tenant ${tenantId}:`);
  console.log(`  Zones: ${zoneCount}`);
  console.log(`  Inventory Items: ${itemCount}`);
  
  // Verify no cross-tenant leakage
  const { data: crossTenant } = await adminClient
    .from('inventory_items')
    .select('id')
    .eq('tenant_id', tenantId)
    .is('tenant_id', null)
    .limit(1);
  
  if (crossTenant && crossTenant.length > 0) {
    throw new Error('Data leakage detected: items without tenant_id');
  }
}

NPM Scripts

Add to package.json:

{
  "scripts": {
    "seed": "tsx scripts/seed.ts",
    "seed:tenant": "tsx scripts/seedTenant.ts",
    "clear:mock": "tsx scripts/clearMock.ts"
  }
}

Best Practices

1. Always Use Service Role: Seeding requires bypassing RLS
2. Tag Mock Data: Mark seeded data for easy cleanup
3. Respect Foreign Keys: Insert in correct order
4. Validate Results: Verify data after seeding
5. Environment Checks: Only seed in safe environments
6. Idempotent Scripts: Safe to run multiple times
7. Error Handling: Handle errors gracefully
8. Logging: Log progress and results

Related Documentation

• Service Role - Using service role for seeding
• Tenant Bootstrap - Creating new tenants
• Database Structure - Table schemas