[P1] Database Integration - Mostly Incomplete (remaining issues)

[frankbria]

• Multiple TODO comments for "Query database with filters"
• API endpoints return mock/placeholder data instead of real database queries
• Repository pattern exists but many core query methods are unimplemented
• Result: No real data persistence or retrieval

[traycerai]

Plan

Observations

The database integration in CodeFRAME has been successfully refactored and is largely complete with a well-implemented repository pattern. PR #216 has completed the high-priority P0 items:

1. ✅ Issue dependency parsing: depends_on column added to issues table with JSON parsing in get_issues_with_tasks()
2. ✅ Audit logging for PROJECT_UPDATED: Logs user_id, IP address, and updated fields metadata
3. ✅ Audit logging for PROJECT_DELETED: Logs user_id, IP address, and preserves project name before deletion

The remaining work focuses on completing P1 and P2 items that enhance observability and developer experience:

P1 - Medium Priority:

• IP address context: Pass IP from request context to audit logs (currently optional param with TODO)
• Tool metrics recording: Create tool_metrics table and record tool-specific execution metrics

P2 - Low Priority:

• Task-based file discovery: Implement auto-discovery of files modified by a task for linting
• Upload workspace extraction: Implement archive extraction in _init_from_upload()

Approach

The plan focuses on completing the identified P1/P2 TODOs to enhance observability and developer experience. The approach prioritizes:

1. Audit trail completeness: Implement IP address passing from request context to all audit logging calls
2. Observability: Add tool-specific metrics tracking in database
3. Developer experience: Improve task-file association for linting
4. Feature completion: Implement upload workspace extraction

This targeted approach addresses the remaining gaps without unnecessary refactoring of working code.

Implementation Steps

1. Implement IP Address Context in Audit Logs (P1)

Files:

• file:codeframe/ui/dependencies.py (add new dependency)
• file:codeframe/ui/routers/projects.py (update all endpoints)
• file:codeframe/persistence/repositories/project_repository.py (already has IP param)

Implementation approach:

Step 1a - Create IP extraction dependency:

• Add new function to file:codeframe/ui/dependencies.py:

  def get_client_ip(request: Request) -> str:
      """Extract client IP from request.
      
      Handles X-Forwarded-For header (for proxies) and direct client.host.
      """
      # Check for X-Forwarded-For header (proxy/load balancer)
      if "x-forwarded-for" in request.headers:
          return request.headers["x-forwarded-for"].split(",")[0].strip()
      # Fall back to direct client IP
      return request.client.host if request.client else "unknown"

Step 1b - Update projects router endpoints:

• Add get_client_ip dependency to all endpoints that call db.update_project() or db.delete_project()
• Currently affected endpoints:
  • create_project() - line 130: db.update_project() call
  • Any other endpoints that modify projects (search for all db.update_project() and db.delete_project() calls)

Step 1c - Pass IP to repository methods:

• Update db.update_project() calls to include ip_address=client_ip parameter
• Update db.delete_project() calls to include ip_address=client_ip parameter
• Update db.user_has_project_access() calls to include ip_address=client_ip parameter (lines 588, 612, 623)

Affected code locations:

• projects.py line 130: db.update_project() in create_project()
• project_repository.py lines 588, 612, 623: user_has_project_access() audit logging

2. Implement Tool-Specific Metrics in Database (P1)

Files:

• file:codeframe/persistence/schema_manager.py (add table schema)
• file:codeframe/persistence/repositories/activity_repository.py (add method)
• file:codeframe/lib/sdk_hooks.py (record metrics)

Implementation approach:

Step 2a - Add tool_metrics table to schema:

• Update file:codeframe/persistence/schema_manager.py to include:

  CREATE TABLE IF NOT EXISTS tool_metrics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tool_name TEXT NOT NULL,
      project_id INTEGER,
      agent_id TEXT,
      execution_time_ms REAL,
      success BOOLEAN NOT NULL,
      metadata TEXT,  -- JSON for tool-specific data
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
  );

Step 2b - Add repository method:

• Add method to file:codeframe/persistence/repositories/activity_repository.py:

  def record_tool_usage(
      self,
      tool_name: str,
      project_id: Optional[int],
      agent_id: Optional[str],
      execution_time_ms: float,
      success: bool,
      metadata: Optional[Dict[str, Any]] = None,
  ) -> int:
      """Record tool execution metrics."""
      cursor = self.conn.cursor()
      cursor.execute(
          """
          INSERT INTO tool_metrics 
          (tool_name, project_id, agent_id, execution_time_ms, success, metadata)
          VALUES (?, ?, ?, ?, ?, ?)
          """,
          (
              tool_name,
              project_id,
              agent_id,
              execution_time_ms,
              success,
              json.dumps(metadata) if metadata else None,
          ),
      )
      self.conn.commit()
      return cursor.lastrowid

Step 2c - Update sdk_hooks.py:

• Modify create_metrics_post_hook() function (line 186-253)
• Replace TODO comment at line 238 with actual implementation:

  if tool_name in ["Write", "Bash"]:
      # Record tool-specific metrics in database
      try:
          execution_time_ms = context.get("execution_time_ms", 0) if context else 0
          success = "error" not in str(tool_response).lower()
          
          # Get project_id and agent_id from context if available
          project_id = context.get("project_id") if context else None
          agent_id = context.get("agent_id") if context else None
          
          # Build metadata based on tool type
          metadata = {}
          if tool_name == "Write":
              metadata["file_path"] = tool_input.get("file_path", "")
          elif tool_name == "Bash":
              metadata["command"] = tool_input.get("command", "")[:100]  # Truncate for storage
          
          # Record in database
          db.activities.record_tool_usage(
              tool_name=tool_name,
              project_id=project_id,
              agent_id=agent_id,
              execution_time_ms=execution_time_ms,
              success=success,
              metadata=metadata,
          )
      except Exception as e:
          logger.warning(f"Failed to record tool metrics for {tool_name}: {e}")

3. Implement Task-Based File Discovery for Linting (P2)

File: file:codeframe/ui/routers/lint.py

Location: Line 198-204

Implementation approach:

Option 1 - Git Diff Approach (Recommended for immediate implementation):

• Query task's commit_sha from database
• Run git diff --name-only <commit_sha>^..<commit_sha> to get modified files
• Filter by project workspace path
• Return list of files for linting

Implementation:

# In lint.py, replace TODO with:
if task_id:
    # Get task details to find associated commit
    task = db.get_task(task_id)
    if task and task.get("commit_sha"):
        try:
            # Get modified files from git diff
            result = subprocess.run(
                ["git", "diff", "--name-only", f"{task['commit_sha']}^..{task['commit_sha']}"],
                cwd=project["workspace_path"],
                capture_output=True,
                text=True,
                timeout=5,
            )
            if result.returncode == 0:
                files = [f for f in result.stdout.strip().split("\n") if f]
                # Filter to only files in project
                project_root = Path(project["workspace_path"])
                files = [str(Path(f).relative_to(project_root)) for f in files if Path(f).exists()]
        except Exception as e:
            logger.warning(f"Failed to discover files from git diff: {e}")
            # Fall through to require explicit files list
    else:
        # Task has no commit_sha, require explicit files list
        if not files:
            raise HTTPException(
                status_code=400,
                detail="Task has no associated commit. Please provide explicit files list."
            )

Option 2 - Add files_modified Field (Long-term solution):

• Add files_modified TEXT column to tasks table (JSON array)
• Update task creation/completion to record modified files
• Query from database directly
• More reliable but requires schema change and task update logic

Recommended: Implement Option 1 immediately, plan Option 2 for future sprint

4. Implement Upload Workspace Initialization (P2)

File: file:codeframe/workspace/manager.py

Location: Line 247-255 in _init_from_upload() method

Implementation approach:

Step 4a - Add archive extraction logic:

def _init_from_upload(self, workspace_path: Path, upload_filename: str) -> None:
    """Extract from uploaded archive."""
    import zipfile
    import tarfile
    
    workspace_path.mkdir(parents=True, exist_ok=True)
    
    # Determine archive type from extension
    upload_path = Path(upload_filename)
    
    try:
        if upload_path.suffix.lower() == ".zip":
            # Extract zip archive
            with zipfile.ZipFile(upload_filename, 'r') as zip_ref:
                # Validate archive size (prevent zip bombs)
                total_size = sum(info.file_size for info in zip_ref.infolist())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in zip_ref.infolist():
                    # Prevent path traversal attacks
                    if member.filename.startswith("/") or ".." in member.filename:
                        logger.warning(f"Skipping suspicious path in archive: {member.filename}")
                        continue
                    zip_ref.extract(member, workspace_path)
        
        elif upload_path.suffix.lower() in [".gz", ".bz2"]:
            # Extract tar archive
            tar_mode = "r:gz" if upload_path.suffix.lower() == ".gz" else "r:bz2"
            with tarfile.open(upload_filename, tar_mode) as tar_ref:
                # Validate archive size
                total_size = sum(member.size for member in tar_ref.getmembers())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in tar_ref.getmembers():
                    if member.name.startswith("/") or ".." in member.name:
                        logger.warning(f"Skipping suspicious path in archive: {member.name}")
                        continue
                    tar_ref.extract(member, workspace_path)
        
        else:
            raise ValueError(f"Unsupported archive format: {upload_path.suffix}")
        
        # Initialize git repository after extraction
        subprocess.run(["git", "init"], cwd=workspace_path, check=True, capture_output=True)
        
        # Validate extracted content
        extracted_files = list(workspace_path.glob("**/*"))
        if not extracted_files:
            raise ValueError("Archive appears to be empty")
        
        logger.info(f"Successfully extracted archive to {workspace_path}")
        
    except (zipfile.BadZipFile, tarfile.ReadError) as e:
        logger.error(f"Failed to extract archive: {e}")
        raise ValueError(f"Invalid archive file: {e}")
    except Exception as e:
        logger.error(f"Failed to initialize workspace from upload: {e}")
        raise

Step 4b - Update workspace creation endpoint:

• Ensure upload endpoint passes upload_filename to _init_from_upload()
• Add proper error handling and cleanup on failure

5. Testing and Validation

For each change:

• Add unit tests in appropriate test file
• Test error cases (NULL values, invalid JSON, missing data)
• Verify backward compatibility
• Run existing test suite to ensure no regressions

Test files to update:

• file:tests/persistence/test_database.py - for repository changes
• file:tests/api/test_endpoints_database.py - for API changes
• file:tests/lib/test_sdk_hooks.py - for tool metrics
• Create new test file for upload functionality

6. Documentation Updates

Files to update:

• file:docs/architecture/database-repository-pattern.md - document new methods
• file:CHANGELOG.md - add entries for completed TODOs
• Add inline documentation for new methods
• Update API documentation if endpoints change

Sequence Diagram

sequenceDiagram
    participant Router as API Router
    participant Dep as Dependencies
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. IP Address Context
    Router->>Dep: get_client_ip(request)
    Dep-->>Router: client_ip
    Router->>Repo: update_project(id, updates, user_id, ip_address)
    Repo->>DB: UPDATE projects SET ...
    Repo->>Audit: log_project_event(PROJECT_UPDATED, ip_address)
    Audit->>DB: INSERT INTO audit_logs
    
    Note over Router,Audit: 2. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID

Loading

Priority Order

1. High Priority (P1 - Medium):

   • IP address context (audit trail completeness) - 1-2 days
   • Tool-specific metrics (observability) - 1-2 days

2. Low Priority (P2):

   • Task-based file discovery (developer convenience) - 1 day
   • Upload workspace initialization (feature completion) - 1 day

Rollout Strategy

1. Phase 1: Implement IP address context in audit logs (1-2 days)
2. Phase 2: Add tool-specific metrics recording (1-2 days)
3. Phase 3: Implement task-based file discovery (1 day)
4. Phase 4: Implement upload workspace extraction (1 day)
5. Phase 5: Testing and documentation (1-2 days)

Total estimated effort: 5-8 days

Import In IDE

🤖 Prompt for AI Agents

## Observations

The database integration in CodeFRAME has been **successfully refactored and is largely complete** with a well-implemented repository pattern. PR #216 has completed the high-priority P0 items:

1. ✅ **Issue dependency parsing**: `depends_on` column added to issues table with JSON parsing in `get_issues_with_tasks()`
2. ✅ **Audit logging for PROJECT_UPDATED**: Logs user_id, IP address, and updated fields metadata
3. ✅ **Audit logging for PROJECT_DELETED**: Logs user_id, IP address, and preserves project name before deletion

The remaining work focuses on **completing P1 and P2 items** that enhance observability and developer experience:

**P1 - Medium Priority**:
- IP address context: Pass IP from request context to audit logs (currently optional param with TODO)
- Tool metrics recording: Create `tool_metrics` table and record tool-specific execution metrics

**P2 - Low Priority**:
- Task-based file discovery: Implement auto-discovery of files modified by a task for linting
- Upload workspace extraction: Implement archive extraction in `_init_from_upload()`

## Approach

The plan focuses on **completing the identified P1/P2 TODOs** to enhance observability and developer experience. The approach prioritizes:

1. **Audit trail completeness**: Implement IP address passing from request context to all audit logging calls
2. **Observability**: Add tool-specific metrics tracking in database
3. **Developer experience**: Improve task-file association for linting
4. **Feature completion**: Implement upload workspace extraction

This targeted approach addresses the remaining gaps without unnecessary refactoring of working code.

## Implementation Steps

### 1. Implement IP Address Context in Audit Logs (P1)

**Files**: 
- `file:codeframe/ui/dependencies.py` (add new dependency)
- `file:codeframe/ui/routers/projects.py` (update all endpoints)
- `file:codeframe/persistence/repositories/project_repository.py` (already has IP param)

**Implementation approach**:

**Step 1a - Create IP extraction dependency**:
- Add new function to `file:codeframe/ui/dependencies.py`:
  ```python
  def get_client_ip(request: Request) -> str:
      """Extract client IP from request.
      
      Handles X-Forwarded-For header (for proxies) and direct client.host.
      """
      # Check for X-Forwarded-For header (proxy/load balancer)
      if "x-forwarded-for" in request.headers:
          return request.headers["x-forwarded-for"].split(",")[0].strip()
      # Fall back to direct client IP
      return request.client.host if request.client else "unknown"
  ```

**Step 1b - Update projects router endpoints**:
- Add `get_client_ip` dependency to all endpoints that call `db.update_project()` or `db.delete_project()`
- Currently affected endpoints:
  - `create_project()` - line 130: `db.update_project()` call
  - Any other endpoints that modify projects (search for all `db.update_project()` and `db.delete_project()` calls)

**Step 1c - Pass IP to repository methods**:
- Update `db.update_project()` calls to include `ip_address=client_ip` parameter
- Update `db.delete_project()` calls to include `ip_address=client_ip` parameter
- Update `db.user_has_project_access()` calls to include `ip_address=client_ip` parameter (lines 588, 612, 623)

**Affected code locations**:
- `projects.py` line 130: `db.update_project()` in `create_project()`
- `project_repository.py` lines 588, 612, 623: `user_has_project_access()` audit logging

### 2. Implement Tool-Specific Metrics in Database (P1)

**Files**: 
- `file:codeframe/persistence/schema_manager.py` (add table schema)
- `file:codeframe/persistence/repositories/activity_repository.py` (add method)
- `file:codeframe/lib/sdk_hooks.py` (record metrics)

**Implementation approach**:

**Step 2a - Add tool_metrics table to schema**:
- Update `file:codeframe/persistence/schema_manager.py` to include:
  ```sql
  CREATE TABLE IF NOT EXISTS tool_metrics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tool_name TEXT NOT NULL,
      project_id INTEGER,
      agent_id TEXT,
      execution_time_ms REAL,
      success BOOLEAN NOT NULL,
      metadata TEXT,  -- JSON for tool-specific data
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
  );
  ```

**Step 2b - Add repository method**:
- Add method to `file:codeframe/persistence/repositories/activity_repository.py`:
  ```python
  def record_tool_usage(
      self,
      tool_name: str,
      project_id: Optional[int],
      agent_id: Optional[str],
      execution_time_ms: float,
      success: bool,
      metadata: Optional[Dict[str, Any]] = None,
  ) -> int:
      """Record tool execution metrics."""
      cursor = self.conn.cursor()
      cursor.execute(
          """
          INSERT INTO tool_metrics 
          (tool_name, project_id, agent_id, execution_time_ms, success, metadata)
          VALUES (?, ?, ?, ?, ?, ?)
          """,
          (
              tool_name,
              project_id,
              agent_id,
              execution_time_ms,
              success,
              json.dumps(metadata) if metadata else None,
          ),
      )
      self.conn.commit()
      return cursor.lastrowid
  ```

**Step 2c - Update sdk_hooks.py**:
- Modify `create_metrics_post_hook()` function (line 186-253)
- Replace TODO comment at line 238 with actual implementation:
  ```python
  if tool_name in ["Write", "Bash"]:
      # Record tool-specific metrics in database
      try:
          execution_time_ms = context.get("execution_time_ms", 0) if context else 0
          success = "error" not in str(tool_response).lower()
          
          # Get project_id and agent_id from context if available
          project_id = context.get("project_id") if context else None
          agent_id = context.get("agent_id") if context else None
          
          # Build metadata based on tool type
          metadata = {}
          if tool_name == "Write":
              metadata["file_path"] = tool_input.get("file_path", "")
          elif tool_name == "Bash":
              metadata["command"] = tool_input.get("command", "")[:100]  # Truncate for storage
          
          # Record in database
          db.activities.record_tool_usage(
              tool_name=tool_name,
              project_id=project_id,
              agent_id=agent_id,
              execution_time_ms=execution_time_ms,
              success=success,
              metadata=metadata,
          )
      except Exception as e:
          logger.warning(f"Failed to record tool metrics for {tool_name}: {e}")
  ```

### 3. Implement Task-Based File Discovery for Linting (P2)

**File**: `file:codeframe/ui/routers/lint.py`

**Location**: Line 198-204

**Implementation approach**:

**Option 1 - Git Diff Approach (Recommended for immediate implementation)**:
- Query task's `commit_sha` from database
- Run `git diff --name-only <commit_sha>^..<commit_sha>` to get modified files
- Filter by project workspace path
- Return list of files for linting

**Implementation**:
```python
# In lint.py, replace TODO with:
if task_id:
    # Get task details to find associated commit
    task = db.get_task(task_id)
    if task and task.get("commit_sha"):
        try:
            # Get modified files from git diff
            result = subprocess.run(
                ["git", "diff", "--name-only", f"{task['commit_sha']}^..{task['commit_sha']}"],
                cwd=project["workspace_path"],
                capture_output=True,
                text=True,
                timeout=5,
            )
            if result.returncode == 0:
                files = [f for f in result.stdout.strip().split("\n") if f]
                # Filter to only files in project
                project_root = Path(project["workspace_path"])
                files = [str(Path(f).relative_to(project_root)) for f in files if Path(f).exists()]
        except Exception as e:
            logger.warning(f"Failed to discover files from git diff: {e}")
            # Fall through to require explicit files list
    else:
        # Task has no commit_sha, require explicit files list
        if not files:
            raise HTTPException(
                status_code=400,
                detail="Task has no associated commit. Please provide explicit files list."
            )
```

**Option 2 - Add files_modified Field (Long-term solution)**:
- Add `files_modified` TEXT column to `tasks` table (JSON array)
- Update task creation/completion to record modified files
- Query from database directly
- More reliable but requires schema change and task update logic

**Recommended**: Implement Option 1 immediately, plan Option 2 for future sprint

### 4. Implement Upload Workspace Initialization (P2)

**File**: `file:codeframe/workspace/manager.py`

**Location**: Line 247-255 in `_init_from_upload()` method

**Implementation approach**:

**Step 4a - Add archive extraction logic**:
```python
def _init_from_upload(self, workspace_path: Path, upload_filename: str) -> None:
    """Extract from uploaded archive."""
    import zipfile
    import tarfile
    
    workspace_path.mkdir(parents=True, exist_ok=True)
    
    # Determine archive type from extension
    upload_path = Path(upload_filename)
    
    try:
        if upload_path.suffix.lower() == ".zip":
            # Extract zip archive
            with zipfile.ZipFile(upload_filename, 'r') as zip_ref:
                # Validate archive size (prevent zip bombs)
                total_size = sum(info.file_size for info in zip_ref.infolist())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in zip_ref.infolist():
                    # Prevent path traversal attacks
                    if member.filename.startswith("/") or ".." in member.filename:
                        logger.warning(f"Skipping suspicious path in archive: {member.filename}")
                        continue
                    zip_ref.extract(member, workspace_path)
        
        elif upload_path.suffix.lower() in [".gz", ".bz2"]:
            # Extract tar archive
            tar_mode = "r:gz" if upload_path.suffix.lower() == ".gz" else "r:bz2"
            with tarfile.open(upload_filename, tar_mode) as tar_ref:
                # Validate archive size
                total_size = sum(member.size for member in tar_ref.getmembers())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in tar_ref.getmembers():
                    if member.name.startswith("/") or ".." in member.name:
                        logger.warning(f"Skipping suspicious path in archive: {member.name}")
                        continue
                    tar_ref.extract(member, workspace_path)
        
        else:
            raise ValueError(f"Unsupported archive format: {upload_path.suffix}")
        
        # Initialize git repository after extraction
        subprocess.run(["git", "init"], cwd=workspace_path, check=True, capture_output=True)
        
        # Validate extracted content
        extracted_files = list(workspace_path.glob("**/*"))
        if not extracted_files:
            raise ValueError("Archive appears to be empty")
        
        logger.info(f"Successfully extracted archive to {workspace_path}")
        
    except (zipfile.BadZipFile, tarfile.ReadError) as e:
        logger.error(f"Failed to extract archive: {e}")
        raise ValueError(f"Invalid archive file: {e}")
    except Exception as e:
        logger.error(f"Failed to initialize workspace from upload: {e}")
        raise
```

**Step 4b - Update workspace creation endpoint**:
- Ensure upload endpoint passes `upload_filename` to `_init_from_upload()`
- Add proper error handling and cleanup on failure

### 5. Testing and Validation

**For each change**:
- Add unit tests in appropriate test file
- Test error cases (NULL values, invalid JSON, missing data)
- Verify backward compatibility
- Run existing test suite to ensure no regressions

**Test files to update**:
- `file:tests/persistence/test_database.py` - for repository changes
- `file:tests/api/test_endpoints_database.py` - for API changes
- `file:tests/lib/test_sdk_hooks.py` - for tool metrics
- Create new test file for upload functionality

### 6. Documentation Updates

**Files to update**:
- `file:docs/architecture/database-repository-pattern.md` - document new methods
- `file:CHANGELOG.md` - add entries for completed TODOs
- Add inline documentation for new methods
- Update API documentation if endpoints change

## Sequence Diagram

```mermaid
sequenceDiagram
    participant Router as API Router
    participant Dep as Dependencies
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. IP Address Context
    Router->>Dep: get_client_ip(request)
    Dep-->>Router: client_ip
    Router->>Repo: update_project(id, updates, user_id, ip_address)
    Repo->>DB: UPDATE projects SET ...
    Repo->>Audit: log_project_event(PROJECT_UPDATED, ip_address)
    Audit->>DB: INSERT INTO audit_logs
    
    Note over Router,Audit: 2. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID
```

## Priority Order

1. **High Priority** (P1 - Medium):
   - IP address context (audit trail completeness) - 1-2 days
   - Tool-specific metrics (observability) - 1-2 days

2. **Low Priority** (P2):
   - Task-based file discovery (developer convenience) - 1 day
   - Upload workspace initialization (feature completion) - 1 day

## Rollout Strategy

1. **Phase 1**: Implement IP address context in audit logs (1-2 days)
2. **Phase 2**: Add tool-specific metrics recording (1-2 days)
3. **Phase 3**: Implement task-based file discovery (1 day)
4. **Phase 4**: Implement upload workspace extraction (1 day)
5. **Phase 5**: Testing and documentation (1-2 days)

Total estimated effort: 5-8 days

---

Execution Information

Branch: main
Commit: 6aa19a3

---

💡 Tips

Supported Commands (Inside Comments)

• Use @traycerai generate to iterate on the previous version of the implementation plan.

Supported Commands (Inside Description)

• Add @traycerai ignore anywhere in the ticket description to prevent this ticket from being processed.
• Add @traycerai branch:<branch-name> anywhere in the ticket description to specify the target branch for the implementation plan.

Community

• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

---

⚠️ Possible Duplicate Issue

• [P1] Replace mock data with database queries in GET /api/projects/{id}/tasks #104

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[frankbria]

P0 Items Completed ✅

PR #216 implements the high-priority database TODOs:

• Issue dependency parsing: Added depends_on column to issues table with JSON parsing in get_issues_with_tasks()
• Audit logging for PROJECT_UPDATED: Logs user_id, IP address, and updated fields metadata
• Audit logging for PROJECT_DELETED: Logs user_id, IP address, and preserves project name before deletion

All 16 new tests + 72 existing database tests pass.

---

Remaining P1/P2 Items

P1 - Medium Priority

Item	File	Description
IP address context	project_repository.py	Pass IP from request context to audit logs (currently optional param)
Tool metrics recording	sdk_hooks.py:238	Create tool_metrics table and record tool-specific execution metrics

P2 - Low Priority

Item	File	Description
Task-based file discovery	lint.py:194-204	Implement auto-discovery of files modified by a task for linting
Upload workspace extraction	workspace/manager.py:247	Implement archive extraction in _init_from_upload()

These can be addressed in future PRs as needed.

[traycerai]

I have updated the Implementation Plan to reflect the recent changes in the ticket.

Previous Plan

Observations

The database integration in CodeFRAME has been refactored and is largely complete with a well-implemented repository pattern. The issue #207 appears to reference an outdated state. However, there are specific minor TODOs that need completion:

1. Issue dependency parsing: depends_on field returns empty array instead of parsing from database
2. Audit logging gaps: Missing audit events for PROJECT_UPDATED and PROJECT_DELETED
3. Request context: IP addresses not passed from request context to audit logs
4. Tool metrics: SDK hooks don't record tool-specific metrics in database
5. Task-file mapping: Lint router lacks task-based file discovery

The repository pattern is properly implemented with real SQL queries, proper filtering, and comprehensive CRUD operations across 17 domain-specific repositories.

Approach

The plan focuses on completing the identified TODOs rather than reimplementing database integration (which is already functional). The approach prioritizes:

1. Data completeness: Implement missing field parsing (depends_on)
2. Audit trail: Complete audit logging for critical operations
3. Observability: Add tool-specific metrics tracking
4. Developer experience: Improve task-file association for linting

This targeted approach addresses the actual gaps without unnecessary refactoring of working code.

Implementation Steps

1. Implement Issue Dependency Parsing

File: file:codeframe/persistence/repositories/issue_repository.py

Location: Line 169 in get_issues_with_tasks() method

Changes:

• Add depends_on column to issues table schema if not present
• Parse depends_on JSON field from database instead of returning empty array
• Handle NULL values and invalid JSON gracefully
• Update the field assignment from "depends_on": [] to parse actual database value

Schema verification needed:

• Check file:codeframe/persistence/schema_manager.py for issues table definition
• Verify if depends_on column exists (likely as TEXT/JSON type)
• If missing, add migration to create column

Implementation details:

In get_issues_with_tasks() method:
- After fetching issue_dict, parse depends_on field
- Use json.loads() with try-except for error handling
- Validate it's a list, default to [] if invalid
- Similar pattern to task depends_on parsing (lines 195-205)

2. Complete Audit Logging for Project Operations

File: file:codeframe/persistence/repositories/project_repository.py

Locations:

• Line 198: update_project() method
• Line 249: delete_project() method

Changes for update_project():

• Add user_id parameter to method signature
• Update all callers in file:codeframe/ui/routers/projects.py to pass current_user.id
• Add audit log call using AuditLogger.log_project_event() with AuditEventType.PROJECT_UPDATED
• Include updated fields in metadata

Changes for delete_project():

• Add user_id parameter to method signature
• Update all callers to pass current_user.id
• Add audit log call with AuditEventType.PROJECT_DELETED
• Include project name in metadata before deletion

Caller updates needed:

• Search for all db.update_project() calls in routers
• Search for all db.delete_project() calls in routers
• Add user_id=current_user.id parameter to each call

3. Add IP Address Context to Audit Logs

Files:

• file:codeframe/persistence/repositories/project_repository.py (lines 128, 538, 562, 573)
• file:codeframe/auth/dependencies.py (or create new middleware)

Implementation approach:

Option A - Request Context (Recommended):

• Create FastAPI dependency to extract IP from request
• Add to file:codeframe/ui/dependencies.py:

  def get_client_ip(request: Request) -> str:
      return request.client.host
  

• Update audit logging calls to accept IP parameter
• Pass IP from router endpoints to repository methods

Option B - Context Variable:

• Use contextvars to store request IP globally
• Set in middleware, access in repositories
• More implicit but avoids parameter threading

Affected methods:

• create_project() - line 128
• user_has_project_access() - lines 538, 562, 573

4. Implement Tool-Specific Metrics in Database

File: file:codeframe/lib/sdk_hooks.py

Location: Line 242 in _post_tool_execution() method

Changes:

• Create new table tool_metrics in schema if not exists:
  • Columns: id, tool_name, project_id, agent_id, execution_time, success, timestamp, metadata
• Add method to file:codeframe/persistence/repositories/activity_repository.py:
  • record_tool_usage(tool_name, project_id, agent_id, execution_time, success, metadata)
• Update _post_tool_execution() to call repository method
• Track Write and Bash tools initially, expand to others later

Schema addition:

CREATE TABLE IF NOT EXISTS tool_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tool_name TEXT NOT NULL,
    project_id INTEGER,
    agent_id TEXT,
    execution_time_ms REAL,
    success BOOLEAN NOT NULL,
    metadata TEXT,  -- JSON
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

5. Implement Task-Based File Discovery for Linting

File: file:codeframe/ui/routers/lint.py

Location: Line 198-204

Implementation options:

Option 1 - Git Diff Approach:

• Query task's commit_sha from database
• Run git diff --name-only <commit_sha>^..<commit_sha> to get modified files
• Filter by project workspace path
• Return list of files for linting

Option 2 - Add files_modified Field:

• Add files_modified TEXT column to tasks table (JSON array)
• Update task creation/completion to record modified files
• Query from database directly
• More reliable but requires schema change

Option 3 - Changelog Tracking:

• Query changelog table for entries related to task_id
• Extract file paths from changelog entries
• Aggregate unique files

Recommended: Option 1 (Git Diff) for immediate implementation, Option 2 for long-term solution

6. Implement Upload Workspace Initialization

File: file:codeframe/workspace/manager.py

Location: Line 251 in _init_from_upload() method

Changes:

• Accept upload_filename parameter (path to uploaded archive)
• Detect archive type (zip, tar.gz, tar.bz2) from extension
• Extract archive to workspace_path using appropriate library:
  • zipfile for .zip
  • tarfile for .tar.gz, .tar.bz2
• Initialize git repository after extraction
• Validate extracted content (check for common project files)
• Handle extraction errors gracefully

Security considerations:

• Validate archive size before extraction (prevent zip bombs)
• Sanitize file paths during extraction (prevent path traversal)
• Limit extraction to workspace directory
• Set appropriate file permissions

7. Testing and Validation

For each change:

• Add unit tests in appropriate test file
• Test error cases (NULL values, invalid JSON, missing data)
• Verify backward compatibility
• Run existing test suite to ensure no regressions

Test files to update:

• file:tests/persistence/test_database.py - for repository changes
• file:tests/api/test_endpoints_database.py - for API changes
• file:tests/lib/test_sdk_hooks.py - for tool metrics
• Create new test file for upload functionality

8. Documentation Updates

Files to update:

• file:docs/architecture/database-repository-pattern.md - document new methods
• file:CHANGELOG.md - add entries for completed TODOs
• Add inline documentation for new methods
• Update API documentation if endpoints change

Sequence Diagram

sequenceDiagram
    participant Router as API Router
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. Issue Dependency Parsing
    Router->>Repo: get_issues_with_tasks(project_id)
    Repo->>DB: SELECT * FROM issues WHERE project_id=?
    DB-->>Repo: issue rows with depends_on JSON
    Repo->>Repo: Parse depends_on JSON field
    Repo-->>Router: Issues with parsed dependencies
    
    Note over Router,Audit: 2. Audit Logging
    Router->>Repo: update_project(id, updates, user_id, ip)
    Repo->>DB: UPDATE projects SET ... WHERE id=?
    Repo->>Audit: log_project_event(PROJECT_UPDATED)
    Audit->>DB: INSERT INTO audit_logs
    Repo-->>Router: Success
    
    Note over Router,Audit: 3. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID

Loading

Priority Order

1. High Priority (P0 - Blocker):

   • Issue dependency parsing (affects data completeness)
   • Audit logging for project operations (security/compliance)

2. Medium Priority (P1):

   • IP address context (audit trail completeness)
   • Tool-specific metrics (observability)

3. Low Priority (P2):

   • Task-based file discovery (developer convenience)
   • Upload workspace initialization (feature completion)

Rollout Strategy

1. Phase 1: Complete dependency parsing and audit logging (1-2 days)
2. Phase 2: Add IP context and tool metrics (1-2 days)
3. Phase 3: Implement file discovery and upload (2-3 days)
4. Phase 4: Testing and documentation (1-2 days)

Total estimated effort: 5-9 days

Import In IDE

🤖 Prompt for AI Agents

## Observations

The database integration in CodeFRAME has been **refactored and is largely complete** with a well-implemented repository pattern. The issue #207 appears to reference an outdated state. However, there are **specific minor TODOs** that need completion:

1. **Issue dependency parsing**: `depends_on` field returns empty array instead of parsing from database
2. **Audit logging gaps**: Missing audit events for PROJECT_UPDATED and PROJECT_DELETED
3. **Request context**: IP addresses not passed from request context to audit logs
4. **Tool metrics**: SDK hooks don't record tool-specific metrics in database
5. **Task-file mapping**: Lint router lacks task-based file discovery

The repository pattern is properly implemented with real SQL queries, proper filtering, and comprehensive CRUD operations across 17 domain-specific repositories.

## Approach

The plan focuses on **completing the identified TODOs** rather than reimplementing database integration (which is already functional). The approach prioritizes:

1. **Data completeness**: Implement missing field parsing (depends_on)
2. **Audit trail**: Complete audit logging for critical operations
3. **Observability**: Add tool-specific metrics tracking
4. **Developer experience**: Improve task-file association for linting

This targeted approach addresses the actual gaps without unnecessary refactoring of working code.

## Implementation Steps

### 1. Implement Issue Dependency Parsing

**File**: `file:codeframe/persistence/repositories/issue_repository.py`

**Location**: Line 169 in `get_issues_with_tasks()` method

**Changes**:
- Add `depends_on` column to `issues` table schema if not present
- Parse `depends_on` JSON field from database instead of returning empty array
- Handle NULL values and invalid JSON gracefully
- Update the field assignment from `"depends_on": []` to parse actual database value

**Schema verification needed**:
- Check `file:codeframe/persistence/schema_manager.py` for `issues` table definition
- Verify if `depends_on` column exists (likely as TEXT/JSON type)
- If missing, add migration to create column

**Implementation details**:
```
In get_issues_with_tasks() method:
- After fetching issue_dict, parse depends_on field
- Use json.loads() with try-except for error handling
- Validate it's a list, default to [] if invalid
- Similar pattern to task depends_on parsing (lines 195-205)
```

### 2. Complete Audit Logging for Project Operations

**File**: `file:codeframe/persistence/repositories/project_repository.py`

**Locations**: 
- Line 198: `update_project()` method
- Line 249: `delete_project()` method

**Changes for `update_project()`**:
- Add `user_id` parameter to method signature
- Update all callers in `file:codeframe/ui/routers/projects.py` to pass `current_user.id`
- Add audit log call using `AuditLogger.log_project_event()` with `AuditEventType.PROJECT_UPDATED`
- Include updated fields in metadata

**Changes for `delete_project()`**:
- Add `user_id` parameter to method signature
- Update all callers to pass `current_user.id`
- Add audit log call with `AuditEventType.PROJECT_DELETED`
- Include project name in metadata before deletion

**Caller updates needed**:
- Search for all `db.update_project()` calls in routers
- Search for all `db.delete_project()` calls in routers
- Add `user_id=current_user.id` parameter to each call

### 3. Add IP Address Context to Audit Logs

**Files**: 
- `file:codeframe/persistence/repositories/project_repository.py` (lines 128, 538, 562, 573)
- `file:codeframe/auth/dependencies.py` (or create new middleware)

**Implementation approach**:

**Option A - Request Context (Recommended)**:
- Create FastAPI dependency to extract IP from request
- Add to `file:codeframe/ui/dependencies.py`:
  ```
  def get_client_ip(request: Request) -> str:
      return request.client.host
  ```
- Update audit logging calls to accept IP parameter
- Pass IP from router endpoints to repository methods

**Option B - Context Variable**:
- Use `contextvars` to store request IP globally
- Set in middleware, access in repositories
- More implicit but avoids parameter threading

**Affected methods**:
- `create_project()` - line 128
- `user_has_project_access()` - lines 538, 562, 573

### 4. Implement Tool-Specific Metrics in Database

**File**: `file:codeframe/lib/sdk_hooks.py`

**Location**: Line 242 in `_post_tool_execution()` method

**Changes**:
- Create new table `tool_metrics` in schema if not exists:
  - Columns: id, tool_name, project_id, agent_id, execution_time, success, timestamp, metadata
- Add method to `file:codeframe/persistence/repositories/activity_repository.py`:
  - `record_tool_usage(tool_name, project_id, agent_id, execution_time, success, metadata)`
- Update `_post_tool_execution()` to call repository method
- Track Write and Bash tools initially, expand to others later

**Schema addition**:
```sql
CREATE TABLE IF NOT EXISTS tool_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tool_name TEXT NOT NULL,
    project_id INTEGER,
    agent_id TEXT,
    execution_time_ms REAL,
    success BOOLEAN NOT NULL,
    metadata TEXT,  -- JSON
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
```

### 5. Implement Task-Based File Discovery for Linting

**File**: `file:codeframe/ui/routers/lint.py`

**Location**: Line 198-204

**Implementation options**:

**Option 1 - Git Diff Approach**:
- Query task's `commit_sha` from database
- Run `git diff --name-only <commit_sha>^..<commit_sha>` to get modified files
- Filter by project workspace path
- Return list of files for linting

**Option 2 - Add files_modified Field**:
- Add `files_modified` TEXT column to `tasks` table (JSON array)
- Update task creation/completion to record modified files
- Query from database directly
- More reliable but requires schema change

**Option 3 - Changelog Tracking**:
- Query `changelog` table for entries related to task_id
- Extract file paths from changelog entries
- Aggregate unique files

**Recommended**: Option 1 (Git Diff) for immediate implementation, Option 2 for long-term solution

### 6. Implement Upload Workspace Initialization

**File**: `file:codeframe/workspace/manager.py`

**Location**: Line 251 in `_init_from_upload()` method

**Changes**:
- Accept `upload_filename` parameter (path to uploaded archive)
- Detect archive type (zip, tar.gz, tar.bz2) from extension
- Extract archive to workspace_path using appropriate library:
  - `zipfile` for .zip
  - `tarfile` for .tar.gz, .tar.bz2
- Initialize git repository after extraction
- Validate extracted content (check for common project files)
- Handle extraction errors gracefully

**Security considerations**:
- Validate archive size before extraction (prevent zip bombs)
- Sanitize file paths during extraction (prevent path traversal)
- Limit extraction to workspace directory
- Set appropriate file permissions

### 7. Testing and Validation

**For each change**:
- Add unit tests in appropriate test file
- Test error cases (NULL values, invalid JSON, missing data)
- Verify backward compatibility
- Run existing test suite to ensure no regressions

**Test files to update**:
- `file:tests/persistence/test_database.py` - for repository changes
- `file:tests/api/test_endpoints_database.py` - for API changes
- `file:tests/lib/test_sdk_hooks.py` - for tool metrics
- Create new test file for upload functionality

### 8. Documentation Updates

**Files to update**:
- `file:docs/architecture/database-repository-pattern.md` - document new methods
- `file:CHANGELOG.md` - add entries for completed TODOs
- Add inline documentation for new methods
- Update API documentation if endpoints change

## Sequence Diagram

```mermaid
sequenceDiagram
    participant Router as API Router
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. Issue Dependency Parsing
    Router->>Repo: get_issues_with_tasks(project_id)
    Repo->>DB: SELECT * FROM issues WHERE project_id=?
    DB-->>Repo: issue rows with depends_on JSON
    Repo->>Repo: Parse depends_on JSON field
    Repo-->>Router: Issues with parsed dependencies
    
    Note over Router,Audit: 2. Audit Logging
    Router->>Repo: update_project(id, updates, user_id, ip)
    Repo->>DB: UPDATE projects SET ... WHERE id=?
    Repo->>Audit: log_project_event(PROJECT_UPDATED)
    Audit->>DB: INSERT INTO audit_logs
    Repo-->>Router: Success
    
    Note over Router,Audit: 3. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID
```

## Priority Order

1. **High Priority** (P0 - Blocker):
   - Issue dependency parsing (affects data completeness)
   - Audit logging for project operations (security/compliance)

2. **Medium Priority** (P1):
   - IP address context (audit trail completeness)
   - Tool-specific metrics (observability)

3. **Low Priority** (P2):
   - Task-based file discovery (developer convenience)
   - Upload workspace initialization (feature completion)

## Rollout Strategy

1. **Phase 1**: Complete dependency parsing and audit logging (1-2 days)
2. **Phase 2**: Add IP context and tool metrics (1-2 days)
3. **Phase 3**: Implement file discovery and upload (2-3 days)
4. **Phase 4**: Testing and documentation (1-2 days)

Total estimated effort: 5-9 days

---

Execution Information

Branch: main
Commit: c863ec5

New Plan

Observations

The database integration in CodeFRAME has been successfully refactored and is largely complete with a well-implemented repository pattern. PR #216 has completed the high-priority P0 items:

1. ✅ Issue dependency parsing: depends_on column added to issues table with JSON parsing in get_issues_with_tasks()
2. ✅ Audit logging for PROJECT_UPDATED: Logs user_id, IP address, and updated fields metadata
3. ✅ Audit logging for PROJECT_DELETED: Logs user_id, IP address, and preserves project name before deletion

The remaining work focuses on completing P1 and P2 items that enhance observability and developer experience:

P1 - Medium Priority:

• IP address context: Pass IP from request context to audit logs (currently optional param with TODO)
• Tool metrics recording: Create tool_metrics table and record tool-specific execution metrics

P2 - Low Priority:

• Task-based file discovery: Implement auto-discovery of files modified by a task for linting
• Upload workspace extraction: Implement archive extraction in _init_from_upload()

Approach

The plan focuses on completing the identified P1/P2 TODOs to enhance observability and developer experience. The approach prioritizes:

1. Audit trail completeness: Implement IP address passing from request context to all audit logging calls
2. Observability: Add tool-specific metrics tracking in database
3. Developer experience: Improve task-file association for linting
4. Feature completion: Implement upload workspace extraction

This targeted approach addresses the remaining gaps without unnecessary refactoring of working code.

Implementation Steps

1. Implement IP Address Context in Audit Logs (P1)

Files:

• file:codeframe/ui/dependencies.py (add new dependency)
• file:codeframe/ui/routers/projects.py (update all endpoints)
• file:codeframe/persistence/repositories/project_repository.py (already has IP param)

Implementation approach:

Step 1a - Create IP extraction dependency:

• Add new function to file:codeframe/ui/dependencies.py:

  def get_client_ip(request: Request) -> str:
      """Extract client IP from request.
      
      Handles X-Forwarded-For header (for proxies) and direct client.host.
      """
      # Check for X-Forwarded-For header (proxy/load balancer)
      if "x-forwarded-for" in request.headers:
          return request.headers["x-forwarded-for"].split(",")[0].strip()
      # Fall back to direct client IP
      return request.client.host if request.client else "unknown"

Step 1b - Update projects router endpoints:

• Add get_client_ip dependency to all endpoints that call db.update_project() or db.delete_project()
• Currently affected endpoints:
  • create_project() - line 130: db.update_project() call
  • Any other endpoints that modify projects (search for all db.update_project() and db.delete_project() calls)

Step 1c - Pass IP to repository methods:

• Update db.update_project() calls to include ip_address=client_ip parameter
• Update db.delete_project() calls to include ip_address=client_ip parameter
• Update db.user_has_project_access() calls to include ip_address=client_ip parameter (lines 588, 612, 623)

Affected code locations:

• projects.py line 130: db.update_project() in create_project()
• project_repository.py lines 588, 612, 623: user_has_project_access() audit logging

2. Implement Tool-Specific Metrics in Database (P1)

Files:

• file:codeframe/persistence/schema_manager.py (add table schema)
• file:codeframe/persistence/repositories/activity_repository.py (add method)
• file:codeframe/lib/sdk_hooks.py (record metrics)

Implementation approach:

Step 2a - Add tool_metrics table to schema:

• Update file:codeframe/persistence/schema_manager.py to include:

  CREATE TABLE IF NOT EXISTS tool_metrics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tool_name TEXT NOT NULL,
      project_id INTEGER,
      agent_id TEXT,
      execution_time_ms REAL,
      success BOOLEAN NOT NULL,
      metadata TEXT,  -- JSON for tool-specific data
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
  );

Step 2b - Add repository method:

• Add method to file:codeframe/persistence/repositories/activity_repository.py:

  def record_tool_usage(
      self,
      tool_name: str,
      project_id: Optional[int],
      agent_id: Optional[str],
      execution_time_ms: float,
      success: bool,
      metadata: Optional[Dict[str, Any]] = None,
  ) -> int:
      """Record tool execution metrics."""
      cursor = self.conn.cursor()
      cursor.execute(
          """
          INSERT INTO tool_metrics 
          (tool_name, project_id, agent_id, execution_time_ms, success, metadata)
          VALUES (?, ?, ?, ?, ?, ?)
          """,
          (
              tool_name,
              project_id,
              agent_id,
              execution_time_ms,
              success,
              json.dumps(metadata) if metadata else None,
          ),
      )
      self.conn.commit()
      return cursor.lastrowid

Step 2c - Update sdk_hooks.py:

• Modify create_metrics_post_hook() function (line 186-253)
• Replace TODO comment at line 238 with actual implementation:

  if tool_name in ["Write", "Bash"]:
      # Record tool-specific metrics in database
      try:
          execution_time_ms = context.get("execution_time_ms", 0) if context else 0
          success = "error" not in str(tool_response).lower()
          
          # Get project_id and agent_id from context if available
          project_id = context.get("project_id") if context else None
          agent_id = context.get("agent_id") if context else None
          
          # Build metadata based on tool type
          metadata = {}
          if tool_name == "Write":
              metadata["file_path"] = tool_input.get("file_path", "")
          elif tool_name == "Bash":
              metadata["command"] = tool_input.get("command", "")[:100]  # Truncate for storage
          
          # Record in database
          db.activities.record_tool_usage(
              tool_name=tool_name,
              project_id=project_id,
              agent_id=agent_id,
              execution_time_ms=execution_time_ms,
              success=success,
              metadata=metadata,
          )
      except Exception as e:
          logger.warning(f"Failed to record tool metrics for {tool_name}: {e}")

3. Implement Task-Based File Discovery for Linting (P2)

File: file:codeframe/ui/routers/lint.py

Location: Line 198-204

Implementation approach:

Option 1 - Git Diff Approach (Recommended for immediate implementation):

• Query task's commit_sha from database
• Run git diff --name-only <commit_sha>^..<commit_sha> to get modified files
• Filter by project workspace path
• Return list of files for linting

Implementation:

# In lint.py, replace TODO with:
if task_id:
    # Get task details to find associated commit
    task = db.get_task(task_id)
    if task and task.get("commit_sha"):
        try:
            # Get modified files from git diff
            result = subprocess.run(
                ["git", "diff", "--name-only", f"{task['commit_sha']}^..{task['commit_sha']}"],
                cwd=project["workspace_path"],
                capture_output=True,
                text=True,
                timeout=5,
            )
            if result.returncode == 0:
                files = [f for f in result.stdout.strip().split("\n") if f]
                # Filter to only files in project
                project_root = Path(project["workspace_path"])
                files = [str(Path(f).relative_to(project_root)) for f in files if Path(f).exists()]
        except Exception as e:
            logger.warning(f"Failed to discover files from git diff: {e}")
            # Fall through to require explicit files list
    else:
        # Task has no commit_sha, require explicit files list
        if not files:
            raise HTTPException(
                status_code=400,
                detail="Task has no associated commit. Please provide explicit files list."
            )

Option 2 - Add files_modified Field (Long-term solution):

• Add files_modified TEXT column to tasks table (JSON array)
• Update task creation/completion to record modified files
• Query from database directly
• More reliable but requires schema change and task update logic

Recommended: Implement Option 1 immediately, plan Option 2 for future sprint

4. Implement Upload Workspace Initialization (P2)

File: file:codeframe/workspace/manager.py

Location: Line 247-255 in _init_from_upload() method

Implementation approach:

Step 4a - Add archive extraction logic:

def _init_from_upload(self, workspace_path: Path, upload_filename: str) -> None:
    """Extract from uploaded archive."""
    import zipfile
    import tarfile
    
    workspace_path.mkdir(parents=True, exist_ok=True)
    
    # Determine archive type from extension
    upload_path = Path(upload_filename)
    
    try:
        if upload_path.suffix.lower() == ".zip":
            # Extract zip archive
            with zipfile.ZipFile(upload_filename, 'r') as zip_ref:
                # Validate archive size (prevent zip bombs)
                total_size = sum(info.file_size for info in zip_ref.infolist())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in zip_ref.infolist():
                    # Prevent path traversal attacks
                    if member.filename.startswith("/") or ".." in member.filename:
                        logger.warning(f"Skipping suspicious path in archive: {member.filename}")
                        continue
                    zip_ref.extract(member, workspace_path)
        
        elif upload_path.suffix.lower() in [".gz", ".bz2"]:
            # Extract tar archive
            tar_mode = "r:gz" if upload_path.suffix.lower() == ".gz" else "r:bz2"
            with tarfile.open(upload_filename, tar_mode) as tar_ref:
                # Validate archive size
                total_size = sum(member.size for member in tar_ref.getmembers())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in tar_ref.getmembers():
                    if member.name.startswith("/") or ".." in member.name:
                        logger.warning(f"Skipping suspicious path in archive: {member.name}")
                        continue
                    tar_ref.extract(member, workspace_path)
        
        else:
            raise ValueError(f"Unsupported archive format: {upload_path.suffix}")
        
        # Initialize git repository after extraction
        subprocess.run(["git", "init"], cwd=workspace_path, check=True, capture_output=True)
        
        # Validate extracted content
        extracted_files = list(workspace_path.glob("**/*"))
        if not extracted_files:
            raise ValueError("Archive appears to be empty")
        
        logger.info(f"Successfully extracted archive to {workspace_path}")
        
    except (zipfile.BadZipFile, tarfile.ReadError) as e:
        logger.error(f"Failed to extract archive: {e}")
        raise ValueError(f"Invalid archive file: {e}")
    except Exception as e:
        logger.error(f"Failed to initialize workspace from upload: {e}")
        raise

Step 4b - Update workspace creation endpoint:

• Ensure upload endpoint passes upload_filename to _init_from_upload()
• Add proper error handling and cleanup on failure

5. Testing and Validation

For each change:

• Add unit tests in appropriate test file
• Test error cases (NULL values, invalid JSON, missing data)
• Verify backward compatibility
• Run existing test suite to ensure no regressions

Test files to update:

• file:tests/persistence/test_database.py - for repository changes
• file:tests/api/test_endpoints_database.py - for API changes
• file:tests/lib/test_sdk_hooks.py - for tool metrics
• Create new test file for upload functionality

6. Documentation Updates

Files to update:

• file:docs/architecture/database-repository-pattern.md - document new methods
• file:CHANGELOG.md - add entries for completed TODOs
• Add inline documentation for new methods
• Update API documentation if endpoints change

Sequence Diagram

sequenceDiagram
    participant Router as API Router
    participant Dep as Dependencies
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. IP Address Context
    Router->>Dep: get_client_ip(request)
    Dep-->>Router: client_ip
    Router->>Repo: update_project(id, updates, user_id, ip_address)
    Repo->>DB: UPDATE projects SET ...
    Repo->>Audit: log_project_event(PROJECT_UPDATED, ip_address)
    Audit->>DB: INSERT INTO audit_logs
    
    Note over Router,Audit: 2. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID

Loading

Priority Order

1. High Priority (P1 - Medium):

   • IP address context (audit trail completeness) - 1-2 days
   • Tool-specific metrics (observability) - 1-2 days

2. Low Priority (P2):

   • Task-based file discovery (developer convenience) - 1 day
   • Upload workspace initialization (feature completion) - 1 day

Rollout Strategy

1. Phase 1: Implement IP address context in audit logs (1-2 days)
2. Phase 2: Add tool-specific metrics recording (1-2 days)
3. Phase 3: Implement task-based file discovery (1 day)
4. Phase 4: Implement upload workspace extraction (1 day)
5. Phase 5: Testing and documentation (1-2 days)

Total estimated effort: 5-8 days

Import In IDE

🤖 Prompt for AI Agents

## Observations

The database integration in CodeFRAME has been **successfully refactored and is largely complete** with a well-implemented repository pattern. PR #216 has completed the high-priority P0 items:

1. ✅ **Issue dependency parsing**: `depends_on` column added to issues table with JSON parsing in `get_issues_with_tasks()`
2. ✅ **Audit logging for PROJECT_UPDATED**: Logs user_id, IP address, and updated fields metadata
3. ✅ **Audit logging for PROJECT_DELETED**: Logs user_id, IP address, and preserves project name before deletion

The remaining work focuses on **completing P1 and P2 items** that enhance observability and developer experience:

**P1 - Medium Priority**:
- IP address context: Pass IP from request context to audit logs (currently optional param with TODO)
- Tool metrics recording: Create `tool_metrics` table and record tool-specific execution metrics

**P2 - Low Priority**:
- Task-based file discovery: Implement auto-discovery of files modified by a task for linting
- Upload workspace extraction: Implement archive extraction in `_init_from_upload()`

## Approach

The plan focuses on **completing the identified P1/P2 TODOs** to enhance observability and developer experience. The approach prioritizes:

1. **Audit trail completeness**: Implement IP address passing from request context to all audit logging calls
2. **Observability**: Add tool-specific metrics tracking in database
3. **Developer experience**: Improve task-file association for linting
4. **Feature completion**: Implement upload workspace extraction

This targeted approach addresses the remaining gaps without unnecessary refactoring of working code.

## Implementation Steps

### 1. Implement IP Address Context in Audit Logs (P1)

**Files**: 
- `file:codeframe/ui/dependencies.py` (add new dependency)
- `file:codeframe/ui/routers/projects.py` (update all endpoints)
- `file:codeframe/persistence/repositories/project_repository.py` (already has IP param)

**Implementation approach**:

**Step 1a - Create IP extraction dependency**:
- Add new function to `file:codeframe/ui/dependencies.py`:
  ```python
  def get_client_ip(request: Request) -> str:
      """Extract client IP from request.
      
      Handles X-Forwarded-For header (for proxies) and direct client.host.
      """
      # Check for X-Forwarded-For header (proxy/load balancer)
      if "x-forwarded-for" in request.headers:
          return request.headers["x-forwarded-for"].split(",")[0].strip()
      # Fall back to direct client IP
      return request.client.host if request.client else "unknown"
  ```

**Step 1b - Update projects router endpoints**:
- Add `get_client_ip` dependency to all endpoints that call `db.update_project()` or `db.delete_project()`
- Currently affected endpoints:
  - `create_project()` - line 130: `db.update_project()` call
  - Any other endpoints that modify projects (search for all `db.update_project()` and `db.delete_project()` calls)

**Step 1c - Pass IP to repository methods**:
- Update `db.update_project()` calls to include `ip_address=client_ip` parameter
- Update `db.delete_project()` calls to include `ip_address=client_ip` parameter
- Update `db.user_has_project_access()` calls to include `ip_address=client_ip` parameter (lines 588, 612, 623)

**Affected code locations**:
- `projects.py` line 130: `db.update_project()` in `create_project()`
- `project_repository.py` lines 588, 612, 623: `user_has_project_access()` audit logging

### 2. Implement Tool-Specific Metrics in Database (P1)

**Files**: 
- `file:codeframe/persistence/schema_manager.py` (add table schema)
- `file:codeframe/persistence/repositories/activity_repository.py` (add method)
- `file:codeframe/lib/sdk_hooks.py` (record metrics)

**Implementation approach**:

**Step 2a - Add tool_metrics table to schema**:
- Update `file:codeframe/persistence/schema_manager.py` to include:
  ```sql
  CREATE TABLE IF NOT EXISTS tool_metrics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tool_name TEXT NOT NULL,
      project_id INTEGER,
      agent_id TEXT,
      execution_time_ms REAL,
      success BOOLEAN NOT NULL,
      metadata TEXT,  -- JSON for tool-specific data
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
  );
  ```

**Step 2b - Add repository method**:
- Add method to `file:codeframe/persistence/repositories/activity_repository.py`:
  ```python
  def record_tool_usage(
      self,
      tool_name: str,
      project_id: Optional[int],
      agent_id: Optional[str],
      execution_time_ms: float,
      success: bool,
      metadata: Optional[Dict[str, Any]] = None,
  ) -> int:
      """Record tool execution metrics."""
      cursor = self.conn.cursor()
      cursor.execute(
          """
          INSERT INTO tool_metrics 
          (tool_name, project_id, agent_id, execution_time_ms, success, metadata)
          VALUES (?, ?, ?, ?, ?, ?)
          """,
          (
              tool_name,
              project_id,
              agent_id,
              execution_time_ms,
              success,
              json.dumps(metadata) if metadata else None,
          ),
      )
      self.conn.commit()
      return cursor.lastrowid
  ```

**Step 2c - Update sdk_hooks.py**:
- Modify `create_metrics_post_hook()` function (line 186-253)
- Replace TODO comment at line 238 with actual implementation:
  ```python
  if tool_name in ["Write", "Bash"]:
      # Record tool-specific metrics in database
      try:
          execution_time_ms = context.get("execution_time_ms", 0) if context else 0
          success = "error" not in str(tool_response).lower()
          
          # Get project_id and agent_id from context if available
          project_id = context.get("project_id") if context else None
          agent_id = context.get("agent_id") if context else None
          
          # Build metadata based on tool type
          metadata = {}
          if tool_name == "Write":
              metadata["file_path"] = tool_input.get("file_path", "")
          elif tool_name == "Bash":
              metadata["command"] = tool_input.get("command", "")[:100]  # Truncate for storage
          
          # Record in database
          db.activities.record_tool_usage(
              tool_name=tool_name,
              project_id=project_id,
              agent_id=agent_id,
              execution_time_ms=execution_time_ms,
              success=success,
              metadata=metadata,
          )
      except Exception as e:
          logger.warning(f"Failed to record tool metrics for {tool_name}: {e}")
  ```

### 3. Implement Task-Based File Discovery for Linting (P2)

**File**: `file:codeframe/ui/routers/lint.py`

**Location**: Line 198-204

**Implementation approach**:

**Option 1 - Git Diff Approach (Recommended for immediate implementation)**:
- Query task's `commit_sha` from database
- Run `git diff --name-only <commit_sha>^..<commit_sha>` to get modified files
- Filter by project workspace path
- Return list of files for linting

**Implementation**:
```python
# In lint.py, replace TODO with:
if task_id:
    # Get task details to find associated commit
    task = db.get_task(task_id)
    if task and task.get("commit_sha"):
        try:
            # Get modified files from git diff
            result = subprocess.run(
                ["git", "diff", "--name-only", f"{task['commit_sha']}^..{task['commit_sha']}"],
                cwd=project["workspace_path"],
                capture_output=True,
                text=True,
                timeout=5,
            )
            if result.returncode == 0:
                files = [f for f in result.stdout.strip().split("\n") if f]
                # Filter to only files in project
                project_root = Path(project["workspace_path"])
                files = [str(Path(f).relative_to(project_root)) for f in files if Path(f).exists()]
        except Exception as e:
            logger.warning(f"Failed to discover files from git diff: {e}")
            # Fall through to require explicit files list
    else:
        # Task has no commit_sha, require explicit files list
        if not files:
            raise HTTPException(
                status_code=400,
                detail="Task has no associated commit. Please provide explicit files list."
            )
```

**Option 2 - Add files_modified Field (Long-term solution)**:
- Add `files_modified` TEXT column to `tasks` table (JSON array)
- Update task creation/completion to record modified files
- Query from database directly
- More reliable but requires schema change and task update logic

**Recommended**: Implement Option 1 immediately, plan Option 2 for future sprint

### 4. Implement Upload Workspace Initialization (P2)

**File**: `file:codeframe/workspace/manager.py`

**Location**: Line 247-255 in `_init_from_upload()` method

**Implementation approach**:

**Step 4a - Add archive extraction logic**:
```python
def _init_from_upload(self, workspace_path: Path, upload_filename: str) -> None:
    """Extract from uploaded archive."""
    import zipfile
    import tarfile
    
    workspace_path.mkdir(parents=True, exist_ok=True)
    
    # Determine archive type from extension
    upload_path = Path(upload_filename)
    
    try:
        if upload_path.suffix.lower() == ".zip":
            # Extract zip archive
            with zipfile.ZipFile(upload_filename, 'r') as zip_ref:
                # Validate archive size (prevent zip bombs)
                total_size = sum(info.file_size for info in zip_ref.infolist())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in zip_ref.infolist():
                    # Prevent path traversal attacks
                    if member.filename.startswith("/") or ".." in member.filename:
                        logger.warning(f"Skipping suspicious path in archive: {member.filename}")
                        continue
                    zip_ref.extract(member, workspace_path)
        
        elif upload_path.suffix.lower() in [".gz", ".bz2"]:
            # Extract tar archive
            tar_mode = "r:gz" if upload_path.suffix.lower() == ".gz" else "r:bz2"
            with tarfile.open(upload_filename, tar_mode) as tar_ref:
                # Validate archive size
                total_size = sum(member.size for member in tar_ref.getmembers())
                if total_size > 1_000_000_000:  # 1GB limit
                    raise ValueError("Archive too large (>1GB)")
                
                # Extract with path traversal protection
                for member in tar_ref.getmembers():
                    if member.name.startswith("/") or ".." in member.name:
                        logger.warning(f"Skipping suspicious path in archive: {member.name}")
                        continue
                    tar_ref.extract(member, workspace_path)
        
        else:
            raise ValueError(f"Unsupported archive format: {upload_path.suffix}")
        
        # Initialize git repository after extraction
        subprocess.run(["git", "init"], cwd=workspace_path, check=True, capture_output=True)
        
        # Validate extracted content
        extracted_files = list(workspace_path.glob("**/*"))
        if not extracted_files:
            raise ValueError("Archive appears to be empty")
        
        logger.info(f"Successfully extracted archive to {workspace_path}")
        
    except (zipfile.BadZipFile, tarfile.ReadError) as e:
        logger.error(f"Failed to extract archive: {e}")
        raise ValueError(f"Invalid archive file: {e}")
    except Exception as e:
        logger.error(f"Failed to initialize workspace from upload: {e}")
        raise
```

**Step 4b - Update workspace creation endpoint**:
- Ensure upload endpoint passes `upload_filename` to `_init_from_upload()`
- Add proper error handling and cleanup on failure

### 5. Testing and Validation

**For each change**:
- Add unit tests in appropriate test file
- Test error cases (NULL values, invalid JSON, missing data)
- Verify backward compatibility
- Run existing test suite to ensure no regressions

**Test files to update**:
- `file:tests/persistence/test_database.py` - for repository changes
- `file:tests/api/test_endpoints_database.py` - for API changes
- `file:tests/lib/test_sdk_hooks.py` - for tool metrics
- Create new test file for upload functionality

### 6. Documentation Updates

**Files to update**:
- `file:docs/architecture/database-repository-pattern.md` - document new methods
- `file:CHANGELOG.md` - add entries for completed TODOs
- Add inline documentation for new methods
- Update API documentation if endpoints change

## Sequence Diagram

```mermaid
sequenceDiagram
    participant Router as API Router
    participant Dep as Dependencies
    participant Repo as Repository
    participant DB as SQLite Database
    participant Audit as AuditLogger
    
    Note over Router,Audit: 1. IP Address Context
    Router->>Dep: get_client_ip(request)
    Dep-->>Router: client_ip
    Router->>Repo: update_project(id, updates, user_id, ip_address)
    Repo->>DB: UPDATE projects SET ...
    Repo->>Audit: log_project_event(PROJECT_UPDATED, ip_address)
    Audit->>DB: INSERT INTO audit_logs
    
    Note over Router,Audit: 2. Tool Metrics
    Router->>Repo: record_tool_usage(tool, project, agent)
    Repo->>DB: INSERT INTO tool_metrics
    Repo-->>Router: Metric ID
```

## Priority Order

1. **High Priority** (P1 - Medium):
   - IP address context (audit trail completeness) - 1-2 days
   - Tool-specific metrics (observability) - 1-2 days

2. **Low Priority** (P2):
   - Task-based file discovery (developer convenience) - 1 day
   - Upload workspace initialization (feature completion) - 1 day

## Rollout Strategy

1. **Phase 1**: Implement IP address context in audit logs (1-2 days)
2. **Phase 2**: Add tool-specific metrics recording (1-2 days)
3. **Phase 3**: Implement task-based file discovery (1 day)
4. **Phase 4**: Implement upload workspace extraction (1 day)
5. **Phase 5**: Testing and documentation (1-2 days)

Total estimated effort: 5-8 days

---

Execution Information

Branch: main
Commit: 6aa19a3