Construyendo consultas SQL con IA | Original, traducido por IA

Home 2025.07

Esquema de Base de Datos (PostgreSQL):

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(10),
    phone_number VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    zip_code VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE,
    last_login TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE,
    role VARCHAR(50),
    profile_picture_url VARCHAR(255),
    bio TEXT,
    failed_login_attempts INTEGER DEFAULT 0,
    account_balance NUMERIC(10, 2) DEFAULT 0.00
);

arriba es la base de datos de postgre, abajo es la definición de la tabla. aquí están las instrucciones de consulta en lenguaje natural.

quiero obtener algunos usuarios cuyo nombre de pila es andy, creados_ en torno al día del mes pasado, y que tengan 20 años en 2025, y cuyo último_inicio de sesión sea similar a la semana reciente, dame el sql para consultar tales usuarios

Consulta SQL Generada:

SELECT *
FROM users
WHERE first_name ILIKE 'andy'
  AND CAST(created_at AS DATE) BETWEEN
      (CURRENT_DATE - INTERVAL '1 MONTH' - INTERVAL '1 DAY')
      AND
      (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '1 DAY')
  AND EXTRACT(YEAR FROM AGE(date_of_birth)) = 20
  AND last_login >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS';

A continuación se presenta una definición de tabla PostgreSQL para una tabla conversations que podría usarse para almacenar interacciones de usuarios con un sistema de IA como Grok. La tabla está diseñada para rastrear conversaciones, incluyendo al usuario involucrado, el contenido de la conversación, marcas de tiempo y metadatos relevantes.

CREATE TABLE conversations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255),
    conversation_type VARCHAR(50) DEFAULT 'chat',
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(50) DEFAULT 'active',
    message_count INTEGER DEFAULT 0,
    last_message_at TIMESTAMP WITH TIME ZONE,
    is_archived BOOLEAN DEFAULT FALSE,
    ai_model_version VARCHAR(100),
    total_tokens_used INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Explicación:

Esta tabla está diseñada para ser flexible y puede extenderse según necesidades específicas, como almacenar mensajes individuales en una tabla separada messages o agregar campos para análisis de sentimientos o etiquetas de conversación. ¡Hazme saber si necesitas una tabla relacionada (por ejemplo, para mensajes) o una consulta para interactuar con esta tabla!

Para habilitar un frontend donde los usuarios puedan consultar sus propias conversaciones usando lenguaje natural (por ejemplo, “dame mis últimas 5 conversaciones” o “conversaciones cuyo título tiene la palabra java”), debes asegurarte de que la consulta SQL restrinja los resultados a las conversaciones del usuario autenticado. Esto implica incorporar la identidad del usuario (por ejemplo, user_id) en la consulta y traducir la entrada de lenguaje natural a condiciones SQL. A continuación se presentan prompts y consultas SQL correspondientes para la tabla conversations, asegurando que los usuarios solo puedan acceder a sus propios datos.

Consideraciones Clave

Prompts de Lenguaje Natural y Consultas SQL

Prompt 1: “Dame mis últimas 5 conversaciones”

Requisitos Analizados:

Consulta SQL:

SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
ORDER BY started_at DESC
LIMIT 5;

Prompt 2: “Conversaciones cuyo título tiene la palabra java”

Requisitos Analizados:

Consulta SQL:

SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
  AND title ILIKE '%java%';

Notas de Implementación para el Frontend

  1. Autenticación de Usuario:
    • El frontend debe enviar el ID del usuario autenticado (por ejemplo, desde un token JWT o sesión) a la API del backend.
    • El backend debe validar el user_id y usarlo en la cláusula WHERE para filtrar resultados.
  2. Procesamiento de Lenguaje Natural:
    • Usar un analizador basado en reglas simple o un modelo de IA (como Grok) para interpretar la consulta de lenguaje natural y extraer:
      • Intención: Listar conversaciones.
      • Filtros: Palabras clave (por ejemplo, “java”), ordenación (por ejemplo, “últimas”) o límites (por ejemplo, “5”).
    • Por ejemplo, mapear “últimas 5” a ORDER BY started_at DESC LIMIT 5 y “título tiene palabra java” a title ILIKE '%java%'.
  3. Diseño de API:
    • Crear un endpoint (por ejemplo, /api/conversations) que acepte:
      • user_id: De la sesión autenticada.
      • query: La entrada de lenguaje natural o parámetros estructurados (por ejemplo, { filter: "title contains java" }).
    • El backend traduce la consulta a SQL, asegurando que user_id siempre se incluya en la cláusula WHERE.
  4. Seguridad:
    • Nunca permitir que los usuarios ingresen SQL directamente o modifiquen el filtro user_id.
    • Usar consultas parametrizadas (por ejemplo, :authenticated_user_id) para evitar inyección SQL.
    • Validar las entradas para evitar patrones maliciosos en búsquedas ILIKE.
  5. Visualización en el Frontend:
    • Devolver campos como id, title, started_at, last_message_at y status para mostrar una lista de conversaciones.
    • Permitir opciones de ordenación o filtrado en la interfaz de usuario (por ejemplo, menús desplegables para “últimas” o entrada de texto para búsqueda de título).

Flujo de Trabajo de Ejemplo

  1. El usuario inicia sesión y el frontend recupera su user_id (por ejemplo, 123).
  2. El usuario escribe “dame mis últimas 5 conversaciones” en una barra de búsqueda.
  3. El frontend envía una solicitud al backend: /api/conversations?user_id=123&query=últimas 5.
  4. El backend analiza la consulta, genera el SQL: 
    SELECT *
FROM conversations
WHERE user_id = 123
ORDER BY started_at DESC
LIMIT 5;
  5. El backend devuelve los resultados como JSON y el frontend muestra las conversaciones.

Notas Adicionales

¡Hazme saber si necesitas ayuda con el código del frontend, la configuración de la API del backend o ejemplos de consultas adicionales!

Back Donate