AI Chat Assistant Best Practices

The AI Chat Assistant transforms database interaction from technical SQL writing to natural conversation. This guide is the canonical reference for using the AI assistant effectively, safely, and efficiently in production environments.

Understanding AI-Powered Database Interaction

The AI Chat Assistant is fundamentally different from traditional database tools. Rather than writing SQL directly, you describe what you want in natural language, and the AI generates appropriate queries based on your database schema.

How AI Assistants Work

Key Differences from Traditional SQL

Query Formulation Best Practices

Effective communication with the AI assistant follows specific patterns that produce accurate, efficient results.

Be Specific and Explicit

Vague questions produce unreliable results. Specificity ensures the AI understands your exact intent.

Provide Context

Context helps the AI understand your intent and generate more accurate queries.

Include Business Context:

Show revenue by product category for the last quarter (for quarterly report)
Find users who haven't logged in for 90 days (for cleanup campaign)

Mention Expected Results:

Show all orders (expecting about 1000 records)
Count active subscriptions (should be around 500)

Expected results help you quickly identify when queries return unexpected data.

Use Proper Database Terminology

Use terminology appropriate to your database type: tables, rows, columns, JOINs, and WHERE clauses for SQL databases; collections, documents, fields, and aggregation pipelines for MongoDB; keys, values, sets, and hashes for Redis.

SQL Example:

Join the orders table with customers table on customer_id and show customer names with their order totals

MongoDB Example:

Aggregate users collection grouped by email domain with count

Start Simple, Then Refine

Build complex queries through iterative refinement rather than trying to get everything perfect in one question:

1. Show me all orders
2. Just orders from the last 30 days
3. Group those by customer
4. Show total order value for each customer
5. Sort by total value descending

The AI understands each question refines the previous one. This iterative approach is faster and more reliable than trying to construct complex queries in a single request.

Safety and Security Best Practices

Using AI assistants safely requires understanding what data is shared, potential risks, and protective measures.

Understand Data Sharing

Different AI providers have different data handling policies.

What Gets Sent to AI Providers:

• Your natural language questions
• Database table names and schemas
• Column names and data types
• Database type (PostgreSQL, MySQL, etc.)
• Previous conversation context

What Does NOT Get Sent:

• Actual row data from your database
• Query result contents
• Stored data values
• Connection credentials

For Maximum Privacy:

• Use local models (Ollama, LM Studio) for complete data isolation
• Avoid mentioning sensitive values in questions
• Use generic terms instead of revealing schema names

Verify Before Modifying Data

Always review and verify before confirming data modification operations.

Use Read-Only Users When Possible

For data exploration and analysis tasks, connect with read-only database credentials.

PostgreSQL Read-Only User:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

MySQL Read-Only User:

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON mydb.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;

Backup Before Bulk Operations

Before executing bulk modifications, ensure a recent, tested backup exists that includes all affected tables, and that the restoration procedure is documented.

Quick Backup Commands:

PostgreSQL:

pg_dump -h localhost -U username -d database -t table_name > backup_$(date +%Y%m%d_%H%M%S).sql

MySQL:

mysqldump -h localhost -u username -p database table_name > backup_$(date +%Y%m%d_%H%M%S).sql

Review Generated SQL

The AI assistant shows generated SQL before execution. Use this visibility to verify correctness:

• SELECT: correct tables, appropriate JOIN conditions, WHERE filters match intent, no expensive functions on large tables
• UPDATE: WHERE clause present and correct, SET values appropriate
• DELETE: WHERE clause present (unless intentionally deleting all), correct table, backup exists
• INSERT: all required columns included, values match column data types

Provider Selection Strategy

Choosing the right AI provider for each situation optimizes cost, performance, privacy, and accuracy.

When to Use Each Provider

Model lists are fetched live from each provider when you add it, so pick from your provider's current lineup. For current model options and rates, check your provider's pricing page.

Cost Optimization Strategies

Per-token pricing changes frequently — compare current rates on your providers' pricing pages rather than relying on fixed estimates.

Privacy Considerations

Choose providers based on data sensitivity and organizational policies.

Performance Optimization

Optimize AI assistant performance through efficient query patterns and conversation management.

Efficient Query Patterns

Managing Conversation Context

Long conversations accumulate context that slows response times and increases costs. Short conversations (1-10 messages) are fastest and cheapest; conversations beyond 30-50 messages become noticeably slower and more expensive.

When to Start New Conversations:

Collaboration and Documentation

Preserve valuable queries and build shared knowledge with a lightweight process.

When a query is worth reusing — regular reports, complex analysis you'll repeat, or queries that revealed useful insights — hover over the result, click the ellipsis (...) button, and select Move to Scratchpad. Choose or create an appropriately named page, then add a comment explaining what the query does, when to use it, and any caveats.

Organize Scratchpad pages by purpose (reporting, analysis templates, data quality checks, maintenance) so queries are easy to find later. For team sharing, copy important SQL into your normal shared docs, runbooks, or repository.

Finally, well-documented schemas help the AI generate more accurate queries. Add database-level comments to tables and non-obvious columns:

COMMENT ON TABLE users IS 'Customer user accounts with authentication';
COMMENT ON COLUMN users.last_login IS 'UTC timestamp of most recent successful login';

Error Handling and Recovery

Understand common mistakes and recovery strategies.

Common Mistakes to Avoid

Troubleshooting Approach

When queries don't work as expected, follow a systematic troubleshooting process.

Recovery Strategies

Production Environment Guidelines

Using AI assistants in production requires additional discipline and procedures.

Testing Queries

Never execute untested queries directly in production.

Change Management

Follow established change management procedures for data modifications.

Pre-Change Checklist:

• Change request documented and approved
• Testing completed in non-production
• Backup verified and accessible
• Rollback procedure documented
• Team members notified

During and After:

• Execute during scheduled window and monitor progress
• Verify results match expectations
• Update documentation and notify stakeholders

Audit Requirements

Maintain audit trails for compliance and troubleshooting.

Audit Logging in WhoDB Community Edition: Use database-level audit logging to record executed queries. Per-query audit trails with user attribution are available in the Enterprise Edition.

Additional Audit Measures:

• Enable database-level query logging
• Review audit logs regularly and retain them per compliance requirements
• Consider regulatory obligations: GDPR (personal data access), HIPAA (PHI access), SOX (financial data), PCI DSS (cardholder data)

Learning and Improvement

The AI assistant is also an effective SQL tutor. Ask it to explain generated queries ("Why did you use LEFT JOIN instead of INNER JOIN?"), request alternative approaches ("Is there a more efficient way to write this query?"), and explore features in context ("How do window functions work in PostgreSQL?").

Build skills progressively: start with simple SELECT queries, then add WHERE conditions, aggregations, JOINs, and eventually subqueries and window functions. Study the SQL the AI generates — its JOIN structure, aliasing, and date-filtering patterns — rather than using queries blindly. Comparing AI-generated SQL against queries you write manually in Scratchpad is a fast way to discover techniques you might not have considered.

Next Steps