sbroenne__mcp-server-excel/specs/SESSION-API-REDESIGN-SPEC.md

Session API Redesign Specification

Version: 1.0 Status: Draft Date: 2025-01-13 Author: Development Team

Executive Summary

This specification proposes a breaking redesign of ExcelMcp's session API to use intuitive Open/Save/Close semantics exclusively. The goal is to eliminate the "batch" concept entirely and remove all cognitive load from LLMs - every operation works through sessions, always. No backwards compatibility, no dual patterns, no decisions about when to batch.

⚠️ CRITICAL: Excel COM Threading & Concurrency Limitations

Excel COM API is fundamentally single-threaded and does NOT support parallel operations.

Operations Within a Session are ALWAYS SERIAL

• Each session (IExcelBatch/IExcelSession) runs on ONE dedicated STA thread with ONE Excel instance
• Operations are queued and executed sequentially via Channel<Func<Task>>
• Multiple session.Execute() calls are processed one at a time (never in parallel)
• This is a COM interop requirement, not an implementation choice

Example - Operations are SERIAL, not parallel:

// ❌ These do NOT run in parallel - they are queued serially!
var task1 = session.Execute(ctx => ctx.Book.Worksheets.Add("Sheet1"));  // Queued
var task2 = session.Execute(ctx => ctx.Book.Worksheets.Add("Sheet2"));  // Queued AFTER task1
await Task.WhenAll(task1, task2);  // Still serial execution on STA thread!

Why: Excel COM requires Single-Threaded Apartment (STA) model. No concurrent access to Excel objects is possible.

Multiple Sessions = Multiple Excel Processes (Resource Heavy)

You CAN create multiple sessions for DIFFERENT files:

• Each session = one Excel.Application process (~50-100MB+ memory each)
• Sessions run in separate processes (true parallelism between files)
• But operations within each session remain serial

Example - Multiple files (parallel processes, serial operations per file):

// ✅ CORRECT: Multiple files = multiple Excel processes (true parallelism)
var session1 = await manager.CreateSessionAsync("fileA.xlsx");  // Excel process 1
var session2 = await manager.CreateSessionAsync("fileB.xlsx");  // Excel process 2

// These run in parallel (different Excel processes)
var task1 = GetSession(session1).Execute(...);  // Runs in Excel process 1
var task2 = GetSession(session2).Execute(...);  // Runs in Excel process 2
await Task.WhenAll(task1, task2);  // ✅ True parallelism (different processes)

// But within each session, operations are still serial
var task3 = GetSession(session1).Execute(...);  // Queued after task1
var task4 = GetSession(session1).Execute(...);  // Queued after task3

Resource Limits:

• Each Excel process consumes ~50-100MB+ memory
• Windows desktop machines have finite resources
• Recommendation: Limit to 3-5 concurrent sessions for typical desktops

File Creation Must Be Sequential

File creation is automatically serialized by the implementation:

// ✅ This is now SAFE - internal lock serializes calls automatically
var tasks = Enumerable.Range(1, 10).Select(i =>
    ExcelSession.CreateNew($"file{i}.xlsx", false, ...));
await Task.WhenAll(tasks);  // Executes sequentially despite Task.WhenAll!

// ✅ This is also safe and more explicit
for (int i = 1; i <= 10; i++)
{
    await ExcelSession.CreateNew($"file{i}.xlsx", false, ...);
}

How it works: ExcelSession uses a static SemaphoreSlim(1, 1) to serialize all CreateNew() and CreateNewAsync() calls. Even if called in parallel, they queue and execute one at a time.

Why enforced: Each CreateNew() temporarily creates an Excel instance, saves the file, then closes it. Without serialization, parallel creation would spawn many Excel processes simultaneously, causing memory exhaustion.

SessionManager Prevents Same-File Conflicts

// SessionManager enforces one session per file
await manager.CreateSessionAsync("sales.xlsx");  // ✅ OK
await manager.CreateSessionAsync("sales.xlsx");  // ❌ Throws: "File already open in another session"

This matches Excel UI behavior (cannot open same file twice).

Key Takeaways for Implementation

1. Within-session parallelism is IMPOSSIBLE - all operations are queued serially on STA thread
2. Between-sessions parallelism is POSSIBLE - different files = different processes
3. File creation is AUTOMATICALLY SERIALIZED - enforced by SemaphoreSlim lock (prevents resource exhaustion)
4. Resource limits matter - each session = one Excel process (~50-100MB+)
5. LLMs must manage session lifecycle carefully - close sessions promptly to free resources

Problem Statement

Current Pain Points

1. Unintuitive Terminology: "Begin batch" and "Commit batch" are technical terms that require explanation
2. Cognitive Load: LLMs must decide when to use batch mode vs. single operations
3. Resource Leak Risk: Forgotten commits leave Excel instances running
4. Dual Patterns: Tools support both batchId parameter and standalone operation modes (complexity!)
5. Decision Fatigue: LLMs waste tokens deciding "Should I use batch mode for this?"
6. Performance Inconsistency: Single operations are slow, batch is fast - LLM must choose correctly
7. File Lock Race Condition (Issue #173): Rapid sequential non-batch calls fail because Excel disposal (2-17s) from first call hasn't completed when second call tries to open the file

What Users Actually Think

Users and LLMs naturally think in terms of:

• Open a file → work with it → Save changes → Close the file
• NOT: Begin session → track GUID → commit session

This is the universal pattern for file operations across all systems.

Proposed Solution

High-Level Design

Sessions are the ONLY way to work with Excel files. No exceptions.

1. excel_file(action: 'open') - Opens a workbook, returns a sessionId
2. excel_file(action: 'save') - Saves changes to an open workbook
3. excel_file(action: 'close') - Closes workbook and session
4. ALL other tools REQUIRE sessionId - No standalone operation mode

Revolutionary Change: Remove the batchId optional parameter pattern entirely. Sessions are mandatory, not optional.

Why This Works Better

Old Pattern	New Pattern	Benefit
excel_batch(action: 'begin')	excel_file(action: 'open')	Matches universal file paradigm
Track batchId GUID	Track sessionId (still a GUID)	More intuitive name
excel_batch(action: 'commit', save: true)	excel_file(action: 'close')	Natural action name
Optional batchId parameter	REQUIRED sessionId parameter	No decision fatigue
"Should I use batch mode?"	Sessions always used	Zero cognitive load
Dual code paths (batch vs. single)	Single code path only	Simpler implementation
Rapid sequential calls fail (#173)	Single Excel instance reused	Eliminates file lock race

Terminology Changes

Current Term → New Term → Rationale
────────────────────────────────────────────────────────
batchId      → sessionId  → "Session" is more intuitive than "batch"
begin        → open       → Universal file operation
commit       → close      → Universal file operation (does NOT save)
batch-of-one → REMOVED    → No standalone operations anymore
Optional     → REQUIRED   → sessionId is mandatory for all operations
save param   → REMOVED    → close never saves, use explicit save action
excelPath    → REMOVED    → Session knows the file (except open/create)

BREAKING CHANGE: The optional batchId parameter is completely removed. Every operation on a workbook requires an active session.

Detailed API Design

1. excel_file Tool - Updated Actions

Current Actions:

• create-empty - Create new workbook
• close-workbook - Emergency close (rarely used)
• test - Connection test

New Actions (added):

• open - Opens workbook, returns sessionId (replaces batch begin)
• save - Saves changes to open session
• close - Closes session WITHOUT saving (use explicit save action)

Removed Actions:

• None (keep create-empty, test, close-workbook for backwards compat)

2. API Signatures

Open Workbook

[McpServerTool(Name = "excel_file")]
[Description(@"Manage Excel file lifecycle. All Excel operations require an active session.

REQUIRED WORKFLOW:
1. open - Opens workbook, returns sessionId (ALWAYS FIRST)
2. Use sessionId for ALL operations (worksheets, queries, ranges, etc.)
3. save - Saves changes (EXPLICIT action, call anytime during session)
4. close - Closes workbook and session (NEVER saves - use explicit save action)

Sessions are mandatory - there are no standalone operations.")]
public static async Task<string> ExcelFile(
    [Required]
    [Description("Action to perform")]
    FileAction action,

    [Description("Full path to Excel file - required for 'open' and 'create-empty'")]
    string? filePath = null,

    [Description("Session ID from 'open' action - required for 'save' and 'close'")]
    string? sessionId = null)
{
    return action switch
    {
        FileAction.Open => await OpenWorkbookAsync(filePath!),
        FileAction.Save => await SaveWorkbookAsync(sessionId!),
        FileAction.Close => await CloseWorkbookAsync(sessionId!),  // No save parameter - close NEVER saves
        FileAction.CreateEmpty => await CreateEmptyAsync(filePath!),
        FileAction.Test => TestConnection(),
        _ => throw new McpException($"Unknown action: {action}")
    };
}

Example Response - Open

{
  "success": true,
  "sessionId": "abc-123-def-456",
  "filePath": "C:\\data\\sales.xlsx",
  "message": "Workbook opened successfully",
  "suggestedNextActions": [
    "Use sessionId='abc-123-def-456' for all operations",
    "Call excel_file(action: 'save', sessionId='...') to save changes (explicit only)",
    "Call excel_file(action: 'close', sessionId='...') when done (does NOT save)"
  ],
  "workflowHint": "Session active. Remember: close does NOT save - use explicit save action."
}

3. Other Tools - Breaking Changes

All other tools now REQUIRE sessionId parameter and REMOVE excelPath parameter:

// PowerQuery example - sessionId is now REQUIRED, excelPath REMOVED
public static async Task<string> ExcelPowerQuery(
    [Required] PowerQueryAction action,
    [Required] string sessionId,  // ✅ REQUIRED (was optional batchId)
    // ... other params (excelPath REMOVED - session already knows the file)
)

BREAKING CHANGE: No more optional batchId. Every tool method signature changes to require sessionId.

BREAKING CHANGE: excelPath parameter REMOVED from all tools except excel_file open/create actions. The session already knows which file is open, so passing excelPath is redundant and creates potential for mismatches (sessionId points to fileA.xlsx, but excelPath says fileB.xlsx).

Implementation simplification:

• Remove WithBatchAsync() dual-path logic entirely
• Remove "batch-of-one" pattern
• Every tool method becomes simpler: just lookup session and use it
• No more "if sessionId provided, else create temporary batch" logic

Implementation Strategy

Single-Phase Breaking Refactor

No backwards compatibility. Clean slate redesign.

Step 1: Remove Old Infrastructure (1-2 days)

1. Delete excel_batch tool entirely

   • Remove src/ExcelMcp.McpServer/Tools/BatchSessionTool.cs
   • Remove src/ExcelMcp.CLI/Commands/BatchCommands.cs
   • Remove src/ExcelMcp.McpServer/Prompts/Content/excel_batch.md

2. Remove dual-path logic in ExcelToolsBase

   • Delete WithBatchAsync() method entirely
   • Remove "batch-of-one" pattern
   • Remove all if (sessionId != null) { ... } else { ... } conditionals

3. Rename internal classes

   • _activeBatches → _activeSessions
   • IExcelBatch → IExcelSession (interface rename)
   • ExcelBatch → ExcelSession (implementation rename)
   • BeginBatchAsync → OpenSessionAsync

Step 2: Add Session Lifecycle to excel_file (2-3 days)

// Add new actions to existing excel_file tool
public enum FileAction
{
    CreateEmpty,
    Open,        // NEW - replaces batch begin
    Save,        // NEW - explicit save
    Close,       // NEW - replaces batch commit
    Test
}

Implementation:

• OpenWorkbookAsync() - Creates session, returns sessionId
• SaveWorkbookAsync(sessionId) - Saves changes
• CloseWorkbookAsync(sessionId, save) - Closes and disposes

Step 3: Update ALL Tools to Require sessionId (3-5 days)

Before (12 tools with optional batchId):

string? batchId = null

After (12 tools with required sessionId):

[Required] string sessionId

Files to modify:

• ExcelConnectionTool.cs
• ExcelDataModelTool.cs
• ExcelFileTool.cs (add open/save/close actions)
• ExcelNamedRangeTool.cs
• ExcelPivotTableTool.cs
• ExcelPowerQueryTool.cs
• ExcelQueryTableTool.cs
• ExcelRangeTool.cs
• ExcelTableTool.cs
• ExcelVbaTool.cs
• ExcelWorksheetTool.cs

Step 4: Simplify Tool Implementation (2-3 days)

Remove complexity everywhere:

// OLD - Complex dual-path logic
var result = await ExcelToolsBase.WithBatchAsync(
    batchId, filePath, save: true,
    async (batch) => await commands.SomeAsync(batch, args));

// NEW - Simple direct session lookup
var session = SessionManager.GetSession(sessionId);
var result = await commands.SomeAsync(session, args);

Benefits:

• ~40% less code in each tool method
• No branching logic
• Easier to understand and maintain

Step 5: Update Documentation (1-2 days)

Delete:

• excel_batch.md prompt file
• All references to "batch mode" in docs
• Performance comparison sections (sessions are ALWAYS used)

Update:

• excel_file.md - Add session lifecycle patterns
• tool_selection_guide.md - Remove batch decision logic
• All tool descriptions - Change to "sessionId (required)"
• README - Update examples to show session workflow

Performance & Simplification

No More "Auto-Detection"

Current: LLM must decide when to use batch mode (decision fatigue) New: Sessions are mandatory - no decision needed

Key Insight: By making sessions mandatory, we:

1. Eliminate decision fatigue - LLM never thinks "Should I batch?"
2. Consistent performance - Every operation is optimized
3. Simpler code - Single code path through entire system
4. Better UX - Open/Close workflow is intuitive

Performance Best Practices

✅ DO: Batch Operations on Same File (Single Session)

// ✅ FAST: All operations in one session (single Excel instance reused)
var session = await CreateSessionAsync("sales.xlsx");
await GetSession(session).Execute(ctx => ctx.Book.Worksheets.Add("Q1"));  // Op 1
await GetSession(session).Execute(ctx => ctx.Book.Worksheets.Add("Q2"));  // Op 2
await GetSession(session).Execute(ctx => ctx.Book.Worksheets.Add("Q3"));  // Op 3
await SaveSessionAsync(session);
await CloseSessionAsync(session);

// Result: 1 Excel instance created/destroyed (fast)

❌ DON'T: Open/Close Repeatedly (Multiple Excel Instances)

// ❌ SLOW: Creates 3 separate Excel instances (5-10x overhead per open/close)
await CreateSessionAsync("sales.xlsx");
await GetSession(...).Execute(ctx => ctx.Book.Worksheets.Add("Q1"));
await CloseSessionAsync(...);

await CreateSessionAsync("sales.xlsx");  // New Excel instance!
await GetSession(...).Execute(ctx => ctx.Book.Worksheets.Add("Q2"));
await CloseSessionAsync(...);

await CreateSessionAsync("sales.xlsx");  // Yet another Excel instance!
await GetSession(...).Execute(ctx => ctx.Book.Worksheets.Add("Q3"));
await CloseSessionAsync(...);

// Result: 3 Excel instances created/destroyed (very slow)

Performance Impact: Opening Excel repeatedly is 5-10x slower than reusing a session. The session API is designed to keep Excel open for multiple operations.

✅ DO: Parallel Processing of DIFFERENT Files

// ✅ TRUE PARALLELISM: Different files = different processes
var files = new[] { "sales.xlsx", "inventory.xlsx", "customers.xlsx" };
var sessions = await Task.WhenAll(files.Select(f => CreateSessionAsync(f)));

// Process all files in parallel (3 Excel processes running simultaneously)
await Task.WhenAll(sessions.Select(async sessId => {
    var session = GetSession(sessId);
    await session.Execute(ctx => ProcessWorkbook(ctx));
    await SaveSessionAsync(sessId);
    await CloseSessionAsync(sessId);
}));

// Result: True parallelism - operations on different files don't block each other

❌ DON'T: Try to Parallelize Operations on SAME File

// ❌ NO BENEFIT: Operations are queued serially anyway (Excel COM limitation)
var sess = await CreateSessionAsync("data.xlsx");
await Task.WhenAll(
    GetSession(sess).Execute(ctx => ctx.Book.Worksheets.Add("Sheet1")),  // Queued
    GetSession(sess).Execute(ctx => ctx.Book.Worksheets.Add("Sheet2")),  // Queued (waits)
    GetSession(sess).Execute(ctx => ctx.Book.Worksheets.Add("Sheet3"))   // Queued (waits)
);

// Result: Operations still run serially (no speedup) - just write them sequentially for clarity

Why No Benefit: Each session has one STA thread processing operations one at a time. Task.WhenAll doesn't change this - they still execute serially on the Excel COM thread.

File Creation (Automatically Serialized)

// ✅ This pattern works correctly - internal lock serializes calls
var tasks = Enumerable.Range(1, 10).Select(i =>
    ExcelSession.CreateNew($"report{i}.xlsx", false,
        (ctx, ct) => {
            ctx.Book.Worksheets[1].Name = $"Report {i}";
            return 0;
        }));
await Task.WhenAll(tasks);  // Executes sequentially despite Task.WhenAll!

// ✅ This explicit sequential pattern also works
for (int i = 1; i <= 10; i++)
{
    await ExcelSession.CreateNew($"report{i}.xlsx", false, ...);
}

// Result: Files created one at a time - peak memory = 1 temporary Excel instance

How it works: ExcelSession uses a static SemaphoreSlim(1, 1) to serialize all CreateNew() and CreateNewAsync() calls. Even if called via Task.WhenAll, they queue and execute one at a time.

Why enforced: Each CreateNew() temporarily spawns an Excel process. Without serialization, parallel calls would create N Excel processes simultaneously, causing memory exhaustion. The lock prevents this automatically.

Code Simplification

OLD - Complex WithBatchAsync logic:

public static async Task<T> WithBatchAsync<T>(
    string? batchId,
    string filePath,
    bool save,
    Func<IExcelBatch, Task<T>> action)
{
    if (!string.IsNullOrEmpty(batchId))
    {
        // Path 1: Use existing batch
        var batch = BatchSessionTool.GetBatch(batchId);
        if (batch == null) throw new McpException(...);
        if (!PathMatches(...)) throw new McpException(...);
        return await action(batch);
    }
    else
    {
        // Path 2: Create temporary "batch-of-one"
        await using var batch = await ExcelSession.BeginBatchAsync(filePath);
        var result = await action(batch);
        if (save) await batch.Save();
        return result;
    }
}

NEW - Simple session lookup:

// Every tool method becomes:
var session = SessionManager.GetSession(sessionId);  // Throws if not found
return await commands.SomeAsync(session, args);

Lines of code saved: ~200+ LOC across 12 tools

LLM Guidance - Sessions Are Always Required

Simplified prompt (no decisions):

## Excel File Operations - ALWAYS Use Sessions

**EVERY workflow follows this pattern:**
1. excel_file(action: 'open', filePath: '...') → Get sessionId
2. Perform operations (ALL require sessionId)
3. excel_file(action: 'close', sessionId: '...') → Close file

**No exceptions.** You cannot list queries, create worksheets, or read ranges without an active session.

**Single operation?** Still requires open/close:
```yaml
# Even for "just list worksheets"
1. excel_file(action: 'open', filePath: 'data.xlsx')
   → { sessionId: 'abc-123' }
2. excel_worksheet(action: 'list', sessionId: 'abc-123')
3. excel_file(action: 'close', sessionId: 'abc-123')

Why? Sessions ensure proper Excel COM lifecycle management. There are no "quick operations" - all operations are safe and optimized.

Only excel_file(action: 'open'|'create-empty') accepts filePath. All other tools use sessionId only and do not take a file path.

**Cognitive load reduced to zero:** LLM no longer decides anything about performance optimization.

## Migration Path

### No Backwards Compatibility

## Benefits Analysis

### Fixes Issue #173: File Lock Race Condition

**Problem:** In the current system, rapid sequential non-batch calls fail with file lock errors because:

1. First call creates temporary Excel instance (batch-of-one)
2. First call completes, triggers `DisposeAsync()` (2-17 seconds)
3. Second call arrives before disposal completes
4. Second call tries to open same file → **FILE LOCKED ERROR**

**How this spec eliminates the problem:**

Current System (Issue #173): Call 1: excel_range(action, NO batchId) → Create temp Excel → Use → Start disposal (2-17s background) Call 2: excel_range(action, NO batchId) → Try create NEW Excel → File locked! ❌

New System (Mandatory Sessions): Call 1: excel_file(action: 'open') → Create Excel instance, return sessionId Call 2: excel_range(action, sessionId='abc-123') → Reuse SAME Excel instance ✅ Call 3: excel_range(action, sessionId='abc-123') → Reuse SAME Excel instance ✅ Call 4: excel_file(action: 'close') → Dispose Excel once (at end)

**Key insight:** By requiring sessions, we eliminate the "create → dispose → create → dispose" cycle that causes the race condition. The Excel instance stays alive for the entire workflow.

**Alternative considered:** Add retry logic with exponential backoff (proposed in #173) - but this is a **workaround** for a flawed architecture. Mandatory sessions **eliminate the root cause**.

### For LLMs

| Aspect | Before | After | Improvement |
|--------|--------|-------|-------------|
| **Decision Making** | "Should I use batch mode?" | No decision - sessions always used | ✅ Zero cognitive load |
| **Parameter Naming** | `batchId` (technical) | `sessionId` (familiar) | ✅ Intuitive |
| **Workflow Clarity** | Begin→Track GUID→Commit | Open→Work→Close | ✅ Universal pattern |
| **Learning Curve** | Must understand batching | Standard file operations | ✅ No explanation needed |
| **Error Recovery** | "Did I commit?" confusion | "Is file still open?" | ✅ Natural debugging |
| **Token Efficiency** | Decide + explain batch mode | Just open/close | ✅ 50% fewer tokens |
| **Code Complexity** | Handle optional parameter | sessionId always present | ✅ Simpler reasoning |

### For Users

| Aspect | Before | After |
|--------|--------|-------|
| **Terminology** | "What's a batch?" | "Opening a file" (universal) |
| **Documentation** | Explain batch optimization | No explanation needed |
| **Error Messages** | "Batch xyz not found" | "Session xyz not found" |
| **Tool Discovery** | Find excel_batch tool | Excel_file is obvious |

### For Developers

| Aspect | Impact | Benefit |
|--------|--------|---------|
| **Code Changes** | Breaking - remove dual paths, require sessionId | ✅ ~40% less code |
| **Infrastructure** | Rename classes (Batch→Session), single path | ✅ Half the complexity |
| **Testing** | Rewrite tests for session-only workflow | ✅ Simpler test setup |
| **Backwards Compat** | None - clean break | ✅ No legacy cruft |
| **Maintenance** | Single code path to maintain | ✅ Easier debugging |
| **New Features** | Build on simpler foundation | ✅ Faster development |

## Implementation Checklist

### Core Code Changes (Breaking)

- [ ] **DELETE** `BatchSessionTool.cs` entirely
- [ ] **DELETE** `BatchCommands.cs` (CLI) entirely
- [ ] **DELETE** `excel_batch.md` prompt file
- [ ] **DELETE** `WithBatchAsync()` method in ExcelToolsBase
- [ ] **RENAME** `IExcelBatch` → `IExcelSession` (interface)
- [ ] **RENAME** `ExcelBatch` → `ExcelSession` (implementation)
- [ ] **RENAME** `_activeBatches` → `_activeSessions` in SessionManager
- [ ] **RENAME** `BeginBatchAsync` → `OpenSessionAsync` in ExcelSession
- [ ] **ADD** `FileAction.Open`, `FileAction.Save`, `FileAction.Close` enum values
- [ ] **IMPLEMENT** `OpenWorkbookAsync()`, `SaveWorkbookAsync()`, `CloseWorkbookAsync()` in ExcelFileTool
- [ ] **CHANGE** all 12 tools: `batchId` (optional) → `sessionId` (required)
- [ ] **REMOVE** `excelPath` parameter from all 11 tools (except excel_file open/create)
- [ ] **REMOVE** `save` parameter from close action in excel_file tool
- [ ] **SIMPLIFY** all tool methods: remove WithBatchAsync, direct session lookup
- [ ] **UPDATE** session to track filePath internally (for excelPath removal)

### Testing (Complete Rewrite)

- [ ] **DELETE** all batch-mode specific tests
- [ ] **REWRITE** all tool tests to use session pattern (open → operate → close)
- [ ] **ADD** session lifecycle tests (open, save, close actions)
- [ ] **ADD** error tests: operate without sessionId → clear error message
- [ ] **ADD** error tests: sessionId not found → helpful error
- [ ] **ADD** read-only workflow tests: open → read → close (no save)
- [ ] **ADD** multiple-save workflow tests: open → modify → save → modify → save → close
- [ ] **ADD** discard changes tests: open → modify → close (no save = rollback)
- [ ] **VERIFY** no performance regression (sessions were batches internally)
- [ ] **TEST** integration with MCP clients (Claude, Copilot) using new API
- [ ] **ADD** concurrency tests: verify operations within session are serial (not parallel)
- [ ] **ADD** multi-session tests: verify operations between sessions CAN run parallel
- [ ] **ADD** resource limit tests: verify 5+ concurrent sessions don't cause memory issues
- [ ] **ADD** file creation tests: verify sequential creation pattern (not parallel)

### Documentation (Complete Rewrite)

- [ ] **DELETE** `excel_batch.md` prompt file
- [ ] **DELETE** all references to "batch mode" and "when to batch"
- [ ] **REWRITE** `excel_file.md` with session lifecycle patterns
- [ ] **REWRITE** `tool_selection_guide.md` (remove batch decision logic)
- [ ] **REWRITE** README examples (all use session pattern)
- [ ] **UPDATE** all 12 tool `[Description]` attributes: "sessionId (required)"
- [ ] **ADD** session lifecycle diagram to README
- [ ] **REWRITE** `examples/` directory scripts (all use open/close)
- [ ] **ADD** migration guide: "Breaking Changes in 2.0.0"

### LLM Guidance

- [ ] Create `session_lifecycle.md` prompt with open/save/close patterns
- [ ] Update `user_request_patterns.md` with session detection hints
- [ ] Add session error recovery guidance
- [ ] Update elicitations to ask about multi-operation intent
- [ ] **ADD** concurrency model documentation: operations within session are serial
- [ ] **ADD** performance guidance: batch operations on same file, parallelize different files
- [ ] **ADD** resource limits guidance: recommend 3-5 concurrent sessions max
- [ ] **ADD** file creation guidance: always sequential, never parallel

## Edge Cases & Error Handling

### Session Not Found

**Before:**
```json
{
  "error": "Batch session 'xyz' not found. It may have already been committed..."
}

After:

{
   "success": false,
   "errorMessage": "Session 'xyz' not found. The workbook may have already been closed.",
   "isError": true,
   "suggestedNextActions": [
      "Call excel_file(action: 'open', filePath: '...') to open the workbook again",
      "Check if another process closed the file"
   ]
}

All tools MUST return JSON for business errors:

• success: false
• errorMessage: human-readable reason
• isError: true
• suggestedNextActions: concrete next steps for the LLM

MCP exceptions (McpException) are reserved for protocol issues only (missing/invalid parameters, unknown actions, missing files), not business logic failures.

Forgotten Close

Mitigation (client-side execution model):

1. Process termination cleanup - When MCP client (VS Code, Claude Desktop) closes, all Excel instances automatically close
2. Manual process kill - User can terminate Excel via Task Manager if needed
3. Session listing - Future enhancement: excel_file(action: 'list-sessions') to show active sessions
4. No automatic timeout - Client-side execution means no server-side cleanup needed

Why this works:

• MCP server runs on user's machine (not remote server)
• Excel process lifetime tied to MCP client process lifetime
• User has full control via OS process management

File Locking

No change - same Excel COM behavior. Sessions don't change locking semantics.

Multiple Workbooks / Sessions

You can open multiple files simultaneously, but understand the concurrency model:

1. Each file gets its own session (and Excel process)
2. Operations between files run in parallel (separate processes)
3. Operations within each file remain serial (Excel COM limitation - see CRITICAL section above)

Example:

// Open 3 files (3 Excel processes created)
var sessA = await CreateSessionAsync("A.xlsx");  // Excel.Application process 1
var sessB = await CreateSessionAsync("B.xlsx");  // Excel.Application process 2
var sessC = await CreateSessionAsync("C.xlsx");  // Excel.Application process 3

// These 3 operations run in TRUE parallel (different processes)
await Task.WhenAll(
    GetSession(sessA).Execute(ctx => ctx.Book.Worksheets.Add("Sheet1")),  // Process 1
    GetSession(sessB).Execute(ctx => ctx.Book.Worksheets.Add("Sheet1")),  // Process 2
    GetSession(sessC).Execute(ctx => ctx.Book.Worksheets.Add("Sheet1"))   // Process 3
);  // ✅ True parallelism - different Excel processes

// But operations on SAME file are SERIAL (queued)
await GetSession(sessA).Execute(ctx => ctx.Book.Worksheets.Add("Sheet2"));  // Queued operation 1
await GetSession(sessA).Execute(ctx => ctx.Book.Worksheets.Add("Sheet3"));  // Queued operation 2 (waits for 1)

Resource Limits & Best Practices:

• Each session = one Excel process (~50-100MB+ memory)
• Recommendation: Limit to 3-5 concurrent sessions for typical desktop machines
• LLMs should close sessions promptly to free resources
• Monitor system resources when processing many files

File Locking:

• SessionManager prevents opening same file twice: File 'X.xlsx' is already open in another session
• This matches Excel UI behavior (cannot open same file in multiple windows)
• Attempting to open an already-open file throws InvalidOperationException

LLM Guidance:

LLMs should track the correct sessionId for each workbook and close sessions when each logical workflow completes. For bulk file processing, consider sequential processing to limit resource usage:

# Processing many files - sequential approach (resource-friendly)
for each file:
  1. open → sessionId
  2. perform operations (all serial within session)
  3. save
  4. close (frees Excel process immediately)

# OR parallel approach for small batches (faster but memory-intensive)
1. open files 1-5 → get 5 sessionIds (5 Excel processes)
2. process all 5 in parallel
3. close all 5
4. repeat for files 6-10

Success Metrics

Quantitative

• Reduced token usage: Session guidance ~40% shorter than batch guidance
• Fewer errors: Track "session not found" vs. "batch not found" rates
• Adoption rate: % of multi-operation workflows using sessions

Qualitative

• LLM feedback: Do Claude/Copilot naturally use open/close without prompting?
• User confusion: Reduced questions about "what's a batch?" in docs/issues
• Code clarity: Naming matches intent in tool descriptions

Design Decisions (Resolved)

1. Should open action fail if workbook already open in Excel UI?

Decision: Yes, fail immediately with clear error Rationale: Excel COM limitation - we can't safely work with UI-open files Implementation: Existing file lock detection works correctly

2. Should save be implicit on close by default?

Decision: No, close NEVER saves - explicit save action only Rationale:

• Explicit is better than implicit - No surprise saves
• LLM clarity - "save" action = save, "close" action = close (no overlap)
• Read-only workflows - Just open → read → close (no save needed)
• Multiple saves - Save multiple times during session, close at end
• Predictable behavior - close always does same thing (cleanup only)

Implementation: Remove save parameter from close entirely. Users call excel_file(action: 'save') explicitly when needed.

3. Should sessions timeout automatically after inactivity?

Decision: No automatic timeout - rely on process lifetime Rationale:

• Client-side execution - MCP server runs on user's machine, not remote server
• Process lifetime - When user closes MCP client (VS Code, Claude Desktop), process terminates and Excel closes
• Manual control - User can kill Excel process via Task Manager if needed
• Simpler implementation - No background timers, no timeout logic
• No false positives - No "session timed out" errors during long-running operations

Implementation: No timeout logic. Sessions persist until explicitly closed or process terminates.

4. What are the save semantics for different workflows?

Decision: Explicit save action only, close never saves

Workflows supported:

1. Read-only workflow:

   open → read operations → close (no save needed)
   

   Use case: List queries, view data, check connections

2. Single save workflow:

   open → modify operations → save → close
   

   Use case: Standard edit workflow

3. Multiple save workflow:

   open → modify → save → modify → save → modify → save → close
   

   Use case: Incremental changes, checkpoints, complex multi-step operations

4. Discard changes workflow:

   open → modify operations → close (no save = changes discarded)
   

   Use case: Experimental changes, testing, rollback

Rationale:

• Explicit control - User decides when to persist changes
• Read-only support - No save parameter needed anywhere
• Flexibility - Save 0, 1, or N times during session
• Predictability - close always does same thing (cleanup)

5. Should we keep excel_batch as deprecated alias?

Decision: No, complete removal Rationale:

• Maintaining alias adds complexity
• Breaking change anyway, might as well be clean
• Forces users to adopt new pattern completely
• No confusion from "two ways to do same thing"

6. Should CLI and MCP Server both use same session API?

Decision: Yes, unified API everywhere Rationale:

• CLI and MCP Server share Core/ComInterop
• Consistent experience across interfaces
• Same documentation applies to both
• No mode-specific quirks

Timeline

Estimated effort: 2-3 weeks (one developer)

Week 1: Delete & Rename (Breaking Changes)

• Day 1-2: Delete batch infrastructure, rename classes
• Day 3-4: Add session lifecycle to excel_file tool
• Day 5: Update 3-4 tools to require sessionId

Week 2: Tool Updates & Testing

• Day 1-3: Update remaining 8-9 tools to require sessionId
• Day 4: Simplify all tool implementations (remove WithBatchAsync)
• Day 5: Rewrite core tests for session-only pattern

Week 3: Integration & Documentation

• Day 1-2: Integration tests with MCP clients
• Day 3: Rewrite all documentation and examples
• Day 4: Migration guide, release notes, breaking change announcements
• Day 5: Beta release testing

Release Timeline:

• Week 4: Version 2.0.0-beta (breaking changes, early adopters)
• Week 6: Version 2.0.0 stable (after beta feedback)
• Month 6: Version 1.x end-of-life (final security patch)

Appendix: Example Workflows

Before (Batch API)

LLM: I'll create 3 worksheets using batch mode for performance.

1. excel_batch(action: 'begin', filePath: 'sales.xlsx')
   → { batchId: 'abc-123' }

2. excel_worksheet(action: 'create', excelPath: 'sales.xlsx',
                   sheetName: 'Q1', batchId: 'abc-123')

3. excel_worksheet(action: 'create', excelPath: 'sales.xlsx',
                   sheetName: 'Q2', batchId: 'abc-123')

4. excel_worksheet(action: 'create', excelPath: 'sales.xlsx',
                   sheetName: 'Q3', batchId: 'abc-123')

5. excel_batch(action: 'commit', batchId: 'abc-123', save: true)
   → { success: true }

After (Session API)

LLM: I'll open the workbook and create 3 worksheets.

1. excel_file(action: 'open', filePath: 'sales.xlsx')
   → { sessionId: 'abc-123' }

2. excel_worksheet(action: 'create',
                   sheetName: 'Q1', sessionId: 'abc-123')

3. excel_worksheet(action: 'create',
                   sheetName: 'Q2', sessionId: 'abc-123')

4. excel_worksheet(action: 'create',
                   sheetName: 'Q3', sessionId: 'abc-123')

5. excel_file(action: 'close', sessionId: 'abc-123')
   → { success: true }

Differences:

• "Begin batch" → "Open file" (natural language)
• "Commit batch" → "Close file" (universal action)
• batchId (optional) → sessionId (required)
• No more decision about "should I batch?"
• Close never saves - explicit save action only
• excelPath removed from all operations except open (session knows the file)
• Simpler: 5 calls vs 5 calls, but with intuitive naming

Read-Only Workflow Example

LLM: I'll check which Power Queries are in this workbook.

1. excel_file(action: 'open', filePath: 'sales.xlsx')
   → { sessionId: 'abc-123' }

2. excel_powerquery(action: 'list', sessionId: 'abc-123')
   → { queries: ['SalesData', 'CustomerInfo', 'ProductCatalog'] }

3. excel_file(action: 'close', sessionId: 'abc-123')
   → { success: true }

Key points:

• No save action needed (read-only operation)
• Close doesn't save (no changes made)
• Simple: open → read → close

Multiple-Save Workflow Example

LLM: I'll create multiple queries with checkpoints after each one.

1. excel_file(action: 'open', filePath: 'sales.xlsx')
   → { sessionId: 'abc-123' }

2. excel_powerquery(action: 'import', sessionId: 'abc-123',
                    queryName: 'SalesData', mCodeFile: 'sales.m')
   → { success: true }

3. excel_file(action: 'save', sessionId: 'abc-123')
   → { success: true }  // Checkpoint 1

4. excel_powerquery(action: 'import', sessionId: 'abc-123',
                    queryName: 'CustomerInfo', mCodeFile: 'customers.m')
   → { success: true }

5. excel_file(action: 'save', sessionId: 'abc-123')
   → { success: true }  // Checkpoint 2

6. excel_powerquery(action: 'import', sessionId: 'abc-123',
                    queryName: 'ProductCatalog', mCodeFile: 'products.m')
   → { success: true }

7. excel_file(action: 'save', sessionId: 'abc-123')
   → { success: true }  // Final checkpoint

8. excel_file(action: 'close', sessionId: 'abc-123')
   → { success: true }

Key points:

• Multiple explicit save actions during session
• Each save creates a checkpoint (changes persisted)
• Close at end does NOT save (last save already persisted everything)
• Incremental persistence reduces risk of data loss

Conclusion

This redesign achieves the ultimate goal: Eliminate all cognitive load from LLMs by making sessions the only way to work with Excel files. The Open/Save/Close pattern is:

1. Universal - Every developer/LLM knows file lifecycle (no explanation needed)
2. Mandatory - No decisions about batching, sessions are always used
3. Simple - Single code path, 40% less code to maintain
4. Performant - Same Excel COM optimization (sessions are batches internally)
5. Breaking - Clean slate, no backwards compatibility baggage
6. Extensible - Future optimizations (connection pooling, caching) build on simpler foundation
7. Bug-fixing - Eliminates Issue #173 file lock race condition by design
8. Realistic - Acknowledges and documents Excel COM threading limitations (single-threaded STA model)

Key Achievements

For LLMs:

• ✅ Zero decision fatigue (no "should I batch?" questions)
• ✅ 50% fewer tokens for workflows (no batch mode explanations)
• ✅ Intuitive API (open/close is universal)
• ✅ Clear concurrency model (operations within session = serial, between sessions = parallel)

For Developers:

• ✅ 40% less code (remove dual paths)
• ✅ Simpler testing (single pattern)
• ✅ Easier maintenance (one way to do things)
• ✅ Fixes Issue #173 (eliminates file lock race condition at architectural level)
• ✅ Honest documentation (acknowledges COM limitations, not hidden complexity)

For Users:

• ✅ Consistent performance (always optimized)
• ✅ Clear errors ("session not found" is obvious)
• ✅ Predictable behavior (explicit lifecycle)
• ✅ No more file lock errors from rapid sequential operations
• ✅ Realistic expectations (understand that operations within a file are serial due to Excel COM)

Critical Technical Constraints

This spec acknowledges and documents fundamental Excel COM limitations:

1. Single-threaded nature - Each session runs on one STA thread with serial operation queue
2. No within-session parallelism - Multiple operations on same file execute serially (COM requirement)
3. Between-session parallelism possible - Different files = different Excel processes = true parallelism
4. Resource constraints - Each session = ~50-100MB+ memory; recommend 3-5 concurrent sessions max
5. File creation must be sequential - Parallel creation causes resource exhaustion

These are not implementation deficiencies - they are inherent Excel COM API constraints that apply to ANY Excel automation solution (VBA, .NET interop, Python xlwings, etc.). By documenting them clearly, we set correct expectations and guide users toward performant patterns.

Breaking Change Justification

Why break backwards compatibility?

1. Current API is fundamentally flawed - Optional batching creates decision fatigue
2. Gradual migration would take years - Dual paths would persist indefinitely
3. Clean break is clearer - Users update once vs. confused by deprecated patterns
4. Version 2.0 is the right time - Major version signals breaking changes
5. Migration is straightforward - Wrap operations in open/close (mechanical change)
6. Fixes critical bug at architectural level - Issue #173 file lock race condition eliminated by design (retry logic is just a workaround)

Recommendation: ✅ Approve for implementation in Version 2.0.0 (breaking release)

This is not just a rename - it's a fundamental simplification that makes ExcelMcp significantly easier for LLMs to use correctly while eliminating an entire class of file locking bugs.