Skip to content

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

126 KB
Raw Blame
import hashlib
import logging
import re
import uuid
from dataclasses import dataclass
from datetime import date, datetime, timedelta, timezone
from decimal import Decimal, InvalidOperation
from typing import Literal, Optional

from sqlalchemy import delete, exists, func, or_, select, update
from sqlalchemy.exc import IntegrityError
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.asset import Asset
from app.models.asset_group import AssetGroup
from app.models.asset_value import AssetValue
from app.models.bank_connection import BankConnection
from app.models.account import Account
from app.models.category import Category
from app.models.institution import Institution
from app.models.goal import Goal
from app.models.collection import collection_asset_groups
from app.models.credit_card_bill import CreditCardBill
from app.models.payee import Payee, PayeeMapping
from app.models.transaction import Transaction
from app.models.user import User
from app.providers import get_provider
from app.providers.base import (
    AccountData,
    HoldingData,
    ProviderNotConfiguredError,
    ProviderRateLimited,
    ProviderUserActionRequired,
    SessionExpiredError,
    TransactionData,
)
from app.services import oauth_state
from app.services import admin_service
from app.services import reconciliation_service, recurring_match_service
from app.services.text_similarity import token_overlap
from app.services.account_service import (
    _provider_to_internal_balance,
    sync_opening_balance_for_connected_account,
)
from app.services.asset_group_service import (
    _unique_default_name,
    ensure_group_for_connection,
)
from app.services.credit_card_service import apply_effective_date
from app.services.rule_engine import merge_notes
from app.services.rule_service import apply_rules_to_transaction, preview_rules_for_transaction
from app.services.transfer_detection_service import detect_transfer_pairs
from app.services.fx_rate_service import stamp_primary_amount
from app.services.payee_service import get_or_create_payee
from app.services.transaction_match_service import find_unique_transaction_match

logger = logging.getLogger(__name__)

LOCAL_IMPORT_SOURCES = {"import", "csv", "ofx", "qif", "camt"}

settings = get_settings()

_PROVIDER_SELL_DATE_METADATA_KEY = "_securo_provider_sell_date"


def _clean_logo_url(value: object) -> Optional[str]:
    """Normalize a provider-supplied logo to a non-empty string or None.

    Guards the DB column against anything a provider hands back that isn't a
    usable URL (None, empty string, a non-string, or one longer than the
    column — truncating would store a broken URL, so it's dropped), so a
    misbehaving integration can never write junk into a ``logo_url`` column
    or abort a sync with a DataError.
    """
    if isinstance(value, str) and value.strip() and len(value) <= 500:
        return value
    return None


def _clean_institution_name(value: object) -> Optional[str]:
    """Same guard as _clean_logo_url, for the 255-char institution columns."""
    return value[:255] if isinstance(value, str) and value.strip() else None


def _wallet_external_id(connection_external_id: str, account_key: Optional[str]) -> str:
    """The per-account wallet key, squeezed into the 255-char column.

    Truncation alone could collide two accounts; over-long composites keep a
    deterministic digest suffix instead.
    """
    if not account_key:
        return connection_external_id
    key = f"{connection_external_id}::{account_key}"
    if len(key) <= 255:
        return key
    digest = hashlib.sha256(key.encode("utf-8")).hexdigest()
    return f"{key[:214]}::{digest[:39]}"


def _is_auto_wallet_name(name: str, institution_name: str) -> bool:
    """True while a wallet still carries its creation-time auto-name — the
    institution label, possibly with _unique_default_name's " N" suffix."""
    if name == institution_name:
        return True
    return bool(re.fullmatch(re.escape(institution_name) + r" \d+", name))


async def _resolve_institution(
    session: AsyncSession,
    connection_id: uuid.UUID,
    cache: dict[str, Institution],
    acc_data: AccountData,
) -> Optional[Institution]:
    """Get-or-create the Institution row an account's provider hint names.

    Matched by the provider's stable org id when it sends one, so a bank
    renamed on the provider side updates its row in place instead of minting
    a new one (review on #654); name identity is the fallback for servers
    that only send a name. Most Pluggy/Enable connections are one
    institution and never send this hint (returns None, serialization falls
    back to the connection's own fields) — the exception is a Pluggy
    connection spanning a banking group's brokerage arm (issue #723), which
    the provider detects and hints the same way SimpleFIN already does.
    """
    name = _clean_institution_name(acc_data.institution_name)
    if not name:
        return None
    ext = _clean_institution_name(acc_data.institution_external_id)
    key = f"id:{ext}" if ext else f"name:{name}"
    if key in cache:
        return cache[key]

    inst: Optional[Institution] = None
    if ext:
        inst = (
            await session.execute(
                select(Institution).where(
                    Institution.connection_id == connection_id,
                    Institution.external_id == ext,
                )
            )
        ).scalar_one_or_none()
        if inst is None:
            # Adopt a row created before the server sent org ids, so the
            # accounts pointing at it don't get orphaned onto a new row.
            inst = (
                await session.execute(
                    select(Institution).where(
                        Institution.connection_id == connection_id,
                        Institution.external_id.is_(None),
                        Institution.name == name,
                    )
                )
            ).scalar_one_or_none()
            if inst is not None:
                inst.external_id = ext
    else:
        # Name-only hint. Match any row with this name — id-keyed rows
        # included, so a payload that intermittently drops org ids doesn't
        # spawn duplicates. NULLS FIRST keeps the pick deterministic.
        inst = (
            await session.execute(
                select(Institution)
                .where(
                    Institution.connection_id == connection_id,
                    Institution.name == name,
                )
                .order_by(Institution.external_id.asc().nulls_first())
                .limit(1)
            )
        ).scalar_one_or_none()

    if inst is None:
        inst = Institution(
            connection_id=connection_id,
            external_id=ext,
            name=name,
            logo_url=_clean_logo_url(acc_data.institution_logo_url),
        )
        try:
            # Savepoint: a concurrent sync (scheduled + manual) can insert
            # the same identity between our SELECT and this flush. The
            # partial unique index rejects the loser — reuse the winner's
            # row instead of failing the whole sync.
            async with session.begin_nested():
                session.add(inst)
                await session.flush()
        except IntegrityError:
            inst = (
                await session.execute(
                    select(Institution)
                    .where(
                        Institution.connection_id == connection_id,
                        Institution.external_id == ext
                        if ext
                        else Institution.name == name,
                    )
                    .limit(1)
                )
            ).scalar_one()
    else:
        if inst.name != name:
            inst.name = name  # provider-side rename
        # The logo is favicon-derived from the org's website, never user-set,
        # so it follows the provider too — a rename that moves domains would
        # otherwise keep the old bank's icon forever.
        new_logo = _clean_logo_url(acc_data.institution_logo_url)
        if new_logo is not None and inst.logo_url != new_logo:
            inst.logo_url = new_logo
    cache[key] = inst
    return inst

PLUGGY_CATEGORY_MAP = {
    "Eating out": "Alimentação",
    "Restaurants": "Alimentação",
    "Food": "Alimentação",
    "Groceries": "Mercado",
    "Supermarkets": "Mercado",
    "Pharmacy": "Saúde",
    "Health": "Saúde",
    "Taxi and ride-hailing": "Transporte",
    "Transport": "Transporte",
    "Gas": "Transporte",
    "Travel": "Transporte",
    "Housing": "Moradia",
    "Rent": "Moradia",
    "Utilities": "Moradia",
    "Entertainment": "Lazer",
    "Leisure": "Lazer",
    "Education": "Educação",
    "Subscriptions": "Assinaturas",
    "Online services": "Assinaturas",
    "Transfer": "Transferências",
    "Transfers": "Transferências",
    "Wire transfers": "Transferências",
}


def _sync_assets_enabled(settings: Optional[dict]) -> bool:
    """Return whether provider investment holdings should sync for a connection.

    Missing settings keep the legacy behavior (enabled). Users can opt out per
    connection via Connection settings without disabling account/transaction sync.
    """
    return (settings or {}).get("sync_assets", True) is not False


def _account_balance(acc_data: AccountData, deducted: set[str]) -> Decimal:
    """Balance to store for a synced account.

    Some banks auto-apply idle cash to an investment and the provider reports
    that money both inside the account balance and as a holding (issue #843).
    The invested portion leaves the account balance only for the accounts in
    ``deducted`` (see ``_auto_invested_deductions``); every other account
    keeps the full reported balance so the money is never dropped from net
    worth.
    """
    if acc_data.external_id not in deducted or acc_data.auto_invested_balance <= 0:
        return acc_data.balance
    return acc_data.balance - acc_data.auto_invested_balance


async def _fetch_holdings(
    connection: BankConnection, credentials: dict
) -> Optional[list[HoldingData]]:
    """Fetch the connection's holdings, or None when the provider call fails.

    Provider-side failures are tolerated (not every connector exposes
    investment data, and a brokerage hiccup must not break the bank-account
    sync), so they are logged and reported as None.
    """
    try:
        provider = get_provider(connection.provider)
        return await provider.get_holdings(credentials)
    except Exception:  # noqa: BLE001
        logger.exception(
            "Failed to fetch holdings for connection %s", connection.id
        )
        return None


@dataclass(frozen=True)
class _CountedHolding:
    """A holding that stays counted in net worth after this sync."""

    account_external_id: Optional[str]  # None: not attributable to an account
    value: Decimal
    currency: str


def _provider_sell_date_reverts(asset: Asset) -> bool:
    """Whether a live payload clears this asset's sell date.

    Only a sell date the sync itself set for a provider withdrawal
    (TOTAL_WITHDRAWAL) is reversible; a date the user entered stays.
    """
    if asset.sell_date is None:
        return False
    metadata = asset.external_metadata or {}
    return (
        str(metadata.get("status") or "").upper() == "TOTAL_WITHDRAWAL"
        and metadata.get(_PROVIDER_SELL_DATE_METADATA_KEY)
        == asset.sell_date.isoformat()
    )


def _stays_counted(existing: Optional[Asset], connection_id: uuid.UUID) -> bool:
    """Whether a live holding leaves its asset counted after ``_sync_holdings``.

    Mirrors what the assets sync keeps: a user-set sell date survives, and
    an archived asset is only unarchived when the holding moved to another
    connection.
    """
    if existing is None:
        return True
    if existing.sell_date is not None and not _provider_sell_date_reverts(existing):
        return False
    return not (existing.is_archived and existing.connection_id == connection_id)


async def _counted_holdings(
    session: AsyncSession,
    connection: BankConnection,
    holdings: Optional[list[HoldingData]],
    accounts_data: list[AccountData],
) -> list[_CountedHolding]:
    """Holdings this sync leaves counted in net worth, with their owners.

    A fetched payload decides on its own: withdrawn positions are not
    counted, and neither are positions whose stored asset the user sold or
    archived, since the assets sync keeps that state. Without a payload
    (asset sync disabled, or the fetch failed) the assets sync does not run
    and whatever it stored last stays in place, so the answer comes from
    the database: every live (not archived, not sold) asset this
    connection's holdings sync owns, at its latest stored value. Its owner
    is read back from the per-account wallet the sync put it in; any other
    wallet means unattributed.
    """
    if holdings is not None:
        live = [h for h in holdings if not h.is_withdrawn]
        if not live:
            return []
        existing_by_external = {
            asset.external_id: asset
            for asset in (
                await session.scalars(
                    select(Asset).where(
                        Asset.workspace_id == connection.workspace_id,
                        Asset.source == connection.provider,
                        Asset.external_id.in_([h.external_id for h in live]),
                    )
                )
            ).all()
        }
        return [
            _CountedHolding(h.account_external_id, h.current_value, h.currency)
            for h in live
            if _stays_counted(existing_by_external.get(h.external_id), connection.id)
        ]

    latest_value = (
        select(AssetValue.amount)
        .where(AssetValue.asset_id == Asset.id)
        .order_by(AssetValue.date.desc())
        .limit(1)
        .scalar_subquery()
    )
    rows = (
        await session.execute(
            select(AssetGroup.external_id, latest_value, Asset.currency)
            .select_from(Asset)
            .outerjoin(AssetGroup, Asset.group_id == AssetGroup.id)
            .where(
                Asset.workspace_id == connection.workspace_id,
                Asset.source == connection.provider,
                Asset.connection_id == connection.id,
                Asset.external_id.is_not(None),
                Asset.is_archived.is_(False),
                Asset.sell_date.is_(None),
            )
        )
    ).all()
    account_by_wallet = {
        _wallet_external_id(connection.external_id, acc.external_id): acc.external_id
        for acc in accounts_data
        if acc.external_id
    }
    return [
        _CountedHolding(
            account_by_wallet.get(key) if key else None,
            Decimal(value) if value is not None else Decimal(0),
            currency,
        )
        for key, value, currency in rows
    ]


# How far an unattributed holding's value may sit from an account's
# auto-invested balance and still count as the same money: the larger of
# 1% and 1.00 in the account currency. The two figures come from different
# provider endpoints and can drift by a day's yield.
_AUTO_INVEST_MATCH_RELATIVE = Decimal("0.01")
_AUTO_INVEST_MATCH_ABSOLUTE = Decimal("1")


def _mirrors_auto_invested(holding: _CountedHolding, acc: AccountData) -> bool:
    """Whether an unattributed holding looks like this account's auto-invested money."""
    if holding.currency != acc.currency:
        return False
    tolerance = max(
        acc.auto_invested_balance * _AUTO_INVEST_MATCH_RELATIVE,
        _AUTO_INVEST_MATCH_ABSOLUTE,
    )
    return abs(holding.value - acc.auto_invested_balance) <= tolerance


def _auto_invested_deductions(
    accounts_data: list[AccountData], counted: list[_CountedHolding]
) -> set[str]:
    """External ids of the accounts whose auto-invested portion is deducted.

    The deduction is per account: an account loses its auto-invested portion
    only when a counted holding is attributed to that same account, never
    because some other account of the connection has one.

    Holdings without an owning account (Pluggy's investments payload carries
    the item but no account id, so this is the normal case there) cannot say
    which account they came from, or whether they are the auto-invested
    money at all (the same item can hold unrelated investments). Deducting
    on a guess would understate net worth, so an unattributed holding only
    allows the deduction when both hold:

    - exactly one account of the connection reports an auto-invested
      balance, so it is the only account the holding can mirror;
    - the holding's value matches that balance, in the same currency,
      within the tolerance above (in the case of issue #843 the two figures
      were identical).

    Otherwise the account keeps the full reported balance, which can count
    the money twice but never drops it.
    """
    candidates = [acc for acc in accounts_data if acc.auto_invested_balance > 0]
    owners = {h.account_external_id for h in counted}
    deducted = {acc.external_id for acc in candidates if acc.external_id in owners}
    if len(candidates) == 1:
        only = candidates[0]
        if any(
            h.account_external_id is None and _mirrors_auto_invested(h, only)
            for h in counted
        ):
            deducted.add(only.external_id)
    return deducted


async def _resolve_auto_invested_deductions(
    session: AsyncSession,
    connection: BankConnection,
    holdings: Optional[list[HoldingData]],
    accounts_data: list[AccountData],
) -> set[str]:
    """Accounts whose auto-invested portion leaves the stored balance.

    Skips the database lookup when no account reports an auto-invested
    portion, which is every connection but the affected ones.
    """
    if not any(acc.auto_invested_balance > 0 for acc in accounts_data):
        return set()
    counted = await _counted_holdings(session, connection, holdings, accounts_data)
    return _auto_invested_deductions(accounts_data, counted)


async def _sync_holdings(
    session: AsyncSession,
    user_id: uuid.UUID,
    connection: BankConnection,
    credentials: dict,
    holdings: Optional[list[HoldingData]] = None,
) -> None:
    """Fetch investment holdings from the provider and upsert them as Assets.

    ``holdings`` lets a caller pass a payload it already fetched with
    ``_fetch_holdings``; when omitted the provider is called here.

    Each holding becomes one Asset (type="investment") keyed by
    (workspace_id, source, external_id). Every sync appends an AssetValue row
    dated today; if a row for today already exists (same day re-sync) it
    is updated in place rather than creating a duplicate.

    Holdings that disappear from the provider response (e.g. fully
    redeemed fixed income) get archived rather than deleted so the user
    keeps their value history.

    Failures here are swallowed: not all Pluggy connectors expose
    investment data, and we don't want a brokerage hiccup to break the
    bank-account sync that just succeeded.
    """
    if holdings is None:
        holdings = await _fetch_holdings(connection, credentials)
        if holdings is None:
            return

    source = connection.provider
    today = app_today()

    # Find-or-create the wallet(s) that own this connection's holdings. A
    # holding carrying its owning account (SimpleFIN — issue #345) gets one
    # wallet per investment account, named after it; the rest share the
    # connection-named wallet. Users can rename wallets freely without
    # breaking future syncs (matching is by external_id).
    groups_by_key: dict[Optional[str], AssetGroup] = {}
    holding_ids = {h.external_id for h in holdings if h.external_id}
    # Snapshots before any bucket runs, so payload order can't change them.
    # Once per-account wallets exist under the current connection key, the
    # plain-keyed wallet is the live connection-default, not a legacy row
    # awaiting its first split — adopting it would hijack it. A keyed
    # bucket applies the stricter test: any split wallet in scope (old
    # prefixes included) means past the legacy era, so a genuinely new
    # account must mint even right after a reconnect rotated the prefix —
    # only the keyless bucket may still adopt the stale default then.
    has_split_wallets = bool(
        await session.scalar(
            select(
                exists().where(
                    AssetGroup.user_id == user_id,
                    AssetGroup.workspace_id == connection.workspace_id,
                    AssetGroup.source == source,
                    AssetGroup.external_id.startswith(
                        f"{connection.external_id}::", autoescape=True
                    ),
                )
            )
        )
    )
    has_any_split_wallets = has_split_wallets or bool(
        await session.scalar(
            select(
                exists().where(
                    AssetGroup.user_id == user_id,
                    AssetGroup.workspace_id == connection.workspace_id,
                    AssetGroup.source == source,
                    or_(
                        AssetGroup.connection_id == connection.id,
                        AssetGroup.connection_id.is_(None),
                    ),
                    AssetGroup.external_id.contains("::"),
                )
            )
        )
    )

    async def _holds_synced_asset(group_id: uuid.UUID) -> bool:
        """Does this wallet already hold an asset this payload re-syncs?"""
        if not holding_ids:
            return False
        return bool(
            await session.scalar(
                select(
                    exists().where(
                        Asset.group_id == group_id,
                        Asset.external_id.in_(holding_ids),
                    )
                )
            )
        )

    async def _wallet_for(holding: HoldingData) -> AssetGroup:
        key = holding.account_external_id
        if key not in groups_by_key:
            # The owning account's institution backs the wallet's
            # "Synced from …" subtitle (issue #345).
            institution_id = (
                await session.scalar(
                    select(Account.institution_id).where(
                        Account.connection_id == connection.id,
                        Account.external_id == key,
                    )
                )
                if key
                else None
            )
            wallet_key = _wallet_external_id(connection.external_id, key)
            default_name = (
                _clean_institution_name(holding.account_name)
                or connection.institution_name
            )
            # The first claim on a wallet key adopts the wallet these
            # holdings lived in before — the legacy connection-keyed one, or
            # one left on a stale key by a reconnect (plain or per-account:
            # a "…::{account}" suffix match is this very account's wallet
            # under an older connection key) — instead of minting a new
            # row, so an existing user's rename/icon/color/position
            # survive. Only an untouched auto-name is refreshed. Guards, in
            # order: never adopt when a wallet already owns the key
            # (re-keying a twin next to it would trip the unique
            # (user, source, external_id) index and fail every sync); never
            # adopt a wallet another bucket of this run claimed; never
            # reach into another workspace; a plain candidate only counts
            # before the first split — afterwards it is the live
            # connection-default wallet, not a legacy awaiting adoption; a
            # stale-keyed candidate must be provably this bank's — still
            # linked to this connection, or an orphan holding assets this
            # payload re-syncs (a deleted sibling connection's orphan is
            # neither); ambiguity means minting, not guessing.
            existing = await session.scalar(
                select(AssetGroup.id).where(
                    AssetGroup.user_id == user_id,
                    AssetGroup.source == source,
                    AssetGroup.external_id == wallet_key,
                )
            )
            legacy: Optional[AssetGroup] = None
            if existing is None:
                claimed = {g.id for g in groups_by_key.values()}
                key_shape = ~AssetGroup.external_id.contains("::")
                if key:
                    key_shape = or_(
                        key_shape,
                        AssetGroup.external_id.endswith(
                            f"::{key}", autoescape=True
                        ),
                    )
                candidates = [
                    g
                    for g in (
                        await session.execute(
                            select(AssetGroup).where(
                                AssetGroup.user_id == user_id,
                                AssetGroup.workspace_id == connection.workspace_id,
                                AssetGroup.source == source,
                                or_(
                                    AssetGroup.connection_id == connection.id,
                                    AssetGroup.connection_id.is_(None),
                                ),
                                AssetGroup.external_id.isnot(None),
                                # Wallets keyed under the live prefix belong
                                # to their own accounts, never to this one.
                                ~AssetGroup.external_id.startswith(
                                    f"{connection.external_id}::",
                                    autoescape=True,
                                ),
                                key_shape,
                            )
                        )
                    ).scalars().all()
                    if g.id not in claimed
                ]
                # A stale key parses as exactly "{old prefix}::{key}"; a
                # leftover "::" in the remainder means the tail straddles
                # another account's key (ids may themselves contain "::").
                tail = f"::{key}" if key else None
                suffixed = [
                    g
                    for g in candidates
                    if tail
                    and g.external_id
                    and g.external_id.endswith(tail)
                    and g.external_id[: -len(tail)]
                    and "::" not in g.external_id[: -len(tail)]
                ]
                plain = [
                    g
                    for g in candidates
                    if g.external_id and "::" not in g.external_id
                ]
                pool = suffixed or (
                    []
                    if (has_any_split_wallets if key else has_split_wallets)
                    else plain
                )
                exact = [
                    g for g in pool if g.external_id == connection.external_id
                ]
                if exact:
                    legacy = exact[0]
                else:
                    ours = [g for g in pool if g.connection_id == connection.id]
                    if not ours:
                        ours = [
                            g for g in pool if await _holds_synced_asset(g.id)
                        ]
                    if len(ours) == 1:
                        legacy = ours[0]
            if legacy is not None:
                # A concurrent sync can win the key between the guard above
                # and this flush; fall through to the mint path, which
                # re-selects the winner's row.
                try:
                    async with session.begin_nested():
                        legacy.external_id = wallet_key
                        legacy.connection_id = connection.id
                        if _is_auto_wallet_name(
                            legacy.name, connection.institution_name
                        ):
                            legacy.name = await _unique_default_name(
                                session,
                                user_id,
                                default_name[:95],
                                exclude_group_id=legacy.id,
                            )
                        if institution_id is not None:
                            legacy.institution_id = institution_id
                        await session.flush()
                except IntegrityError:
                    await session.refresh(legacy)
                else:
                    groups_by_key[key] = legacy
                    return legacy
            groups_by_key[key] = await ensure_group_for_connection(
                session,
                user_id=user_id,
                connection_id=connection.id,
                source=source,
                external_id=wallet_key,
                default_name=default_name,
                institution_id=institution_id,
                workspace_id=connection.workspace_id,
            )
        return groups_by_key[key]

    # Wallets this sync owns: this connection's, plus orphans a prior
    # disconnect left behind (connection_id went NULL via SET NULL) — their
    # re-adopted assets must still split into per-account wallets instead of
    # staying stranded. An asset sitting in one may be re-attributed below;
    # a user's custom wallet (source "manual") and other workspaces' wallets
    # are never touched.
    sync_owned_rows = await session.execute(
        select(AssetGroup.id, AssetGroup.external_id).where(
            AssetGroup.user_id == user_id,
            AssetGroup.workspace_id == connection.workspace_id,
            AssetGroup.source == source,
            or_(
                AssetGroup.connection_id == connection.id,
                AssetGroup.connection_id.is_(None),
            ),
        )
    )
    sync_owned = sync_owned_rows.all()
    sync_owned_group_ids = {row[0] for row in sync_owned}
    # Per-account wallets, by their "…::…" keys. A holding that lost its
    # account hint must not drain one of these into the default bucket — a
    # single degraded payload would empty them and the reap would delete
    # the user's customization with them.
    split_group_ids = {row[0] for row in sync_owned if row[1] and "::" in row[1]}

    existing_rows = await session.execute(
        select(Asset).where(
            Asset.workspace_id == connection.workspace_id,
            Asset.source == source,
        )
    )
    existing_assets = list(existing_rows.scalars().all())
    existing_by_external: dict[str, Asset] = {
        asset.external_id: asset for asset in existing_assets if asset.external_id
    }
    archive_candidates: dict[str, Asset] = {
        asset.external_id: asset
        for asset in existing_assets
        if asset.external_id
        and (asset.connection_id == connection.id or asset.connection_id is None)
    }
    seen: set[str] = set()

    for holding in holdings:
        seen.add(holding.external_id)
        existing = existing_by_external.get(holding.external_id)

        # Provider-reported closure (Pluggy TOTAL_WITHDRAWAL). Two cases:
        #   - New + withdrawn: skip entirely. A dead zero-balance asset
        #     with no history is noise; the user never saw this position
        #     while it was active, no reason to surface it closed.
        #   - Existing + withdrawn: mark sell_date (if not already set by
        #     the user) so it drops out of current totals but historical
        #     AssetValues remain visible in reports. No new AssetValue —
        #     appending today's zero would bury the real closing value.
        if holding.is_withdrawn:
            if existing is None:
                continue
            provider_sell_date: Optional[str] = None
            if existing.sell_date is None:
                existing.sell_date = today
                provider_sell_date = today.isoformat()
            else:
                # Preserve provenance across repeated withdrawn payloads, but
                # deliberately drop it once the user changes the date.
                previous_marker = (existing.external_metadata or {}).get(
                    _PROVIDER_SELL_DATE_METADATA_KEY
                )
                if previous_marker == existing.sell_date.isoformat():
                    provider_sell_date = previous_marker
            # Keep descriptive fields fresh in case the provider still
            # updates them post-closure, but don't touch valuation.
            existing.name = holding.name
            withdrawn_metadata = dict(holding.metadata or {})
            if provider_sell_date is not None:
                withdrawn_metadata[_PROVIDER_SELL_DATE_METADATA_KEY] = provider_sell_date
            existing.external_metadata = withdrawn_metadata or None
            existing.connection_id = connection.id
            continue

        # A provider-reported closure is reversible. The prior raw status is
        # already persisted in external_metadata, so no separate schema field
        # is needed to distinguish TOTAL_WITHDRAWAL -> ACTIVE from a manually
        # entered sell date. Manual dates remain authoritative when there was
        # no provider withdrawal transition.
        if existing is not None and _provider_sell_date_reverts(existing):
            existing.sell_date = None

        asset = await _upsert_asset_from_holding(
            session, existing, holding, user_id, connection.id, source,
            workspace_id=connection.workspace_id,
        )
        if asset.group_id is not None:
            existing_group = await session.get(AssetGroup, asset.group_id)
            if (
                existing_group is not None
                and existing_group.workspace_id == connection.workspace_id
                and existing_group.source == source
                and (
                    existing_group.connection_id == connection.id
                    or existing_group.connection_id is None
                )
            ):
                existing_group.user_id = user_id
                sync_owned_group_ids.add(existing_group.id)
                if existing_group.external_id and "::" in existing_group.external_id:
                    split_group_ids.add(existing_group.id)
        # Attach to its institution's wallet. NOTE this deliberately moves
        # holdings between sync-owned wallets, not just out of a null group
        # like it used to: re-attribution corrects the sync's own earlier
        # bucketing (one-wallet-per-connection → per-account). A wallet the
        # user made themselves ("US Stocks", source "manual") is never
        # touched.
        group = await _wallet_for(holding)
        hint_lost = (
            holding.account_external_id is None
            and asset.group_id in split_group_ids
        )
        if (
            not hint_lost
            and (asset.group_id is None or asset.group_id in sync_owned_group_ids)
            and asset.group_id != group.id
        ):
            asset.group_id = group.id
        # Seed a historical value at purchase_date so users get a real
        # evolution curve from day one — not just today's snapshot.
        # Idempotent: skips if any AssetValue already exists at that date.
        if holding.purchase_date and holding.purchase_price is not None:
            await _ensure_historical_seed(
                session, asset, holding.purchase_date, holding.purchase_price
            )
        # Respect a user-set sell_date: if they've marked the asset as
        # sold we stop recording new values even when the provider still
        # reports the position. Historical values stay; current totals
        # already exclude it via the sell_date filter in rollups.
        if asset.sell_date is None:
            await _upsert_asset_value_for_today(session, asset, holding.current_value, today)

    for ext_id, asset in archive_candidates.items():
        if ext_id not in seen and not asset.is_archived:
            asset.is_archived = True

    # Sync owns its wallets: drop any it emptied by re-attribution above
    # (e.g. the single connection-named wallet that predates per-institution
    # ones). Wallets still holding assets — or used this run — are kept, and
    # so is anything a goal tracks or a collection contains: deleting those
    # would SET NULL the goal's target and CASCADE the membership away,
    # silently breaking things the user built on the wallet.
    if holdings:
        await session.flush()
        used_ids = {g.id for g in groups_by_key.values()}
        for gid in sync_owned_group_ids - used_ids:
            has_assets = await session.scalar(
                select(func.count()).select_from(Asset).where(Asset.group_id == gid)
            )
            if has_assets:
                continue
            referenced = await session.scalar(
                select(
                    exists().where(Goal.asset_group_id == gid)
                    | exists()
                    .select_from(collection_asset_groups)
                    .where(collection_asset_groups.c.asset_group_id == gid)
                )
            )
            if referenced:
                continue
            emptied = await session.get(AssetGroup, gid)
            if emptied is not None:
                await session.delete(emptied)


async def _upsert_asset_from_holding(
    session: AsyncSession,
    asset: Optional[Asset],
    holding: HoldingData,
    user_id: uuid.UUID,
    connection_id: uuid.UUID,
    source: str,
    workspace_id: uuid.UUID,
) -> Asset:
    """Create or update an Asset from a HoldingData payload.

    Synced fields (name, currency, quantity, purchase_price, maturity,
    metadata) are always overwritten — the UI disables editing these on
    synced assets. Provider-reported withdrawal is handled by the caller
    via `sell_date`, not here, so this function only ever sees ACTIVE
    holdings and never flips `is_archived` on its own.
    """
    if asset is None:
        asset = Asset(
            user_id=user_id,
            workspace_id=workspace_id,
            connection_id=connection_id,
            source=source,
            external_id=holding.external_id,
            name=holding.name,
            type="investment",
            currency=holding.currency,
            units=holding.quantity,
            purchase_price=holding.purchase_price,
            purchase_date=holding.purchase_date,
            isin=holding.isin,
            ticker=holding.ticker,
            maturity_date=holding.maturity_date,
            external_metadata=holding.metadata,
            valuation_method="manual",
        )
        session.add(asset)
        await session.flush()
        return asset

    # Fields Pluggy consistently returns — safe to overwrite each sync.
    asset.name = holding.name
    asset.currency = holding.currency
    asset.user_id = user_id
    # external_metadata is a snapshot blob: we want the latest every time.
    asset.external_metadata = holding.metadata
    previous_connection_id = asset.connection_id
    asset.connection_id = connection_id
    # Only auto-unarchive when the holding moved to a different connection
    # (e.g. unlink + reconnect). This avoids overriding user-archived assets.
    if asset.is_archived and previous_connection_id != connection_id:
        asset.is_archived = False
    # Re-adopted across workspaces (bank deleted in one, re-added in the
    # other): the asset follows its connection; its old wallet stays behind
    # in the old workspace, so placement is redone by the caller.
    if asset.workspace_id != workspace_id:
        asset.workspace_id = workspace_id
        asset.group_id = None

    # Sparse fields — merge, don't clobber. Pluggy sometimes returns
    # these on first sync and null on later ones (e.g. amountOriginal
    # present at creation, missing on daily rebalances). Keeping the
    # first-seen value is better than wiping data we already have.
    if holding.quantity is not None:
        asset.units = holding.quantity
    if holding.purchase_price is not None:
        asset.purchase_price = holding.purchase_price
    if holding.purchase_date:
        asset.purchase_date = holding.purchase_date
    if holding.isin:
        asset.isin = holding.isin
    if holding.ticker:
        asset.ticker = holding.ticker
    if holding.maturity_date:
        asset.maturity_date = holding.maturity_date
    return asset


async def _ensure_historical_seed(
    session: AsyncSession,
    asset: Asset,
    purchase_date: date,
    purchase_price,
) -> None:
    """Insert a one-time AssetValue at purchase_date with purchase_price.

    Called on every sync but a no-op once the seed exists. Skips if ANY
    AssetValue already exists on that date (even a manual one) — we don't
    want to stomp a value the user may have entered themselves.
    """
    existing = await session.execute(
        select(AssetValue).where(
            AssetValue.asset_id == asset.id,
            AssetValue.date == purchase_date,
        )
    )
    if existing.scalar_one_or_none() is not None:
        return
    session.add(
        AssetValue(
            asset_id=asset.id,
            amount=purchase_price,
            date=purchase_date,
            source="sync",
        )
    )


async def _upsert_asset_value_for_today(
    session: AsyncSession,
    asset: Asset,
    amount,
    today: date,
) -> None:
    """One sync-sourced AssetValue per asset per day.

    Re-syncing the same day updates the amount in place; a later day
    creates a new row so we build a daily valuation history over time.
    """
    existing = await session.execute(
        select(AssetValue).where(
            AssetValue.asset_id == asset.id,
            AssetValue.date == today,
            AssetValue.source == "sync",
        )
    )
    row = existing.scalar_one_or_none()
    if row is not None:
        row.amount = amount
    else:
        session.add(
            AssetValue(
                asset_id=asset.id,
                amount=amount,
                date=today,
                source="sync",
            )
        )


async def _match_pluggy_category(
    session: AsyncSession,
    workspace_id: uuid.UUID,
    pluggy_category: Optional[str],
    enabled: bool = True,
) -> Optional[uuid.UUID]:
    # `enabled` is the resolved value of the global `use_provider_categories`
    # admin setting. Off = sync skips the provider->user category mapping
    # entirely so transactions arrive uncategorized and Rules are the only
    # source of truth. Default keeps the historical behavior.
    if not enabled or not pluggy_category:
        return None
    # Try exact match first, then prefix before " - " (e.g. "Transfer - PIX" → "Transfer")
    app_name = PLUGGY_CATEGORY_MAP.get(pluggy_category)
    if not app_name and " - " in pluggy_category:
        app_name = PLUGGY_CATEGORY_MAP.get(pluggy_category.split(" - ")[0])
    if not app_name:
        return None
    # Scope to the connection's workspace: a user in multiple workspaces owns
    # the same default category names in each, so a user_id-only lookup returns
    # several rows. `.first()` is belt-and-suspenders — a category match must
    # never crash the whole sync even if a workspace somehow has name dupes.
    result = await session.execute(
        select(Category.id)
        .where(Category.workspace_id == workspace_id, Category.name == app_name)
        .limit(1)
    )
    return result.scalars().first()


def _bank_converted_amount(
    txn_data: TransactionData, account_currency: str, user_currency: str
) -> Optional[Decimal]:
    """The bank's own conversion of a foreign charge into the primary currency.

    Only when the account is held in the primary currency, so the figure the
    bank charged the account is the primary amount. None otherwise.
    """
    if (
        txn_data.amount_in_account_currency is not None
        and txn_data.amount
        and account_currency == user_currency
        and txn_data.currency != account_currency
    ):
        return txn_data.amount_in_account_currency
    return None


async def _stamp_synced_primary_amount(
    session: AsyncSession,
    user_id: uuid.UUID,
    transaction: Transaction,
    converted: Optional[Decimal],
) -> None:
    """Prefer the bank's conversion (see _bank_converted_amount); else use our FX rates.

    The transaction's amount must already be the charged amount.
    """
    if converted is not None:
        transaction.amount_primary = converted
        transaction.fx_rate_used = converted / transaction.amount
    else:
        await stamp_primary_amount(session, user_id, transaction)


async def _find_installment_category(
    session: AsyncSession,
    account_id: uuid.UUID,
    txn_data,
) -> Optional[uuid.UUID]:
    """Reuse the latest user-selected category from an earlier installment.

    Pluggy gives every parcel the original purchase date, installment count,
    and total purchase amount. Together with the card account and transaction
    type, those fields identify the purchase series without relying on the
    description (whose ``03/12`` suffix changes every month).

    Only earlier synced parcels qualify. This keeps manual installment series
    separate and lets a user's most recent category correction become the
    source of truth for the next imported parcel.
    """
    if (
        txn_data.installment_number is None
        or txn_data.total_installments is None
        or txn_data.installment_total_amount is None
        or txn_data.installment_purchase_date is None
    ):
        return None

    result = await session.execute(
        select(Transaction.category_id)
        .where(
            Transaction.account_id == account_id,
            Transaction.source == "sync",
            Transaction.category_id.is_not(None),
            Transaction.installment_purchase_date == txn_data.installment_purchase_date,
            Transaction.total_installments == txn_data.total_installments,
            Transaction.installment_total_amount == txn_data.installment_total_amount,
            Transaction.type == txn_data.type,
            Transaction.installment_number < txn_data.installment_number,
        )
        .order_by(Transaction.installment_number.desc(), Transaction.date.desc())
        .limit(1)
    )
    return result.scalars().first()


async def get_connections(session: AsyncSession, workspace_id: uuid.UUID) -> list[BankConnection]:
    result = await session.execute(
        select(BankConnection)
        .where(BankConnection.workspace_id == workspace_id)
        .options(selectinload(BankConnection.accounts))
        .order_by(BankConnection.created_at.desc())
    )
    return list(result.scalars().all())


async def get_connection(
    session: AsyncSession,
    connection_id: uuid.UUID,
    workspace_id: uuid.UUID,
    *,
    for_update: bool = False,
) -> Optional[BankConnection]:
    statement = (
        select(BankConnection)
        .where(BankConnection.id == connection_id, BankConnection.workspace_id == workspace_id)
        .options(selectinload(BankConnection.accounts))
    )
    if for_update:
        statement = statement.with_for_update()
    result = await session.execute(statement)
    return result.scalar_one_or_none()


async def get_oauth_url(
    provider_name: str,
    user_id: uuid.UUID,
    workspace_id: uuid.UUID,
    flow_params: Optional[dict] = None,
    reconnect_connection_id: Optional[uuid.UUID] = None,
) -> str:
    provider = get_provider(provider_name)
    state = await oauth_state.store_state(
        {
            "user_id": str(user_id),
            "workspace_id": str(workspace_id),
            "provider": provider_name,
            "flow_params": flow_params or {},
            "reconnect_connection_id": (
                str(reconnect_connection_id) if reconnect_connection_id else None
            ),
        }
    )
    return await provider.get_oauth_url(provider.redirect_uri, state, flow_params)


async def get_reauth_url(
    session: AsyncSession,
    connection_id: uuid.UUID,
    workspace_id: uuid.UUID,
    user_id: uuid.UUID,
) -> str:
    connection = await get_connection(session, connection_id, workspace_id)
    if not connection:
        raise ValueError("Connection not found")
    provider = get_provider(connection.provider)
    state = await oauth_state.store_state(
        {
            "user_id": str(user_id),
            "workspace_id": str(workspace_id),
            "provider": connection.provider,
            "flow_params": (connection.settings or {}).get("flow_params") or {},
            "reconnect_connection_id": str(connection.id),
        }
    )
    return await provider.reauth_url(
        connection.credentials or {},
        connection.settings or {},
        provider.redirect_uri,
        state,
    )


async def list_provider_institutions(
    provider_name: str, country: Optional[str] = None
) -> dict:
    provider = get_provider(provider_name)
    data = await provider.list_institutions(country)
    return {
        "countries": data.countries,
        "institutions": [
            {
                "name": i.name,
                "display_name": i.display_name,
                "country": i.country,
                "logo": i.logo,
                "bic": i.bic,
                "psu_types": i.psu_types,
                "max_consent_days": i.max_consent_days,
                "max_history_days": i.max_history_days,
            }
            for i in data.institutions
        ],
    }


async def create_connect_token(
    provider_name: str, user_id: uuid.UUID, item_id: str | None = None
) -> dict:
    provider = get_provider(provider_name)
    token_data = await provider.create_connect_token(str(user_id), item_id=item_id)
    return {"access_token": token_data.access_token}


async def update_connection_settings(
    session: AsyncSession,
    connection_id: uuid.UUID,
    workspace_id: uuid.UUID,
    settings_update: dict,
) -> Optional[BankConnection]:
    connection = await get_connection(session, connection_id, workspace_id)
    if not connection:
        return None

    if "display_name" in settings_update:
        raw = settings_update.pop("display_name")
        trimmed = raw.strip() if isinstance(raw, str) else raw
        connection.display_name = trimmed or None

    current = dict(connection.settings or {})
    for key, value in settings_update.items():
        if value is not None:
            current[key] = value
    connection.settings = current

    await session.commit()
    await session.refresh(connection)
    return connection


async def handle_oauth_callback(
    session: AsyncSession,
    workspace_id: uuid.UUID,
    user_id: uuid.UUID,
    code: str,
    provider_name: Optional[str] = None,
    state: Optional[str] = None,
    sync_assets: Optional[bool] = None,
    reconnect_connection_id: Optional[uuid.UUID] = None,
) -> BankConnection:
    state_payload: dict = {}
    if state:
        consumed = await oauth_state.consume_state(state)
        if not consumed:
            raise ValueError("OAuth state is invalid or expired")
        # The state is authoritative — caller-supplied provider_name is a hint.
        if consumed.get("user_id") != str(user_id):
            raise ValueError("OAuth state user does not match authenticated user")
        if consumed.get("workspace_id") != str(workspace_id):
            raise ValueError("OAuth state workspace does not match active workspace")
        state_payload = consumed
        provider_name = consumed.get("provider") or provider_name
    reconnect_id = state_payload.get("reconnect_connection_id") or reconnect_connection_id
    existing_reconnect: BankConnection | None = None
    if reconnect_id:
        existing_reconnect = await session.get(BankConnection, uuid.UUID(str(reconnect_id)))
        if not existing_reconnect or existing_reconnect.workspace_id != workspace_id:
            raise ValueError("Reconnect target connection not found")
        # Token reconnects do not carry OAuth state, so the request body may be
        # the only source of provider_name. Never allow a pasted token for one
        # provider to overwrite another provider's stored credentials.
        if provider_name and provider_name != existing_reconnect.provider:
            raise ValueError("Reconnect provider does not match target connection")
        provider_name = existing_reconnect.provider

    if not provider_name:
        raise ValueError("OAuth callback missing provider")

    provider = get_provider(provider_name)
    connection_data = await provider.handle_oauth_callback(code)

    if existing_reconnect:
        existing_reconnect.external_id = connection_data.external_id
        existing_reconnect.institution_name = (
            connection_data.institution_name or existing_reconnect.institution_name
        )
        existing_reconnect.logo_url = _clean_logo_url(connection_data.logo_url) or existing_reconnect.logo_url
        existing_reconnect.credentials = connection_data.credentials
        existing_reconnect.status = "active"
        # Re-sync from current data on next sync cycle.
        existing_reconnect.last_sync_at = None
        await session.commit()
        await session.refresh(existing_reconnect)
        return existing_reconnect

    flow_params = dict(state_payload.get("flow_params") or {})
    flow_sync_assets = flow_params.pop("sync_assets", None)
    initial_settings: dict[str, object] = {"flow_params": flow_params}
    if sync_assets is None and isinstance(flow_sync_assets, bool):
        sync_assets = flow_sync_assets
    if sync_assets is not None:
        initial_settings["sync_assets"] = sync_assets

    connection = BankConnection(
        workspace_id=workspace_id,
        user_id=user_id,
        provider=provider_name,
        external_id=connection_data.external_id,
        institution_name=connection_data.institution_name,
        logo_url=_clean_logo_url(connection_data.logo_url),
        credentials=connection_data.credentials,
        settings=initial_settings,
        status="active",
    )
    session.add(connection)
    await session.flush()

    user = await session.get(User, user_id)
    user_currency = user.primary_currency if user else get_settings().default_currency
    new_tx_ids: list[uuid.UUID] = []

    use_provider_cats = await admin_service.use_provider_categories(session)

    # Holdings are fetched before the accounts so each account balance knows
    # whether its auto-invested portion is counted as an asset (issue #843).
    # Without a payload (asset sync off or a failed fetch) the deduction is
    # decided from the live synced assets already stored.
    holdings: Optional[list[HoldingData]] = None
    if _sync_assets_enabled(connection.settings):
        holdings = await _fetch_holdings(connection, connection_data.credentials)
    auto_invested_deducted = await _resolve_auto_invested_deductions(
        session, connection, holdings, connection_data.accounts
    )

    institution_cache: dict[str, Institution] = {}
    for acc_data in connection_data.accounts:
        is_cc = acc_data.type == "credit_card"
        institution = await _resolve_institution(
            session, connection.id, institution_cache, acc_data
        )
        account = Account(
            user_id=user_id,
            workspace_id=workspace_id,
            connection_id=connection.id,
            external_id=acc_data.external_id,
            name=acc_data.name,
            display_name=institution.name if institution else None,
            masked_number=acc_data.masked_number,
            type=acc_data.type,
            balance=_account_balance(acc_data, auto_invested_deducted),
            currency=acc_data.currency,
            credit_limit=acc_data.credit_limit if is_cc else None,
            statement_close_day=acc_data.statement_close_day if is_cc else None,
            payment_due_day=acc_data.payment_due_day if is_cc else None,
            minimum_payment=acc_data.minimum_payment if is_cc else None,
            card_brand=acc_data.card_brand if is_cc else None,
            card_level=acc_data.card_level if is_cc else None,
            shared_balance_group=(
                f"{connection.id}:{acc_data.shared_balance_group}"
                if acc_data.shared_balance_group else None
            ),
            institution_id=institution.id if institution else None,
        )
        session.add(account)
        await session.flush()

        bills_by_external_id = await _sync_credit_card_bills(
            session, user_id, account, provider, connection_data.credentials
        )

        # Fetch initial transactions (since=None fetches all available history)
        transactions_data = await provider.get_transactions(
            connection_data.credentials, acc_data.external_id, None
        )
        incoming_external_ids = {txn.external_id for txn in transactions_data}
        seen_external_ids: set[str] = set()
        for txn_data in transactions_data:
            # Providers should return each external transaction once, but an
            # upstream pagination bug can repeat a page. This account was just
            # created, so batch-local tracking avoids both duplicate inserts
            # and an unnecessary SELECT for every imported transaction.
            if txn_data.external_id in seen_external_ids:
                continue
            seen_external_ids.add(txn_data.external_id)

            # Pending↔posted twin (and the credit-card installment variant).
            # When the same logical operation comes back under a new external
            # id with a different status, fingerprint match prevents the
            # second copy from landing.
            synced_dup = await _find_synced_duplicate(
                session, account.id, txn_data, incoming_external_ids
            )
            if synced_dup:
                if synced_dup.original_description is None:
                    synced_dup.original_description = txn_data.description
                if synced_dup.status == "pending" and txn_data.status == "posted":
                    synced_dup.status = "posted"
                    _merge_sync_metadata(
                        synced_dup,
                        txn_data,
                        replace_external_id=synced_dup.source == "sync",
                    )
                    if (
                        txn_data.bill_external_id
                        and synced_dup.effective_bill_date is None
                    ):
                        bill = bills_by_external_id.get(txn_data.bill_external_id)
                        if bill is not None and synced_dup.bill_id != bill.id:
                            synced_dup.bill_id = bill.id
                            apply_effective_date(
                                synced_dup, account, bill_due_date=bill.due_date
                            )
                elif synced_dup.external_id != txn_data.external_id:
                    _merge_sync_metadata(
                        synced_dup,
                        txn_data,
                        replace_external_id=synced_dup.source == "sync",
                    )
                continue

            category_id = await _find_installment_category(
                session, account.id, txn_data
            )
            if category_id is None:
                category_id = await _match_pluggy_category(
                    session,
                    workspace_id,
                    txn_data.pluggy_category,
                    enabled=use_provider_cats,
                )
            # Resolve payee entity from raw payee text
            payee_id = None
            if txn_data.payee:
                payee_entity = await get_or_create_payee(
                    session, user_id, txn_data.payee, workspace_id=workspace_id
                )
                payee_id = payee_entity.id

            bill = (
                bills_by_external_id.get(txn_data.bill_external_id)
                if txn_data.bill_external_id
                else None
            )
            transaction = Transaction(
                user_id=user_id,
                workspace_id=workspace_id,
                account_id=account.id,
                external_id=txn_data.external_id,
                description=txn_data.description,
                original_description=txn_data.description,
                amount=txn_data.amount,
                currency=txn_data.currency or acc_data.currency or user_currency,
                date=txn_data.date,
                type=txn_data.type,
                source="sync",
                status=txn_data.status,
                payee=txn_data.payee,
                payee_id=payee_id,
                raw_data=txn_data.raw_data,
                category_id=None,
                installment_number=txn_data.installment_number,
                total_installments=txn_data.total_installments,
                installment_total_amount=txn_data.installment_total_amount,
                installment_purchase_date=txn_data.installment_purchase_date,
                bill_id=bill.id if bill else None,
            )
            apply_effective_date(
                transaction, account, bill_due_date=bill.due_date if bill else None
            )
            session.add(transaction)
            await session.flush()
            new_tx_ids.append(transaction.id)
            await apply_rules_to_transaction(session, user_id, transaction)
            if transaction.category_id is None:
                transaction.category_id = category_id

            acct_currency = acc_data.currency or user_currency
            await _stamp_synced_primary_amount(
                session,
                user_id,
                transaction,
                _bank_converted_amount(txn_data, acct_currency, user_currency),
            )

        # After importing the initial batch, reconcile the opening balance so
        # that SUM(all transactions) matches the provider-reported balance. Any
        # history that falls outside the provider's lookback window gets
        # absorbed into this synthetic transaction.
        await sync_opening_balance_for_connected_account(session, account)

    # Detect transfer pairs among newly synced transactions
    await detect_transfer_pairs(session, workspace_id, candidate_ids=new_tx_ids)

    # And settle what this money was promised against, once the rows exist.
    if new_tx_ids:
        landed = list(
            (
                await session.execute(
                    select(Transaction).where(Transaction.id.in_(new_tx_ids))
                )
            )
            .scalars()
            .all()
        )
        await reconciliation_service.match_incoming(session, workspace_id, landed)

    # Investment holdings live on /investments — separate endpoint from
    # /accounts. Fetched before the account loop and stored here once the
    # accounts exist, so holdings are available on the Assets page
    # immediately after the widget closes.
    if holdings is not None:
        await _sync_holdings(
            session, user_id, connection, connection_data.credentials, holdings
        )

    connection.last_sync_at = datetime.now(timezone.utc)
    await session.commit()
    await session.refresh(connection)
    return connection


def _normalized_account_name(name: str | None) -> str:
    """Normalize provider account names for fallback matching.

    Some SimpleFIN bridges re-key account ids, but the display name / masked
    suffix stays stable (e.g. "High Yield Savings Account (9402)").
    """
    return " ".join((name or "").casefold().split())


async def _find_existing_connected_account(
    session: AsyncSession,
    connection: BankConnection,
    acc_data: AccountData,
    institution: Optional[Institution],
    incoming_external_ids: set[str],
    incoming_institution_external_ids: frozenset[str] = frozenset(),
) -> Optional[Account]:
    """Find an existing account for incoming provider account data.

    Primary identity is provider external_id. For SimpleFIN only, fall back to
    stable account name + currency because some bridges emit fresh UUID-like
    account ids on each pull; when that happens, blindly keying by id creates a
    new app account on every sync.
    """
    result = await session.execute(
        select(Account).where(
            Account.connection_id == connection.id,
            Account.external_id == acc_data.external_id,
        )
    )
    account = result.scalar_one_or_none()
    if account is not None:
        return account

    # The provider re-keyed this account. Enable Banking issues a `uid` scoped to
    # one session, so reauthorising changes it while `stable_external_id` (the
    # identification_hash) survives. Rebind the existing row, keeping its
    # transactions, rules and name overrides, instead of inserting a duplicate.
    # EB does not guarantee the hash is unique, so require exactly one unclaimed
    # candidate: a row another incoming account already took in this run carries
    # an external_id that is in the batch, and adopting it a second time would
    # put both transaction feeds on one account.id.
    if acc_data.stable_external_id:
        stable_rows = (await session.execute(
            select(Account).where(
                Account.connection_id == connection.id,
                Account.stable_external_id == acc_data.stable_external_id,
            )
        )).scalars().all()
        stable_rows = [
            row for row in stable_rows if row.external_id not in incoming_external_ids
        ]
        if len(stable_rows) > 1:
            return None
        if len(stable_rows) == 1:
            stable_rows[0].external_id = acc_data.external_id
            return stable_rows[0]

    # Last resort for rows that predate `stable_external_id` (added by migration
    # 097, and only ever backfilled by a *successful* sync, which a user whose
    # bank was failing never had). The masked identifier is the closest thing to
    # an account number we keep. Two guards, both fail-closed: a single candidate
    # (a shared last-4 must never merge two accounts), and candidates limited to
    # rows with no stable id: a row that is already identified is a different
    # account, so adopting it would overwrite its identity and mix histories.
    if connection.provider == "enable_banking" and acc_data.masked_number:
        masked_rows = (await session.execute(
            select(Account).where(
                Account.connection_id == connection.id,
                Account.masked_number == acc_data.masked_number,
                Account.currency == acc_data.currency,
                Account.stable_external_id.is_(None),
            )
        )).scalars().all()
        masked_rows = [
            row for row in masked_rows if row.external_id not in incoming_external_ids
        ]
        if len(masked_rows) == 1:
            masked_rows[0].external_id = acc_data.external_id
            return masked_rows[0]

    if connection.provider != "simplefin":
        return None

    normalized_name = _normalized_account_name(acc_data.name)
    if not normalized_name:
        return None

    candidates_result = await session.execute(
        select(Account).where(
            Account.connection_id == connection.id,
            Account.currency == acc_data.currency,
        )
    )
    candidates = [
        candidate
        for candidate in candidates_result.scalars().all()
        if candidate.external_id not in incoming_external_ids
        and _normalized_account_name(candidate.name) == normalized_name
    ]
    if institution is not None:
        matched_institution = [
            candidate for candidate in candidates
            if candidate.institution_id == institution.id
        ]
        if not matched_institution and candidates:
            # Re-linking a bank in SimpleFIN Bridge can send it under a new
            # conn_id, so _resolve_institution just minted a fresh row while
            # the account still points at the old, same-named one.
            #
            # Only a conn_id this pull no longer carries can have been
            # re-linked. A bank still listed under its own conn_id was not,
            # so a same-named one arriving beside it is a second login at
            # that bank (SimpleFIN has no account number to tell them apart,
            # and two logins at one bank share its name). Adopting its
            # account would point the user's existing row, display name and
            # history at the other login's feed.
            same_name_ids = set((await session.execute(
                select(Institution.id).where(
                    Institution.connection_id == connection.id,
                    Institution.name == institution.name,
                    Institution.external_id.not_in(incoming_institution_external_ids),
                )
            )).scalars().all())
            matched_institution = [
                candidate for candidate in candidates
                if candidate.institution_id in same_name_ids
            ]
        if matched_institution:
            candidates = matched_institution
        else:
            candidates = [
                candidate for candidate in candidates
                if candidate.institution_id is None
            ]
    else:
        candidates = [
            candidate for candidate in candidates
            if candidate.institution_id is None
        ]
    if len(candidates) != 1:
        return None

    account = candidates[0]
    account.external_id = acc_data.external_id
    return account


async def _fuzzy_match_manual(
    session: AsyncSession,
    account_id: uuid.UUID,
    txn_data,
) -> Optional[Transaction]:
    """Try to find a manual transaction that matches the incoming synced one."""
    date_lo = txn_data.date - timedelta(days=3)
    date_hi = txn_data.date + timedelta(days=3)

    result = await session.execute(
        select(Transaction).where(
            Transaction.account_id == account_id,
            Transaction.external_id.is_(None),
            Transaction.source == "manual",
            Transaction.amount == txn_data.amount,
            Transaction.type == txn_data.type,
            Transaction.date >= date_lo,
            Transaction.date <= date_hi,
        )
    )
    candidates = result.scalars().all()
    if not candidates:
        return None

    best_match = None
    best_score = 0.0
    for candidate in candidates:
        score = token_overlap(
            candidate.original_description or candidate.description,
            txn_data.description,
        )
        if score > best_score:
            best_score = score
            best_match = candidate

    if best_match and best_score >= 0.6:
        return best_match
    return None


def _merge_sync_metadata(
    transaction: Transaction,
    txn_data,
    *,
    replace_external_id: bool = False,
) -> None:
    if transaction.source == "sync" and transaction.date.year <= 1970:
        transaction.date = txn_data.date
        if transaction.effective_date.year <= 1970:
            transaction.effective_date = txn_data.date
    if transaction.source in LOCAL_IMPORT_SOURCES:
        transaction.import_id = None
        replace_external_id = True
    if txn_data.external_id and (replace_external_id or not transaction.external_id):
        transaction.external_id = txn_data.external_id
    if txn_data.raw_data:
        if not transaction.raw_data:
            transaction.raw_data = txn_data.raw_data
        elif isinstance(transaction.raw_data, dict) and isinstance(txn_data.raw_data, dict):
            merged = {**transaction.raw_data, **txn_data.raw_data}
            if merged != transaction.raw_data:
                transaction.raw_data = merged
    if transaction.original_description is None:
        transaction.original_description = txn_data.description
    if txn_data.payee and not transaction.payee:
        transaction.payee = txn_data.payee


async def _find_synced_duplicate(
    session: AsyncSession,
    account_id: uuid.UUID,
    txn_data,
    incoming_external_ids: set[str],
) -> Optional[Transaction]:
    """Find an existing row that the incoming `txn_data` is a twin of.

    The `(account_id, external_id)` lookup only catches the case where a
    provider keeps the same id while a row's `status` flips pending→posted.
    It misses two patterns where the same logical operation comes back with
    two different external ids:

    1. The provider re-emits the operation with a new id when its state
       changes — e.g. a scheduled/pending row replaced by a posted row.
       Same account/date/amount/type with statuses differing.
    2. A credit-card installment that lands on the current bill but is also
       still scheduled against the next bill. Two different external ids
       and two different bills, but the same installment fingerprint
       `(purchase_date, number, total, amount, type)`.

    Returns the existing Transaction the caller should reuse; the caller
    decides whether to upgrade its status (pending→posted + swap external_id),
    enrich an imported row, or skip the incoming insert. Synthetic bill-charge rows
    (`bill_charge:*`) are excluded — they have their own idempotency keys.
    """
    # Path 1: installment fingerprint. Highly specific, so we don't require a
    # description match on top.
    if (
        txn_data.installment_purchase_date is not None
        and txn_data.installment_number is not None
        and txn_data.total_installments is not None
    ):
        result = await session.execute(
            select(Transaction).where(
                Transaction.account_id == account_id,
                Transaction.source == "sync",
                Transaction.installment_purchase_date == txn_data.installment_purchase_date,
                Transaction.installment_number == txn_data.installment_number,
                Transaction.total_installments == txn_data.total_installments,
                Transaction.amount == txn_data.amount,
                Transaction.type == txn_data.type,
                Transaction.external_id != txn_data.external_id,
            )
        )
        for candidate in result.scalars():
            if candidate.external_id and candidate.external_id.startswith("bill_charge:"):
                continue
            return candidate

    # Path 2: pending↔posted twin on the same account/date/amount/type. The
    # status differential is the load-bearing signal — without it we'd risk
    # collapsing two genuinely separate transactions that happen to share a
    # day and amount. A light description-similarity check guards against
    # the residual false positive of two different merchants charging the
    # same amount the same day where one is pending and one is posted.
    result = await session.execute(
        select(Transaction).where(
            Transaction.account_id == account_id,
            Transaction.source == "sync",
            Transaction.date == txn_data.date,
            Transaction.amount == txn_data.amount,
            Transaction.type == txn_data.type,
            Transaction.status != txn_data.status,
            Transaction.external_id != txn_data.external_id,
        )
    )
    for candidate in result.scalars():
        if candidate.external_id and candidate.external_id.startswith("bill_charge:"):
            continue
        if token_overlap(
            candidate.original_description or candidate.description,
            txn_data.description,
        ) >= 0.7:
            return candidate

    # Path 3: exact posted/transacted timestamp fingerprint. Some SimpleFIN
    # bridges re-key already-posted rows on later pulls, so status does not
    # differ. The raw bank timestamps plus same account/date/amount/type and a
    # near-identical description are specific enough to collapse the re-keyed
    # row while avoiding broad same-day/same-amount merchant dedupe.
    raw = txn_data.raw_data if isinstance(txn_data.raw_data, dict) else {}
    posted = raw.get("posted")
    transacted_at = raw.get("transacted_at")
    if posted is not None or transacted_at is not None:
        result = await session.execute(
            select(Transaction).where(
                Transaction.account_id == account_id,
                or_(
                    Transaction.source == "sync",
                    (
                        Transaction.source.in_(LOCAL_IMPORT_SOURCES)
                        & Transaction.raw_data.is_not(None)
                    ),
                ),
                Transaction.date >= txn_data.date - timedelta(days=3),
                Transaction.date <= txn_data.date + timedelta(days=3),
                Transaction.amount == txn_data.amount,
                Transaction.type == txn_data.type,
                Transaction.status == txn_data.status,
                Transaction.external_id != txn_data.external_id,
            )
        )
        for candidate in result.scalars():
            if candidate.external_id and candidate.external_id.startswith("bill_charge:"):
                continue
            if candidate.external_id in incoming_external_ids:
                continue
            candidate_raw = candidate.raw_data if isinstance(candidate.raw_data, dict) else {}
            candidate_descriptions = (
                candidate_raw.get("description"), candidate.payee, candidate.description,
            )
            incoming_descriptions = (
                raw.get("description"), txn_data.payee, txn_data.description,
            )
            description_matches = any(
                left and right and token_overlap(left, right) >= 0.9
                for left in candidate_descriptions
                for right in incoming_descriptions
            )
            if (
                candidate_raw.get("posted") == posted
                and candidate_raw.get("transacted_at") == transacted_at
                and description_matches
            ):
                return candidate

    # Path 5: local import history from before the account was connected.
    # Posting lag and statement exports can shift the date or shorten the
    # merchant description, so accept only one exact normalized merchant/payee.
    return await find_unique_transaction_match(
        session,
        account_id,
        txn_data,
        LOCAL_IMPORT_SOURCES,
        unclaimed_only=True,
        exclude_external_ids=incoming_external_ids,
    )


async def _cleanup_phantom_duplicates(
    session: AsyncSession,
    connection_id: uuid.UUID,
    live_external_ids: Optional[dict[uuid.UUID, set[str]]] = None,
    skip_account_ids: Optional[set[uuid.UUID]] = None,
) -> set[uuid.UUID]:
    """Delete synced transactions that are phantom duplicates.

    Some providers (or sandbox data) report the same payment twice with
    different external ids on adjacent days. Transfer detection matches the
    real one against the counterpart in another account; the phantom remains
    orphaned.

    We delete an unpaired synced tx when it has a *paired* sibling in the same
    account with: same amount, same type, near-identical description, dated
    within ±1 day. The pairing of the sibling is the safety signal that lets
    us distinguish the duplicate from a legitimate same-day repeat (e.g. two
    real Uber rides for the same fare).

    ``live_external_ids`` maps an account id to the transaction ids the
    provider returned for it in this sync. For an account in that map, a
    sibling with a different id marks the tx as a phantom only when exactly
    one of the two ids is still returned, which is what a re-keyed twin looks
    like. Both ids returned means two real payments (e.g. two same-day Pix of
    the same amount, #1038). Neither returned means the rows have left the
    provider's fetch window, so this sync has no evidence about them, and they
    were already checked while they were in it.

    ``skip_account_ids`` are accounts whose fetch this sync was cut short
    (e.g. an authorization failure mid-window). Their live set is partial,
    so a row from the missing part would look like a re-keyed twin of a
    returned sibling; they are left alone until a complete fetch.

    Returns the ids of the accounts that lost a row, so the caller can
    reconcile their opening balances against what is left.
    """
    accounts_result = await session.execute(
        select(Account.id).where(Account.connection_id == connection_id)
    )
    account_ids = [
        row[0] for row in accounts_result.all()
        if row[0] not in (skip_account_ids or set())
    ]
    if not account_ids:
        return set()

    unmatched_result = await session.execute(
        select(Transaction).where(
            Transaction.account_id.in_(account_ids),
            Transaction.source == "sync",
            Transaction.transfer_pair_id.is_(None),
        )
    )
    unmatched = list(unmatched_result.scalars().all())

    touched: set[uuid.UUID] = set()
    for tx in unmatched:
        live = (live_external_ids or {}).get(tx.account_id)
        date_lo = tx.date - timedelta(days=1)
        date_hi = tx.date + timedelta(days=1)
        sibling_result = await session.execute(
            select(Transaction).where(
                Transaction.account_id == tx.account_id,
                Transaction.source == "sync",
                Transaction.amount == tx.amount,
                Transaction.type == tx.type,
                Transaction.date >= date_lo,
                Transaction.date <= date_hi,
                Transaction.transfer_pair_id.is_not(None),
                Transaction.id != tx.id,
            )
        )
        for sibling in sibling_result.scalars():
            if (
                live is not None
                and tx.external_id != sibling.external_id
                and (tx.external_id in live) == (sibling.external_id in live)
            ):
                continue
            if token_overlap(
                sibling.original_description or sibling.description,
                tx.original_description or tx.description,
            ) >= 0.9:
                await session.delete(tx)
                touched.add(tx.account_id)
                break

    return touched


# Finance-charge `additionalInfo` strings that Pluggy emits but which would
# double-count if materialized as transactions:
#   - "Saldo em atraso" — the prior bill's unpaid balance carried into this
#     bill. It's an informational line, not part of bill.totalAmount.
#   - "Juros de dívida encerrada" — an aggregate that equals the sum of the
#     detailed late-charge items (IOF + LATE_PAYMENT_*) Pluggy ALSO lists
#     separately on the same bill.
# Matched case-insensitively after stripping whitespace. Issue #92.
_FINANCE_CHARGE_SKIP_INFO = {
    "saldo em atraso",
    "juros de dívida encerrada",
}


def _compute_bill_close_date(due_date: date, close_day: Optional[int]) -> date:
    """The cycle's close date — when the bank snapshots the bill and applies
    finance charges. We don't get this from the provider directly; we derive
    it as "the most recent statement_close_day on or before the bill's
    due_date" (a few days before due, the typical close-to-due gap). When
    the account has no close_day configured we fall back to due_date.

    Why this date, not due_date: charges accrue at close, before the user
    pays the bill. Stamping them at due_date makes them appear chronologically
    after the payment in the tx list, which doesn't match real bank semantics.
    """
    import calendar  # local — not used elsewhere in this file
    if not close_day:
        return due_date
    last = calendar.monthrange(due_date.year, due_date.month)[1]
    same_month = date(due_date.year, due_date.month, min(close_day, last))
    if same_month <= due_date:
        return same_month
    if due_date.month == 1:
        py, pm = due_date.year - 1, 12
    else:
        py, pm = due_date.year, due_date.month - 1
    plast = calendar.monthrange(py, pm)[1]
    return date(py, pm, min(close_day, plast))


def _describe_finance_charge(type_str: str, additional_info: Optional[str]) -> str:
    """User-facing description for a synthetic finance-charge transaction.

    Pluggy connectors emit human-readable Portuguese strings in
    `additionalInfo`; we prefer those because the bank's own wording is what
    the user expects to see. Fall back to a localized label keyed off the
    enumerated `type` when the info field is absent.
    """
    if additional_info:
        return additional_info.strip()
    return {
        "IOF": "IOF",
        "LATE_PAYMENT_FEE": "Multa por atraso",
        "LATE_PAYMENT_INTEREST": "Juros por atraso",
        "LATE_PAYMENT_REMUNERATIVE_INTEREST": "Juros remuneratórios",
    }.get(type_str, "Encargo")


async def _sync_bill_finance_charges(
    session: AsyncSession,
    user_id: uuid.UUID,
    account: Account,
    bill: CreditCardBill,
    raw_charges: list,
) -> None:
    """Materialize a bill's finance charges (IOF, juros, multa, etc.) as
    synthetic transactions linked to the bill.

    Without this, the cycle's tx sum can't reconcile to bill.total_amount —
    the bank charges these but the provider doesn't always emit them as
    standalone transactions.

    Each synthetic tx has a stable external_id of the form
    `bill_charge:{bill.external_id}:{charge.id}` so re-sync is idempotent and
    self-healing: removed charges are detected and deleted; updated charges
    overwrite in place. Charges matching the double-count patterns above
    (carry-over balance, aggregate of detailed lines) are skipped.
    """
    # date = close (when the bank applied the charge); effective_date stays
    # at bill.due_date so accrual-mode aggregations bucket the same as
    # regular CC purchases for this bill.
    charge_date = _compute_bill_close_date(bill.due_date, account.statement_close_day)

    desired_external_ids: set[str] = set()
    for raw in raw_charges:
        if not isinstance(raw, dict):
            continue
        info = (raw.get("additionalInfo") or "").strip().lower()
        if info in _FINANCE_CHARGE_SKIP_INFO:
            continue
        amount_raw = raw.get("amount")
        try:
            amount = Decimal(str(amount_raw))
        except (ValueError, TypeError, InvalidOperation):
            continue
        if amount == 0:
            continue
        charge_id = raw.get("id")
        if not charge_id:
            continue
        external_id = f"bill_charge:{bill.external_id}:{charge_id}"
        desired_external_ids.add(external_id)

        existing = (await session.execute(
            select(Transaction).where(
                Transaction.account_id == account.id,
                Transaction.external_id == external_id,
            )
        )).scalar_one_or_none()

        description = _describe_finance_charge(
            str(raw.get("type") or ""), raw.get("additionalInfo")
        )

        if existing:
            existing.amount = abs(amount)
            existing.description = description
            existing.date = charge_date
            existing.effective_date = bill.due_date
            existing.bill_id = bill.id
            existing.raw_data = raw
        else:
            tx = Transaction(
                user_id=user_id,
                workspace_id=account.workspace_id,
                account_id=account.id,
                external_id=external_id,
                description=description,
                amount=abs(amount),
                currency=bill.currency,
                date=charge_date,
                effective_date=bill.due_date,
                type="debit",
                source="sync",
                status="posted",
                raw_data=raw,
                bill_id=bill.id,
            )
            session.add(tx)

    # Drop synthetic charges Pluggy no longer reports for this bill (e.g.
    # the bank reversed an erroneous fee on a re-sync). Real transactions
    # don't share the bill_charge: prefix so they're untouched.
    orphans = (await session.execute(
        select(Transaction).where(
            Transaction.account_id == account.id,
            Transaction.bill_id == bill.id,
            Transaction.external_id.like(f"bill_charge:{bill.external_id}:%"),
        )
    )).scalars().all()
    for tx in orphans:
        if tx.external_id not in desired_external_ids:
            await session.delete(tx)


async def _sync_credit_card_bills(
    session: AsyncSession,
    user_id: uuid.UUID,
    account: Account,
    provider,
    credentials: dict,
) -> dict[str, CreditCardBill]:
    """Fetch and upsert bills for a credit-card account.

    Returns a {external_id: bill} dict so the caller can resolve transaction
    bill_id without N+1 queries. For non-CC accounts or providers that don't
    expose bills, returns an empty dict — the read path then falls back to
    locally-computed cycle math via apply_effective_date.

    Failures are intentionally swallowed (logged at info): a non-regulado
    Pluggy connection 4xx'es here, a temporary API hiccup shouldn't fail
    the whole sync, and the cycle-math fallback already covers the gap.
    """
    if account.type != "credit_card":
        return {}

    try:
        bills_data = await provider.get_bills(credentials, account.external_id)
    except Exception as e:  # noqa: BLE001 — provider failures must not fail sync
        logger.info(
            "Skipping credit-card bills sync for account %s: %s", account.id, e
        )
        return {}

    if not bills_data:
        return {}

    existing = (
        await session.execute(
            select(CreditCardBill).where(CreditCardBill.account_id == account.id)
        )
    ).scalars().all()
    by_external_id: dict[str, CreditCardBill] = {b.external_id: b for b in existing}

    for bd in bills_data:
        bill = by_external_id.get(bd.external_id)
        if bill is None:
            bill = CreditCardBill(
                user_id=user_id,
                account_id=account.id,
                external_id=bd.external_id,
                due_date=bd.due_date,
                total_amount=bd.total_amount,
                currency=bd.currency,
                minimum_payment=bd.minimum_payment,
                raw_data=bd.raw_data,
            )
            session.add(bill)
            by_external_id[bd.external_id] = bill
        else:
            bill.due_date = bd.due_date
            bill.total_amount = bd.total_amount
            bill.currency = bd.currency
            bill.minimum_payment = bd.minimum_payment
            bill.raw_data = bd.raw_data

    await session.flush()

    # Materialize finance charges (IOF, juros, multa, etc.) as transactions
    # linked to each bill so the cycle sum reconciles to bill.total_amount.
    for bd in bills_data:
        bill = by_external_id.get(bd.external_id)
        if bill is None:
            continue
        raw_charges = (bd.raw_data or {}).get("financeCharges")
        if isinstance(raw_charges, list) and raw_charges:
            await _sync_bill_finance_charges(
                session, user_id, account, bill, raw_charges,
            )

    return by_external_id


# Outcome of a sync that completed without raising, reported to the caller
# so the UI can tell "data read" from "the provider throttled us and nothing
# was pulled" (issue #736). "refreshed" covers the normal path (the data may
# be the provider's cached copy; whether a fresh pull happened is the
# provider's own semantics). "rate_limited" means the provider returned a
# 429: nothing was pulled and last_sync_at was left untouched so the next
# sync retries the same window. Errors (expired session, user action
# required, generic failure) still propagate as exceptions.
SyncOutcome = Literal["refreshed", "rate_limited"]


async def sync_connection(
    session: AsyncSession,
    connection_id: uuid.UUID,
    workspace_id: uuid.UUID,
    requesting_user_id: uuid.UUID,
    trigger_provider_refresh: bool = False,
) -> tuple[BankConnection, int, SyncOutcome]:
    connection = await get_connection(
        session, connection_id, workspace_id, for_update=True
    )
    if not connection:
        raise ValueError("Connection not found")
    if not connection.credentials:
        raise ValueError("Credentials not found")

    # Authorization is workspace-scoped and happens before this service is
    # called. Data imported from a bank connection, however, must always be
    # owned by the user who owns that connection — not by whichever workspace
    # member clicked Sync. Mixing those identities creates duplicate holdings
    # and wallets because provider external IDs are unique per user.
    if requesting_user_id != connection.user_id:
        logger.info(
            "Connection %s sync requested by workspace member %s; importing as owner %s",
            connection.id,
            requesting_user_id,
            connection.user_id,
        )
    user_id = connection.user_id

    conn_settings = connection.settings or {}
    payee_source = conn_settings.get("payee_source", "auto")
    import_pending = conn_settings.get("import_pending", True)
    use_provider_cats = await admin_service.use_provider_categories(session)

    # Resolve the provider before the error-handling block: an unregistered
    # provider is a server misconfiguration, and the catch-all below would
    # wrongly stamp the (healthy) connection with status="error".
    try:
        provider = get_provider(connection.provider)
    except ValueError as exc:
        raise ProviderNotConfiguredError(
            f"Provider '{connection.provider}' is not configured in this process. "
            "If connecting from the web app works but background sync fails, the "
            "worker service is likely not loading the environment (.env) that "
            "enables this provider."
        ) from exc

    try:
        # Refresh credentials if needed
        credentials = await provider.refresh_credentials(connection.credentials)
        connection.credentials = credentials

        # Backfill the institution logo for connections linked before logo
        # capture existed. Best-effort: a failure here must never break sync.
        if not connection.logo_url:
            try:
                logo = _clean_logo_url(await provider.get_institution_logo(credentials))
                if logo:
                    connection.logo_url = logo
            except Exception:
                logger.warning(
                    "Failed to backfill logo for connection %s", connection.id,
                    exc_info=True,
                )

        # When the caller asks for fresh data (typically a user-initiated
        # manual sync), ask the provider to pull from the bank before we
        # read. Providers that don't expose an on-demand refresh return
        # "skipped" via the default implementation and we proceed normally.
        if trigger_provider_refresh:
            connection.settings = {
                **conn_settings,
                "last_provider_refresh_at": datetime.now(timezone.utc).isoformat(),
            }
            outcome = await provider.trigger_refresh(credentials)
            if outcome == "needs_user_action":
                # Surfacing reconnect immediately is better than silently
                # reading stale data the user knows is stale.
                connection.status = "error"
                await session.commit()
                raise ProviderUserActionRequired(
                    "Provider needs the user to reconnect before fetching fresh data",
                    code="credentials_invalid",
                )
            # "refreshed", "skipped", or "failed" all fall through to a read.
            # On "failed" we read whatever cached copy the provider has —
            # better than aborting the entire sync over a transient hiccup.

        # Update accounts
        user = await session.get(User, user_id)
        user_currency = user.primary_currency if user else get_settings().default_currency
        new_tx_ids: list[uuid.UUID] = []
        merged_count = 0
        live_txn_external_ids: dict[uuid.UUID, set[str]] = {}
        incomplete_fetch_account_ids: set[uuid.UUID] = set()
        accounts_data = await provider.get_accounts(credentials)
        incoming_external_ids = {acc.external_id for acc in accounts_data}
        incoming_institution_external_ids = frozenset(
            acc.institution_external_id
            for acc in accounts_data
            if acc.institution_external_id
        )
        # Holdings are fetched before the accounts so each account balance
        # knows whether its auto-invested portion is counted as an asset
        # (issue #843). Without a payload (asset sync off or a failed fetch)
        # the deduction is decided from the live synced assets already stored.
        holdings: Optional[list[HoldingData]] = None
        if _sync_assets_enabled(conn_settings):
            holdings = await _fetch_holdings(connection, credentials)
        auto_invested_deducted = await _resolve_auto_invested_deductions(
            session, connection, holdings, accounts_data
        )
        institution_cache: dict[str, Institution] = {}
        for acc_data in accounts_data:
            institution = await _resolve_institution(
                session, connection.id, institution_cache, acc_data
            )
            account = await _find_existing_connected_account(
                session,
                connection,
                acc_data,
                institution,
                incoming_external_ids,
                incoming_institution_external_ids,
            )

            if account is not None:
                account.user_id = user_id
                account.workspace_id = connection.workspace_id
                await session.execute(
                    update(Transaction)
                    .where(
                        Transaction.account_id == account.id,
                        Transaction.source == "sync",
                    )
                    .values(user_id=user_id, workspace_id=connection.workspace_id)
                )
                await session.execute(
                    update(CreditCardBill)
                    .where(CreditCardBill.account_id == account.id)
                    .values(user_id=user_id, workspace_id=connection.workspace_id)
                )

            # Honor user intent: a closed connected account stays closed and is
            # not touched by sync. The row is left alone (no balance/name
            # rewrite, no new transactions) but the connection link is kept so
            # the next sync still finds it here instead of creating a duplicate
            # active account (issue #90). Its institution pointer still follows
            # the provider, though — otherwise a renamed org's abandoned row
            # would stay pinned forever and keep a single-bank link presenting
            # as multi-institution.
            if account and account.is_closed:
                if institution is not None and account.institution_id != institution.id:
                    account.institution_id = institution.id
                continue

            if account:
                # Normalize the provider sign using the account's CURRENT type,
                # which reflects any user override (sync never rewrites `type`).
                # SimpleFIN reports card debt as negative under a "checking"
                # label; once the user overrides the type to credit_card the
                # downstream sites negate it, so store positive-for-debt to keep
                # them provider-agnostic and avoid double-counting. A loan reads
                # the same way whatever the provider called the account.
                account.balance = _provider_to_internal_balance(
                    connection.provider,
                    account.type,
                    _account_balance(acc_data, auto_invested_deducted),
                )
                account.name = acc_data.name
                # Backfills existing accounts on their next sync. Only written
                # when the provider actually returns an identifier, so a payload
                # that intermittently omits it can't blank out a known mask.
                if acc_data.masked_number is not None:
                    account.masked_number = acc_data.masked_number
                # Backfills existing accounts on their next sync, same rule as
                # masked_number: provider-owned, and a payload that omits it
                # must not blank a value that is already known.
                if acc_data.stable_external_id is not None:
                    account.stable_external_id = acc_data.stable_external_id
                # Backfills existing accounts on next sync (issue #345).
                if institution is not None:
                    account.institution_id = institution.id
                    # Only when the user hasn't named the account themselves —
                    # never overwrite a manual display_name.
                    if account.display_name is None:
                        account.display_name = institution.name
                if acc_data.type == "credit_card":
                    # Preserve existing CC metadata when the provider doesn't
                    # expose it. Pluggy's creditData fields (limit, close/due
                    # dates, minimum payment, brand/level) are intermittently
                    # null even on connectors that have them elsewhere, and
                    # users may have filled them in manually via the edit
                    # dialog. Treat user input + previously-synced values as
                    # the higher source of truth than a fresh None.
                    if acc_data.credit_limit is not None:
                        account.credit_limit = acc_data.credit_limit
                    if acc_data.statement_close_day is not None:
                        account.statement_close_day = acc_data.statement_close_day
                    if acc_data.payment_due_day is not None:
                        account.payment_due_day = acc_data.payment_due_day
                    if acc_data.minimum_payment is not None:
                        account.minimum_payment = acc_data.minimum_payment
                    if acc_data.card_brand is not None:
                        account.card_brand = acc_data.card_brand
                    if acc_data.card_level is not None:
                        account.card_level = acc_data.card_level
                    account.shared_balance_group = (
                        f"{connection.id}:{acc_data.shared_balance_group}"
                        if acc_data.shared_balance_group else None
                    )
            else:
                is_cc = acc_data.type == "credit_card"
                account = Account(
                    user_id=user_id,
                    workspace_id=workspace_id,
                    connection_id=connection.id,
                    external_id=acc_data.external_id,
                    stable_external_id=acc_data.stable_external_id,
                    name=acc_data.name,
                    display_name=institution.name if institution else None,
                    masked_number=acc_data.masked_number,
                    type=acc_data.type,
                    balance=_provider_to_internal_balance(
                        connection.provider,
                        acc_data.type,
                        _account_balance(acc_data, auto_invested_deducted),
                    ),
                    currency=acc_data.currency,
                    credit_limit=acc_data.credit_limit if is_cc else None,
                    statement_close_day=acc_data.statement_close_day if is_cc else None,
                    payment_due_day=acc_data.payment_due_day if is_cc else None,
                    minimum_payment=acc_data.minimum_payment if is_cc else None,
                    card_brand=acc_data.card_brand if is_cc else None,
                    card_level=acc_data.card_level if is_cc else None,
                    shared_balance_group=(
                        f"{connection.id}:{acc_data.shared_balance_group}"
                        if acc_data.shared_balance_group else None
                    ),
                    institution_id=institution.id if institution else None,
                )
                session.add(account)
                await session.flush()

            # Fetch the bills feed before transactions so transaction → bill
            # FK resolution happens in-memory (no N+1). Empty dict for non-CC
            # accounts or providers without /bills.
            bills_by_external_id = await _sync_credit_card_bills(
                session, user_id, account, provider, credentials
            )

            # Fetch and sync transactions. The 14-day rewind is on Pluggy's
            # `createdAt` (when their row was inserted), so it covers two
            # cases: (1) PENDING transactions that POSTED since last sync,
            # (2) any rows Pluggy ingested late but backdated. Dedup on
            # external_id below handles overlap cheaply.
            since = (
                connection.last_sync_at.date() - timedelta(days=14)
                if connection.last_sync_at
                else None
            )
            transactions_data = await provider.get_transactions(
                credentials, acc_data.external_id, since, payee_source=payee_source
            )

            # Every id the provider returned, pending ones included, so the
            # phantom cleanup still sees a pending row the filter below skips.
            live_txn_external_ids[account.id] = {
                txn.external_id for txn in transactions_data
            }
            incomplete = getattr(provider, "incomplete_account_ids", None)
            if isinstance(incomplete, set) and acc_data.external_id in incomplete:
                incomplete_fetch_account_ids.add(account.id)
            if not import_pending:
                transactions_data = [t for t in transactions_data if t.status != "pending"]

            incoming_txn_external_ids = {txn.external_id for txn in transactions_data}
            for txn_data in transactions_data:
                existing = await session.execute(
                    select(Transaction)
                    .where(
                        Transaction.account_id == account.id,
                        Transaction.external_id == txn_data.external_id,
                    )
                    .order_by(Transaction.created_at)
                )
                # `.first()` rather than `.scalar_one_or_none()`: a prior sync
                # race (two overlapping passes both select-then-insert the same
                # external_id before either commits) can leave two rows sharing
                # (account_id, external_id). scalar_one_or_none() would raise
                # MultipleResultsFound and abort the whole connection's sync;
                # we instead reconcile onto the oldest matching row and skip
                # re-inserting, so a stray duplicate is harmless and never grows.
                existing_tx = existing.scalars().first()
                if existing_tx:
                    # User-flagged rows are frozen: skip status/bill drift so
                    # a re-sync can't revive a transaction the user hid.
                    if existing_tx.is_ignored:
                        continue
                    _merge_sync_metadata(existing_tx, txn_data)
                    if existing_tx.status == "pending" and txn_data.status == "posted":
                        existing_tx.status = "posted"
                    # Self-heal bill linkage: a tx that pre-dates the bills
                    # feature (or whose bill we hadn't ingested last time)
                    # picks up bill_id + bank-truth effective_date on the
                    # first sync after the bill becomes available. Same
                    # branch covers re-bucketing if the bank moved a tx to
                    # a different bill (e.g. a chargeback).
                    #
                    # User's manual override wins: if effective_bill_date is
                    # set, we don't touch bill_id or effective_date — the
                    # user has explicitly overridden the auto bucketing.
                    if (
                        txn_data.bill_external_id
                        and existing_tx.effective_bill_date is None
                    ):
                        bill = bills_by_external_id.get(txn_data.bill_external_id)
                        if bill is not None and existing_tx.bill_id != bill.id:
                            existing_tx.bill_id = bill.id
                            apply_effective_date(
                                existing_tx, account, bill_due_date=bill.due_date
                            )
                    continue

                # Pass 2: Fuzzy match against manual transactions
                fuzzy_match = await _fuzzy_match_manual(session, account.id, txn_data)
                if fuzzy_match:
                    if fuzzy_match.is_ignored:
                        continue
                    _merge_sync_metadata(fuzzy_match, txn_data)
                    fuzzy_match.source = "sync"
                    merged_count += 1
                    continue

                # Pass 3: pending↔posted twin (and the credit-card
                # installment variant). When the same logical operation
                # comes back under a new external id with a different
                # status, fingerprint match collapses it instead of letting
                # both rows land.
                synced_dup = await _find_synced_duplicate(
                    session, account.id, txn_data, incoming_txn_external_ids
                )
                if synced_dup:
                    if synced_dup.original_description is None:
                        synced_dup.original_description = txn_data.description
                    if synced_dup.status == "pending" and txn_data.status == "posted":
                        # Posted truth wins: swap in the new id so subsequent
                        # syncs match by external_id and update raw_data.
                        synced_dup.status = "posted"
                        _merge_sync_metadata(
                            synced_dup,
                            txn_data,
                            replace_external_id=synced_dup.source == "sync",
                        )
                        if (
                            txn_data.bill_external_id
                            and synced_dup.effective_bill_date is None
                        ):
                            bill = bills_by_external_id.get(txn_data.bill_external_id)
                            if bill is not None and synced_dup.bill_id != bill.id:
                                synced_dup.bill_id = bill.id
                                apply_effective_date(
                                    synced_dup, account, bill_due_date=bill.due_date
                                )
                    elif synced_dup.external_id != txn_data.external_id:
                        # Same logical posted row re-keyed by a provider such
                        # as SimpleFIN. Keep the user's row and move its
                        # idempotency key forward instead of inserting a twin.
                        _merge_sync_metadata(
                            synced_dup,
                            txn_data,
                            replace_external_id=synced_dup.source == "sync",
                        )
                    continue

                incoming_currency = (
                    txn_data.currency or acc_data.currency or user_currency
                )
                acct_currency = acc_data.currency or user_currency
                converted = _bank_converted_amount(
                    txn_data, acct_currency, user_currency
                )
                category_id = await _find_installment_category(
                    session, account.id, txn_data
                )
                if category_id is None:
                    category_id = await _match_pluggy_category(
                        session,
                        workspace_id,
                        txn_data.pluggy_category,
                        enabled=use_provider_cats,
                    )

                sync_payee_id = None
                if txn_data.payee:
                    sync_payee_entity = await get_or_create_payee(
                        session,
                        user_id,
                        txn_data.payee,
                        workspace_id=workspace_id,
                    )
                    sync_payee_id = sync_payee_entity.id

                bill = (
                    bills_by_external_id.get(txn_data.bill_external_id)
                    if txn_data.bill_external_id
                    else None
                )
                transaction = Transaction(
                    user_id=user_id,
                    workspace_id=workspace_id,
                    account_id=account.id,
                    external_id=txn_data.external_id,
                    description=txn_data.description,
                    original_description=txn_data.description,
                    amount=txn_data.amount,
                    currency=incoming_currency,
                    date=txn_data.date,
                    type=txn_data.type,
                    source="sync",
                    status=txn_data.status,
                    payee=txn_data.payee,
                    payee_id=sync_payee_id,
                    raw_data=txn_data.raw_data,
                    category_id=None,
                    installment_number=txn_data.installment_number,
                    total_installments=txn_data.total_installments,
                    installment_total_amount=txn_data.installment_total_amount,
                    installment_purchase_date=txn_data.installment_purchase_date,
                    bill_id=bill.id if bill else None,
                )
                apply_effective_date(
                    transaction,
                    account,
                    bill_due_date=bill.due_date if bill else None,
                )
                preview = await preview_rules_for_transaction(
                    session, user_id, transaction
                )

                # Normalize before recurring reconciliation. A generated
                # placeholder is upgraded in place; otherwise the normalized
                # candidate may fulfill an active definition, which advances so
                # later generation cannot duplicate the occurrence.
                placeholder = (
                    await recurring_match_service.find_placeholder_for_incoming(
                        session,
                        account.id,
                        txn_data.amount,
                        incoming_currency,
                        txn_data.type,
                        txn_data.date,
                        preview.description,
                    )
                )
                if placeholder:
                    if placeholder.is_ignored:
                        continue
                    placeholder.external_id = txn_data.external_id
                    placeholder.source = "sync"
                    placeholder.status = txn_data.status
                    placeholder.raw_data = txn_data.raw_data
                    # Same shape as the import path: fold in the `preview` the
                    # rules already produced from the incoming charge instead of
                    # re-running them against the placeholder, whose description
                    # is the recurring definition's own wording. Existing values
                    # win, the charge fills the empty ones, and only its
                    # provenance is recorded outright.
                    placeholder.original_description = txn_data.description
                    if placeholder.amount != txn_data.amount or converted is not None:
                        # Matched under a tolerance, or the bank said what the
                        # charge cost in the primary currency: the row now
                        # stands for what was actually charged, not for what
                        # the bill expected.
                        placeholder.amount = txn_data.amount
                        await _stamp_synced_primary_amount(
                            session, user_id, placeholder, converted
                        )
                    if placeholder.category_id is None:
                        placeholder.category_id = preview.category_id or category_id
                    if txn_data.payee and not placeholder.payee:
                        placeholder.payee = txn_data.payee
                    if placeholder.payee_id is None:
                        placeholder.payee_id = preview.payee_id
                    placeholder.notes = merge_notes(
                        placeholder.notes, preview.notes
                    )
                    if preview.is_ignored:
                        placeholder.is_ignored = True
                    merged_count += 1
                    continue

                recurring_link = (
                    await recurring_match_service.find_bill_for_incoming(
                        session,
                        user_id,
                        account.id,
                        txn_data.amount,
                        incoming_currency,
                        txn_data.type,
                        txn_data.date,
                        preview.description,
                    )
                )
                transaction.recurring_transaction_id = (
                    recurring_link.id if recurring_link else None
                )
                session.add(transaction)
                await session.flush()
                if recurring_link is not None:
                    recurring_match_service.advance_past(
                        recurring_link, txn_data.date
                    )
                new_tx_ids.append(transaction.id)
                await apply_rules_to_transaction(session, user_id, transaction)
                if transaction.category_id is None:
                    transaction.category_id = category_id

                await _stamp_synced_primary_amount(
                    session, user_id, transaction, converted
                )

            # Reconcile the opening balance after any new transactions land so
            # SUM(all txs) keeps matching account.balance from the provider.
            await sync_opening_balance_for_connected_account(session, account)

        # Detect transfer pairs among newly synced transactions
        if new_tx_ids:
            await detect_transfer_pairs(session, workspace_id, candidate_ids=new_tx_ids)

            # Then settle whatever this money was promised against. It runs
            # after the rows are written, not inside the loop: the recurring
            # match upgrades a placeholder in place, while an invoice link is
            # a row pointing at a transaction that has to exist first. It is
            # also a single batch, so the candidate invoices are loaded once
            # per sync rather than once per transaction.
            landed = list(
                (
                    await session.execute(
                        select(Transaction).where(Transaction.id.in_(new_tx_ids))
                    )
                )
                .scalars()
                .all()
            )
            await reconciliation_service.match_incoming(
                session, workspace_id, landed
            )

        # Clean up phantom duplicates: providers occasionally double-report the
        # same payment with different ids. Once transfer detection has paired
        # the real one, the orphan twin gets removed here.
        touched_account_ids = await _cleanup_phantom_duplicates(
            session, connection.id, live_txn_external_ids,
            skip_account_ids=incomplete_fetch_account_ids,
        )

        # The opening balances above were reconciled with the phantoms still
        # counted. Reconcile the open accounts that lost one again, so the
        # removed amount does not stay behind in their synthetic opening
        # transaction. Closed accounts stay out, as in the account loop.
        if touched_account_ids:
            await session.flush()
            touched_accounts = await session.execute(
                select(Account).where(
                    Account.id.in_(touched_account_ids),
                    Account.is_closed == False,
                )
            )
            for touched_account in touched_accounts.scalars():
                await sync_opening_balance_for_connected_account(session, touched_account)

        # Refresh investment holdings (brokerage, fixed income, funds,
        # etc.) when enabled for this connection. Errors here are logged but
        # don't fail the sync; a bank connector that doesn't expose
        # /investments shouldn't block the transaction sync that just succeeded.
        if holdings is not None:
            await _sync_holdings(session, user_id, connection, credentials, holdings)

        # Reap institution rows referenced by nothing. Id-carrying servers
        # never orphan a row (renames update in place), but a name-only
        # server that renames its bank mints a fresh row and repoints the
        # accounts — the abandoned row would otherwise keep a single-bank
        # link presenting as multi-institution forever. Requiring zero
        # account AND zero wallet references means a wallet's "Synced from"
        # label can never be taken away. Runs after the holdings sync so a
        # wallet repointed away from the old row frees it this sync, not next.
        orphaned_institutions = await session.execute(
            select(Institution).where(
                Institution.connection_id == connection.id,
                ~exists().where(Account.institution_id == Institution.id),
                ~exists().where(AssetGroup.institution_id == Institution.id),
            )
        )
        for orphan in orphaned_institutions.scalars().all():
            await session.delete(orphan)

        # An account whose fetch was cut short (issue #1077) is missing the
        # rest of the window. The next fetch starts from last_sync_at, so
        # moving it forward would leave that gap behind for good once the
        # user reauthorizes; keep it until every account fetched in full.
        # The healthy accounts just re-read a longer window, deduplicated.
        if not incomplete_fetch_account_ids:
            connection.last_sync_at = datetime.now(timezone.utc)
        action_required_warnings = getattr(provider, "action_required_warnings", None)
        if isinstance(action_required_warnings, list) and action_required_warnings:
            logger.warning(
                "Provider %s synced with %d user-action warning(s) for connection %s",
                connection.provider,
                len(action_required_warnings),
                connection.id,
            )
            connection.status = "error"
        else:
            connection.status = "active"
        await session.commit()
        await session.refresh(connection)
        return connection, merged_count, "refreshed"

    except SessionExpiredError:
        # Provider consent expired — distinct from a generic error so the UI
        # can show a clearer "reauthorize" prompt.
        await session.rollback()
        async with session.begin():
            conn = await session.get(BankConnection, connection_id)
            if conn:
                conn.status = "expired"
        raise
    except ProviderUserActionRequired:
        # Stale/revoked provider credentials require a non-destructive
        # reconnect path. Mark the connection unhealthy so the accounts page
        # shows the reconnect banner, then let the API return a typed 409
        # instead of a generic 500.
        await session.rollback()
        async with session.begin():
            conn = await session.get(BankConnection, connection_id)
            if conn:
                conn.status = "error"
        raise
    except ProviderRateLimited:
        # The bank/aggregator is throttling data requests (PSD2 caps unattended
        # access, commonly ~4/day). The connection is healthy, so don't error
        # it or 500 the request — skip this run, keep it active, and leave
        # last_sync_at untouched so the next sync retries the same window.
        await session.rollback()
        async with session.begin():
            conn = await session.get(BankConnection, connection_id)
            if conn and conn.status != "expired":
                conn.status = "active"
        # The row can vanish if the connection was deleted mid-sync. Fall back
        # to the one we already hold rather than raising: re-raising here would
        # escape as a 500, which is exactly what this handler exists to avoid.
        refreshed = await session.get(BankConnection, connection_id)
        # Report "rate_limited" so the caller (manual sync endpoint, UI) knows
        # no fresh data was pulled and can say so instead of a success toast.
        return refreshed or connection, 0, "rate_limited"
    except Exception:
        # Mark connection as errored so UI shows reconnect banner
        await session.rollback()
        async with session.begin():
            conn = await session.get(BankConnection, connection_id)
            if conn:
                conn.status = "error"
        raise


async def delete_connection(
    session: AsyncSession, connection_id: uuid.UUID, workspace_id: uuid.UUID
) -> bool:
    connection = await get_connection(session, connection_id, workspace_id)
    if not connection:
        return False

    # Archive synced investment assets rather than deleting them: the user
    # may still want to see their historical AssetValue trend, and if they
    # re-connect the same provider later we can un-archive by matching
    # (user_id, source, external_id). The FK's ON DELETE SET NULL will
    # then clear connection_id when the row is removed below.
    await session.execute(
        update(Asset)
        .where(Asset.connection_id == connection.id)
        .values(is_archived=True)
    )

    # Track payees referenced by this connection's transactions so we can
    # remove only newly-orphaned records after deleting the connection.
    affected_payee_ids = (
        await session.execute(
            select(Transaction.payee_id)
            .join(Account, Account.id == Transaction.account_id)
            .where(
                Account.connection_id == connection.id,
                Transaction.payee_id.isnot(None),
            )
            .distinct()
        )
    ).scalars().all()

    await session.delete(connection)
    await session.flush()

    if affected_payee_ids:
        has_transactions = exists(
            select(Transaction.id).where(Transaction.payee_id == Payee.id)
        )
        has_external_mappings = exists(
            select(PayeeMapping.id).where(
                PayeeMapping.target_id == Payee.id,
                PayeeMapping.id != Payee.id,
            )
        )
        await session.execute(
            delete(Payee).where(
                Payee.workspace_id == workspace_id,
                Payee.id.in_(affected_payee_ids),
                ~has_transactions,
                ~has_external_mappings,
            )
        )

    await session.commit()
    return True