Reports API: KPI Summary and Filtered Order Analytics

The Reports API powers the Tradiciones y Sabores analytics dashboard. It exposes three endpoints: a KPI summary that compares the current 30-day window against the prior period, a flexible order-history query with period and status filters, and a lightweight health-check endpoint that confirms the backend’s connection to PostgreSQL. All timestamps are handled in UTC, and percentage-change calculations degrade gracefully when the prior period contains no data.

GET /api/reportes/resumen

Returns a five-field KPI snapshot covering the rolling last 30 days compared against the immediately preceding 30-day period (days 31–60 in the past). Use this endpoint to populate dashboard cards showing revenue trends, order volume, and kitchen throughput.

If the prior 30-day period has zero orders or zero revenue, the corresponding percentage-change field returns 100.0 when the current period has activity, or 0.0 when both periods are empty. This prevents division-by-zero errors on a freshly deployed system.

curl -X GET https://your-api-host/api/reportes/resumen \
  -H "Accept: application/json"

Response

total_pedidos

integer

Number of orders (pedido rows) whose fecha_creacion falls within the last 30 days.

ingresos_brutos

float

Sum of factura.total for all invoices linked to orders created in the last 30 days. Returns 0.0 if no invoices exist for the period.

tiempo_promedio_seg

float

Average elapsed time in seconds from fecha_creacion to the moment the report is generated, measured only across orders that have reached listo or entregado status within the last 30 days. Returns 0.0 if no completed orders exist in the window.

pct_cambio_pedidos

float

Percentage change in order count between the current and prior 30-day periods, rounded to one decimal place. Positive values indicate growth; negative values indicate a decline.

pct_cambio_ingresos

float

Percentage change in gross revenue between the current and prior 30-day periods, rounded to one decimal place.

Example response

{
  "total_pedidos": 312,
  "ingresos_brutos": 148750.50,
  "tiempo_promedio_seg": 1823.4,
  "pct_cambio_pedidos": 12.5,
  "pct_cambio_ingresos": -3.1
}

GET /api/reportes/pedidos

Returns an array of order objects filtered by time period and/or order status. This endpoint drives the filterable orders table in the analytics dashboard and accepts the same optional query parameters independently — you can filter by period alone, status alone, or both together. Orders are returned in descending ticket number order (most recent first). Each element in the array has the same shape as the responses from GET /api/ordenes.

curl -X GET "https://your-api-host/api/reportes/pedidos?periodo=semana&estado=preparando" \
  -H "Accept: application/json"

Query parameters

periodo

string

Time window to filter orders by fecha_creacion. Accepted values:

Value	Window
`hoy`	From midnight of the current day (UTC) to now
`semana`	Last 7 days from now
`mes`	Last 30 days from now (default)

Defaults to mes (last 30 days) when omitted or when an unrecognised value is provided.

estado

string

Filter by order status. Accepted values: recibido, preparando, listo, entregado, or Todos. When set to Todos or omitted entirely, orders of all statuses are returned.

Response

Returns a JSON array of order objects. Each element has the same flattened shape produced by format_pedido_response() — the same format returned by GET /api/ordenes. Customer and line-item data are inlined directly into the order object rather than nested under separate cliente or detalles keys.

id_pedido

integer

Ticket number, mirrored from num_ticket for compatibility with older clients.

num_ticket

integer

Auto-incremented primary key and ticket number for the order.

hora_creacion

string

ISO 8601 UTC timestamp of when the order was created, e.g. "2025-01-14T19:32:05".

cliente_nombre

string

Full name of the customer. Defaults to "Cliente General" if the client record is missing.

cliente_cedula

string

Venezuelan national ID of the customer, e.g. "V-18340921".

cliente_telefono

string

Phone number on file for the customer.

tipo

string

Order type string, same value as tipo_pedido. Included for compatibility.

tipo_pedido

string

Order fulfillment type: mesa, pickup, or delivery.

mesa

integer | null

Table number assigned to the order, same value as id_mesa.

id_mesa

integer | null

Table ID for dine-in orders. null for pickup or delivery.

direccion

string | null

Delivery address, same value as direccion_envio. null for non-delivery orders.

direccion_envio

string | null

Full delivery address string. Only populated when tipo_pedido is delivery.

items

array

Line items attached to the order. Each element contains:

items[].id_producto

integer

Dish ID, mirrored from id_plato.

items[].id_plato

integer

Primary key of the plato record.

items[].nombre

string

Dish name resolved from the plato table. Falls back to "Plato #<id>" if the record is missing.

items[].cantidad

integer

Quantity ordered.

items[].precio_unitario

number

Unit price at the time of ordering, sourced from plato.precio.

items[].subtotal

number

Line total (precio_unitario × cantidad), rounded to two decimal places.

subtotal

number

Sum of all line item subtotals before tax.

iva

number

Value-added tax at 16% (subtotal × 0.16), rounded to two decimal places.

total

number

Final amount due (subtotal + iva), rounded to two decimal places.

Estatus_Orden

string

Capitalized status string for display purposes, e.g. "Preparando". Same state as estado_orden.

estado_orden

string

Lowercase status string. One of recibido, preparando, listo, or entregado.

Example response

[
  {
    "id_pedido": 88,
    "num_ticket": 88,
    "hora_creacion": "2025-01-14T19:32:05",
    "cliente_nombre": "Ana Rivas",
    "cliente_cedula": "V-18340921",
    "cliente_telefono": "0416-7823041",
    "tipo": "mesa",
    "tipo_pedido": "mesa",
    "mesa": 3,
    "id_mesa": 3,
    "direccion": null,
    "direccion_envio": null,
    "items": [
      {
        "id_producto": 5,
        "id_plato": 5,
        "nombre": "Pabellón Criollo",
        "cantidad": 2,
        "precio_unitario": 24.00,
        "subtotal": 48.00
      }
    ],
    "subtotal": 48.00,
    "iva": 7.68,
    "total": 55.68,
    "Estatus_Orden": "Preparando",
    "estado_orden": "preparando"
  }
]

GET /api/debug

Health-check endpoint that verifies environment configuration and confirms whether the application can reach the PostgreSQL database. Returns the values of all four connection environment variables alongside a live connection test result.

This endpoint performs a real query (SELECT COUNT(*) FROM plato) on every call to confirm the database session is functional. In production environments, restrict access to this endpoint to internal network calls or authenticated admin users, as it exposes database host and credential metadata.

curl -X GET https://your-api-host/api/debug \
  -H "Accept: application/json"

Response

environment

object

Object containing the four database connection environment variables read at request time.

Show environment fields

DB_HOST

string

Hostname or IP address of the PostgreSQL server. Defaults to "NOT_SET" if the environment variable is absent.

DB_PORT

string

Port the PostgreSQL server is listening on. Defaults to "NOT_SET" if absent.

DB_NAME

string

Name of the target database. Defaults to "NOT_SET" if absent.

DB_USER

string

Database username. Defaults to "NOT_SET" if absent.

db_connection

string

Result of the live connectivity probe. Returns a string beginning with "CONNECTED" (e.g. "CONNECTED (Platos count: 42)") on success, or "FAILED" if the connection or query raised an exception.

db_error

string | null

Exception message including the exception type if db_connection is "FAILED". null when the connection succeeds.

Example response — healthy

{
  "environment": {
    "DB_HOST": "db.internal.tradicionesysabores.com",
    "DB_PORT": "5432",
    "DB_NAME": "restaurante_db",
    "DB_USER": "api_user"
  },
  "db_connection": "CONNECTED (Platos count: 18)",
  "db_error": null
}

Example response — connection failure

{
  "environment": {
    "DB_HOST": "NOT_SET",
    "DB_PORT": "NOT_SET",
    "DB_NAME": "NOT_SET",
    "DB_USER": "NOT_SET"
  },
  "db_connection": "FAILED",
  "db_error": "OperationalError: could not connect to server: Connection refused"
}

API Overview

Endpoints

Reports API: KPI Summary and Filtered Order Analytics

GET /api/reportes/resumen

Response

GET /api/reportes/pedidos

Query parameters

Response

GET /api/debug

Response

Build docs developers (and LLMs) love

API Overview

Endpoints

Documentation Index

​GET /api/reportes/resumen

​Response

​GET /api/reportes/pedidos

​Query parameters

​Response

​GET /api/debug

​Response

Build docs developers (and LLMs) love

GET /api/reportes/resumen

Response

GET /api/reportes/pedidos

Query parameters

Response

GET /api/debug

Response