Skip to content

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

8.0 KB
Raw Blame
"""Global search across the user's financial entities.

Implements lightweight case-insensitive ILIKE matching across transactions,
accounts, payees, categories, goals and assets. Designed to power the
command palette (Cmd/Ctrl+K) on the frontend. Kept intentionally simple —
no full-text indexes required — but fast enough for tens of thousands of
rows thanks to per-entity LIMITs and trigger-word scoping.
"""

from __future__ import annotations

import uuid
from dataclasses import dataclass
from decimal import Decimal
from typing import Any, Literal, Optional

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

from app.models.account import Account
from app.models.asset import Asset
from app.models.category import Category
from app.models.goal import Goal
from app.models.payee import Payee
from app.models.transaction import Transaction


EntityType = Literal[
    "transaction",
    "account",
    "payee",
    "category",
    "goal",
    "asset",
]


@dataclass
class SearchHit:
    type: EntityType
    id: str
    label: str
    subtitle: Optional[str] = None
    amount: Optional[Decimal] = None
    currency: Optional[str] = None
    date: Optional[str] = None
    icon: Optional[str] = None
    color: Optional[str] = None
    meta: Optional[dict[str, Any]] = None

    def to_dict(self) -> dict[str, Any]:
        return {
            "type": self.type,
            "id": self.id,
            "label": self.label,
            "subtitle": self.subtitle,
            "amount": float(self.amount) if self.amount is not None else None,
            "currency": self.currency,
            "date": self.date,
            "icon": self.icon,
            "color": self.color,
            "meta": self.meta or {},
        }


def _like(term: str) -> str:
    # Escape SQL LIKE wildcards the user typed, then wrap.
    escaped = term.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
    return f"%{escaped}%"


async def search_all(
    session: AsyncSession,
    workspace_id: uuid.UUID,
    user_id: uuid.UUID,
    query: str,
    per_type_limit: int = 5,
) -> list[dict[str, Any]]:
    """Search across all entity types in the current workspace.

    Returns a flat list of hits ordered by entity type, newest first within
    each group. Matching is case-insensitive ILIKE on the most useful string
    columns per model.

    Transaction search additionally pulls in cross-workspace rows the
    caller participates in via group splits (the Splitwise projection
    — "concert" finds the parent that someone else paid for but is on
    the caller's settlement ledger).
    """
    term = (query or "").strip()
    if len(term) < 1:
        return []

    pattern = _like(term)
    hits: list[SearchHit] = []

    # -- Transactions -------------------------------------------------------
    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_tx_ids = (
        select(TransactionSplit.transaction_id)
        .where(TransactionSplit.group_member_id.in_(viewer_member_ids))
        .distinct()
    )
    tx_result = await session.execute(
        select(Transaction)
        .where(
            or_(
                Transaction.workspace_id == workspace_id,
                Transaction.id.in_(shared_tx_ids),
            ),
            or_(
                Transaction.description.ilike(pattern, escape="\\"),
                Transaction.payee.ilike(pattern, escape="\\"),
                Transaction.notes.ilike(pattern, escape="\\"),
            ),
        )
        .order_by(Transaction.date.desc(), Transaction.created_at.desc())
        .limit(per_type_limit)
    )
    for tx in tx_result.scalars().all():
        hits.append(
            SearchHit(
                type="transaction",
                id=str(tx.id),
                label=tx.description,
                subtitle=tx.payee,
                amount=tx.amount,
                currency=tx.currency,
                date=tx.date.isoformat() if tx.date else None,
                meta={
                    "account_id": str(tx.account_id),
                    "category_id": str(tx.category_id) if tx.category_id else None,
                    "tx_type": tx.type,
                    "source": tx.source,
                },
            )
        )

    # -- Accounts -----------------------------------------------------------
    acc_result = await session.execute(
        select(Account)
        .where(
            Account.workspace_id == workspace_id,
            Account.name.ilike(pattern, escape="\\"),
        )
        .order_by(Account.is_closed.asc(), Account.name.asc())
        .limit(per_type_limit)
    )
    for acc in acc_result.scalars().all():
        hits.append(
            SearchHit(
                type="account",
                id=str(acc.id),
                label=acc.name,
                subtitle=acc.type,
                amount=acc.balance,
                currency=acc.currency,
                meta={"is_closed": acc.is_closed},
            )
        )

    # -- Payees -------------------------------------------------------------
    payee_result = await session.execute(
        select(Payee)
        .where(
            Payee.workspace_id == workspace_id,
            or_(
                Payee.name.ilike(pattern, escape="\\"),
                Payee.notes.ilike(pattern, escape="\\"),
            ),
        )
        .order_by(Payee.is_favorite.desc(), Payee.name.asc())
        .limit(per_type_limit)
    )
    for payee in payee_result.scalars().all():
        hits.append(
            SearchHit(
                type="payee",
                id=str(payee.id),
                label=payee.name,
                subtitle=payee.type,
                meta={"is_favorite": payee.is_favorite},
            )
        )

    # -- Categories ---------------------------------------------------------
    cat_result = await session.execute(
        select(Category)
        .where(
            Category.workspace_id == workspace_id,
            Category.name.ilike(pattern, escape="\\"),
        )
        .order_by(Category.name.asc())
        .limit(per_type_limit)
    )
    for cat in cat_result.scalars().all():
        hits.append(
            SearchHit(
                type="category",
                id=str(cat.id),
                label=cat.name,
                icon=cat.icon,
                color=cat.color,
            )
        )

    # -- Goals --------------------------------------------------------------
    goal_result = await session.execute(
        select(Goal)
        .where(
            Goal.workspace_id == workspace_id,
            Goal.name.ilike(pattern, escape="\\"),
        )
        .order_by(Goal.position.asc(), Goal.name.asc())
        .limit(per_type_limit)
    )
    for goal in goal_result.scalars().all():
        hits.append(
            SearchHit(
                type="goal",
                id=str(goal.id),
                label=goal.name,
                subtitle=goal.status,
                amount=goal.target_amount,
                currency=goal.currency,
                icon=goal.icon,
                color=goal.color,
            )
        )

    # -- Assets -------------------------------------------------------------
    asset_result = await session.execute(
        select(Asset)
        .where(
            Asset.workspace_id == workspace_id,
            Asset.name.ilike(pattern, escape="\\"),
        )
        .order_by(Asset.is_archived.asc(), Asset.position.asc(), Asset.name.asc())
        .limit(per_type_limit)
    )
    for asset in asset_result.scalars().all():
        hits.append(
            SearchHit(
                type="asset",
                id=str(asset.id),
                label=asset.name,
                subtitle=asset.type,
                currency=asset.currency,
                meta={"is_archived": asset.is_archived},
            )
        )

    return [hit.to_dict() for hit in hits]