Skip to content

query-api

Objetivo

Puente HTTP genérico de solo lectura entre Cowork (Claude en la nube, solo HTTPS saliente, sin red hacia ctunlinux) y las bases de Trivasa: Postgres warehouse (trivasa_dw) y SQL Server (.207 producción, .205 staging). Documentado también en Stack de BI — es el servicio real detrás de reportesweb.frento.com.mx en el ingress de ctunlinux.

No es una app de conciliación ni expone endpoints por tabla. Un único POST /query acepta cualquier SELECT/WITH ... SELECT, para que quien lo consuma pueda explorar information_schema, paginar con su propio LIMIT/OFFSET y armar cualquier query sin pedir cambios en la API.

Dónde vive el código

trivasa-bi-core/query_api/ (en git, mismo repo que connections/ y shared/db_credentials.py, de donde reusa las credenciales de SQL Server sin duplicarlas):

  • main.py — la app FastAPI: GET /health (sin auth), POST /query.
  • db.py — engines de los 3 targets (postgres_dw, mssql_207, mssql_205).
  • sql_guard.py — filtro de solo-lectura por texto (bloquea todo lo que no sea un único SELECT/WITH).
  • bearer.py — verificación del bearer token.
  • audit.py — log de cada consulta a Loki + logs/query_api.log.
  • requirements.txt — fastapi, uvicorn[standard], sqlalchemy, psycopg2-binary, pymssql, requests (los últimos 4 ya estaban instalados a nivel sistema para el resto del repo).

Endpoints

GET  /health                 -- sin auth, {"status": "ok"}
POST /query                  -- bearer token requerido
     body: {"target": "postgres_dw" | "mssql_207" | "mssql_205",
            "sql": "SELECT ...", "params": {opcional, bind params}}
     resp: {"columns": [...], "rows": [[...], ...],
            "row_count": N, "truncated": bool}

Ejemplo real (probado contra producción):

curl -X POST https://reportesweb.frento.com.mx/query \
  -H "Authorization: Bearer $QUERY_API_TOKEN" -H "Content-Type: application/json" \
  -d '{"target":"postgres_dw","sql":"SELECT COUNT(*) AS n FROM raw_sat.cfdi_recibidos"}'
# -> {"columns":["n"],"rows":[[45012]],"row_count":1,"truncated":false}

Reglas de seguridad

Esto toca la base de producción de contabilidad (.207), así que:

  1. Solo lectura, en dos capas. sql_guard.py bloquea por texto cualquier statement que no empiece por SELECT/WITH, o que contenga una palabra clave de escritura (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, EXEC, MERGE, GRANT, CREATE, INTO, etc., sobre el texto sin comentarios/strings) o más de un statement (; en medio). Segunda capa real de conexión: Postgres abre la transacción en modo postgresql_readonly=True; para los dos targets de SQL Server (pymssql no soporta un modo read-only real por sesión) la red de seguridad es no llamar commit() nunca — al cerrar la conexión, SQLAlchemy hace rollback de lo que haya quedado abierto.
  2. Tope de filas y timeout. 20,000 filas por respuesta (si se excede, truncated: true y quien consulta pagina con su propio LIMIT/OFFSET); 30s de timeout por consulta (statement_timeout en Postgres, timeout de conexión en pymssql).
  3. Auditoría. Cada consulta (statement completo, hasta 4000 caracteres, target, status, row_count, duración) se loguea a Loki (job=query_api) y a trivasa-bi-core/logs/query_api.log — el push a Loki es best-effort, nunca tumba la request si Loki está caído (mismo criterio que el resto de observability/checks/*.py).
  4. Bearer token en todo menos /health. Ver "Secretos" abajo.

Probado real (no solo revisado el código): DELETE/UPDATE/INSERT y multi-statement contra postgres_dw — los 4 devuelven HTTP 400 sin tocar la base, tanto en local como a través del túnel público. Sin token → HTTP 401.

Credenciales

  • SQL Server (.207/.205): mismo patrón que connections/connection_207.py / connection_205_trivasadb3.py — host/ puerto/base hardcodeados, usuario/password vía shared/db_credentials.py (ealcocer() para .207, sa() para .205). Ver Credenciales y conexiones.
  • Postgres (postgres_dw): no usa db_credentials.postgres_dw() (esa es la cuenta de escritura de dlt/dbt) — usa el rol dedicado de solo lectura ealcocer_ro (ver Stack de BI § acceso de solo lectura). Las 5 variables (POSTGRES_DW_HOST/PORT/DBNAME/RO_USERNAME/RO_PASSWORD) llegan por Infisical, proyecto Trivasa (b6567423-9986-448e-b2b8-dffe44fe1657), entorno dev — nunca se leen de archivo ni se hardcodean.
  • API_TOKEN (el bearer del punto anterior): Infisical, proyecto secret-management (2aefdbd1-389c-4fd0-bdb8-a5621af8aac1), entorno dev, clave QUERY_API_TOKEN — mismo proyecto que las demás "point secrets" del homelab (no es credencial de BD, por eso no va en dlt/.dlt/secrets.toml).

Rotación:

infisical secrets set QUERY_API_TOKEN="$(openssl rand -hex 32)" --env dev \
  --projectId 2aefdbd1-389c-4fd0-bdb8-a5621af8aac1
sudo systemctl restart query-api.service

Infraestructura

Servicio systemd (mismo patrón que cloudflared.service, no cron — esto es un proceso persistente, no un job programado): /etc/systemd/system/query-api.service, User=ealcocer, WorkingDirectory=trivasa-bi-core/query_api, escuchando solo en 127.0.0.1:8765 (nunca 0.0.0.0 — todo el tráfico entrante pasa por el túnel de Cloudflare).

ExecStart encadena dos infisical run anidados (uno por proyecto de Infisical, no se pueden combinar en una sola invocación) envolviendo a uvicorn:

infisical run --projectId <Trivasa> --env dev -- \
  infisical run --projectId <secret-management> --env dev -- \
  uvicorn main:app --host 127.0.0.1 --port 8765

El EnvironmentFile de la unidad (~/.config/infisical/ehas-uk-systemd.env) le da a ese infisical run exterior su propio INFISICAL_TOKEN/ INFISICAL_API_URL — archivo separado del que usa el shell interactivo (~/.config/infisical/ehas-uk.env, con prefijo export): systemd no soporta ese prefijo en EnvironmentFile pese a lo que dice alguna documentación, y lo ignora en silencio (Ignoring invalid environment assignment) en vez de fallar fuerte — costó el primer intento de arranque real. El de systemd es texto plano KEY=VALUE, sin export.

Publicación: reusa el hostname ya reservado en el ingress del túnel nombrado de ctunlinux, reportesweb.frento.com.mx → 127.0.0.1:8765 (ver Stack de BI § acceso remoto) — no se creó túnel nuevo. Ese puerto lo ocupaba antes un script de prueba stdlib (~/api-cowork/api.py, servidor HTTP mínimo con /health//ping/ /echo) que se dio de baja para dejarle el lugar a este servicio.

Pendiente: el subdominio reportesweb.frento.com.mx es un nombre genérico heredado de cuando el hostname solo estaba "reservado para pruebas de API ad-hoc" — no describe lo que corre ahí ahora. Cambiarlo a algo más específico (ej. query-api.frento.com.mx) implica: agregar la nueva regla de ingress en /etc/cloudflared/config.yml, cloudflared tunnel route dns -f ctunlinux <hostname-nuevo>, avisarle a Cowork la URL nueva, y solo entonces retirar reportesweb.frento.com.mx del ingress (o dejarlo como alias). No hecho todavía.

Véase también

  • Stack de BI en ctunlinux — el túnel compartido, el resto de los servicios en el mismo host, y el rol de solo lectura ealcocer_ro de Postgres.
  • Credenciales y conexiones — por qué SQL Server usa shared/db_credentials.py y Postgres usa Infisical para este caso específico.
  • PROGRESS.md — estado vivo.