# Sync Hooks System - Auto-Aggregation & Declarative Data Transformations

## Overview

The Sync Hooks System is a declarative framework for automatic data transformations when records are synced via the `/api/sync` endpoints. It enables:

- **Auto-aggregation**: Automatically calculate aggregates from detail records (e.g., time clocks → payroll aggregates)
- **Cascade updates**: Update related tables when source data changes (e.g., inventory transactions → inventory levels)
- **Derived fields**: Calculate computed fields automatically
- **Validation**: Run post-sync validation rules
- **Custom logic**: Execute arbitrary PHP code on sync events

## Architecture

### Database Tables

**sync_hooks**: Stores hook definitions
```sql
CREATE TABLE sync_hooks (
    hook_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    name VARCHAR(255) NOT NULL UNIQUE,
    trigger_table VARCHAR(100) NOT NULL,        -- Table that triggers the hook
    trigger_operations VARCHAR(50)[] NOT NULL,  -- ['PULL', 'PUSH', 'DELETE']
    hook_type VARCHAR(50) NOT NULL,             -- 'aggregate', 'cascade', 'derive', 'validate', 'custom'
    priority INTEGER DEFAULT 100,               -- Lower = higher priority
    config JSONB NOT NULL,                      -- Hook-specific configuration
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**sync_hook_logs**: Audit trail for hook executions
```sql
CREATE TABLE sync_hook_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    hook_id UUID REFERENCES sync_hooks(hook_id),
    trigger_table VARCHAR(100),
    operation VARCHAR(50),
    status VARCHAR(50),                         -- 'success', 'error', 'warning'
    result_data JSONB,
    execution_time_ms NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### PHP Components

**SyncHookService** (`api/services/SyncHookService.php`):
- Loads and caches active hooks
- Executes hooks based on table and operation
- Logs execution results
- Extensible for new hook types

**SyncController Integration** (`api/controllers/SyncController.php`):
- After pulling records, executes applicable hooks
- Passes filters (like date ranges) to hooks
- Returns hook results in `_hooks` response field

## Hook Types

### 1. Aggregate Hooks

Calculate aggregates from detail records into summary tables.

**Use Cases**:
- Time clock entries → payroll aggregates
- Sales orders → revenue summaries
- Support tickets → team metrics

**Configuration**:
```json
{
  "source_table": "time_clocks",
  "target_table": "time_clock_aggregates",
  "group_by": ["user_id", "pay_period_start", "pay_period_end"],
  "filter_conditions": {
    "status": "approved",
    "clock_out IS NOT NULL": true
  },
  "aggregations": [
    {
      "field": "total_net_hours",
      "formula": "SUM((EXTRACT(EPOCH FROM (clock_out - clock_in)) / 3600) - (break_minutes / 60.0))"
    },
    {
      "field": "total_overtime_hours",
      "formula": "SUM(GREATEST(((EXTRACT(EPOCH FROM (clock_out - clock_in)) / 3600) - (break_minutes / 60.0)) - 8.0, 0))"
    },
    {
      "field": "total_regular_hours",
      "formula": "SUM(LEAST(((EXTRACT(EPOCH FROM (clock_out - clock_in)) / 3600) - (break_minutes / 60.0)), 8.0))"
    },
    {
      "field": "total_days_worked",
      "formula": "COUNT(DISTINCT DATE(clock_in))"
    }
  ],
  "pay_period": {
    "type": "bi-weekly",
    "start_day": "Monday"
  },
  "require_date_filter": true
}
```

**How It Works**:
1. When `time_clock_aggregates` is pulled with date filters
2. Hook queries `time_clocks` table with filters
3. Groups by user and pay period
4. Calculates aggregations using SQL formulas
5. Inserts/updates records in `time_clock_aggregates`

### 2. Cascade Hooks (Planned)

Update related tables when source records change.

**Use Cases**:
- Inventory transactions → update stock levels
- Order line items → update order totals
- Task completion → update project progress

**Configuration** (Example):
```json
{
  "source_table": "inventory_txn",
  "target_table": "inventory",
  "key_fields": ["product_id", "location_id"],
  "updates": [
    {
      "field": "quantity_on_hand",
      "formula": "quantity_on_hand + NEW.quantity_change"
    },
    {
      "field": "last_transaction_date",
      "value": "NEW.transaction_date"
    }
  ]
}
```

### 3. Derive Hooks (Planned)

Calculate derived/computed fields automatically.

**Use Cases**:
- Order totals = sum of line items
- Age from birth date
- Status based on multiple conditions

**Configuration** (Example):
```json
{
  "table": "orders",
  "derived_fields": [
    {
      "field": "total_amount",
      "formula": "(SELECT SUM(quantity * unit_price) FROM order_lines WHERE order_id = orders.order_id)"
    },
    {
      "field": "discount_percentage",
      "formula": "CASE WHEN total_amount > 1000 THEN 10 ELSE 0 END"
    }
  ]
}
```

### 4. Validate Hooks (Planned)

Run validation rules after sync operations.

**Use Cases**:
- Check inventory levels don't go negative
- Validate business rules (e.g., max hours per day)
- Ensure referential integrity

**Configuration** (Example):
```json
{
  "table": "inventory",
  "validations": [
    {
      "rule": "quantity_on_hand >= 0",
      "error_message": "Inventory cannot be negative",
      "severity": "error"
    },
    {
      "rule": "quantity_on_hand < reorder_point",
      "error_message": "Inventory below reorder point",
      "severity": "warning"
    }
  ]
}
```

### 5. Custom Hooks (Planned)

Execute custom PHP class methods.

**Use Cases**:
- Complex business logic
- External API calls
- Email notifications

**Configuration** (Example):
```json
{
  "class": "App\\Services\\CustomProcessor",
  "method": "processRecords",
  "parameters": {
    "notify": true,
    "batch_size": 100
  }
}
```

## Adding New Hooks

### Step 1: Define Hook in Database

```sql
INSERT INTO sync_hooks (
    name,
    trigger_table,
    trigger_operations,
    hook_type,
    priority,
    config,
    is_active
) VALUES (
    'inventory_stock_update',           -- Unique name
    'inventory_txn',                    -- Table that triggers hook
    ARRAY['PULL', 'PUSH'],              -- Operations that trigger hook
    'cascade',                          -- Hook type
    100,                                -- Priority (lower = runs first)
    '{                                  -- Configuration JSONB
        "source_table": "inventory_txn",
        "target_table": "inventory",
        "key_fields": ["product_id", "location_id"],
        "updates": [
            {
                "field": "quantity_on_hand",
                "formula": "quantity_on_hand + NEW.quantity_change"
            }
        ]
    }'::jsonb,
    true                                -- Active
);
```

### Step 2: Test Hook Execution

```bash
# Pull the trigger table with date filters if required
curl 'http://localhost:8080/api/sync/pull?tables[inventory_txn]=&start_date=2024-01-01&end_date=2024-12-31' \
    -b 'PHPSESSID=your_session_id'

# Check hook logs
docker exec postgres psql -U taf -d devdb -c "
    SELECT * FROM sync_hook_logs 
    WHERE hook_id = (SELECT hook_id FROM sync_hooks WHERE name = 'inventory_stock_update')
    ORDER BY created_at DESC LIMIT 5;
"
```

### Step 3: Implement Hook Type (if needed)

If using a hook type that's not yet implemented (cascade, derive, validate, custom), add implementation to `SyncHookService.php`:

```php
private function executeCascadeHook(array $config, array $affectedIds): array
{
    $sourceTable = $config['source_table'];
    $targetTable = $config['target_table'];
    $keyFields = $config['key_fields'];
    $updates = $config['updates'];
    
    // Implementation here...
    
    return [
        'updated' => $count,
        'details' => $results
    ];
}
```

## Time Clock Aggregation Example

### Current Implementation

The time clock aggregation hook is fully implemented and serves as a reference for future hooks.

**Hook Definition**:
```sql
SELECT * FROM sync_hooks WHERE name = 'time_clock_aggregation';
```

**Configuration Highlights**:
- **Trigger**: Pulls on `time_clock_aggregates` table
- **Source**: Reads from `time_clocks` table
- **Grouping**: By user_id and pay period (bi-weekly starting Monday)
- **Filters**: Requires date range (start_date, end_date)
- **Calculations**:
  - Total net hours = work hours - breaks
  - Overtime = hours over 8/day
  - Regular hours = hours up to 8/day
  - Total days worked = distinct days

**Frontend Integration**:

The Payroll module includes date range filters that automatically trigger aggregation:

```javascript
// In modules table config
{
    "header": [
        {
            "type": "filter-date-range",
            "filterField": "start_date",
            "label": "Pay Period"
        }
    ]
}
```

When users select dates and pull payroll data, hooks execute automatically.

### Testing

```bash
# Run comprehensive test suite
./test_sync_hooks.sh

# Manual API test
curl 'http://localhost:8080/api/sync/pull?tables[time_clock_aggregates]=&start_date=2024-01-01&end_date=2024-12-31' \
    -b 'PHPSESSID=d39e7c4fef5dc152c3f222a2ce30da7a' | jq

# Check execution logs
docker exec postgres psql -U taf -d devdb -c "
    SELECT 
        created_at,
        trigger_table,
        operation,
        status,
        result_data->>'inserted' as inserted,
        result_data->>'updated' as updated,
        execution_time_ms
    FROM sync_hook_logs
    WHERE hook_id = (SELECT hook_id FROM sync_hooks WHERE name = 'time_clock_aggregation')
    ORDER BY created_at DESC
    LIMIT 10;
"
```

## Performance Considerations

1. **Hook Caching**: Hooks are cached in memory on first use. Clear cache when hooks change:
   ```php
   $hookService = new SyncHookService();
   $hookService->clearCache();
   ```

2. **Priority Ordering**: Use priority to control execution order. Lower values run first.

3. **Selective Triggers**: Use specific trigger_table instead of '*' wildcard when possible.

4. **Date Filters**: For time-based aggregations, always require date filters to avoid processing entire table.

5. **Indexing**: Ensure source and target tables have appropriate indexes:
   ```sql
   -- For time_clocks aggregation
   CREATE INDEX idx_time_clocks_user_date ON time_clocks(user_id, clock_in);
   CREATE INDEX idx_time_clock_aggregates_lookup ON time_clock_aggregates(user_id, pay_period_start, pay_period_end);
   ```

## Monitoring & Debugging

### View Hook Execution History

```sql
-- Recent executions
SELECT 
    sh.name,
    shl.created_at,
    shl.trigger_table,
    shl.operation,
    shl.status,
    shl.execution_time_ms,
    shl.result_data
FROM sync_hook_logs shl
JOIN sync_hooks sh ON shl.hook_id = sh.hook_id
ORDER BY shl.created_at DESC
LIMIT 20;
```

### Identify Failed Hooks

```sql
SELECT 
    sh.name,
    COUNT(*) as failure_count,
    MAX(shl.created_at) as last_failure,
    shl.result_data->>'error' as error_message
FROM sync_hook_logs shl
JOIN sync_hooks sh ON shl.hook_id = sh.hook_id
WHERE shl.status = 'error'
GROUP BY sh.name, shl.result_data
ORDER BY failure_count DESC;
```

### Performance Analysis

```sql
SELECT 
    sh.name,
    AVG(shl.execution_time_ms) as avg_time,
    MAX(shl.execution_time_ms) as max_time,
    COUNT(*) as execution_count
FROM sync_hook_logs shl
JOIN sync_hooks sh ON shl.hook_id = sh.hook_id
WHERE shl.created_at > NOW() - INTERVAL '7 days'
GROUP BY sh.name
ORDER BY avg_time DESC;
```

## Migration Files

**Created**: `sql/migrations/20251008_sync_hooks_system.sql`
- Creates sync_hooks and sync_hook_logs tables
- Inserts time_clock_aggregation hook
- Includes example inventory_stock_update hook (inactive)

**Applied via**:
```bash
docker exec -i postgres psql -U taf -d devdb < sql/migrations/20251008_sync_hooks_system.sql
```

## Future Extensions

### Inventory Management
- Hook: `inventory_stock_update`
- Trigger: `inventory_txn` PULL/PUSH
- Type: cascade
- Updates: inventory.quantity_on_hand

### Financial Consolidation
- Hook: `gl_account_balances`
- Trigger: `journal_entries` PULL/PUSH
- Type: aggregate
- Calculates: account balances by period

### Project Management
- Hook: `project_progress_update`
- Trigger: `tasks` PULL/PUSH
- Type: derive
- Calculates: project completion percentage

### Notification Triggers
- Hook: `low_inventory_alert`
- Trigger: `inventory` PULL
- Type: custom
- Action: Send notification if below reorder point

## Best Practices

1. **Start Simple**: Implement aggregate hooks first, then cascade, then custom
2. **Test Incrementally**: Test each hook with small datasets before production
3. **Monitor Logs**: Review sync_hook_logs regularly for errors and performance
4. **Use Filters**: Always require date filters for time-based aggregations
5. **Document Config**: Include comments in hook config JSONB explaining formulas
6. **Version Control**: Keep hook definitions in migration files for reproducibility
7. **Inactive Examples**: Include example hooks as is_active=false for documentation

## Troubleshooting

**Hook not executing**:
- Check `is_active = true` in sync_hooks
- Verify trigger_table matches exactly
- Ensure operation is in trigger_operations array
- Check if filters are required but not provided

**Hook failing**:
- Review sync_hook_logs.result_data for error details
- Verify SQL formulas are valid PostgreSQL syntax
- Check that source/target tables exist
- Ensure user has permissions on tables

**Performance issues**:
- Add indexes on frequently queried columns
- Use more specific trigger_table (avoid '*')
- Increase priority number to delay execution
- Consider batch processing for large datasets

## Related Documentation

- `AGENTS.md`: Overall architecture and conventions
- `docs/WorkflowEngine.md`: Workflow triggers and actions
- `docs/FormBuilder.md`: Form metadata and configurations
- `docs/frontend/ModalBuilder.md`: UI module patterns
- `api/services/SyncHookService.php`: Implementation details and inline documentation

---

**Questions or Issues?**
Contact the TAF ERP development team or review the comprehensive documentation in `SyncHookService.php`.