# Proposal: MySQL Review Persistence

## Intent

Persist incoming ActivePiece review payloads into MySQL so webhook deliveries are durably stored in `reviews` while preserving the existing reply-generation flow.

## Scope

### In Scope
- Add MySQL configuration via `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`, `DB_PORT`.
- Introduce a repository/service to upsert review rows by `review_id`.
- Map webhook payload fields into the target schema and keep response generation intact.

### Out of Scope
- Schema changes to `reviews`.
- Backfills, migrations, or analytics queries.
- Any auth/secret management beyond env-based config.

## Capabilities

### New Capabilities
- `review-persistence`: store incoming review events in MySQL with idempotent upsert behavior.

### Modified Capabilities
- `review-webhook`: webhook handling now includes persistence before/alongside response generation.

## Approach

- Keep the route thin; move DB access into a repository and orchestration into a service.
- Upsert on `review_id` to tolerate duplicate deliveries.
- Normalize payloads before persistence; serialize `images` as JSON text when present.

## Affected Areas

| Area | Impact | Description |
|------|--------|-------------|
| `src/routes/reviewRoute.ts` | Modified | Pass parsed payload into persistence/orchestration flow. |
| `src/services/reviewResponseService.ts` | Modified | Accept persisted review input fields as needed. |
| `src/server/app.ts` | Modified | Wire MySQL repository/service dependencies. |
| `src/config.ts` | Modified | Validate DB env vars. |
| `package.json` | Modified | Add MySQL driver dependency. |

## Risks

| Risk | Likelihood | Mitigation |
|------|------------|------------|
| Payload fields may not match schema exactly | Medium | Confirm field mapping before implementation. |
| Duplicate webhook deliveries | High | Use `INSERT ... ON DUPLICATE KEY UPDATE`. |
| Secret leakage | Low | Use env vars only; never hardcode credentials. |

## Rollback Plan

Disable the persistence wiring, remove the repository/service from composition root, and revert to reply-only webhook handling. No schema rollback is required.

## Dependencies

- A reachable MySQL instance configured via env vars.

## Success Criteria

- [ ] Incoming reviews are written to `reviews`.
- [ ] Duplicate deliveries update the existing row, not create duplicates.
- [ ] No DB credentials are committed or logged.
- [ ] Open field-mapping questions are resolved before spec/implementation.