Spaces:
Runtime error
Runtime error
File size: 5,221 Bytes
b12d042 | 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 | import uuid
from datetime import datetime
from pgvector.sqlalchemy import Vector
from sqlalchemy import (
CheckConstraint,
DateTime,
Float,
ForeignKey,
Index,
PrimaryKeyConstraint,
String,
Text,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func
from app.config import get_settings
from app.db import Base
_dim = get_settings().embedding_dim
class LostDog(Base):
"""A registered missing-dog cluster: name + last-seen + 1..N reference photos.
Carries a small state machine on top of the cluster:
- ``status`` flips from ``looking`` (default) to ``found`` (owner action)
or ``archived`` (system, after 6 months of no owner activity). Archived
dogs drop out of /search results.
- ``last_owner_visit_at`` is bumped on every owner read/write; drives
both the "what's new since last visit" UI and the inactivity job.
- ``inactivity_email_sent_at`` records the 5-month "are you still
looking?" nudge so we don't re-send.
- ``archived_at`` is stamped by the inactivity job when the dog flips
to ``archived`` after another 30 days of silence post-nudge.
"""
__tablename__ = "lost_dogs"
__table_args__ = (
CheckConstraint(
"status IN ('looking', 'found', 'archived')",
name="lost_dogs_status_check",
),
Index("lost_dogs_status_visit_idx", "status", "last_owner_visit_at"),
)
id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
)
name: Mapped[str | None] = mapped_column(String(80), nullable=True)
last_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), nullable=False
)
last_seen_lat: Mapped[float] = mapped_column(Float, nullable=False)
last_seen_lng: Mapped[float] = mapped_column(Float, nullable=False)
contact_name: Mapped[str | None] = mapped_column(String(80), nullable=True)
contact_email: Mapped[str | None] = mapped_column(String(200), nullable=True)
contact_phone: Mapped[str | None] = mapped_column(String(40), nullable=True)
status: Mapped[str] = mapped_column(
String(16), nullable=False, server_default="looking", default="looking"
)
last_owner_visit_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
inactivity_email_sent_at: Mapped[datetime | None] = mapped_column(
DateTime(timezone=True), nullable=True
)
archived_at: Mapped[datetime | None] = mapped_column(
DateTime(timezone=True), nullable=True
)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
photos: Mapped[list["Sighting"]] = relationship(
back_populates="lost_dog",
cascade="all, delete-orphan",
)
class Sighting(Base):
__tablename__ = "sightings"
id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
)
image_url: Mapped[str] = mapped_column(Text, nullable=False)
cropped_url: Mapped[str] = mapped_column(Text, nullable=False)
embedding: Mapped[list[float]] = mapped_column(Vector(_dim), nullable=False)
latitude: Mapped[float] = mapped_column(Float, nullable=False)
longitude: Mapped[float] = mapped_column(Float, nullable=False)
sighted_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
notes: Mapped[str | None] = mapped_column(Text, nullable=True)
source: Mapped[str] = mapped_column(String(16), nullable=False, default="user")
identity: Mapped[str | None] = mapped_column(String(64), nullable=True)
lost_dog_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True),
ForeignKey("lost_dogs.id", ondelete="CASCADE"),
nullable=True,
)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
lost_dog: Mapped[LostDog | None] = relationship(back_populates="photos")
class SightingRejection(Base):
"""Owner said 'not my dog' on a possible spotting.
Composite PK on (lost_dog_id, sighting_id) makes inserts idempotent and
keeps the LEFT JOIN anti-join filter on possible-sightings cheap.
"""
__tablename__ = "sighting_rejections"
__table_args__ = (
PrimaryKeyConstraint("lost_dog_id", "sighting_id"),
Index("sighting_rejections_sighting_idx", "sighting_id"),
)
lost_dog_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("lost_dogs.id", ondelete="CASCADE"),
nullable=False,
)
sighting_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("sightings.id", ondelete="CASCADE"),
nullable=False,
)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
|