Skip to main content

rostra_client_db/
social.rs

1use std::collections::{BTreeMap, BTreeSet, HashMap};
2
3use bincode::{Decode, Encode};
4use rostra_core::event::{EventExt as _, PersonaId, PersonaTag, SocialPost, content_kind};
5use rostra_core::id::RostraId;
6use rostra_core::{ExternalEventId, ShortEventId, Timestamp};
7use serde::{Deserialize, Serialize};
8use tracing::{debug, warn};
9
10use super::Database;
11use crate::event::ContentStoreRecord;
12use crate::{
13    DbResult, LOG_TARGET, content_store, events, events_content_state,
14    shoutbox_posts_by_received_at, social_posts, social_posts_by_received_at, social_posts_by_time,
15    social_posts_reactions, social_posts_replaced_by, social_posts_replaces, social_posts_replies,
16    tables,
17};
18
19/// Cursor for paginating events by their author timestamp.
20///
21/// Used for Followees and Network timeline tabs where posts are ordered
22/// by when they were authored, not when we received them.
23/// Key structure: `(author_timestamp, event_id)`
24#[derive(
25    Encode, Decode, Serialize, Deserialize, Debug, Copy, Clone, PartialEq, Eq, PartialOrd, Ord,
26)]
27pub struct EventPaginationCursor {
28    pub ts: Timestamp,
29    pub event_id: ShortEventId,
30}
31
32impl EventPaginationCursor {
33    pub const ZERO: Self = Self {
34        ts: Timestamp::ZERO,
35        event_id: ShortEventId::ZERO,
36    };
37    pub const MAX: Self = Self {
38        ts: Timestamp::MAX,
39        event_id: ShortEventId::MAX,
40    };
41}
42
43/// Cursor for paginating events by when we received them.
44///
45/// Used for Notifications tab where posts are ordered by reception time,
46/// not author timestamp. Includes a monotonic counter for strict ordering
47/// when multiple events arrive at the same timestamp.
48/// Key structure: `(received_timestamp, seq)`
49#[derive(
50    Encode, Decode, Serialize, Deserialize, Debug, Copy, Clone, PartialEq, Eq, PartialOrd, Ord,
51)]
52pub struct ReceivedAtPaginationCursor {
53    pub ts: Timestamp,
54    /// Monotonic sequence number for ordering events at the same timestamp.
55    /// Alias for backwards compatibility with cookies saved as
56    /// "reception_order".
57    #[serde(alias = "reception_order")]
58    pub seq: u64,
59}
60
61impl ReceivedAtPaginationCursor {
62    pub const ZERO: Self = Self {
63        ts: Timestamp::ZERO,
64        seq: 0,
65    };
66    pub const MAX: Self = Self {
67        ts: Timestamp::MAX,
68        seq: u64::MAX,
69    };
70
71    /// Returns the next cursor position (exclusive start for forward
72    /// iteration).
73    ///
74    /// Used when we want to iterate starting AFTER this cursor position.
75    pub fn next(self) -> Self {
76        if self.seq < u64::MAX {
77            Self {
78                ts: self.ts,
79                seq: self.seq + 1,
80            }
81        } else {
82            // seq overflow, increment timestamp
83            Self {
84                ts: self.ts.saturating_add_secs(1),
85                seq: 0,
86            }
87        }
88    }
89}
90#[derive(Clone, Debug)]
91pub struct SocialPostRecord<C> {
92    pub ts: Timestamp,
93    pub event_id: ShortEventId,
94    pub author: RostraId,
95    pub reply_to: Option<ExternalEventId>,
96    pub content: C,
97    pub reply_count: u64,
98}
99
100/// Record for a shoutbox post with associated metadata.
101#[derive(Clone, Debug)]
102pub struct ShoutboxPostRecord {
103    pub received_ts: Timestamp,
104    pub event_id: ShortEventId,
105    pub author: RostraId,
106    pub content: content_kind::Shoutbox,
107}
108
109impl Database {
110    pub(crate) fn is_social_post_replaced_tx(
111        author: RostraId,
112        event_id: ShortEventId,
113        social_posts_replaced_by_table: &impl social_posts_replaced_by::ReadableTable,
114    ) -> DbResult<bool> {
115        Ok(social_posts_replaced_by_table
116            .range(
117                &(author, event_id, ShortEventId::ZERO)..=&(author, event_id, ShortEventId::MAX),
118            )?
119            .next()
120            .transpose()?
121            .is_some())
122    }
123
124    fn latest_social_post_version_tx(
125        author: RostraId,
126        event_id: ShortEventId,
127        social_posts_replaced_by_table: &impl social_posts_replaced_by::ReadableTable,
128    ) -> DbResult<ShortEventId> {
129        let mut current = event_id;
130
131        while let Some(record) = social_posts_replaced_by_table
132            .range(&(author, current, ShortEventId::ZERO)..=&(author, current, ShortEventId::MAX))?
133            .next()
134            .transpose()?
135        {
136            let (_, _, new_event_id) = record.0.value();
137            current = new_event_id;
138        }
139
140        Ok(current)
141    }
142
143    fn social_post_versions_tx(
144        author: RostraId,
145        event_id: ShortEventId,
146        social_posts_replaces_table: &impl social_posts_replaces::ReadableTable,
147    ) -> DbResult<Vec<ShortEventId>> {
148        let mut versions = vec![event_id];
149        let mut current = event_id;
150
151        while let Some(record) = social_posts_replaces_table
152            .range(&(author, current, ShortEventId::ZERO)..=&(author, current, ShortEventId::MAX))?
153            .next()
154            .transpose()?
155        {
156            let (_, _, old_event_id) = record.0.value();
157            versions.push(old_event_id);
158            current = old_event_id;
159        }
160
161        Ok(versions)
162    }
163
164    fn social_post_reply_count_tx(
165        author: RostraId,
166        event_id: ShortEventId,
167        social_posts_table: &impl social_posts::ReadableTable,
168        social_posts_replaces_table: &impl social_posts_replaces::ReadableTable,
169    ) -> DbResult<u64> {
170        let mut reply_count = 0u64;
171        for version in Self::social_post_versions_tx(author, event_id, social_posts_replaces_table)?
172        {
173            reply_count = reply_count.saturating_add(
174                Database::get_social_post_tx(version, social_posts_table)?
175                    .unwrap_or_default()
176                    .reply_count,
177            );
178        }
179        Ok(reply_count)
180    }
181
182    fn social_post_record_by_id_tx(
183        event_id: ShortEventId,
184        ts: Timestamp,
185        events_table: &impl events::ReadableTable,
186        social_posts_table: &impl social_posts::ReadableTable,
187        events_content_state_table: &impl events_content_state::ReadableTable,
188        content_store_table: &impl content_store::ReadableTable,
189        social_posts_replaces_table: &impl social_posts_replaces::ReadableTable,
190    ) -> DbResult<Option<SocialPostRecord<content_kind::SocialPost>>> {
191        let Some(event) = Database::get_event_tx(event_id, events_table)? else {
192            warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
193            return Ok(None);
194        };
195        let author = event.author();
196        let content_hash = event.content_hash();
197
198        if Database::get_event_content_state_tx(event_id, events_content_state_table)?.is_some() {
199            debug!(target: LOG_TARGET, %event_id, "Skipping post without content present");
200            return Ok(None);
201        }
202
203        let Some(store_record) = content_store_table.get(&content_hash)?.map(|g| g.value()) else {
204            debug!(target: LOG_TARGET, %event_id, "Skipping post without content present");
205            return Ok(None);
206        };
207        let ContentStoreRecord(content) = store_record;
208
209        let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>() else {
210            debug!(target: LOG_TARGET, %event_id, "Skipping post with invalid content");
211            return Ok(None);
212        };
213
214        let reply_count = Self::social_post_reply_count_tx(
215            author,
216            event_id,
217            social_posts_table,
218            social_posts_replaces_table,
219        )?;
220
221        Ok(Some(SocialPostRecord {
222            ts,
223            author,
224            event_id,
225            reply_to: social_post.reply_to,
226            reply_count,
227            content: social_post,
228        }))
229    }
230
231    pub async fn paginate_social_posts(
232        &self,
233        cursor: Option<EventPaginationCursor>,
234        limit: usize,
235        filter_fn: impl Fn(&SocialPostRecord<SocialPost>) -> bool + Send + 'static,
236    ) -> (
237        Vec<SocialPostRecord<content_kind::SocialPost>>,
238        Option<EventPaginationCursor>,
239    ) {
240        self.read_with(|tx| {
241            let events_table = tx.open_table(&events::TABLE)?;
242            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
243            let social_posts_by_time_table = tx.open_table(&social_posts_by_time::TABLE)?;
244            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
245            let content_store_table = tx.open_table(&content_store::TABLE)?;
246            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
247            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
248
249            let (ret, cursor) = Self::paginate_table(&social_posts_by_time_table,
250                cursor.map(|c| (c.ts, c.event_id)),
251                limit,
252                move |(ts, event_id), _| {
253
254                let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
255                    warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
256                    return Ok(None);
257                };
258                let author = event.author();
259                if Self::is_social_post_replaced_tx(author, event_id, &social_posts_replaced_by_table)? {
260                    return Ok(None);
261                }
262                let content_hash = event.content_hash();
263
264                // If event has a content state, it means content is deleted or pruned
265                if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
266                    .is_some()
267                {
268                    return Ok(None);
269                }
270
271                // Get content from store
272                let Some(store_record) =
273                    content_store_table.get(&content_hash)?.map(|g| g.value())
274                else {
275                    return Ok(None);
276                };
277                let ContentStoreRecord(content) = store_record;
278
279                let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>() else {
280                    debug!(target: LOG_TARGET, %event_id, "Content invalid");
281                    return Ok(None);
282                };
283
284                let reply_count = Self::social_post_reply_count_tx(
285                    author,
286                    event_id,
287                    &social_posts_table,
288                    &social_posts_replaces_table,
289                )?;
290
291                let social_post_record = SocialPostRecord {
292                    ts,
293                    author,
294                    event_id,
295                    reply_count,
296                    reply_to: social_post.reply_to,
297                    content: social_post,
298                };
299
300                if !filter_fn(&social_post_record) {
301                    return Ok(None);
302                }
303
304                Ok(Some(social_post_record))
305            })?;
306
307            Ok((
308                ret,
309                cursor.map(|(ts, event_id)| EventPaginationCursor { ts, event_id }),
310            ))
311        })
312        .await
313        .expect("Storage error")
314    }
315
316    pub async fn paginate_social_posts_rev(
317        &self,
318        cursor: Option<EventPaginationCursor>,
319        limit: usize,
320        filter_fn: impl Fn(&SocialPostRecord<SocialPost>) -> bool + Send + 'static,
321    ) -> (
322        Vec<SocialPostRecord<content_kind::SocialPost>>,
323        Option<EventPaginationCursor>,
324    ) {
325        self.read_with(|tx| {
326            let events_table = tx.open_table(&events::TABLE)?;
327            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
328            let social_posts_by_time_table = tx.open_table(&social_posts_by_time::TABLE)?;
329            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
330            let content_store_table = tx.open_table(&content_store::TABLE)?;
331            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
332            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
333
334            let (ret, cursor) = Self::paginate_table_rev(&social_posts_by_time_table,
335                cursor.map(|c| (c.ts, c.event_id)),
336                limit,
337                move |(ts, event_id), _| {
338
339                let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
340                    warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
341                    return Ok(None);
342                };
343                let author = event.author();
344                if Self::is_social_post_replaced_tx(author, event_id, &social_posts_replaced_by_table)? {
345                    return Ok(None);
346                }
347                let content_hash = event.content_hash();
348
349                // If event has a content state, it means content is deleted or pruned
350                if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
351                    .is_some()
352                {
353                    return Ok(None);
354                }
355
356                // Get content from store
357                let Some(store_record) =
358                    content_store_table.get(&content_hash)?.map(|g| g.value())
359                else {
360                    return Ok(None);
361                };
362                let ContentStoreRecord(content) = store_record;
363
364                let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>() else {
365                    debug!(target: LOG_TARGET, %event_id, "Content invalid");
366                    return Ok(None);
367                };
368
369                let reply_count = Self::social_post_reply_count_tx(
370                    author,
371                    event_id,
372                    &social_posts_table,
373                    &social_posts_replaces_table,
374                )?;
375
376                let social_post_record = SocialPostRecord {
377                    ts,
378                    author,
379                    event_id,
380                    reply_count,
381                    reply_to: social_post.reply_to,
382                    content: social_post,
383                };
384
385                if !filter_fn(&social_post_record) {
386                    return Ok(None);
387                }
388
389                Ok(Some(social_post_record))
390            })?;
391
392            Ok((
393                ret,
394                cursor.map(|(ts, event_id)| EventPaginationCursor { ts, event_id }),
395            ))
396        })
397        .await
398        .expect("Storage error")
399    }
400
401    /// Get the cursor of the most recently received social post.
402    ///
403    /// Used for tracking "last seen" notification position.
404    pub async fn get_latest_social_post_received_at_cursor(
405        &self,
406    ) -> Option<ReceivedAtPaginationCursor> {
407        self.read_with(|tx| {
408            let social_posts_by_received_at_table =
409                tx.open_table(&social_posts_by_received_at::TABLE)?;
410
411            // Get the last (most recent) entry in the table
412            if let Some(entry) = social_posts_by_received_at_table.last()? {
413                let (ts, seq) = entry.0.value();
414                Ok(Some(ReceivedAtPaginationCursor { ts, seq }))
415            } else {
416                Ok(None)
417            }
418        })
419        .await
420        .expect("Storage error")
421    }
422
423    /// Paginate social posts ordered by when we received them (forward).
424    ///
425    /// Used for notification badge count calculation.
426    pub async fn paginate_social_posts_by_received_at(
427        &self,
428        cursor: Option<ReceivedAtPaginationCursor>,
429        limit: usize,
430        filter_fn: impl Fn(&SocialPostRecord<SocialPost>) -> bool + Send + 'static,
431    ) -> (
432        Vec<SocialPostRecord<content_kind::SocialPost>>,
433        Option<ReceivedAtPaginationCursor>,
434    ) {
435        self.read_with(|tx| {
436            let events_table = tx.open_table(&events::TABLE)?;
437            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
438            let social_posts_by_received_at_table =
439                tx.open_table(&social_posts_by_received_at::TABLE)?;
440            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
441            let content_store_table = tx.open_table(&content_store::TABLE)?;
442            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
443            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
444
445            let (ret, cursor) = Self::paginate_table(
446                &social_posts_by_received_at_table,
447                cursor.map(|c| (c.ts, c.seq)),
448                limit,
449                move |(ts, _seq), event_id| {
450                    let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
451                        warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
452                        return Ok(None);
453                    };
454                    let author = event.author();
455                    if Self::is_social_post_replaced_tx(
456                        author,
457                        event_id,
458                        &social_posts_replaced_by_table,
459                    )? {
460                        return Ok(None);
461                    }
462                    let content_hash = event.content_hash();
463
464                    // If event has a content state, it means content is deleted or pruned
465                    if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
466                        .is_some()
467                    {
468                        return Ok(None);
469                    }
470
471                    // Get content from store
472                    let Some(store_record) =
473                        content_store_table.get(&content_hash)?.map(|g| g.value())
474                    else {
475                        return Ok(None);
476                    };
477                    let ContentStoreRecord(content) = store_record;
478
479                    let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>()
480                    else {
481                        debug!(target: LOG_TARGET, %event_id, "Content invalid");
482                        return Ok(None);
483                    };
484
485                    let reply_count = Self::social_post_reply_count_tx(
486                        author,
487                        event_id,
488                        &social_posts_table,
489                        &social_posts_replaces_table,
490                    )?;
491
492                    let social_post_record = SocialPostRecord {
493                        ts,
494                        author,
495                        event_id,
496                        reply_count,
497                        reply_to: social_post.reply_to,
498                        content: social_post,
499                    };
500
501                    if !filter_fn(&social_post_record) {
502                        return Ok(None);
503                    }
504
505                    Ok(Some(social_post_record))
506                },
507            )?;
508
509            Ok((
510                ret,
511                cursor.map(|(ts, seq)| ReceivedAtPaginationCursor {
512                    ts,
513                    seq,
514                }),
515            ))
516        })
517        .await
518        .expect("Storage error")
519    }
520
521    /// Paginate social posts ordered by when we received them (reverse).
522    ///
523    /// Used for notification timeline display.
524    pub async fn paginate_social_posts_by_received_at_rev(
525        &self,
526        cursor: Option<ReceivedAtPaginationCursor>,
527        limit: usize,
528        filter_fn: impl Fn(&SocialPostRecord<SocialPost>) -> bool + Send + 'static,
529    ) -> (
530        Vec<SocialPostRecord<content_kind::SocialPost>>,
531        Option<ReceivedAtPaginationCursor>,
532    ) {
533        self.read_with(|tx| {
534            let events_table = tx.open_table(&events::TABLE)?;
535            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
536            let social_posts_by_received_at_table =
537                tx.open_table(&social_posts_by_received_at::TABLE)?;
538            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
539            let content_store_table = tx.open_table(&content_store::TABLE)?;
540            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
541            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
542
543            let (ret, cursor) = Self::paginate_table_rev(
544                &social_posts_by_received_at_table,
545                cursor.map(|c| (c.ts, c.seq)),
546                limit,
547                move |(ts, _seq), event_id| {
548                    let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
549                        warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
550                        return Ok(None);
551                    };
552                    let author = event.author();
553                    if Self::is_social_post_replaced_tx(
554                        author,
555                        event_id,
556                        &social_posts_replaced_by_table,
557                    )? {
558                        return Ok(None);
559                    }
560                    let content_hash = event.content_hash();
561
562                    // If event has a content state, it means content is deleted or pruned
563                    if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
564                        .is_some()
565                    {
566                        return Ok(None);
567                    }
568
569                    // Get content from store
570                    let Some(store_record) =
571                        content_store_table.get(&content_hash)?.map(|g| g.value())
572                    else {
573                        return Ok(None);
574                    };
575                    let ContentStoreRecord(content) = store_record;
576
577                    let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>()
578                    else {
579                        debug!(target: LOG_TARGET, %event_id, "Content invalid");
580                        return Ok(None);
581                    };
582
583                    let reply_count = Self::social_post_reply_count_tx(
584                        author,
585                        event_id,
586                        &social_posts_table,
587                        &social_posts_replaces_table,
588                    )?;
589
590                    let social_post_record = SocialPostRecord {
591                        ts,
592                        author,
593                        event_id,
594                        reply_count,
595                        reply_to: social_post.reply_to,
596                        content: social_post,
597                    };
598
599                    if !filter_fn(&social_post_record) {
600                        return Ok(None);
601                    }
602
603                    Ok(Some(social_post_record))
604                },
605            )?;
606
607            Ok((
608                ret,
609                cursor.map(|(ts, seq)| ReceivedAtPaginationCursor {
610                    ts,
611                    seq,
612                }),
613            ))
614        })
615        .await
616        .expect("Storage error")
617    }
618
619    pub async fn paginate_social_post_comments_rev(
620        &self,
621        post_event_id: ShortEventId,
622        cursor: Option<EventPaginationCursor>,
623        limit: usize,
624    ) -> (
625        Vec<SocialPostRecord<content_kind::SocialPost>>,
626        Option<EventPaginationCursor>,
627    ) {
628        self.read_with(|tx| {
629            let events_table = tx.open_table(&events::TABLE)?;
630            let social_posts_tbl = tx.open_table(&social_posts::TABLE)?;
631            let social_post_replies_tbl = tx.open_table(&social_posts_replies::TABLE)?;
632            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
633            let content_store_table = tx.open_table(&content_store::TABLE)?;
634            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
635
636            let versions =
637                if let Some(event) = Database::get_event_tx(post_event_id, &events_table)? {
638                    Self::social_post_versions_tx(
639                        event.author(),
640                        post_event_id,
641                        &social_posts_replaces_table,
642                    )?
643                } else {
644                    vec![post_event_id]
645                };
646
647            let mut records = vec![];
648            for version in versions {
649                for entry in social_post_replies_tbl.range(
650                    &(version, Timestamp::ZERO, ShortEventId::ZERO)
651                        ..=&(version, Timestamp::MAX, ShortEventId::MAX),
652                )? {
653                    let (key, _) = entry?;
654                    let (_, ts, event_id) = key.value();
655                    if cursor.is_some_and(|cursor| (cursor.ts, cursor.event_id) <= (ts, event_id)) {
656                        continue;
657                    }
658
659                    let Some(record) = Self::social_post_record_by_id_tx(
660                        event_id,
661                        ts,
662                        &events_table,
663                        &social_posts_tbl,
664                        &events_content_state_table,
665                        &content_store_table,
666                        &social_posts_replaces_table,
667                    )?
668                    else {
669                        continue;
670                    };
671                    records.push(record);
672                }
673            }
674
675            records.sort_by_key(|record| std::cmp::Reverse((record.ts, record.event_id)));
676            records.truncate(limit);
677            let cursor = records.last().map(|record| EventPaginationCursor {
678                ts: record.ts,
679                event_id: record.event_id,
680            });
681
682            Ok((records, cursor))
683        })
684        .await
685        .expect("Storage error")
686    }
687
688    pub async fn paginate_social_post_reactions_rev(
689        &self,
690        post_event_id: ShortEventId,
691        cursor: Option<EventPaginationCursor>,
692        limit: usize,
693    ) -> (
694        Vec<SocialPostRecord<content_kind::SocialPost>>,
695        Option<EventPaginationCursor>,
696    ) {
697        self.read_with(|tx| {
698            let events_table = tx.open_table(&events::TABLE)?;
699            let social_posts_tbl = tx.open_table(&social_posts::TABLE)?;
700            let social_post_reactions_tbl = tx.open_table(&social_posts_reactions::TABLE)?;
701            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
702            let content_store_table = tx.open_table(&content_store::TABLE)?;
703            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
704
705            let versions =
706                if let Some(event) = Database::get_event_tx(post_event_id, &events_table)? {
707                    Self::social_post_versions_tx(
708                        event.author(),
709                        post_event_id,
710                        &social_posts_replaces_table,
711                    )?
712                } else {
713                    vec![post_event_id]
714                };
715
716            let mut records = vec![];
717            for version in versions {
718                for entry in social_post_reactions_tbl.range(
719                    &(version, Timestamp::ZERO, ShortEventId::ZERO)
720                        ..=&(version, Timestamp::MAX, ShortEventId::MAX),
721                )? {
722                    let (key, _) = entry?;
723                    let (_, ts, event_id) = key.value();
724                    if cursor.is_some_and(|cursor| (cursor.ts, cursor.event_id) <= (ts, event_id)) {
725                        continue;
726                    }
727
728                    let Some(record) = Self::social_post_record_by_id_tx(
729                        event_id,
730                        ts,
731                        &events_table,
732                        &social_posts_tbl,
733                        &events_content_state_table,
734                        &content_store_table,
735                        &social_posts_replaces_table,
736                    )?
737                    else {
738                        continue;
739                    };
740                    records.push(record);
741                }
742            }
743
744            records.sort_by_key(|record| std::cmp::Reverse((record.ts, record.event_id)));
745            records.truncate(limit);
746            let cursor = records.last().map(|record| EventPaginationCursor {
747                ts: record.ts,
748                event_id: record.event_id,
749            });
750
751            Ok((records, cursor))
752        })
753        .await
754        .expect("Storage error")
755    }
756
757    pub async fn get_posts_by_id(
758        &self,
759        post_ids: impl Iterator<Item = ShortEventId>,
760    ) -> HashMap<ShortEventId, SocialPostRecord<content_kind::SocialPost>> {
761        self.read_with(|tx| {
762            let events_table = tx.open_table(&events::TABLE)?;
763            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
764            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
765            let content_store_table = tx.open_table(&content_store::TABLE)?;
766            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
767            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
768
769            let mut ret = HashMap::new();
770
771            for requested_event_id in post_ids {
772                let event_id = if let Some(event) =
773                    Database::get_event_tx(requested_event_id, &events_table)?
774                {
775                    Self::latest_social_post_version_tx(
776                        event.author(),
777                        requested_event_id,
778                        &social_posts_replaced_by_table,
779                    )?
780                } else {
781                    requested_event_id
782                };
783
784                let Some((social_post, event, _social_post_record)) =
785                    Self::get_social_post_record_tx(
786                        &events_table,
787                        &social_posts_table,
788                        &events_content_state_table,
789                        &content_store_table,
790                        event_id,
791                    )?
792                else {
793                    continue;
794                };
795
796                ret.insert(
797                    requested_event_id,
798                    SocialPostRecord {
799                        ts: event.timestamp(),
800                        author: event.author(),
801                        event_id,
802                        reply_count: Self::social_post_reply_count_tx(
803                            event.author(),
804                            event_id,
805                            &social_posts_table,
806                            &social_posts_replaces_table,
807                        )?,
808                        reply_to: social_post.reply_to,
809                        content: social_post,
810                    },
811                );
812            }
813
814            Ok(ret)
815        })
816        .await
817        .expect("Storage error")
818    }
819    /// Get all persona tags used by an identity, with usage counts > 0.
820    pub async fn get_persona_tags_for_id(&self, id: RostraId) -> BTreeSet<PersonaTag> {
821        self.read_with(|tx| {
822            let social_posts_by_time_table = tx.open_table(&social_posts_by_time::TABLE)?;
823            let events_table = tx.open_table(&events::TABLE)?;
824            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
825            let content_store_table = tx.open_table(&content_store::TABLE)?;
826
827            let mut tags = BTreeSet::new();
828
829            // Scan posts by this author and collect all persona tags
830            for entry in social_posts_by_time_table.range(..)?.rev() {
831                let entry = entry?;
832                let (_ts, event_id) = entry.0.value();
833
834                let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
835                    continue;
836                };
837
838                if event.author() != id {
839                    continue;
840                }
841
842                let content_hash = event.content_hash();
843
844                if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
845                    .is_some()
846                {
847                    continue;
848                }
849
850                let Some(store_record) = content_store_table.get(&content_hash)?.map(|g| g.value())
851                else {
852                    continue;
853                };
854                let crate::event::ContentStoreRecord(content) = store_record;
855
856                if let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>() {
857                    tags.extend(social_post.persona_tags());
858                }
859
860                // Limit scan to avoid excessive reads; 500 posts is enough
861                // to discover tags
862                if 500 < tags.len() {
863                    break;
864                }
865            }
866
867            Ok(tags)
868        })
869        .await
870        .expect("Storage error")
871    }
872
873    pub async fn get_personas_for_id(&self, id: RostraId) -> BTreeMap<PersonaId, String> {
874        self.read_with(|tx| {
875            let personas = tx.open_table(&tables::ids_personas::TABLE)?;
876
877            // Default predefined personas
878            let mut ret = BTreeMap::from([
879                (PersonaId(0), "Personal".into()),
880                (PersonaId(1), "Professional".into()),
881                (PersonaId(2), "Civic".into()),
882            ]);
883
884            for record in personas.range(&(id, PersonaId::MIN)..=&(id, PersonaId::MAX))? {
885                let (k, v) = record?;
886                ret.insert(k.value().1, v.value().display_name);
887            }
888
889            Ok(ret)
890        })
891        .await
892        .expect("Storage error")
893    }
894
895    pub async fn get_personas(
896        &self,
897        iter: impl Iterator<Item = (RostraId, PersonaId)>,
898    ) -> BTreeMap<(RostraId, PersonaId), String> {
899        self.read_with(|tx| {
900            let personas = tx.open_table(&tables::ids_personas::TABLE)?;
901
902            // Default predefined personas
903            let default_personas: BTreeMap<PersonaId, String> = BTreeMap::from([
904                (PersonaId(0), "Personal".into()),
905                (PersonaId(1), "Professional".into()),
906                (PersonaId(2), "Civic".into()),
907            ]);
908
909            let mut ret = BTreeMap::new();
910            for (rostra_id, persona_id) in iter {
911                if let Some(record) = personas.get(&(rostra_id, persona_id))? {
912                    ret.insert((rostra_id, persona_id), record.value().display_name);
913                } else {
914                    if let Some(d) = default_personas.get(&persona_id) {
915                        ret.insert((rostra_id, persona_id), d.clone());
916                    }
917                }
918            }
919            Ok(ret)
920        })
921        .await
922        .expect("Storage error")
923    }
924
925    pub async fn get_social_post(
926        &self,
927        event_id: ShortEventId,
928    ) -> Option<SocialPostRecord<content_kind::SocialPost>> {
929        self.read_with(|tx| {
930            let events_table = tx.open_table(&events::TABLE)?;
931            let social_posts_table = tx.open_table(&social_posts::TABLE)?;
932            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
933            let content_store_table = tx.open_table(&content_store::TABLE)?;
934            let social_posts_replaces_table = tx.open_table(&social_posts_replaces::TABLE)?;
935            let social_posts_replaced_by_table = tx.open_table(&social_posts_replaced_by::TABLE)?;
936
937            let event_id = if let Some(event) = Database::get_event_tx(event_id, &events_table)? {
938                Self::latest_social_post_version_tx(
939                    event.author(),
940                    event_id,
941                    &social_posts_replaced_by_table,
942                )?
943            } else {
944                event_id
945            };
946
947            let Some((social_post, event, _social_post_record)) = Self::get_social_post_record_tx(
948                &events_table,
949                &social_posts_table,
950                &events_content_state_table,
951                &content_store_table,
952                event_id,
953            )?
954            else {
955                return Ok(None);
956            };
957
958            if event.is_delete_parent_aux_content_set()
959                && social_post
960                    .djot_content
961                    .as_deref()
962                    .is_none_or(|text| text.trim().is_empty())
963            {
964                return Ok(None);
965            }
966
967            Ok(Some(SocialPostRecord {
968                ts: event.timestamp(),
969                author: event.author(),
970                event_id,
971                reply_count: Self::social_post_reply_count_tx(
972                    event.author(),
973                    event_id,
974                    &social_posts_table,
975                    &social_posts_replaces_table,
976                )?,
977                reply_to: social_post.reply_to,
978                content: social_post,
979            }))
980        })
981        .await
982        .expect("Storage error")
983    }
984
985    pub(crate) fn get_social_post_record_tx(
986        events_table: &impl events::ReadableTable,
987        social_posts_table: &impl social_posts::ReadableTable,
988        events_content_state_table: &impl events_content_state::ReadableTable,
989        content_store_table: &impl content_store::ReadableTable,
990        event_id: ShortEventId,
991    ) -> DbResult<Option<(SocialPost, crate::EventRecord, crate::SocialPostRecord)>> {
992        // Get event first to find content_hash
993        let Some(event) = Database::get_event_tx(event_id, events_table)? else {
994            warn!(target: LOG_TARGET, %event_id, "Missing event for a post with social_post_record?!");
995            return Ok(None);
996        };
997        let content_hash = event.content_hash();
998
999        // If event has a content state, it means content is deleted or pruned
1000        if Database::get_event_content_state_tx(event_id, events_content_state_table)?.is_some() {
1001            return Ok(None);
1002        }
1003
1004        // Look up content from store
1005        let Some(store_record) = content_store_table.get(&content_hash)?.map(|g| g.value()) else {
1006            return Ok(None);
1007        };
1008
1009        let ContentStoreRecord(content) = store_record;
1010
1011        let Ok(social_post) = content.deserialize_cbor::<content_kind::SocialPost>() else {
1012            debug!(target: LOG_TARGET, %event_id, "Content invalid");
1013            return Ok(None);
1014        };
1015        let social_post_record =
1016            Database::get_social_post_tx(event_id, social_posts_table)?.unwrap_or_default();
1017        Ok(Some((social_post, event, social_post_record)))
1018    }
1019
1020    /// Check if a post is a self-mention (mentions the local user).
1021    ///
1022    /// Returns `true` if the post with the given event_id is recorded in
1023    /// the `social_posts_self_mention` table, meaning it contains an @mention
1024    /// of the local user.
1025    pub async fn is_self_mention(&self, event_id: ShortEventId) -> bool {
1026        self.read_with(|tx| {
1027            let self_mention_table = tx.open_table(&crate::social_posts_self_mention::TABLE)?;
1028            Ok(self_mention_table.get(&event_id)?.is_some())
1029        })
1030        .await
1031        .unwrap_or(false)
1032    }
1033
1034    /// Get the set of all event IDs that are self-mentions.
1035    ///
1036    /// Returns a HashSet of ShortEventIds for posts that mention the local
1037    /// user. Used for efficient filtering in notification queries.
1038    pub async fn get_self_mentions(&self) -> std::collections::HashSet<ShortEventId> {
1039        self.read_with(|tx| {
1040            let self_mention_table = tx.open_table(&crate::social_posts_self_mention::TABLE)?;
1041            let mut mentions = std::collections::HashSet::new();
1042            for entry in self_mention_table.range(..)? {
1043                let (key, _) = entry?;
1044                mentions.insert(key.value());
1045            }
1046            Ok(mentions)
1047        })
1048        .await
1049        .unwrap_or_default()
1050    }
1051
1052    /// Get the cursor of the most recently received shoutbox post.
1053    ///
1054    /// Used for tracking "last seen" shoutbox position.
1055    pub async fn get_latest_shoutbox_received_at_cursor(
1056        &self,
1057    ) -> Option<ReceivedAtPaginationCursor> {
1058        self.read_with(|tx| {
1059            let shoutbox_by_received_at_table =
1060                tx.open_table(&shoutbox_posts_by_received_at::TABLE)?;
1061
1062            // Get the last (most recent) entry in the table
1063            if let Some(entry) = shoutbox_by_received_at_table.last()? {
1064                let (ts, seq) = entry.0.value();
1065                Ok(Some(ReceivedAtPaginationCursor { ts, seq }))
1066            } else {
1067                Ok(None)
1068            }
1069        })
1070        .await
1071        .expect("Storage error")
1072    }
1073
1074    /// Paginate shoutbox posts ordered by when we received them (reverse -
1075    /// newest first).
1076    ///
1077    /// Unlike social posts, shoutbox posts are not filtered - all posts are
1078    /// shown.
1079    pub async fn paginate_shoutbox_posts_by_received_at_rev(
1080        &self,
1081        cursor: Option<ReceivedAtPaginationCursor>,
1082        limit: usize,
1083    ) -> (Vec<ShoutboxPostRecord>, Option<ReceivedAtPaginationCursor>) {
1084        self.read_with(|tx| {
1085            let events_table = tx.open_table(&events::TABLE)?;
1086            let shoutbox_by_received_at_table =
1087                tx.open_table(&shoutbox_posts_by_received_at::TABLE)?;
1088            let events_content_state_table = tx.open_table(&events_content_state::TABLE)?;
1089            let content_store_table = tx.open_table(&content_store::TABLE)?;
1090
1091            let (ret, cursor) = Self::paginate_table_rev(
1092                &shoutbox_by_received_at_table,
1093                cursor.map(|c| (c.ts, c.seq)),
1094                limit,
1095                move |(ts, _seq), event_id| {
1096                    let Some(event) = Database::get_event_tx(event_id, &events_table)? else {
1097                        warn!(target: LOG_TARGET, %event_id, "Missing event for shoutbox post");
1098                        return Ok(None);
1099                    };
1100                    let content_hash = event.content_hash();
1101
1102                    // If event has a content state, it means content is deleted or pruned
1103                    if Database::get_event_content_state_tx(event_id, &events_content_state_table)?
1104                        .is_some()
1105                    {
1106                        return Ok(None);
1107                    }
1108
1109                    // Get content from store
1110                    let Some(store_record) =
1111                        content_store_table.get(&content_hash)?.map(|g| g.value())
1112                    else {
1113                        return Ok(None);
1114                    };
1115                    let ContentStoreRecord(content) = store_record;
1116
1117                    let Ok(shoutbox) = content.deserialize_cbor::<content_kind::Shoutbox>() else {
1118                        debug!(target: LOG_TARGET, %event_id, "Shoutbox content invalid");
1119                        return Ok(None);
1120                    };
1121
1122                    Ok(Some(ShoutboxPostRecord {
1123                        received_ts: ts,
1124                        author: event.author(),
1125                        event_id,
1126                        content: shoutbox,
1127                    }))
1128                },
1129            )?;
1130
1131            Ok((
1132                ret,
1133                cursor.map(|(ts, seq)| ReceivedAtPaginationCursor { ts, seq }),
1134            ))
1135        })
1136        .await
1137        .expect("Storage error")
1138    }
1139
1140    /// Count shoutbox posts received after the given cursor.
1141    ///
1142    /// Used for counting unread shoutbox messages.
1143    pub async fn count_shoutbox_posts_since(
1144        &self,
1145        cursor: Option<ReceivedAtPaginationCursor>,
1146        limit: usize,
1147    ) -> usize {
1148        self.read_with(|tx| {
1149            let shoutbox_by_received_at_table =
1150                tx.open_table(&shoutbox_posts_by_received_at::TABLE)?;
1151
1152            // Use cursor.next() to start AFTER the cursor
1153            let start = cursor.map(|c| c.next());
1154            let mut count = 0;
1155
1156            for entry in if let Some(start) = start {
1157                shoutbox_by_received_at_table.range(&(start.ts, start.seq)..)?
1158            } else {
1159                shoutbox_by_received_at_table.range(..)?
1160            } {
1161                let _ = entry?;
1162                count += 1;
1163                if limit <= count {
1164                    break;
1165                }
1166            }
1167
1168            Ok(count)
1169        })
1170        .await
1171        .expect("Storage error")
1172    }
1173}