# Chat & Agent Execution Models

This document covers models related to AI agent conversations, command execution, and audit logging. These models capture the full lifecycle of agent interactions, from user messages to executed commands and their outcomes.

**File**: `c:\Users\Smirk\Downloads\Tesslate-Studio\orchestrator\app\models.py`

## Chat Model

The Chat model represents a conversation thread between a user and an AI agent. Each chat is associated with a specific project and contains multiple messages.

### Schema

```python
class Chat(Base):
    __tablename__ = "chats"

    # Identity
    id: UUID                    # Primary key
    user_id: UUID               # Foreign key to User
    project_id: UUID            # Foreign key to Project (nullable for standalone chats)

    # Timestamp
    created_at: datetime        # When conversation started
```

### Key Relationships

```python
# Chat belongs to a user and project
user = relationship("User", back_populates="chats")
project = relationship("Project", back_populates="chats")

# Chat contains multiple messages
messages = relationship("Message", back_populates="chat", cascade="all, delete-orphan")
```

### Common Queries

**Get chat with all messages**:
```python
result = await db.execute(
    select(Chat)
    .options(selectinload(Chat.messages))
    .where(Chat.id == chat_id)
)
chat = result.scalar_one_or_none()
```

**Get user's chats for a project**:
```python
result = await db.execute(
    select(Chat)
    .where(Chat.user_id == user.id)
    .where(Chat.project_id == project.id)
    .order_by(Chat.created_at.desc())
)
chats = result.scalars().all()
```

**Create a new chat**:
```python
chat = Chat(
    user_id=user.id,
    project_id=project.id
)
db.add(chat)
await db.commit()
await db.refresh(chat)
```

**Get recent chats across all projects**:
```python
result = await db.execute(
    select(Chat)
    .options(
        selectinload(Chat.project),
        selectinload(Chat.messages)
    )
    .where(Chat.user_id == user.id)
    .order_by(Chat.created_at.desc())
    .limit(10)
)
recent_chats = result.scalars().all()
```

### Notes

- Each chat is scoped to a single project (or NULL for general chats)
- Chats are persistent—users can return to previous conversations
- When a project is deleted, all associated chats are deleted (CASCADE)
- The frontend typically shows one active chat per project

---

## Message Model

The Message model stores individual messages within a chat conversation, including both user prompts and AI assistant responses.

### Schema

```python
class Message(Base):
    __tablename__ = "messages"

    # Identity
    id: UUID                    # Primary key
    chat_id: UUID               # Foreign key to Chat

    # Content
    role: str                   # 'user' or 'assistant'
    content: str                # Message text content
    message_metadata: JSON      # Agent execution data (steps, iterations, tool calls)

    # Timestamp
    created_at: datetime        # When message was created
```

### Message Roles

- **user**: Messages sent by the user (prompts, requests)
- **assistant**: Messages generated by the AI agent (responses, code, explanations)

### Message Metadata

The `message_metadata` JSON field stores rich execution data from agent runs:

```json
{
  "agent_type": "StreamAgent",
  "model": "claude-sonnet-4",
  "iterations": 3,
  "total_tokens": 4523,
  "steps": [
    {
      "type": "tool_call",
      "tool": "write_file",
      "args": {"file_path": "src/App.tsx", "content": "..."},
      "result": "success"
    },
    {
      "type": "tool_call",
      "tool": "bash_command",
      "args": {"command": "npm install react-router-dom"},
      "result": "success",
      "output": "added 12 packages in 3s"
    },
    {
      "type": "thinking",
      "content": "Now I'll update the routing configuration..."
    }
  ],
  "files_modified": ["src/App.tsx", "package.json"],
  "commands_executed": ["npm install react-router-dom"]
}
```

### Key Relationships

```python
chat = relationship("Chat", back_populates="messages")
```

### Common Queries

**Get messages in a chat (chronological)**:
```python
result = await db.execute(
    select(Message)
    .where(Message.chat_id == chat_id)
    .order_by(Message.created_at)
)
messages = result.scalars().all()
```

**Add a user message**:
```python
message = Message(
    chat_id=chat.id,
    role="user",
    content="Add a login page with email and password fields"
)
db.add(message)
await db.commit()
```

**Add an assistant message with metadata**:
```python
message = Message(
    chat_id=chat.id,
    role="assistant",
    content="I've created a login page with email and password fields...",
    message_metadata={
        "agent_type": "StreamAgent",
        "model": "claude-sonnet-4",
        "iterations": 2,
        "files_modified": ["src/pages/Login.tsx", "src/App.tsx"],
        "commands_executed": []
    }
)
db.add(message)
await db.commit()
```

**Get last N messages**:
```python
result = await db.execute(
    select(Message)
    .where(Message.chat_id == chat_id)
    .order_by(Message.created_at.desc())
    .limit(20)
)
messages = result.scalars().all()
messages.reverse()  # Chronological order
```

### Notes

- Messages are immutable—they should not be edited after creation
- The `message_metadata` field enables rich UI features (showing tool calls, file changes)
- Message content is stored as plain text (Markdown supported in UI)
- Very long messages (>100k chars) may be truncated in the UI

---

## AgentCommandLog Model

The AgentCommandLog model provides a comprehensive audit trail of all shell commands executed by AI agents. This is critical for security, compliance, and debugging.

### Schema

```python
class AgentCommandLog(Base):
    __tablename__ = "agent_command_logs"

    # Identity
    id: UUID                    # Primary key
    user_id: UUID               # Foreign key to User
    project_id: UUID            # Foreign key to Project

    # Command details
    command: str                # Shell command executed
    working_dir: str            # Working directory (default: ".")

    # Execution result
    success: bool               # True if exit code 0
    exit_code: int              # Shell exit code
    stdout: str                 # Standard output
    stderr: str                 # Standard error
    duration_ms: int            # Execution duration in milliseconds

    # Risk assessment
    risk_level: str             # safe, moderate, high
    dry_run: bool               # True if command was simulated (not executed)

    # Timestamp
    created_at: datetime        # When command was executed
```

### Risk Levels

Commands are automatically classified by risk:

- **safe**: Read-only operations (ls, cat, grep, git status)
- **moderate**: Write operations with local scope (npm install, mkdir, echo >file)
- **high**: Destructive operations (rm -rf, git reset --hard, docker stop)

### Key Relationships

```python
user = relationship("User", back_populates="agent_commands")
project = relationship("Project", back_populates="agent_command_logs")
```

### Common Queries

**Log a command execution**:
```python
log = AgentCommandLog(
    user_id=user.id,
    project_id=project.id,
    command="npm install react-router-dom",
    working_dir="/app",
    success=True,
    exit_code=0,
    stdout="added 12 packages in 3.2s",
    stderr="",
    duration_ms=3200,
    risk_level="moderate",
    dry_run=False
)
db.add(log)
await db.commit()
```

**Get recent commands for a project**:
```python
result = await db.execute(
    select(AgentCommandLog)
    .where(AgentCommandLog.project_id == project.id)
    .order_by(AgentCommandLog.created_at.desc())
    .limit(50)
)
logs = result.scalars().all()
```

**Get failed commands**:
```python
result = await db.execute(
    select(AgentCommandLog)
    .where(AgentCommandLog.project_id == project.id)
    .where(AgentCommandLog.success == False)
    .order_by(AgentCommandLog.created_at.desc())
)
failed_logs = result.scalars().all()
```

**Get high-risk commands**:
```python
result = await db.execute(
    select(AgentCommandLog)
    .where(AgentCommandLog.user_id == user.id)
    .where(AgentCommandLog.risk_level == "high")
    .order_by(AgentCommandLog.created_at.desc())
)
high_risk_logs = result.scalars().all()
```

**Get command statistics**:
```python
from sqlalchemy import func

# Count commands by risk level
result = await db.execute(
    select(
        AgentCommandLog.risk_level,
        func.count(AgentCommandLog.id)
    )
    .where(AgentCommandLog.project_id == project.id)
    .group_by(AgentCommandLog.risk_level)
)
stats = result.all()
# [(safe, 142), (moderate, 38), (high, 3)]
```

### Notes

- Every shell command executed by an agent is logged (no exceptions)
- Logs are immutable—they cannot be edited or deleted by users
- The `dry_run` flag indicates commands that were simulated but not executed
- Long output (>10k chars) may be truncated to save space
- Logs are used for debugging agent behavior and security audits

---

## ShellSession Model

The ShellSession model tracks persistent terminal sessions created for WebSocket-based shell access. Each session represents a PTY (pseudo-terminal) connection to a container.

### Schema

```python
class ShellSession(Base):
    __tablename__ = "shell_sessions"

    # Identity
    id: UUID                    # Primary key
    session_id: str             # Unique session UUID (for WebSocket routing)
    user_id: UUID               # Foreign key to User
    project_id: UUID            # Foreign key to Project
    container_name: str         # Docker/K8s container name

    # Session configuration
    command: str                # Shell command (default: "/bin/bash")
    working_dir: str            # Initial working directory (default: "/app")
    terminal_rows: int          # Terminal size (default: 24)
    terminal_cols: int          # Terminal size (default: 80)

    # Lifecycle tracking
    status: str                 # initializing, active, idle, closed, failed
    created_at: datetime        # When session was created
    last_activity_at: datetime  # Last I/O activity
    closed_at: datetime         # When session was closed

    # Resource tracking
    bytes_read: int             # PTY output buffered
    bytes_written: int          # Client input sent to PTY
    total_reads: int            # Number of read requests
```

### Session Status

- **initializing**: Session is being created (container not ready yet)
- **active**: Session is open and receiving I/O
- **idle**: Session is open but no recent activity
- **closed**: Session has been closed normally
- **failed**: Session creation or execution failed

### Key Relationships

```python
user = relationship("User")
project = relationship("Project", back_populates="shell_sessions")
```

### Common Queries

**Create a shell session**:
```python
session = ShellSession(
    session_id=str(uuid.uuid4()),
    user_id=user.id,
    project_id=project.id,
    container_name=f"{project.slug}-frontend",
    command="/bin/bash",
    working_dir="/app",
    terminal_rows=24,
    terminal_cols=80,
    status="initializing"
)
db.add(session)
await db.commit()
await db.refresh(session)
```

**Get active sessions for a user**:
```python
result = await db.execute(
    select(ShellSession)
    .where(ShellSession.user_id == user.id)
    .where(ShellSession.status.in_(["initializing", "active", "idle"]))
    .order_by(ShellSession.last_activity_at.desc())
)
sessions = result.scalars().all()
```

**Update session activity**:
```python
session.status = "active"
session.last_activity_at = datetime.utcnow()
session.bytes_written += len(input_data)
await db.commit()
```

**Close a session**:
```python
session.status = "closed"
session.closed_at = datetime.utcnow()
await db.commit()
```

**Get idle sessions (for cleanup)**:
```python
from datetime import timedelta

idle_threshold = datetime.utcnow() - timedelta(minutes=30)

result = await db.execute(
    select(ShellSession)
    .where(ShellSession.status == "active")
    .where(ShellSession.last_activity_at < idle_threshold)
)
idle_sessions = result.scalars().all()

# Mark as idle or close
for session in idle_sessions:
    session.status = "idle"
await db.commit()
```

### Notes

- Shell sessions are ephemeral—they exist only while the container is running
- Sessions are automatically closed when the project is stopped or hibernated
- Resource tracking (`bytes_read`, `bytes_written`) helps monitor usage
- Idle sessions may be terminated automatically to free resources
- The `session_id` is used to route WebSocket messages to the correct PTY

---

## PodAccessLog Model

The PodAccessLog model provides security audit logging for Kubernetes pod access. It tracks all attempts to access user project pods, including both successful and failed attempts.

### Schema

```python
class PodAccessLog(Base):
    __tablename__ = "pod_access_logs"

    # Identity
    id: UUID                    # Primary key
    user_id: UUID               # Authenticated user making the request
    expected_user_id: UUID      # User ID extracted from URL/hostname
    project_id: UUID            # Project ID (if available from hostname)

    # Access result
    success: bool               # True if access granted, False if denied

    # Request details
    request_uri: str            # Original request URI
    request_host: str           # Request hostname
    ip_address: str             # Client IP address
    user_agent: str             # User agent string

    # Failure details
    failure_reason: str         # Reason for denial (if failed)

    # Timestamp
    created_at: datetime        # When access was attempted
```

### Key Relationships

```python
user = relationship("User")
```

### Common Queries

**Log a pod access attempt (success)**:
```python
log = PodAccessLog(
    user_id=user.id,
    expected_user_id=user.id,
    project_id=project.id,
    success=True,
    request_uri="/",
    request_host=f"{project.slug}.studio-test.tesslate.com",
    ip_address="192.168.1.100",
    user_agent="Mozilla/5.0 ..."
)
db.add(log)
await db.commit()
```

**Log a pod access attempt (denied)**:
```python
log = PodAccessLog(
    user_id=user.id,
    expected_user_id=other_user.id,  # Mismatch!
    project_id=project.id,
    success=False,
    request_uri="/",
    request_host=f"{project.slug}.studio-test.tesslate.com",
    ip_address="192.168.1.100",
    user_agent="Mozilla/5.0 ...",
    failure_reason="User ID mismatch: authenticated user does not own project"
)
db.add(log)
await db.commit()
```

**Get failed access attempts**:
```python
result = await db.execute(
    select(PodAccessLog)
    .where(PodAccessLog.success == False)
    .order_by(PodAccessLog.created_at.desc())
    .limit(100)
)
failed_attempts = result.scalars().all()
```

**Get access logs for a user**:
```python
result = await db.execute(
    select(PodAccessLog)
    .where(PodAccessLog.user_id == user.id)
    .order_by(PodAccessLog.created_at.desc())
)
logs = result.scalars().all()
```

**Detect suspicious access patterns**:
```python
# Multiple failed attempts from same IP
from sqlalchemy import func

result = await db.execute(
    select(
        PodAccessLog.ip_address,
        func.count(PodAccessLog.id).label("attempts")
    )
    .where(PodAccessLog.success == False)
    .where(PodAccessLog.created_at > datetime.utcnow() - timedelta(hours=1))
    .group_by(PodAccessLog.ip_address)
    .having(func.count(PodAccessLog.id) > 5)
)
suspicious_ips = result.all()
```

### Notes

- PodAccessLog is used for compliance and security monitoring
- All pod access attempts are logged, regardless of success
- Failed attempts may indicate malicious activity or misconfiguration
- Logs are immutable and cannot be deleted by users
- Used by NGINX auth_request subrequest to enforce access control

---

## Agent Execution Audit Trail

The chat and agent execution models work together to provide a complete audit trail:

### 1. User sends a message
```
User → Chat (project-scoped conversation)
     → Message (role: user, content: "Add a login page")
```

### 2. Agent processes the request
```
Agent (StreamAgent)
  ├─> Message (role: assistant, content: "I'll create a login page...")
  │   └─> message_metadata: {steps: [...], files_modified: [...]}
  │
  ├─> AgentCommandLog (command: "npm install bcrypt")
  │     └─> success: true, stdout: "added 5 packages"
  │
  └─> AgentCommandLog (command: "npm run build")
        └─> success: false, stderr: "Type error in Login.tsx"
```

### 3. User opens a terminal
```
User → ShellSession (WebSocket connection)
     → PTY in container
     → Commands executed manually (not logged in AgentCommandLog)
```

### 4. User accesses project URL
```
User → NGINX auth_request → Backend validates ownership
     → PodAccessLog (success: true)
     → Request forwarded to pod
```

### Example: Full Audit Trail

```python
# Get complete audit trail for a project
result = await db.execute(
    select(Chat)
    .options(
        selectinload(Chat.messages),
        selectinload(Chat.project).selectinload(Project.agent_command_logs),
        selectinload(Chat.project).selectinload(Project.shell_sessions)
    )
    .where(Chat.project_id == project.id)
)
chat = result.scalar_one_or_none()

# Now we have:
# - chat.messages: All user/assistant messages
# - chat.project.agent_command_logs: All agent-executed commands
# - chat.project.shell_sessions: All terminal sessions

# Reconstruct timeline
events = []

for message in chat.messages:
    events.append({
        "time": message.created_at,
        "type": "message",
        "role": message.role,
        "content": message.content[:100]
    })

for log in chat.project.agent_command_logs:
    events.append({
        "time": log.created_at,
        "type": "command",
        "command": log.command,
        "success": log.success
    })

for session in chat.project.shell_sessions:
    events.append({
        "time": session.created_at,
        "type": "shell_session",
        "status": session.status
    })

# Sort by time
events.sort(key=lambda e: e["time"])
```

---

## Summary

The chat and agent execution models provide comprehensive tracking of all AI agent interactions:

- **Chat**: Conversation threads scoped to projects
- **Message**: Individual user/assistant messages with execution metadata
- **AgentCommandLog**: Audit log of all shell commands executed by agents
- **ShellSession**: Persistent terminal sessions for WebSocket access
- **PodAccessLog**: Security audit log for Kubernetes pod access

Together, these models enable:
- Full conversation history and replay
- Detailed execution logging for debugging
- Security compliance and audit trails
- Resource usage tracking
- Anomaly detection (failed access attempts)

This comprehensive logging is essential for a production AI coding platform where security, reliability, and transparency are critical.
