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

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

class Tenant(Base):
  """Institutional client (SACCO, bank, MFI)."""
  __tablename__ = "tenants"
  
  id = Column(Integer, primary_key=True)
  tenant_id = Column(String(36), unique=True, nullable=False, index=True)
  
  name = Column(String(300), nullable=False)
  # "Stima SACCO" | "KCB Group"
  
  tenant_type = Column(String(30))
  # sacco | bank | mfi | fintech | enterprise
  
  country_code = Column(String(5), default="KE")
  
  # Subscription
  plan = Column(String(30), default="pilot")
  # pilot | standard | enterprise
  
  # Limits
  max_users = Column(Integer, default=10)
  max_api_calls_per_day = Column(Integer, default=10000)
  
  # Config
  custom_rules = Column(Text, default="{}")
  # JSON: institution-specific business rules
  
  # Status
  is_active = Column(Boolean, default=True)
  
  created_at = Column(DateTime, server_default=func.now())
  contract_start = Column(DateTime, nullable=True)
  contract_end = Column(DateTime, nullable=True)
  
  __table_args__ = (
    Index("ix_tenants_id", "tenant_id"),
  )

class TenantUser(Base):
  """User within an institutional tenant."""
  __tablename__ = "tenant_users"
  
  id = Column(Integer, primary_key=True)
  user_hash = Column(String(64), nullable=False, index=True)
  tenant_id = Column(String(36), nullable=False, index=True)
  
  role = Column(String(30), nullable=False)
  # super_admin | risk_manager | auditor | compliance | branch_manager
  
  branch = Column(String(100), nullable=True)
  
  # RBAC permissions
  can_approve = Column(Boolean, default=False)
  can_reject = Column(Boolean, default=False)
  can_export = Column(Boolean, default=False)
  can_configure = Column(Boolean, default=False)
  can_view_all = Column(Boolean, default=False)
  
  is_active = Column(Boolean, default=True)
  created_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_tenant_users_user", "user_hash"),
    Index("ix_tenant_users_tenant", "tenant_id"),
    Index("ix_tenant_users_role", "role"),
  )

class ApprovalQueue(Base):
  __tablename__ = "approval_queue"
  
  id = Column(Integer, primary_key=True)
  item_id = Column(String(36), unique=True, nullable=False, index=True)
  tenant_id = Column(String(36), nullable=False, index=True)
  
  requester_hash = Column(String(64), nullable=False)
  requester_name = Column(String(200))
  
  # What needs approval
  action_type = Column(String(50))
  # loan | credit_limit | account_closure | large_transfer | kyc_override
  
  amount = Column(Float, nullable=True)
  currency = Column(String(5), default="KES")
  description = Column(Text)
  
  # Risk assessment
  risk_score = Column(Float)
  risk_tier = Column(String(20))
  # PRIME | GOOD | FAIR | SUBPRIME | FLAGGED
  
  # AI reasoning
  ai_recommendation = Column(String(20))
  # APPROVE | REVIEW | REJECT
  ai_reasoning = Column(Text)
  ai_confidence = Column(Float)
  
  # Decision
  status = Column(String(20), default="pending")
  # pending | approved | rejected | escalated | expired
  
  decided_by_hash = Column(String(64), nullable=True)
  decision_reason = Column(Text, nullable=True)
  decided_at = Column(DateTime, nullable=True)
  
  created_at = Column(DateTime, server_default=func.now())
  expires_at = Column(DateTime)
  
  __table_args__ = (
    Index("ix_approvals_tenant", "tenant_id"),
    Index("ix_approvals_status", "status"),
    Index("ix_approvals_created", "created_at"),
  )

class RiskFlag(Base):
  __tablename__ = "risk_flags"
  
  id = Column(Integer, primary_key=True)
  flag_id = Column(String(36), unique=True, nullable=False, index=True)
  tenant_id = Column(String(36), nullable=False, index=True)
  
  member_id = Column(String(100), nullable=False, index=True)
  member_name = Column(String(200))
  
  flag_type = Column(String(50))
  # aml_pattern | unusual_activity | multiple_accounts |
  # velocity_breach | sanctions_match | fraud_indicator
  
  severity = Column(String(20))
  # LOW | MEDIUM | HIGH | CRITICAL
  
  description = Column(Text)
  evidence = Column(Text, default="{}")
  # JSON: supporting evidence
  
  # Status
  status = Column(String(20), default="open")
  # open | under_review | resolved | false_positive
  
  resolved_by = Column(String(64), nullable=True)
  resolution_notes = Column(Text, nullable=True)
  resolved_at = Column(DateTime, nullable=True)
  
  flagged_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_flags_tenant", "tenant_id"),
    Index("ix_flags_status", "status"),
    Index("ix_flags_severity", "severity"),
    Index("ix_flags_member", "member_id"),
  )

class Loan(Base):
  """Institutional loan record for member tracking."""
  __tablename__ = "loans"
  
  id = Column(Integer, primary_key=True)
  loan_id = Column(String(36), unique=True, nullable=False, index=True)
  tenant_id = Column(String(36), nullable=False, index=True)
  member_id = Column(String(100), nullable=False, index=True)
  
  amount = Column(Float, nullable=False)
  status = Column(String(30), default="active")
  # active | cleared | in_arrears | defaulted | restructured
  
  days_overdue = Column(Integer, default=0)
  interest_rate = Column(Float)
  
  created_at = Column(DateTime, server_default=func.now())
  due_date = Column(DateTime)
  
  __table_args__ = (
    Index("ix_loans_tenant", "tenant_id"),
    Index("ix_loans_member", "member_id"),
    Index("ix_loans_status", "status"),
    Index("ix_loans_overdue", "days_overdue"),
  )