Spaces:
Sleeping
Sleeping
Download auth/rule_models.py from Coeinterns/bank-statement-api: direct link, hf CLI and curl.
- Browser
- Download file 22.3 kB
-
https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/auth/rule_models.py
- Command line
-
hf download hf://spaces/Coeinterns/bank-statement-api/auth/rule_models.py
-
curl -L -o rule_models.py https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/auth/rule_models.py
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 βββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| 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 βββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| 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()] | |
| 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 βββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| def category_name(self) -> str: | |
| return self.category.name if self.category else "" | |
| def group_name(self) -> Optional[str]: | |
| return self.category.group_name if self.category else None | |
| 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="") | |
| 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) | |
| def category_name(self) -> str: | |
| try: | |
| return self.category_obj.name if self.category_obj else self.category | |
| except Exception: | |
| return self.category | |
| 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") | |
| 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) | |
| 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, | |
| } |