# Operation Log Examples - Before and After
## Example 1: Fetch Operation with Column Error
### Before Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'abc123';
```
| abc123 | fetch | users | 400 | 45 | {"error": "column email does not exist", "cache_key": "users:...:xyz"} |
**Issues:**
- ❌ Need to check status_code >= 400 to find errors
- ❌ Must parse JSON to get error message
- ❌ Must parse JSON to get cache_key
- ❌ Complex queries required for error analytics
### After Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'abc123';
```
| abc123 | fetch | users | 400 | 45 | true | The specified column does not exist | users:...:xyz | {full JSON} |
**Improvements:**
- ✅ `error = true` for easy filtering
- ✅ `message` directly queryable
- ✅ `cache_key` directly queryable
- ✅ Simple, fast queries for error analytics
## Example 2: Query Operation Success
### Before Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'def456';
```
| def456 | query | 200 | 123 | {} |
### After Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'def456';
```
| def456 | query | 200 | 123 | false | NULL | NULL | {} |
**Improvements:**
- ✅ `error = false` clearly indicates success
- ✅ NULL values for message/cache_key when not applicable
- ✅ Consistent schema across all operations
## Example 3: Insert Operation with Unique Violation
### Before Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'ghi789';
```
| ghi789 | insert | users | 409 | 78 | {"error": "duplicate key violates constraint users_email_key"} |
### After Implementation
```sql
SELECT * FROM gateway_operation_log WHERE request_id = 'ghi789';
```
| ghi789 | insert | users | 409 | 78 | true | A record with this value already exists | NULL | {full error JSON with constraint info} |
**Improvements:**
- ✅ User-friendly message extracted
- ✅ Technical details preserved in JSON
- ✅ Easy to aggregate by message type
## Powerful Queries Now Possible
### Query 1: Recent Errors with Messages
```sql
SELECT
created_at,
operation,
table_name,
message,
cache_key,
duration_ms
FROM gateway_operation_log
WHERE error = true
AND created_at > NOW() - INTERVAL '1 hour'
ORDER BY created_at DESC
LIMIT 20;
```
**Result:**
| 2026-02-09 02:15:00 | fetch | messages | The specified column does not exist | messages:...:abc | 45 |
| 2026-02-09 02:14:30 | insert | users | A record with this value already exists | NULL | 78 |
| 2026-02-09 02:13:45 | query | NULL | Database connection pool timed out | NULL | 3000 |
### Query 2: Error Frequency by Message
```sql
SELECT
message,
COUNT(*) as occurrences,
AVG(duration_ms) as avg_duration,
MIN(created_at) as first_seen,
MAX(created_at) as last_seen
FROM gateway_operation_log
WHERE error = true
AND message IS NOT NULL
AND created_at > NOW() - INTERVAL '24 hours'
GROUP BY message
ORDER BY occurrences DESC;
```
**Result:**
| The specified column does not exist | 47 | 52.3 | 2026-02-09 01:00:00 | 2026-02-09 02:15:00 |
| A record with this value already exists | 23 | 65.8 | 2026-02-09 00:30:00 | 2026-02-09 02:14:30 |
| Database connection pool timed out | 8 | 2987.5 | 2026-02-09 01:45:00 | 2026-02-09 02:13:45 |
### Query 3: Cache Keys with Errors
```sql
SELECT
cache_key,
COUNT(*) as error_count,
array_agg(DISTINCT message) as error_messages,
MAX(created_at) as last_error
FROM gateway_operation_log
WHERE error = true
AND cache_key IS NOT NULL
AND created_at > NOW() - INTERVAL '24 hours'
GROUP BY cache_key
HAVING COUNT(*) > 5
ORDER BY error_count DESC;
```
**Result:**
| messages:user_id:id,content:100:false:abc123 | 47 | ["The specified column does not exist"] | 2026-02-09 02:15:00 |
| users:email:id,name:50:false:def456 | 12 | ["The specified column does not exist", "Database connection pool timed out"] | 2026-02-09 02:10:00 |
### Query 4: Error Rate Trend by Hour
```sql
SELECT
DATE_TRUNC('hour', created_at) as hour,
COUNT(*) as total_operations,
SUM(CASE WHEN error THEN 1 ELSE 0 END) as errors,
ROUND(100.0 * SUM(CASE WHEN error THEN 1 ELSE 0 END) / COUNT(*), 2) as error_rate_percent
FROM gateway_operation_log
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour DESC;
```
**Result:**
| 2026-02-09 02:00:00 | 1523 | 78 | 5.12 |
| 2026-02-09 01:00:00 | 2847 | 145 | 5.09 |
| 2026-02-09 00:00:00 | 3201 | 89 | 2.78 |
### Query 5: Slowest Errors
```sql
SELECT
operation,
table_name,
message,
duration_ms,
status_code,
created_at
FROM gateway_operation_log
WHERE error = true
AND duration_ms > 1000
ORDER BY duration_ms DESC
LIMIT 10;
```
**Result:**
| fetch | attachments | Database connection pool timed out | 3000 | 503 | 2026-02-09 02:13:45 |
| query | NULL | Database connection pool timed out | 2987 | 503 | 2026-02-09 01:55:23 |
| fetch | messages | The operation references a non-existent resource | 1456 | 422 | 2026-02-09 01:42:11 |
## Grafana/Dashboard Queries
### Simple Error Count Metric
```sql
SELECT COUNT(*) FROM gateway_operation_log
WHERE error = true
AND created_at > NOW() - INTERVAL '5 minutes';
```
### Error Rate Percentage
```sql
SELECT
100.0 * SUM(CASE WHEN error THEN 1 ELSE 0 END) / COUNT(*) as error_rate
FROM gateway_operation_log
WHERE created_at > NOW() - INTERVAL '5 minutes';
```
### Top Error Messages (for alerts)
```sql
SELECT
message,
COUNT(*) as count
FROM gateway_operation_log
WHERE error = true
AND created_at > NOW() - INTERVAL '15 minutes'
AND message IS NOT NULL
GROUP BY message
ORDER BY count DESC
LIMIT 5;
```
## Benefits Summary
### Before
- ❌ Complex JSON parsing required
- ❌ Slow queries with JSON operators
- ❌ Difficult to aggregate errors
- ❌ Hard to create dashboards
- ❌ No direct message querying
### After
- ✅ Simple boolean filtering
- ✅ Fast indexed queries
- ✅ Easy error aggregation
- ✅ Dashboard-friendly schema
- ✅ Direct message/cache_key access
- ✅ Better analytics capabilities
- ✅ Faster incident response