1. Text-to-SQL－將問題轉化為查詢
文字到 SQL 允許聊天機器人直接用自然語言查詢資料庫—「這個月的收入是多少？」 → 從訂單中選擇總和（金額），其中...。
┌────────────── 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.模式註入－為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產生器
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. 唯讀執行層
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. 知識圖譜查詢
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. 自然語言結果格式化
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;
}
}
第 11 課總結
- 文字轉SQL：模式註入→SQL產生→驗證→唯讀執行
- 安全：唯讀使用者、語句逾時、租戶過濾、屏蔽關鍵字、需要 LIMIT
- 知識圖譜：實體擷取→Cypher產生→圖查詢+向量混合
- 結果格式化：自動檢測格式（單值、表格、時間序列）→ 越南語本地化
- 行級安全性:PostgreSQL RLS+
SET app.tenant_id確保資料隔離
下一篇： 護欄與 AI 安全 — 輸入/輸出過濾、毒性偵測、PII 屏蔽、內容審核、越獄預防。