# app/tests/test_unitarios.py
import pytest
from datetime import date
from src.dominio import calcular_disponibilidad, validar_reserva, ErrorValidacion

class TestDisponibilidad:
    """The core business rule: never more bookings than capacity."""

    def test_refugio_vacio_ofrece_toda_la_capacidad(self):
        assert calcular_disponibilidad(capacidad=40, ocupadas=0) == 40

    def test_descuenta_las_plazas_ocupadas(self):
        assert calcular_disponibilidad(capacidad=40, ocupadas=15) == 25

    def test_refugio_lleno_ofrece_cero(self):
        assert calcular_disponibilidad(capacidad=40, ocupadas=40) == 0

    def test_nunca_devuelve_negativo(self):
        """Edge case: if a bug left overbooking in the data, the function
        must return 0, not a negative number that the interface would
        display as '-3 places free'."""
        assert calcular_disponibilidad(capacidad=40, ocupadas=45) == 0

class TestValidacion:
    @pytest.mark.parametrize("plazas", [0, -1, 13, 999])
    def test_rechaza_plazas_fuera_de_rango(self, plazas):
        with pytest.raises(ErrorValidacion):
            validar_reserva(plazas=plazas, fecha=date(2026, 8, 15))

    def test_rechaza_fecha_pasada(self):
        with pytest.raises(ErrorValidacion, match="pasado"):
            validar_reserva(plazas=2, fecha=date(2020, 1, 1))

    @pytest.mark.parametrize("correo", ["", "sin-arroba", "a@", "@b.com"])
    def test_rechaza_correo_invalido(self, correo):
        with pytest.raises(ErrorValidacion):
            validar_reserva(plazas=2, fecha=date(2026, 8, 15), email=correo)

# Local emulators, free and without touching the cloud
gcloud emulators firestore start --host-port=localhost:8080
gcloud emulators pubsub    start --host-port=localhost:8085

# The client libraries detect them through an environment variable
export FIRESTORE_EMULATOR_HOST=localhost:8080
export PUBSUB_EMULATOR_HOST=localhost:8085

# app/tests/conftest.py
import pytest, subprocess, time, os
import psycopg

@pytest.fixture(scope="session")
def bd_efimera():
    """PostgreSQL in a container, created and destroyed by the test session.
    Cost: €0. Does not touch the cloud."""
    subprocess.run([
        "docker", "run", "-d", "--name", "pg-pruebas",
        "-e", "POSTGRES_PASSWORD=pruebas",
        "-e", "POSTGRES_DB=reservas",
        "-p", "55432:5432", "postgres:16-alpine",
    ], check=True)

    dsn = "postgresql://postgres:pruebas@localhost:55432/reservas"
    for _ in range(30):                       # wait until it accepts connections
        try:
            psycopg.connect(dsn).close()
            break
        except Exception:
            time.sleep(1)

    # Apply the SAME migrations as in production
    with psycopg.connect(dsn) as con:
        for f in sorted(os.listdir("data/migraciones")):
            with open(f"data/migraciones/{f}") as fh:
                con.execute(fh.read())
        con.commit()

    yield dsn

    subprocess.run(["docker", "rm", "-f", "pg-pruebas"], check=True)

# app/tests/test_integracion.py
import pytest, psycopg
from concurrent.futures import ThreadPoolExecutor
from src.repositorio import crear_reserva, ErrorSinPlazas

def test_no_hay_sobreventa_con_concurrencia(bd_efimera):
    """The case that breaks badly built booking systems:
    twenty people try to book the last place at the same time."""
    with psycopg.connect(bd_efimera) as con:
        con.execute("INSERT INTO refugio (nombre, altitud_m, capacidad) "
                    "VALUES ('Refugio Prueba', 2000, 1)")
        con.commit()

    def intentar():
        try:
            with psycopg.connect(bd_efimera) as c:
                crear_reserva(c, refugio_id=1, fecha="2026-08-15", plazas=1,
                              titular="Fictitious", email="[email protected]")
            return "ok"
        except ErrorSinPlazas:
            return "sin_plazas"

    with ThreadPoolExecutor(max_workers=20) as ex:
        resultados = list(ex.map(lambda _: intentar(), range(20)))

    # EXACTLY one must succeed. Not zero, not two.
    assert resultados.count("ok") == 1, f"Overbooking: {resultados.count('ok')} bookings"
    assert resultados.count("sin_plazas") == 19

    with psycopg.connect(bd_efimera) as con:
        total = con.execute("SELECT COALESCE(SUM(plazas),0) FROM reserva "
                            "WHERE refugio_id=1 AND estado='confirmada'").fetchone()[0]
    assert total == 1

def crear_reserva(con, refugio_id, fecha, plazas, titular, email):
    with con.transaction():
        # The row lock serialises the concurrent attempts
        cap = con.execute(
            "SELECT capacidad FROM refugio WHERE id = %s FOR UPDATE",
            (refugio_id,)).fetchone()[0]
        ocupadas = con.execute(
            "SELECT COALESCE(SUM(plazas),0) FROM reserva "
            "WHERE refugio_id=%s AND fecha=%s AND estado='confirmada'",
            (refugio_id, fecha)).fetchone()[0]
        if ocupadas + plazas > cap:
            raise ErrorSinPlazas()
        con.execute("INSERT INTO reserva (refugio_id, fecha, plazas, "
                    "nombre_titular, email_titular) VALUES (%s,%s,%s,%s,%s)",
                    (refugio_id, fecha, plazas, titular, email))

# app/tests/test_e2e.py
import os, time, uuid, pytest, requests
from google.cloud import bigquery

BASE = os.environ["URL_DEV"]

@pytest.mark.e2e
def test_flujo_completo_reserva_llega_a_analitica():
    """The whole critical path: book → event → BigQuery."""
    marca = str(uuid.uuid4())[:8]
    r = requests.post(f"{BASE}/api/reservas", timeout=30, json={
        "refugio_id": 1, "fecha": "2027-01-15", "plazas": 1,
        "nombre_titular": f"E2E {marca}",          # FICTITIOUS data
        "email_titular": f"e2e-{marca}@example.com",
    })
    assert r.status_code == 201
    reserva_id = r.json()["id"]

    # Propagation through Pub/Sub is not instant: poll with a limit
    bq = bigquery.Client()
    consulta = """
      SELECT COUNT(*) AS n
      FROM `refugio-datos.refugio_analitica.reservas_eventos`
      WHERE reserva_id = @rid AND DATE(ocurrido_en) = CURRENT_DATE()
    """
    cfg = bigquery.QueryJobConfig(query_parameters=[
        bigquery.ScalarQueryParameter("rid", "STRING", reserva_id)])

    for _ in range(12):                     # up to 2 minutes
        n = list(bq.query(consulta, job_config=cfg).result())[0].n
        if n == 1:
            return
        time.sleep(10)
    pytest.fail("The event did not reach BigQuery within 2 minutes")

@pytest.mark.e2e
def test_sondas_de_salud_responden():
    assert requests.get(f"{BASE}/salud/vivo", timeout=10).status_code == 200
    assert requests.get(f"{BASE}/salud/arranque", timeout=10).status_code == 200

steps:
  - id: unitarias-e-integracion
    name: python:3.12-slim
    entrypoint: bash
    args:
      - -c
      - |
        pip install --no-cache-dir -r app/requirements.txt -r app/requirements-dev.txt
        cd app && python -m pytest tests/ -v -m "not e2e" --tb=short \
          --junitxml=/workspace/resultados.xml

  # ... build, publish, deploy ...

  - id: e2e
    name: python:3.12-slim
    entrypoint: bash
    args:
      - -c
      - |
        pip install --no-cache-dir -r app/requirements-dev.txt
        export URL_DEV=$(gcloud run services describe refugio-web \
          --region=europe-west1 --format='value(status.url)')
        cd app && python -m pytest tests/ -v -m e2e
    waitFor: [desplegar]

terraform fmt -check -recursive        # consistent formatting
terraform validate                     # syntax and references
terraform plan -detailed-exitcode      # 0=no changes, 2=changes, 1=error

# .github/workflows/plan.yml
name: Terraform plan
on: pull_request

permissions:
  contents: read
  id-token: write
  pull-requests: write

jobs:
  plan:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: google-github-actions/auth@v2
        with:
          workload_identity_provider: ${{ vars.WIF_PROVIDER }}
          service_account: ${{ vars.SA_PLAN }}      # READ-ONLY SA
      - uses: hashicorp/setup-terraform@v3
      - run: terraform -chdir=infra/envs/dev init
      - id: plan
        run: terraform -chdir=infra/envs/dev plan -no-color -out=plan.tfplan
      - name: Publish the plan as a comment
        uses: actions/github-script@v7
        with:
          script: |
            const salida = `#### Terraform plan
            \`\`\`
            ${{ steps.plan.outputs.stdout }}
            \`\`\``;
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner, repo: context.repo.repo, body: salida
            });

# tflint: syntax errors, invalid arguments, provider best practices
tflint --init && tflint --recursive

# checkov: security and compliance checks over the code
checkov -d infra/ --framework terraform --compact

# checkov:skip=CKV_GCP_79:High availability not applicable; portfolio project
# with a €12/month budget. See docs/adr/ADR-002.

  - id: analisis-iac
    name: bridgecrew/checkov:latest
    args: ["-d", "infra/", "--compact", "--soft-fail"]

#!/usr/bin/env bash
# scripts/prueba-reconstruccion.sh
set -euo pipefail

ENTORNO="dev"
INICIO=$(date +%s)

echo "=== 1. Initial state ==="
terraform -chdir="infra/envs/${ENTORNO}" state list | wc -l

echo "=== 2. DESTROY ==="
terraform -chdir="infra/envs/${ENTORNO}" destroy -auto-approve

echo "=== 3. Check that nothing is left ==="
gcloud run services list      --project="refugio-${ENTORNO}" --format="value(name)"
gcloud sql instances list     --project="refugio-${ENTORNO}" --format="value(name)"
gcloud compute networks list  --project="refugio-${ENTORNO}" --format="value(name)"

echo "=== 4. REBUILD ==="
terraform -chdir="infra/envs/${ENTORNO}" apply -auto-approve

echo "=== 5. Migrations and data ==="
./scripts/migrar.sh "${ENTORNO}"
python data/seed/generar.py --entorno="${ENTORNO}"

echo "=== 6. Deploy the last known image ==="
./scripts/desplegar.sh "${ENTORNO}" "$(git rev-parse --short HEAD)"

echo "=== 7. Check that it works ==="
URL=$(gcloud run services describe refugio-web --region=europe-west1 \
      --project="refugio-${ENTORNO}" --format='value(status.url)')
CODIGO=$(curl -s -o /dev/null -w '%{http_code}' "${URL}/salud/arranque")
test "${CODIGO}" = "200" || { echo "FAILURE: the app does not respond"; exit 1; }

echo "=== 8. End-to-end test ==="
URL_DEV="${URL}" python -m pytest app/tests/ -m e2e -q

FIN=$(date +%s)
echo "✅ FULL REBUILD in $(( (FIN-INICIO)/60 )) minutes"

gcloud artifacts docker images scan \
  "europe-west1-docker.pkg.dev/refugio-dev/refugio-imagenes/web:latest" \
  --format="value(response.scan)"

# And to list the findings
gcloud artifacts docker images list-vulnerabilities <SCAN_RESULT> \
  --format="table(vulnerability.effectiveSeverity, vulnerability.shortDescription)"

#!/usr/bin/env bash
# scripts/auditoria-seguridad.sh
set -uo pipefail
PROY="${1:?Usage: $0 <project>}"
FALLOS=0
ok()   { echo "  ✅ $1"; }
falla(){ echo "  ❌ $1"; FALLOS=$((FALLOS+1)); }
avisa(){ echo "  ⚠️  $1"; }

echo "=== SECURITY AUDIT: ${PROY} ==="

echo "[1] Primitive roles on service accounts"
N=$(gcloud projects get-iam-policy "$PROY" --format=json |
    jq '[.bindings[] | select(.role|test("roles/(owner|editor)")) |
         .members[] | select(startswith("serviceAccount:"))] | length')
[ "$N" -eq 0 ] && ok "No SA with owner/editor" || falla "$N accounts with a primitive role"

echo "[2] User-managed service account keys"
TOTAL=0
for SA in $(gcloud iam service-accounts list --project="$PROY" --format="value(email)"); do
  K=$(gcloud iam service-accounts keys list --iam-account="$SA" --managed-by=user \
      --format="value(name)" 2>/dev/null | wc -l)
  TOTAL=$((TOTAL+K))
  [ "$K" -gt 0 ] && echo "     · $SA has $K key(s)"
done
[ "$TOTAL" -eq 0 ] && ok "Zero JSON keys" || falla "$TOTAL downloadable keys"

echo "[3] Publicly accessible buckets"
PUB=0
for B in $(gcloud storage buckets list --project="$PROY" --format="value(name)"); do
  if gcloud storage buckets get-iam-policy "gs://$B" --format=json |
     jq -e '.bindings[]?.members[]? | select(. == "allUsers" or . == "allAuthenticatedUsers")' >/dev/null 2>&1; then
    echo "     · gs://$B is PUBLIC"; PUB=$((PUB+1))
  fi
done
[ "$PUB" -eq 0 ] && ok "No public bucket" || falla "$PUB public bucket(s)"

echo "[4] Databases with a public IP"
for I in $(gcloud sql instances list --project="$PROY" --format="value(name)"); do
  IPV4=$(gcloud sql instances describe "$I" --project="$PROY" \
         --format="value(settings.ipConfiguration.ipv4Enabled)")
  [ "$IPV4" = "False" ] && ok "$I has no public IP" || falla "$I HAS A PUBLIC IP"
done

echo "[5] Secrets in the repository"
if git log -p --all 2>/dev/null | grep -Ei '(password|api[_-]?key|secret|BEGIN (RSA|PRIVATE))' \
   | grep -v 'secret_id\|secretAccessor\|SecretManager\|secret_key_ref\|refugio-db-password' \
   | head -5 | grep -q .; then
  falla "Possible secrets in the git history — REVIEW"
else
  ok "No obvious secrets in the history"
fi

echo "[6] Credential files in the tree"
if find . -name "*.json" -not -path "./node_modules/*" -not -path "./.git/*" \
   -exec grep -l '"type": *"service_account"' {} \; 2>/dev/null | grep -q .; then
  falla "There are service account key files in the repository"
else
  ok "No credential files"
fi

echo "[7] Cloud Run services without authentication"
for S in $(gcloud run services list --project="$PROY" --region=europe-west1 --format="value(name)"); do
  if gcloud run services get-iam-policy "$S" --project="$PROY" --region=europe-west1 \
     --format=json | jq -e '.bindings[]?.members[]? | select(. == "allUsers")' >/dev/null 2>&1; then
    avisa "$S is public (correct if it is the web app; review otherwise)"
  else
    ok "$S requires authentication"
  fi
done

echo "[8] Admin activity audit logs"
gcloud logging read 'logName:"cloudaudit.googleapis.com%2Factivity"' \
  --project="$PROY" --limit=1 --format="value(timestamp)" | grep -q . \
  && ok "Audit logs present" || avisa "No recent activity recorded"

echo
echo "=== RESULT: ${FALLOS} failure(s) ==="
exit $((FALLOS > 0))

DOMINIO="refugioreserva.example"

# Certificate: issuer and validity
echo | openssl s_client -connect "${DOMINIO}:443" -servername "${DOMINIO}" 2>/dev/null |
  openssl x509 -noout -subject -issuer -dates

# TLS versions: 1.2 and 1.3 yes; 1.0 and 1.1 must fail
for V in tls1 tls1_1 tls1_2 tls1_3; do
  printf "%-8s " "$V"
  echo | openssl s_client -"$V" -connect "${DOMINIO}:443" 2>/dev/null | \
    grep -q "Verify return code: 0" && echo "accepts" || echo "rejects"
done

# Security headers
curl -sI "https://${DOMINIO}" | grep -iE \
  'strict-transport|x-content-type|x-frame|content-security|referrer-policy'

# HTTP must redirect
curl -sI "http://${DOMINIO}" | head -1

# What can my application service account actually do?
gcloud projects get-iam-policy refugio-prod --format=json |
  jq -r --arg sa "serviceAccount:[email protected]" \
     '.bindings[] | select(.members[]? == $sa) | .role'

# Check a specific permission it should NOT have
gcloud policy-troubleshoot iam \
  "//cloudresourcemanager.googleapis.com/projects/refugio-prod" \
  --principal-email="[email protected]" \
  --permission="resourcemanager.projects.setIamPolicy"
# Expected: NOT_GRANTED

// pruebas/carga.js  —  run with: k6 run pruebas/carga.js
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Rate } from 'k6/metrics';

const erroresNegocio = new Rate('errores_negocio');

export const options = {
  stages: [
    { duration: '1m', target: 10 },   // warm-up
    { duration: '2m', target: 50 },   // expected load
    { duration: '2m', target: 100 },  // double that
    { duration: '1m', target: 0 },    // cool-down
  ],
  thresholds: {
    http_req_duration:  ['p(95)<1000', 'p(99)<2000'],
    http_req_failed:    ['rate<0.01'],
    errores_negocio:    ['rate<0.05'],
  },
};

const BASE = __ENV.URL;

export default function () {
  // 80% queries, 20% bookings: a realistic profile, not all writes
  if (Math.random() < 0.8) {
    const r = http.get(`${BASE}/api/disponibilidad?refugio_id=1&fecha=2027-01-15`);
    check(r, { 'query 200': (x) => x.status === 200 });
    erroresNegocio.add(r.status >= 500);
  } else {
    const r = http.post(`${BASE}/api/reservas`, JSON.stringify({
      refugio_id: Math.ceil(Math.random() * 12),
      fecha: '2027-01-15', plazas: 1,
      nombre_titular: 'Fictitious Load',
      email_titular: `carga-${__VU}-${__ITER}@example.com`,
    }), { headers: { 'Content-Type': 'application/json' } });
    // 409 (no places) is correct, not a system error
    check(r, { 'booking 201 or 409': (x) => x.status === 201 || x.status === 409 });
    erroresNegocio.add(r.status >= 500);
  }
  sleep(Math.random() * 2);
}

# DRILL IN DEVELOPMENT, with the time recorded
echo "Drill start: $(date)" | tee -a docs/evidencias/ensayo-bd.log

# Stop the database
gcloud sql instances patch refugio-db --project=refugio-dev \
  --activation-policy=NEVER --quiet

# Observe the behaviour
curl -s -o /dev/null -w "Home page: %{http_code} in %{time_total}s\n"  "${URL}/"
curl -s -o /dev/null -w "API:       %{http_code} in %{time_total}s\n"  "${URL}/api/refugios"
curl -s -w "Health: %{http_code}\n" "${URL}/salud/arranque"
curl -s "${URL}/api/refugios" | head -c 300

# Restore
gcloud sql instances patch refugio-db --project=refugio-dev \
  --activation-policy=ALWAYS --quiet

INICIO=$(date +%s)

# 1. What backups do I have?
gcloud sql backups list --instance=refugio-db --project=refugio-dev \
  --format="table(id, windowStartTime, status)"

BACKUP_ID=$(gcloud sql backups list --instance=refugio-db --project=refugio-dev \
            --format="value(id)" --limit=1)

# 2. Restore onto a NEW instance (never onto the original in a drill)
gcloud sql instances create refugio-db-restaurada \
  --project=refugio-dev --region=europe-west1 \
  --database-version=POSTGRES_16 --tier=db-f1-micro

gcloud sql backups restore "${BACKUP_ID}" \
  --restore-instance=refugio-db-restaurada \
  --backup-instance=refugio-db --project=refugio-dev --quiet

# 3. VERIFY that the data is there (this is what almost nobody does)
./cloud-sql-proxy --port 55433 "refugio-dev:europe-west1:refugio-db-restaurada" &
psql -h 127.0.0.1 -p 55433 -U app -d reservas -c "
  SELECT (SELECT count(*) FROM reserva) AS reservas,
         (SELECT count(*) FROM refugio) AS refugios,
         (SELECT max(creada_en) FROM reserva) AS ultima;"

FIN=$(date +%s)
echo "Measured RTO: $(( (FIN-INICIO)/60 )) minutes"

# 4. CLEAN UP — do not leave the restored instance running
gcloud sql instances delete refugio-db-restaurada --project=refugio-dev --quiet

# Get the EXACT digest of the image tested in dev
DIGEST=$(gcloud run services describe refugio-web --region=europe-west1 \
         --project=refugio-dev --format="value(spec.template.spec.containers[0].image)")
echo "Promoting: ${DIGEST}"

# Deploy to production WITHOUT traffic
gcloud run deploy refugio-web --image="${DIGEST}" \
  --region=europe-west1 --project=refugio-prod \
  --no-traffic --tag=candidata

SERVICIO="refugio-web"; REGION="europe-west1"; PROY="refugio-prod"
NUEVA=$(gcloud run revisions list --service=$SERVICIO --region=$REGION \
        --project=$PROY --limit=1 --format="value(name)")

# Phase 1 — 10% for 10 minutes
gcloud run services update-traffic $SERVICIO --region=$REGION --project=$PROY \
  --to-revisions="${NUEVA}=10"

# Observe THAT SPECIFIC new revision, not the whole service
gcloud logging read \
  "resource.type=cloud_run_revision AND
   resource.labels.revision_name=${NUEVA} AND severity>=ERROR" \
  --project=$PROY --limit=20 --freshness=10m

#!/usr/bin/env bash
# scripts/revertir.sh — tested on 2026-10-28, takes 38 seconds
set -euo pipefail
SERVICIO="${1:-refugio-web}"; REGION="europe-west1"; PROY="refugio-prod"

ANTERIOR=$(gcloud run revisions list --service="$SERVICIO" --region="$REGION" \
  --project="$PROY" --format="value(name)" --sort-by="~metadata.creationTimestamp" \
  | sed -n '2p')

echo "Rolling back ${SERVICIO} → ${ANTERIOR}"
gcloud run services update-traffic "$SERVICIO" --region="$REGION" --project="$PROY" \
  --to-revisions="${ANTERIOR}=100"

URL=$(gcloud run services describe "$SERVICIO" --region="$REGION" --project="$PROY" \
      --format='value(status.url)')
sleep 5
curl -s -o /dev/null -w "Check: %{http_code}\n" "${URL}/salud/arranque"
echo "✅ Rolled back to ${ANTERIOR}"

# Operations manual — RefugioReserva

## Contact details and access
Owner: <me>. Projects: refugio-dev, refugio-prod, refugio-datos.
Console: https://console.cloud.google.com/home/dashboard?project=refugio-prod

## P1 — The application returns 5xx errors
1. `gcloud logging read 'severity>=ERROR' --project=refugio-prod --limit=20 --freshness=15m`
2. Does it coincide with a deployment? `gcloud run revisions list --service=refugio-web --limit=3`
3. If it does → **roll back**: `./scripts/revertir.sh refugio-web` (38 s)
4. If not → check the DB: `gcloud sql instances describe refugio-db --format="value(state)"`
5. Write down what happened in `docs/diario.md`

## P2 — The application does not respond at all
1. `curl -sI https://refugioreserva.example`
2. Service alive? `gcloud run services describe refugio-web --format="value(status.conditions)"`
3. Does DNS resolve? `dig +short refugioreserva.example`
4. Valid certificate? `openssl s_client -connect refugioreserva.example:443`

## P3 — Restore the database
See `scripts/restaurar.sh`. **Measured RTO: 22 minutes. RPO: up to 24 h.**

## P4 — Runaway cost
1. Billing report → group by service and by the `componente` label
2. Usual suspects: forgotten instances, jobs in a loop, BigQuery queries with no partition filter
3. Immediate measure: lower `max-instances`; as a last resort, `terraform destroy` of dev

## P5 — Deploy a new version
`git push` to `main` → dev automatically → approve in Cloud Build → canary 10/50/100

# 1. Final check
./scripts/auditoria-seguridad.sh refugio-prod
terraform -chdir=infra/envs/prod plan          # No changes

# 2. Promote the tested image
./scripts/promocionar.sh "$(git rev-parse --short HEAD)"

# 3. Canary 10%, wait, verify
# 4. 50%, wait, verify
# 5. 100%
# 6. Mark the moment
git tag -a v1.0.0 -m "Final project launch"
git push --tags

# The command you leave running in a terminal for the first hour
gcloud logging tail \
  'resource.type="cloud_run_revision" AND severity>=WARNING' \
  --project=refugio-prod

# Incident 2026-11-04 — 5xx errors after the v1.1.0 deployment

**Duration:** 19:42 → 19:51 (9 minutes)
**Impact:** ~40 requests with a 500 error. No data loss.
**Detection:** error rate alert (arrived at 19:47, 5 min after the start)

## What happened
The v1.1.0 deployment introduced a query that used a column
(`opinion.magnitud`) created by migration 003, which had not been applied
in production. The application started up fine (the startup probe does not
touch that table) and only failed on requests to `/api/opiniones`.

## Timeline
- 19:42 Canary deployment at 10 %
- 19:44 First 500s in the logs (I did not look at them: I was watching the
        dashboard, which at 10 % of traffic barely moved the needle)
- 19:47 The alert arrives
- 19:49 I run `./scripts/revertir.sh`
- 19:51 Verified: 0 errors

## Root cause
The pipeline deploys the application but **does not run the migrations**.
In development I apply them by hand, so it worked there.

## What worked
- The rollback: 38 seconds, exactly as rehearsed.
- The canary limited the impact to 10 % of the traffic.

## What did not work
- The alert took 5 minutes. At 10 % of traffic, the 5 % threshold over the
  whole service is reached late.
- I was not watching the logs of the canary revision specifically.

## Actions
1. ✅ Add a migrations step to the pipeline, before the deployment.
2. ✅ Additional alert per revision, not only per service.
3. ⬜ Specific smoke test on `/api/opiniones` after deploying.

     ✓ query 200
     ✓ booking 201 or 409

     checks.........................: 99.31% ✓ 24893  ✗ 172
     http_req_duration..............: avg=241ms min=61ms med=178ms max=8.9s
       { expected_response:true }...: avg=228ms
       p(90)=402ms  p(95)=712ms  p(99)=2.41s
     http_req_failed................: 0.68%  ✓ 172    ✗ 25065
     errores_negocio................: 0.68%
     iterations.....................: 25065
     vus_max........................: 100

     ✗ http_req_duration.............: p(99)<2000  → 2.41s  FAILED
     ✓ http_req_failed...............: rate<0.01   → 0.0068 OK

$ gcloud logging read 'severity>=ERROR' --project=refugio-dev --limit=5 \
    --format="value(jsonPayload.message)"
FATAL: remaining connection slots are reserved for non-replication superuser connections

# Before
pool = ConnectionPool(dsn, min_size=5, max_size=10)
# After
pool = ConnectionPool(dsn, min_size=1, max_size=3, timeout=5)

     http_req_duration.....: p(95)=634ms  p(99)=1.82s   ✓
     http_req_failed.......: 0.00%  ✓ 0  ✗ 26102        ✓
     Cost of the test: €0.38

Symptom: after deploying v1.2.0 at 10 %, the p95 latency of the canary
revision went from 680 ms to 1,940 ms. Zero 5xx errors. Zero alerts.

Detection: I was watching it myself, comparing the canary revision with the
stable one on the dashboard, because the promotion criterion includes
"p95 < 1.2 × the baseline" and 1,940 does not meet it.

Cause: v1.2.0 added the average sentiment to the /api/refugios response
with a correlated subquery over `opinion`, with no index.

Decision: roll back without investigating further, at 22:14. The fix
(an index and a rewritten query) was deployed two days later.

FAILED test_no_hay_sobreventa_con_concurrencia
AssertionError: Overbooking: 7 bookings

=== SECURITY AUDIT: refugio-prod ===
[1] Primitive roles            ✅ No SA with owner/editor
[2] Account keys               ✅ Zero JSON keys
[3] Public buckets             ❌ 1 public bucket: gs://refugio-fotos-8f2a
[4] DB with a public IP        ✅ refugio-db has no public IP
[5] Secrets in git             ✅ No obvious secrets
[6] Credentials in the tree    ✅ No credential files
[7] Cloud Run without auth     ⚠️  refugio-web is public (correct)
[8] Audit logs                 ✅ Audit logs present

=== RESULT: 1 failure ===

def url_firmada(nombre_objeto: str, minutos: int = 60) -> str:
    blob = _bucket.blob(nombre_objeto)
    return blob.generate_signed_url(version="v4",
                                    expiration=timedelta(minutes=minutos),
                                    method="GET")

tls1     rejects    ✅
tls1_1   rejects    ✅
tls1_2   accepts    ✅
tls1_3   accepts    ✅
strict-transport-security: max-age=31536000; includeSubDomains
x-content-type-options: nosniff
x-frame-options: DENY
content-security-policy: default-src 'self'; img-src 'self' data: https://storage.googleapis.com
referrer-policy: strict-origin-when-cross-origin

Home page: 200 in 0.31s        ✅ still working (static content)
API:       500 in 30.02s       ❌ 30 seconds and a Python traceback
Health:    200                 ❌ IT LIES: it says it is ready and it is not

Home page: 200 in 0.28s        ✅
API:       503 in 1.24s        ✅ {"error":"servicio_no_disponible",
                                   "mensaje":"We cannot check availability
                                   right now. Try again in a few minutes."}
Health:    503                 ✅ Cloud Run stops sending traffic
Photos:    200                 ✅ the bucket is independent of the DB
Dashboard: 200                 ✅ Looker Studio reads from BigQuery