Tool Design & Permissions

package com.interviewpreparator.agents;
import java.util.List;
import java.util.Set;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import static com.interviewpreparator.agents.AgentContracts.*;
/** A local database-backed target with atomic domain mutation and idempotency receipt. */
public final class JdbcPlanTools implements Tools {
    private final JdbcTemplate jdbc;
    private final TransactionTemplate transaction;
    public JdbcPlanTools(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
        this.transaction = new TransactionTemplate(new DataSourceTransactionManager(java.util.Objects.requireNonNull(jdbc.getDataSource())));
    }
    public void initializeLocalSchema() {
        jdbc.execute("""
            create table if not exists agent_plans (tenant text not null, plan_id text not null,
            status text not null, mutation_count int not null, primary key (tenant, plan_id))
            """);
        jdbc.execute("""
            create table if not exists agent_plan_receipts (tenant text not null, owner_id text not null,
            conversation text not null, run_id text not null, call_id text not null,
            tool text not null, resource text not null, result text not null,
            primary key (tenant, owner_id, conversation, run_id, call_id))
            """);
    }
    public void seedPlan(String tenant, String id) {
        jdbc.update("insert into agent_plans values (?, ?, 'IN_PROGRESS', 0) on conflict do nothing", tenant, id);
    }
    @Override public Permission authorize(Scope scope, ToolCall call, Set<String> approvals) {
        if (!Set.of("readPlan", "completePlan").contains(call.tool())) return Permission.DENY;
        Integer count = jdbc.queryForObject("select count(*) from agent_plans where tenant=? and plan_id=?", Integer.class,
                scope.tenant(), call.resource());
        if (count == null || count == 0) return Permission.DENY;
        return !isReadOnly(call) && !approvals.contains(call.resource()) ? Permission.APPROVAL_REQUIRED : Permission.ALLOW;
    }
    @Override public boolean isReadOnly(ToolCall call) { return call.tool().equals("readPlan"); }
    @Override public String execute(Scope scope, ToolCall call, Set<String> approvals) {
        return java.util.Objects.requireNonNull(transaction.execute(status -> {
            if (authorize(scope, call, approvals) != Permission.ALLOW) throw new SecurityException("Forbidden");
            List<String> plans = jdbc.query("select status from agent_plans where tenant=? and plan_id=? for update",
                    (row, index) -> row.getString(1), scope.tenant(), call.resource());
            if (plans.size() != 1) throw new SecurityException("Forbidden");
            List<Receipt> receipts = jdbc.query("""
                select tool, resource, result from agent_plan_receipts
                where tenant=? and owner_id=? and conversation=? and run_id=? and call_id=?
                """, (row, index) -> new Receipt(new ToolCall(call.callId(), row.getString(1), row.getString(2)), row.getString(3)),
                    scope.tenant(), scope.user(), scope.conversation(), scope.runId(), call.callId());
            if (!receipts.isEmpty()) {
                if (!receipts.getFirst().call().equals(call)) throw new SecurityException("Idempotency payload mismatch");
                return receipts.getFirst().result();
            }
            String result = plans.getFirst();
            if (!isReadOnly(call)) {
                jdbc.update("update agent_plans set status='COMPLETED', mutation_count=mutation_count+1 where tenant=? and plan_id=? and status <> 'COMPLETED'",
                        scope.tenant(), call.resource());
                result = "COMPLETED";
            }
            jdbc.update("insert into agent_plan_receipts values (?, ?, ?, ?, ?, ?, ?, ?)", scope.tenant(), scope.user(),
                    scope.conversation(), scope.runId(), call.callId(), call.tool(), call.resource(), result);
            return result;
        }));
    }
    public int mutations(String tenant, String plan) {
        return java.util.Objects.requireNonNull(jdbc.queryForObject("select mutation_count from agent_plans where tenant=? and plan_id=?", Integer.class, tenant, plan));
    }
}