Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/tutosrive/factus_challenge/llms.txt

Use this file to discover all available pages before exploring further.

This endpoint accepts a raw SQL string in the request body and executes it directly against the PostgreSQL database via db.query(body.query). It exists to support use-cases that the other generic CRUD endpoints cannot handle — most importantly JOIN queries that span multiple tables, and targeted lookups by non-primary-key columns. In the reference implementation, the frontend uses this endpoint to resolve a product by code_reference and to fetch a single customer by id when assembling an invoice payload.

​
Endpoint

POST /get-join

​
Request Body

​
query
string
required
The complete, valid SQL query to execute. The string is passed directly to the pg driver with no preprocessing, parameterisation, or sanitisation.
{ "query": "SELECT * FROM products WHERE code_reference = 'F7W1G'" }

​
Responses

​
200 — OK

Returned when at least one row is found (rowCount > 0). 
{
  "status": 200,
  "message": "Datos encontrados",
  "data": [ /* array of row objects */ ]
}
​
status
number
Always 200 on success.
​
message
string
"Datos encontrados"
​
data
array
Array of row objects returned by PostgreSQL. The shape of each object depends entirely on the columns selected by the SQL query.

​
404 — Not Found

Returned when rowCount === 0 — the query executed without error but returned no rows. 
{
  "status": 404,
  "message": "No se encontraron datos"
}

​
500 — Internal Server Error

Returned when the pg driver throws an exception (syntax error in the SQL, missing table, connection failure, etc.). 
{
  "status": 500,
  "message": "Error al obtener los datos",
  "error": { /* pg error object */ }
}
​
error
object
Raw error from the pg driver. Check error.code and error.message for PostgreSQL diagnostics.

​
How the Frontend Uses This Endpoint

The reference frontend makes two specific calls to /get-join when building an invoice payload:
  1. Look up a product by code_reference — after the user enters or scans a product code, the frontend resolves the full product record: 
    { "query": "SELECT * FROM products WHERE code_reference = 'F7W1G'" }
  2. Look up a customer by id — to attach complete customer details to the invoice: 
    { "query": "SELECT * FROM customer WHERE id = '42'" }
Both calls return the row(s) inside the data array, and the frontend picks data[0] for the single-result case.

​
Examples

curl -X POST http://localhost:3000/get-join \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT c.id, c.names, c.email, m.name AS municipality FROM customer c JOIN municipality m ON c.municipality_id = m.id WHERE c.id = 42"
  }'

​
Example Response — JOIN query

{
  "status": 200,
  "message": "Datos encontrados",
  "data": [
    {
      "id": 42,
      "names": "Santiago Rivera",
      "email": "[email protected]",
      "municipality": "Armenia"
    }
  ]
}
This endpoint executes raw, unsanitized SQL. The query string from the request body is passed directly to db.query() with absolutely no validation, parameterisation, or escaping. A malicious caller can run arbitrary SQL — including DROP TABLE, DELETE FROM, or data-exfiltration queries — against any table in the connected PostgreSQL database. Never expose this endpoint without:
  • Strong authentication (verify the caller’s identity before the route is reached).
  • Input validation (restrict the allowed SQL patterns or use an allowlist of pre-approved query templates).
  • A read-only database role if write access is not required.
This endpoint is intended for trusted internal use only.
In the reference implementation, the frontend uses /get-join for exactly two purposes: resolving a single product record by code_reference and resolving a single customer record by id when constructing an invoice payload before submission to the Factus billing API. All other data fetching goes through the safer GET /get-data/:table endpoint.

Build docs developers (and LLMs) love

Get started for freeTalk to us