# Phase 7: Integration Layer — Implementation Plan

## Architecture

### Data Model

**New table: `webhook_destinations`**
```sql
CREATE TABLE webhook_destinations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    site_id INTEGER NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    name TEXT NOT NULL DEFAULT 'Integration',
    type TEXT NOT NULL,  -- 'webhook', 'google_sheets', 'slack', 'discord', 'telegram', 'airtable', 'notion', 'csv_export', 'json_export'
    url TEXT,            -- webhook URL or API endpoint (NULL for export types)
    config TEXT,         -- JSON: additional config (channel, auth, headers, etc.)
    enabled INTEGER DEFAULT 1,
    last_status TEXT,    -- 'delivered', 'failed', null
    last_error TEXT,
    last_delivered_at TIMESTAMP,
    success_count INTEGER DEFAULT 0,
    failure_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_destinations_site ON webhook_destinations(site_id);
```

**Extend `webhook_logs`:**
- Add `destination_id INTEGER REFERENCES webhook_destinations(id)`
- Backfill existing entries with `destination_id = NULL` (legacy webhook_url)

### Integration Formatters

Each integration type has a formatter function: `format_payload(type, config, submission, field_config, site)` → `(url, payload, headers)`

| Type | URL Source | Payload Format | Extra Config |
|------|-----------|---------------|--------------|
| webhook | url field | Standard JSON (existing format) | Custom headers |
| google_sheets | Apps Script URL | CSV line or JSON | Sheet name |
| slack | Incoming Webhook URL | Blocks/markdown | Channel (optional) |
| discord | Webhook URL | Embed + fields | Username, avatar, color |
| telegram | Bot API URL | Formatted message | chat_id |
| airtable | API endpoint | JSON record | api_key, base_id, table |
| notion | API endpoint | JSON page | api_key, database_id |
| csv_export | N/A (download) | CSV response | N/A |
| json_export | N/A (download) | JSON response | N/A |

### Implementation Order

1. **7.1** Migration: `webhook_destinations` table + extend `webhook_logs`
2. **7.2** `app/services/integrations.py`: formatter functions
3. **7.2** Refactor `app/services/webhook.py`: iterate destinations, use formatters
4. **7.2** Refactor retry worker: per-destination retry
5. **7.11** Update models.py: destination delivery tracking functions
6. **7.3** UI: Integrations tab in edit_site.html
7. **7.4-7.10** Integration templates + setup docs (UI components)
8. **7.8** CSV/JSON export endpoints
9. **7.12** Delivery status UI (per-destination stats)

### Backward Compatibility

- Legacy `sites.webhook_url` + `sites.webhook_enabled` still works
- If `webhook_enabled=1` and no destinations exist, fire the legacy URL
- If destinations exist, fire destinations (ignoring legacy webhook_url)
- Old webhook_logs entries with NULL destination_id treated as legacy