Open-source personal finance manager. Self-hosted, privacy-first.
import uuid
from datetime import date, timedelta
from decimal import Decimal
from typing import Optional
from sqlalchemy import and_, select, func, case, extract, or_
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload
from app.core.app_clock import app_today
from app.core.config import get_settings
from app.models.account import Account
from app.models.bank_connection import BankConnection
from app.models.transaction import Transaction
from app.models.category import Category
from app.models.recurring_transaction import RecurringTransaction
from app.schemas.dashboard import DashboardSummary, SpendingByCategory, MonthlyTrend, ProjectedTransaction, DailyBalance, DailyBaseline, BalanceHistory
from app.services._query_filters import (
counts_as_user_pnl,
owner_split_offset_by_category,
owner_split_offset_pnl,
reporting_date_col,
viewer_shared_pnl,
viewer_shared_spending_by_category,
)
from app.services import invoice_forecast_service
from app.services.admin_service import get_credit_card_accounting_mode
from app.services.recurring_transaction_service import get_occurrences_in_range
from app.services.asset_service import get_asset_values_at
from app.services.fx_rate_service import _resolve_rate, convert
from app.models.user import User
BASELINE_MONTHS = 12
BASELINE_MIN_MONTHS = 3
_TAIL_ANCHOR = 27
def _month_range(month: date) -> tuple[date, date]:
"""Return (month_start, month_end) for a given date."""
month_start = month.replace(day=1)
if month.month == 12:
month_end = month.replace(year=month.year + 1, month=1, day=1)
else:
month_end = month.replace(month=month.month + 1, day=1)
return month_start, month_end
async def _materialized_recurring_occurrences(
session: AsyncSession,
workspace_id: uuid.UUID,
recurring_ids: set[uuid.UUID],
range_start: date,
range_end: date,
) -> set[tuple[uuid.UUID, date]]:
"""Return linked recurring occurrences already represented by real rows."""
if not recurring_ids:
return set()
result = await session.execute(
select(Transaction.recurring_transaction_id, Transaction.date).where(
Transaction.workspace_id == workspace_id,
Transaction.recurring_transaction_id.in_(recurring_ids),
Transaction.date >= range_start,
Transaction.date < range_end,
)
)
return {
(recurring_id, transaction_date)
for recurring_id, transaction_date in result.all()
if recurring_id is not None
}
async def _get_recurring_projections(
session: AsyncSession,
workspace_id: uuid.UUID,
month_start: date,
month_end: date,
account_ids: Optional[list[uuid.UUID]] = None,
*,
include_transfer_like: bool = False,
) -> list[dict]:
"""Compute virtual recurring transaction projections for a month.
Pure read — no DB writes. Returns list of dicts with category_id, amount, type, currency.
When ``account_ids`` is given (Collection filter), only recurring rules on
those accounts are projected. Empty list → no projections.
Transfer-like categories are excluded by default because most callers use
these rows for P&L, budgets, reports, or spending. Balance projections can
opt in because transfers and investment applications still move cash in
the affected account. Ignored categories never participate."""
if account_ids is not None and len(account_ids) == 0:
return []
filters = [
RecurringTransaction.workspace_id == workspace_id,
RecurringTransaction.is_active == True,
RecurringTransaction.start_date < month_end + timedelta(days=2),
or_(
RecurringTransaction.category_id.is_(None),
Category.is_ignored.is_not(True),
),
]
if not include_transfer_like:
filters.append(or_(
RecurringTransaction.category_id.is_(None),
Category.treat_as_transfer.is_not(True),
))
stmt = (
select(RecurringTransaction)
.outerjoin(Category, RecurringTransaction.category_id == Category.id)
.where(*filters)
)
if account_ids:
stmt = stmt.where(RecurringTransaction.account_id.in_(account_ids))
result = await session.execute(stmt)
recurring_list = list(result.scalars().all())
# A recurrence may already have been materialized without its nominal
# pointer being advanced yet (for example by generate-ahead workflows).
# Suppress the matching virtual row so clients never render or total the
# same occurrence twice.
materialized_occurrences = await _materialized_recurring_occurrences(
session,
workspace_id,
{rec.id for rec in recurring_list},
month_start,
month_end,
)
projections = []
for rec in recurring_list:
# Compute occurrences from the nominal pointer; linked rows are
# filtered explicitly below because the pointer can lag materialization.
occurrences = get_occurrences_in_range(
start=rec.next_occurrence,
frequency=rec.frequency,
end_date=rec.end_date,
range_start=month_start,
range_end=month_end,
intended_day=rec.day_of_month or rec.start_date.day,
weekend_adjustment=rec.weekend_adjustment,
)
for occ_date in occurrences:
if (rec.id, occ_date) in materialized_occurrences:
continue
projections.append({
"account_id": rec.account_id,
"category_id": rec.category_id,
"amount": float(rec.amount),
"type": rec.type,
"currency": rec.currency,
"date": occ_date,
})
return projections
async def _get_forecast_transactions(
session: AsyncSession,
workspace_id: uuid.UUID,
range_start: date,
range_end: date,
account_ids: Optional[list[uuid.UUID]] = None,
range_date_col=None,
) -> list[Transaction]:
"""Return real rows that belong to the forecast, not the current balance.
A pending row is forecast regardless of its date. A posted row dated after
today is also forecast: this is how future installments and generate-ahead
recurring rows follow the same rule as bank pending rows without needing a
source-specific branch at every consumer.
"""
if account_ids is not None and len(account_ids) == 0:
return []
today = app_today()
bucket_date = range_date_col if range_date_col is not None else Transaction.date
stmt = (
select(Transaction)
.join(Account, Transaction.account_id == Account.id)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.workspace_id == workspace_id,
Account.is_closed == False,
bucket_date >= range_start,
bucket_date < range_end,
Transaction.source != "opening_balance",
or_(
Transaction.status == "pending",
bucket_date > today,
),
)
.options(
selectinload(Transaction.account),
selectinload(Transaction.category),
)
.order_by(Transaction.date.asc(), Transaction.created_at.asc())
)
if account_ids:
stmt = stmt.where(Transaction.account_id.in_(account_ids))
result = await session.execute(stmt)
return list(result.scalars().all())
def _counts_as_user_pnl_row(tx: Transaction) -> bool:
"""Python equivalent of ``counts_as_user_pnl`` for loaded forecast rows."""
category = tx.category
return not (
tx.transfer_pair_id
or tx.is_ignored
or tx.exclude_from_pnl
or tx.source == "settlement"
or (category and (category.treat_as_transfer or category.is_ignored))
)
async def _sum_to_primary(
session: AsyncSession,
buckets: dict[str, float],
primary_currency: str,
target_date: Optional[date],
) -> tuple[float, bool]:
"""Sum per-currency buckets in the primary currency, real rates only.
A bucket without a real rate is kept at its native amount (1:1) and the
returned flag reports the estimate, so the UI can surface it instead of
a foreign balance passing as converted (issue #1165, same rule as the
account list in app/api/accounts.py).
"""
total = 0.0
fx_fallback = False
for currency, amount in buckets.items():
if currency == primary_currency:
total += float(amount)
continue
if not amount:
# Zero is zero in any currency: no rate needed, nothing estimated.
continue
rate = await _resolve_rate(session, currency, primary_currency, target_date)
if rate is None:
fx_fallback = True
total += float(amount)
continue
total += float((Decimal(str(amount)) * rate).quantize(Decimal("0.01")))
return total, fx_fallback
async def get_summary(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
month: Optional[date] = None,
balance_date: Optional[date] = None,
account_ids: Optional[list[uuid.UUID]] = None,
asset_group_ids: Optional[list[uuid.UUID]] = None,
) -> DashboardSummary:
if not month:
month = app_today().replace(day=1)
month_start, month_end = _month_range(month)
today = app_today()
# Collection filter (issue #105): show the *raw* P&L/balances of the
# collection's accounts, plus the assets in its wallets (asset_group_ids);
# group-split redistribution and cross-group shares are gated off so the
# filtered numbers stay self-consistent. A wallet-only collection (wallets
# but no accounts) still filters — coerce accounts to empty.
if asset_group_ids is not None and account_ids is None:
account_ids = []
filtered = account_ids is not None
acct_filter = [Transaction.account_id.in_(account_ids)] if filtered else []
# Reporting mode is a global app setting (admin-controlled). When "accrual",
# aggregation queries bucket credit card transactions by the bill's due
# date (effective_date) instead of the purchase date — gives a true
# cash-flow view.
user = await session.get(User, user_id)
accounting_mode = await get_credit_card_accounting_mode(session)
report_date = reporting_date_col(accounting_mode)
# Compute the effective cutoff date for balance calculation
if balance_date:
cutoff = balance_date
elif month_end <= today:
# Past month: last day of that month
cutoff = month_end - timedelta(days=1)
else:
# Current or future month: today
cutoff = today
# ``total_balance`` is deliberately current/posted-only. Forecast rows are
# accumulated separately so a future installment or pending bank row never
# silently changes the number labelled as the current balance.
total_balance = await _total_balance_by_currency(session, workspace_id, cutoff, account_ids)
projected_balance = dict(total_balance)
# For current/future months, project the balance by adding recurring
# projections and real forecast rows from cutoff+1 through month_end.
# A month that has already elapsed has no forecast: `cutoff` is its own
# last day there, so `month_end > cutoff` alone would still let the whole
# pending backlog land on a month that is already history.
if month_end > cutoff and month_end > today:
projection_start = cutoff + timedelta(days=1)
balance_projections = await _get_recurring_projections(
session,
workspace_id,
projection_start,
month_end,
account_ids,
include_transfer_like=True,
)
for proj in balance_projections:
signed = proj["amount"] if proj["type"] == "credit" else -proj["amount"]
projected_balance[proj["currency"]] = projected_balance.get(proj["currency"], 0.0) + signed
# Pending rows are forecast regardless of their transaction date. A
# future-month dashboard still needs to carry an old/stale pending row
# into the projected balance, so do not narrow this query to the
# selected month's start.
balance_forecast_start = date.min
balance_forecast_transactions = await _get_forecast_transactions(
session, workspace_id, balance_forecast_start, month_end, account_ids
)
for tx in balance_forecast_transactions:
if tx.is_ignored or (tx.category and tx.category.is_ignored):
continue
# A connected provider is authoritative for today's current
# number. If it exposes a pending row dated today or earlier, do
# not add that same row again to the projected walk. A recurring
# placeholder is the exception: we generated it from a schedule, so
# it is missing from that number and still has to be applied.
if (
tx.account
and tx.account.connection_id
and tx.status == "pending"
and tx.date <= today
and tx.source != "recurring"
):
continue
account_currency = tx.account.currency if tx.account else tx.currency
amount = (
tx.amount
if tx.currency == account_currency
else (tx.amount_primary or tx.amount)
)
signed = amount if tx.type == "credit" else -amount
projected_balance[account_currency] = (
projected_balance.get(account_currency, 0.0) + float(signed)
)
# Money promised but not yet moved. An invoice is a claim rather
# than a movement, so it never entered here for free, and adding
# it before matching existed would have counted the same money
# twice: the open invoice and the pending bank credit that pays
# it. Only the *unallocated* balance is carried, so the moment a
# payment is linked the claim shrinks and the transaction above
# carries the forecast alone.
#
# Filtered by account? No. A claim has no account until it is
# paid, so narrowing the dashboard to one account cannot include
# it without inventing where the money will land.
# `is None`, not falsy: a collection holding only wallets narrows
# the report to an empty set of bank accounts, and an empty list
# means filtered to nothing rather than not filtered at all.
if account_ids is None:
for claim in await invoice_forecast_service.claims_in_range(
session, workspace_id, projection_start, month_end
):
projected_balance[claim.currency] = (
projected_balance.get(claim.currency, 0.0) + float(claim.signed)
)
# Monthly income and expenses — exclude opening_balance so initial deposits
# don't inflate the month's income figure. counts_as_user_pnl() skips
# paired transfers, transfer-like categories AND settlement movements
# (whose offset is already in the owner's share, see share-only model).
# Only posted (settled) transactions count in the actual period totals;
# pending and future rows are layered into the forecast fields below.
monthly_result = await session.execute(
select(
func.sum(case((Transaction.type == "credit", Transaction.amount), else_=0)),
func.sum(case((Transaction.type == "debit", Transaction.amount), else_=0)),
)
.where(
Transaction.workspace_id == workspace_id,
report_date >= month_start,
report_date < month_end,
report_date <= today,
Transaction.source != "opening_balance",
Transaction.status == "posted",
counts_as_user_pnl(),
*acct_filter,
)
)
monthly_row = monthly_result.one()
monthly_income = float(monthly_row[0] or 0)
monthly_expenses = float(monthly_row[1] or 0)
if not filtered:
# Subtract non-owner shares of the user's own split txs — they paid
# for the others, so those amounts aren't their actual cost.
own_offset_inc, own_offset_exp = await owner_split_offset_pnl(
session,
user_id,
month_start,
month_end,
use_effective_date=False,
workspace_id=workspace_id,
)
monthly_income -= own_offset_inc
monthly_expenses -= own_offset_exp
# Add the viewer's share from group splits where they're a linked
# member but not the owner. Their concert ticket paid by a friend
# is a real expense in their P/L picture.
shared_income, shared_expenses = await viewer_shared_pnl(
session, user_id, month_start, month_end, use_effective_date=False
)
monthly_income += shared_income
monthly_expenses += shared_expenses
# Keep actual and forecast totals separate. Pending rows, future-dated
# installments and generate-ahead rows belong only to the projected view.
real_monthly_income = monthly_income
real_monthly_expenses = monthly_expenses
projected_monthly_income = real_monthly_income
projected_monthly_expenses = real_monthly_expenses
# Add virtual recurring projections
projections = await _get_recurring_projections(
session, workspace_id, month_start, month_end, account_ids
)
forecast_transactions = await _get_forecast_transactions(
session, workspace_id, month_start, month_end, account_ids,
range_date_col=report_date,
)
for proj in projections:
if proj["type"] == "credit":
projected_monthly_income += proj["amount"]
else:
projected_monthly_expenses += proj["amount"]
for tx in forecast_transactions:
if not _counts_as_user_pnl_row(tx):
continue
if tx.type == "credit":
projected_monthly_income += float(tx.amount)
else:
projected_monthly_expenses += float(tx.amount)
# Account count — the active collection's accounts when filtered, else all
# accounts in this workspace (manual + bank-connected).
if filtered:
accounts_count = len(account_ids)
else:
accounts_count = await session.scalar(
select(func.count())
.select_from(Account)
.where(Account.workspace_id == workspace_id)
) or 0
# Pending categorization — exclude opening_balance and transfer pairs
pending_cat_filters = [
Transaction.workspace_id == workspace_id,
Transaction.category_id.is_(None),
Transaction.source != "opening_balance",
# Settlement-sourced rows are auto-generated movements (paying
# back / receiving back a group debt). They aren't expenses or
# income that need a category, so exclude them.
Transaction.source != "settlement",
Transaction.transfer_pair_id.is_(None),
*acct_filter,
]
pending_categorization_result = await session.execute(
select(
func.count(),
func.coalesce(func.sum(func.abs(Transaction.amount)), 0),
)
.select_from(Transaction)
.where(*pending_cat_filters)
)
pending_categorization, pending_categorization_amount = (
pending_categorization_result.one()
)
pending_categorization_amount = abs(float(pending_categorization_amount or 0))
# Get user's primary currency (user already loaded above for reporting mode)
primary_currency = user.primary_currency if user else get_settings().default_currency
# Asset values — use cutoff so past months show historical values. Under a
# collection filter, include only assets in the collection's wallets
# (asset_group_ids); a collection with no wallets → no assets.
assets_value, assets_value_primary = await get_asset_values_at(
session, workspace_id, as_of_date=cutoff, primary_currency=primary_currency,
by_workspace=True,
group_ids=(asset_group_ids or []) if filtered else None,
)
# Add asset values to total balance
for currency, amount in assets_value.items():
total_balance[currency] = total_balance.get(currency, 0.0) + amount
projected_balance[currency] = projected_balance.get(currency, 0.0) + amount
# Convert totals to primary currency. A currency bucket without a real
# rate stays at its native amount and sets the flag, so the estimate is
# visible instead of foreign money passing as converted (issue #1165).
total_balance_primary, fx_fallback = await _sum_to_primary(
session, total_balance, primary_currency, cutoff
)
projected_balance_primary, projected_fx_fallback = await _sum_to_primary(
session, projected_balance, primary_currency, cutoff
)
fx_fallback = fx_fallback or projected_fx_fallback
# Convert income/expenses to primary currency using amount_primary when available
# Use real-only totals (without projections) to avoid double-counting;
# projections are added separately below via convert().
monthly_income_primary = real_monthly_income
monthly_expenses_primary = abs(real_monthly_expenses)
# Use amount_primary sums for more accurate multi-currency income/expenses.
# Same posted-only rule as the native-currency totals above.
primary_result = await session.execute(
select(
func.sum(case((Transaction.type == "credit", Transaction.amount_primary), else_=0)),
func.sum(case((Transaction.type == "debit", Transaction.amount_primary), else_=0)),
)
.where(
Transaction.workspace_id == workspace_id,
report_date >= month_start,
report_date < month_end,
report_date <= today,
Transaction.source != "opening_balance",
Transaction.status == "posted",
counts_as_user_pnl(),
Transaction.amount_primary.isnot(None),
*acct_filter,
)
)
primary_row = primary_result.one()
if primary_row[0] is not None or primary_row[1] is not None:
monthly_income_primary = float(primary_row[0] or 0)
monthly_expenses_primary = abs(float(primary_row[1] or 0))
if not filtered:
# Apply share-only offset in primary currency (FX-converted).
own_offset_inc_pri, own_offset_exp_pri = await owner_split_offset_pnl(
session,
user_id,
month_start,
month_end,
use_effective_date=False,
primary_currency=primary_currency,
workspace_id=workspace_id,
)
monthly_income_primary -= own_offset_inc_pri
monthly_expenses_primary -= own_offset_exp_pri
# Add the viewer's shared shares to primary totals too. The shares
# are stored in the parent transaction's currency, so we convert
# each currency bucket separately rather than re-using shared_income
# / shared_expenses (which were summed without conversion).
# Gated under a collection filter — those parent transactions live in
# other users'/workspaces' accounts, outside the filtered account set.
if not filtered:
from app.models.group import GroupMember
from app.models.transaction_split import TransactionSplit
viewer_member_ids = select(GroupMember.id).where(
GroupMember.linked_user_id == user_id,
GroupMember.is_self.is_(False),
)
shared_currency_rows = await session.execute(
select(
Transaction.currency,
func.sum(
case(
(Transaction.type == "credit", TransactionSplit.share_amount),
else_=0,
)
),
func.sum(
case(
(Transaction.type == "debit", TransactionSplit.share_amount),
else_=0,
)
),
)
.select_from(TransactionSplit)
.join(Transaction, TransactionSplit.transaction_id == Transaction.id)
.where(
TransactionSplit.group_member_id.in_(viewer_member_ids),
Transaction.user_id != user_id,
Transaction.source != "opening_balance",
report_date >= month_start,
report_date < month_end,
report_date <= today,
Transaction.status == "posted",
counts_as_user_pnl(),
)
.group_by(Transaction.currency)
)
for row in shared_currency_rows.all():
cur = row[0]
in_credit = float(row[1] or 0)
in_debit = float(row[2] or 0)
if in_credit:
credit_pri, _ = await convert(
session, Decimal(str(in_credit)), cur, primary_currency
)
monthly_income_primary += float(credit_pri)
if in_debit:
debit_pri, _ = await convert(
session, Decimal(str(in_debit)), cur, primary_currency
)
monthly_expenses_primary += abs(float(debit_pri))
projected_income_primary = monthly_income_primary
projected_expenses_primary = monthly_expenses_primary
# Add recurring projections to primary forecast totals (convert each)
for proj in projections:
proj_converted, _ = await convert(
session, Decimal(str(proj["amount"])),
proj["currency"], primary_currency,
)
if proj["type"] == "credit":
projected_income_primary += float(proj_converted)
else:
projected_expenses_primary += float(proj_converted)
for tx in forecast_transactions:
if not _counts_as_user_pnl_row(tx):
continue
if tx.amount_primary is not None:
forecast_converted = Decimal(str(tx.amount_primary))
else:
forecast_converted, _ = await convert(
session, Decimal(str(tx.amount)), tx.currency, primary_currency,
)
if tx.type == "credit":
projected_income_primary += abs(float(forecast_converted))
else:
projected_expenses_primary += abs(float(forecast_converted))
# Aggregate the user's net pending balance across all groups they
# participate in. We reuse the group balance computation so partial
# settlements are already netted out.
pending_shares_net = (
0.0
if filtered
else await _compute_pending_shares_net(
session, workspace_id, user_id, primary_currency
)
)
return DashboardSummary(
total_balance=total_balance,
total_balance_primary=round(total_balance_primary, 2),
projected_balance=projected_balance,
projected_balance_primary=round(projected_balance_primary, 2),
balance_date=cutoff.isoformat(),
monthly_income=real_monthly_income,
monthly_expenses=abs(real_monthly_expenses),
monthly_income_primary=round(monthly_income_primary, 2),
monthly_expenses_primary=round(monthly_expenses_primary, 2),
projected_income=projected_monthly_income,
projected_expenses=abs(projected_monthly_expenses),
projected_income_primary=round(projected_income_primary, 2),
projected_expenses_primary=round(projected_expenses_primary, 2),
accounts_count=accounts_count,
pending_categorization=pending_categorization,
pending_categorization_amount=pending_categorization_amount,
assets_value=assets_value,
assets_value_primary=round(assets_value_primary, 2),
primary_currency=primary_currency,
fx_fallback=fx_fallback,
pending_shares_net=round(pending_shares_net, 2),
)
async def _compute_pending_shares_net(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
primary_currency: str,
) -> float:
"""Sum, in primary currency, the user's net position across every
group they belong to.
For each group:
- Owner: sum of (positive lines) - sum of (abs negative lines).
Positive net = others owe them; negative = they owe.
- Linked member: their own line. Positive line = they owe the
owner; we flip the sign so the dashboard shows it as negative
(net liability).
"""
from app.models.group import Group, GroupMember
from app.services.balance_service import compute_balances
# Scope to groups that live in the CURRENT workspace (where the
# user is the creator) PLUS groups they're a linked member of from
# other workspaces (cross-workspace projection). Drop `is_self`
# links — those are the user's own self-member in their own group,
# already accounted for by the workspace-scoped path.
owned_q = await session.execute(
select(Group.id).where(
Group.user_id == user_id,
Group.workspace_id == workspace_id,
)
)
owned_ids = {row[0] for row in owned_q.all()}
linked_q = await session.execute(
select(GroupMember.group_id, GroupMember.id).where(
GroupMember.linked_user_id == user_id,
GroupMember.is_self.is_(False),
)
)
linked_rows = list(linked_q.all())
linked_ids = {row.group_id for row in linked_rows} - owned_ids
member_id_for_group = {row.group_id: row.id for row in linked_rows}
total_primary = 0.0
for gid in owned_ids | linked_ids:
balances = await compute_balances(session, gid, workspace_id, user_id)
if not balances:
continue
for line in balances["lines"]:
line_amount = float(line["amount"])
currency = line["currency"]
if gid in owned_ids:
# Positive = member owes the owner (an asset).
# Negative = owner owes the member (a liability).
signed = line_amount
else:
# Linked-member view: only the line about *us* matters
# to our personal net. Flip the sign — `compute_balances`
# frames it from the owner's perspective.
if line["member_id"] != member_id_for_group[gid]:
continue
signed = -line_amount
converted, _ = await convert(
session, Decimal(str(signed)), currency, primary_currency
)
total_primary += float(converted)
return total_primary
async def get_spending_by_category(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
month: Optional[date] = None,
account_ids: Optional[list[uuid.UUID]] = None,
) -> list[SpendingByCategory]:
if not month:
month = app_today().replace(day=1)
month_start, month_end = _month_range(month)
today = app_today()
filtered = account_ids is not None
acct_filter = [Transaction.account_id.in_(account_ids)] if filtered else []
user = await session.get(User, user_id)
accounting_mode = await get_credit_card_accounting_mode(session)
primary_currency = user.primary_currency if user else get_settings().default_currency
report_date = reporting_date_col(accounting_mode)
# Real transactions grouped by category (exclude transfer-like movements
# and closed accounts). Use amount_primary for multi-currency support.
result = await session.execute(
select(
Category.id,
Category.name,
Category.icon,
Category.color,
func.sum(_primary_amount_expr()),
)
.select_from(Transaction)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.workspace_id == workspace_id,
Transaction.type == "debit",
report_date >= month_start,
report_date < month_end,
report_date <= today,
Transaction.source != "opening_balance",
Transaction.status == "posted",
counts_as_user_pnl(),
*acct_filter,
)
.group_by(Category.id, Category.name, Category.icon, Category.color)
.order_by(func.sum(_primary_amount_expr()).desc())
)
# Build a dict of category_id -> {name, icon, color, total}
spending_map: dict[str | None, dict] = {}
for row in result.all():
cat_id = str(row[0]) if row[0] else None
spending_map[cat_id] = {
"name": row[1] or "Sem categoria",
"icon": row[2] or "circle-help",
"color": row[3] or "#6B7280",
"total": abs(float(row[4] or 0)),
"projected": 0.0,
}
# Subtract non-owner shares per category — owner-side splits should
# contribute only the owner's share, not the full amount.
owner_offset = {} if filtered else await owner_split_offset_by_category(
session,
user_id,
month_start,
month_end,
use_effective_date=accounting_mode == "accrual",
primary_currency=primary_currency,
workspace_id=workspace_id,
)
for cat_uuid, offset_total in owner_offset.items():
cat_id = str(cat_uuid) if cat_uuid else None
if cat_id in spending_map:
spending_map[cat_id]["total"] -= offset_total
if spending_map[cat_id]["total"] <= 0:
spending_map.pop(cat_id)
# Add shared shares — the viewer's portion of group-split debits
# they participate in but don't own. The category comes from the
# parent transaction.
shared_by_cat = {} if filtered else await viewer_shared_spending_by_category(
session, user_id, month_start, month_end,
use_effective_date=accounting_mode == "accrual",
primary_currency=primary_currency,
)
if shared_by_cat:
cat_meta_cache: dict[str, dict] = {}
for cat_uuid, share_total in shared_by_cat.items():
cat_id = str(cat_uuid) if cat_uuid else None
if cat_id and cat_id not in cat_meta_cache and cat_id not in spending_map:
meta_row = (
await session.execute(
select(Category.name, Category.icon, Category.color).where(
Category.id == cat_uuid
)
)
).one_or_none()
if meta_row:
cat_meta_cache[cat_id] = {
"name": meta_row[0],
"icon": meta_row[1],
"color": meta_row[2],
}
if cat_id in spending_map:
spending_map[cat_id]["total"] += share_total
else:
meta = cat_meta_cache.get(
cat_id,
{"name": "Sem categoria", "icon": "circle-help", "color": "#6B7280"},
)
spending_map[cat_id] = {
"name": meta["name"],
"icon": meta["icon"],
"color": meta["color"],
"total": share_total,
"projected": 0.0,
}
# Add virtual recurring projections (debit only), converted to primary currency
projections = await _get_recurring_projections(
session, workspace_id, month_start, month_end, account_ids
)
# We need category info for recurring projections — fetch categories
cat_cache: dict[str, dict] = {}
for proj in projections:
if proj["type"] != "debit":
continue
cat_id = str(proj["category_id"]) if proj["category_id"] else None
if cat_id and cat_id not in cat_cache:
# Fetch category info
cat_result = await session.execute(
select(Category.name, Category.icon, Category.color)
.where(Category.id == proj["category_id"])
)
cat_row = cat_result.one_or_none()
if cat_row:
cat_cache[cat_id] = {"name": cat_row[0], "icon": cat_row[1], "color": cat_row[2]}
else:
cat_cache[cat_id] = {"name": "Sem categoria", "icon": "circle-help", "color": "#6B7280"}
# Convert projection amount to primary currency
proj_amount, _ = await convert(
session, Decimal(str(proj["amount"])), proj["currency"], primary_currency,
)
proj_amount_float = float(proj_amount)
if cat_id in spending_map:
spending_map[cat_id]["projected"] += proj_amount_float
else:
info = cat_cache.get(cat_id, {"name": "Sem categoria", "icon": "circle-help", "color": "#6B7280"})
spending_map[cat_id] = {
"name": info["name"],
"icon": info["icon"],
"color": info["color"],
"total": 0.0,
"projected": proj_amount_float,
}
# Pending and future-dated real transactions are forecast rows. They are
# deliberately layered after posted actuals, alongside recurring rules.
forecast_transactions = await _get_forecast_transactions(
session, workspace_id, month_start, month_end, account_ids,
range_date_col=report_date,
)
for tx in forecast_transactions:
if tx.type != "debit" or tx.is_ignored:
continue
category = tx.category
if not _counts_as_user_pnl_row(tx):
continue
cat_id = str(tx.category_id) if tx.category_id else None
if cat_id and cat_id not in cat_cache:
cat_cache[cat_id] = {
"name": category.name if category else "Sem categoria",
"icon": category.icon if category else "circle-help",
"color": category.color if category else "#6B7280",
}
if tx.amount_primary is not None:
projected_amount = float(abs(tx.amount_primary))
else:
amount, _ = await convert(
session,
Decimal(str(abs(tx.amount))),
tx.currency,
primary_currency,
)
projected_amount = float(amount)
if cat_id in spending_map:
spending_map[cat_id]["projected"] += projected_amount
else:
info = cat_cache.get(
cat_id,
{"name": "Sem categoria", "icon": "circle-help", "color": "#6B7280"},
)
spending_map[cat_id] = {**info, "total": 0.0, "projected": projected_amount}
# `total` stays posted-only so the breakdown adds up to the expenses card,
# which is also posted-only. The forecast rides along in `projected_total`
# (actual + forecast) for callers that want the same split the card shows.
grand_total = sum(entry["total"] for entry in spending_map.values())
spending = []
for cat_id, entry in sorted(spending_map.items(), key=lambda x: x[1]["total"], reverse=True):
spending.append(SpendingByCategory(
category_id=cat_id,
category_name=entry["name"],
category_icon=entry["icon"],
category_color=entry["color"],
total=entry["total"],
projected_total=entry["total"] + entry.get("projected", 0.0),
percentage=(entry["total"] / grand_total * 100) if grand_total else 0,
))
return spending
async def get_monthly_trend(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
months: int = 6,
account_ids: Optional[list[uuid.UUID]] = None,
) -> list[MonthlyTrend]:
filtered = account_ids is not None
acct_filter = [Transaction.account_id.in_(account_ids)] if filtered else []
today = app_today()
accounting_mode = await get_credit_card_accounting_mode(session)
report_date = reporting_date_col(accounting_mode)
month_label = func.to_char(report_date, 'YYYY-MM').label('month')
primary_amt = _primary_amount_expr()
result = await session.execute(
select(
month_label,
func.sum(case((Transaction.type == "credit", primary_amt), else_=0)),
func.sum(case((Transaction.type == "debit", primary_amt), else_=0)),
)
.where(
Transaction.workspace_id == workspace_id,
Transaction.source != "opening_balance",
report_date <= today,
Transaction.status == "posted",
counts_as_user_pnl(),
*acct_filter,
)
.group_by(month_label)
.order_by(month_label.desc())
.limit(months)
)
user = await session.get(User, user_id)
primary_currency = user.primary_currency if user else get_settings().default_currency
trend_map: dict[str, list[float]] = {}
for row in result.all():
trend_map[row[0]] = [float(row[1] or 0), abs(float(row[2] or 0))]
# Keep the dashboard trend's current month useful even when its only
# activity is still pending or future-dated. Those rows are forecast data,
# not actuals, but this endpoint has no separate actual/projected series.
forecast_start = today.replace(day=1)
for _ in range(max(months - 1, 0)):
forecast_start = (
forecast_start.replace(year=forecast_start.year - 1, month=12)
if forecast_start.month == 1
else forecast_start.replace(month=forecast_start.month - 1)
)
forecast_end = _month_range(today.replace(day=1))[1]
forecast_transactions = await _get_forecast_transactions(
session, workspace_id, forecast_start, forecast_end, account_ids,
range_date_col=report_date,
)
for tx in forecast_transactions:
if not _counts_as_user_pnl_row(tx):
continue
tx_report_date = (
tx.effective_bill_date
or (tx.effective_date if accounting_mode == "accrual" else tx.date)
)
month_key = f"{tx_report_date.year:04d}-{tx_report_date.month:02d}"
bucket = trend_map.setdefault(month_key, [0.0, 0.0])
if tx.amount_primary is not None:
amount = abs(float(tx.amount_primary))
else:
converted, _ = await convert(
session, Decimal(str(abs(tx.amount))), tx.currency, primary_currency,
)
amount = abs(float(converted))
if tx.type == "credit":
bucket[0] += amount
else:
bucket[1] += amount
trends_raw = sorted(
((month, values[0], values[1]) for month, values in trend_map.items()),
key=lambda row: row[0],
reverse=True,
)[:months]
# Subtract owner non-owner-share offsets per month, and add the
# viewer's shares of others' splits.
adjusted: list[MonthlyTrend] = []
for month_str, income, expenses in trends_raw:
year, mnum = month_str.split("-")
m_start = date(int(year), int(mnum), 1)
m_end = (
date(int(year), int(mnum) + 1, 1)
if int(mnum) < 12
else date(int(year) + 1, 1, 1)
)
if filtered:
own_inc, own_exp, shared_inc, shared_exp = 0.0, 0.0, 0.0, 0.0
else:
own_inc, own_exp = await owner_split_offset_pnl(
session, user_id, m_start, m_end,
use_effective_date=accounting_mode == "accrual",
primary_currency=primary_currency,
workspace_id=workspace_id,
)
shared_inc, shared_exp = await viewer_shared_pnl(
session, user_id, m_start, m_end,
use_effective_date=accounting_mode == "accrual",
primary_currency=primary_currency,
)
adjusted.append(
MonthlyTrend(
month=month_str,
income=max(0.0, income - own_inc + shared_inc),
expenses=max(0.0, expenses - own_exp + shared_exp),
)
)
return list(reversed(adjusted))
async def get_projected_transactions(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
month: Optional[date] = None,
*,
account_id: Optional[uuid.UUID] = None,
from_date: Optional[date] = None,
to_date: Optional[date] = None,
) -> list[ProjectedTransaction]:
"""Return virtual recurring transaction projections for a month,
enriched with description and category info for display.
A date range is inclusive at the API boundary. It is useful for Account
Detail, while the month argument remains the compact dashboard contract.
Transfer-like rules stay in: this lists upcoming movements rather than
P&L, and an investment contribution is money that will leave the account.
The P&L-shaped callers exclude them on their own side, through
``_get_recurring_projections(include_transfer_like=False)``.
"""
if (from_date is None) != (to_date is None):
raise ValueError("from_date and to_date must be provided together")
if from_date and to_date:
if from_date > to_date:
raise ValueError("from_date must be before or equal to to_date")
range_start, range_end = from_date, to_date + timedelta(days=1)
else:
if not month:
month = app_today().replace(day=1)
range_start, range_end = _month_range(month)
# Get user's primary currency for live conversion
user = await session.get(User, user_id)
primary_currency = user.primary_currency if user else get_settings().default_currency
stmt = (
select(RecurringTransaction)
.outerjoin(Category, RecurringTransaction.category_id == Category.id)
.where(
RecurringTransaction.workspace_id == workspace_id,
RecurringTransaction.is_active == True,
RecurringTransaction.start_date < range_end + timedelta(days=2),
or_(
RecurringTransaction.category_id.is_(None),
Category.is_ignored.is_not(True),
),
)
)
if account_id is not None:
stmt = stmt.where(RecurringTransaction.account_id == account_id)
result = await session.execute(stmt)
recurring_list = list(result.scalars().all())
materialized_occurrences = await _materialized_recurring_occurrences(
session,
workspace_id,
{rec.id for rec in recurring_list},
range_start,
range_end,
)
# Pre-fetch categories for all recurring templates that have one
cat_ids = {r.category_id for r in recurring_list if r.category_id}
cat_map: dict[uuid.UUID, tuple[str, str, str, bool]] = {}
if cat_ids:
cat_result = await session.execute(
select(
Category.id,
Category.name,
Category.icon,
Category.color,
Category.treat_as_transfer,
)
.where(Category.id.in_(cat_ids))
)
for row in cat_result.all():
cat_map[row[0]] = (row[1], row[2], row[3], bool(row[4]))
projections: list[ProjectedTransaction] = []
for rec in recurring_list:
occurrences = get_occurrences_in_range(
start=rec.next_occurrence,
frequency=rec.frequency,
end_date=rec.end_date,
range_start=range_start,
range_end=range_end,
intended_day=rec.day_of_month or rec.start_date.day,
weekend_adjustment=rec.weekend_adjustment,
)
cat_name, cat_icon, cat_color, cat_is_transfer = (
cat_map.get(rec.category_id, (None, None, None, False))
if rec.category_id
else (None, None, None, False)
)
# Convert to primary currency at the latest real rate. A missing rate
# stays unknown; virtual projections must never manufacture a 1:1
# conversion just to render a number.
amt_primary = None
if rec.currency != primary_currency:
rate = await _resolve_rate(
session, rec.currency, primary_currency,
)
if rate is not None:
amt_primary = float(
(Decimal(str(rec.amount)) * rate).quantize(Decimal("0.01"))
)
for occ_date in occurrences:
if (rec.id, occ_date) in materialized_occurrences:
continue
projections.append(ProjectedTransaction(
recurring_id=str(rec.id),
account_id=str(rec.account_id) if rec.account_id else None,
description=rec.description,
amount=float(rec.amount),
amount_primary=amt_primary,
currency=rec.currency,
type=rec.type,
date=occ_date.isoformat(),
category_id=str(rec.category_id) if rec.category_id else None,
category_name=cat_name,
category_icon=cat_icon,
category_color=cat_color,
category_treat_as_transfer=cat_is_transfer,
))
return projections
def _signed_balance_expr(account_currency: str = ""):
"""Reusable SQL expression: credit → +amount, debit → −amount.
Uses amount_primary when tx currency differs from account currency."""
if account_currency:
effective = case(
(Transaction.currency == account_currency, Transaction.amount),
else_=func.coalesce(Transaction.amount_primary, Transaction.amount),
)
else:
effective = Transaction.amount
return case(
(Transaction.type == "credit", effective),
else_=-effective,
)
def _signed_transaction_amount(tx: Transaction) -> float:
"""Return a loaded transaction's signed amount in its account currency."""
account_currency = tx.account.currency if tx.account else tx.currency
amount = (
tx.amount
if tx.currency == account_currency
else (tx.amount_primary or tx.amount)
)
signed = amount if tx.type == "credit" else -amount
return float(signed)
async def _signed_forecast_amount_primary(
session: AsyncSession, tx: Transaction, primary_currency: str
) -> float:
"""Convert a loaded forecast row's signed account amount to primary."""
account_currency = tx.account.currency if tx.account else tx.currency
converted, _ = await convert(
session,
Decimal(str(_signed_transaction_amount(tx))),
account_currency,
primary_currency,
)
return float(converted)
def _primary_amount_expr():
"""Amount in primary currency: uses amount_primary when available, falls back to amount."""
return func.coalesce(Transaction.amount_primary, Transaction.amount)
def _signed_primary_expr():
"""Signed amount in primary currency: credit → +, debit → −."""
amt = _primary_amount_expr()
return case(
(Transaction.type == "credit", amt),
else_=-amt,
)
async def _get_open_accounts(
session: AsyncSession,
workspace_id: uuid.UUID,
account_ids: Optional[list[uuid.UUID]] = None,
) -> list[Account]:
"""Get all non-closed accounts for a workspace.
When ``account_ids`` is provided (an active Collection filter, issue #105),
restrict to that subset. An empty list returns no accounts — a collection
with no members shows nothing rather than everything.
"""
if account_ids is not None and len(account_ids) == 0:
return []
stmt = (
select(Account)
.outerjoin(BankConnection)
.where(
or_(
Account.workspace_id == workspace_id,
BankConnection.workspace_id == workspace_id,
),
Account.is_closed == False,
)
)
if account_ids:
stmt = stmt.where(Account.id.in_(account_ids))
result = await session.execute(stmt)
return [row[0] for row in result.all()]
async def _account_balance_at(
session: AsyncSession,
account: Account,
cutoff: date,
*,
include_pending: bool = False,
) -> float:
"""Get balance for a single account at a specific date.
The default is the current/posted view: pending rows and future-dated rows
do not affect it. Connected accounts are the one explicit exception at the
current cutoff: their provider balance is returned verbatim so it keeps
matching the bank app. Forecast callers add pending/future rows separately.
"""
today = app_today()
balance_cutoff = min(cutoff, today)
if account.connection_id:
# Start from the provider's authoritative current balance
current_bal = float(account.balance)
if account.type in {"credit_card", "loan"}:
current_bal = -current_bal
# The provider number is the source of truth for the current balance,
# including whatever pending entries the provider happens to include.
if cutoff >= today:
return current_bal
status_filter = [] if include_pending else [Transaction.status == "posted"]
# Subtract activity after cutoff to get the balance AT cutoff
# Exclude ignored transactions from balance calculation
delta_after = await session.scalar(
select(func.coalesce(func.sum(_signed_balance_expr(account.currency)), 0))
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.account_id == account.id,
Transaction.date > cutoff,
Transaction.date <= today,
Transaction.is_ignored == False,
*status_filter,
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
)
)
historical = current_bal - float(delta_after or 0)
if not include_pending:
# Provider snapshots can include pending rows. Remove those from
# historical posted-only snapshots; the current snapshot above is
# intentionally left untouched to preserve provider parity.
pending_delta = await session.scalar(
select(func.coalesce(func.sum(_signed_balance_expr(account.currency)), 0))
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.account_id == account.id,
Transaction.date <= today,
Transaction.status == "pending",
Transaction.is_ignored == False,
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
)
)
historical -= float(pending_delta or 0)
return historical
else:
# Manual: sum signed transactions up to cutoff
# Exclude ignored transactions from balance calculation
status_filter = [] if include_pending else [Transaction.status == "posted"]
result = await session.scalar(
select(func.coalesce(func.sum(_signed_balance_expr(account.currency)), 0))
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.account_id == account.id,
Transaction.date <= balance_cutoff,
Transaction.is_ignored == False,
*status_filter,
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
)
)
return float(result or 0)
async def _total_balance_by_currency(
session: AsyncSession, workspace_id: uuid.UUID, cutoff: date,
account_ids: Optional[list[uuid.UUID]] = None,
*,
include_pending: bool = False,
) -> dict[str, float]:
"""Get total balance across all open accounts at a date, grouped by currency."""
accounts = await _get_open_accounts(session, workspace_id, account_ids)
if not accounts:
return {}
today = app_today()
balance_cutoff = min(cutoff, today)
connected = [account for account in accounts if account.connection_id]
manual = [account for account in accounts if not account.connection_id]
# Resolve every account of the same kind in one aggregate instead of one
# query per account. This matters especially for balance-history, which
# asks for two historical cutoffs and used to multiply the round-trips by
# the number of accounts.
effective = case(
(Transaction.currency == Account.currency, Transaction.amount),
else_=func.coalesce(Transaction.amount_primary, Transaction.amount),
)
signed = case(
(Transaction.type == "credit", effective),
else_=-effective,
)
async def grouped_sums(account_ids: list[uuid.UUID], *filters) -> dict[uuid.UUID, float]:
if not account_ids:
return {}
result = await session.execute(
select(Transaction.account_id, func.coalesce(func.sum(signed), 0))
.join(Account, Transaction.account_id == Account.id)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.account_id.in_(account_ids),
Transaction.is_ignored == False,
*filters,
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
)
.group_by(Transaction.account_id)
)
return {row[0]: float(row[1] or 0) for row in result.all()}
manual_sums = await grouped_sums(
[account.id for account in manual],
Transaction.date <= balance_cutoff,
*([] if include_pending else [Transaction.status == "posted"]),
)
connected_deltas: dict[uuid.UUID, float] = {}
connected_pending: dict[uuid.UUID, float] = {}
if connected and cutoff < today:
connected_ids = [account.id for account in connected]
connected_deltas = await grouped_sums(
connected_ids,
Transaction.date > cutoff,
Transaction.date <= today,
*([] if include_pending else [Transaction.status == "posted"]),
)
if not include_pending:
connected_pending = await grouped_sums(
connected_ids,
Transaction.date <= today,
Transaction.status == "pending",
)
totals: dict[str, float] = {}
balances: dict[uuid.UUID, float] = {}
for account in accounts:
if account.connection_id:
bal = float(account.balance)
if account.type in {"credit_card", "loan"}:
bal = -bal
if cutoff < today:
bal -= connected_deltas.get(account.id, 0.0)
if not include_pending:
bal -= connected_pending.get(account.id, 0.0)
else:
bal = manual_sums.get(account.id, 0.0)
balances[account.id] = bal
grouped: dict[str, list[Account]] = {}
for account in accounts:
key = account.shared_balance_group or str(account.id)
grouped.setdefault(key, []).append(account)
for group in grouped.values():
total = sum(balances[account.id] for account in group)
if len(group) > 1 and group[0].shared_balance_group:
# Every card in the group carries the same shared debt, so `total`
# counts it len(group) times; strip the duplicates.
current_shared = await _account_balance_at(
session, group[0], today, include_pending=include_pending
)
total -= current_shared * (len(group) - 1)
currency = group[0].currency
totals[currency] = totals.get(currency, 0) + total
return totals
async def _balance_at(
session: AsyncSession, workspace_id: uuid.UUID, cutoff: date,
*, primary_currency_hint: Optional[str] = None,
account_ids: Optional[list[uuid.UUID]] = None,
include_pending: bool = False,
) -> float:
"""Get total balance across all open accounts at a specific date, converted to primary currency.
`primary_currency_hint` lets callers avoid an extra User lookup when they
already know the workspace's primary currency.
"""
totals = await _total_balance_by_currency(
session, workspace_id, cutoff, account_ids, include_pending=include_pending
)
if not totals:
return 0.0
primary_currency = primary_currency_hint or get_settings().default_currency
total = 0.0
for currency, amount in totals.items():
if currency == primary_currency:
total += amount
continue
converted, _ = await convert(session, Decimal(str(amount)), currency, primary_currency)
total += float(converted)
return total
async def _daily_balance_deltas_by_date(
session: AsyncSession,
workspace_id: uuid.UUID,
start: date,
end: date,
*,
primary_currency_hint: Optional[str] = None,
account_ids: Optional[list[uuid.UUID]] = None,
include_pending: bool = False,
exclude_opening_balance: bool = False,
month_starts: Optional[list[date]] = None,
) -> dict[date, float]:
"""Get daily balance deltas for a date range [start, end), keyed by date.
Computes per-account in native currency (using amount_primary only for
foreign txs within an account), grouped by date and account currency,
then converts each currency to primary. This is consistent with
``_balance_at`` and avoids per-transaction FX conversion loops.
"""
effective = case(
(Transaction.currency == Account.currency, Transaction.amount),
else_=func.coalesce(Transaction.amount_primary, Transaction.amount),
)
signed = case(
(Transaction.type == "credit", effective),
else_=-effective,
)
status_filter = [] if include_pending else [Transaction.status == "posted"]
if month_starts is not None and not month_starts:
return {}
date_filters = (
[
or_(*[
and_(
Transaction.date >= month_start.replace(day=1),
Transaction.date < _month_range(month_start)[1],
)
for month_start in month_starts
])
]
if month_starts is not None
else [Transaction.date >= start, Transaction.date < end]
)
result = await session.execute(
select(
Transaction.date,
Account.currency,
func.sum(signed),
)
.join(Account, Transaction.account_id == Account.id)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.workspace_id == workspace_id,
Account.is_closed == False,
*date_filters,
Transaction.date <= app_today(),
Transaction.is_ignored == False,
*status_filter,
*([Transaction.source != "opening_balance"] if exclude_opening_balance else []),
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
*( [Transaction.account_id.in_(account_ids)] if account_ids is not None else [] ),
)
.group_by(Transaction.date, Account.currency)
)
primary_currency = primary_currency_hint or get_settings().default_currency
deltas: dict[date, float] = {}
for tx_date, currency, raw_amount in result.all():
amount = raw_amount or 0
if currency != primary_currency:
converted, _ = await convert(session, Decimal(str(amount)), currency, primary_currency)
amount = converted
deltas[tx_date] = deltas.get(tx_date, 0.0) + float(amount)
return deltas
async def _daily_deltas(
session: AsyncSession,
workspace_id: uuid.UUID,
start: date,
end: date,
*,
primary_currency_hint: Optional[str] = None,
account_ids: Optional[list[uuid.UUID]] = None,
include_pending: bool = False,
exclude_opening_balance: bool = False,
) -> dict[int, float]:
"""Get daily balance deltas for a date range [start, end), keyed by day-of-month."""
by_date = await _daily_balance_deltas_by_date(
session,
workspace_id,
start,
end,
primary_currency_hint=primary_currency_hint,
account_ids=account_ids,
include_pending=include_pending,
exclude_opening_balance=exclude_opening_balance,
)
return {tx_date.day: amount for tx_date, amount in by_date.items()}
def _baseline_from_deltas(
deltas: dict[date, float],
month_starts: list[date],
target_days: int,
today: date,
) -> list[DailyBaseline]:
"""Per-day median and P25/P75 of cumulative change across months with data."""
by_day: dict[int, list[float]] = {}
for month_start in month_starts:
month_start = month_start.replace(day=1)
month_end = _month_range(month_start)[1]
days = (month_end - month_start).days
if any(month_start <= d < month_end for d in deltas):
cumulative = []
running = 0.0
for day in range(1, days + 1):
running += deltas.get(month_start.replace(day=day), 0.0)
cumulative.append(running)
for day in range(1, target_days + 1):
source_day = _source_day(day, target_days, days)
# Days after today have no data yet; don't carry the balance flat.
if month_start.replace(day=source_day) > today:
continue
by_day.setdefault(day, []).append(cumulative[source_day - 1])
baseline = []
for day in range(1, target_days + 1):
values = sorted(by_day.get(day, []))
if len(values) < 2:
baseline.append(DailyBaseline(day=day))
continue
baseline.append(DailyBaseline(
day=day,
median=round(_percentile(values, 0.5), 2),
lower_quartile=(
round(_percentile(values, 0.25), 2)
if len(values) >= BASELINE_MIN_MONTHS else None
),
upper_quartile=(
round(_percentile(values, 0.75), 2)
if len(values) >= BASELINE_MIN_MONTHS else None
),
))
return baseline
def _source_day(day: int, target_days: int, source_days: int) -> int:
"""Map a day of the target month onto a source month of a different length.
Days up to the anchor match by calendar day; the tail is stretched linearly so
the last day always maps to the last day (month-end income lines up).
"""
if day <= _TAIL_ANCHOR or target_days == source_days:
return min(day, source_days)
frac = (day - _TAIL_ANCHOR) / (target_days - _TAIL_ANCHOR)
return min(source_days, round(_TAIL_ANCHOR + frac * (source_days - _TAIL_ANCHOR)))
def _percentile(sorted_values: list[float], q: float) -> float:
"""Linear-interpolated percentile of a non-empty sorted list, q in [0, 1]."""
pos = (len(sorted_values) - 1) * q
lo = int(pos)
hi = min(lo + 1, len(sorted_values) - 1)
return sorted_values[lo] + (sorted_values[hi] - sorted_values[lo]) * (pos - lo)
def _months_before(month: date, count: int) -> list[date]:
months = []
cursor = month.replace(day=1)
for _ in range(count):
cursor = (cursor - timedelta(days=1)).replace(day=1)
months.append(cursor)
return list(reversed(months))
def _normalize_comparison_months(
comparison_months: Optional[list[date]], viewed_month: date
) -> list[date]:
if comparison_months is None:
return _months_before(viewed_month, BASELINE_MONTHS)
if not comparison_months:
raise ValueError("At least one comparison month is required")
months = sorted({month.replace(day=1) for month in comparison_months})
if len(months) > 24:
raise ValueError("A maximum of 24 comparison months is allowed")
if any(month >= viewed_month for month in months):
raise ValueError("Comparison months must be before the viewed month")
return months
async def get_balance_history_months(
session: AsyncSession,
workspace_id: uuid.UUID,
account_ids: Optional[list[uuid.UUID]] = None,
) -> list[str]:
if account_ids is not None and not account_ids:
return []
tx_year = extract("year", Transaction.date)
tx_month = extract("month", Transaction.date)
result = await session.execute(
select(tx_year, tx_month)
.join(Account, Transaction.account_id == Account.id)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(
Transaction.workspace_id == workspace_id,
Account.is_closed == False,
Account.type != "loan",
Transaction.date <= app_today(),
Transaction.status == "posted",
Transaction.is_ignored == False,
Transaction.source != "opening_balance",
or_(
Transaction.category_id.is_(None),
Category.is_ignored == False,
),
*([Transaction.account_id.in_(account_ids)] if account_ids is not None else []),
)
.group_by(tx_year, tx_month)
.order_by(tx_year, tx_month)
)
return [f"{int(year):04d}-{int(month):02d}" for year, month in result.all()]
async def get_balance_history(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
month: Optional[date] = None,
account_ids: Optional[list[uuid.UUID]] = None,
comparison_months: Optional[list[date]] = None,
) -> BalanceHistory:
if not month:
month = app_today().replace(day=1)
month_start, month_end = _month_range(month)
selected_comparison_months = _normalize_comparison_months(
comparison_months, month_start
)
today = app_today()
is_current = month_start.year == today.year and month_start.month == today.month
days_in_month = (month_end - month_start).days
cutoff_day = today.day if is_current else days_in_month
# Look up the user's primary currency once so the helpers can avoid
# re-querying.
user = await session.get(User, user_id)
primary_currency = user.primary_currency if user else get_settings().default_currency
# Loans are long-term debt: they count in net worth, not in the money this
# curve tracks, so it leaves them out (like the sidebar accounts total).
loan_ids = set((await session.execute(
select(Account.id).where(Account.workspace_id == workspace_id, Account.type == "loan")
)).scalars())
balance_account_ids = account_ids
if loan_ids:
if account_ids is None:
balance_account_ids = list((await session.execute(
select(Account.id).where(Account.workspace_id == workspace_id, Account.type != "loan")
)).scalars())
else:
balance_account_ids = [i for i in account_ids if i not in loan_ids]
# Starting balances
current_start = await _balance_at(
session, workspace_id, month_start - timedelta(days=1),
primary_currency_hint=primary_currency, account_ids=balance_account_ids,
)
# Daily deltas from real transactions
current_deltas = await _daily_deltas(
session, workspace_id, month_start, month_end,
primary_currency_hint=primary_currency, account_ids=balance_account_ids,
exclude_opening_balance=True,
)
comparison = None
comparison_month = None
comparison_opening_balance = None
baseline: list[DailyBaseline] = []
if len(selected_comparison_months) == 1:
comparison_month = selected_comparison_months[0]
comparison_start, comparison_end = _month_range(comparison_month)
comparison_opening_balance = await _balance_at(
session,
workspace_id,
comparison_start - timedelta(days=1),
primary_currency_hint=primary_currency,
account_ids=balance_account_ids,
)
comparison_deltas = await _daily_deltas(
session,
workspace_id,
comparison_start,
comparison_end,
primary_currency_hint=primary_currency,
account_ids=balance_account_ids,
exclude_opening_balance=True,
)
if comparison_deltas:
comparison = []
comparison_balance = comparison_opening_balance
comparison_days = (comparison_end - comparison_start).days
for day in range(1, comparison_days + 1):
comparison_balance += comparison_deltas.get(day, 0.0)
is_future = comparison_start.replace(day=day) > today
comparison.append(DailyBalance(
day=day,
balance=None if is_future else round(comparison_balance, 2),
))
else:
baseline_deltas = await _daily_balance_deltas_by_date(
session,
workspace_id,
selected_comparison_months[0],
_month_range(selected_comparison_months[-1])[1],
primary_currency_hint=primary_currency,
account_ids=balance_account_ids,
exclude_opening_balance=True,
month_starts=selected_comparison_months,
)
baseline = _baseline_from_deltas(
baseline_deltas, selected_comparison_months, days_in_month, today
)
# Recurring projections for future days of current month (converted to primary currency)
proj_deltas: dict[int, float] = {}
if month_end > today:
# A future month's chart starts from the posted-only current balance.
# Carry forecast rows dated before that month into its projected
# opening position. Connected accounts are the one exception: their
# provider snapshot is authoritative and may already include pending
# rows dated today or earlier.
if month_start > today:
carried_forecasts = await _get_forecast_transactions(
session, workspace_id, date.min, month_start, balance_account_ids
)
for tx in carried_forecasts:
if tx.is_ignored or (tx.category and tx.category.is_ignored):
continue
if tx.account and tx.account.connection_id and tx.status == "pending" and tx.date <= today:
continue
current_start += await _signed_forecast_amount_primary(
session, tx, primary_currency
)
proj_start = max(month_start, today + timedelta(days=1))
forecast_transactions = await _get_forecast_transactions(
session,
workspace_id,
proj_start,
month_end,
balance_account_ids,
)
for tx in forecast_transactions:
if tx.is_ignored or (tx.category and tx.category.is_ignored):
continue
amount = _signed_transaction_amount(tx)
if tx.account and tx.account.connection_id and tx.date <= today:
continue
converted, _ = await convert(
session,
Decimal(str(amount)),
tx.account.currency if tx.account else tx.currency,
primary_currency,
)
proj_deltas[tx.date.day] = proj_deltas.get(tx.date.day, 0) + float(converted)
projections = await _get_recurring_projections(
session, workspace_id, proj_start, month_end, account_ids
)
for proj in projections:
if proj["account_id"] in loan_ids:
continue
day = proj["date"].day
proj_converted, _ = await convert(
session, Decimal(str(proj["amount"])), proj["currency"], primary_currency,
)
amount = float(proj_converted)
signed = amount if proj["type"] == "credit" else -amount
proj_deltas[day] = proj_deltas.get(day, 0) + signed
# Build current month daily balances
current_daily = []
balance = current_start
for day in range(1, days_in_month + 1):
balance += current_deltas.get(day, 0) + proj_deltas.get(day, 0)
current_daily.append(DailyBalance(
day=day,
balance=round(balance, 2) if day <= cutoff_day else None,
))
return BalanceHistory(
current=current_daily,
opening_balance=round(current_start, 2),
baseline=baseline,
comparison=comparison,
comparison_month=(
comparison_month.strftime("%Y-%m") if comparison_month else None
),
comparison_opening_balance=(
round(comparison_opening_balance, 2)
if comparison_opening_balance is not None else None
),
)