# Design: MySQL Review Persistence

## Technical Approach

Add MySQL persistence as a side-effect inside the existing review webhook flow without changing the successful Gemini response contract. `src/config.ts` will validate DB env vars at startup, `src/database/mysqlPool.ts` will create a `mysql2/promise` pool, `src/repositories/mysqlReviewRepository.ts` will own SQL, and a small persistence service/mapper will translate ActivePiece query params into the `reviews` row shape before the route calls Gemini.

## Architecture Decisions

| Option | Tradeoff | Decision |
|---|---|---|
| `mysql2/promise` pool | Adds one dependency, but supports typed async/await, prepared statements, and pooling in Node/TS | Use `mysql2/promise`; repo has no existing DB convention and already uses promise-based services. |
| Repository + persistence service | More files than inline SQL, but keeps route thin and SQL isolated | Route validates input, service maps/orchestrates, repository upserts. |
| Persist before Gemini | A DB failure prevents response generation, but matches spec failure behavior and avoids replying to unrecorded reviews | Call persistence first; then call `generateResponse` unchanged. |
| Leave `author_url` null | May omit `name` data, but avoids inventing table semantics | Map `name` only to outbound response identity; do not store it as `author_url`. |

## Data Flow

```text
POST /webhook/activepieces/review?...
  └─ authenticateWebhook
  └─ zod query validation + rating normalization
  └─ ReviewPersistenceService.persistFromWebhook
       └─ map ActivePiece fields to ReviewRow
       └─ MySqlReviewRepository.upsert(review)
  └─ GeminiReviewResponseService.generateResponse(existing input)
  └─ 200 { text, name }
```

## File Changes

| File | Action | Description |
|---|---|---|
| `package.json`, `package-lock.json` | Modify | Add `mysql2`. |
| `.env.example` | Modify | Document `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`, `DB_PORT` names only. |
| `src/config.ts` | Modify | Add required DB config with valid port parsing and no default credentials. |
| `src/database/mysqlPool.ts` | Create | Build `Pool` from validated config. |
| `src/repositories/reviewRepository.ts` | Create | Define repository contract and row input type. |
| `src/repositories/mysqlReviewRepository.ts` | Create | Implement parameterized upsert SQL. |
| `src/services/reviewPersistenceService.ts` | Create | Map validated webhook input into repository input. |
| `src/routes/reviewRoute.ts` | Modify | Accept optional persistence service dependency; persist before Gemini. |
| `src/server/app.ts` | Modify | Wire pool, repository, persistence service, existing Gemini service. |
| `src/**/*.test.ts` | Modify/Create | Add strict TDD unit/integration coverage. |

## Interfaces / Contracts

```ts
export type PersistableReview = {
  reviewId: string;
  authorName: string | null;
  text: string;
  rating: 1 | 2 | 3 | 4 | 5;
  source: 'Google';
  authorUrl: null;
};

export interface ReviewRepository {
  upsert(review: PersistableReview): Promise<void>;
}
```

Mapping: `id -> review_id`, `nombre || null -> author_name`, `comentario -> text`, normalized `puntuacion -> rating`, `source -> 'Google'`, `author_profile_url`, `author_id`, `language`, `published_time`, `last_edited_time`, `images`, `response`, and `author_url` remain null/default unless later confirmed.

Repository SQL uses placeholders:

```sql
INSERT INTO reviews (review_id, author_name, text, rating, source, author_url)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  author_name = VALUES(author_name),
  text = VALUES(text),
  rating = VALUES(rating),
  source = VALUES(source),
  author_url = VALUES(author_url)
```

## Testing Strategy

| Layer | What to Test | Approach |
|---|---|---|
| Unit | DB env parsing, rating conversion, mapper null/default behavior | Vitest first; isolate modules with env reset/dynamic import. |
| Unit | Repository SQL and parameters | Mock `Pool.execute`; assert SQL contains upsert and values are secret-free. |
| Integration | Route persists then calls Gemini and preserves `{ text, name }` | Inject fake repository/service into `createReviewRouter`. |
| Integration | Persistence failure returns failure without Gemini call or secret logging | Mock failure; spy on `console.error`; expect no env values in logs. |
| E2E | Full MySQL write | Not required by current OpenSpec config; can be added later with test DB. |

## Migration / Rollout

No schema migration in this change. Roll out by setting `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`, and `DB_PORT` in the runtime environment. Rollback removes persistence wiring while preserving Gemini route behavior.

## Open Questions

- [ ] Confirm the exact existing `reviews` table column names/types before implementation; design assumes columns named in the spec.