Skip to content

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

5.2 KB
Raw Blame
import uuid
from datetime import date, datetime
from decimal import Decimal
from typing import TYPE_CHECKING, Optional

from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Numeric, SmallInteger, String
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.database import Base

if TYPE_CHECKING:
    from app.models.bank_connection import BankConnection
    from app.models.institution import Institution
    from app.models.transaction import Transaction


class Account(Base):
    __tablename__ = "accounts"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"))
    workspace_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), index=True
    )
    connection_id: Mapped[Optional[uuid.UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("bank_connections.id"), nullable=True)
    # The provider's id for this account, and the first key the sync matches on.
    # Expected to be stable across re-syncs, but not guaranteed: Enable Banking
    # mints a `uid` scoped to a single session, so it is re-keyed on every
    # reauthorisation. `stable_external_id` is the value that survives that.
    external_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    name: Mapped[str] = mapped_column(String(255))
    display_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    # Last 4 chars of the bank's identifier (IBAN, account or card number), when
    # the provider exposes one. Provider-owned like `name`: refreshed on sync,
    # not user-editable. Never holds the full identifier.
    masked_number: Mapped[Optional[str]] = mapped_column(String(4), nullable=True)
    # Provider-owned and session-independent (Enable Banking's
    # identification_hash): the durable half of the pair with `external_id`. It
    # survives that being re-keyed, so the sync matches on it to avoid
    # duplicating the account after a reconnect. Refreshed alongside `name`,
    # never blanked by a missing value.
    stable_external_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    type: Mapped[str] = mapped_column(String(50))  # checking, savings, credit_card, loan
    # Loan accounts only: the property or vehicle this loan is secured against.
    secured_asset_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("assets.id", ondelete="SET NULL"), nullable=True, index=True
    )
    balance: Mapped[Decimal] = mapped_column(Numeric(precision=15, scale=2), default=Decimal("0.00"))
    # Loan accounts only: the contract terms, all optional. The payment
    # schedule is computed from them on read (app/services/loan_schedule.py).
    loan_kind: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    loan_original_amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(precision=15, scale=2), nullable=True)
    loan_interest_rate: Mapped[Optional[Decimal]] = mapped_column(Numeric(precision=9, scale=6), nullable=True)
    loan_rate_period: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    loan_term_months: Mapped[Optional[int]] = mapped_column(SmallInteger, nullable=True)
    loan_first_payment_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True)
    loan_amortization: Mapped[Optional[str]] = mapped_column(String(10), nullable=True)
    currency: Mapped[str] = mapped_column(String(3), default="USD")
    balance_primary: Mapped[Optional[Decimal]] = mapped_column(Numeric(precision=15, scale=2), nullable=True)
    credit_limit: Mapped[Optional[Decimal]] = mapped_column(Numeric(precision=15, scale=2), nullable=True)
    statement_close_day: Mapped[Optional[int]] = mapped_column(SmallInteger, nullable=True)
    payment_due_day: Mapped[Optional[int]] = mapped_column(SmallInteger, nullable=True)
    minimum_payment: Mapped[Optional[Decimal]] = mapped_column(Numeric(precision=15, scale=2), nullable=True)
    card_brand: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    card_level: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    shared_balance_group: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
    # The institution this account is actually at. One connection can span
    # several (SimpleFIN — issue #345); null falls back to the connection's
    # own institution_name/logo_url. Eager (selectin) because serialization
    # always reads it and lazy loads raise in async sessions.
    institution_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("institutions.id", ondelete="SET NULL"),
        nullable=True, index=True,
    )
    is_closed: Mapped[bool] = mapped_column(Boolean, default=False)
    closed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    connection: Mapped[Optional["BankConnection"]] = relationship(back_populates="accounts")
    institution: Mapped[Optional["Institution"]] = relationship(
        back_populates="accounts", lazy="selectin"
    )
    transactions: Mapped[list["Transaction"]] = relationship(back_populates="account", cascade="all, delete-orphan")