How the AI SQL Assistant Understands Your Database Schema

What the AI Reads From Your Schema

The schema scan collects several categories of information that the AI uses when generating queries:

• Tables and views: Every table and view in your database (or in the schemas your user has access to). The AI uses table names to match your questions to the right data source.
• Columns and data types: Each column's name, type (VARCHAR, INT, DECIMAL, DATE, BOOLEAN, JSON, etc.), and whether it allows NULL values. This tells the AI what kind of operations are valid on each column.
• Primary keys: The unique identifier for each table. The AI uses this when it needs to fetch or reference specific records.
• Foreign keys: Relationships between tables. A foreign key from orders.customer_id to customers.id tells the AI how to JOIN these tables when you ask questions that span both.
• Indexes: Which columns are indexed. While this does not change the query logic, it helps the AI suggest efficient queries that use available indexes rather than full table scans.
• Constraints: Unique constraints, check constraints, and default values. These help the AI understand the rules your data follows.

How the AI Uses Schema Information

Matching Questions to Tables

When you ask "show me recent orders," the AI looks through your table names for something matching "orders." If you have tables called orders, order_items, and order_history, it picks the most relevant one based on your question. If ambiguous, it may ask for clarification or make its best guess based on the column structure.

Building JOIN Queries

Foreign keys are critical for multi-table queries. When you ask "show me customer names with their order totals," the AI sees the foreign key from orders to customers and writes the correct JOIN clause automatically. Without foreign keys defined in your schema, the AI can still infer relationships from column naming conventions (like customer_id matching customers.id), but explicit foreign keys are more reliable.

Choosing the Right Functions

Data types determine which SQL functions the AI applies. It uses date functions on DATE columns, string functions on VARCHAR columns, and aggregate functions (SUM, AVG, COUNT) on numeric columns. It will not try to calculate an average on a text column or apply string matching to an integer.

Handling Database-Specific Syntax

The AI adjusts its SQL syntax based on whether you are connected to MySQL or PostgreSQL. MySQL uses backtick quoting and functions like DATE_FORMAT and IFNULL. PostgreSQL uses double-quote identifiers and functions like TO_CHAR and COALESCE. The schema tells the AI which dialect to use.

Improving Schema Understanding

The more descriptive your table and column names are, the better the AI performs. A column named cust_ltv is harder for the AI to interpret than customer_lifetime_value. Similarly, a table called tbl_01 gives the AI no context, while support_tickets is immediately understandable.

If your database uses abbreviated or cryptic names (which is common in legacy systems), you can add context by telling the AI about your naming conventions. For example, "in my database, 'cust' means customer and 'ltv' means lifetime value." The AI remembers this context within the conversation.

Refreshing the Schema

The schema is scanned when you first connect your database and is cached for fast access. If you add new tables, modify columns, or change relationships, you can refresh the schema from the app settings. The refresh reads the updated structure and makes it available to the AI immediately.

Schema and Security

The schema information is used only for query generation within your account. It is not shared with other users, stored outside your session, or used for any purpose other than helping you query your own data. The AI sees only the tables and columns that your database user has permission to access, so limiting the user's permissions also limits what the AI can see. For more on this topic, see How to Secure Your Database Connection.