Understanding Intent Recognition for TSQL.APP: A Beginner's Guide

This is a conceptual overview of intent recognition for TSQL.APP operations. This will help you better understand the fundamentals before diving into the implementation details.

Conceptual Overview of Intent Recognition

What is Intent Recognition?

Intent recognition is the process of identifying what a user wants to accomplish based on their natural language input. Think of it as trying to understand the "purpose" or "goal" behind a user's request.

For example, when a user says:

  • "Add a comment saying the customer called back" → The intent is to add a comment
  • "Find all open tickets from yesterday" → The intent is to search/filter records
  • "Change the status to completed" → The intent is to update a status

Why Intent Recognition Matters for TSQL.APP

TSQL.APP is a SQL-first application development platform that allows you to build applications directly from your database using T-SQL. When adding natural language capabilities to TSQL.APP:

  1. Bridge Between Natural Language and SQL: Intent recognition acts as the translator between what users say and the SQL operations you need to perform.

  2. Action Determination: It helps determine which TSQL.APP stored procedures to call (like sp_api_modal_text or sp_api_toast).

  3. Workflow Automation: It enables users to trigger complex workflows with simple language commands.

The Intent Recognition Pipeline

The process typically follows these steps:

  1. Input Processing: Tokenizing and normalizing user input
  2. Intent Classification: Determining the type of action requested
  3. Entity Extraction: Identifying specific data points mentioned in the request
  4. SQL Generation: Creating T-SQL code that accomplishes the intent
  5. Execution: Running the T-SQL in the TSQL.APP environment

Common TSQL.APP Intents

Let's explore some common operations users might want to perform in TSQL.APP:

1. Data Manipulation Intents

  • Add/Create: Adding new records, comments, or files
  • Update/Modify: Changing field values, statuses, or assignments
  • Delete/Remove: Deleting records or attachments

2. Data Retrieval Intents

  • Search/Filter: Finding records matching specific criteria
  • Sort/Order: Changing the display order of records
  • Export/Report: Generating downloads or reports

3. Navigation Intents

  • Go to/Open: Navigating to specific cards, views, or records
  • Return/Back: Going back to previous views

4. UI Interaction Intents

  • Show/Display: Displaying specific UI elements
  • Hide/Close: Hiding UI elements or closing modals
  • Submit/Send: Submitting forms or sending messages

Entity Extraction: Getting the Details

After identifying the intent, you need to extract the specific details or "entities" that are relevant to that intent:

Example: Status Update Intent

For a status update intent like "Change the ticket status to closed", you would need to extract:

  • Target Entity: "ticket" (what's being updated)
  • Field: "status" (what field to change)
  • Value: "closed" (the new value)

These entities become parameters for your SQL operation.

Mapping Intents to TSQL.APP Actions

Once you have the intent and entities, you need to map them to actual TSQL.APP operations:

Example Mappings

  1. Add Comment Intent

    • Stored Procedures: Use sp_api_toast for feedback, UPDATE statement for saving
    • Context: Needs current record ID (@id)
    • SQL Action: UPDATE {tablename} SET comment = CONCAT(comment, N'new text')

  2. Search/Filter Intent

    • Stored Procedures: sp_api_modal_text, sp_api_modal_table
    • Temporary Tables: Create #SearchResults
    • SQL Action: SELECT * FROM {tablename} WHERE field LIKE '%searchterm%'

  3. Status Update Intent

    • Check for selected records (@ids or @id)
    • Use UPDATE statement for data change
    • Use sp_api_toast for user feedback

Challenges in TSQL.APP Intent Recognition

As a beginner, be aware of these common challenges:

  1. Context Dependency: Intent might depend on current application state (what card is open, what records are selected)

  2. Ambiguity: "Show tickets" could mean navigation or search depending on context

  3. Compound Intents: "Find all overdue tickets and change status to urgent" combines search and update

  4. TSQL.APP Specifics: You need to follow the mandated practices (variable declaration at top, parameter passing rules, etc.)

Best Practices for TSQL.APP Intent Recognition

  1. Follow Mandated Practices: Always adhere to TSQL.APP's coding guidelines:

    • Declare all variables at the start
    • Use N prefix for Unicode strings
    • Never pass expressions directly to stored procedures

  2. Use Framework Config: Leverage the configuration to ensure consistent parameter handling

  3. Clean Error Handling: Provide useful feedback when intents can't be fulfilled

  4. Validate Before Acting: Check for required entities before generating SQL

  5. Training Data Diversity: Include various ways users might express the same intent

Getting Started: Implementing Your First Intent

Here's a simplified step-by-step approach to implement your first intent:

  1. Define the Intent Pattern: Identify key phrases that signal this intent

    if any(phrase in query for phrase in ["add comment", "new comment"]):
    return "add_comment"

  2. Extract Relevant Entities: Get the specific details needed

    if intent == "add_comment":
    # Find everything after the trigger phrase
    start_index = query.lower().find("add comment") + len("add comment")
    comment_text = query[start_index:].strip()
    entities["comment"] = comment_text

  3. Generate SQL Code: Create T-SQL that follows TSQL.APP practices

    def handle_add_comment(entities, config):
    if "comment" in entities:
        comment = entities["comment"]
        # Generate SQL that updates the comment field
        return f"UPDATE {{{{tablename}}}} SET comment = CONCAT(comment, N'{comment}');"

  4. Wrap in Framework Code: Add variable declarations and state retrieval

    variable_declarations = generate_variable_declarations(framework_config)
state_value_retrieval = generate_state_value_retrieval(framework_config)
action_script = framework_config["script_prefix"] + variable_declarations + state_value_retrieval + intent_sql + framework_config["script_suffix"]

Expanding Your System

Once you've mastered basic intent recognition, you can expand by:

  1. Adding More Intents: Implement navigation, data manipulation, and UI interaction intents

  2. Improving NLP: Use more sophisticated NLP libraries or services for better understanding

  3. Building Context Awareness: Maintain session state to understand context

  4. Adding Confirmation: For destructive operations or complex queries

  5. Supporting Compound Intents: Break down multi-action requests into steps

Development Workflow

As you develop your system, follow this workflow:

  1. Research: Understand the TSQL.APP operation you want to support
  2. Test Manually: Write and test the T-SQL code directly in TSQL.APP
  3. Identify Patterns: Determine what phrases indicate this intent
  4. Implement Recognition: Code the intent recognition logic
  5. Extract Entities: Develop entity extraction for this intent
  6. Generate SQL: Create the function to generate proper T-SQL
  7. Test: Try various phrasings and edge cases
  8. Refine: Improve based on testing results

Learning Resources

To deepen your understanding:

  1. Study the TSQL.APP documentation thoroughly
  2. Explore Python NLP libraries like spaCy (which you're already using)
  3. Learn about state management in conversational systems
  4. Practice writing T-SQL following TSQL.APP mandated practices

By starting with these concepts and gradually implementing the code examples I provided earlier, you'll build a solid foundation for developing intent recognition capabilities for TSQL.APP operations.