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

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

class UserMemory(Base):
  __tablename__ = "user_memory"
  
  id = Column(Integer, primary_key=True)
  user_hash = Column(String(64), unique=True, nullable=False, index=True)
  
  # Identity
  preferred_name = Column(String(100))
  language_preference = Column(String(10), default="en")
  user_type = Column(String(20))
  country_code = Column(String(5), default="KE")
  currency = Column(String(5), default="KES")
  
  # Business context
  business_name = Column(String(200))
  business_type = Column(String(50))
  employee_count = Column(Integer)
  kra_pin = Column(String(20))
  # Stored encrypted in production
  tax_regime = Column(String(30))
  # tot | paye | corporate | none
  
  # Financial profile
  monthly_income = Column(Float)
  monthly_expenses = Column(Float)
  
  # Preferences
  reporting_style = Column(String(20), default="detailed")
  file_format_preference = Column(String(10), default="xlsx")
  
  # Active workflow checkpoint
  active_workflow_json = Column(Text, nullable=True)
  
  # Metadata
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  memory_version = Column(Integer, default=1)
  
  __table_args__ = (
    Index("ix_user_memory_hash", "user_hash"),
  )

class FinancialState(Base):
  __tablename__ = "financial_state"
  
  id = Column(Integer, primary_key=True)
  user_hash = Column(String(64), unique=True, nullable=False, index=True)
  
  # Current balances
  cash_balance = Column(Float, default=0)
  savings_balance = Column(Float, default=0)
  investment_balance = Column(Float, default=0)
  total_debt = Column(Float, default=0)
  net_worth = Column(Float)
  
  # Monthly figures (last known)
  monthly_revenue = Column(Float)
  monthly_expenses_total = Column(Float)
  monthly_profit = Column(Float)
  
  # JSON fields
  debts_json = Column(Text, default="[]")
  # [{"name":"Fuliza","balance":5000,"rate":365,"monthly_payment":1500}]
  
  goals_json = Column(Text, default="[]")
  # [{"name":"Fridge","target":45000,"current":12000,"deadline":"2026-12"}]
  
  recurring_bills_json = Column(Text, default="[]")
  # [{"name":"Rent","amount":15000,"day":1,"category":"housing"}]
  
  investments_json = Column(Text, default="[]")
  # [{"name":"MMF","value":50000,"return_pct":14.5}]
  
  # Tax state
  tot_registered = Column(Boolean, default=False)
  vat_registered = Column(Boolean, default=False)
  last_tot_filed = Column(DateTime, nullable=True)
  
  # Snapshot dates
  last_pnl_date = Column(DateTime)
  last_budget_date = Column(DateTime)
  last_payroll_date = Column(DateTime)
  last_net_worth_date = Column(DateTime)
  
  updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
  
  __table_args__ = (
    Index("ix_fin_state_hash", "user_hash"),
  )

class ConversationSummary(Base):
  __tablename__ = "conversation_summaries"
  
  id = Column(Integer, primary_key=True)
  conversation_id = Column(String(36), nullable=False, index=True)
  user_hash = Column(String(64), nullable=False, index=True)
  project_id = Column(String(36), nullable=True, index=True)
  
  summary_text = Column(Text)
  numbers_mentioned = Column(Text, default="{}")
  decisions_made = Column(Text, default="[]")
  files_generated = Column(Text, default="[]")
  open_items = Column(Text, default="[]")
  
  message_count = Column(Integer, default=0)
  started_at = Column(DateTime)
  ended_at = Column(DateTime)
  compressed_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_conv_summary_user", "user_hash"),
    Index("ix_conv_summary_conv", "conversation_id"),
  )

class DomainMemory(Base):
  __tablename__ = "domain_memory"
  
  id = Column(Integer, primary_key=True)
  user_hash = Column(String(64), nullable=False, index=True)
  domain = Column(String(50), nullable=False, index=True)
  facts_json = Column(Text, default="{}")
  
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  
  __table_args__ = (
    Index("ix_domain_mem_user_domain", "user_hash", "domain"),
  )

class OrgMemory(Base):
  __tablename__ = "org_memory"
  
  id = Column(Integer, primary_key=True)
  tenant_id = Column(String(36), nullable=False, index=True)
  facts_json = Column(Text, default="{}")
  
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  
  __table_args__ = (
    Index("ix_org_mem_tenant", "tenant_id"),
  )