1. Text-to-SQL — Biến câu hỏi thành truy vấn
Text-to-SQL cho phép chatbot truy vấn database trực tiếp từ natural language — "Doanh thu tháng này là bao nhiêu?" → SELECT SUM(amount) FROM orders WHERE....
┌────────────── TEXT-TO-SQL PIPELINE ───────────────────┐
│ │
│ "Doanh thu tháng này?" │
│ │ │
│ ┌────▼────┐ ┌──────────┐ ┌──────────────┐ │
│ │ Intent │──▶│ Schema │──▶│ SQL │ │
│ │ Detect │ │ Selector │ │ Generator │ │
│ └─────────┘ └──────────┘ └──────┬───────┘ │
│ │ │
│ ┌────▼────┐ │
│ │SQL │ │
│ │Validator│ │
│ └────┬────┘ │
│ │ │
│ ┌──────────┐ ┌──────────┐ ┌────▼────┐ │
│ │ Format │◀──│ Execute │◀──│Sanitize │ │
│ │ Response │ │ Query │ │& Limit │ │
│ └──────────┘ └──────────┘ └─────────┘ │
└───────────────────────────────────────────────────────┘
2. Schema Injection — Providing Database Context to LLM
interface TableSchema {
tableName: string;
description: string;
columns: ColumnSchema[];
sampleData?: Record<string, unknown>[];
relationships: Relationship[];
}
class SchemaManager {
private schemaCache = new Map<string, TableSchema[]>();
async getRelevantSchema(
tenantId: string,
userQuery: string,
): Promise<TableSchema[]> {
const allSchemas = await this.loadSchemas(tenantId);
// Use embedding similarity to find relevant tables
const queryEmbedding = await this.embedder.embed(userQuery);
const scored = await Promise.all(
allSchemas.map(async (schema) => {
const schemaText = `${schema.tableName}: ${schema.description}. Columns: ${
schema.columns.map(c => `${c.name} (${c.type}): ${c.description}`).join(', ')
}`;
const schemaEmbedding = await this.embedder.embed(schemaText);
return {
schema,
score: this.cosineSimilarity(queryEmbedding, schemaEmbedding),
};
}),
);
// Return top-K relevant tables
return scored
.sort((a, b) => b.score - a.score)
.slice(0, 5)
.map(s => s.schema);
}
formatSchemaForLLM(schemas: TableSchema[]): string {
return schemas.map(s => `
-- Table: ${s.tableName}
-- Description: ${s.description}
CREATE TABLE ${s.tableName} (
${s.columns.map(c => ` ${c.name} ${c.type}${c.nullable ? '' : ' NOT NULL'} -- ${c.description}`).join(',\n')}
);
${s.relationships.map(r => `-- FK: ${s.tableName}.${r.column} -> ${r.refTable}.${r.refColumn}`).join('\n')}
${s.sampleData ? `-- Sample: ${JSON.stringify(s.sampleData[0])}` : ''}
`).join('\n');
}
}
3. SQL Generator with Safety
class TextToSQLAgent {
async generateSQL(
query: string,
tenantId: string,
context: QueryContext,
): Promise<SQLResult> {
// 1. Get relevant schema
const schemas = await this.schemaManager.getRelevantSchema(tenantId, query);
const schemaText = this.schemaManager.formatSchemaForLLM(schemas);
// 2. Generate SQL
const response = await this.llm.chat({
messages: [{
role: 'system',
content: `You are a SQL expert. Generate PostgreSQL queries based on user questions.
DATABASE SCHEMA:
${schemaText}
RULES:
1. Only use SELECT statements (no INSERT, UPDATE, DELETE, DROP, etc.)
2. Always include WHERE tenant_id = '${tenantId}' for multi-tenant safety
3. Add LIMIT 100 to prevent large result sets
4. Use CTEs for complex queries
5. Handle NULL values appropriately
6. Format dates in Vietnamese locale
Output JSON:
{
"sql": "SELECT ...",
"explanation": "brief explanation of the query",
"expectedColumns": ["col1", "col2"]
}`,
}, {
role: 'user',
content: query,
}],
response_format: { type: 'json_object' },
model: 'gpt-4o',
temperature: 0,
});
const generated = JSON.parse(response.content);
// 3. Validate SQL
const validation = this.validateSQL(generated.sql, tenantId);
if (!validation.safe) {
throw new Error(`Unsafe SQL detected: ${validation.reason}`);
}
// 4. Execute
const results = await this.executeQuery(generated.sql, tenantId);
// 5. Format results
const formattedResponse = await this.formatResults(query, results, generated);
return {
sql: generated.sql,
results,
explanation: generated.explanation,
formattedResponse,
};
}
private validateSQL(sql: string, tenantId: string): ValidationResult {
const upperSQL = sql.toUpperCase().trim();
// Block dangerous statements
const blocked = ['INSERT', 'UPDATE', 'DELETE', 'DROP', 'ALTER', 'TRUNCATE',
'CREATE', 'GRANT', 'REVOKE', 'EXEC', 'EXECUTE'];
for (const keyword of blocked) {
if (upperSQL.startsWith(keyword)) {
return { safe: false, reason: `${keyword} statements are not allowed` };
}
}
// Must include tenant filter
if (!sql.includes(tenantId)) {
return { safe: false, reason: 'Missing tenant_id filter' };
}
// Must have LIMIT
if (!upperSQL.includes('LIMIT')) {
return { safe: false, reason: 'Missing LIMIT clause' };
}
// Block subqueries that could bypass tenant filter
const subqueryCount = (sql.match(/SELECT/gi) ?? []).length;
if (subqueryCount > 3) {
return { safe: false, reason: 'Too many subqueries' };
}
return { safe: true };
}
}
4. Read-Only Execution Layer
class SafeQueryExecutor {
private readOnlyPool: Pool;
constructor(config: PoolConfig) {
// Use read-only replica with limited permissions
this.readOnlyPool = new Pool({
...config,
user: 'chatbot_readonly', // Read-only user
database: config.database,
max: 10, // Limited connections
idleTimeoutMillis: 30_000,
connectionTimeoutMillis: 5_000,
});
}
async execute(sql: string, tenantId: string): Promise<QueryResult> {
const client = await this.readOnlyPool.connect();
try {
// Set statement timeout (prevent long-running queries)
await client.query('SET statement_timeout = 10000'); // 10s max
// Set row security policy context
await client.query('SET app.tenant_id = $1', [tenantId]);
// Execute in read-only transaction
await client.query('BEGIN READ ONLY');
const result = await client.query(sql);
await client.query('COMMIT');
return {
rows: result.rows,
rowCount: result.rowCount ?? 0,
fields: result.fields.map(f => ({ name: f.name, dataType: f.dataTypeID })),
};
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
client.release();
}
}
}
5. Knowledge Graph Querying
class KnowledgeGraphAgent {
async query(
naturalLanguageQuery: string,
tenantId: string,
): Promise<GraphQueryResult> {
// 1. Detect entities
const entities = await this.extractEntities(naturalLanguageQuery);
// 2. Generate Cypher query (Neo4j)
const cypherQuery = await this.generateCypher(
naturalLanguageQuery,
entities,
tenantId,
);
// 3. Execute on graph DB
const graphResults = await this.neo4j.run(cypherQuery, { tenantId });
// 4. Combine with vector search for richer context
const vectorResults = await this.vectorStore.search({
query: naturalLanguageQuery,
filter: { tenantId },
topK: 5,
});
// 5. Merge and format
return this.mergeResults(graphResults, vectorResults, naturalLanguageQuery);
}
private async generateCypher(
query: string,
entities: Entity[],
tenantId: string,
): Promise<string> {
const response = await this.llm.chat({
messages: [{
role: 'system',
content: `Generate a Cypher query for Neo4j.
Graph schema:
- (Product {name, price, category, tenantId})
- (Customer {name, email, segment, tenantId})
- (Order {id, date, total, tenantId})
- (Product)-[:BELONGS_TO]->(Category)
- (Customer)-[:PLACED]->(Order)
- (Order)-[:CONTAINS]->(Product)
RULES:
1. Always filter by tenantId = $tenantId
2. LIMIT 50
3. Return meaningful properties`,
}, {
role: 'user',
content: `Query: ${query}\nEntities detected: ${JSON.stringify(entities)}`,
}],
temperature: 0,
});
return response.content;
}
}
6. Natural Language Result Formatting
class ResultFormatter {
async format(
originalQuery: string,
results: QueryResult,
context: FormatContext,
): Promise<FormattedResponse> {
// Determine best format based on data shape
const formatType = this.detectBestFormat(results);
const response = await this.llm.chat({
messages: [{
role: 'system',
content: `Convert query results to a natural language response in Vietnamese.
Format the data as: ${formatType}
Rules:
- Use Vietnamese number formatting (dấu chấm ngàn: 1.000.000)
- Format currency as VNĐ
- Format dates as DD/MM/YYYY
- If data is tabular, use markdown table
- Highlight key metrics
- Be concise but complete`,
}, {
role: 'user',
content: `Question: ${originalQuery}\n\nQuery results:\n${JSON.stringify(results.rows, null, 2)}`,
}],
});
return {
text: response.content,
format: formatType,
rawData: results.rows,
chartSuggestion: this.suggestChart(results),
};
}
private detectBestFormat(results: QueryResult): string {
if (results.rowCount === 1 && results.fields.length <= 3) return 'single_value';
if (results.rowCount <= 5) return 'bullet_list';
if (results.fields.some(f => f.name.includes('date'))) return 'time_series';
return 'table';
}
private suggestChart(results: QueryResult): ChartSuggestion | null {
const hasNumeric = results.fields.some(f => [23, 20, 701].includes(f.dataType));
const hasDate = results.fields.some(f => [1082, 1114].includes(f.dataType));
if (hasDate && hasNumeric) return { type: 'line', xAxis: 'date', yAxis: 'value' };
if (hasNumeric && results.rowCount <= 10) return { type: 'bar' };
return null;
}
}
Tổng kết Bài 11
- Text-to-SQL: Schema injection → SQL generation → Validation → Read-only execution
- Safety: Read-only user, statement timeout, tenant filter, blocked keywords, LIMIT required
- Knowledge Graph: Entity extraction → Cypher generation → Graph query + vector hybrid
- Result Formatting: Auto-detect format (single value, table, time series) → Vietnamese localization
- Row-Level Security: PostgreSQL RLS +
SET app.tenant_idđảm bảo data isolation
Bài tiếp theo: Guardrails & AI Safety — input/output filtering, toxicity detection, PII masking, content moderation, jailbreak prevention.