Coverage for src/local_deep_research/database/models/news.py: 99%
117 statements
« prev ^ index » next coverage.py v7.15.1, created at 2026-07-20 01:24 +0000
1"""
2Database models for news subscriptions and related functionality.
3These tables are created in per-user encrypted databases.
4"""
6from sqlalchemy import (
7 Column,
8 Integer,
9 String,
10 JSON,
11 Text,
12 Boolean,
13 ForeignKey,
14 Enum,
15 and_,
16)
17from sqlalchemy_utc import UtcDateTime, utcnow
18import enum
20from .base import Base
23class CardType(enum.Enum):
24 """Types of cards in the system"""
26 NEWS = "news"
27 RESEARCH = "research"
28 UPDATE = "update"
29 OVERVIEW = "overview"
32class RatingType(enum.Enum):
33 """Types of ratings"""
35 RELEVANCE = "relevance" # Thumbs up/down
36 QUALITY = "quality" # 1-5 stars
39class SubscriptionType(enum.Enum):
40 """Types of subscriptions"""
42 SEARCH = "search"
43 TOPIC = "topic"
46class SubscriptionStatus(enum.Enum):
47 """Status of subscriptions"""
49 ACTIVE = "active"
50 PAUSED = "paused"
51 EXPIRED = "expired"
52 ERROR = "error"
55class NewsSubscription(Base):
56 """User's news subscriptions"""
58 __tablename__ = "news_subscriptions"
60 id = Column(String(50), primary_key=True)
62 # Subscription details
63 name = Column(String(255)) # Optional friendly name
64 subscription_type = Column(
65 String(20), nullable=False
66 ) # 'search' or 'topic'
67 query_or_topic = Column(Text, nullable=False)
68 refresh_interval_minutes = Column(
69 Integer, default=1440
70 ) # Default 24 hours = 1440 minutes
71 frequency = Column(
72 String(50), default="daily"
73 ) # daily, weekly, hourly, etc.
75 # Timing
76 created_at = Column(UtcDateTime, default=utcnow())
77 updated_at = Column(
78 UtcDateTime,
79 default=utcnow(),
80 onupdate=utcnow(),
81 )
82 last_refresh = Column(UtcDateTime)
83 next_refresh = Column(UtcDateTime)
84 expires_at = Column(UtcDateTime) # Optional expiration
86 # Source tracking
87 source_type = Column(String(50)) # 'manual', 'research', 'news_topic'
88 source_id = Column(String(100)) # ID of source (research_id, news_id)
89 created_from = Column(Text) # Description of source
91 # Organization
92 folder = Column(String(100)) # Folder name
93 folder_id = Column(String(36)) # Folder ID
94 notes = Column(Text) # User notes
96 # Model configuration
97 model_provider = Column(String(50)) # OLLAMA, OPENAI, ANTHROPIC, etc.
98 model = Column(String(100)) # Specific model name
99 search_strategy = Column(String(50)) # Strategy for searches
100 custom_endpoint = Column(String(255)) # Custom API endpoint if used
102 # Search configuration
103 search_engine = Column(String(50)) # Search engine to use
104 search_iterations = Column(
105 Integer, default=3
106 ) # Number of search iterations
107 questions_per_iteration = Column(
108 Integer, default=5
109 ) # Questions per iteration
111 # State
112 # `status` is the single source of truth for whether a subscription is
113 # switched on. create_subscription() and update_subscription() write only
114 # status; scheduling decisions (active_filter / due_filter below) key off
115 # it exclusively.
116 status = Column(String(20), default="active")
117 # Legacy mirror of `status == "active"`. It is NOT kept in sync on the
118 # create/update paths (create_subscription leaves it at the default True
119 # even for paused subscriptions), so it must never be used to decide
120 # whether to run a subscription — use status / the filters below instead.
121 # Retained only for backwards-compatible serialization; not dropped because
122 # this model lives in per-user encrypted databases where a column migration
123 # is costly and risky.
124 is_active = Column(Boolean, default=True) # Legacy mirror; see above
125 error_count = Column(Integer, default=0)
126 last_error = Column(Text)
128 # Additional data
129 extra_data = Column(JSON) # Additional flexible data
131 @classmethod
132 def active_filter(cls):
133 """SQLAlchemy predicate for subscriptions that are switched on.
135 Single authoritative definition of "active", keyed on the `status`
136 column (see the column comments above). Use this instead of
137 re-spelling ``status == "active"`` or filtering on ``is_active``.
138 """
139 return cls.status == SubscriptionStatus.ACTIVE.value
141 @classmethod
142 def due_filter(cls, now):
143 """SQLAlchemy predicate for active subscriptions whose run is due.
145 ``now`` should be a timezone-aware UTC datetime. The ``next_refresh
146 is not None`` guard matters: a freshly created subscription always has
147 next_refresh set, but defending against NULL keeps a stray NULL row
148 from ever being treated as "infinitely overdue".
149 """
150 return and_(
151 cls.active_filter(),
152 cls.next_refresh.is_not(None),
153 cls.next_refresh <= now,
154 )
157class SubscriptionFolder(Base):
158 """Folders for organizing subscriptions"""
160 __tablename__ = "subscription_folders"
162 id = Column(String(36), primary_key=True) # UUID
163 name = Column(String(100), nullable=False)
164 description = Column(Text)
165 color = Column(String(7)) # Hex color
166 icon = Column(String(50)) # Icon identifier
168 # Timestamps
169 created_at = Column(UtcDateTime, default=utcnow())
170 updated_at = Column(
171 UtcDateTime,
172 default=utcnow(),
173 onupdate=utcnow(),
174 )
176 # Settings
177 is_default = Column(Boolean, default=False)
178 sort_order = Column(Integer, default=0)
180 def to_dict(self):
181 """Convert folder to dictionary."""
182 return {
183 "id": self.id,
184 "name": self.name,
185 "description": self.description,
186 "color": self.color,
187 "icon": self.icon,
188 "created_at": self.created_at.isoformat()
189 if self.created_at
190 else None,
191 "updated_at": self.updated_at.isoformat()
192 if self.updated_at
193 else None,
194 "is_default": self.is_default,
195 "sort_order": self.sort_order,
196 }
199class NewsCard(Base):
200 """Individual news cards/items"""
202 __tablename__ = "news_cards"
204 id = Column(String(50), primary_key=True)
206 # Content
207 title = Column(String(500), nullable=False)
208 summary = Column(Text)
209 content = Column(Text)
210 url = Column(String(1000))
212 # Source info
213 source_name = Column(String(200))
214 source_type = Column(String(50)) # 'research', 'rss', 'api', etc.
215 source_id = Column(String(100)) # ID in source system
217 # Categorization
218 category = Column(String(100))
219 tags = Column(JSON) # List of tags
220 card_type = Column(Enum(CardType), default=CardType.NEWS)
222 # Timing
223 published_at = Column(UtcDateTime)
224 discovered_at = Column(UtcDateTime, default=utcnow())
226 # Interaction tracking
227 is_read = Column(Boolean, default=False)
228 read_at = Column(UtcDateTime)
229 is_saved = Column(Boolean, default=False)
230 saved_at = Column(UtcDateTime)
232 # Metadata
233 extra_data = Column(JSON) # Flexible additional data
235 # Subscription link
236 subscription_id = Column(String(50), ForeignKey("news_subscriptions.id"))
239class UserRating(Base):
240 """User ratings/feedback on news items"""
242 __tablename__ = "news_user_ratings"
244 id = Column(Integer, primary_key=True)
246 # What was rated
247 card_id = Column(String(50), ForeignKey("news_cards.id"), nullable=False)
248 rating_type = Column(Enum(RatingType), nullable=False)
250 # Rating value
251 rating_value = Column(String(20)) # 'up', 'down', or numeric
253 # When
254 created_at = Column(UtcDateTime, default=utcnow())
256 # Optional feedback
257 comment = Column(Text)
258 tags = Column(JSON) # User-applied tags
261class UserPreference(Base):
262 """User preferences for news"""
264 __tablename__ = "news_user_preferences"
266 id = Column(Integer, primary_key=True)
268 # Preference key-value pairs
269 key = Column(String(100), nullable=False, unique=True)
270 value = Column(JSON)
272 # Metadata
273 created_at = Column(UtcDateTime, default=utcnow())
274 updated_at = Column(
275 UtcDateTime,
276 default=utcnow(),
277 onupdate=utcnow(),
278 )
281class NewsInterest(Base):
282 """User's declared interests for news"""
284 __tablename__ = "news_interests"
286 id = Column(Integer, primary_key=True)
288 # Interest details
289 topic = Column(String(200), nullable=False)
290 interest_type = Column(String(50)) # 'positive', 'negative', 'keyword'
291 strength = Column(Integer, default=5) # 1-10 scale
293 # Timing
294 created_at = Column(UtcDateTime, default=utcnow())
295 expires_at = Column(UtcDateTime) # Optional expiration
297 # Source
298 source = Column(String(50)) # 'manual', 'inferred', 'imported'
299 source_id = Column(String(100))