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.

The data model is intentionally flat and narrow. Four tables are all that’s needed to represent a multi-department company’s income, expenses, and monthly budgets across several years. Departments and categories are small, static lookup tables that give every transaction a human-readable label without duplicating strings. The transactions table is the central fact table — every dollar that moved is one row there. The budgets table records what each department planned to spend each month, enabling the budget-vs-actual comparisons that getBudgetStatus powers. Keeping the model this lean means every analytic query is a straightforward GROUP BY with a couple of joins, which makes the SQL in agent/lib/finance.ts easy to read and fast to run.

​
Full Schema

CREATE TABLE IF NOT EXISTS departments (
  id   integer PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS categories (
  id   integer PRIMARY KEY,
  name text NOT NULL UNIQUE,
  kind text NOT NULL CHECK (kind IN ('expense', 'revenue'))
);

CREATE TABLE IF NOT EXISTS transactions (
  id            integer PRIMARY KEY,
  date          date NOT NULL,
  amount        numeric(12, 2) NOT NULL,
  department_id integer NOT NULL REFERENCES departments(id),
  category_id   integer NOT NULL REFERENCES categories(id),
  type          text NOT NULL CHECK (type IN ('income', 'expense')),
  description   text NOT NULL
);

CREATE TABLE IF NOT EXISTS budgets (
  id            integer PRIMARY KEY,
  department_id integer NOT NULL REFERENCES departments(id),
  month         date NOT NULL,
  amount        numeric(12, 2) NOT NULL,
  UNIQUE (department_id, month)
);

CREATE INDEX IF NOT EXISTS idx_tx_date ON transactions(date);
CREATE INDEX IF NOT EXISTS idx_tx_dept ON transactions(department_id);
CREATE INDEX IF NOT EXISTS idx_tx_cat  ON transactions(category_id);

​
Tables

​
departments

A fixed lookup of the five business units in the seeded dataset: Sales, Marketing, Engineering, Operations, and Finance. Each row is just an integer primary key and a unique name. The small, bounded set makes pie-chart breakdowns, budget rows, and anomaly filters predictable — every department always appears, even when it has no transactions in a given range (the profitability query uses a LEFT JOIN for exactly this reason).
Several code paths hardcode 5 departments. If you add or remove a department, sweep agent/lib/finance.test.ts and agent/instructions.md for references.

​
categories

Each category is tagged with a kind of either 'revenue' or 'expense'. This single column drives filtering across most analytics queries — getTrend, getAnomalies, and getCategoryBreakdown all use it to separate income sources from cost lines. Revenue categories:
  • Product Revenue
  • Services Revenue
  • Subscription Revenue
Expense categories:
  • Payroll
  • SaaS
  • Advertising
  • Travel
  • Office
  • Cloud Infrastructure
  • Contractors
  • Recruiting
The kind column has a CHECK constraint enforcing that only 'expense' or 'revenue' are valid values, so new categories can never be inserted with an ambiguous classification.

​
transactions

The main fact table — every income or expense event is one row here. Key columns:
ColumnTypeNotes
datedateThe calendar date of the transaction
amountnumeric(12, 2)Stored as exact decimal; never a float
department_idintegerFK → departments.id
category_idintegerFK → categories.id
typetext'income' or 'expense' (CHECK-constrained)
descriptiontextHuman-readable label generated at seed time
All analytic functions filter on date ranges and aggregate amount. The type column mirrors the categories.kind of the linked category and is the primary split used by getSummary, getTrend, and getCashflow.

​
budgets

One row per department per month, recording the planned spend for that department in that calendar month. The month column always holds the first-of-month date (e.g. 2025-01-01). getBudgetStatus joins budgets against the actual expense total from transactions for the same month and department, then computes variance (actual − budget) and pctUsed (actual ÷ budget). The seed script deliberately sets Engineering’s budget cushion tight so it shows up as the chronically over-budget department — a realistic story for budget charts to surface.

​
Indexes

Three indexes cover the query patterns used by every function in agent/lib/finance.ts:
IndexColumn(s)Purpose
idx_tx_datetransactions(date)Date-range filters appear in every query
idx_tx_depttransactions(department_id)Department joins and GROUP BYs
idx_tx_cattransactions(category_id)Category joins and GROUP BYs
Because the dataset is synthetic and bounded at roughly three years of monthly rows, these indexes are sufficient without composite variants. If the dataset grows significantly, a composite index on (date, department_id) would benefit the trend-by-department query most.

​
Important Constraints

  • budgets uniqueness — UNIQUE (department_id, month) enforces that at most one budget row exists per department per month. The seed script’s INSERT INTO budgets relies on this: attempting to reseed without a prior TRUNCATE would fail on duplicate key violations rather than silently doubling budget amounts.
  • transactions.type — CHECK (type IN ('income', 'expense')) prevents any value other than those two strings. The analytics library never needs to guard against unexpected type values.
  • categories.kind — CHECK (kind IN ('expense', 'revenue')) mirrors the same constraint at the category level, ensuring kind-based filters in SQL queries are always exhaustive.

Build docs developers (and LLMs) love

Get started for freeTalk to us