Feature Request: Add per-request email support to DynamicTable API

[wagnert]

Problem Statement

The current DynamicTable API lacks support for per-request RunAsUserEmail configuration, which is essential for multi-user applications where different requests need to execute with different user contexts.

Current Limitation

const table = schemaManager.table<ServicePortfolio>('default', 'service_portfolio');

// ❌ No way to specify which user this request should run as
const services = await table.findAll();

The underlying AppSheetClient.find() method DOES support RunAsUserEmail via the properties parameter:

const result = await client.find({
  tableName: 'service_portfolio',
  properties: { RunAsUserEmail: 'user@example.com' }
});

But DynamicTable doesn't expose this capability, forcing applications to either:

1. Use the low-level AppSheetClient API directly (losing type safety and convenience)
2. Create separate SchemaManager instances per user (inefficient)
3. Use a default user for all operations (incorrect for multi-user scenarios)

Use Case

Multi-User MCP Server Scenario:

// Different users making requests to the same MCP server
async function handleListServicesRequest(userEmail: string) {
  // Need to execute this query AS the requesting user
  const services = await table.findAll(/* how to pass userEmail? */);
  return services;
}

Current Workaround:

// Have to bypass DynamicTable entirely
function getClientForUser(email: string): AppSheetClientInterface {
  return new AppSheetClient({
    appId: config.appId,
    accessKey: config.accessKey,
    runAsUserEmail: email  // Create new client per user
  });
}

// Loses all benefits of SchemaManager and DynamicTable
const client = getClientForUser('user@example.com');
const result = await client.find({ tableName: 'service_portfolio' });

Proposed Solution

Add optional runAsUserEmail parameter to all DynamicTable methods:

export interface DynamicTableOptions {
  runAsUserEmail?: string;
}

export class DynamicTable<T = Record<string, any>> {
  
  async findAll(options?: DynamicTableOptions): Promise<T[]> {
    const properties = options?.runAsUserEmail 
      ? { RunAsUserEmail: options.runAsUserEmail }
      : undefined;
      
    const response = await this.client.find({
      tableName: this.definition.tableName,
      properties
    });
    
    return response.Rows as T[];
  }
  
  async findOne(
    selector: string, 
    options?: DynamicTableOptions
  ): Promise<T | null> {
    const properties = options?.runAsUserEmail 
      ? { RunAsUserEmail: options.runAsUserEmail }
      : undefined;
      
    const response = await this.client.find({
      tableName: this.definition.tableName,
      selector,
      properties
    });
    
    return response.Rows[0] as T || null;
  }
  
  async add(
    rows: Partial<T>[], 
    options?: DynamicTableOptions
  ): Promise<T[]> {
    const properties = options?.runAsUserEmail 
      ? { RunAsUserEmail: options.runAsUserEmail }
      : undefined;
      
    const response = await this.client.add({
      tableName: this.definition.tableName,
      rows,
      properties
    });
    
    return response.Rows as T[];
  }
  
  async update(
    rows: Partial<T>[], 
    options?: DynamicTableOptions
  ): Promise<T[]> {
    const properties = options?.runAsUserEmail 
      ? { RunAsUserEmail: options.runAsUserEmail }
      : undefined;
      
    const response = await this.client.update({
      tableName: this.definition.tableName,
      rows,
      properties
    });
    
    return response.Rows as T[];
  }
  
  async delete(
    rows: Partial<T>[], 
    options?: DynamicTableOptions
  ): Promise<void> {
    const properties = options?.runAsUserEmail 
      ? { RunAsUserEmail: options.runAsUserEmail }
      : undefined;
      
    await this.client.delete({
      tableName: this.definition.tableName,
      rows,
      properties
    });
  }
}

Usage Example

const schemaManager = new SchemaManager(schema);
const table = schemaManager.table<ServicePortfolio>('default', 'service_portfolio');

// Execute query as specific user
const services = await table.findAll({ 
  runAsUserEmail: 'user@example.com' 
});

// Add row as specific user
await table.add([newService], { 
  runAsUserEmail: 'admin@example.com' 
});

// Default behavior (no email) remains unchanged
const allServices = await table.findAll();

Benefits

✅ Backward Compatible: Optional parameter, existing code continues to work
✅ Type-Safe: Full TypeScript support with generics
✅ Consistent API: Follows existing AppSheetClient patterns
✅ Multi-User Ready: Essential for server applications
✅ Clean Architecture: No need to create clients per-request

Alternative Considered

Global email in SchemaManager constructor:

const schemaManager = new SchemaManager(schema, { 
  runAsUserEmail: 'user@example.com' 
});

❌ Rejected: Doesn't solve per-request requirement, forces separate SchemaManager instances per user

Impact

This feature would enable proper multi-user support in applications using the schema-based API, making DynamicTable a viable option for production MCP servers and multi-tenant applications.

Context

• Related to: techdivision/service-portfolio-mcp Story 5.26 (AppSheet v2.0.0 migration)
• Blocker for: Story 5.27 (Schema Manager adoption in production)
• Current status: Using workaround with per-user AppSheetClient instances

---

Would you accept a Pull Request implementing this feature?