A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "strings"
8
9 "Coves/internal/core/communities"
10 "github.com/lib/pq"
11)
12
13type postgresCommunityRepo struct {
14 db *sql.DB
15}
16
17// NewCommunityRepository creates a new PostgreSQL community repository
18func NewCommunityRepository(db *sql.DB) communities.Repository {
19 return &postgresCommunityRepo{db: db}
20}
21
22// Create inserts a new community into the communities table
23func (r *postgresCommunityRepo) Create(ctx context.Context, community *communities.Community) (*communities.Community, error) {
24 query := `
25 INSERT INTO communities (
26 did, handle, name, display_name, description, description_facets,
27 avatar_cid, banner_cid, owner_did, created_by_did, hosted_by_did,
28 visibility, allow_external_discovery, moderation_type, content_warnings,
29 member_count, subscriber_count, post_count,
30 federated_from, federated_id, created_at, updated_at,
31 record_uri, record_cid
32 ) VALUES (
33 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15,
34 $16, $17, $18, $19, $20, $21, $22, $23, $24
35 )
36 RETURNING id, created_at, updated_at`
37
38 // Handle JSONB field - use sql.NullString with valid JSON or NULL
39 var descFacets interface{}
40 if community.DescriptionFacets != nil && len(community.DescriptionFacets) > 0 {
41 descFacets = community.DescriptionFacets
42 } else {
43 descFacets = nil
44 }
45
46 err := r.db.QueryRowContext(ctx, query,
47 community.DID,
48 community.Handle,
49 community.Name,
50 nullString(community.DisplayName),
51 nullString(community.Description),
52 descFacets,
53 nullString(community.AvatarCID),
54 nullString(community.BannerCID),
55 community.OwnerDID,
56 community.CreatedByDID,
57 community.HostedByDID,
58 community.Visibility,
59 community.AllowExternalDiscovery,
60 nullString(community.ModerationType),
61 pq.Array(community.ContentWarnings),
62 community.MemberCount,
63 community.SubscriberCount,
64 community.PostCount,
65 nullString(community.FederatedFrom),
66 nullString(community.FederatedID),
67 community.CreatedAt,
68 community.UpdatedAt,
69 nullString(community.RecordURI),
70 nullString(community.RecordCID),
71 ).Scan(&community.ID, &community.CreatedAt, &community.UpdatedAt)
72
73 if err != nil {
74 // Check for unique constraint violations
75 if strings.Contains(err.Error(), "duplicate key") {
76 if strings.Contains(err.Error(), "communities_did_key") {
77 return nil, communities.ErrCommunityAlreadyExists
78 }
79 if strings.Contains(err.Error(), "communities_handle_key") {
80 return nil, communities.ErrHandleTaken
81 }
82 }
83 return nil, fmt.Errorf("failed to create community: %w", err)
84 }
85
86 return community, nil
87}
88
89// GetByDID retrieves a community by its DID
90func (r *postgresCommunityRepo) GetByDID(ctx context.Context, did string) (*communities.Community, error) {
91 community := &communities.Community{}
92 query := `
93 SELECT id, did, handle, name, display_name, description, description_facets,
94 avatar_cid, banner_cid, owner_did, created_by_did, hosted_by_did,
95 visibility, allow_external_discovery, moderation_type, content_warnings,
96 member_count, subscriber_count, post_count,
97 federated_from, federated_id, created_at, updated_at,
98 record_uri, record_cid
99 FROM communities
100 WHERE did = $1`
101
102 var displayName, description, avatarCID, bannerCID, moderationType sql.NullString
103 var federatedFrom, federatedID, recordURI, recordCID sql.NullString
104 var descFacets []byte
105 var contentWarnings []string
106
107 err := r.db.QueryRowContext(ctx, query, did).Scan(
108 &community.ID, &community.DID, &community.Handle, &community.Name,
109 &displayName, &description, &descFacets,
110 &avatarCID, &bannerCID,
111 &community.OwnerDID, &community.CreatedByDID, &community.HostedByDID,
112 &community.Visibility, &community.AllowExternalDiscovery,
113 &moderationType, pq.Array(&contentWarnings),
114 &community.MemberCount, &community.SubscriberCount, &community.PostCount,
115 &federatedFrom, &federatedID,
116 &community.CreatedAt, &community.UpdatedAt,
117 &recordURI, &recordCID,
118 )
119
120 if err == sql.ErrNoRows {
121 return nil, communities.ErrCommunityNotFound
122 }
123 if err != nil {
124 return nil, fmt.Errorf("failed to get community by DID: %w", err)
125 }
126
127 // Map nullable fields
128 community.DisplayName = displayName.String
129 community.Description = description.String
130 community.AvatarCID = avatarCID.String
131 community.BannerCID = bannerCID.String
132 community.ModerationType = moderationType.String
133 community.ContentWarnings = contentWarnings
134 community.FederatedFrom = federatedFrom.String
135 community.FederatedID = federatedID.String
136 community.RecordURI = recordURI.String
137 community.RecordCID = recordCID.String
138 if descFacets != nil {
139 community.DescriptionFacets = descFacets
140 }
141
142 return community, nil
143}
144
145// GetByHandle retrieves a community by its scoped handle
146func (r *postgresCommunityRepo) GetByHandle(ctx context.Context, handle string) (*communities.Community, error) {
147 community := &communities.Community{}
148 query := `
149 SELECT id, did, handle, name, display_name, description, description_facets,
150 avatar_cid, banner_cid, owner_did, created_by_did, hosted_by_did,
151 visibility, allow_external_discovery, moderation_type, content_warnings,
152 member_count, subscriber_count, post_count,
153 federated_from, federated_id, created_at, updated_at,
154 record_uri, record_cid
155 FROM communities
156 WHERE handle = $1`
157
158 var displayName, description, avatarCID, bannerCID, moderationType sql.NullString
159 var federatedFrom, federatedID, recordURI, recordCID sql.NullString
160 var descFacets []byte
161 var contentWarnings []string
162
163 err := r.db.QueryRowContext(ctx, query, handle).Scan(
164 &community.ID, &community.DID, &community.Handle, &community.Name,
165 &displayName, &description, &descFacets,
166 &avatarCID, &bannerCID,
167 &community.OwnerDID, &community.CreatedByDID, &community.HostedByDID,
168 &community.Visibility, &community.AllowExternalDiscovery,
169 &moderationType, pq.Array(&contentWarnings),
170 &community.MemberCount, &community.SubscriberCount, &community.PostCount,
171 &federatedFrom, &federatedID,
172 &community.CreatedAt, &community.UpdatedAt,
173 &recordURI, &recordCID,
174 )
175
176 if err == sql.ErrNoRows {
177 return nil, communities.ErrCommunityNotFound
178 }
179 if err != nil {
180 return nil, fmt.Errorf("failed to get community by handle: %w", err)
181 }
182
183 // Map nullable fields
184 community.DisplayName = displayName.String
185 community.Description = description.String
186 community.AvatarCID = avatarCID.String
187 community.BannerCID = bannerCID.String
188 community.ModerationType = moderationType.String
189 community.ContentWarnings = contentWarnings
190 community.FederatedFrom = federatedFrom.String
191 community.FederatedID = federatedID.String
192 community.RecordURI = recordURI.String
193 community.RecordCID = recordCID.String
194 if descFacets != nil {
195 community.DescriptionFacets = descFacets
196 }
197
198 return community, nil
199}
200
201// Update modifies an existing community's metadata
202func (r *postgresCommunityRepo) Update(ctx context.Context, community *communities.Community) (*communities.Community, error) {
203 query := `
204 UPDATE communities
205 SET display_name = $2, description = $3, description_facets = $4,
206 avatar_cid = $5, banner_cid = $6,
207 visibility = $7, allow_external_discovery = $8,
208 moderation_type = $9, content_warnings = $10,
209 updated_at = NOW(),
210 record_uri = $11, record_cid = $12
211 WHERE did = $1
212 RETURNING updated_at`
213
214 // Handle JSONB field - use sql.NullString with valid JSON or NULL
215 var descFacets interface{}
216 if community.DescriptionFacets != nil && len(community.DescriptionFacets) > 0 {
217 descFacets = community.DescriptionFacets
218 } else {
219 descFacets = nil
220 }
221
222 err := r.db.QueryRowContext(ctx, query,
223 community.DID,
224 nullString(community.DisplayName),
225 nullString(community.Description),
226 descFacets,
227 nullString(community.AvatarCID),
228 nullString(community.BannerCID),
229 community.Visibility,
230 community.AllowExternalDiscovery,
231 nullString(community.ModerationType),
232 pq.Array(community.ContentWarnings),
233 nullString(community.RecordURI),
234 nullString(community.RecordCID),
235 ).Scan(&community.UpdatedAt)
236
237 if err == sql.ErrNoRows {
238 return nil, communities.ErrCommunityNotFound
239 }
240 if err != nil {
241 return nil, fmt.Errorf("failed to update community: %w", err)
242 }
243
244 return community, nil
245}
246
247// Delete removes a community from the database
248func (r *postgresCommunityRepo) Delete(ctx context.Context, did string) error {
249 query := `DELETE FROM communities WHERE did = $1`
250
251 result, err := r.db.ExecContext(ctx, query, did)
252 if err != nil {
253 return fmt.Errorf("failed to delete community: %w", err)
254 }
255
256 rowsAffected, err := result.RowsAffected()
257 if err != nil {
258 return fmt.Errorf("failed to check delete result: %w", err)
259 }
260
261 if rowsAffected == 0 {
262 return communities.ErrCommunityNotFound
263 }
264
265 return nil
266}
267
268// List retrieves communities with filtering and pagination
269func (r *postgresCommunityRepo) List(ctx context.Context, req communities.ListCommunitiesRequest) ([]*communities.Community, int, error) {
270 // Build query with filters
271 whereClauses := []string{}
272 args := []interface{}{}
273 argCount := 1
274
275 if req.Visibility != "" {
276 whereClauses = append(whereClauses, fmt.Sprintf("visibility = $%d", argCount))
277 args = append(args, req.Visibility)
278 argCount++
279 }
280
281 if req.HostedBy != "" {
282 whereClauses = append(whereClauses, fmt.Sprintf("hosted_by_did = $%d", argCount))
283 args = append(args, req.HostedBy)
284 argCount++
285 }
286
287 whereClause := ""
288 if len(whereClauses) > 0 {
289 whereClause = "WHERE " + strings.Join(whereClauses, " AND ")
290 }
291
292 // Get total count
293 countQuery := fmt.Sprintf("SELECT COUNT(*) FROM communities %s", whereClause)
294 var totalCount int
295 err := r.db.QueryRowContext(ctx, countQuery, args...).Scan(&totalCount)
296 if err != nil {
297 return nil, 0, fmt.Errorf("failed to count communities: %w", err)
298 }
299
300 // Build sort clause
301 sortColumn := "created_at"
302 if req.SortBy != "" {
303 switch req.SortBy {
304 case "member_count", "subscriber_count", "post_count", "created_at":
305 sortColumn = req.SortBy
306 }
307 }
308
309 sortOrder := "DESC"
310 if strings.ToUpper(req.SortOrder) == "ASC" {
311 sortOrder = "ASC"
312 }
313
314 // Get communities with pagination
315 query := fmt.Sprintf(`
316 SELECT id, did, handle, name, display_name, description, description_facets,
317 avatar_cid, banner_cid, owner_did, created_by_did, hosted_by_did,
318 visibility, allow_external_discovery, moderation_type, content_warnings,
319 member_count, subscriber_count, post_count,
320 federated_from, federated_id, created_at, updated_at,
321 record_uri, record_cid
322 FROM communities
323 %s
324 ORDER BY %s %s
325 LIMIT $%d OFFSET $%d`,
326 whereClause, sortColumn, sortOrder, argCount, argCount+1)
327
328 args = append(args, req.Limit, req.Offset)
329
330 rows, err := r.db.QueryContext(ctx, query, args...)
331 if err != nil {
332 return nil, 0, fmt.Errorf("failed to list communities: %w", err)
333 }
334 defer rows.Close()
335
336 result := []*communities.Community{}
337 for rows.Next() {
338 community := &communities.Community{}
339 var displayName, description, avatarCID, bannerCID, moderationType sql.NullString
340 var federatedFrom, federatedID, recordURI, recordCID sql.NullString
341 var descFacets []byte
342 var contentWarnings []string
343
344 err := rows.Scan(
345 &community.ID, &community.DID, &community.Handle, &community.Name,
346 &displayName, &description, &descFacets,
347 &avatarCID, &bannerCID,
348 &community.OwnerDID, &community.CreatedByDID, &community.HostedByDID,
349 &community.Visibility, &community.AllowExternalDiscovery,
350 &moderationType, pq.Array(&contentWarnings),
351 &community.MemberCount, &community.SubscriberCount, &community.PostCount,
352 &federatedFrom, &federatedID,
353 &community.CreatedAt, &community.UpdatedAt,
354 &recordURI, &recordCID,
355 )
356 if err != nil {
357 return nil, 0, fmt.Errorf("failed to scan community: %w", err)
358 }
359
360 // Map nullable fields
361 community.DisplayName = displayName.String
362 community.Description = description.String
363 community.AvatarCID = avatarCID.String
364 community.BannerCID = bannerCID.String
365 community.ModerationType = moderationType.String
366 community.ContentWarnings = contentWarnings
367 community.FederatedFrom = federatedFrom.String
368 community.FederatedID = federatedID.String
369 community.RecordURI = recordURI.String
370 community.RecordCID = recordCID.String
371 if descFacets != nil {
372 community.DescriptionFacets = descFacets
373 }
374
375 result = append(result, community)
376 }
377
378 if err = rows.Err(); err != nil {
379 return nil, 0, fmt.Errorf("error iterating communities: %w", err)
380 }
381
382 return result, totalCount, nil
383}
384
385// Search searches communities by name/description using fuzzy matching
386func (r *postgresCommunityRepo) Search(ctx context.Context, req communities.SearchCommunitiesRequest) ([]*communities.Community, int, error) {
387 // Build query with fuzzy search and visibility filter
388 whereClauses := []string{
389 "(name ILIKE '%' || $1 || '%' OR description ILIKE '%' || $1 || '%')",
390 }
391 args := []interface{}{req.Query}
392 argCount := 2
393
394 if req.Visibility != "" {
395 whereClauses = append(whereClauses, fmt.Sprintf("visibility = $%d", argCount))
396 args = append(args, req.Visibility)
397 argCount++
398 }
399
400 whereClause := "WHERE " + strings.Join(whereClauses, " AND ")
401
402 // Get total count
403 countQuery := fmt.Sprintf("SELECT COUNT(*) FROM communities %s", whereClause)
404 var totalCount int
405 err := r.db.QueryRowContext(ctx, countQuery, args...).Scan(&totalCount)
406 if err != nil {
407 return nil, 0, fmt.Errorf("failed to count search results: %w", err)
408 }
409
410 // Search with relevance ranking using pg_trgm similarity
411 // Filter out results with very low relevance (< 0.2) to avoid noise
412 query := fmt.Sprintf(`
413 SELECT id, did, handle, name, display_name, description, description_facets,
414 avatar_cid, banner_cid, owner_did, created_by_did, hosted_by_did,
415 visibility, allow_external_discovery, moderation_type, content_warnings,
416 member_count, subscriber_count, post_count,
417 federated_from, federated_id, created_at, updated_at,
418 record_uri, record_cid,
419 similarity(name, $1) + similarity(COALESCE(description, ''), $1) as relevance
420 FROM communities
421 %s AND (similarity(name, $1) + similarity(COALESCE(description, ''), $1)) > 0.2
422 ORDER BY relevance DESC, member_count DESC
423 LIMIT $%d OFFSET $%d`,
424 whereClause, argCount, argCount+1)
425
426 args = append(args, req.Limit, req.Offset)
427
428 rows, err := r.db.QueryContext(ctx, query, args...)
429 if err != nil {
430 return nil, 0, fmt.Errorf("failed to search communities: %w", err)
431 }
432 defer rows.Close()
433
434 result := []*communities.Community{}
435 for rows.Next() {
436 community := &communities.Community{}
437 var displayName, description, avatarCID, bannerCID, moderationType sql.NullString
438 var federatedFrom, federatedID, recordURI, recordCID sql.NullString
439 var descFacets []byte
440 var contentWarnings []string
441 var relevance float64
442
443 err := rows.Scan(
444 &community.ID, &community.DID, &community.Handle, &community.Name,
445 &displayName, &description, &descFacets,
446 &avatarCID, &bannerCID,
447 &community.OwnerDID, &community.CreatedByDID, &community.HostedByDID,
448 &community.Visibility, &community.AllowExternalDiscovery,
449 &moderationType, pq.Array(&contentWarnings),
450 &community.MemberCount, &community.SubscriberCount, &community.PostCount,
451 &federatedFrom, &federatedID,
452 &community.CreatedAt, &community.UpdatedAt,
453 &recordURI, &recordCID,
454 &relevance,
455 )
456 if err != nil {
457 return nil, 0, fmt.Errorf("failed to scan community: %w", err)
458 }
459
460 // Map nullable fields
461 community.DisplayName = displayName.String
462 community.Description = description.String
463 community.AvatarCID = avatarCID.String
464 community.BannerCID = bannerCID.String
465 community.ModerationType = moderationType.String
466 community.ContentWarnings = contentWarnings
467 community.FederatedFrom = federatedFrom.String
468 community.FederatedID = federatedID.String
469 community.RecordURI = recordURI.String
470 community.RecordCID = recordCID.String
471 if descFacets != nil {
472 community.DescriptionFacets = descFacets
473 }
474
475 result = append(result, community)
476 }
477
478 if err = rows.Err(); err != nil {
479 return nil, 0, fmt.Errorf("error iterating search results: %w", err)
480 }
481
482 return result, totalCount, nil
483}
484
485// Helper functions
486func nullString(s string) sql.NullString {
487 return sql.NullString{String: s, Valid: s != ""}
488}
489
490func nullBytes(b []byte) []byte {
491 if b == nil || len(b) == 0 {
492 return nil
493 }
494 return b
495}