Overview
The basic guide showed the simplest query scenario. In real project management, however, different roles slice the numbers very differently — product managers want "requirement distribution across projects", developers want "the status of requirements they own", and executives want "overall progress and delay risk". Having the AI re-interpret the metric and write SQL on the fly every time is slow and error-prone.
This case shows what the dev team does when the statistics get complex and the definitions must be unified: wrap the SQL into an endpoint, so once the AI loads a Skill, a single sentence returns statistics in a fixed format.
The business scenario: why standardize metric definitions
The cost of not standardizing
When the AI generates SQL on the fly, the same question can produce different answers:
- The first time you ask about "requirements added this week", the AI reads "added" as creation time
- Asked the same question again, it may define "added" by update time
- Three months later the business changes the definition, and the AI's interpretation changes again
The result: one metric, three numbers. Management can't compare trends, and decisions lose their basis.
The value of standardized definitions
Consistency: whoever asks, whenever they ask, however many times — the number is the same. That's a baseline requirement for management reporting.
Auditability: when an executive asks "where does this number come from", you can trace it to a specific SQL file — not to "whatever the AI generated at the time".
Clear ownership: the dev team owns the SQL definitions, the AI executes the Skill, and definition changes go through a change process — never improvised by the AI on the spot.
What this means for the enterprise
Enterprises run on data, and inconsistent data is more dangerous than no data — a wrong number leads people astray more easily than no number at all.
Once metric definitions are standardized, they stop being one-off query results and become a data asset:
- A new hire onboards and sees the same numbers as everyone else
- Monthly and quarterly reports share consistent definitions, so trend analysis means something
- Cross-team collaboration no longer argues about what a metric means
This is a management problem, not a technical one. Standardizing definitions is the prerequisite for data to actually drive decisions.
Implementation: building the SQL statistics endpoint
The architecture has two layers with separated responsibilities:
┌─────────────────────────────────┐
│ 第一层：自定义 SQL（平台注册） │
│ 存 SQL 模板，#{param} 占位 │
└──────────────┬──────────────────┘
│ 获得 sqlCode
▼
┌─────────────────────────────────┐
│ 第二层：Backend Function（脚本） │
│ 调用 sql.execute(sqlCode,params)│
│ 可加前置校验、后置格式化 │
└──────────────┬──────────────────┘
│ 获得接口路径
▼
AI 调用接口Step 1: Register custom SQL on the platform
The dev team creates the custom SQL on the Lovrabet platform, which assigns a sqlCode:
-- sqlCode: get_requirement_stats
-- 参数：project_id（可选）, time_range（本周/本月/本季度）
SELECT
p.name AS project_name,
COUNT(CASE WHEN r.status = "new") AS new_count,
COUNT(CASE WHEN r.status = "in_progress") AS in_progress_count,
COUNT(CASE WHEN r.status = "completed") AS completed_count,
COUNT(CASE WHEN r.status = "closed") AS closed_count
FROM yt_requirements r
JOIN yt_projects p ON r.project_id = p.id
WHERE r.updated_at >= #{start_date}
AND (#{project_id} IS NULL OR r.project_id = #{project_id})
GROUP BY p.nameStep 2: Build a Backend Function that calls the SQL
The BF calls the SQL and post-processes the output; it holds no SQL logic of its own:
/**
* 需求统计接口
* 脚本名称: get_requirement_stats
* 所属应用: app-173e8652
*/
export default async function getRequirementStats(params, context) {
const { project_id, time_range } = params;
// 计算时间范围起始日期
const now = new Date();
let startDate;
if (time_range === "本月") {
startDate = new Date(now.getFullYear(), now.getMonth(), 1).toISOString();
} else if (time_range === "本季度") {
const quarter = Math.floor(now.getMonth() / 3);
startDate = new Date(now.getFullYear(), quarter * 3, 1).toISOString();
} else {
// 默认本周
const dayOfWeek = now.getDay();
startDate = new Date(now.setDate(now.getDate() - dayOfWeek)).toISOString();
}
// 调用平台已注册的自定义 SQL
const result = await context.client.sql.execute({
sqlCode: "get_requirement_stats",
params: {
start_date: startDate,
project_id: project_id || null
}
});
if (!result.execSuccess || !result.execResult) {
throw new Error("SQL 执行失败：" + (result.errorMsg || "未知错误"));
}
// 后置处理：计算合计行
const rows = result.execResult;
const totals = rows.reduce((acc, row) => ({
new_count: acc.new_count + Number(row.new_count || 0),
in_progress_count: acc.in_progress_count + Number(row.in_progress_count || 0),
completed_count: acc.completed_count + Number(row.completed_count || 0),
closed_count: acc.closed_count + Number(row.closed_count || 0)
}), { new_count: 0, in_progress_count: 0, completed_count: 0, closed_count: 0 });
return {
success: true,
data: rows,
totals: { project_name: "合计", ...totals }
};
}Registered path
Once registered, the BF is reachable at:
POST /api/endpoint/{appCode}/{scriptName}
// 完整路径：POST /api/endpoint/app-173e8652/get_requirement_statsPrerequisites
Before development, the dev team confirms:
- The app app-173e8652 exists
- yt_requirements (requirements table) and yt_projects (projects table) exist and hold data
- The custom SQL
get_requirement_statsis registered on the platform - The operator has read permission on both tables
Skill packaging: one sentence from a business user triggers the stats
Once the endpoint is ready, package the flow as a Skill. The AI then runs automatically: recognize the statistics intent → call the endpoint → format the result.
Skill template
---
name: get_requirement_stats
version: 0.1.0
description: "需求统计查询。触发词：帮我查一下本周需求、各项目需求情况、需求进度报表。"
---
# 需求统计查询
## 第一步：识别统计口径
根据用户输入，识别以下维度：
- 时间范围：本周 / 本月 / 本季度（默认本周）
- 项目范围：特定项目 or 全量
- 角色上下文：如果能获取用户所在项目或负责模块，自动加上筛选
如果用户没有明确说明，询问后继续。
## 第二步：调用统计接口
调用 `get_requirement_stats` 接口（/api/endpoint/app-173e8652/get_requirement_stats），传入：
- project_id（如有）
- time_range（本周/本月/本季度）
如果接口返回失败，告知用户失败原因，终止流程。
## 第三步：格式化返回结果
将接口返回的数据格式化为表格或列表，确保：
- 各项目分列显示状态分布
- 包含合计行
- 数字右对齐，文字左对齐
## 注意事项
- 不需要解释 SQL 逻辑，只需要呈现最终数字
- 如果某个项目没有数据，显示 0 而非空白
- 可以根据用户角色（老板/产品/研发）调整展示重点Example
User input
帮我查一下本周各项目的需求完成情况What the AI does
Loads the Skill → identifies the time range (this week) and project scope (all) → calls the get_requirement_stats endpoint → formats the result into a table
Sample result
✅ 本周需求统计
━━━━━━━━━━━━━━━━━━
项目 新增 进行中 完成 关闭
━━━━━━━━━━━━━━━━━━
平台·官网与文档 3 2 5 1
Agent·运行态 7 4 3 0
Rabetbase-CLI 2 1 6 2
━━━━━━━━━━━━━━━━━━
合计 12 7 14 3FAQ
Q: What if a metric definition changes?
A: The dev team just updates the custom SQL on the platform. The BF script and the Skill stay untouched, and results sync automatically.
Q: What about adding a new dimension?
A: Add fields to the custom SQL — say, "delayed requirements" or "requirements in testing" — and let the Skill handle the presentation.
Q: Can stats be broken down per person?
A: Yes. Add a WHERE r.assignee_id = #{user_id} condition to the custom SQL; the Skill already recognizes contexts like "my requirements".
Q: Will the endpoint slow down on large datasets?
A: For statistics endpoints, consider adding platform-level caching (a "this week" figure doesn't change within the week). The dev team decides the approach after evaluation.
Next steps
When business users keep asking for the same kind of statistics, the product / dev team can:
- Confirm the metric definitions with the business side
- Register the custom SQL on the platform, build the BF, and register it with Lovrabet
- Load the matching Skill on the AI side, triggered by a single sentence