Stop Writing Ad‑hoc Reports for Your Boss: Build a One‑sentence Data Query Assistant with GPT + Spring Boot

Problem

Business users frequently need quick answers such as "yesterday's new users", "this month's GMV", or "refund rate" but they do not want to learn SQL or wait for development cycles. Java developers spend most of their time writing temporary reports, adjusting BI dashboards, or modifying SQL queries for slightly different questions.

Solution Overview

Expose well‑defined business metrics as secure Spring Boot services, wrap each metric as a GPT‑callable tool using Spring AI @Tool annotation, and let GPT handle natural‑language understanding while the backend enforces metric definitions, permission checks, and audit logging.

1. Define metric services

@Service
@RequiredArgsConstructor
public class BusinessMetricsService {
    private final OrderRepository orderRepository;
    private final RefundRepository refundRepository;

    public RevenueSummary getRevenueSummary(LocalDate date) {
        BigDecimal paidAmount = orderRepository.sumPaidAmount(date.atStartOfDay(), date.plusDays(1).atStartOfDay());
        long paidOrderCount = orderRepository.countPaidOrders(date.atStartOfDay(), date.plusDays(1).atStartOfDay());
        return new RevenueSummary(date, paidAmount == null ? BigDecimal.ZERO : paidAmount, paidOrderCount);
    }

    public RefundRateSummary getRefundRate(LocalDate start, LocalDate end) {
        long paidOrders = orderRepository.countPaidOrders(start.atStartOfDay(), end.plusDays(1).atStartOfDay());
        long refundedOrders = refundRepository.countRefundedOrders(start.atStartOfDay(), end.plusDays(1).atStartOfDay());
        BigDecimal refundRate = paidOrders == 0 ? BigDecimal.ZERO : BigDecimal.valueOf(refundedOrders)
                .divide(BigDecimal.valueOf(paidOrders), 4, RoundingMode.HALF_UP);
        return new RefundRateSummary(start, end, paidOrders, refundedOrders, refundRate);
    }
}

These services encapsulate the business logic and provide a single source of truth for metric definitions.

2. Expose metrics as GPT‑callable tools

@Component
@RequiredArgsConstructor
public class BusinessMetricsTools {
    private final BusinessMetricsService metricsService;

    @Tool(description = """
        查询指定日期的收入汇总。
        适用于用户询问今天收入、昨天收入、某一天订单金额时调用。
        返回已支付订单金额和已支付订单数量。
        不包含未支付、取消和测试订单。
    """)
    public RevenueSummary queryRevenueByDate(@ToolParam(description = "查询日期，格式为 yyyy-MM-dd") String date) {
        return metricsService.getRevenueSummary(LocalDate.parse(date));
    }

    @Tool(description = """
        查询指定日期范围内的退款率。
        适用于用户询问最近退款率、退款是否变高、某段时间退款情况时调用。
        退款率 = 已退款订单数 / 已支付订单数。
    """)
    public RefundRateSummary queryRefundRate(
            @ToolParam(description = "开始日期，格式为 yyyy-MM-dd") String startDate,
            @ToolParam(description = "结束日期，格式为 yyyy-MM-dd") String endDate) {
        return metricsService.getRefundRate(LocalDate.parse(startDate), LocalDate.parse(endDate));
    }
}

Each tool includes a clear description, usage scenario, and metric scope so that GPT knows exactly what it can invoke.

3. System prompt to constrain GPT

你是一个电商经营数据分析助手。
只能基于工具返回的数据回答问题，不允许编造任何经营数据。
当用户询问收入、订单、退款、趋势、商品排行等问题时，请优先判断是否已有工具可以查询。
规则：
1. 不要自己生成 SQL。
2. 不要猜测数据库中不存在的数据。
3. 如果工具无法回答，请明确说明当前系统暂不支持该指标。
4. 如果用户问题缺少时间范围，请先追问时间范围。
5. 回答时要说明统计口径。
6. 如果数据出现明显波动，只给出可能原因，不要当成确定结论。
7. 不要输出用户手机号、订单号、支付流水号等敏感信息。
8. 不要提供任何绕过权限的数据查询建议。
回答风格：
1. 先给结论。
2. 再给关键数字。
3. 最后给一句建议。
4. 尽量用业务人员能看懂的话。

The prompt ensures GPT never fabricates data, respects permissions, and always cites the metric definition.

4. Combine tools with Spring AI ChatClient

@RestController
@RequiredArgsConstructor
@RequestMapping("/api/ai/metrics")
public class AiMetricsController {
    private final ChatClient chatClient;
    private final BusinessMetricsTools businessMetricsTools;

    @PostMapping("/ask")
    public String ask(@RequestBody AskMetricsRequest request) {
        return chatClient.prompt()
                .system("""
                    你是一个电商经营数据分析助手。
                    只能基于工具返回的数据回答问题。
                    不允许编造数据。
                    回答时必须说明统计口径。
                """)
                .user(request.question())
                .tools(businessMetricsTools)
                .call()
                .content();
    }
}

The controller forwards the user’s natural‑language question to GPT, which selects the appropriate tool, executes it, and returns a formatted answer.

5. Permission checks

@Service
@RequiredArgsConstructor
public class MetricsPermissionService {
    private final CurrentUserService currentUserService;

    public void checkCanViewRevenue() {
        CurrentUser user = currentUserService.getCurrentUser();
        if (!user.hasRole("BOSS") && !user.hasRole("OPERATIONS") && !user.hasRole("FINANCE")) {
            throw new BusinessException("无权查看收入数据");
        }
    }
}

Each metric method calls the permission service before querying the database.

public RevenueSummary getRevenueSummary(LocalDate date) {
    permissionService.checkCanViewRevenue();
    // query logic as shown earlier
}

6. Audit logging

@Entity
@Table(name = "ai_metrics_query_log")
@Getter @Setter
public class AiMetricsQueryLog {
    @Id private UUID id;
    private Long userId;
    private String question;
    private String toolName;
    private String argumentsJson;
    private Boolean success;
    @Column(length = 5000) private String answer;
    private Instant createdAt;
}

Every GPT‑initiated query records the user, question, invoked tool, arguments, success flag, token usage, and final answer, providing traceability for business decisions.

7. Minimal viable version roadmap

Pick three high‑frequency metrics (e.g., yesterday’s revenue, 7‑day revenue trend, refund rate).

Implement them as Java service methods and fix the metric definition in code.

Annotate each method with @Tool and provide detailed descriptions.

Add role‑based permission checks and audit logging.

Provide a simple single‑line UI (e.g., an input box that sends the question to /api/ai/metrics/ask).

Keeping the scope narrow enables rapid delivery, demonstrates real business value, and avoids the complexity of an over‑ambitious AI platform.

Example Interaction

User asks: "昨天收入怎么样？"

GPT selects queryRevenueByDate, receives RevenueSummary, and returns:

昨天已支付订单收入为 128,430 元，共 936 笔已支付订单。
统计口径：仅包含已支付订单，不包含未支付、取消和测试订单。
建议关注：如果想判断收入是否异常，建议继续查看前一天或最近 7 天趋势。

This response is generated without GPT writing SQL, without exposing sensitive fields, and with the metric’s statistical definition clearly stated.