Spaces:
Sleeping
Sleeping
File size: 7,562 Bytes
4050c8c af01fe3 4050c8c af01fe3 4050c8c af01fe3 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | """ORM models for the rent-roll domain."""
from __future__ import annotations
from datetime import date, datetime
from sqlalchemy import (
String, Integer, Float, Date, Boolean, ForeignKey, JSON, Index, DateTime, Text
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
# Use JSONB on Postgres for indexed `->`/`->>` access on raw_row; fall back
# to generic JSON on other dialects (SQLite tests, MySQL, etc.).
JSON_VARIANT = JSON().with_variant(JSONB(), "postgresql")
class Base(DeclarativeBase):
pass
class Property(Base):
__tablename__ = "properties"
property_code: Mapped[str] = mapped_column(String(32), primary_key=True)
property_name: Mapped[str] = mapped_column(String(255))
property_type: Mapped[str] = mapped_column(String(32)) # r / a / c / land / other
address: Mapped[str | None] = mapped_column(String(255), nullable=True)
units: Mapped[list["Unit"]] = relationship(back_populates="property", cascade="all, delete-orphan")
leases: Mapped[list["Lease"]] = relationship(back_populates="property", cascade="all, delete-orphan")
snapshots: Mapped[list["RentSnapshot"]] = relationship(back_populates="property", cascade="all, delete-orphan")
class Unit(Base):
__tablename__ = "units"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
property_code: Mapped[str] = mapped_column(String(32), ForeignKey("properties.property_code"), index=True)
unit_number: Mapped[str] = mapped_column(String(64))
unit_type: Mapped[str | None] = mapped_column(String(64), nullable=True)
bedrooms: Mapped[float | None] = mapped_column(Float, nullable=True)
bathrooms: Mapped[float | None] = mapped_column(Float, nullable=True)
sqft: Mapped[float | None] = mapped_column(Float, nullable=True)
market_rent: Mapped[float | None] = mapped_column(Float, nullable=True)
property: Mapped[Property] = relationship(back_populates="units")
__table_args__ = (
Index("ix_units_code_unit", "property_code", "unit_number", unique=True),
)
class Lease(Base):
__tablename__ = "leases"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
property_code: Mapped[str] = mapped_column(String(32), ForeignKey("properties.property_code"), index=True)
unit_number: Mapped[str] = mapped_column(String(64))
tenant_id: Mapped[str | None] = mapped_column(String(64), nullable=True) # already redacted upstream
lease_start: Mapped[date | None] = mapped_column(Date, nullable=True)
lease_end: Mapped[date | None] = mapped_column(Date, nullable=True)
monthly_rent: Mapped[float | None] = mapped_column(Float, nullable=True)
balance: Mapped[float | None] = mapped_column(Float, nullable=True)
status: Mapped[str | None] = mapped_column(String(32), nullable=True)
# v4 additions — were silently dropped before:
resident_deposit: Mapped[float | None] = mapped_column(Float, nullable=True)
other_deposit: Mapped[float | None] = mapped_column(Float, nullable=True)
move_out_date: Mapped[date | None] = mapped_column(Date, nullable=True)
property: Mapped[Property] = relationship(back_populates="leases")
class RentSnapshot(Base):
"""One row per (property, unit, month). Powers time-series queries."""
__tablename__ = "rent_snapshots"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
property_code: Mapped[str] = mapped_column(String(32), ForeignKey("properties.property_code"), index=True)
snapshot_month: Mapped[date] = mapped_column(Date, index=True)
unit_number: Mapped[str] = mapped_column(String(64))
monthly_rent: Mapped[float | None] = mapped_column(Float, nullable=True)
occupied: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
raw_row: Mapped[dict | None] = mapped_column(JSON_VARIANT, nullable=True)
property: Mapped[Property] = relationship(back_populates="snapshots")
charge_lines: Mapped[list["RentChargeLine"]] = relationship(back_populates="snapshot", cascade="all, delete-orphan")
__table_args__ = (
Index("ix_snap_code_month_unit", "property_code", "snapshot_month", "unit_number"),
)
class RentChargeLine(Base):
"""One row per charge line within a unit block.
Preserves the granularity that `RentSnapshot.raw_row.charges` lost — that
dict sums charges per code, so two PARKING lines of $75 and $100 collapsed
into a single $175. This table keeps every line item with its position
(`line_index`) so the agent can answer "list the individual parking fees".
"""
__tablename__ = "rent_charge_lines"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
snapshot_id: Mapped[int] = mapped_column(Integer, ForeignKey("rent_snapshots.id", ondelete="CASCADE"), index=True)
property_code: Mapped[str] = mapped_column(String(32), ForeignKey("properties.property_code"), index=True)
snapshot_month: Mapped[date] = mapped_column(Date, index=True)
unit_number: Mapped[str] = mapped_column(String(64))
line_index: Mapped[int] = mapped_column(Integer)
charge_code: Mapped[str] = mapped_column(String(32), index=True)
amount: Mapped[float | None] = mapped_column(Float, nullable=True)
snapshot: Mapped["RentSnapshot"] = relationship(back_populates="charge_lines")
__table_args__ = (
Index("ix_charge_code_month_unit", "property_code", "snapshot_month", "unit_number"),
)
# ---------------------------------------------------------------------------
# Evaluation harness — persisted alongside the rent-roll schema in Supabase
# so the Monitoring UI can read run history without a separate datastore.
# ---------------------------------------------------------------------------
class EvalRun(Base):
__tablename__ = "eval_runs"
id: Mapped[str] = mapped_column(String(64), primary_key=True)
started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
trigger: Mapped[str] = mapped_column(String(32)) # manual | scheduled | cli
status: Mapped[str] = mapped_column(String(16)) # running | completed | failed
summary: Mapped[dict | None] = mapped_column(JSON_VARIANT, nullable=True)
cases: Mapped[list["EvalCase"]] = relationship(
back_populates="run", cascade="all, delete-orphan",
)
class EvalCase(Base):
__tablename__ = "eval_cases"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
run_id: Mapped[str] = mapped_column(String(64), ForeignKey("eval_runs.id", ondelete="CASCADE"), index=True)
golden_id: Mapped[str] = mapped_column(String(128))
property_code: Mapped[str | None] = mapped_column(String(32), nullable=True)
question: Mapped[str] = mapped_column(Text)
answer: Mapped[str | None] = mapped_column(Text, nullable=True)
scores: Mapped[dict | None] = mapped_column(JSON_VARIANT, nullable=True)
ok: Mapped[bool] = mapped_column(Boolean, default=False)
error: Mapped[str | None] = mapped_column(Text, nullable=True)
duration_ms: Mapped[int | None] = mapped_column(Integer, nullable=True)
trace_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
run: Mapped["EvalRun"] = relationship(back_populates="cases")
|