# Trigger Execution Logging

## Overview

The `trg_task_fixed_update_visibility` trigger now logs all execution attempts to the `trigger_execution_log` table. This provides comprehensive visibility into trigger behavior, performance, and errors.

## Log Table Schema

The `trigger_execution_log` table tracks:

- **Execution Details**: Trigger name, task ID, customer/store/week context
- **Status Tracking**: Old/new status values, execution status (success/error/skipped)
- **Timing**: Start time, completion time, duration in milliseconds
- **Results**: Function results, availability values, update flags
- **Errors**: Error messages and codes when failures occur
- **Notes**: Step-by-step execution notes

## Log Status Values

- `processing`: Initial log entry created when trigger fires
- `success`: All steps completed successfully
- `error`: An error occurred during execution
- `skipped`: Execution was skipped (guard conditions not met)

## Querying Logs

Use `view_trigger_logs.sql` for common queries:

- Recent executions
- Error-only logs
- Success-only logs
- Skipped executions with reasons
- Performance statistics
- Filter by task_id, store_code, customer_id

## Example Queries

```sql
-- View recent executions
SELECT * FROM trigger_execution_log
ORDER BY created_at DESC LIMIT 20;

-- Find errors
SELECT * FROM trigger_execution_log
WHERE status = 'error'
ORDER BY created_at DESC;

-- Performance stats
SELECT 
    status,
    COUNT(*) as count,
    AVG(execution_duration_ms) as avg_ms
FROM trigger_execution_log
WHERE execution_duration_ms IS NOT NULL
GROUP BY status;
```

## Logging Behavior

- **Always logs**: Every trigger fire is logged, even if skipped
- **Error-safe**: Logging failures don't break the trigger
- **Detailed notes**: Each step appends notes to track execution flow
- **Performance tracking**: Duration calculated automatically

## Files

- `create_trigger_log_table.sql`: Creates the log table and indexes
- `task_status_fixed_trigger.sql`: Updated trigger function with logging
- `view_trigger_logs.sql`: Useful queries for viewing logs