Implement Event Sourcing for batch operations and editor state management

[mk3008]

Feature Request: Event Sourcing Architecture for Editor Operations

Problem Statement

Currently, ZoSQL has basic undo/redo functionality but lacks comprehensive event sourcing for batch operations. When users perform batch operations (formatting, static analysis, CTE decomposition, etc.), the system needs robust state management to track, replay, and rollback complex operations.

Current State Analysis

✅ Implemented: Basic UndoRedo utilities in src/lib/functional/state.ts
❌ Missing: Full event sourcing architecture for complex operations
❌ Missing: Operation-level granular tracking
❌ Missing: Cross-tab state synchronization with events

Why Event Sourcing is Critical

Batch Operations Complexity

• Format Operations: SQL formatting changes entire query structure
• Static Analysis: May suggest and apply multiple fixes
• CTE Decomposition: Creates multiple new CTEs and modifies references
• CTE Rename: Updates references across multiple tabs simultaneously
• Auto-completion: Inserts complex SQL constructs

Current Limitations

• Simple undo/redo only tracks final states
• No operation-level granularity
• Difficult to rollback complex batch operations
• No audit trail of what actually changed
• Cross-tab operations not properly tracked

Proposed Event Sourcing Architecture

1. Event Definition

```typescript
interface EditorEvent {
id: string;
timestamp: number;
type: string;
tabId: string;
operation: {
name: string;
params: Record<string, unknown>;
batch?: boolean;
};
changes: {
before: string;
after: string;
affected?: string[]; // Other affected tabs
};
metadata?: {
user?: string;
source?: 'manual' | 'batch' | 'auto';
reason?: string;
};
}
```

2. Event Types

• CONTENT_CHANGED: Direct text editing
• FORMAT_APPLIED: SQL formatting operation
• ANALYSIS_APPLIED: Static analysis fixes
• CTE_DECOMPOSED: CTE decomposition operation
• CTE_RENAMED: CTE rename with reference updates
• TAB_CREATED: New tab creation
• TAB_DELETED: Tab removal
• WORKSPACE_LOADED: Initial workspace state

3. Event Store Interface

```typescript
interface EventStore {
// Core operations
append(event: EditorEvent): Promise;
getEvents(tabId?: string, fromTimestamp?: number): Promise<EditorEvent[]>;
getSnapshot(tabId: string, atTimestamp?: number): Promise;

// Replay and rollback
replay(fromEvent: string, toEvent?: string): Promise;
rollback(toEvent: string): Promise;

// Batch operations
startBatch(batchId: string): void;
endBatch(batchId: string): Promise<EditorEvent[]>;
rollbackBatch(batchId: string): Promise;
}
```

Implementation Benefits

1. Granular Operation Tracking

• Track individual steps within batch operations
• Understand what each operation actually changed
• Selective rollback of specific changes

2. Cross-Tab Consistency

• Track operations that affect multiple tabs
• Ensure consistent state across all affected components
• Proper handling of CTE rename operations

3. Audit and Debugging

• Complete history of all changes
• Ability to replay operations for debugging
• Understanding of operation side effects

4. Advanced Undo/Redo

• Undo entire batch operations as single unit
• Selective undo of specific types of changes
• Redo with conflict detection

5. Collaboration Preparation

• Event-based architecture ready for real-time collaboration
• Conflict resolution capabilities
• Operation merging and transformation

Implementation Phases

Phase 1: Core Event Infrastructure

• Define event schema and types
• Implement in-memory event store
• Basic event append and retrieval
• Integration with existing UndoRedo

Phase 2: Batch Operation Support

• Batch operation tracking
• Multi-tab event coordination
• Batch rollback functionality
• Integration with format/analysis operations

Phase 3: Advanced Features

• Event store persistence (IndexedDB)
• Event compression and cleanup
• Performance optimization
• Conflict detection and resolution

Phase 4: UI Integration

• History panel showing operations
• Selective undo/redo interface
• Operation impact visualization
• Debugging and replay tools

Example Usage Scenarios

Scenario 1: CTE Decomposition

```
User clicks "Decompose CTE"
↓
System creates batch: "cte_decomposition_001"
↓
Events generated:

1. CTE_CREATED: "base_data" tab
2. CTE_CREATED: "aggregated" tab
3. CONTENT_CHANGED: Main query updated
4. TAB_SWITCHED: Focus moved to "base_data"
   ↓
   User can rollback entire batch if needed
   ```

Scenario 2: Format + Static Analysis

```
User clicks "Format & Analyze"
↓
Batch operation:

1. FORMAT_APPLIED: Query reformatted
2. ANALYSIS_APPLIED: 3 issues fixed automatically
3. CONTENT_CHANGED: Final content updated
   ↓
   User sees granular history of what changed
   ```

Technical Considerations

Performance

• Event store optimization for large operation histories
• Snapshot creation for fast state reconstruction
• Event compression for old operations

Storage

• In-memory for active sessions
• IndexedDB persistence for session recovery
• Cleanup policies for old events

Concurrency

• Event ordering guarantees
• Conflict detection for simultaneous operations
• Lock-free operation where possible

Integration Points

• Monaco Editor change tracking
• Tab management system
• Batch operation processors
• Workspace state management
• CTE reference tracking

Priority

High - Essential for robust batch operation support and user confidence

---

Event sourcing will provide the foundation for reliable batch operations and advanced undo/redo capabilities