senti/backend/database/postgres/models/finance.py

from sqlalchemy import (
  Column, String, Boolean, DateTime, Integer,
  Float, Text, Date, Index
)
from sqlalchemy.sql import func
from .base import Base

class UserGoal(Base):
  __tablename__ = "user_goals"
  
  id = Column(Integer, primary_key=True)
  goal_id = Column(String(36), unique=True, nullable=False, index=True)
  user_hash = Column(String(64), nullable=False, index=True)
  
  name = Column(String(200), nullable=False)
  goal_type = Column(String(30))
  # savings | debt_payoff | investment | expense | emergency_fund
  
  target_amount = Column(Float, nullable=False)
  current_amount = Column(Float, default=0)
  
  target_date = Column(Date, nullable=True)
  monthly_contribution = Column(Float)
  
  status = Column(String(20), default="active")
  priority = Column(Integer, default=1)
  # 1=low, 2=medium, 3=high
  
  notes = Column(Text)
  linked_project_id = Column(String(36), nullable=True)
  
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  completed_at = Column(DateTime, nullable=True)
  
  __table_args__ = (
    Index("ix_goals_user", "user_hash"),
    Index("ix_goals_status", "status"),
  )

class RecurringBill(Base):
  __tablename__ = "recurring_bills"
  
  id = Column(Integer, primary_key=True)
  bill_id = Column(String(36), unique=True, nullable=False, index=True)
  user_hash = Column(String(64), nullable=False, index=True)
  
  name = Column(String(200), nullable=False)
  amount = Column(Float, nullable=False)
  currency = Column(String(5), default="KES")
  
  frequency = Column(String(20), default="monthly")
  # monthly | weekly | quarterly | annual
  
  day_of_month = Column(Integer)
  # 1-31, which day to pay
  
  category = Column(String(50))
  # rent | utilities | insurance | loan | subscription | other
  
  is_active = Column(Boolean, default=True)
  last_paid_date = Column(Date, nullable=True)
  next_due_date = Column(Date, nullable=True)
  
  created_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_bills_user", "user_hash"),
    Index("ix_bills_next_due", "next_due_date"),
  )

class DebtRecord(Base):
  __tablename__ = "debt_records"
  
  id = Column(Integer, primary_key=True)
  debt_id = Column(String(36), unique=True, nullable=False, index=True)
  user_hash = Column(String(64), nullable=False, index=True)
  
  name = Column(String(200), nullable=False)
  # "Fuliza" | "SACCO Loan" | "Bank Loan"
  
  debt_type = Column(String(30))
  # mobile_loan | sacco | bank | hire_purchase | personal | other
  
  original_amount = Column(Float)
  current_balance = Column(Float, nullable=False)
  interest_rate_annual = Column(Float)
  monthly_payment = Column(Float)
  
  # For payoff planning
  payoff_strategy = Column(String(20), default="minimum")
  # minimum | avalanche | snowball
  
  start_date = Column(Date)
  expected_payoff_date = Column(Date, nullable=True)
  
  is_active = Column(Boolean, default=True)
  
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  
  __table_args__ = (
    Index("ix_debts_user", "user_hash"),
    Index("ix_debts_active", "is_active"),
  )