""" auth/rule_models.py — SQLAlchemy ORM models for DB-backed classification. Tables ------ classification_groups – Top-level expense-report sections (e.g. "Food and Groceries"). – What the mortgage team sees as report tabs/sections. classification_categories – Individual line items within a group (e.g. "Fruit & Vegetables"). – What the LLM and rule engine classify transactions into. – FK → classification_groups. classification_rules – Keyword rules for the rule engine. – FK → classification_categories. Design rationale ---------------- Groups live in the DB (not in code) so admins can rename them, add new ones, or reorder them for reports — without any code deploy. The LLM is given the flat category list (not groups). The rule engine matches against categories. Only the export/report layer needs groups. """ from __future__ import annotations from sqlalchemy import UniqueConstraint import uuid from datetime import datetime from typing import Optional from sqlalchemy import ( Boolean, Column, DateTime, Float, ForeignKey, Index, Integer, String, Text, func, ) from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.orm import relationship from auth.database import Base # ── Group ───────────────────────────────────────────────────────────────────── class ClassificationGroup(Base): """ A top-level expense-report section. Examples: "Food and Groceries", "Transport & Auto", "Children" Groups only exist for report layout — the classifier never sees them. Admins can rename, reorder, or add groups entirely through the admin API. """ __tablename__ = "classification_groups" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), unique=True, nullable=False, index=True) display_order = Column(Integer, nullable=False, default=0) # sort order in reports is_active = Column(Boolean, nullable=False, default=True) # Audit created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) updated_by = Column(String(200), nullable=True) categories = relationship( "ClassificationCategory", back_populates="group", order_by="ClassificationCategory.display_order", ) def __repr__(self) -> str: return f"" def to_dict(self) -> dict: return { "id": self.id, "name": self.name, "display_order": self.display_order, "is_active": self.is_active, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, "created_by": self.created_by, "updated_by": self.updated_by, } # ── Category ────────────────────────────────────────────────────────────────── class ClassificationCategory(Base): """ A single transaction category — the unit the classifier assigns. Examples: "Fuel", "Dentist", "Pet food" Belongs to a ClassificationGroup for report layout. The group FK is nullable so categories can exist without a group (e.g. "External Transfer", "Wages & Salary" which are excluded from reports). """ __tablename__ = "classification_categories" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), unique=True, nullable=False, index=True) # FK to the group this category belongs to. # NULL = excluded from the expense report (income, transfers, etc.) group_id = Column(String, ForeignKey("classification_groups.id", ondelete="SET NULL"), nullable=True, index=True) # Display order within the group display_order = Column(Integer, nullable=False, default=0) # Whether this category appears on the expense report. # False for: External Transfer, Internal Transfer, Wages & Salary, etc. is_reportable = Column(Boolean, nullable=False, default=True) sheet_name = Column(String(200), nullable=True) sub_column = Column(String(200), nullable=True) is_active = Column(Boolean, nullable=False, default=True) # Audit created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) updated_by = Column(String(200), nullable=True) group = relationship("ClassificationGroup", back_populates="categories") rules = relationship( "ClassificationRule", back_populates="category", cascade="all, delete-orphan", ) # ── Derived helpers ─────────────────────────────────────────────────────── @property def group_name(self) -> Optional[str]: return self.group.name if self.group else None def __repr__(self) -> str: return f"" def to_dict(self) -> dict: return { "id": self.id, "name": self.name, "group_id": self.group_id, "group_name": self.group_name, "sheet_name": self.sheet_name, "sub_column": self.sub_column, "display_order": self.display_order, "is_reportable": self.is_reportable, "is_active": self.is_active, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, "created_by": self.created_by, "updated_by": self.updated_by, } # ── Rule ────────────────────────────────────────────────────────────────────── class ClassificationRule(Base): """ A single keyword rule for the rule engine. keywords are stored as a newline-separated string (one per line). They are lowercased on write and split on read. Trailing spaces on a keyword are intentional word-boundary guards (e.g. "uber " matches "UBER EATS" but not "suburban"). """ __tablename__ = "classification_rules" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), nullable=False, index=True) category_id = Column( String, ForeignKey("classification_categories.id", ondelete="CASCADE"), nullable=False, index=True, ) # Stored as newline-separated lowercased strings keywords_raw = Column(Text, nullable=False, default="") priority = Column(Integer, nullable=False, default=10) # Which field to match against match_on = Column(String(50), nullable=False, default="description") # Valid values: "description" | "osko_reference" | "osko_purpose" # Optional: only match debit or credit transactions tx_type_filter = Column(String(20), nullable=True) # Valid values: "debit" | "credit" | NULL (match both) # Optional: only match transactions in this amount range amount_min = Column(Float, nullable=True) amount_max = Column(Float, nullable=True) is_active = Column(Boolean, nullable=False, default=True) # Audit created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) updated_by = Column(String(200), nullable=True) category = relationship("ClassificationCategory", back_populates="rules") __table_args__ = ( Index("ix_classification_rules_category_priority", "category_id", "priority"), ) # ── Keyword helpers ─────────────────────────────────────────────────────── @property def keywords(self) -> list[str]: """Return list of lowercased keyword strings (blank lines removed).""" return [kw for kw in self.keywords_raw.splitlines() if kw.strip()] @keywords.setter def keywords(self, value: list[str]) -> None: """Store keywords lowercased, preserving intentional trailing spaces.""" self.keywords_raw = "\n".join( kw.lower().lstrip() for kw in value if kw.strip() ) # ── Other helpers ───────────────────────────────────────────────────────── @property def category_name(self) -> str: return self.category.name if self.category else "" @property def group_name(self) -> Optional[str]: return self.category.group_name if self.category else None @property def amount_range(self) -> Optional[list[float]]: if self.amount_min is not None and self.amount_max is not None: return [self.amount_min, self.amount_max] return None def __repr__(self) -> str: return f"" def to_dict(self) -> dict: return { "id": self.id, "name": self.name, "category_id": self.category_id, "category": self.category_name, "group": self.group_name, "keywords": self.keywords, "priority": self.priority, "match_on": self.match_on, "tx_type_filter": self.tx_type_filter, "amount_range": self.amount_range, "is_active": self.is_active, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, "created_by": self.created_by, "updated_by": self.updated_by, } class ClassificationFlat(Base): __tablename__ = "classification_flat" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) keyword = Column(Text, nullable=False) category = Column(Text, nullable=False) sub_category = Column(Text, nullable=True) is_active = Column(Boolean, default=True, nullable=False) created_at = Column(DateTime(timezone=True), server_default=func.now()) updated_at = Column(DateTime(timezone=True), onupdate=func.now()) requires_review = Column(Boolean, nullable=False, default=False) risk_flags = Column(Text, nullable=True, default="") @property def risk_flags_list(self) -> list[str]: return [flag.strip() for flag in (self.risk_flags or "").split(",") if flag.strip()] def __repr__(self): return f"" class LearnedRuleLog(Base): """Stores auto-learned rule suggestions for human review before promotion. These records are NOT active rules and must be promoted by an admin to create a permanent `ClassificationRule` entry. """ __tablename__ = "learned_rule_logs" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) keyword = Column(String(300), nullable=False, index=True) category = Column(String(200), nullable=False) category_id = Column(String, ForeignKey("classification_categories.id", ondelete="SET NULL"), nullable=True, index=True) source = Column(String(50), nullable=False) # 'ml' | 'llm' confidence = Column(Float, nullable=True) example_description = Column(Text, nullable=True) hit_count = Column(Integer, nullable=False, default=1) status = Column(String(50), nullable=False, default="pending") # pending|approved|rejected promoted_rule_id = Column(String, nullable=True) category_obj = relationship("ClassificationCategory", foreign_keys=[category_id]) created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) updated_by = Column(String(200), nullable=True) @property def category_name(self) -> str: try: return self.category_obj.name if self.category_obj else self.category except Exception: return self.category @property def group_name(self) -> Optional[str]: try: return self.category_obj.group_name if self.category_obj else None except Exception: return None def to_dict(self) -> dict: # Parse example_description as a newline-separated list for the API examples = [ex for ex in (self.example_description or "").splitlines() if ex.strip()] return { "id": self.id, "keyword": self.keyword, "category": self.category_name, "category_id": self.category_id, "group_name": self.group_name, "source": self.source, "confidence": self.confidence, "example_description": self.example_description, "example_descriptions": examples, "hit_count": self.hit_count, "status": self.status, "promoted_rule_id": self.promoted_rule_id, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, "created_by": self.created_by, "updated_by": self.updated_by, } class BpayBiller(Base): """Lookup table for deterministic BPAY category routing.""" __tablename__ = "bpay_billers" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) biller_code = Column(String(20), nullable=False, unique=True, index=True) name = Column(String(200), nullable=False) category_id = Column( String, ForeignKey("classification_categories.id", ondelete="SET NULL"), nullable=True, index=True, ) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) updated_by = Column(String(200), nullable=True) category = relationship("ClassificationCategory") @property def category_name(self) -> Optional[str]: return self.category.name if self.category else None def __repr__(self) -> str: return f"" class RuleChangeRequest(Base): """Employee-submitted requests to add/delete/move rules, categories, or keywords. Status lifecycle: pending → approved | rejected On approval the router executes the actual DB change. """ __tablename__ = "rule_change_requests" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) requested_by = Column(String(200), nullable=False) # add_rule | delete_rule | add_category | delete_category # add_keyword | move_rule | move_category request_type = Column(String(50), nullable=False) # Source item (rule / category being added, deleted, or moved) rule_name = Column(String(200), nullable=True) category_id = Column(String, ForeignKey("classification_categories.id", ondelete="SET NULL"), nullable=True) category_name = Column(String(200), nullable=True) keywords_raw = Column(Text, nullable=True) priority = Column(Integer, nullable=True, default=5) match_on = Column(String(50), nullable=True, default="description") tx_type_filter = Column(String(20), nullable=True) target_rule_id = Column(String, nullable=True) # for delete_rule / add_keyword / move_rule category_sheet = Column(String(200), nullable=True) # Destination (for move_rule / move_category) dest_category_id = Column(String, nullable=True) dest_category_name = Column(String(200), nullable=True) dest_group_name = Column(String(200), nullable=True) reason = Column(Text, nullable=True) status = Column(String(50), nullable=False, default="pending") reviewed_by = Column(String(200), nullable=True) review_note = Column(Text, nullable=True) created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) @property def keywords(self) -> list[str]: return [k for k in (self.keywords_raw or "").splitlines() if k.strip()] def to_dict(self) -> dict: return { "id": self.id, "requested_by": self.requested_by, "request_type": self.request_type, "rule_name": self.rule_name, "category_id": self.category_id, "category_name": self.category_name, "keywords": self.keywords, "priority": self.priority, "match_on": self.match_on, "tx_type_filter": self.tx_type_filter, "target_rule_id": self.target_rule_id, "category_sheet": self.category_sheet, "dest_category_id": self.dest_category_id, "dest_category_name": self.dest_category_name, "dest_group_name": self.dest_group_name, "reason": self.reason, "status": self.status, "reviewed_by": self.reviewed_by, "review_note": self.review_note, "created_at": self.created_at.isoformat() if self.created_at else None, "updated_at": self.updated_at.isoformat() if self.updated_at else None, } class Practice(Base): """ A CA firm / mortgage practice that uses BeFree. Replaces the MOCK_PRACTICES hardcoded array on the frontend. """ __tablename__ = "practices" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), unique=True, nullable=False, index=True) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) overrides = relationship("PracticeCategoryOverride", back_populates="practice") def to_dict(self) -> dict: return { "id": self.id, "name": self.name, "is_active": self.is_active, "created_at": self.created_at.isoformat() if self.created_at else None, } class PracticeCategoryOverride(Base): """ Per-practice override: moves a category into a different group at classification time, without touching global category data. override_group_id = NULL means exclude from report entirely. """ __tablename__ = "practice_category_overrides" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) practice_id = Column(String, ForeignKey("practices.id", ondelete="CASCADE"), nullable=False, index=True) category_id = Column(String, ForeignKey("classification_categories.id", ondelete="CASCADE"), nullable=False, index=True) override_group_id = Column(String, ForeignKey("classification_groups.id", ondelete="SET NULL"), nullable=True) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime, nullable=False, default=datetime.utcnow) updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(200), nullable=True) practice = relationship("Practice", back_populates="overrides") category = relationship("ClassificationCategory") override_group = relationship("ClassificationGroup") __table_args__ = ( UniqueConstraint("practice_id", "category_id", name="uq_practice_category_override"), ) def to_dict(self) -> dict: return { "id": self.id, "practice_id": self.practice_id, "practice_name": self.practice.name if self.practice else None, "category_id": self.category_id, "category_name": self.category.name if self.category else None, "override_group_id": self.override_group_id, "override_group": self.override_group.name if self.override_group else None, "is_active": self.is_active, }