---
name: Error post-processing module
overview: Create a sophisticated error post-processing module that sanitizes SQL queries from error messages and formats them into user-friendly, structured error responses with proper categorization, metadata, and helpful hints.
todos:
  - id: create-error-module
    content: Create src/error/mod.rs with core error types (ErrorCategory, ProcessedError) and error processing traits
    status: completed
  - id: sql-sanitizer
    content: Create src/error/sql_sanitizer.rs with SQL sanitization utilities to strip queries from error messages
    status: completed
  - id: sqlx-parser
    content: Create src/error/sqlx_parser.rs to parse sqlx::Error into structured ProcessedError with user-friendly messages
    status: completed
  - id: update-sqlx-driver
    content: Update src/drivers/postgresql/sqlx_driver.rs to remove SQL from error contexts and use error processor
    status: completed
  - id: update-gateway-routes
    content: Update gateway routes (fetch, query) to use error processor for consistent error responses
    status: completed
  - id: extend-response-builders
    content: Extend src/api/response.rs with processed_error() helper for consistent error formatting
    status: completed
isProject: false
---

# Error Post-Processing Module Implementation Plan

## Problem Analysis

Currently, the system dumps entire SQL queries and raw error messages back to clients, which creates several issues:

- **Security**: Exposes internal database schema, table structures, and query patterns
- **UX**: Raw error messages are not user-friendly or actionable
- **Debugging**: No structured error metadata for logging and tracing

### Current problematic locations:

- `[src/drivers/postgresql/sqlx_driver.rs:289,371](src/drivers/postgresql/sqlx_driver.rs)` - Includes full SQL in error context
- `[src/api/gateway/fetch/mod.rs:617](src/api/gateway/fetch/mod.rs)` - Returns raw error strings
- `[src/api/gateway/query/mod.rs:108](src/api/gateway/query/mod.rs)` - Returns raw sqlx errors

## Solution Architecture

### 1. Create Core Error Processing Module

**New file**: `src/error/mod.rs`

This module will contain:

- Error type categorization (database, validation, network, etc.)
- SQL sanitization utilities
- Error context extraction from sqlx errors
- User-friendly message generation
- Structured error response builders

### 2. Error Categories

```rust
pub enum ErrorCategory {
    DatabaseConstraint,    // FK, unique, check violations
    DatabaseConnection,    // Connection pool, timeout
    QuerySyntax,          // Malformed SQL
    Authentication,       // Auth failures
    Authorization,        // Permission issues
    NotFound,            // Resource not found
    Validation,          // Input validation
    Internal,            // Unexpected errors
    RateLimiting,        // Too many requests
}
```

### 3. Structured Error Response

```rust
pub struct ProcessedError {
    pub category: ErrorCategory,
    pub status_code: StatusCode,
    pub error_code: &'static str,
    pub message: String,          // User-friendly message
    pub hint: Option<String>,     // Actionable suggestion
    pub trace_id: String,
    pub metadata: Map<String, Value>,  // Safe metadata (no SQL)
}
```

### 4. SQL Sanitization Strategy

- **Remove full queries**: Strip SQL statements from error messages
- **Extract safe info**: Keep constraint names, column names, SQL state codes
- **Redact sensitive data**: Remove parameter values from error strings
- **Preserve debugging context**: Log full errors server-side but return sanitized versions

### 5. Integration Points

#### PostgreSQL Driver Changes

- Modify error handling in `[sqlx_driver.rs](src/drivers/postgresql/sqlx_driver.rs)`
- Use error processor instead of `with_context(|| format!("... {}", sql))`
- Return structured errors from `insert_row`, `fetch_rows`, `fetch_rows_with_columns`

#### Gateway Route Updates

- Update `[gateway/fetch/mod.rs](src/api/gateway/fetch/mod.rs)` to use error processor
- Update `[gateway/query/mod.rs](src/api/gateway/query/mod.rs)` to use error processor
- Maintain consistent error response format across all routes

#### Response Builder Integration

- Extend `[src/api/response.rs](src/api/response.rs)` with new error response builders
- Add `processed_error()` function that accepts `ProcessedError`

## Implementation Steps

### Phase 1: Core Error Module

1. Create `src/error/mod.rs` with error types and traits
2. Implement `ErrorCategory` enum
3. Implement `ProcessedError` struct with builder pattern
4. Create SQL sanitization utilities

### Phase 2: SqlX Error Parser

1. Create error parser for `sqlx::Error` types
2. Extract database error codes (23505, 23503, etc.)
3. Parse constraint names from error messages
4. Map error codes to user-friendly messages
5. Generate helpful hints based on error type

### Phase 3: Driver Integration

1. Create `PostgresQueryError` enum for structured driver errors
2. Update `fetch_rows_with_columns` error handling
3. Update `fetch_rows` error handling
4. Update `insert_row` to use new error types (already has good structure)
5. Remove SQL from `.with_context()` calls

### Phase 4: Gateway Integration

1. Update `gateway/fetch` route error handling
2. Update `gateway/query` route error handling
3. Update `query/sql` route error handling
4. Ensure consistent trace_id generation

### Phase 5: Response Formatting

1. Add `processed_error()` helper to `api/response.rs`
2. Ensure JSON response includes all required fields
3. Add examples of error responses in documentation

## Error Message Examples

**Before:**

```json
{
  "error": "Failed to fetch data: failed to execute select query: SELECT jsonb_build_object('id', t.id, 'name', t.name) FROM users WHERE email = $1: column 'email' does not exist"
}
```

**After:**

```json
{
  "status": "error",
  "code": "query_syntax_error",
  "message": "Unable to fetch data due to a database query issue",
  "hint": "The specified column may not exist in the table. Please verify your column selection.",
  "trace_id": "a3f8d9e2",
  "details": {
    "category": "query_syntax",
    "sql_state": "42703",
    "table": "users"
  }
}
```

## Testing Considerations

- Test constraint violation errors (unique, FK)
- Test connection errors
- Test syntax errors
- Test permission errors
- Verify SQL is never exposed in responses
- Verify trace_ids are consistent in logs and responses

## Files to Create/Modify

**New Files:**

- `src/error/mod.rs` - Core error processing module
- `src/error/sql_sanitizer.rs` - SQL sanitization utilities
- `src/error/sqlx_parser.rs` - SqlX error parser

**Modified Files:**

- `src/lib.rs` - Add `pub mod error`
- `src/drivers/postgresql/sqlx_driver.rs` - Update error handling
- `src/api/gateway/fetch/mod.rs` - Use error processor
- `src/api/gateway/query/mod.rs` - Use error processor
- `src/api/query/sql/mod.rs` - Use error processor
- `src/api/response.rs` - Add processed error helpers

