ss-tools/specs/008-migration-ui-improvements/data-model.md

# Data Model: Migration UI Improvements

**Date**: 2025-12-27 | **Status**: Draft

## Entities

### 1. Task (Extended)

**Source**: `backend/src/core/task_manager.py`

**Fields**:
- `id: UUID` - Unique task identifier
- `type: str` - Task type (e.g., "migration")
- `status: TaskStatus` - Current status (extended enum)
- `start_time: datetime` - When task was created
- `end_time: datetime | None` - When task completed (if applicable)
- `logs: List[LogEntry]` - Task execution logs
- `context: Dict` - Task-specific data
- `input_required: bool` - Whether task is awaiting user input
- `input_request: Dict | None` - Details about required input (for AWAITING_INPUT state)

**New Status Values**:
- `AWAITING_INPUT` - Task is paused waiting for user input (e.g., password)

**Relationships**:
- Has many: `LogEntry`
- Belongs to: `Migration` (if migration task)

**Validation Rules**:
- `id` must be unique and non-null
- `status` must be valid TaskStatus enum value
- `start_time` must be set on creation
- `input_request` required when status is `AWAITING_INPUT`

**State Transitions**:
```mermaid
graph LR
    PENDING --> RUNNING
    RUNNING --> SUCCESS
    RUNNING --> FAILED
    RUNNING --> AWAITING_INPUT
    AWAITING_INPUT --> RUNNING
    AWAITING_INPUT --> FAILED
```

### 2. LogEntry

**Source**: Existing in codebase

**Fields**:
- `timestamp: datetime` - When log entry was created
- `level: str` - Log level (INFO, WARNING, ERROR, etc.)
- `message: str` - Log message
- `context: Dict | None` - Additional context data

**Validation Rules**:
- `timestamp` must be set
- `level` must be valid log level
- `message` must be non-empty

### 3. DatabasePasswordRequest (New)

**Source**: New entity for password prompts

**Fields**:
- `database_name: str` - Name of database requiring password
- `connection_string: str | None` - Partial connection string (without password)
- `error_message: str | None` - Original error message
- `attempt_count: int` - Number of password attempts

**Validation Rules**:
- `database_name` must be non-empty
- `attempt_count` must be >= 0

**Relationships**:
- Embedded in: `Task.input_request`

### 4. TaskListResponse (API DTO)

**Fields**:
- `tasks: List[TaskSummary]` - List of task summaries
- `total: int` - Total number of tasks
- `limit: int` - Pagination limit
- `offset: int` - Pagination offset

### 5. TaskSummary (API DTO)

**Fields**:
- `id: UUID` - Task ID
- `type: str` - Task type
- `status: str` - Current status
- `start_time: datetime` - Start time
- `end_time: datetime | None` - End time (if completed)
- `requires_input: bool` - Whether task needs user input

### 6. TaskLogResponse (API DTO)

**Fields**:
- `task_id: UUID` - Task ID
- `logs: List[LogEntry]` - Task logs
- `status: str` - Current task status

### 7. PasswordPromptRequest (API DTO)

**Fields**:
- `task_id: UUID` - Task ID
- `passwords: Dict[str, str]` - Database name to password mapping

**Validation Rules**:
- `task_id` must exist and be in AWAITING_INPUT state
- All required databases must be provided

## API Contracts

### 1. GET /api/tasks - List Tasks

**Purpose**: Retrieve list of recent migration tasks

**Parameters**:
- `limit: int` (query, optional) - Pagination limit (default: 10)
- `offset: int` (query, optional) - Pagination offset (default: 0)
- `status: str` (query, optional) - Filter by status

**Response**: `TaskListResponse`

**Example**:
```json
{
  "tasks": [
    {
      "id": "abc-123",
      "type": "migration",
      "status": "RUNNING",
      "start_time": "2025-12-27T09:47:12Z",
      "end_time": null,
      "requires_input": false
    }
  ],
  "total": 1,
  "limit": 10,
  "offset": 0
}
```

### 2. GET /api/tasks/{task_id}/logs - Get Task Logs

**Purpose**: Retrieve detailed logs for a specific task

**Parameters**: None

**Response**: `TaskLogResponse`

**Example**:
```json
{
  "task_id": "abc-123",
  "status": "AWAITING_INPUT",
  "logs": [
    {
      "timestamp": "2025-12-27T09:47:12Z",
      "level": "ERROR",
      "message": "Must provide a password for the database",
      "context": {
        "database": "PostgreSQL"
      }
    }
  ]
}
```

### 3. POST /api/tasks/{task_id}/resume - Resume Task with Input

**Purpose**: Provide required input and resume a paused task

**Request Body**: `PasswordPromptRequest`

**Response**:
```json
{
  "success": true,
  "message": "Task resumed successfully"
}
```

**Error Responses**:
- `404 Not Found` - Task not found
- `400 Bad Request` - Invalid input or task not in AWAITING_INPUT state
- `409 Conflict` - Task already completed or failed

## Database Schema Changes

### Task Persistence (SQLite)

**Table**: `persistent_tasks`

**Columns**:
- `id TEXT PRIMARY KEY` - Task ID
- `status TEXT NOT NULL` - Task status
- `created_at TEXT NOT NULL` - Creation timestamp
- `updated_at TEXT NOT NULL` - Last update timestamp
- `input_request JSON` - Serialized input request data
- `context JSON` - Serialized task context

**Indexes**:
- `idx_status` on `status` column
- `idx_created_at` on `created_at` column

## Event Flow

### Normal Task Execution

```mermaid
sequenceDiagram
    participant UI
    participant API
    participant TaskManager
    participant MigrationPlugin

    UI->>API: Start migration
    API->>TaskManager: Create task
    TaskManager->>MigrationPlugin: Execute
    MigrationPlugin->>TaskManager: Update status (RUNNING)
    MigrationPlugin->>TaskManager: Add logs
    MigrationPlugin->>TaskManager: Update status (SUCCESS/FAILED)
```

### Task with Password Requirement

```mermaid
sequenceDiagram
    participant UI
    participant API
    participant TaskManager
    participant MigrationPlugin

    UI->>API: Start migration
    API->>TaskManager: Create task
    TaskManager->>MigrationPlugin: Execute
    MigrationPlugin->>TaskManager: Update status (RUNNING)
    MigrationPlugin->>TaskManager: Detect password error
    TaskManager->>TaskManager: Update status (AWAITING_INPUT)
    TaskManager->>API: Persist task (if needed)
    API->>UI: Task status update
    UI->>API: Get task logs
    API->>UI: Return logs with error
    UI->>User: Show password prompt
    User->>UI: Provide password
    UI->>API: POST /tasks/{id}/resume
    API->>TaskManager: Resume task with password
    TaskManager->>MigrationPlugin: Continue execution
    MigrationPlugin->>TaskManager: Update status (RUNNING)
    MigrationPlugin->>TaskManager: Complete task
```

## Validation Rules

### Task Creation
- Task ID must be unique
- Start time must be set
- Initial status must be PENDING

### Task State Transitions
- Only RUNNING tasks can transition to AWAITING_INPUT
- Only AWAITING_INPUT tasks can be resumed
- Completed tasks (SUCCESS/FAILED) cannot be modified

### Password Input
- All required databases must be provided
- Passwords must meet minimum complexity requirements
- Invalid passwords trigger new error and prompt again

## Implementation Notes

1. **Task Persistence**: Only tasks in AWAITING_INPUT state will be persisted to handle backend restarts

2. **Error Detection**: Specific pattern matching for Superset "Must provide a password" errors

3. **UI Integration**: Real-time updates using existing WebSocket infrastructure

4. **Security**: Passwords are not permanently stored, only used for immediate task resumption

5. **Performance**: Basic pagination for task history to handle growth

## Open Questions

None - All design decisions have been documented and validated against requirements.