# review-persistence Specification

## Purpose

Persist ActivePiece review webhook payloads into MySQL while preserving the existing response-generation flow.

## Requirements

### Requirement: Validate MySQL config

The system MUST require `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`, and `DB_PORT` from environment variables and MUST fail fast when any are missing or invalid.

#### Scenario: Missing DB config

- GIVEN one or more required DB env vars are absent
- WHEN the application starts
- THEN startup fails with a configuration error

#### Scenario: Invalid DB port

- GIVEN `DB_PORT` is not a valid port number
- WHEN the application starts
- THEN startup fails with a configuration error

### Requirement: Store reviews with idempotent upsert

The system MUST persist each review into `reviews` using `review_id` as the unique key and MUST update the existing row on duplicate delivery.

#### Scenario: First delivery inserts a row

- GIVEN a valid review payload
- WHEN the webhook is processed
- THEN a row is inserted into MySQL

#### Scenario: Duplicate delivery updates the same row

- GIVEN a row already exists for the same `review_id`
- WHEN the same payload is processed again
- THEN the existing row is updated, not duplicated

### Requirement: Map ActivePiece fields to review columns

The system MUST map `id` to `review_id`, `nombre` to `author_name`, `comentario` to `text`, and `puntuacion` labels `ONE` through `FIVE` to integer ratings `1` through `5`. `source` MUST default to `Google` unless an explicit source is available.

#### Scenario: Rating is converted from enum label

- GIVEN `puntuacion` is `FIVE`
- WHEN the payload is persisted
- THEN `rating` is stored as `5`

#### Scenario: Missing fields remain null

- GIVEN optional fields are absent from the payload
- WHEN the payload is persisted
- THEN `author_profile_url`, `author_id`, `language`, `published_time`, `last_edited_time`, `images`, and `response` remain NULL or their table defaults

### Requirement: Handle author URL storage safely

The system SHOULD store `name` in `author_url` only if the existing table semantics and current code contract support that meaning; otherwise it MUST leave `author_url` nullable and MUST NOT invent a value.

#### Scenario: No safe mapping exists

- GIVEN `name` is present but `author_url` semantics are not confirmed
- WHEN the payload is persisted
- THEN `author_url` remains NULL

### Requirement: Preserve response generation and report failures

The system MUST continue generating the webhook response even when persistence succeeds, and MUST surface persistence failures as a failed request without leaking secrets.

#### Scenario: Persistence succeeds

- GIVEN MySQL accepts the write
- WHEN the webhook is processed
- THEN the reply flow continues normally

#### Scenario: Persistence fails

- GIVEN the database is unreachable or the write fails
- WHEN the webhook is processed
- THEN the request fails with an error and no credentials are logged