# Investibook Entity Relationship Diagram

This diagram shows the data model and relationships in the Investibook system.

```mermaid
erDiagram
    User ||--o{ UserSession : "has"
    User ||--o{ Workspace : "owns"
    Workspace ||--|| WorkspaceSession : "has active"
    Workspace ||--o{ Source : "contains"
    Workspace ||--o{ Artifact : "generates"
    Workspace ||--o{ Message : "has"
    Workspace ||--o{ WorkspaceFile : "stores"
    Source ||--o{ Artifact : "generates"
    Alert ||--o| Source : "creates"

    User {
        string id PK
        string email UK
        datetime created_at
    }

    UserSession {
        string token PK
        string user_id FK
        datetime created_at
    }

    Workspace {
        string id PK
        string user_id FK
        string name
        string description
        boolean is_example
        boolean is_shared
        datetime created_at
        datetime updated_at
    }

    WorkspaceSession {
        string workspace_id PK,FK
        string session_id
        datetime updated_at
    }

    Source {
        string id PK
        string workspace_id FK
        enum type "alert | git_repo | mcp_connector | cluster_meta | user_meta"
        string name
        string description
        boolean readonly
        string alertId
        json meta
        datetime created_at
        boolean displayOnly
        enum status "loading | ready | error"
        string statusMessage
        string taskId
    }

    Artifact {
        string id PK
        string workspace_id FK
        string sourceId FK
        enum type "report | chart | summary | timeline | note"
        string title
        string description
        json data
        datetime created_at
    }

    Message {
        string id PK
        string workspace_id FK
        enum role "user | assistant | system"
        string content
        datetime timestamp
        json parts
    }

    WorkspaceFile {
        string path PK
        string workspace_id FK
        string name
        enum type "file | directory"
        string size
        json children
    }

    Alert {
        string alertId PK
        string cluster_id
        string org_id
        string alert_type
        datetime alert_time
        json cluster_meta
    }

    InsightData {
        string analysis_uuid PK
        string jira_issue_id
        string cluster_id
        json direct_cause
        json detail
        json metadata
        number time_range_start_ts
        number time_range_end_ts
        datetime created_at
    }
```

## Key Relationships

### Core Entities

1. **User → Workspace (1:N)**
   - Each user can own multiple workspaces
   - Workspaces can be shared (read-only) with other users
   - Example workspace is a singleton per system

2. **Workspace → Source (1:N)**
   - Each workspace contains multiple sources
   - Sources are stored as JSON in `workspace_sources.data`
   - Source types: alert, git_repo, mcp_connector, cluster_meta, user_meta

3. **Workspace → Artifact (1:N)**
   - Each workspace generates multiple artifacts
   - Artifacts are stored as JSON in `workspace_artifacts.data`
   - Artifact types: report, chart, summary, timeline, note

4. **Source → Artifact (1:N)**
   - Sources generate artifacts via `sourceId` reference
   - Example: Alert source generates Guardian Insight Report and Anomaly Detection Report

5. **Workspace → Message (1:N)**
   - Each workspace has a chat history
   - Messages stored as JSON in `workspace_messages.data`
   - Message parts include text, tool calls, reasoning, images

### Special Entities

6. **Alert → Source**
   - External alerts create alert sources
   - Alert preparation happens asynchronously
   - Status tracked via taskId polling

7. **Workspace → WorkspaceFile**
   - Each workspace has a file tree
   - Files include: insight.json, diagnostic_package.json, resource_worker_insight.json
   - Used for report rendering

8. **Workspace → WorkspaceSession**
   - Each workspace has one active ACP session
   - Session connects to OpenCode or Claude agent
   - Session ID tracks agent process

## Storage Strategy

### SQLite Tables
- `users` - User accounts
- `user_sessions` - Authentication tokens
- `workspaces` - Workspace metadata
- `workspace_sessions` - Active agent sessions
- `workspace_sources` - JSON blob of sources
- `workspace_artifacts` - JSON blob of artifacts
- `workspace_messages` - JSON blob of messages

### File System
- `{DATA_DIR}/workspaces/{workspace_id}/` - Workspace working directory
- `{DATA_DIR}/workspaces/{workspace_id}/insight.json` - Guardian Insight Report data
- `{DATA_DIR}/workspaces/{workspace_id}/diagnostic_package.json` - Anomaly Detection Report data
- `{DATA_DIR}/workspaces/{workspace_id}/resource_worker_insight.json` - Resource Worker data

## Data Flow

1. **User creates workspace** → SQLite `workspaces` table
2. **User adds alert source** → JSON in `workspace_sources`, triggers async alert preparation
3. **Alert preparation completes** → Files written to workspace directory
4. **System checks available files** → Creates artifacts only if files exist
5. **User clicks artifact** → Frontend fetches JSON file and renders report
6. **User sends message** → Stored in `workspace_messages`, sent to ACP agent
7. **Agent responds** → Message parts (text, tool calls, reasoning) stored

## Type Enums

### SourceType
- `alert` - Alert from monitoring system
- `git_repo` - Git repository (not yet supported)
- `mcp_connector` - MCP protocol connector
- `cluster_meta` - TiDB cluster metadata
- `user_meta` - User context and preferences

### ArtifactType
- `report` - Analysis report (Guardian Insight, Anomaly Detection)
- `chart` - Data visualization
- `summary` - Summary document
- `timeline` - Timeline view
- `note` - User note

### MessageRole
- `user` - User message
- `assistant` - Agent response
- `system` - System message