Coverage for src/local_deep_research/database/models/library.py: 99%

1""" 

2Library and document models - Unified architecture. 

3All documents (research downloads and user uploads) are stored in one table. 

4Collections organize documents, with "Library" as the default collection. 

5""" 

6 

7import enum 

8import warnings 

9 

10from sqlalchemy import ( 

11 JSON, 

12 Boolean, 

13 Column, 

14 Date, 

15 Enum, 

16 ForeignKey, 

17 Index, 

18 Integer, 

19 LargeBinary, 

20 String, 

21 Text, 

22 UniqueConstraint, 

23) 

24from sqlalchemy.orm import backref, relationship 

25from sqlalchemy_utc import UtcDateTime, utcnow 

26 

27from .base import Base 

28 

29 

30class RAGIndexStatus(enum.Enum): 

31 """Status values for RAG indices.""" 

32 

33 ACTIVE = "active" 

34 REBUILDING = "rebuilding" 

35 DEPRECATED = "deprecated" 

36 

37 

38class DocumentStatus(enum.Enum): 

39 """Status values for document processing and downloads.""" 

40 

41 PENDING = "pending" 

42 PROCESSING = "processing" 

43 COMPLETED = "completed" 

44 FAILED = "failed" 

45 

46 

47class EmbeddingProvider(enum.Enum): 

48 """Embedding model provider types. 

49 

50 OPENAI covers both the OpenAI cloud API and any OpenAI-compatible 

51 endpoint (LM Studio, vLLM, llama.cpp server, etc.) — the underlying 

52 provider class reads ``embeddings.openai.base_url`` to target a local 

53 server when set, falling back to the OpenAI cloud when unset. 

54 """ 

55 

56 SENTENCE_TRANSFORMERS = "sentence_transformers" 

57 OLLAMA = "ollama" 

58 OPENAI = "openai" 

59 

60 

61class ExtractionMethod(str, enum.Enum): 

62 """Methods used to extract text from documents.""" 

63 

64 PDF_EXTRACTION = "pdf_extraction" 

65 NATIVE_API = "native_api" 

66 UNKNOWN = "unknown" 

67 

68 

69class ExtractionSource(str, enum.Enum): 

70 """Sources used for text extraction.""" 

71 

72 ARXIV_API = "arxiv_api" 

73 PUBMED_API = "pubmed_api" 

74 PDFPLUMBER = "pdfplumber" 

75 PDFPLUMBER_FALLBACK = "pdfplumber_fallback" 

76 LOCAL_PDF = "local_pdf" 

77 LEGACY_FILE = "legacy_file" 

78 

79 

80class ExtractionQuality(str, enum.Enum): 

81 """Quality levels for extracted text.""" 

82 

83 HIGH = "high" 

84 MEDIUM = "medium" 

85 LOW = "low" 

86 

87 

88class DistanceMetric(str, enum.Enum): 

89 """Distance metrics for vector similarity search.""" 

90 

91 COSINE = "cosine" 

92 L2 = "l2" 

93 DOT_PRODUCT = "dot_product" 

94 

95 

96class IndexType(str, enum.Enum): 

97 """FAISS index types for RAG.""" 

98 

99 FLAT = "flat" 

100 HNSW = "hnsw" 

101 IVF = "ivf" 

102 

103 

104class SplitterType(str, enum.Enum): 

105 """Text splitter types for chunking.""" 

106 

107 RECURSIVE = "recursive" 

108 SEMANTIC = "semantic" 

109 TOKEN = "token" 

110 SENTENCE = "sentence" 

111 

112 

113class PDFStorageMode(str, enum.Enum): 

114 """Storage modes for PDF files.""" 

115 

116 NONE = "none" # Don't store PDFs, text-only 

117 FILESYSTEM = "filesystem" # Store PDFs unencrypted on filesystem 

118 DATABASE = "database" # Store PDFs encrypted in database 

119 

120 

121class SourceType(Base): 

122 """ 

123 Document source types (research_download, user_upload, manual_entry, etc.). 

124 Normalized table for consistent categorization. 

125 """ 

126 

127 __tablename__ = "source_types" 

128 

129 id = Column(String(36), primary_key=True) # UUID 

130 name = Column(String(50), nullable=False, unique=True, index=True) 

131 display_name = Column(String(100), nullable=False) 

132 description = Column(Text) 

133 icon = Column(String(50)) # Icon name for UI 

134 

135 # Timestamps 

136 created_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

137 

138 def __repr__(self): 

139 return ( 

140 f"<SourceType(name='{self.name}', display='{self.display_name}')>" 

141 ) 

142 

143 

144class UploadBatch(Base): 

145 """ 

146 DEPRECATED but DO NOT DELETE. See "Why we can't drop this" below. 

147 

148 A multi-round dead-code audit in 2026-05 confirmed this table is 

149 dormant: no code path creates ``UploadBatch`` rows or sets 

150 ``Document.upload_batch_id`` (declared below). Wiring it up would 

151 have needed a product decision on what defines a "batch" (per 

152 upload submit, per UI session, etc.) and surfacing the grouping in 

153 the upload routes / UI — neither happened. Constructing an 

154 ``UploadBatch`` emits a ``DeprecationWarning`` so future 

155 contributors don't accidentally start writing to it. 

156 

157 Why we can't drop this (and the table) 

158 -------------------------------------- 

159 The same audit attempted to write the table-drop migration and 

160 hit an irreconcilable conflict in this codebase's migration 

161 framework: 

162 

163 * ``0001_initial_schema.py`` builds the initial schema from 

164 ``Base.metadata.create_all()``, so the ORM defines what 

165 "schema at every revision" means. 

166 * ``test_migrations_produce_schema_matching_models`` and 

167 ``test_check_schema_shows_no_missing_tables`` enforce that 

168 the migrated DB matches ``Base.metadata`` exactly. 

169 * ``test_stairway_up_down_up_per_revision`` and 

170 ``test_downgrade_leaves_no_residual_tables`` require that 

171 downgrade restores the schema that existed before the upgrade. 

172 

173 The combination means you cannot drop the table from the DB 

174 without also removing it from the ORM, AND you cannot remove it 

175 from the ORM without breaking either the round-trip tests 

176 (because 0001 stops producing the table at pre-0010 revisions) or 

177 the parity tests (model and migrated schema diverge). 

178 

179 A real removal needs a precursor refactor that converts 

180 ``0001_initial_schema.py`` to an explicit table list before the 

181 drop migration can be added. The benefit of finishing the drop is 

182 roughly 3 KB of empty-table overhead per user DB and one fewer 

183 ORM class. The cost is real migration risk against deployed 

184 encrypted DBs. The audit concluded the trade-off was not worth 

185 it; the deprecation here is the entire shipped fix. 

186 

187 If you still want to delete this, bring fresh evidence that the 

188 cost/benefit has changed and read PR #4178 plus the abandoned 

189 "PR 11b" thread that established this constraint. 

190 """ 

191 

192 __tablename__ = "upload_batches" 

193 

194 id = Column(String(36), primary_key=True) # UUID 

195 collection_id = Column( 

196 String(36), 

197 ForeignKey("collections.id", ondelete="CASCADE"), 

198 nullable=False, 

199 index=True, 

200 ) 

201 uploaded_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

202 file_count = Column(Integer, default=0) 

203 total_size = Column(Integer, default=0) # Total bytes 

204 

205 # Relationships 

206 collection = relationship("Collection", backref="upload_batches") 

207 documents = relationship("Document", backref="upload_batch") 

208 

209 def __init__(self, *args, **kwargs): 

210 warnings.warn( 

211 "UploadBatch is deprecated and scheduled for removal in a " 

212 "follow-up release; the table is dormant and has no production " 

213 "writers.", 

214 DeprecationWarning, 

215 stacklevel=2, 

216 ) 

217 super().__init__(*args, **kwargs) 

218 

219 def __repr__(self): 

220 return f"<UploadBatch(id='{self.id}', files={self.file_count}, size={self.total_size})>" 

221 

222 

223class Document(Base): 

224 """ 

225 Unified document table for all documents (research downloads + user uploads). 

226 """ 

227 

228 __tablename__ = "documents" 

229 

230 id = Column(String(36), primary_key=True) # UUID as string 

231 

232 # Source type (research_download, user_upload, etc.) 

233 source_type_id = Column( 

234 String(36), 

235 ForeignKey("source_types.id"), 

236 nullable=False, 

237 index=True, 

238 ) 

239 

240 # Link to original research resource (for research downloads) - nullable for uploads 

241 resource_id = Column( 

242 Integer, 

243 ForeignKey("research_resources.id", ondelete="SET NULL"), 

244 nullable=True, 

245 index=True, 

246 ) 

247 

248 # Link to research (for research downloads) - nullable for uploads 

249 research_id = Column( 

250 String(36), 

251 ForeignKey("research_history.id", ondelete="CASCADE"), 

252 nullable=True, 

253 index=True, 

254 ) 

255 

256 # Link to upload batch (for user uploads) - nullable for research downloads 

257 upload_batch_id = Column( 

258 String(36), 

259 ForeignKey("upload_batches.id", ondelete="SET NULL"), 

260 nullable=True, 

261 index=True, 

262 ) 

263 

264 # Document identification 

265 document_hash = Column( 

266 String(64), nullable=False, unique=True, index=True 

267 ) # SHA256 for deduplication 

268 original_url = Column(Text, nullable=True) # Source URL (for downloads) 

269 filename = Column(String(500), nullable=True) # Display name (for uploads) 

270 original_filename = Column( 

271 String(500), nullable=True 

272 ) # Original upload name 

273 

274 # File information 

275 file_path = Column( 

276 Text, nullable=True 

277 ) # Path relative to library/uploads root 

278 file_size = Column(Integer, nullable=False) # Size in bytes 

279 file_type = Column(String(50), nullable=False) # pdf, txt, md, html, etc. 

280 mime_type = Column(String(100), nullable=True) # MIME type 

281 

282 # Content storage - text always stored in DB 

283 text_content = Column( 

284 Text, nullable=True 

285 ) # Extracted/uploaded text content 

286 

287 # PDF storage mode (none, filesystem, database) 

288 storage_mode = Column( 

289 String(20), nullable=True, default="database" 

290 ) # PDFStorageMode value 

291 

292 # Metadata 

293 title = Column(Text) # Document title 

294 description = Column(Text) # User description 

295 authors = Column(JSON) # List of authors (for research papers) 

296 published_date = Column(Date, nullable=True) # Publication date 

297 

298 # Academic identifiers (for research papers) 

299 doi = Column(String(255), nullable=True, index=True) 

300 arxiv_id = Column(String(100), nullable=True, index=True) 

301 pmid = Column(String(50), nullable=True, index=True) 

302 pmcid = Column(String(50), nullable=True, index=True) 

303 isbn = Column(String(20), nullable=True) 

304 

305 # Download/Upload information 

306 status = Column( 

307 Enum( 

308 DocumentStatus, values_callable=lambda obj: [e.value for e in obj] 

309 ), 

310 nullable=False, 

311 default=DocumentStatus.COMPLETED, 

312 ) 

313 attempts = Column(Integer, default=1) 

314 error_message = Column(Text, nullable=True) 

315 processed_at = Column(UtcDateTime, nullable=False, default=utcnow()) 

316 last_accessed = Column(UtcDateTime, nullable=True) 

317 

318 # Text extraction metadata (for research downloads from PDFs) 

319 extraction_method = Column( 

320 String(50), nullable=True 

321 ) # pdf_extraction, native_api, etc. 

322 extraction_source = Column( 

323 String(50), nullable=True 

324 ) # arxiv_api, pdfplumber, etc. 

325 extraction_quality = Column(String(20), nullable=True) # high, medium, low 

326 has_formatting_issues = Column(Boolean, default=False) 

327 has_encoding_issues = Column(Boolean, default=False) 

328 character_count = Column(Integer, nullable=True) 

329 word_count = Column(Integer, nullable=True) 

330 

331 # Organization 

332 tags = Column(JSON) # User-defined tags 

333 favorite = Column(Boolean, default=False) 

334 

335 # Timestamps 

336 created_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

337 updated_at = Column( 

338 UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False 

339 ) 

340 

341 # Relationships 

342 source_type = relationship("SourceType", backref="documents") 

343 resource = relationship( 

344 "ResearchResource", 

345 foreign_keys="[Document.resource_id]", 

346 backref="documents", 

347 ) 

348 research = relationship("ResearchHistory", backref="documents") 

349 collections = relationship( 

350 "DocumentCollection", 

351 back_populates="document", 

352 cascade="all, delete-orphan", 

353 ) 

354 

355 # Indexes for efficient queries 

356 __table_args__ = ( 

357 Index("idx_source_type", "source_type_id", "status"), 

358 Index("idx_research_documents", "research_id", "status"), 

359 Index("idx_document_type", "file_type", "status"), 

360 Index("idx_document_hash", "document_hash"), 

361 ) 

362 

363 def __repr__(self): 

364 # ID-only repr — earlier versions interpolated the title slice 

365 # which leaks user note content into log lines / SQLAlchemy 

366 # error messages on a multi-tenant deployment. The id is enough 

367 # for debugging; full row data is one query away. 

368 doc_id = self.id[:8] if self.id else "None" 

369 return f"<Document(id={doc_id}, type={self.file_type})>" 

370 

371 

372class DocumentBlob(Base): 

373 """ 

374 Separate table for storing PDF binary content. 

375 SQLite best practices: keep BLOBs in separate table for better query performance. 

376 Stored in encrypted SQLCipher database for security. 

377 """ 

378 

379 __tablename__ = "document_blobs" 

380 

381 # Primary key references Document.id 

382 document_id = Column( 

383 String(36), 

384 ForeignKey("documents.id", ondelete="CASCADE"), 

385 primary_key=True, 

386 nullable=False, 

387 ) 

388 

389 # Binary PDF content 

390 pdf_binary = Column(LargeBinary, nullable=False) 

391 

392 # Hash for integrity verification 

393 blob_hash = Column(String(64), nullable=True, index=True) # SHA256 

394 

395 # Timestamps 

396 stored_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

397 last_accessed = Column(UtcDateTime, nullable=True) 

398 

399 # Relationship 

400 document = relationship( 

401 "Document", 

402 backref=backref("blob", passive_deletes=True), 

403 passive_deletes=True, 

404 ) 

405 

406 def __repr__(self): 

407 size = len(self.pdf_binary) if self.pdf_binary else 0 

408 return f"<DocumentBlob(document_id='{self.document_id[:8]}...', size={size})>" 

409 

410 

411class Collection(Base): 

412 """ 

413 Collections for organizing documents. 

414 'Library' is the default collection for research downloads. 

415 Users can create custom collections for organization. 

416 """ 

417 

418 __tablename__ = "collections" 

419 

420 id = Column(String(36), primary_key=True) # UUID as string 

421 name = Column(String(255), nullable=False) 

422 description = Column(Text) 

423 

424 # Collection type (default_library, user_collection, linked_folder) 

425 collection_type = Column(String(50), default="user_collection") 

426 

427 # Is this the default library collection? 

428 is_default = Column(Boolean, default=False) 

429 

430 # Egress classification: is this collection's content non-sensitive 

431 # ("public") or sensitive ("private")? Defaults to private (False) — the 

432 # safe choice. A private collection is excluded under PUBLIC_ONLY scope 

433 # and forces local LLM/embeddings inference when used, so its chunks 

434 # never reach a cloud model. Operators flip it per-collection. 

435 is_public = Column(Boolean, default=False) 

436 

437 # Whether this collection is offered to the research agent (LangGraph) as a 

438 # specialized search tool. Defaults to True (available) so existing 

439 # collections keep their current behaviour; flip it off to declutter the 

440 # agent's tool list when a collection isn't needed for agentic research. 

441 # Independent of is_public / egress scope — this is a usability switch, not 

442 # a security control. 

443 agent_enabled = Column(Boolean, default=True) 

444 

445 # Embedding model used for this collection (stored when first indexed) 

446 embedding_model = Column( 

447 String(100), nullable=True 

448 ) # e.g., 'all-MiniLM-L6-v2', 'nomic-embed-text:latest' 

449 embedding_model_type = Column( 

450 Enum( 

451 EmbeddingProvider, 

452 values_callable=lambda obj: [e.value for e in obj], 

453 ), 

454 nullable=True, 

455 ) 

456 embedding_dimension = Column(Integer, nullable=True) # Vector dimension 

457 chunk_size = Column(Integer, nullable=True) # Chunk size used 

458 chunk_overlap = Column(Integer, nullable=True) # Chunk overlap used 

459 

460 # Advanced embedding configuration options (Issue #1054) 

461 splitter_type = Column( 

462 String(50), nullable=True 

463 ) # Splitter type: 'recursive', 'semantic', 'token', 'sentence' 

464 text_separators = Column( 

465 JSON, nullable=True 

466 ) # Text separators for chunking, e.g., ["\n\n", "\n", ". ", " ", ""] 

467 distance_metric = Column( 

468 String(50), nullable=True 

469 ) # Distance metric: 'cosine', 'l2', 'dot_product' 

470 normalize_vectors = Column( 

471 Boolean, nullable=True 

472 ) # Whether to normalize embeddings with L2 

473 index_type = Column( 

474 String(50), nullable=True 

475 ) # FAISS index type: 'flat', 'hnsw', 'ivf' 

476 

477 # Timestamps 

478 created_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

479 updated_at = Column( 

480 UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False 

481 ) 

482 

483 # Relationships 

484 document_links = relationship( 

485 "DocumentCollection", 

486 back_populates="collection", 

487 cascade="all, delete-orphan", 

488 ) 

489 linked_folders = relationship( 

490 "CollectionFolder", 

491 back_populates="collection", 

492 cascade="all, delete-orphan", 

493 ) 

494 

495 def __repr__(self): 

496 return f"<Collection(id='{self.id}', name='{self.name}', type='{self.collection_type}')>" 

497 

498 

499class DocumentCollection(Base): 

500 """ 

501 Many-to-many relationship between documents and collections. 

502 Tracks indexing status per collection (documents can be in multiple collections). 

503 """ 

504 

505 __tablename__ = "document_collections" 

506 

507 id = Column(Integer, primary_key=True, autoincrement=True) 

508 

509 # Foreign keys 

510 document_id = Column( 

511 String(36), 

512 ForeignKey("documents.id", ondelete="CASCADE"), 

513 nullable=False, 

514 index=True, 

515 ) 

516 collection_id = Column( 

517 String(36), 

518 ForeignKey("collections.id", ondelete="CASCADE"), 

519 nullable=False, 

520 index=True, 

521 ) 

522 

523 # Indexing status (per collection!) 

524 indexed = Column( 

525 Boolean, default=False 

526 ) # Whether indexed for this collection 

527 chunk_count = Column( 

528 Integer, default=0 

529 ) # Number of chunks in this collection 

530 last_indexed_at = Column(UtcDateTime, nullable=True) 

531 

532 # Timestamps 

533 added_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

534 

535 # Relationships 

536 document = relationship("Document", back_populates="collections") 

537 collection = relationship("Collection", back_populates="document_links") 

538 

539 # Ensure one entry per document-collection pair 

540 __table_args__ = ( 

541 UniqueConstraint( 

542 "document_id", "collection_id", name="uix_document_collection" 

543 ), 

544 Index("idx_collection_indexed", "collection_id", "indexed"), 

545 ) 

546 

547 def __repr__(self): 

548 return f"<DocumentCollection(doc_id={self.document_id}, coll_id={self.collection_id}, indexed={self.indexed})>" 

549 

550 

551class DocumentChunk(Base): 

552 """ 

553 Universal chunk storage for RAG across all sources. 

554 Stores text chunks in encrypted database for semantic search. 

555 """ 

556 

557 __tablename__ = "document_chunks" 

558 

559 id = Column(Integer, primary_key=True, autoincrement=True) 

560 

561 # Chunk identification 

562 chunk_hash = Column( 

563 String(64), nullable=False, index=True 

564 ) # SHA256 for deduplication 

565 

566 # Source tracking - now points to unified Document table 

567 source_type = Column( 

568 String(20), nullable=False, index=True 

569 ) # 'document', 'folder_file' 

570 source_id = Column( 

571 String(36), nullable=True, index=True 

572 ) # Document.id (UUID as string) 

573 source_path = Column( 

574 Text, nullable=True 

575 ) # File path if local collection source 

576 collection_name = Column( 

577 String(100), nullable=False, index=True 

578 ) # collection_<uuid> 

579 

580 # Chunk content (encrypted in SQLCipher DB) 

581 chunk_text = Column(Text, nullable=False) # The actual chunk text 

582 chunk_index = Column(Integer, nullable=False) # Position in source document 

583 start_char = Column(Integer, nullable=False) # Start character position 

584 end_char = Column(Integer, nullable=False) # End character position 

585 word_count = Column(Integer, nullable=False) # Number of words in chunk 

586 

587 # Embedding metadata 

588 embedding_id = Column( 

589 String(36), nullable=False, unique=True, index=True 

590 ) # UUID for FAISS vector mapping 

591 embedding_model = Column( 

592 String(100), nullable=False 

593 ) # e.g., 'all-MiniLM-L6-v2' 

594 embedding_model_type = Column( 

595 Enum( 

596 EmbeddingProvider, 

597 values_callable=lambda obj: [e.value for e in obj], 

598 ), 

599 nullable=False, 

600 ) 

601 embedding_dimension = Column(Integer, nullable=True) # Vector dimension 

602 

603 # Document metadata (for context) 

604 document_title = Column(Text, nullable=True) # Title of source document 

605 document_metadata = Column( 

606 JSON, nullable=True 

607 ) # Additional metadata from source 

608 

609 # Timestamps 

610 created_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

611 last_accessed = Column(UtcDateTime, nullable=True) 

612 

613 # Indexes for efficient queries. 

614 # NOTE: the old UniqueConstraint("chunk_hash", "collection_name", 

615 # name="uix_chunk_collection") was DROPPED (migration 0023). The RAG store 

616 # is now one-row-per-chunk keyed by the int PK (no cross-document chunk 

617 # sharing), so identical text in two documents legitimately produces two 

618 # rows; the unique constraint would raise IntegrityError on that (and on a 

619 # document with a repeated/blank chunk). chunk_hash stays a plain index for 

620 # query-time result de-duplication. 

621 __table_args__ = ( 

622 Index("idx_chunk_source", "source_type", "source_id"), 

623 Index("idx_chunk_collection", "collection_name", "created_at"), 

624 Index("idx_chunk_embedding", "embedding_id"), 

625 # AUTOINCREMENT so this id is NEVER reused after rows are deleted. 

626 # The id keys the vector store; a plain SQLite ROWID gets recycled, so 

627 # a recycled id could be re-adopted by an unrelated later chunk while a 

628 # stale vector for the old id still lingers in the index — making search 

629 # rehydrate the WRONG document's text. A monotonic id closes that. 

630 {"sqlite_autoincrement": True}, 

631 ) 

632 

633 def __repr__(self): 

634 return f"<DocumentChunk(collection='{self.collection_name}', source_type='{self.source_type}', index={self.chunk_index}, words={self.word_count})>" 

635 

636 

637class DownloadQueue(Base): 

638 """ 

639 Queue for pending document downloads. 

640 Renamed from LibraryDownloadQueue for consistency. 

641 """ 

642 

643 __tablename__ = "download_queue" 

644 

645 id = Column(Integer, primary_key=True, autoincrement=True) 

646 

647 # What to download 

648 resource_id = Column( 

649 Integer, 

650 ForeignKey("research_resources.id", ondelete="CASCADE"), 

651 nullable=False, 

652 unique=True, # One queue entry per resource 

653 ) 

654 research_id = Column(String(36), nullable=False, index=True) 

655 

656 # Target collection (defaults to Library collection) 

657 collection_id = Column( 

658 String(36), 

659 ForeignKey("collections.id", ondelete="SET NULL"), 

660 nullable=True, 

661 index=True, 

662 ) 

663 

664 # Queue management 

665 priority = Column(Integer, default=0) # Higher = more important 

666 status = Column( 

667 Enum( 

668 DocumentStatus, values_callable=lambda obj: [e.value for e in obj] 

669 ), 

670 nullable=False, 

671 default=DocumentStatus.PENDING, 

672 ) 

673 attempts = Column(Integer, default=0) 

674 max_attempts = Column(Integer, default=3) 

675 

676 # Error tracking 

677 last_error = Column(Text, nullable=True) 

678 last_attempt_at = Column(UtcDateTime, nullable=True) 

679 

680 # Timestamps 

681 queued_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

682 completed_at = Column(UtcDateTime, nullable=True) 

683 

684 # Relationships 

685 resource = relationship("ResearchResource", backref="download_queue") 

686 collection = relationship("Collection", backref="download_queue_items") 

687 

688 # Index for the common filter_by(research_id=..., status=...) query 

689 # pattern used by download_bulk, queue_all_undownloaded, and the 

690 # background scheduler. Mirrors Document.idx_research_documents. 

691 __table_args__ = ( 

692 Index("idx_download_queue_research_status", "research_id", "status"), 

693 ) 

694 

695 def __repr__(self): 

696 return f"<DownloadQueue(resource_id={self.resource_id}, status={self.status}, attempts={self.attempts})>" 

697 

698 

699class LibraryStatistics(Base): 

700 """ 

701 Aggregate statistics for the library. 

702 Updated periodically for dashboard display. 

703 """ 

704 

705 __tablename__ = "library_statistics" 

706 

707 id = Column(Integer, primary_key=True, autoincrement=True) 

708 

709 # Document counts 

710 total_documents = Column(Integer, default=0) 

711 total_pdfs = Column(Integer, default=0) 

712 total_html = Column(Integer, default=0) 

713 total_other = Column(Integer, default=0) 

714 

715 # Storage metrics 

716 total_size_bytes = Column(Integer, default=0) 

717 average_document_size = Column(Integer, default=0) 

718 

719 # Research metrics 

720 total_researches_with_downloads = Column(Integer, default=0) 

721 average_documents_per_research = Column(Integer, default=0) 

722 

723 # Download metrics 

724 total_download_attempts = Column(Integer, default=0) 

725 successful_downloads = Column(Integer, default=0) 

726 failed_downloads = Column(Integer, default=0) 

727 pending_downloads = Column(Integer, default=0) 

728 

729 # Academic sources breakdown 

730 arxiv_count = Column(Integer, default=0) 

731 pubmed_count = Column(Integer, default=0) 

732 doi_count = Column(Integer, default=0) 

733 other_count = Column(Integer, default=0) 

734 

735 # Timestamps 

736 calculated_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

737 

738 def __repr__(self): 

739 return f"<LibraryStatistics(documents={self.total_documents}, size={self.total_size_bytes})>" 

740 

741 

742class RAGIndex(Base): 

743 """ 

744 Tracks FAISS indices for RAG collections. 

745 Each collection+embedding_model combination has its own FAISS index. 

746 """ 

747 

748 __tablename__ = "rag_indices" 

749 

750 id = Column(Integer, primary_key=True, autoincrement=True) 

751 

752 # Collection and model identification 

753 collection_name = Column( 

754 String(100), nullable=False, index=True 

755 ) # 'collection_<uuid>' 

756 embedding_model = Column( 

757 String(100), nullable=False 

758 ) # e.g., 'all-MiniLM-L6-v2' 

759 embedding_model_type = Column( 

760 Enum( 

761 EmbeddingProvider, 

762 values_callable=lambda obj: [e.value for e in obj], 

763 ), 

764 nullable=False, 

765 ) 

766 embedding_dimension = Column(Integer, nullable=False) # Vector dimension 

767 

768 # Index file location 

769 index_path = Column(Text, nullable=False) # Path to .faiss file 

770 index_hash = Column( 

771 String(64), nullable=False, unique=True, index=True 

772 ) # SHA256 of collection+model for uniqueness 

773 

774 # Chunking parameters used 

775 chunk_size = Column(Integer, nullable=False) 

776 chunk_overlap = Column(Integer, nullable=False) 

777 

778 # Advanced embedding configuration options (Issue #1054) 

779 splitter_type = Column( 

780 String(50), nullable=True 

781 ) # Splitter type: 'recursive', 'semantic', 'token', 'sentence' 

782 text_separators = Column( 

783 JSON, nullable=True 

784 ) # Text separators for chunking, e.g., ["\n\n", "\n", ". ", " ", ""] 

785 distance_metric = Column( 

786 String(50), nullable=True 

787 ) # Distance metric: 'cosine', 'l2', 'dot_product' 

788 normalize_vectors = Column( 

789 Boolean, nullable=True 

790 ) # Whether to normalize embeddings with L2 

791 index_type = Column( 

792 String(50), nullable=True 

793 ) # FAISS index type: 'flat', 'hnsw', 'ivf' 

794 

795 # Index statistics 

796 chunk_count = Column(Integer, default=0) # Number of chunks in this index 

797 total_documents = Column(Integer, default=0) # Number of source documents 

798 

799 # Status 

800 status = Column( 

801 Enum( 

802 RAGIndexStatus, values_callable=lambda obj: [e.value for e in obj] 

803 ), 

804 nullable=False, 

805 default=RAGIndexStatus.ACTIVE, 

806 ) 

807 is_current = Column( 

808 Boolean, default=True 

809 ) # Whether this is the current index for this collection 

810 

811 # Timestamps 

812 created_at = Column(UtcDateTime, default=utcnow(), nullable=False) 

813 last_updated_at = Column( 

814 UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False 

815 ) 

816 last_used_at = Column( 

817 UtcDateTime, nullable=True 

818 ) # Last time index was searched 

819 

820 # Ensure one active index per collection+model 

821 __table_args__ = ( 

822 UniqueConstraint( 

823 "collection_name", 

824 "embedding_model", 

825 "embedding_model_type", 

826 name="uix_collection_model", 

827 ), 

828 Index("idx_collection_current", "collection_name", "is_current"), 

829 ) 

830 

831 def __repr__(self): 

832 return f"<RAGIndex(collection='{self.collection_name}', model='{self.embedding_model}', chunks={self.chunk_count})>" 

833 

834 

835class RagDocumentStatus(Base): 

836 """ 

837 Tracks which documents have been indexed for RAG. 

838 Row existence = document is indexed. No row = not indexed. 

839 Simple and avoids ORM caching issues. 

840 """ 

841 

842 __tablename__ = "rag_document_status" 

843 

844 # Composite primary key 

845 document_id = Column( 

846 String(36), 

847 ForeignKey("documents.id", ondelete="CASCADE"), 

848 primary_key=True, 

849 nullable=False, 

850 ) 

851 collection_id = Column( 

852 String(36), 

853 ForeignKey("collections.id", ondelete="CASCADE"), 

854 primary_key=True, 

855 nullable=False, 

856 ) 

857 

858 # Which RAG index was used (tracks embedding model indirectly) 

859 rag_index_id = Column( 

860 Integer, 

861 ForeignKey("rag_indices.id", ondelete="CASCADE"), 

862 nullable=False, 

863 index=True, 

864 ) 

865 

866 # Metadata 

867 chunk_count = Column(Integer, nullable=False) 

868 indexed_at = Column(UtcDateTime, nullable=False, default=utcnow()) 

869 

870 # Indexes for fast lookups 

871 __table_args__ = ( 

872 Index("idx_rag_status_collection", "collection_id"), 

873 Index("idx_rag_status_index", "rag_index_id"), 

874 ) 

875 

876 def __repr__(self): 

877 return f"<RagDocumentStatus(doc='{self.document_id[:8]}...', coll='{self.collection_id[:8]}...', chunks={self.chunk_count})>" 

878 

879 

880class CollectionFolder(Base): 

881 """ 

882 Local folders linked to a collection for indexing. 

883 """ 

884 

885 __tablename__ = "collection_folders" 

886 

887 id = Column(Integer, primary_key=True, autoincrement=True) 

888 

889 # Collection association 

890 collection_id = Column(