app/db/models.py (view raw)
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 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
from __future__ import annotations
from datetime import datetime
from sqlalchemy import (
Boolean,
DateTime,
Float,
ForeignKey,
Index,
Integer,
String,
Text,
UniqueConstraint,
func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Tracker(Base):
__tablename__ = "trackers"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
canonical_url: Mapped[str] = mapped_column(String(512), unique=True, nullable=False)
scheme: Mapped[str] = mapped_column(String(16), nullable=False)
hostname: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
port: Mapped[int] = mapped_column(Integer, nullable=False)
path: Mapped[str] = mapped_column(String(512), nullable=False, default="/announce")
current_status: Mapped[str] = mapped_column(
String(32), nullable=False, default="unknown", index=True
)
first_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
last_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
last_checked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
next_check_at: Mapped[datetime | None] = mapped_column(
DateTime(timezone=True), nullable=True, index=True
)
announced_interval_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)
last_latency_ms: Mapped[float | None] = mapped_column(Float, nullable=True)
supports_ipv4: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
supports_ipv6: Mapped[str | None] = mapped_column(String(32), nullable=True)
# supports_ipv6 stores: true / false / not_tested / unknown as string for flexibility
terminal_cname: Mapped[str | None] = mapped_column(String(255), nullable=True)
infrastructure_fingerprint: Mapped[str | None] = mapped_column(
String(128), nullable=True, index=True
)
asn: Mapped[int | None] = mapped_column(Integer, nullable=True)
network_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
country_code: Mapped[str | None] = mapped_column(String(8), nullable=True)
consecutive_failures: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
updated_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True),
server_default=func.now(),
onupdate=func.now(),
nullable=False,
)
sources: Mapped[list[TrackerSource]] = relationship(back_populates="tracker")
probe_results: Mapped[list[ProbeResult]] = relationship(back_populates="tracker")
dns_records: Mapped[list[DnsRecord]] = relationship(back_populates="tracker")
class TrackerSource(Base):
__tablename__ = "tracker_sources"
__table_args__ = (UniqueConstraint("tracker_id", "source_name", name="uq_tracker_source"),)
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
tracker_id: Mapped[int] = mapped_column(
ForeignKey("trackers.id", ondelete="CASCADE"), nullable=False, index=True
)
source_name: Mapped[str] = mapped_column(String(64), nullable=False)
source_url: Mapped[str] = mapped_column(String(512), nullable=False)
is_best_at_source: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
is_stable_at_source: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
is_live_at_source: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
first_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
last_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
tracker: Mapped[Tracker] = relationship(back_populates="sources")
class ProbeResult(Base):
__tablename__ = "probe_results"
__table_args__ = (Index("ix_probe_results_checked_at", "checked_at"),)
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
tracker_id: Mapped[int] = mapped_column(
ForeignKey("trackers.id", ondelete="CASCADE"), nullable=False, index=True
)
checked_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
status: Mapped[str] = mapped_column(String(32), nullable=False)
latency_ms: Mapped[float | None] = mapped_column(Float, nullable=True)
ip_address: Mapped[str | None] = mapped_column(String(64), nullable=True)
ip_family: Mapped[str | None] = mapped_column(String(8), nullable=True)
response_valid: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
tracker_interval_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)
seeders: Mapped[int | None] = mapped_column(Integer, nullable=True)
leechers: Mapped[int | None] = mapped_column(Integer, nullable=True)
error_kind: Mapped[str | None] = mapped_column(String(64), nullable=True)
error_detail: Mapped[str | None] = mapped_column(String(512), nullable=True)
tracker: Mapped[Tracker] = relationship(back_populates="probe_results")
class DnsRecord(Base):
__tablename__ = "dns_records"
__table_args__ = (UniqueConstraint("tracker_id", "record_type", "value", name="uq_dns_record"),)
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
tracker_id: Mapped[int] = mapped_column(
ForeignKey("trackers.id", ondelete="CASCADE"), nullable=False, index=True
)
record_type: Mapped[str] = mapped_column(String(16), nullable=False)
value: Mapped[str] = mapped_column(String(512), nullable=False)
first_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
last_seen_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
tracker: Mapped[Tracker] = relationship(back_populates="dns_records")
class SourceImportRun(Base):
__tablename__ = "source_import_runs"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
source_name: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
status: Mapped[str] = mapped_column(String(32), nullable=False, default="running")
received_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
valid_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
new_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
unsupported_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
error_message: Mapped[str | None] = mapped_column(String(512), nullable=True)
etag: Mapped[str | None] = mapped_column(String(255), nullable=True)
last_modified: Mapped[str | None] = mapped_column(String(255), nullable=True)
class CollectorCache(Base):
"""HTTP cache state per source feed URL."""
__tablename__ = "collector_cache"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
source_name: Mapped[str] = mapped_column(String(64), nullable=False)
feed_url: Mapped[str] = mapped_column(String(512), nullable=False, unique=True)
etag: Mapped[str | None] = mapped_column(String(255), nullable=True)
last_modified: Mapped[str | None] = mapped_column(String(255), nullable=True)
updated_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
class AppState(Base):
"""Key/value operational state (bootstrap notes, etc.)."""
__tablename__ = "app_state"
key: Mapped[str] = mapped_column(String(64), primary_key=True)
value: Mapped[str] = mapped_column(Text, nullable=False)
updated_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), server_default=func.now(), nullable=False
)
|