Skip to content

Open-source personal finance manager. Self-hosted, privacy-first.

6.8 KB
Raw Blame
from __future__ import annotations

from typing import Any

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.transaction import Transaction
from app.models.category import Category
from mcp_server.auth import CallContext
from mcp_server.registry import tool
from mcp_server.tools._helpers import num, parse_date, parse_uuid_list, resolve_workspace_id


@tool(
    name="aggregate",
    description=(
        "Aggregate transactions without writing custom SQL. Choose a metric "
        "(sum|count|avg) and a group_by (category|month|account|payee|day). "
        "Returns a list of {bucket, value, count} rows. Amounts use the "
        "user's primary currency when available, falling back to native. "
        "By default counts only POSTED transactions (already settled) — "
        "set status='all' or 'pending' to include scheduled/recurring rows."
    ),
    parameters={
        "type": "object",
        "properties": {
            "metric": {"type": "string", "enum": ["sum", "count", "avg"], "default": "sum"},
            "group_by": {"type": "string", "enum": ["category", "month", "account", "payee", "day"], "default": "category"},
            "from_date": {"type": "string", "format": "date"},
            "to_date": {"type": "string", "format": "date"},
            "account_ids": {"type": "array", "items": {"type": "string", "format": "uuid"}},
            "category_ids": {"type": "array", "items": {"type": "string", "format": "uuid"}},
            "payee_id": {"type": "string", "format": "uuid", "description": "Restrict to a single payee — useful for 'how much did I spend at X?'"},
            "currency": {"type": "string", "description": "Restrict to one native currency (BRL, USD, EUR, ...)"},
            "tx_type": {"type": "string", "enum": ["expense", "income"], "description": "Filter to expenses or income only"},
            "description_contains": {"type": "string", "description": "Case-insensitive substring match against the transaction description — use this to scope to a merchant/keyword like 'uber', 'spotify', 'amigos do bem'."},
            "status": {"type": "string", "enum": ["posted", "pending", "all"], "default": "posted", "description": "Default 'posted' = only money that already moved. Use 'pending' for scheduled/recurring not yet settled, or 'all' to include both."},
            "exclude_transfers": {"type": "boolean", "default": True},
            "limit": {"type": "integer", "minimum": 1, "maximum": 50, "default": 25, "description": "Max bucket rows in the result. Capped at 50."},
        },
        "additionalProperties": False,
    },
    tags=["read", "aggregate"],
)
async def aggregate(
    *,
    session: AsyncSession,
    ctx: CallContext,
    metric: str = "sum",
    group_by: str = "category",
    from_date: str | None = None,
    to_date: str | None = None,
    account_ids: list[str] | None = None,
    category_ids: list[str] | None = None,
    payee_id: str | None = None,
    currency: str | None = None,
    tx_type: str | None = None,
    description_contains: str | None = None,
    status: str = "posted",
    exclude_transfers: bool = True,
    limit: int = 25,
) -> dict[str, Any]:
    # Hard cap regardless of LLM input — keeps payload small enough for
    # token-tight providers.
    limit = max(1, min(int(limit), 50))
    ws_id = await resolve_workspace_id(session, ctx)
    amount_col = func.coalesce(Transaction.amount_primary, Transaction.amount)

    # Group expression + label expression.
    if group_by == "category":
        bucket_id = Transaction.category_id
        label_q = select(Category.id, Category.name).where(Category.workspace_id == ws_id)
    elif group_by == "account":
        bucket_id = Transaction.account_id
        label_q = None
    elif group_by == "payee":
        bucket_id = Transaction.payee_id
        label_q = None
    elif group_by == "month":
        bucket_id = func.to_char(Transaction.date, "YYYY-MM")
        label_q = None
    elif group_by == "day":
        bucket_id = func.to_char(Transaction.date, "YYYY-MM-DD")
        label_q = None
    else:
        return {"error": f"unknown group_by: {group_by}"}

    if metric == "sum":
        value_expr = func.sum(amount_col)
    elif metric == "avg":
        value_expr = func.avg(amount_col)
    else:  # count
        value_expr = func.count(Transaction.id)

    q = (
        select(bucket_id.label("bucket"), value_expr.label("value"), func.count(Transaction.id).label("count"))
        .where(Transaction.workspace_id == ws_id)
    )

    fd = parse_date(from_date)
    td = parse_date(to_date)
    if fd:
        q = q.where(Transaction.date >= fd)
    if td:
        q = q.where(Transaction.date <= td)

    accs = parse_uuid_list(account_ids)
    if accs:
        q = q.where(Transaction.account_id.in_(accs))
    cats = parse_uuid_list(category_ids)
    if cats:
        q = q.where(Transaction.category_id.in_(cats))
    if payee_id:
        # parse_uuid_list returns a list — single payee_id wrapped works too.
        pids = parse_uuid_list([payee_id])
        if pids:
            q = q.where(Transaction.payee_id == pids[0])
    if currency:
        q = q.where(Transaction.currency == currency.upper())
    if description_contains:
        q = q.where(Transaction.description.ilike(f"%{description_contains}%"))
    # Status: default to "posted" so totals reflect money that actually
    # moved. The model can opt into "pending" or "all" when the user
    # explicitly asks about scheduled/projected amounts.
    if status == "posted":
        q = q.where(Transaction.status == "posted")
    elif status == "pending":
        q = q.where(Transaction.status == "pending")
    # status == "all" → no filter

    # Securo convention: type='debit' = expense, type='credit' = income.
    # Amount sign isn't authoritative because some imports normalize it.
    if tx_type == "expense":
        q = q.where(Transaction.type == "debit")
    elif tx_type == "income":
        q = q.where(Transaction.type == "credit")

    if exclude_transfers:
        q = q.where(Transaction.transfer_pair_id.is_(None))

    q = q.group_by(bucket_id).order_by(value_expr.desc().nulls_last()).limit(int(limit))

    rows = (await session.execute(q)).mappings().all()
    label_map: dict[Any, str] = {}
    if label_q is not None:
        for cid, cname in (await session.execute(label_q)).all():
            label_map[cid] = cname

    items = [
        {
            "bucket": str(r["bucket"]) if r["bucket"] is not None else None,
            "label": label_map.get(r["bucket"]),
            "value": num(r["value"]),
            "count": int(r.get("count", 0)),
        }
        for r in rows
    ]
    return {
        "metric": metric,
        "group_by": group_by,
        "from_date": fd.isoformat() if fd else None,
        "to_date": td.isoformat() if td else None,
        "items": items,
        "total": len(items),
    }