bank-statement-api / auth /rule_models.py
pratik-befree's picture
practices added for the classification
c58f0d0
Raw History Blame Contribute Delete
22.3 kB
"""
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"<Group name={self.name}>"
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"<Category name={self.name} group={self.group_name}>"
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"<Rule name={self.name} category={self.category_name} priority={self.priority}>"
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"<ClassificationFlat keyword={self.keyword!r} category={self.category!r} sub_category={self.sub_category!r}>"
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"<BpayBiller code={self.biller_code} name={self.name} category={self.category_name!r}>"
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,
}