Stock Market SQL Query Assistant

StocksTalk is a Web-native, multimodal retrieval pipeline that connects a voice conversation to a stock screener. The code is organized around a four-stage flow: (1) low-latency speech interaction, (2) retrieval-augmented intent understanding, (3) structured query induction, and (4) real-time Web data integration. The UI exposes intermediate output by requesting a SQL-like screening query from the agent and then converting it into a Screener.in URL.

There are two available agents in the UI: Male (Alex) and Female (Sarah). After selecting an agent, you can converse by voice. When you click the SQL query action, the system requests a SQL-like screening query from the agent, converts it to a Screener.in URL, and opens the results in a new tab.

Pipeline (end to end)

1. Speech interaction layer

• A low-latency voice session is established via ElevenLabs ConvAI using a signed URL from the backend.
• The client tracks multi-turn conversation state so the agent can maintain context.

2. Retrieval-augmented intent understanding

• The agent interprets the conversation and extracts screening constraints such as valuation thresholds and growth filters.
• The conversation is cumulative, allowing the user to refine constraints over multiple turns.

3. Structured query induction

• Extracted constraints are composed into a SQL-like screening query.
• The client requests the query explicitly and parses the response into a clean SQL-like condition string.

4. Real-time Web data integration

• The SQL-like query is sent to the backend and converted into a Screener.in URL.
• The browser opens Screener.in with the generated screen results.

User Flow

1. Select an agent (Alex or Sarah).
2. Start the voice session and converse with the agent.
3. Click the SQL query action to request a SQL-like screening query.
4. The app converts the query to a Screener.in URL and opens the results.

How the SQL Query Is Generated

• The client sends a targeted prompt to the agent asking for a SQL-like condition string.
• The response is parsed to remove extraneous text and formatting.
• The cleaned query is submitted to the backend for URL conversion.

SQL-to-URL Conversion

• The backend spawns the Python helper script and passes the SQL-like query through stdin.
• The script normalizes encoding, applies line break handling, and URL-encodes the query.
• The final Screener.in URL is returned to the client and opened in a new tab.

Features (from the code)

• Voice session with ElevenLabs ConvAI using signed URLs from the backend.
• Video avatar playback with a speaking indicator.
• SQL-to-URL conversion via a Python helper script.
• Quick query templates loaded from queries.csv.
• Manual SQL textarea for direct Screener.in URL generation.

Tech Stack

• Frontend: Vanilla JavaScript, HTML, CSS, Webpack
• Backend: Node.js, Express
• Voice: @elevenlabs/client
• SQL-to-URL: Python (urllib, regex)

Prerequisites

• Node.js 18+
• Python 3.7+
• ElevenLabs API key

Environment Variables

Create a .env file in the project root:

API_KEY=your_elevenlabs_api_key
AGENT_ID=your_default_agent_id
MALE_AGENT_ID=your_male_agent_id
FEMALE_AGENT_ID=your_female_agent_id
PORT=3000

Notes:

• The backend uses AGENT_ID as a default and switches to the specific IDs based on the selected agent.
• PORT is optional (defaults to 3000).

Install

npm install

Run

Development (webpack dev server + backend):

npm run dev

Production build + backend:

npm start

Open:

http://localhost:3000

API Endpoints

• GET /api/signed-url?opponent=alex|sarah
  • Returns a signed URL for ElevenLabs ConvAI.
• GET /api/getAgentId
  • Returns the default agent ID.
• POST /api/sql-to-url
  • Body: { "sqlQuery": "..." }
  • Returns: { "url": "https://www.screener.in/screen/raw/..." }
• GET /api/queries
  • Returns the parsed entries from queries.csv.

Project Structure

backend/
  server.js                # Express API for signed URLs, SQL conversion, and queries
src/
  index.html               # UI layout and inline script for quick queries
  app.js                   # ElevenLabs conversation logic and avatar control
  styles.css               # Global styles
  avatar.css               # Avatar styles
  market-themes.css        # Market theming styles
  videos/                  # Avatar video files
screener_url_generator.py  # SQL-to-URL conversion helper
queries.csv                # Quick query templates
webpack.config.js          # Webpack configuration
package.json               # Scripts and dependencies

Notes on SQL-to-URL

The backend calls screener_url_generator.py and sends the SQL-like conditions through stdin. The script encodes the query and returns a Screener.in URL. You can run the script directly to see sample outputs:

python screener_url_generator.py