Prompt file imported from ramonsss/MQTT_Projeto_Vena (.github/prompts/phase2-auth-identity.prompt.md). Copyright stays with the author.

Fase 2 — Auth, Identidade e Retenção de Dados

Objetivo: Implementar autenticação Google OAuth no backend, emissão de JWT, endpoints de claim/listagem de dispositivos, configurar mosquitto-go-auth para ACL baseada em JWT, e adicionar políticas de retenção (hypertable + retention policy) para controlar crescimento do banco.

Pré-requisito: Fase 1 concluída (telemetria E2E funcionando — ESP32 publica → worker ingere → REST devolve histórico; testes E1-E4 passando).

Decisões técnicas (resolver ANTES de codificar)

Contratos

REST: POST /auth/google

Request body:

{
  "id_token": "<Google OAuth id_token>"
}

Response 200:

{
  "access_token": "<JWT>",
  "refresh_token": "<opaque-uuid>",
  "token_type": "bearer",
  "expires_in": 3600,
  "user": {
    "id": "uuid",
    "email": "user@gmail.com"
  }
}

Response 401: token inválido ou expirado.

REST: POST /auth/refresh

Request body:

{
  "refresh_token": "<opaque-uuid>"
}

Response 200: mesmo formato do /auth/google (novo access + refresh, refresh antigo revogado).

Response 401: refresh token inválido/expirado/revogado.

REST: POST /devices/{device_id}/claim

Headers: Authorization: Bearer <access_token>

Request body:

{
  "pairing_code": "K7X9M2P4"
}

Response 200:

{
  "device_id": "vena-a0b765c1d2e3",
  "alias": null,
  "claimed_at": "2026-05-21T12:00:00Z"
}

Response 404: device não existe. Response 403: pairing_code inválido. Response 409: device já claimado por este user.

REST: GET /devices

Headers: Authorization: Bearer <access_token>

Response 200:

{
  "devices": [
    {
      "device_id": "vena-a0b765c1d2e3",
      "alias": "Vena Estufa 1",
      "status": "online",
      "last_seen_at": "2026-05-21T12:00:00Z",
      "fw_version": "1.1.0"
    }
  ]
}

REST: PATCH /devices/{device_id}

Headers: Authorization: Bearer <access_token>

Request body:

{
  "alias": "Vena Estufa Norte"
}

Response 200: device atualizado. Response 403: user não é dono do device.

REST: POST /mqtt/credentials

Headers: Authorization: Bearer <access_token>

Response 200:

{
  "mqtt_token": "<JWT MQTT de curta duração>",
  "expires_in": 3600,
  "broker_host": "mqtt.vena.farm",
  "broker_port": 8883
}

REST: POST /devices/{device_id}/provision (admin)

Headers: Authorization: Bearer <access_token> (scope admin — no MVP, qualquer user autenticado)

Response 200:

{
  "device_jwt": "<JWT MQTT longo-prazo com scope=device>",
  "expires_in": 31536000
}

Broker Auth Endpoints (chamados pelo mosquitto-go-auth)

POST /mqtt/auth

Form body: username=<jwt>&password=&clientid=<clientId> Response: 200 OK (auth ok) ou 403 (deny).

POST /mqtt/acl

Form body: username=<jwt>&topic=vena/xyz/telemetry&clientid=<clientId>&acc=1 (1=sub, 2=pub) Response: 200 OK (allowed) ou 403 (denied).

POST /mqtt/superuser

Form body: username=<jwt> Response: 403 sempre (nenhum superuser via HTTP).

Checklist de implementação

A. Retenção & Agregação (TimescaleDB)

B. Auth — Models & Infra

C. Auth — Service & Routes

D. Devices — Claim & Management

E. MQTT Credentials & Broker Auth

F. Integração & Wiring

G. Firmware — MQTT Auth

H. Testes

Modelo SQLAlchemy: RefreshToken

class RefreshToken(Base):
    __tablename__ = "refresh_tokens"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    user_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    token_hash: Mapped[str] = mapped_column(Text, nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    user: Mapped["User"] = relationship()

Migrations SQL: TimescaleDB Hypertable & Retenção

A1 — Converter telemetry_raw em hypertable

-- Requer extensão ativa (já presente no timescale/timescaledb image)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Converter tabela existente (migrate_data copia rows para chunks)
SELECT create_hypertable(
    'telemetry_raw',
    'ts',
    migrate_data => true,
    chunk_time_interval => INTERVAL '1 day'
);

-- Política de retenção: apagar chunks > 90 dias automaticamente
SELECT add_retention_policy('telemetry_raw', INTERVAL '90 days');

A2 — Continuous aggregate: telemetry_hourly

CREATE MATERIALIZED VIEW telemetry_hourly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', ts) AS bucket,
    AVG(ambient_t)  AS ambient_t_avg,
    MIN(ambient_t)  AS ambient_t_min,
    MAX(ambient_t)  AS ambient_t_max,
    AVG(ambient_h)  AS ambient_h_avg,
    MIN(ambient_h)  AS ambient_h_min,
    MAX(ambient_h)  AS ambient_h_max,
    AVG(diss_t)     AS diss_t_avg,
    MIN(diss_t)     AS diss_t_min,
    MAX(diss_t)     AS diss_t_max,
    AVG(diss_h)     AS diss_h_avg,
    MIN(diss_h)     AS diss_h_min,
    MAX(diss_h)     AS diss_h_max,
    AVG(setpoint)   AS setpoint_avg,
    AVG(pid_out)    AS pid_out_avg,
    COUNT(*)        AS sample_count
FROM telemetry_raw
GROUP BY device_id, bucket
WITH NO DATA;

-- Refresh automático: materializa últimas 3h a cada 30 minutos
SELECT add_continuous_aggregate_policy('telemetry_hourly',
    start_offset    => INTERVAL '3 hours',
    end_offset      => INTERVAL '30 minutes',
    schedule_interval => INTERVAL '30 minutes'
);

-- Retenção do agregado horário: 2 anos
SELECT add_retention_policy('telemetry_hourly', INTERVAL '2 years');

A3 — Continuous aggregate: telemetry_daily

CREATE MATERIALIZED VIEW telemetry_daily
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 day', ts) AS bucket,
    AVG(ambient_t)  AS ambient_t_avg,
    MIN(ambient_t)  AS ambient_t_min,
    MAX(ambient_t)  AS ambient_t_max,
    AVG(ambient_h)  AS ambient_h_avg,
    MIN(ambient_h)  AS ambient_h_min,
    MAX(ambient_h)  AS ambient_h_max,
    AVG(diss_t)     AS diss_t_avg,
    MIN(diss_t)     AS diss_t_min,
    MAX(diss_t)     AS diss_t_max,
    AVG(diss_h)     AS diss_h_avg,
    MIN(diss_h)     AS diss_h_min,
    MAX(diss_h)     AS diss_h_max,
    AVG(setpoint)   AS setpoint_avg,
    AVG(pid_out)    AS pid_out_avg,
    COUNT(*)        AS sample_count
FROM telemetry_raw
GROUP BY device_id, bucket
WITH NO DATA;

-- Refresh: últimas 2 dias a cada 6 horas
SELECT add_continuous_aggregate_policy('telemetry_daily',
    start_offset    => INTERVAL '2 days',
    end_offset      => INTERVAL '6 hours',
    schedule_interval => INTERVAL '6 hours'
);

-- Sem retention policy (manter indefinidamente)

Fluxo de Auth — Diagrama

┌─────────┐        ┌─────────────┐         ┌──────────────────┐
│  App    │        │  Backend    │         │  Google OAuth    │
└────┬────┘        └──────┬──────┘         └────────┬─────────┘
     │  1. Google Sign-In │                         │
     │─────────────────────────────────────────────▶│
     │  2. id_token       │                         │
     │◀─────────────────────────────────────────────│
     │                    │                         │
     │  3. POST /auth/google {id_token}             │
     │───────────────────▶│                         │
     │                    │  4. verify_oauth2_token  │
     │                    │────────────────────────▶│
     │                    │  5. {sub, email}         │
     │                    │◀────────────────────────│
     │                    │  6. upsert User + emit JWT
     │  7. {access, refresh, user}                  │
     │◀───────────────────│                         │
     │                    │                         │
     │  8. POST /mqtt/credentials                   │
     │───────────────────▶│                         │
     │                    │  9. list user devices    │
     │                    │  10. sign mqtt_jwt       │
     │  11. {mqtt_token, broker_host}               │
     │◀───────────────────│                         │
     │                    │                         │
     │  12. MQTT connect(username=mqtt_jwt)          │
     │──────────────────────────────▶ Mosquitto     │
     │                    │          │ 13. POST /mqtt/auth
     │                    │◀─────────│              │
     │                    │  14. 200 │              │
     │                    │─────────▶│              │

Fluxo de Claim — Diagrama

┌─────────┐         ┌──────────┐         ┌──────────┐
│  App    │         │ Backend  │         │ Postgres │
└────┬────┘         └─────┬────┘         └─────┬────┘
     │                    │                    │
     │ 1. Scan QR → extrai pairing_code       │
     │ 2. POST /devices/{id}/claim             │
     │      {pairing_code}                     │
     │───────────────────▶│                    │
     │                    │ 3. SELECT device   │
     │                    │───────────────────▶│
     │                    │ 4. device row      │
     │                    │◀───────────────────│
     │                    │                    │
     │                    │ 5. bcrypt.verify(code, hash)
     │                    │  → match?          │
     │                    │                    │
     │                    │ 6. INSERT user_devices
     │                    │───────────────────▶│
     │                    │ 7. ok              │
     │                    │◀───────────────────│
     │  8. 200 {claimed}  │                    │
     │◀───────────────────│                    │

Configuração mosquitto-go-auth

# mosquitto.conf (trecho relevante)
listener 1883
protocol mqtt

# Anonymous OFF quando auth ativado
allow_anonymous false

# Plugin
plugin /mosquitto/go-auth.so
auth_plugin_deny_special_chars true

# HTTP backend
auth_opt_backends http
auth_opt_http_host backend
auth_opt_http_port 8000
auth_opt_http_getuser_uri /mqtt/auth
auth_opt_http_aclcheck_uri /mqtt/acl
auth_opt_http_superuser_uri /mqtt/superuser
auth_opt_http_method POST
auth_opt_http_params_mode form
auth_opt_http_timeout 5

# Cache para não bater no backend a cada msg
auth_opt_cache true
auth_opt_cache_type go-cache
auth_opt_cache_reset true
auth_opt_auth_cache_seconds 300
auth_opt_acl_cache_seconds 300

Critérios de aceite (DoD)

Armadilhas conhecidas

Ordem de execução recomendada

 1. [Backend]  A1: Migration — hypertable + retention policy
 2. [Backend]  A2-A3: Migrations — continuous aggregates (hourly + daily)
 3. [Backend]  B1-B3: Config + RefreshToken model + migration
 4. [Backend]  C1-C3: Auth package — google.py + jwt.py
 5. [Backend]  C4-C6: Auth deps + service + schemas
 6. [Backend]  C7: Auth routes (POST /auth/google, /auth/refresh)
 7. [Backend]  D1-D5: Devices package — claim, list, alias, pairing
 8. [Backend]  E1-E2: MQTT credentials + provision routes
 9. [Backend]  E3-E5: Broker auth endpoints (/mqtt/auth, /mqtt/acl)
10. [Backend]  F1-F2: Wiring (main.py routers + .env)
11. [Infra]    F3: mosquitto.conf com mosquitto-go-auth
12. [Backend]  H1-H10: Testes de integração
13. [Firmware] G1-G3: MQTT auth no firmware (NVS + setCredentials)
14. [E2E]     Smoke test completo: firmware com JWT → broker autentica → telemetria persiste

Métricas de sucesso (smoke test final)

1. App (simulado via curl/httpx) faz login Google → obtém access token → lista devices (vazio).
2. POST /devices/vena-xxx/claim com pairing_code correto → device aparece em GET /devices.
3. POST /mqtt/credentials → retorna JWT MQTT com claim devices: ["vena-xxx"].
4. mosquitto_pub -u <mqtt_jwt> -t vena/vena-xxx/telemetry -m '{...}' → aceita.
5. mosquitto_pub -u <mqtt_jwt> -t vena/outro-device/telemetry -m '{...}' → rejeita (ACL).
6. SELECT * FROM timescaledb_information.hypertables → telemetry_raw é hypertable.
7. ESP32 com device JWT no NVS conecta ao broker com allow_anonymous false → telemetria flui.
8. Após 90+ dias simulados (ou SELECT drop_chunks(...) manual), dados antigos desaparecem de telemetry_raw mas existem em telemetry_hourly.

Dependências a instalar

# Backend (adicionar ao pyproject.toml)
google-auth>=2.29        # validar id_token Google
bcrypt>=4.2              # hash pairing_code (já instalado)
python-jose[cryptography]>=3.3  # JWT (já instalado)

# Docker (mudar imagem mosquitto)
iegomez/mosquitto-go-auth:latest  # ou build custom com plugin