Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/astrxnomo/financial-analytics-agent/llms.txt

Use this file to discover all available pages before exploring further.

Every chart, every agent tool response, and every REST API endpoint in this project draws from the same source of SQL truth: agent/lib/finance.ts. Rather than embedding query logic inside individual route handlers or tool definitions, the library centralizes all analytics SQL in one file. The agent tools in agent/tools/* are thin defineTool + Zod wrappers that call into these functions and return their output verbatim. The REST routes under app/api/finance/* do the same via parseQuery. This means fixing a query, adding a filter, or changing how a value is computed happens in exactly one place, and both consumers benefit immediately.

​
Type Definitions

All input shapes and return types are defined in agent/lib/finance.types.ts: 
export type Metric = "income" | "expense";
export type GroupBy = "month" | "department";

// The category-breakdown chart only draws this many categories by total,
// folding the rest into an "Other" band (see category-breakdown-chart.tsx) —
// shared here (a types-only module, safe to import from client components)
// so both the server-side tool and the client chart agree on the same cutoff
// without either recomputing or duplicating it.
export const CATEGORY_BREAKDOWN_TOP_N = 8;

export interface Summary { from: string; to: string; income: number; expense: number; net: number; }
export interface TrendPoint { period: string; department?: string; value: number; }
export interface BudgetRow { department: string; budget: number; actual: number; variance: number; pctUsed: number; }
export interface Anomaly {
  id: number; date: string; amount: number; department: string; category: string;
  description: string; categoryMean: number; categoryStdDev: number;
}

export interface CategorySlice { period: string; category: string; value: number; }
export interface CashflowPoint {
  period: string; income: number; expense: number; net: number; cumulativeNet: number;
}

// Income vs. expense and net profit per department for a range. `margin` is
// net / income, or null for a cost-center department that booked no income
// (dividing by zero is meaningless — the chart/answer treats null as "n/a"
// rather than 0%, since 0% would wrongly imply break-even).
export interface ProfitByDept {
  department: string; income: number; expense: number; net: number; margin: number | null;
}

export interface Highlights {
  dataFrom: string;
  dataTo: string;
  latestMonth: string;
  topAnomaly?: { department: string; category: string; amount: number; date: string };
  mostOverBudgetDept?: { department: string; overMonths: number };
  fastestGrowingCategory?: { category: string; multiple: number };
}

// Meta-stats about the dataset itself (row counts, coverage), as opposed to
// the financial figures inside it — answers "how much data do we have"
// questions distinctly from "what did we earn/spend" ones.
export interface DataOverview {
  dataFrom: string;
  dataTo: string;
  departments: number;
  categories: { total: number; revenue: number; expense: number };
  transactions: number;
  budgets: number;
}

​
Function Reference

​
getSummary

getSummary({ from, to }: { from: string; to: string }): Promise<Summary>
Sums all transaction amounts grouped by type over the given date range, returning total income, total expense, and net (income − expense). This is the simplest aggregate query and is the source for the summary-tiles component at the top of a financial overview.

​
getTrend

getTrend({
  metric,
  groupBy,
  from,
  to,
  departments?,
}: {
  metric: Metric;
  groupBy: GroupBy;
  from: string;
  to: string;
  departments?: string[];
}): Promise<TrendPoint[]>
Returns a monthly time series for the chosen metric ("income" or "expense"). When groupBy is "department", each returned TrendPoint includes a department string, producing one series per department. When groupBy is "month", all departments are aggregated into a single total series. The optional departments array filters the result to a subset of departments. The trend chart component renders lines for "month" grouping and grouped bars for "department" grouping.

​
getCategoryBreakdown

getCategoryBreakdown({
  from,
  to,
  metric?,
  department?,
  category?,
}: {
  from: string;
  to: string;
  metric?: Metric;
  department?: string;
  category?: string;
}): Promise<CategorySlice[]>
Returns per-category monthly totals for the chosen metric (defaults to "expense"). All three filter parameters are optional — omitting department includes all departments, omitting category includes all categories. The chart component folds categories beyond CATEGORY_BREAKDOWN_TOP_N (8) into an “Other” band, using the same constant exported from finance.types.ts so server and client agree on the cutoff without duplicating it.

​
getCashflow

getCashflow({ from, to }: { from: string; to: string }): Promise<CashflowPoint[]>
Returns one CashflowPoint per calendar month in the range, each with income, expense, net (income − expense for that month), and cumulativeNet (running sum of all net values up to and including that month). The cumulative net is computed in JavaScript after the query, not in SQL — it accumulates across the returned rows in order, so the result array must not be reordered after calling this function.

​
getBudgetStatus

getBudgetStatus({
  month,
  departments?,
}: {
  month: string;
  departments?: string[];
}): Promise<BudgetRow[]>
Returns one BudgetRow per department for the specified month, joining the budgets table against actual expense totals from transactions. Each row includes budget (the planned amount), actual (the real spend), variance (actual − budget, positive means over-budget), and pctUsed (actual ÷ budget, where a value above 1.0 means over-budget). Departments with no budget row return budget: 0. The seed deliberately sets Engineering’s budget tight so it shows a chronic over-budget pattern.

​
getProfitability

getProfitability({
  from,
  to,
  departments?,
}: {
  from: string;
  to: string;
  departments?: string[];
}): Promise<ProfitByDept[]>
Returns income, expense, net, and margin per department, sorted most-profitable-first by net. The margin field is net / income when income is greater than zero, or null for departments that booked no revenue in the range (cost centers). A null margin is treated as “n/a” in charts and answers — returning 0 would wrongly imply break-even. The query uses a LEFT JOIN with date bounds in the JOIN clause rather than the WHERE clause, ensuring departments with no transactions in the range still appear as rows with zeroed-out values rather than disappearing from the result.

​
getAnomalies

getAnomalies({
  from,
  to,
  threshold?,
  departments?,
  categories?,
}: {
  from: string;
  to: string;
  threshold?: number;
  departments?: string[];
  categories?: string[];
}): Promise<Anomaly[]>
Identifies statistical outliers in expense transactions. For each expense category, it computes the mean and standard deviation of all transaction amounts in the date range, then flags any transaction exceeding mean + threshold × stdDev. The default threshold is 2.5. Each returned Anomaly includes the raw transaction fields plus categoryMean and categoryStdDev so callers can contextualize how far out the outlier sits. A critical design detail: the statistical baseline (mean and stdDev per category) is always computed from the full unfiltered dataset first. The departments and categories filter parameters only narrow which already-identified outliers are returned — they do not shrink the sample used for baseline computation. Filtering the source rows first would change what counts as “unusual” depending on the departments asked about, rather than just showing a consistent subset of a dataset-wide anomaly list.

​
getHighlights

getHighlights(): Promise<Highlights>
An internal function not exposed as an agent tool. It returns the data date range, the fastest-growing revenue category (by comparing the first 6 months vs. the last 6 months of the data window), the department that has been most frequently over budget, and the single largest non-Payroll anomaly. Payroll is excluded from the top anomaly because it scales with headcount and routinely trips the threshold on routine growth, not genuine one-off spend. getHighlights is consumed by two callers: the /api/finance/highlights REST route (which feeds the suggested-question chips in the chat UI via useFinanceHighlights) and the agent/instructions/dates.ts dynamic instructions resolver (which grounds the agent’s session context in real data boundaries every time a session starts).

​
getDataOverview

getDataOverview(): Promise<DataOverview>
Returns row counts and date coverage for the entire dataset — how many departments, categories (split by revenue vs. expense), transactions, and budgets exist, plus the earliest and latest transaction dates. This answers “how much data do we have” questions distinctly from “what did we earn or spend” ones, and is exposed as the /api/finance/data-overview REST route as well as the get_data_overview agent tool.

​
Helper Functions

Three small utilities live at the top of finance.ts and are used throughout the file:
  • num(v) — Converts a Postgres numeric string (or any unknown value) to a JavaScript number via Number(v). Every field that comes back from the database as numeric is passed through num() before being included in a return object.
  • day(v) — Normalizes a value to an ISO date string (YYYY-MM-DD). When the value is a Date object (as returned by the postgres driver for date columns), it calls .toISOString().slice(0, 10). Otherwise it coerces to a string directly.
  • addMonths(iso, months) — Adds a number of months to an ISO date string using UTC arithmetic, returning a new ISO date string. Used internally by getHighlights to compute the growth comparison windows.
Money is stored as Postgres numeric. The num() helper converts to JS number at the lib boundary. Never pass raw numeric strings up to tools or charts.

​
DB Connection

agent/lib/db.ts exports a single db() function that returns the shared postgres client: 
import postgres from "postgres";

let client: ReturnType<typeof postgres> | null = null;

export function db() {
  if (!client) {
    const url = process.env.DATABASE_URL;
    if (!url) throw new Error("DATABASE_URL is not set.");
    client = postgres(url);
  }
  return client;
}
This is a lazy singleton — the client is created on the first call to db() and reused for every subsequent call in the same process. The DATABASE_URL environment variable is read at call time, not at module load time, which means the module can be imported safely in environments where the variable isn’t yet set (e.g. during TypeScript compilation). In practice, DATABASE_URL must be present in .env.local for any code path that actually executes a query.

Build docs developers (and LLMs) love

Get started for freeTalk to us