[Bug]: SQLite-specific `json_extract()` breaks PostgreSQL observability queries

[alyssa-n]

Bug Report: SQLite-specific json_extract() breaks PostgreSQL observability queries

Summary

The observability analytics endpoints in admin.py use SQLite-specific func.json_extract() function calls that fail when the backend database is PostgreSQL, causing SQL errors on tool usage statistics, error rates, performance metrics, and other observability queries.

Environment

• Backend Database: PostgreSQL 16.10
• Affected File: mcpgateway/admin.py
• Error Type: sqlalchemy.exc.OperationalError
• SQLAlchemy Error Code: f405

Error Details

Error Message

2025-11-27 11:53:33,173 - mcpgateway.admin - ERROR - Failed to get tool performance metrics: (psycopg2.errors.UndefinedFunction) function json_extract(json, unknown) does not exist
LINE 1: SELECT json_extract(observability_spans.attributes, '$."tool...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Example SQL Query (Failed)

SELECT json_extract(observability_spans.attributes, %(json_extract_1)s) AS tool_name, observability_spans.duration_ms AS observability_spans_duration_ms 
FROM observability_spans 
WHERE observability_spans.name = %(name_1)s AND observability_spans.start_time >= %(start_time_1)s AND observability_spans.duration_ms IS NOT NULL AND json_extract(observability_spans.attributes, %(json_extract_2)s) IS NOT NULL]

Parameters

[parameters: {'json_extract_1': '$."tool.name"', 'name_1': 'tool.invoke', 'start_time_1': datetime.datetime(2025, 11, 26, 16, 53, 33, 167795), 'json_extract_2': '$."tool.name"'}]

Root Cause

The code uses func.json_extract() which is SQLite-specific. PostgreSQL uses different JSON operators:

• SQLite: json_extract(column, '$.path')
• PostgreSQL: column->>'path' or jsonb_extract_path_text(column, 'path')

Affected Code Locations

All occurrences are in mcpgateway/admin.py:

Tool Analytics (Lines 11920, 12687, 12693, 12695)

# Line 11920 - Tool name filter in trace list
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').ilike(f"%{tool_name}%")

# Lines 12687-12695 - Get most used tools
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').label("tool_name")
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."tool.name"'))

Tool Performance Metrics (Lines 12749, 12756)

# Lines 12749-12756 - Get tool performance statistics
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').label("tool_name")
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').isnot(None)

Tool Error Rates (Lines 12841, 12848, 12850)

# Lines 12841-12850 - Get tool error statistics
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').label("tool_name")
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."tool.name"'))

Tool Chains (Lines 12904, 12910)

# Lines 12904-12910 - Get tool chain analysis
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').label("tool_name")
func.json_extract(ObservabilitySpan.attributes, '$."tool.name"').isnot(None)

Prompt Analytics (Lines 13003, 13009, 13011, 13065, 13072, 13152, 13159, 13161)

# Lines 13003-13011 - Get most used prompts
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').label("prompt_id")
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"'))

# Lines 13065-13072 - Get prompt performance statistics
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').label("prompt_id")
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').isnot(None)

# Lines 13152-13161 - Get prompt error statistics
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').label("prompt_id")
func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."prompt.id"'))

Resource Analytics (Lines 13241, 13247, 13249, 13303, 13310, 13390, 13397, 13399)

# Lines 13241-13249 - Get most accessed resources
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').label("resource_uri")
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"'))

# Lines 13303-13310 - Get resource performance statistics
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').label("resource_uri")
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').isnot(None)

# Lines 13390-13399 - Get resource error statistics
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').label("resource_uri")
func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"').isnot(None)
.group_by(func.json_extract(ObservabilitySpan.attributes, '$."resource.uri"'))

Total Count

27 occurrences of func.json_extract() in admin.py

Affected Endpoints

The following admin API endpoints are broken on PostgreSQL:

1. GET /admin/observability/traces - Tool name filtering
2. GET /admin/observability/tools/usage - Most used tools statistics
3. GET /admin/observability/tools/performance - Tool performance metrics
4. GET /admin/observability/tools/errors - Tool error rates
5. GET /admin/observability/tools/chains - Tool chain analysis
6. GET /admin/observability/prompts/usage - Most used prompts statistics
7. GET /admin/observability/prompts/performance - Prompt performance metrics
8. GET /admin/observability/prompts/errors - Prompt error statistics
9. GET /admin/observability/resources/usage - Most accessed resources
10. GET /admin/observability/resources/performance - Resource performance metrics
11. GET /admin/observability/resources/errors - Resource error statistics

Impact

• Scope: All observability analytics endpoints fail on PostgreSQL
• Users Affected: All users running MCP Gateway with PostgreSQL backend
• Workaround: None available (requires code changes)

References

• SQLAlchemy Error: https://sqlalche.me/e/20/f405
• PostgreSQL JSON Functions: https://www.postgresql.org/docs/current/functions-json.html
• SQLite JSON Functions: https://www.sqlite.org/json1.html
• SQLAlchemy JSON Types: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.JSON