nandi.uk
This repository has no description
semble
/
src
/
modules
/
notifications
/
infrastructure
/
repositories
/
DrizzleNotificationRepository.ts
18 kB
579 lines
1import { eq, desc, and, count, inArray } from 'drizzle-orm';
2import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
3import {
4 INotificationRepository,
5 NotificationQueryOptions,
6 PaginatedNotificationResult,
7 EnrichedNotificationResult,
8 PaginatedEnrichedNotificationResult,
9} from '../../domain/INotificationRepository';
10import { Notification } from '../../domain/Notification';
11import { NotificationId } from '../../domain/value-objects/NotificationId';
12import { CuratorId } from '../../../cards/domain/value-objects/CuratorId';
13import { notifications } from './schema/notification.sql';
14import {
15 NotificationMapper,
16 NotificationDTO,
17} from './mappers/NotificationMapper';
18import { Result, ok, err } from '../../../../shared/core/Result';
19import { cards } from '../../../cards/infrastructure/repositories/schema/card.sql';
20import {
21 collections,
22 collectionCards,
23} from '../../../cards/infrastructure/repositories/schema/collection.sql';
24import { libraryMemberships } from '../../../cards/infrastructure/repositories/schema/libraryMembership.sql';
25import { CardTypeEnum } from '../../../cards/domain/value-objects/CardType';
26import { countDistinct } from 'drizzle-orm';
27
28export class DrizzleNotificationRepository implements INotificationRepository {
29 constructor(private db: PostgresJsDatabase) {}
30
31 async save(notification: Notification): Promise<Result<void>> {
32 try {
33 const dto = NotificationMapper.toPersistence(notification);
34
35 await this.db
36 .insert(notifications)
37 .values({
38 id: dto.id,
39 recipientUserId: dto.recipientUserId,
40 actorUserId: dto.actorUserId,
41 type: dto.type,
42 metadata: dto.metadata,
43 read: dto.read,
44 createdAt: dto.createdAt,
45 updatedAt: dto.updatedAt,
46 })
47 .onConflictDoUpdate({
48 target: notifications.id,
49 set: {
50 read: dto.read,
51 updatedAt: dto.updatedAt,
52 },
53 });
54
55 return ok(undefined);
56 } catch (error) {
57 return err(error as Error);
58 }
59 }
60
61 async findById(id: NotificationId): Promise<Result<Notification | null>> {
62 try {
63 const result = await this.db
64 .select()
65 .from(notifications)
66 .where(eq(notifications.id, id.getStringValue()))
67 .limit(1);
68
69 if (result.length === 0) {
70 return ok(null);
71 }
72
73 const notificationData = result[0]!;
74 const dto: NotificationDTO = {
75 id: notificationData.id,
76 recipientUserId: notificationData.recipientUserId,
77 actorUserId: notificationData.actorUserId,
78 type: notificationData.type,
79 metadata: notificationData.metadata as any,
80 read: notificationData.read,
81 createdAt: notificationData.createdAt,
82 updatedAt: notificationData.updatedAt,
83 };
84
85 const domainResult = NotificationMapper.toDomain(dto);
86 if (domainResult.isErr()) {
87 return err(domainResult.error);
88 }
89
90 return ok(domainResult.value);
91 } catch (error) {
92 return err(error as Error);
93 }
94 }
95
96 async findByRecipient(
97 recipientId: CuratorId,
98 options: NotificationQueryOptions,
99 ): Promise<Result<PaginatedNotificationResult>> {
100 try {
101 const { page, limit, unreadOnly } = options;
102 const offset = (page - 1) * limit;
103
104 // Build where conditions
105 const whereConditions = [
106 eq(notifications.recipientUserId, recipientId.value),
107 ];
108
109 if (unreadOnly) {
110 whereConditions.push(eq(notifications.read, false));
111 }
112
113 const whereClause =
114 whereConditions.length > 1
115 ? and(...whereConditions)
116 : whereConditions[0];
117
118 // Get notifications
119 const notificationsResult = await this.db
120 .select()
121 .from(notifications)
122 .where(whereClause)
123 .orderBy(desc(notifications.createdAt))
124 .limit(limit)
125 .offset(offset);
126
127 // Get total count
128 const totalCountResult = await this.db
129 .select({ count: count() })
130 .from(notifications)
131 .where(whereClause);
132
133 // Get unread count
134 const unreadCountResult = await this.db
135 .select({ count: count() })
136 .from(notifications)
137 .where(
138 and(
139 eq(notifications.recipientUserId, recipientId.value),
140 eq(notifications.read, false),
141 ),
142 );
143
144 const totalCount = totalCountResult[0]?.count || 0;
145 const unreadCount = unreadCountResult[0]?.count || 0;
146
147 // Map to domain objects
148 const notificationsList: Notification[] = [];
149 for (const notificationData of notificationsResult) {
150 const dto: NotificationDTO = {
151 id: notificationData.id,
152 recipientUserId: notificationData.recipientUserId,
153 actorUserId: notificationData.actorUserId,
154 type: notificationData.type,
155 metadata: notificationData.metadata as any,
156 read: notificationData.read,
157 createdAt: notificationData.createdAt,
158 updatedAt: notificationData.updatedAt,
159 };
160
161 const domainResult = NotificationMapper.toDomain(dto);
162 if (domainResult.isErr()) {
163 return err(domainResult.error);
164 }
165
166 notificationsList.push(domainResult.value);
167 }
168
169 const hasMore = offset + notificationsList.length < totalCount;
170
171 return ok({
172 notifications: notificationsList,
173 totalCount,
174 hasMore,
175 unreadCount,
176 });
177 } catch (error) {
178 return err(error as Error);
179 }
180 }
181
182 async getUnreadCount(recipientId: CuratorId): Promise<Result<number>> {
183 try {
184 const result = await this.db
185 .select({ count: count() })
186 .from(notifications)
187 .where(
188 and(
189 eq(notifications.recipientUserId, recipientId.value),
190 eq(notifications.read, false),
191 ),
192 );
193
194 return ok(result[0]?.count || 0);
195 } catch (error) {
196 return err(error as Error);
197 }
198 }
199
200 async markAsRead(notificationIds: NotificationId[]): Promise<Result<void>> {
201 try {
202 const ids = notificationIds.map((id) => id.getStringValue());
203
204 if (ids.length === 0) {
205 return ok(undefined);
206 }
207
208 await this.db
209 .update(notifications)
210 .set({
211 read: true,
212 updatedAt: new Date(),
213 })
214 .where(inArray(notifications.id, ids));
215
216 return ok(undefined);
217 } catch (error) {
218 return err(error as Error);
219 }
220 }
221
222 async findByCardAndActor(
223 cardId: string,
224 actorUserId: CuratorId,
225 ): Promise<Result<Notification[]>> {
226 try {
227 const result = await this.db
228 .select()
229 .from(notifications)
230 .where(eq(notifications.actorUserId, actorUserId.value));
231
232 // Filter by cardId in metadata
233 const matchingNotifications: Notification[] = [];
234 for (const notificationData of result) {
235 const metadata = notificationData.metadata as any;
236 if (metadata.cardId === cardId) {
237 const dto: NotificationDTO = {
238 id: notificationData.id,
239 recipientUserId: notificationData.recipientUserId,
240 actorUserId: notificationData.actorUserId,
241 type: notificationData.type,
242 metadata: notificationData.metadata as any,
243 read: notificationData.read,
244 createdAt: notificationData.createdAt,
245 updatedAt: notificationData.updatedAt,
246 };
247
248 const domainResult = NotificationMapper.toDomain(dto);
249 if (domainResult.isErr()) {
250 return err(domainResult.error);
251 }
252
253 matchingNotifications.push(domainResult.value);
254 }
255 }
256
257 return ok(matchingNotifications);
258 } catch (error) {
259 return err(error as Error);
260 }
261 }
262
263 async markAllAsReadForUser(recipientId: CuratorId): Promise<Result<number>> {
264 try {
265 const result = await this.db
266 .update(notifications)
267 .set({
268 read: true,
269 updatedAt: new Date(),
270 })
271 .where(
272 and(
273 eq(notifications.recipientUserId, recipientId.value),
274 eq(notifications.read, false),
275 ),
276 );
277
278 // For PostgreSQL with drizzle-orm, we need to handle the result differently
279 // The result might not have rowCount, so we'll return 0 as a fallback
280 return ok(0);
281 } catch (error) {
282 return err(error as Error);
283 }
284 }
285
286 async findByRecipientEnriched(
287 recipientId: CuratorId,
288 options: NotificationQueryOptions,
289 ): Promise<Result<PaginatedEnrichedNotificationResult>> {
290 try {
291 const { page, limit, unreadOnly } = options;
292 const offset = (page - 1) * limit;
293
294 // Build where conditions for notifications
295 const notificationWhereConditions = [
296 eq(notifications.recipientUserId, recipientId.value),
297 ];
298
299 if (unreadOnly) {
300 notificationWhereConditions.push(eq(notifications.read, false));
301 }
302
303 const notificationWhereClause =
304 notificationWhereConditions.length > 1
305 ? and(...notificationWhereConditions)
306 : notificationWhereConditions[0];
307
308 // Get enriched notifications with card data
309 const enrichedQuery = this.db
310 .select({
311 // Notification fields
312 id: notifications.id,
313 type: notifications.type,
314 read: notifications.read,
315 createdAt: notifications.createdAt,
316 actorUserId: notifications.actorUserId,
317 metadata: notifications.metadata,
318
319 // Card fields
320 cardId: cards.id,
321 cardAuthorId: cards.authorId,
322 cardUrl: cards.url,
323 cardContentData: cards.contentData,
324 cardLibraryCount: cards.libraryCount,
325 cardCreatedAt: cards.createdAt,
326 cardUpdatedAt: cards.updatedAt,
327 })
328 .from(notifications)
329 .innerJoin(cards, eq(cards.id, notifications.metadata))
330 .where(notificationWhereClause)
331 .orderBy(desc(notifications.createdAt))
332 .limit(limit)
333 .offset(offset);
334
335 // Note: We need to extract cardId from metadata since it's stored as JSON
336 // This is a simplified approach - in practice you'd need to handle the JSON extraction
337 const notificationsResult = await this.db
338 .select({
339 id: notifications.id,
340 type: notifications.type,
341 read: notifications.read,
342 createdAt: notifications.createdAt,
343 actorUserId: notifications.actorUserId,
344 metadata: notifications.metadata,
345 })
346 .from(notifications)
347 .where(notificationWhereClause)
348 .orderBy(desc(notifications.createdAt))
349 .limit(limit)
350 .offset(offset);
351
352 if (notificationsResult.length === 0) {
353 // Get counts for empty result
354 const totalCountResult = await this.db
355 .select({ count: count() })
356 .from(notifications)
357 .where(notificationWhereClause);
358
359 const unreadCountResult = await this.db
360 .select({ count: count() })
361 .from(notifications)
362 .where(
363 and(
364 eq(notifications.recipientUserId, recipientId.value),
365 eq(notifications.read, false),
366 ),
367 );
368
369 return ok({
370 notifications: [],
371 totalCount: totalCountResult[0]?.count || 0,
372 hasMore: false,
373 unreadCount: unreadCountResult[0]?.count || 0,
374 });
375 }
376
377 // Extract card IDs from metadata
378 const cardIds = notificationsResult
379 .map((n) => (n.metadata as any)?.cardId)
380 .filter(Boolean);
381
382 if (cardIds.length === 0) {
383 return ok({
384 notifications: [],
385 totalCount: 0,
386 hasMore: false,
387 unreadCount: 0,
388 });
389 }
390
391 // Get card data with URL library counts
392 const cardsQuery = this.db
393 .select({
394 id: cards.id,
395 authorId: cards.authorId,
396 url: cards.url,
397 contentData: cards.contentData,
398 libraryCount: cards.libraryCount,
399 createdAt: cards.createdAt,
400 updatedAt: cards.updatedAt,
401 })
402 .from(cards)
403 .where(
404 and(inArray(cards.id, cardIds), eq(cards.type, CardTypeEnum.URL)),
405 );
406
407 const cardsResult = await cardsQuery;
408
409 // Get URL library counts for these cards
410 const urls = cardsResult.map((card) => card.url).filter(Boolean);
411 const urlLibraryCountsQuery = this.db
412 .select({
413 url: cards.url,
414 count: countDistinct(libraryMemberships.userId),
415 })
416 .from(cards)
417 .innerJoin(libraryMemberships, eq(cards.id, libraryMemberships.cardId))
418 .where(and(eq(cards.type, CardTypeEnum.URL), inArray(cards.url, urls)))
419 .groupBy(cards.url);
420
421 const urlLibraryCountsResult = await urlLibraryCountsQuery;
422 const urlLibraryCountMap = new Map<string, number>();
423 urlLibraryCountsResult.forEach((row) => {
424 if (row.url) {
425 urlLibraryCountMap.set(row.url, row.count);
426 }
427 });
428
429 // Get notes for these cards
430 const notesQuery = this.db
431 .select({
432 id: cards.id,
433 parentCardId: cards.parentCardId,
434 contentData: cards.contentData,
435 })
436 .from(cards)
437 .where(
438 and(
439 eq(cards.type, CardTypeEnum.NOTE),
440 inArray(cards.parentCardId, cardIds),
441 ),
442 );
443
444 const notesResult = await notesQuery;
445
446 // Get collections for these cards
447 const collectionsQuery = this.db
448 .select({
449 cardId: collectionCards.cardId,
450 collectionId: collections.id,
451 collectionUri: collections.publishedRecordId,
452 collectionName: collections.name,
453 collectionDescription: collections.description,
454 collectionAuthorId: collections.authorId,
455 collectionCardCount: collections.cardCount,
456 collectionCreatedAt: collections.createdAt,
457 collectionUpdatedAt: collections.updatedAt,
458 })
459 .from(collectionCards)
460 .innerJoin(
461 collections,
462 eq(collectionCards.collectionId, collections.id),
463 )
464 .where(inArray(collectionCards.cardId, cardIds));
465
466 const collectionsResult = await collectionsQuery;
467
468 // Build card lookup map
469 const cardMap = new Map(cardsResult.map((card) => [card.id, card]));
470
471 // Build enriched notifications
472 const enrichedNotifications: EnrichedNotificationResult[] = [];
473
474 for (const notification of notificationsResult) {
475 const metadata = notification.metadata as any;
476 const cardId = metadata?.cardId;
477
478 if (!cardId) continue;
479
480 const card = cardMap.get(cardId);
481 if (!card) continue;
482
483 const note = notesResult.find((n) => n.parentCardId === cardId);
484 const cardCollections = collectionsResult
485 .filter((c) => c.cardId === cardId)
486 .map((c) => ({
487 id: c.collectionId,
488 uri: c.collectionUri || undefined,
489 name: c.collectionName,
490 description: c.collectionDescription || undefined,
491 authorId: c.collectionAuthorId,
492 cardCount: c.collectionCardCount,
493 createdAt: c.collectionCreatedAt,
494 updatedAt: c.collectionUpdatedAt,
495 }));
496
497 const urlLibraryCount = urlLibraryCountMap.get(card.url || '') || 0;
498
499 enrichedNotifications.push({
500 id: notification.id,
501 type: notification.type,
502 read: notification.read,
503 createdAt: notification.createdAt,
504 actorUserId: notification.actorUserId,
505 cardAuthorId: card.authorId,
506 cardId: card.id,
507 cardUrl: card.url || '',
508 cardTitle: card.contentData?.metadata?.title,
509 cardDescription: card.contentData?.metadata?.description,
510 cardAuthor: card.contentData?.metadata?.author,
511 cardPublishedDate: card.contentData?.metadata?.publishedDate
512 ? new Date(card.contentData.metadata.publishedDate)
513 : undefined,
514 cardSiteName: card.contentData?.metadata?.siteName,
515 cardImageUrl: card.contentData?.metadata?.imageUrl,
516 cardType: card.contentData?.metadata?.type,
517 cardRetrievedAt: card.contentData?.metadata?.retrievedAt
518 ? new Date(card.contentData.metadata.retrievedAt)
519 : undefined,
520 cardDoi: card.contentData?.metadata?.doi,
521 cardIsbn: card.contentData?.metadata?.isbn,
522 cardLibraryCount: card.libraryCount,
523 cardUrlLibraryCount: urlLibraryCount,
524 cardUrlInLibrary: undefined, // Would need calling user ID to determine this
525 cardCreatedAt: card.createdAt,
526 cardUpdatedAt: card.updatedAt,
527 cardNote: note
528 ? {
529 id: note.id,
530 text: note.contentData?.text || '',
531 }
532 : undefined,
533 collections: cardCollections,
534 });
535 }
536
537 // Get total count and unread count
538 const totalCountResult = await this.db
539 .select({ count: count() })
540 .from(notifications)
541 .where(notificationWhereClause);
542
543 const unreadCountResult = await this.db
544 .select({ count: count() })
545 .from(notifications)
546 .where(
547 and(
548 eq(notifications.recipientUserId, recipientId.value),
549 eq(notifications.read, false),
550 ),
551 );
552
553 const totalCount = totalCountResult[0]?.count || 0;
554 const unreadCount = unreadCountResult[0]?.count || 0;
555 const hasMore = offset + enrichedNotifications.length < totalCount;
556
557 return ok({
558 notifications: enrichedNotifications,
559 totalCount,
560 hasMore,
561 unreadCount,
562 });
563 } catch (error) {
564 return err(error as Error);
565 }
566 }
567
568 async delete(id: NotificationId): Promise<Result<void>> {
569 try {
570 await this.db
571 .delete(notifications)
572 .where(eq(notifications.id, id.getStringValue()));
573
574 return ok(undefined);
575 } catch (error) {
576 return err(error as Error);
577 }
578 }
579}