257 lines
14 KiB
Python
257 lines
14 KiB
Python
|
from datetime import datetime, timezone
|
||
|
|
from decimal import Decimal
|
||
|
|
|
||
|
|
from sqlalchemy import (JSON, BigInteger, Boolean, DateTime, ForeignKey, Index, Integer, LargeBinary, Numeric, String, Text,
|
||
|
|
UniqueConstraint, false)
|
||
|
|
from sqlalchemy import text
|
||
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
||
|
|
|
||
|
|
from .db import Base
|
||
|
|
|
||
|
|
Money = Numeric(18, 6)
|
||
|
|
|
||
|
|
|
||
|
|
def now() -> datetime:
|
||
|
|
return datetime.now(timezone.utc)
|
||
|
|
|
||
|
|
|
||
|
|
class Department(Base):
|
||
|
|
__tablename__ = "departments"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
name: Mapped[str] = mapped_column(String(120), unique=True)
|
||
|
|
active: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now)
|
||
|
|
|
||
|
|
steps: Mapped[list["WorkflowStep"]] = relationship(
|
||
|
|
back_populates="department", cascade="all, delete-orphan", order_by="WorkflowStep.position")
|
||
|
|
|
||
|
|
|
||
|
|
class WorkflowStep(Base):
|
||
|
|
"""One approval step of a department's workflow. Steps run in `position` order after submission."""
|
||
|
|
__tablename__ = "workflow_steps"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
department_id: Mapped[int] = mapped_column(ForeignKey("departments.id", ondelete="CASCADE"), index=True)
|
||
|
|
position: Mapped[int] = mapped_column(Integer, default=0)
|
||
|
|
name: Mapped[str] = mapped_column(String(120))
|
||
|
|
# role: any approver (or admin-role member) of the department; users: only the listed users
|
||
|
|
approver_mode: Mapped[str] = mapped_column(String(10), default="role")
|
||
|
|
approver_ids: Mapped[list | None] = mapped_column(JSON, nullable=True)
|
||
|
|
|
||
|
|
department: Mapped[Department] = relationship(back_populates="steps")
|
||
|
|
|
||
|
|
|
||
|
|
class User(Base):
|
||
|
|
__tablename__ = "users"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
|
||
|
|
name: Mapped[str] = mapped_column(String(255))
|
||
|
|
password_hash: Mapped[str] = mapped_column(String(255))
|
||
|
|
# admin: everything; approver: edit + approve own department; editor: edit own department; viewer: read
|
||
|
|
role: Mapped[str] = mapped_column(String(20), default="viewer")
|
||
|
|
department_id: Mapped[int | None] = mapped_column(ForeignKey("departments.id"), nullable=True)
|
||
|
|
language: Mapped[str] = mapped_column(String(5), default="pt")
|
||
|
|
active: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now)
|
||
|
|
# light, dark, or system (follow the computer's setting)
|
||
|
|
theme: Mapped[str] = mapped_column(String(10), default="system", server_default="system")
|
||
|
|
# MFA with an authenticator app (TOTP). Secrets are stored encrypted; recovery codes as SHA-256 hashes.
|
||
|
|
mfa_enabled: Mapped[bool] = mapped_column(Boolean, default=False, server_default=false())
|
||
|
|
mfa_secret: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
||
|
|
mfa_pending_secret: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
||
|
|
mfa_enabled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
mfa_last_step: Mapped[int | None] = mapped_column(BigInteger, nullable=True) # a code works only once
|
||
|
|
mfa_recovery: Mapped[list | None] = mapped_column(JSON, nullable=True)
|
||
|
|
mfa_failed: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
|
||
|
|
mfa_locked_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
# profile picture, resized to at most 256 px
|
||
|
|
avatar: Mapped[bytes | None] = mapped_column(LargeBinary, nullable=True, deferred=True)
|
||
|
|
avatar_type: Mapped[str | None] = mapped_column(String(30), nullable=True)
|
||
|
|
avatar_updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
|
||
|
|
department: Mapped[Department | None] = relationship()
|
||
|
|
|
||
|
|
|
||
|
|
class Site(Base):
|
||
|
|
__tablename__ = "sites"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
name: Mapped[str] = mapped_column(String(120), unique=True)
|
||
|
|
active: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
|
||
|
|
|
||
|
|
class Category(Base):
|
||
|
|
__tablename__ = "categories"
|
||
|
|
__table_args__ = (UniqueConstraint("department_id", "name_en", name="uq_categories_department_name"),)
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
department_id: Mapped[int] = mapped_column(ForeignKey("departments.id", ondelete="CASCADE"), index=True)
|
||
|
|
name_en: Mapped[str] = mapped_column(String(200))
|
||
|
|
name_pt: Mapped[str] = mapped_column(String(200), default="")
|
||
|
|
# revenue lines are shown in the budget but left out of the cost report
|
||
|
|
section: Mapped[str] = mapped_column(String(20), default="cost")
|
||
|
|
sort_order: Mapped[int] = mapped_column(Integer, default=0)
|
||
|
|
show_when_empty: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
active: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
|
||
|
|
|
||
|
|
class BudgetYear(Base):
|
||
|
|
__tablename__ = "budget_years"
|
||
|
|
# One current budget per department and year; replaced versions are archived next to it.
|
||
|
|
__table_args__ = (Index("uq_budget_years_current", "department_id", "year", unique=True,
|
||
|
|
postgresql_where=text("NOT archived"), sqlite_where=text("NOT archived")),)
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
department_id: Mapped[int] = mapped_column(ForeignKey("departments.id"), index=True)
|
||
|
|
year: Mapped[int] = mapped_column(Integer)
|
||
|
|
title: Mapped[str] = mapped_column(String(200), default="")
|
||
|
|
default_rate: Mapped[Decimal] = mapped_column(Numeric(9, 6), default=Decimal("0"))
|
||
|
|
# draft -> in_review (one round per workflow step) -> approved; only drafts can be edited
|
||
|
|
status: Mapped[str] = mapped_column(String(20), default="draft")
|
||
|
|
current_step: Mapped[int | None] = mapped_column(Integer, nullable=True)
|
||
|
|
notes: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
submitted_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
submitted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
approved_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now)
|
||
|
|
# the Excel file the budget was last loaded from, if any
|
||
|
|
source_file: Mapped[str] = mapped_column(String(255), default="", server_default="")
|
||
|
|
# an archived budget was replaced by an upload: kept for reference, read-only
|
||
|
|
archived: Mapped[bool] = mapped_column(Boolean, default=False, server_default=false())
|
||
|
|
archived_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
archived_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
|
||
|
|
department: Mapped[Department] = relationship()
|
||
|
|
lines: Mapped[list["CostLine"]] = relationship(back_populates="budget_year", cascade="all, delete-orphan")
|
||
|
|
submitted_by: Mapped[User | None] = relationship(foreign_keys=[submitted_by_id])
|
||
|
|
approved_by: Mapped[User | None] = relationship(foreign_keys=[approved_by_id])
|
||
|
|
archived_by: Mapped[User | None] = relationship(foreign_keys=[archived_by_id])
|
||
|
|
reopen_requests: Mapped[list["ReopenRequest"]] = relationship(
|
||
|
|
back_populates="budget_year", cascade="all, delete-orphan", order_by="ReopenRequest.id")
|
||
|
|
|
||
|
|
|
||
|
|
class CostLine(Base):
|
||
|
|
__tablename__ = "cost_lines"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
year_id: Mapped[int] = mapped_column(ForeignKey("budget_years.id", ondelete="CASCADE"), index=True)
|
||
|
|
category_id: Mapped[int] = mapped_column(ForeignKey("categories.id"))
|
||
|
|
site_id: Mapped[int | None] = mapped_column(ForeignKey("sites.id"), nullable=True)
|
||
|
|
name: Mapped[str] = mapped_column(String(255))
|
||
|
|
description: Mapped[str] = mapped_column(String(500), default="")
|
||
|
|
remarks: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
# amounts may be typed as arithmetic, e.g. "3292.56+(12*839.67)"; the evaluated value is stored next to it
|
||
|
|
prior_expr: Mapped[str] = mapped_column(String(500), default="0")
|
||
|
|
prior_actual: Mapped[Decimal] = mapped_column(Money, default=Decimal("0"))
|
||
|
|
base_expr: Mapped[str] = mapped_column(String(500), default="0")
|
||
|
|
base_amount: Mapped[Decimal] = mapped_column(Money, default=Decimal("0"))
|
||
|
|
# year_rate: base * (1 + year rate); percent: base * (1 + value/100); factor: base * value; none: base
|
||
|
|
adj_kind: Mapped[str] = mapped_column(String(20), default="year_rate")
|
||
|
|
adj_value: Mapped[Decimal] = mapped_column(Numeric(12, 6), default=Decimal("0"))
|
||
|
|
adj_reason: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
# even: adjusted / 12; manual: twelve typed amounts, whose sum is the budget
|
||
|
|
monthly_kind: Mapped[str] = mapped_column(String(10), default="even")
|
||
|
|
monthly_values: Mapped[list | None] = mapped_column(JSON, nullable=True)
|
||
|
|
# monthly_kind "selected": the adjusted amount is split equally over these months (1 = January)
|
||
|
|
monthly_months: Mapped[list | None] = mapped_column(JSON, nullable=True)
|
||
|
|
sort_order: Mapped[int] = mapped_column(Integer, default=0)
|
||
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now, onupdate=now)
|
||
|
|
|
||
|
|
budget_year: Mapped[BudgetYear] = relationship(back_populates="lines")
|
||
|
|
category: Mapped[Category] = relationship()
|
||
|
|
site: Mapped[Site | None] = relationship()
|
||
|
|
|
||
|
|
|
||
|
|
class AuditEntry(Base):
|
||
|
|
__tablename__ = "audit_log"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now, index=True)
|
||
|
|
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
year_id: Mapped[int | None] = mapped_column(Integer, nullable=True, index=True)
|
||
|
|
entity: Mapped[str] = mapped_column(String(40))
|
||
|
|
entity_id: Mapped[int | None] = mapped_column(Integer, nullable=True)
|
||
|
|
action: Mapped[str] = mapped_column(String(40))
|
||
|
|
summary: Mapped[str] = mapped_column(String(500), default="")
|
||
|
|
changes: Mapped[dict | None] = mapped_column(JSON, nullable=True)
|
||
|
|
|
||
|
|
user: Mapped[User | None] = relationship()
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
class MailSettings(Base):
|
||
|
|
"""The one mail account the app sends from (row id 1). Passwords are stored encrypted."""
|
||
|
|
__tablename__ = "mail_settings"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
enabled: Mapped[bool] = mapped_column(Boolean, default=False)
|
||
|
|
smtp_host: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
smtp_port: Mapped[int] = mapped_column(Integer, default=587)
|
||
|
|
smtp_security: Mapped[str] = mapped_column(String(10), default="starttls") # starttls | ssl | none
|
||
|
|
smtp_username: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
smtp_password: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
from_address: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
from_name: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
reply_to: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
signature: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
# copy of every sent message in the mailbox's Sent folder, over IMAP
|
||
|
|
save_to_sent: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
imap_host: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
imap_port: Mapped[int] = mapped_column(Integer, default=993)
|
||
|
|
imap_security: Mapped[str] = mapped_column(String(10), default="ssl")
|
||
|
|
imap_same_login: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
|
imap_username: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
imap_password: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
sent_folder: Mapped[str] = mapped_column(String(255), default="") # empty: find it automatically
|
||
|
|
updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
updated_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
|
||
|
|
updated_by: Mapped[User | None] = relationship()
|
||
|
|
|
||
|
|
|
||
|
|
class MailLog(Base):
|
||
|
|
"""One message the app tried to send: who, to whom, what, and how it went."""
|
||
|
|
__tablename__ = "mail_log"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now, index=True)
|
||
|
|
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
kind: Mapped[str] = mapped_column(String(20), default="report") # report | test
|
||
|
|
year_id: Mapped[int | None] = mapped_column(Integer, nullable=True, index=True)
|
||
|
|
context: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
sender: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
recipients: Mapped[dict] = mapped_column(JSON, default=dict) # {"to": [...], "cc": [...]}
|
||
|
|
subject: Mapped[str] = mapped_column(String(500), default="")
|
||
|
|
body: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
attachments: Mapped[list] = mapped_column(JSON, default=list) # [{"name": ..., "size": ...}]
|
||
|
|
status: Mapped[str] = mapped_column(String(20), default="sent") # sent | failed
|
||
|
|
error: Mapped[str] = mapped_column(String(1000), default="")
|
||
|
|
sent_copy: Mapped[str] = mapped_column(String(20), default="off") # saved | failed | off
|
||
|
|
sent_copy_detail: Mapped[str] = mapped_column(String(500), default="")
|
||
|
|
message_id: Mapped[str] = mapped_column(String(255), default="")
|
||
|
|
|
||
|
|
user: Mapped[User | None] = relationship()
|
||
|
|
|
||
|
|
|
||
|
|
class ReopenRequest(Base):
|
||
|
|
"""A user asks for an approved budget to be reopened, saying why; an approver accepts or declines."""
|
||
|
|
__tablename__ = "reopen_requests"
|
||
|
|
|
||
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
||
|
|
year_id: Mapped[int] = mapped_column(ForeignKey("budget_years.id", ondelete="CASCADE"), index=True)
|
||
|
|
requested_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now)
|
||
|
|
reason: Mapped[str] = mapped_column(Text)
|
||
|
|
status: Mapped[str] = mapped_column(String(20), default="pending") # pending | accepted | declined
|
||
|
|
decided_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
|
||
|
|
decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
|
||
|
|
decision_note: Mapped[str] = mapped_column(Text, default="")
|
||
|
|
|
||
|
|
budget_year: Mapped[BudgetYear] = relationship(back_populates="reopen_requests")
|
||
|
|
requested_by: Mapped[User | None] = relationship(foreign_keys=[requested_by_id])
|
||
|
|
decided_by: Mapped[User | None] = relationship(foreign_keys=[decided_by_id])
|