from datetime import date, datetime from decimal import Decimal from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, Numeric, String, Text from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship MONEY = Numeric(14, 2) QTY = Numeric(24, 8) PRICE = Numeric(20, 6) class Base(DeclarativeBase): pass class Account(Base): __tablename__ = "accounts" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(100)) bank: Mapped[str] = mapped_column(String(20), default="ing") # ing | revolut | other iban: Mapped[str | None] = mapped_column(String(40), unique=True) share_pct: Mapped[int] = mapped_column(Integer, default=100) # your share of a joint account balance: Mapped[Decimal | None] = mapped_column(MONEY) balance_date: Mapped[date | None] = mapped_column(Date) eb_account_uid: Mapped[str | None] = mapped_column(String(100)) eb_last_sync: Mapped[datetime | None] = mapped_column(DateTime) class Category(Base): __tablename__ = "categories" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(60), unique=True) kind: Mapped[str] = mapped_column(String(10), default="expense") # expense | income | transfer color: Mapped[str] = mapped_column(String(9), default="#888888") class Rule(Base): __tablename__ = "rules" id: Mapped[int] = mapped_column(primary_key=True) pattern: Mapped[str] = mapped_column(String(200)) # words, or "re:" field: Mapped[str] = mapped_column(String(20), default="any") # any | counterparty | description | iban category_id: Mapped[int] = mapped_column(ForeignKey("categories.id", ondelete="CASCADE")) priority: Mapped[int] = mapped_column(Integer, default=100) # lower wins category: Mapped[Category] = relationship() class Transaction(Base): __tablename__ = "transactions" id: Mapped[int] = mapped_column(primary_key=True) account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id", ondelete="CASCADE"), index=True) date: Mapped[date] = mapped_column(Date, index=True) amount: Mapped[Decimal] = mapped_column(MONEY) # negative = money out currency: Mapped[str] = mapped_column(String(3), default="EUR") counterparty: Mapped[str] = mapped_column(String(200), default="") counter_iban: Mapped[str | None] = mapped_column(String(40)) description: Mapped[str] = mapped_column(Text, default="") raw_type: Mapped[str] = mapped_column(String(60), default="") source: Mapped[str] = mapped_column(String(20), default="csv") hash: Mapped[str] = mapped_column(String(64), unique=True) category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id", ondelete="SET NULL"), index=True) category_locked: Mapped[bool] = mapped_column(Boolean, default=False) recurring_id: Mapped[int | None] = mapped_column(ForeignKey("recurring.id", ondelete="SET NULL")) debt_id: Mapped[int | None] = mapped_column(ForeignKey("debts.id", ondelete="SET NULL")) notes: Mapped[str] = mapped_column(Text, default="") account: Mapped[Account] = relationship() category: Mapped[Category | None] = relationship() class Recurring(Base): __tablename__ = "recurring" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(120)) match: Mapped[str] = mapped_column(String(200)) # normalised counterparty text source: Mapped[str] = mapped_column(String(10), default="detected") # detected | manual status: Mapped[str] = mapped_column(String(10), default="detected") # detected | confirmed | ignored frequency: Mapped[str] = mapped_column(String(10), default="monthly") amount: Mapped[Decimal] = mapped_column(MONEY) # typical amount, negative = out tolerance_pct: Mapped[int] = mapped_column(Integer, default=30) last_date: Mapped[date | None] = mapped_column(Date) next_date: Mapped[date | None] = mapped_column(Date) occurrences: Mapped[int] = mapped_column(Integer, default=0) account_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id", ondelete="SET NULL")) category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id", ondelete="SET NULL")) account: Mapped[Account | None] = relationship() category: Mapped[Category | None] = relationship() class Asset(Base): __tablename__ = "assets" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(120)) kind: Mapped[str] = mapped_column(String(10)) # stock | crypto | gold symbol: Mapped[str] = mapped_column(String(60)) # Yahoo ticker / CoinGecko id / GC=F unit: Mapped[str] = mapped_column(String(10), default="unit") # unit | gram | oz purity: Mapped[Decimal] = mapped_column(Numeric(6, 4), default=Decimal("1")) # gold: 0.9999, 0.9167 (22k)... last_price: Mapped[Decimal | None] = mapped_column(PRICE) # EUR per unit last_price_at: Mapped[datetime | None] = mapped_column(DateTime) manual_price: Mapped[Decimal | None] = mapped_column(PRICE) price_error: Mapped[str | None] = mapped_column(String(300)) trades: Mapped[list["Trade"]] = relationship(back_populates="asset", cascade="all, delete-orphan", order_by="Trade.date") class Trade(Base): __tablename__ = "trades" id: Mapped[int] = mapped_column(primary_key=True) asset_id: Mapped[int] = mapped_column(ForeignKey("assets.id", ondelete="CASCADE")) date: Mapped[date] = mapped_column(Date) side: Mapped[str] = mapped_column(String(4), default="buy") # buy | sell quantity: Mapped[Decimal] = mapped_column(QTY) price: Mapped[Decimal] = mapped_column(PRICE) # EUR per unit fees: Mapped[Decimal] = mapped_column(MONEY, default=Decimal("0")) notes: Mapped[str] = mapped_column(Text, default="") asset: Mapped[Asset] = relationship(back_populates="trades") class PortfolioSnapshot(Base): __tablename__ = "portfolio_snapshots" id: Mapped[int] = mapped_column(primary_key=True) date: Mapped[date] = mapped_column(Date, index=True) kind: Mapped[str] = mapped_column(String(10)) value: Mapped[Decimal] = mapped_column(MONEY) cost: Mapped[Decimal] = mapped_column(MONEY) class Debt(Base): __tablename__ = "debts" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(120)) counterparty: Mapped[str] = mapped_column(String(120), default="") direction: Mapped[str] = mapped_column(String(12)) # i_owe | owed_to_me principal: Mapped[Decimal] = mapped_column(MONEY) start_date: Mapped[date] = mapped_column(Date) interest_rate: Mapped[Decimal | None] = mapped_column(Numeric(6, 3)) monthly_payment: Mapped[Decimal | None] = mapped_column(MONEY) due_day: Mapped[int | None] = mapped_column(Integer) match_pattern: Mapped[str | None] = mapped_column(String(200)) closed: Mapped[bool] = mapped_column(Boolean, default=False) notes: Mapped[str] = mapped_column(Text, default="") payments: Mapped[list["DebtPayment"]] = relationship(back_populates="debt", cascade="all, delete-orphan", order_by="DebtPayment.date") class DebtPayment(Base): __tablename__ = "debt_payments" id: Mapped[int] = mapped_column(primary_key=True) debt_id: Mapped[int] = mapped_column(ForeignKey("debts.id", ondelete="CASCADE")) date: Mapped[date] = mapped_column(Date) amount: Mapped[Decimal] = mapped_column(MONEY) # always positive transaction_id: Mapped[int | None] = mapped_column(ForeignKey("transactions.id", ondelete="SET NULL"), unique=True) notes: Mapped[str] = mapped_column(Text, default="") debt: Mapped[Debt] = relationship(back_populates="payments") class Setting(Base): __tablename__ = "settings" key: Mapped[str] = mapped_column(String(60), primary_key=True) value: Mapped[str] = mapped_column(Text, default="")