Something went wrong. Try again.
Tea journaling on ATProto (alpha)
Something went wrong. Try again.
18 kB · 542 lines
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543// Package sqlitestore provides SQLite-backed store implementations.package sqlitestore
import ( "context" "database/sql" "encoding/json" "fmt" "time"
"tangled.org/arabica.social/arabica/internal/moderation")
// ModerationStore implements moderation.Store using SQLite.// It shares the database connection with the firehose FeedIndex.type ModerationStore struct { db *sql.DB}
// NewModerationStore creates a ModerationStore backed by the given database.// The database must already have the moderation schema applied.func NewModerationStore(db *sql.DB) *ModerationStore { return &ModerationStore{db: db}}
// Ensure ModerationStore implements the interface at compile time.var _ moderation.Store = (*ModerationStore)(nil)
// ========== Hidden Records ==========
func (s *ModerationStore) HideRecord(ctx context.Context, entry moderation.HiddenRecord) error { autoHidden := 0 if entry.AutoHidden { autoHidden = 1 } _, err := s.db.ExecContext(ctx, ` INSERT INTO moderation_hidden_records (uri, hidden_at, hidden_by, reason, auto_hidden) VALUES (?, ?, ?, ?, ?) ON CONFLICT(uri) DO UPDATE SET hidden_at = excluded.hidden_at, hidden_by = excluded.hidden_by, reason = excluded.reason, auto_hidden = excluded.auto_hidden `, entry.ATURI, entry.HiddenAt.Format(time.RFC3339Nano), entry.HiddenBy, entry.Reason, autoHidden) if err != nil { return fmt.Errorf("hide record: %w", err) } return nil}
func (s *ModerationStore) UnhideRecord(ctx context.Context, atURI string) error { _, err := s.db.ExecContext(ctx, `DELETE FROM moderation_hidden_records WHERE uri = ?`, atURI) return err}
func (s *ModerationStore) IsRecordHidden(ctx context.Context, atURI string) bool { var exists int _ = s.db.QueryRowContext(ctx, `SELECT 1 FROM moderation_hidden_records WHERE uri = ?`, atURI).Scan(&exists) return exists == 1}
func (s *ModerationStore) GetHiddenRecord(ctx context.Context, atURI string) (*moderation.HiddenRecord, error) { var r moderation.HiddenRecord var hiddenAtStr string var autoHidden int err := s.db.QueryRowContext(ctx, ` SELECT uri, hidden_at, hidden_by, reason, auto_hidden FROM moderation_hidden_records WHERE uri = ? `, atURI).Scan(&r.ATURI, &hiddenAtStr, &r.HiddenBy, &r.Reason, &autoHidden) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } r.HiddenAt, _ = time.Parse(time.RFC3339Nano, hiddenAtStr) r.AutoHidden = autoHidden == 1 return &r, nil}
func (s *ModerationStore) ListHiddenRecords(ctx context.Context) ([]moderation.HiddenRecord, error) { rows, err := s.db.QueryContext(ctx, ` SELECT uri, hidden_at, hidden_by, reason, auto_hidden FROM moderation_hidden_records ORDER BY hidden_at DESC `) if err != nil { return nil, err } defer rows.Close()
var records []moderation.HiddenRecord for rows.Next() { var r moderation.HiddenRecord var hiddenAtStr string var autoHidden int if err := rows.Scan(&r.ATURI, &hiddenAtStr, &r.HiddenBy, &r.Reason, &autoHidden); err != nil { continue } r.HiddenAt, _ = time.Parse(time.RFC3339Nano, hiddenAtStr) r.AutoHidden = autoHidden == 1 records = append(records, r) } return records, rows.Err()}
// ListHiddenURIs returns all hidden record URIs for batch filtering.func (s *ModerationStore) ListHiddenURIs(ctx context.Context) ([]string, error) { rows, err := s.db.QueryContext(ctx, `SELECT uri FROM moderation_hidden_records`) if err != nil { return nil, err } defer rows.Close()
var uris []string for rows.Next() { var uri string if err := rows.Scan(&uri); err != nil { continue } uris = append(uris, uri) } return uris, rows.Err()}
// ========== Blacklist ==========
func (s *ModerationStore) BlacklistUser(ctx context.Context, entry moderation.BlacklistedUser) error { _, err := s.db.ExecContext(ctx, ` INSERT INTO moderation_blacklist (did, blacklisted_at, blacklisted_by, reason) VALUES (?, ?, ?, ?) ON CONFLICT(did) DO UPDATE SET blacklisted_at = excluded.blacklisted_at, blacklisted_by = excluded.blacklisted_by, reason = excluded.reason `, entry.DID, entry.BlacklistedAt.Format(time.RFC3339Nano), entry.BlacklistedBy, entry.Reason) if err != nil { return fmt.Errorf("blacklist user: %w", err) } return nil}
func (s *ModerationStore) UnblacklistUser(ctx context.Context, did string) error { _, err := s.db.ExecContext(ctx, `DELETE FROM moderation_blacklist WHERE did = ?`, did) return err}
// ListBlacklistedDIDs returns all blacklisted DIDs for batch filtering.func (s *ModerationStore) ListBlacklistedDIDs(ctx context.Context) ([]string, error) { rows, err := s.db.QueryContext(ctx, `SELECT did FROM moderation_blacklist`) if err != nil { return nil, err } defer rows.Close()
var dids []string for rows.Next() { var did string if err := rows.Scan(&did); err != nil { continue } dids = append(dids, did) } return dids, rows.Err()}
func (s *ModerationStore) IsBlacklisted(ctx context.Context, did string) bool { var exists int _ = s.db.QueryRowContext(ctx, `SELECT 1 FROM moderation_blacklist WHERE did = ?`, did).Scan(&exists) return exists == 1}
func (s *ModerationStore) GetBlacklistedUser(ctx context.Context, did string) (*moderation.BlacklistedUser, error) { var u moderation.BlacklistedUser var blacklistedAtStr string err := s.db.QueryRowContext(ctx, ` SELECT did, blacklisted_at, blacklisted_by, reason FROM moderation_blacklist WHERE did = ? `, did).Scan(&u.DID, &blacklistedAtStr, &u.BlacklistedBy, &u.Reason) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } u.BlacklistedAt, _ = time.Parse(time.RFC3339Nano, blacklistedAtStr) return &u, nil}
func (s *ModerationStore) ListBlacklistedUsers(ctx context.Context) ([]moderation.BlacklistedUser, error) { rows, err := s.db.QueryContext(ctx, ` SELECT did, blacklisted_at, blacklisted_by, reason FROM moderation_blacklist ORDER BY blacklisted_at DESC `) if err != nil { return nil, err } defer rows.Close()
var users []moderation.BlacklistedUser for rows.Next() { var u moderation.BlacklistedUser var blacklistedAtStr string if err := rows.Scan(&u.DID, &blacklistedAtStr, &u.BlacklistedBy, &u.Reason); err != nil { continue } u.BlacklistedAt, _ = time.Parse(time.RFC3339Nano, blacklistedAtStr) users = append(users, u) } return users, rows.Err()}
// ========== Reports ==========
func (s *ModerationStore) CreateReport(ctx context.Context, report moderation.Report) error { _, err := s.db.ExecContext(ctx, ` INSERT INTO moderation_reports (id, subject_uri, subject_did, reporter_did, reason, created_at, status, resolved_by, resolved_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) `, report.ID, report.SubjectURI, report.SubjectDID, report.ReporterDID, report.Reason, report.CreatedAt.Format(time.RFC3339Nano), string(report.Status), report.ResolvedBy, nil) if err != nil { return fmt.Errorf("create report: %w", err) } return nil}
func (s *ModerationStore) GetReport(ctx context.Context, id string) (*moderation.Report, error) { var r moderation.Report var createdAtStr string var resolvedAtStr sql.NullString err := s.db.QueryRowContext(ctx, ` SELECT id, subject_uri, subject_did, reporter_did, reason, created_at, status, resolved_by, resolved_at FROM moderation_reports WHERE id = ? `, id).Scan(&r.ID, &r.SubjectURI, &r.SubjectDID, &r.ReporterDID, &r.Reason, &createdAtStr, &r.Status, &r.ResolvedBy, &resolvedAtStr) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } r.CreatedAt, _ = time.Parse(time.RFC3339Nano, createdAtStr) if resolvedAtStr.Valid { t, _ := time.Parse(time.RFC3339Nano, resolvedAtStr.String) r.ResolvedAt = &t } return &r, nil}
func (s *ModerationStore) ListPendingReports(ctx context.Context) ([]moderation.Report, error) { return s.listReports(ctx, `WHERE status = 'pending' ORDER BY created_at DESC`)}
func (s *ModerationStore) ListAllReports(ctx context.Context) ([]moderation.Report, error) { return s.listReports(ctx, `ORDER BY created_at DESC`)}
func (s *ModerationStore) listReports(ctx context.Context, clause string) ([]moderation.Report, error) { rows, err := s.db.QueryContext(ctx, ` SELECT id, subject_uri, subject_did, reporter_did, reason, created_at, status, resolved_by, resolved_at FROM moderation_reports `+clause) if err != nil { return nil, err } defer rows.Close() return scanReports(rows)}
func scanReports(rows *sql.Rows) ([]moderation.Report, error) { var reports []moderation.Report for rows.Next() { var r moderation.Report var createdAtStr string var resolvedAtStr sql.NullString if err := rows.Scan(&r.ID, &r.SubjectURI, &r.SubjectDID, &r.ReporterDID, &r.Reason, &createdAtStr, &r.Status, &r.ResolvedBy, &resolvedAtStr); err != nil { continue } r.CreatedAt, _ = time.Parse(time.RFC3339Nano, createdAtStr) if resolvedAtStr.Valid { t, _ := time.Parse(time.RFC3339Nano, resolvedAtStr.String) r.ResolvedAt = &t } reports = append(reports, r) } return reports, rows.Err()}
func (s *ModerationStore) ResolveReport(ctx context.Context, id string, status moderation.ReportStatus, resolvedBy string) error { now := time.Now().Format(time.RFC3339Nano) res, err := s.db.ExecContext(ctx, ` UPDATE moderation_reports SET status = ?, resolved_by = ?, resolved_at = ? WHERE id = ? `, string(status), resolvedBy, now, id) if err != nil { return fmt.Errorf("resolve report: %w", err) } n, _ := res.RowsAffected() if n == 0 { return fmt.Errorf("report not found: %s", id) } return nil}
func (s *ModerationStore) CountReportsForURI(ctx context.Context, atURI string) (int, error) { var count int err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM moderation_reports WHERE subject_uri = ?`, atURI).Scan(&count) return count, err}
func (s *ModerationStore) CountReportsForDID(ctx context.Context, did string) (int, error) { var count int err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM moderation_reports WHERE subject_did = ?`, did).Scan(&count) return count, err}
func (s *ModerationStore) CountReportsForDIDSince(ctx context.Context, did string, since time.Time) (int, error) { var count int err := s.db.QueryRowContext(ctx, ` SELECT COUNT(*) FROM moderation_reports WHERE subject_did = ? AND created_at > ? `, did, since.Format(time.RFC3339Nano)).Scan(&count) return count, err}
func (s *ModerationStore) HasReportedURI(ctx context.Context, reporterDID, subjectURI string) (bool, error) { var exists int err := s.db.QueryRowContext(ctx, ` SELECT 1 FROM moderation_reports WHERE reporter_did = ? AND subject_uri = ? LIMIT 1 `, reporterDID, subjectURI).Scan(&exists) if err == sql.ErrNoRows { return false, nil } return exists == 1, err}
func (s *ModerationStore) CountReportsFromUserSince(ctx context.Context, reporterDID string, since time.Time) (int, error) { var count int err := s.db.QueryRowContext(ctx, ` SELECT COUNT(*) FROM moderation_reports WHERE reporter_did = ? AND created_at > ? `, reporterDID, since.Format(time.RFC3339Nano)).Scan(&count) return count, err}
// ========== Audit Log ==========
func (s *ModerationStore) LogAction(ctx context.Context, entry moderation.AuditEntry) error { details, err := json.Marshal(entry.Details) if err != nil { details = []byte("{}") } autoMod := 0 if entry.AutoMod { autoMod = 1 } _, err = s.db.ExecContext(ctx, ` INSERT INTO moderation_audit_log (id, action, actor_did, target_uri, reason, details, timestamp, auto_mod) VALUES (?, ?, ?, ?, ?, ?, ?, ?) `, entry.ID, string(entry.Action), entry.ActorDID, entry.TargetURI, entry.Reason, string(details), entry.Timestamp.Format(time.RFC3339Nano), autoMod) if err != nil { return fmt.Errorf("log action: %w", err) } return nil}
func (s *ModerationStore) ListAuditLog(ctx context.Context, limit int) ([]moderation.AuditEntry, error) { rows, err := s.db.QueryContext(ctx, ` SELECT id, action, actor_did, target_uri, reason, details, timestamp, auto_mod FROM moderation_audit_log ORDER BY timestamp DESC LIMIT ? `, limit) if err != nil { return nil, err } defer rows.Close()
var entries []moderation.AuditEntry for rows.Next() { var e moderation.AuditEntry var timestampStr, detailsStr string var autoMod int if err := rows.Scan(&e.ID, &e.Action, &e.ActorDID, &e.TargetURI, &e.Reason, &detailsStr, ×tampStr, &autoMod); err != nil { continue } e.Timestamp, _ = time.Parse(time.RFC3339Nano, timestampStr) e.AutoMod = autoMod == 1 _ = json.Unmarshal([]byte(detailsStr), &e.Details) entries = append(entries, e) } return entries, rows.Err()}
// ========== Auto-hide Resets ==========
func (s *ModerationStore) SetAutoHideReset(ctx context.Context, did string, resetAt time.Time) error { _, err := s.db.ExecContext(ctx, ` INSERT INTO moderation_autohide_resets (did, reset_at) VALUES (?, ?) ON CONFLICT(did) DO UPDATE SET reset_at = excluded.reset_at `, did, resetAt.Format(time.RFC3339Nano)) if err != nil { return fmt.Errorf("set autohide reset: %w", err) } return nil}
func (s *ModerationStore) GetAutoHideReset(ctx context.Context, did string) (time.Time, error) { var resetAtStr string err := s.db.QueryRowContext(ctx, `SELECT reset_at FROM moderation_autohide_resets WHERE did = ?`, did).Scan(&resetAtStr) if err == sql.ErrNoRows { return time.Time{}, nil } if err != nil { return time.Time{}, err } t, _ := time.Parse(time.RFC3339Nano, resetAtStr) return t, nil}
// ========== Labels ==========
func (s *ModerationStore) AddLabel(ctx context.Context, label moderation.Label) error { var expiresAt *string if label.ExpiresAt != nil { v := label.ExpiresAt.Format(time.RFC3339Nano) expiresAt = &v } _, err := s.db.ExecContext(ctx, ` INSERT INTO moderation_labels (id, entity_type, entity_id, label, value, created_at, created_by, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(entity_type, entity_id, label) DO UPDATE SET id = excluded.id, value = excluded.value, created_at = excluded.created_at, created_by = excluded.created_by, expires_at = excluded.expires_at `, label.ID, label.EntityType, label.EntityID, label.Name, label.Value, label.CreatedAt.Format(time.RFC3339Nano), label.CreatedBy, expiresAt) if err != nil { return fmt.Errorf("add label: %w", err) } return nil}
func (s *ModerationStore) RemoveLabel(ctx context.Context, entityType, entityID, labelName string) error { _, err := s.db.ExecContext(ctx, ` DELETE FROM moderation_labels WHERE entity_type = ? AND entity_id = ? AND label = ? `, entityType, entityID, labelName) return err}
func (s *ModerationStore) HasLabel(ctx context.Context, entityType, entityID, labelName string) (bool, error) { var exists int err := s.db.QueryRowContext(ctx, ` SELECT 1 FROM moderation_labels WHERE entity_type = ? AND entity_id = ? AND label = ? AND (expires_at IS NULL OR expires_at > ?) `, entityType, entityID, labelName, time.Now().Format(time.RFC3339Nano)).Scan(&exists) if err == sql.ErrNoRows { return false, nil } return exists == 1, err}
func (s *ModerationStore) GetLabel(ctx context.Context, entityType, entityID, labelName string) (*moderation.Label, error) { var l moderation.Label var createdAtStr string var expiresAtStr sql.NullString err := s.db.QueryRowContext(ctx, ` SELECT id, entity_type, entity_id, label, value, created_at, created_by, expires_at FROM moderation_labels WHERE entity_type = ? AND entity_id = ? AND label = ? `, entityType, entityID, labelName).Scan( &l.ID, &l.EntityType, &l.EntityID, &l.Name, &l.Value, &createdAtStr, &l.CreatedBy, &expiresAtStr) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } l.CreatedAt, _ = time.Parse(time.RFC3339Nano, createdAtStr) if expiresAtStr.Valid { t, _ := time.Parse(time.RFC3339Nano, expiresAtStr.String) l.ExpiresAt = &t } return &l, nil}
func (s *ModerationStore) ListLabels(ctx context.Context, entityType, entityID string) ([]moderation.Label, error) { rows, err := s.db.QueryContext(ctx, ` SELECT id, entity_type, entity_id, label, value, created_at, created_by, expires_at FROM moderation_labels WHERE entity_type = ? AND entity_id = ? ORDER BY created_at DESC `, entityType, entityID) if err != nil { return nil, err } defer rows.Close() return scanLabels(rows)}
func (s *ModerationStore) ListAllLabels(ctx context.Context) ([]moderation.Label, error) { rows, err := s.db.QueryContext(ctx, ` SELECT id, entity_type, entity_id, label, value, created_at, created_by, expires_at FROM moderation_labels ORDER BY created_at DESC `) if err != nil { return nil, err } defer rows.Close() return scanLabels(rows)}
func scanLabels(rows *sql.Rows) ([]moderation.Label, error) { var labels []moderation.Label for rows.Next() { var l moderation.Label var createdAtStr string var expiresAtStr sql.NullString if err := rows.Scan( &l.ID, &l.EntityType, &l.EntityID, &l.Name, &l.Value, &createdAtStr, &l.CreatedBy, &expiresAtStr); err != nil { continue } l.CreatedAt, _ = time.Parse(time.RFC3339Nano, createdAtStr) if expiresAtStr.Valid { t, _ := time.Parse(time.RFC3339Nano, expiresAtStr.String) l.ExpiresAt = &t } labels = append(labels, l) } return labels, rows.Err()}
func (s *ModerationStore) CleanExpiredLabels(ctx context.Context) (int, error) { res, err := s.db.ExecContext(ctx, ` DELETE FROM moderation_labels WHERE expires_at IS NOT NULL AND expires_at < ? `, time.Now().Format(time.RFC3339Nano)) if err != nil { return 0, err } n, _ := res.RowsAffected() return int(n), nil}