بناء وكيل تحليلات عربي يحوّل السؤال إلى SQL فوق نظام ERP بلغة TypeScript

npm install ai @ai-sdk/anthropic zod pg node-sql-parser
npm install -D vitest @types/pg tsx

-- تُنفَّذ بصلاحية superuser على نسخة القراءة
CREATE ROLE analytics_reader LOGIN PASSWORD 'change-me-and-put-it-in-a-secret-manager';
 
-- القارئ لا يملك شيئًا افتراضيًا
REVOKE ALL ON SCHEMA public FROM analytics_reader;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM analytics_reader;
 
-- مخطط مخصّص يحوي فقط العروض المسموح للوكيل بلمسها
CREATE SCHEMA analytics;
GRANT USAGE ON SCHEMA analytics TO analytics_reader;
 
-- إعدادات جلسة لا يستطيع الوكيل تجاوزها من داخل الاستعلام
ALTER ROLE analytics_reader SET default_transaction_read_only = on;
ALTER ROLE analytics_reader SET statement_timeout = '8s';
ALTER ROLE analytics_reader SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE analytics_reader SET search_path = analytics;

-- على الجدول الأساسي، لا على العرض
ALTER TABLE erp.sales_order ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY sales_order_tenant ON erp.sales_order
  FOR SELECT
  TO analytics_reader
  USING (company_id = current_setting('app.company_id', true)::int);

CREATE VIEW analytics.fact_sales_order
  WITH (security_invoker = true) AS
SELECT
  so.id                AS order_id,
  so.company_id,
  so.ordered_at::date  AS order_date,
  b.name_norm          AS branch_name,
  b.city_norm          AS branch_city,
  c.name_norm          AS customer_name,
  so.currency,
  so.net_amount_halalas,
  so.vat_amount_halalas,
  so.status
FROM erp.sales_order so
JOIN erp.branch   b ON b.id = so.branch_id
JOIN erp.customer c ON c.id = so.customer_id;
 
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analytics_reader;

// lib/analytics/semantic-layer.ts
export type Column = {
  name: string;
  type: 'text' | 'date' | 'int' | 'money_halalas';
  description: string;
};
 
export type Entity = {
  view: string;
  title: string;
  description: string;
  grain: string;
  columns: Column[];
  notes?: string[];
};
 
export const ENTITIES: Entity[] = [
  {
    view: 'analytics.fact_sales_order',
    title: 'Sales orders',
    description:
      'One row per sales order across all branches. Use for revenue, order counts, and branch or city comparisons.',
    grain: 'one row per sales order',
    columns: [
      { name: 'order_id', type: 'int', description: 'Primary key.' },
      { name: 'order_date', type: 'date', description: 'Date the order was placed.' },
      { name: 'branch_name', type: 'text', description: 'Branch name, Arabic-normalized.' },
      { name: 'branch_city', type: 'text', description: 'City, Arabic-normalized.' },
      { name: 'customer_name', type: 'text', description: 'Customer name, Arabic-normalized.' },
      { name: 'currency', type: 'text', description: 'ISO code, almost always SAR.' },
      {
        name: 'net_amount_halalas',
        type: 'money_halalas',
        description: 'Net amount excluding VAT, in halalas. Divide by 100 for SAR.',
      },
      {
        name: 'vat_amount_halalas',
        type: 'money_halalas',
        description: 'VAT amount in halalas. Saudi standard rate is 15 percent.',
      },
      {
        name: 'status',
        type: 'text',
        description:
          "Order state. Only 'confirmed' and 'delivered' count as real revenue; 'draft' is an unapproved quotation and 'cancelled' must be excluded.",
      },
    ],
    notes: [
      'Never sum draft or cancelled orders into revenue.',
      'All amounts are integers in halalas. Always divide by 100.0 when presenting SAR.',
    ],
  },
  // ...  fact_invoice, dim_branch, fact_inventory_movement
];
 
export const ALLOWED_VIEWS = new Set(ENTITIES.map((e) => e.view));

// lib/analytics/render-schema.ts
import type { Entity } from './semantic-layer';
 
export function renderEntity(entity: Entity): string {
  const cols = entity.columns
    .map((c) => `  - ${c.name} (${c.type}): ${c.description}`)
    .join('\n');
  const notes = entity.notes?.map((n) => `  ! ${n}`).join('\n') ?? '';
  return [
    `VIEW ${entity.view} — ${entity.title}`,
    `  ${entity.description}`,
    `  Grain: ${entity.grain}`,
    cols,
    notes,
  ]
    .filter(Boolean)
    .join('\n');
}

CREATE OR REPLACE FUNCTION analytics.ar_normalize(t text)
RETURNS text
LANGUAGE sql
IMMUTABLE
PARALLEL SAFE
STRICT
AS $$
  SELECT btrim(regexp_replace(
    translate(
      -- إزالة التشكيل (U+064B..U+0652) والتطويل (U+0640)
      regexp_replace(lower(t), '[ً-ْـ]', '', 'g'),
      -- توحيد صور الهمزة والألف المقصورة والتاء المربوطة والياء/الكاف الفارسية
      'أإآٱىةؤئیک',
      'اااايهوييك'
    ),
    '\s+', ' ', 'g'), ' ')
$$;

ALTER TABLE erp.branch
  ADD COLUMN city_norm text
  GENERATED ALWAYS AS (analytics.ar_normalize(city)) STORED;
 
CREATE INDEX branch_city_norm_idx ON erp.branch (city_norm);

WHERE branch_city = analytics.ar_normalize('الرياض')

// lib/analytics/ar-normalize.ts
const TASHKEEL = /[ً-ْـ]/g;
const FOLD: Record<string, string> = {
  'أ': 'ا', 'إ': 'ا', 'آ': 'ا', 'ٱ': 'ا',
  'ى': 'ي', 'ي': 'ي', 'ی': 'ي',
  'ة': 'ه',
  'ؤ': 'و',
  'ئ': 'ي',
  'ك': 'ك', 'ک': 'ك',
};
 
export function arNormalize(input: string): string {
  return input
    .toLowerCase()
    .replace(TASHKEEL, '')
    .replace(/./gu, (ch) => FOLD[ch] ?? ch)
    .replace(/\s+/g, ' ')
    .trim();
}

// lib/analytics/select-entities.ts
import { ENTITIES, type Entity } from './semantic-layer';
import { arNormalize } from './ar-normalize';
 
const SYNONYMS: Record<string, string[]> = {
  'analytics.fact_sales_order': [
    'sales', 'revenue', 'order', 'branch', 'مبيعات', 'ايرادات', 'طلب', 'فرع',
  ],
  'analytics.fact_invoice': ['invoice', 'vat', 'tax', 'فاتوره', 'ضريبه', 'زكاه'],
};
 
export function selectEntities(question: string, limit = 4): Entity[] {
  const q = arNormalize(question);
  return [...ENTITIES]
    .map((entity) => {
      const terms = SYNONYMS[entity.view] ?? [];
      const score = terms.reduce(
        (acc, term) => (q.includes(arNormalize(term)) ? acc + 1 : acc),
        0,
      );
      return { entity, score };
    })
    .sort((a, b) => b.score - a.score)
    .slice(0, limit)
    .map((r) => r.entity);
}

// lib/analytics/generate-sql.ts
import { anthropic } from '@ai-sdk/anthropic';
import { generateObject } from 'ai';
import { z } from 'zod';
import { renderEntity } from './render-schema';
import { selectEntities } from './select-entities';
 
const Plan = z.object({
  answerable: z
    .boolean()
    .describe('False if the question cannot be answered from the provided views.'),
  refusalReason: z.string().nullable(),
  sql: z.string().nullable().describe('A single PostgreSQL SELECT statement.'),
  assumptions: z
    .array(z.string())
    .describe('Business assumptions made, e.g. which statuses were counted as revenue.'),
  chart: z.enum(['table', 'bar', 'line']).default('table'),
});
 
export type Plan = z.infer<typeof Plan>;
 
const SYSTEM = `You translate business questions into PostgreSQL SELECT statements.
 
Rules:
- Output exactly one SELECT statement. No semicolons, no CTEs that write, no DDL, no DML.
- Only reference the views described below. Never reference a table that is not listed.
- Never add a company_id or tenant filter; row-level security applies it automatically.
- Money columns ending in _halalas are integers. Divide by 100.0 and round to 2 decimals for display.
- Wrap every Arabic string literal in analytics.ar_normalize('...') so it matches normalized columns.
- Respect the "!" notes on each view. They encode business rules that override your intuition.
- If the question cannot be answered from these views, set answerable to false and explain why.
- Today's date is provided; resolve relative periods such as "last quarter" against it explicitly.`;
 
export async function generateSql(question: string, today: string): Promise<Plan> {
  const entities = selectEntities(question);
  const schema = entities.map(renderEntity).join('\n\n');
 
  const { object } = await generateObject({
    model: anthropic('claude-sonnet-5'),
    schema: Plan,
    system: SYSTEM,
    prompt: `Today is ${today}.\n\nAvailable views:\n\n${schema}\n\nQuestion: ${question}`,
    temperature: 0,
  });
 
  return object;
}

// lib/analytics/validate-sql.ts
import { Parser } from 'node-sql-parser';
import { ALLOWED_VIEWS } from './semantic-layer';
 
const parser = new Parser();
const OPTS = { database: 'postgresql' } as const;
const MAX_ROWS = 1000;
 
export class SqlRejected extends Error {}
 
export function validateAndBound(sql: string): string {
  let parsed;
  try {
    parsed = parser.parse(sql, OPTS);
  } catch (err) {
    throw new SqlRejected(`Unparseable SQL: ${(err as Error).message}`);
  }
 
  const ast = parsed.ast;
  if (Array.isArray(ast)) {
    throw new SqlRejected('Multiple statements are not allowed.');
  }
  if (ast.type !== 'select') {
    throw new SqlRejected(`Statement type "${ast.type}" is not allowed.`);
  }
 
  // عناصر tableList تأتي بالشكل: "select::null::fact_sales_order"
  for (const entry of parsed.tableList) {
    const [operation, , table] = entry.split('::');
    if (operation !== 'select') {
      throw new SqlRejected(`Non-select operation on ${table}.`);
    }
    const qualified = table.includes('.') ? table : `analytics.${table}`;
    if (!ALLOWED_VIEWS.has(qualified)) {
      throw new SqlRejected(`View ${qualified} is not in the allowlist.`);
    }
  }
 
  // فرض حدّ أعلى حتى لو أغفله النموذج
  const existing = Number((ast as any).limit?.value?.[0]?.value ?? NaN);
  if (!Number.isFinite(existing) || existing > MAX_ROWS) {
    (ast as any).limit = {
      seperator: '',
      value: [{ type: 'number', value: MAX_ROWS }],
    };
  }
 
  return parser.sqlify(ast, OPTS);
}

// lib/analytics/execute.ts
import { Pool } from 'pg';
 
const pool = new Pool({
  connectionString: process.env.ANALYTICS_READONLY_URL,
  max: 5,
});
 
export type QueryResult = {
  columns: string[];
  rows: Record<string, unknown>[];
  durationMs: number;
};
 
export async function executeScoped(
  sql: string,
  companyId: number,
): Promise<QueryResult> {
  const client = await pool.connect();
  const started = Date.now();
  try {
    await client.query('BEGIN READ ONLY');
    // set_config يقبل المعاملات، بخلاف SET LOCAL
    await client.query("SELECT set_config('app.company_id', $1, true)", [
      String(companyId),
    ]);
    await client.query("SET LOCAL statement_timeout = '8s'");
 
    // حلّ الكائنات بصلاحيات القارئ قبل تنفيذ أي شيء
    await client.query(`EXPLAIN ${sql}`);
 
    const result = await client.query(sql);
    return {
      columns: result.fields.map((f) => f.name),
      rows: result.rows,
      durationMs: Date.now() - started,
    };
  } finally {
    // تراجع دائمًا. لا شيء هنا ينبغي أن يُثبَّت أبدًا
    await client.query('ROLLBACK').catch(() => undefined);
    client.release();
  }
}

// app/api/analytics/ask/route.ts
import { NextResponse } from 'next/server';
import { generateSql } from '@/lib/analytics/generate-sql';
import { validateAndBound, SqlRejected } from '@/lib/analytics/validate-sql';
import { executeScoped } from '@/lib/analytics/execute';
import { getSession } from '@/lib/auth';
 
export async function POST(req: Request) {
  const session = await getSession();
  if (!session) return NextResponse.json({ error: 'unauthorized' }, { status: 401 });
 
  const { question } = (await req.json()) as { question?: string };
  if (!question?.trim()) {
    return NextResponse.json({ error: 'question is required' }, { status: 400 });
  }
 
  const today = new Date().toISOString().slice(0, 10);
  const plan = await generateSql(question, today);
 
  if (!plan.answerable || !plan.sql) {
    return NextResponse.json({
      answerable: false,
      reason: plan.refusalReason ?? 'This question cannot be answered from the available data.',
    });
  }
 
  let safeSql: string;
  try {
    safeSql = validateAndBound(plan.sql);
  } catch (err) {
    if (err instanceof SqlRejected) {
      console.warn('sql_rejected', { question, sql: plan.sql, reason: err.message });
      return NextResponse.json({ answerable: false, reason: 'Generated query failed validation.' }, { status: 422 });
    }
    throw err;
  }
 
  const result = await executeScoped(safeSql, session.companyId);
 
  return NextResponse.json({
    answerable: true,
    sql: safeSql,
    assumptions: plan.assumptions,
    chart: plan.chart,
    ...result,
  });
}

// evals/harness.ts
import { createHash } from 'node:crypto';
import type { QueryResult } from '@/lib/analytics/execute';
 
export function fingerprint(result: QueryResult): string {
  const rows = result.rows.map((row) =>
    Object.keys(row)
      .sort()
      .map((key) => {
        const value = row[key];
        // تقريب الأعداد العشرية حتى يتفق 1.0000000001 مع 1.0
        return typeof value === 'number' ? value.toFixed(4) : String(value);
      })
      .join('|'),
  );
  rows.sort(); // ترتيب الصفوف ليس جزءًا من الصحّة إلا إذا طُلب ORDER BY
  return createHash('sha256').update(rows.join('\n')).digest('hex');
}

// evals/golden.test.ts
import { describe, expect, it } from 'vitest';
import { generateSql } from '@/lib/analytics/generate-sql';
import { validateAndBound } from '@/lib/analytics/validate-sql';
import { executeScoped } from '@/lib/analytics/execute';
import { fingerprint } from './harness';
 
const TODAY = '2026-08-15'; // مجمَّد، حتى تكون "الربع الماضي" حتمية
const COMPANY = 1;
 
const GOLDEN = [
  {
    name: 'revenue by branch, last quarter, Arabic',
    question: 'كم بلغت مبيعات كل فرع في الربع الماضي؟',
    expectedSql: `
      SELECT branch_name,
             ROUND(SUM(net_amount_halalas) / 100.0, 2) AS revenue_sar
      FROM analytics.fact_sales_order
      WHERE status IN ('confirmed', 'delivered')
        AND order_date >= DATE '2026-04-01'
        AND order_date <  DATE '2026-07-01'
      GROUP BY branch_name
      ORDER BY revenue_sar DESC`,
  },
  {
    name: 'Dammam only, Persian-yeh spelling in the question',
    question: 'ما إجمالي مبيعات فرع الدمام هذا العام؟',
    expectedSql: `
      SELECT ROUND(SUM(net_amount_halalas) / 100.0, 2) AS revenue_sar
      FROM analytics.fact_sales_order
      WHERE status IN ('confirmed', 'delivered')
        AND branch_city = analytics.ar_normalize('الدمام')
        AND order_date >= DATE '2026-01-01'`,
  },
  {
    name: 'unanswerable — no HR data in the semantic layer',
    question: 'كم عدد الموظفين السعوديين في الشركة؟',
    expectedSql: null,
  },
];
 
describe('text-to-sql golden set', () => {
  for (const testCase of GOLDEN) {
    it(testCase.name, async () => {
      const plan = await generateSql(testCase.question, TODAY);
 
      if (testCase.expectedSql === null) {
        expect(plan.answerable).toBe(false);
        return;
      }
 
      expect(plan.answerable).toBe(true);
      const safeSql = validateAndBound(plan.sql!);
 
      const actual = await executeScoped(safeSql, COMPANY);
      const expected = await executeScoped(testCase.expectedSql, COMPANY);
 
      expect(fingerprint(actual)).toBe(fingerprint(expected));
    }, 30_000);
  }
});