Coverage for src/local_deep_research/database/models/zotero.py: 94%

1""" 

2Zotero integration models. 

3 

4These tables live in each user's encrypted per-user database and track the 

5state needed to sync a Zotero library/collection into the document library: 

6 

7- ``ZoteroSyncState``: one row per configured (library, collection) pair. It 

8 remembers which LDR :class:`Collection` the Zotero collection maps to and 

9 the Zotero *library version* reached by the last successful sync 

10 (``last_version`` — informational / shown in the status UI; see below). 

11- ``ZoteroItemMap``: maps an individual Zotero item to the LDR 

12 :class:`Document` it was imported as, plus the item's Zotero version. This 

13 enables skipping unchanged items, re-importing changed ones, and detecting 

14 items removed from the collection so they can be removed locally. 

15 

16Sync compares the *full* current ``{itemKey: version}`` map of the 

17collection's top-level items against ``ZoteroItemMap`` — additions/changes by 

18version, removals by absence. This deliberately does NOT use a ``?since=`` 

19delta: a delta (or the library-level ``/deleted`` feed) would not catch items 

20*removed from the collection* but still present in the library, whereas the 

21full-map comparison does. 

22 

23""" 

24 

25from datetime import datetime 

26 

27from sqlalchemy import ( 

28 Boolean, 

29 Column, 

30 ForeignKey, 

31 Integer, 

32 String, 

33 Text, 

34 UniqueConstraint, 

35) 

36from sqlalchemy.orm import Mapped, mapped_column 

37from sqlalchemy_utc import UtcDateTime, utcnow 

38 

39from .base import Base 

40 

41 

42class ZoteroSyncState(Base): 

43 """Per-collection sync bookkeeping for the Zotero integration.""" 

44 

45 __tablename__ = "zotero_sync_state" 

46 __table_args__ = ( 

47 UniqueConstraint( 

48 "library_type", 

49 "library_id", 

50 "collection_key", 

51 name="uq_zotero_sync_state_library_collection", 

52 ), 

53 ) 

54 

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

56 

57 # Zotero library identity 

58 library_type = Column(String(10), nullable=False) # 'user' | 'group' 

59 library_id = Column(String(64), nullable=False) 

60 # Empty string means "the entire library" (no specific collection). 

61 # Stored as "" rather than NULL so the UNIQUE constraint is effective on 

62 # SQLite (which treats NULLs as distinct). 

63 collection_key = Column(String(16), nullable=False, default="") 

64 

65 # LDR collection these items are imported into 

66 ldr_collection_id = Column( 

67 String(36), 

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

69 nullable=False, 

70 index=True, 

71 ) 

72 

73 # Zotero library version reached by the last successful sync. 

74 # Informational (surfaced in the status UI) — NOT used as a ``?since=`` 

75 # cursor; each sync fetches the full current version map (see module 

76 # docstring for why). 0 means "never synced". 

77 last_version = Column(Integer, nullable=False, default=0) 

78 

79 last_synced_at = Column(UtcDateTime, nullable=True) 

80 last_status = Column( 

81 String(20), nullable=False, default="pending" 

82 ) # pending | syncing | completed | failed 

83 last_error = Column(Text, nullable=True) 

84 item_count = Column(Integer, nullable=False, default=0) 

85 

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

87 updated_at = Column( 

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

89 ) 

90 

91 def __repr__(self) -> str: 

92 return ( 

93 f"<ZoteroSyncState(library={self.library_type}:{self.library_id}, " 

94 f"collection={self.collection_key or 'ALL'}, " 

95 f"version={self.last_version})>" 

96 ) 

97 

98 

99class ZoteroItemMap(Base): 

100 """Maps a single Zotero item to the LDR document it was imported as.""" 

101 

102 __tablename__ = "zotero_item_map" 

103 __table_args__ = ( 

104 UniqueConstraint( 

105 "ldr_collection_id", 

106 "zotero_item_key", 

107 name="uq_zotero_item_map_collection_item", 

108 ), 

109 ) 

110 

111 id: Mapped[int] = mapped_column( 

112 Integer, primary_key=True, autoincrement=True 

113 ) 

114 

115 ldr_collection_id: Mapped[str] = mapped_column( 

116 String(36), 

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

118 nullable=False, 

119 index=True, 

120 ) 

121 # The Zotero (parent) item key this row tracks. 

122 zotero_item_key: Mapped[str] = mapped_column( 

123 String(16), nullable=False, index=True 

124 ) 

125 # Zotero item version at last import — used for change detection. 

126 zotero_version: Mapped[int] = mapped_column( 

127 Integer, nullable=False, default=0 

128 ) 

129 

130 # The LDR document this item was imported as. SET NULL on document 

131 # deletion so a removed document doesn't dangle. A null document_id marks 

132 # an item deliberately not imported (skipped, or its document was deleted 

133 # locally); the sync service re-imports it only when the item's Zotero 

134 # version changes. A non-null document_id whose Document row went missing 

135 # IS re-imported on the next sync. 

136 document_id: Mapped[str | None] = mapped_column( 

137 String(36), 

138 ForeignKey("documents.id", ondelete="SET NULL"), 

139 nullable=True, 

140 index=True, 

141 ) 

142 has_pdf: Mapped[bool] = mapped_column( 

143 Boolean, nullable=False, default=False 

144 ) 

145 

146 last_synced_at: Mapped[datetime | None] = mapped_column( 

147 UtcDateTime, nullable=True 

148 ) 

149 created_at: Mapped[datetime] = mapped_column( 

150 UtcDateTime, default=utcnow(), nullable=False 

151 ) 

152 updated_at: Mapped[datetime] = mapped_column( 

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

154 ) 

155 

156 def __repr__(self) -> str: 

157 return ( 

158 f"<ZoteroItemMap(item={self.zotero_item_key}, " 

159 f"document={self.document_id}, version={self.zotero_version})>" 

160 )