Something went wrong. Try again.
forked from scottlanoue.com/atmosphere-mail
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662// SPDX-License-Identifier: AGPL-3.0-or-later
package store
import ( "context" "database/sql" "encoding/json" "fmt" "log" "strings" "sync" "time"
_ "modernc.org/sqlite")
type Label struct { Seq int64 Src string URI string Val string Cts string Neg bool Sig []byte RawCBOR []byte}
type Attestation struct { DID string RKey string // atproto record key Domain string DKIMSelectors []string RelayMember bool Verified bool LastVerified time.Time CreatedAt time.Time
// Reconciliation lifecycle state (internal ref). `verified` above is // retained as the projection `verified == (Status == "verified")`; the // reconciler drives Status as the richer source of truth and keeps the // two in lock-step via UpdateVerificationState. Status string // "pending" | "verified" | "failed" FailureKind string // "" | "transient" | "action_required" (only meaningful when Status=="failed") AttemptCount int // consecutive verification attempts since last success NextCheckAt time.Time // when the reconciler should next evaluate; zero == due now LastError string // specific human-readable reason for the latest failure}
// VerificationState is the atomic state-update payload written by the// reconciler (and the manual reverify path) for a single attestation. One// UpdateVerificationState call sets every reconciliation column and keeps the// legacy verified/last_verified projection in lock-step with Status.type VerificationState struct { Status string // "pending" | "verified" | "failed" FailureKind string // "" | "transient" | "action_required" LastError string // human-readable reason; cleared on success AttemptCount int // absolute value to store (caller computes increment/reset) NextCheckAt time.Time // next reconciler evaluation; zero == due now}
// LabelVersion is the atproto label spec version.const LabelVersion int64 = 1
type Store struct { db *sql.DB
// Label insertion notification for WebSocket subscribers. notifyMu sync.Mutex notifyCh chan struct{}}
func New(dsn string) (*Store, error) { // Mirror relaystore.NewWithPIIKey — bake the PRAGMAs into the DSN // so every pooled connection gets them, not just the first. // See internal/relaystore/dsn.go for the rationale. dsn = withSQLitePragmas(dsn, "busy_timeout(5000)", "journal_mode(WAL)", "foreign_keys(ON)", ) db, err := sql.Open("sqlite", dsn) if err != nil { return nil, fmt.Errorf("open sqlite: %w", err) } if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil { db.Close() return nil, fmt.Errorf("set WAL mode: %w", err) } if _, err := db.Exec("PRAGMA busy_timeout = 5000"); err != nil { db.Close() return nil, fmt.Errorf("set busy timeout: %w", err) } if _, err := db.Exec("PRAGMA foreign_keys=ON"); err != nil { db.Close() return nil, fmt.Errorf("enable foreign keys: %w", err) } s := &Store{db: db, notifyCh: make(chan struct{})} if err := s.migrate(); err != nil { db.Close() return nil, fmt.Errorf("migrate: %w", err) } return s, nil}
func (s *Store) Close() error { return s.db.Close()}
// LabelNotify returns a channel that is closed when a new label is inserted.// Callers should call LabelNotify again after each close to get the next signal.func (s *Store) LabelNotify() <-chan struct{} { s.notifyMu.Lock() defer s.notifyMu.Unlock() return s.notifyCh}
func (s *Store) notifyLabelInserted() { s.notifyMu.Lock() close(s.notifyCh) s.notifyCh = make(chan struct{}) s.notifyMu.Unlock()}
func (s *Store) migrate() error { _, err := s.db.Exec(` CREATE TABLE IF NOT EXISTS labels ( seq INTEGER PRIMARY KEY AUTOINCREMENT, src TEXT NOT NULL, uri TEXT NOT NULL, val TEXT NOT NULL, cts TEXT NOT NULL, neg INTEGER NOT NULL DEFAULT 0, sig BLOB NOT NULL, raw_cbor BLOB NOT NULL ); CREATE INDEX IF NOT EXISTS idx_labels_uri ON labels(uri); CREATE INDEX IF NOT EXISTS idx_labels_src ON labels(src); CREATE INDEX IF NOT EXISTS idx_labels_uri_neg_seq ON labels(uri, neg, seq);
CREATE TABLE IF NOT EXISTS attestations ( did TEXT NOT NULL, domain TEXT NOT NULL, rkey TEXT NOT NULL DEFAULT '', dkim_selectors TEXT NOT NULL DEFAULT '[]', relay_member INTEGER NOT NULL DEFAULT 0, verified INTEGER NOT NULL DEFAULT 0, last_verified TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending', failure_kind TEXT NOT NULL DEFAULT '', attempt_count INTEGER NOT NULL DEFAULT 0, next_check_at TEXT NOT NULL DEFAULT '', last_error TEXT NOT NULL DEFAULT '', PRIMARY KEY (did, domain) ); CREATE INDEX IF NOT EXISTS idx_attestations_did ON attestations(did);
CREATE TABLE IF NOT EXISTS cursor ( id INTEGER PRIMARY KEY CHECK (id = 1), cursor INTEGER NOT NULL DEFAULT 0 ); INSERT OR IGNORE INTO cursor (id, cursor) VALUES (1, 0); `) if err != nil { return err } return s.migrateAttestationLifecycle()}
// migrateAttestationLifecycle adds the reconciliation columns (internal ref)// to attestations tables created before they existed, then backfills the// status/next_check_at projection from the legacy verified bool. The CREATE// TABLE above already includes the columns for fresh databases; this path// upgrades existing ones.//// SQLite has no "ADD COLUMN IF NOT EXISTS", so each ALTER is run independently// and the specific "duplicate column name" error is swallowed — making the// migration idempotent across restarts. Any other error is fatal.func (s *Store) migrateAttestationLifecycle() error { alters := []string{ `ALTER TABLE attestations ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'`, `ALTER TABLE attestations ADD COLUMN failure_kind TEXT NOT NULL DEFAULT ''`, `ALTER TABLE attestations ADD COLUMN attempt_count INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE attestations ADD COLUMN next_check_at TEXT NOT NULL DEFAULT ''`, `ALTER TABLE attestations ADD COLUMN last_error TEXT NOT NULL DEFAULT ''`, } added := false for _, stmt := range alters { if _, err := s.db.Exec(stmt); err != nil { if strings.Contains(err.Error(), "duplicate column name") { continue // column already present — fresh DB or prior migration } return fmt.Errorf("migrate attestations lifecycle: %w", err) } added = true }
// Index on next_check_at backs the reconciler's due-query. Created // unconditionally (IF NOT EXISTS) so it exists for fresh DBs too. if _, err := s.db.Exec( `CREATE INDEX IF NOT EXISTS idx_attestations_next_check_at ON attestations(next_check_at)`, ); err != nil { return fmt.Errorf("create next_check_at index: %w", err) }
if !added { return nil // columns already existed → backfill already ran on a prior open }
// Backfill the lifecycle projection from the legacy verified bool, run // exactly once (only when this open actually added columns): // verified=1 → status='verified' // verified=0 → status='pending', next_check_at='' (due immediately, so // the reconciler heals currently-stuck domains on first tick) if _, err := s.db.Exec( `UPDATE attestations SET status = 'verified' WHERE verified = 1`, ); err != nil { return fmt.Errorf("backfill verified status: %w", err) } if _, err := s.db.Exec( `UPDATE attestations SET status = 'pending', next_check_at = '' WHERE verified = 0`, ); err != nil { return fmt.Errorf("backfill pending status: %w", err) } return nil}
// --- Labels ---
func (s *Store) InsertLabel(ctx context.Context, l *Label) (int64, error) { res, err := s.db.ExecContext(ctx, `INSERT INTO labels (src, uri, val, cts, neg, sig, raw_cbor) VALUES (?, ?, ?, ?, ?, ?, ?)`, l.Src, l.URI, l.Val, l.Cts, boolToInt(l.Neg), l.Sig, l.RawCBOR, ) if err != nil { return 0, fmt.Errorf("insert label: %w", err) } s.notifyLabelInserted() return res.LastInsertId()}
// GetLabelsByURIPatterns queries labels matching any of the given URI patterns// in a single query with correct global pagination. Wildcards (*) in patterns// are converted to SQL LIKE patterns (%).func (s *Store) GetLabelsByURIPatterns(ctx context.Context, patterns []string, cursor string, limit int) ([]Label, string, error) { if len(patterns) == 0 { return nil, "", nil }
var conditions []string var args []any for _, p := range patterns { if strings.Contains(p, "*") { escaped := strings.ReplaceAll(p, `\`, `\\`) escaped = strings.ReplaceAll(escaped, "%", `\%`) escaped = strings.ReplaceAll(escaped, "_", `\_`) likePattern := strings.ReplaceAll(escaped, "*", "%") conditions = append(conditions, `uri LIKE ? ESCAPE '\'`) args = append(args, likePattern) } else { conditions = append(conditions, "uri = ?") args = append(args, p) } }
query := `SELECT seq, src, uri, val, cts, neg, sig, raw_cbor FROM labels WHERE (` + strings.Join(conditions, " OR ") + `)` if cursor != "" { query += ` AND seq > ?` args = append(args, cursor) } query += ` ORDER BY seq ASC LIMIT ?` args = append(args, limit)
return s.queryLabels(ctx, query, args)}
func (s *Store) GetLabelsSince(ctx context.Context, seq int64, limit int) ([]Label, error) { query := `SELECT seq, src, uri, val, cts, neg, sig, raw_cbor FROM labels WHERE seq > ? ORDER BY seq ASC LIMIT ?` labels, _, err := s.queryLabels(ctx, query, []any{seq, limit}) return labels, err}
// GetActiveLabelsForDID returns labels currently active for the given DID.// INVARIANT: label URI always equals the subject DID (set by ReconcileLabels).// This allows us to query by URI = DID. If labels are ever created with a// different URI format (e.g. AT-URI), this query must be updated.func (s *Store) GetActiveLabelsForDID(ctx context.Context, did string) ([]Label, error) { // A label is active if there is no later negation for the same (src, uri, val). query := ` SELECT l.seq, l.src, l.uri, l.val, l.cts, l.neg, l.sig, l.raw_cbor FROM labels l WHERE l.uri = ? AND l.neg = 0 AND NOT EXISTS ( SELECT 1 FROM labels n WHERE n.uri = l.uri AND n.src = l.src AND n.val = l.val AND n.neg = 1 AND n.seq > l.seq ) ORDER BY l.seq ASC ` labels, _, err := s.queryLabels(ctx, query, []any{did}) return labels, err}
func (s *Store) queryLabels(ctx context.Context, query string, args []any) ([]Label, string, error) { rows, err := s.db.QueryContext(ctx, query, args...) if err != nil { return nil, "", fmt.Errorf("query labels: %w", err) } defer rows.Close()
var labels []Label var lastSeq int64 for rows.Next() { var l Label var neg int if err := rows.Scan(&l.Seq, &l.Src, &l.URI, &l.Val, &l.Cts, &neg, &l.Sig, &l.RawCBOR); err != nil { return nil, "", fmt.Errorf("scan label: %w", err) } l.Neg = neg != 0 lastSeq = l.Seq labels = append(labels, l) }
var nextCursor string if lastSeq > 0 { nextCursor = fmt.Sprintf("%d", lastSeq) } return labels, nextCursor, rows.Err()}
// --- Attestations ---
func (s *Store) UpsertAttestation(ctx context.Context, a *Attestation) error { selectors, err := json.Marshal(a.DKIMSelectors) if err != nil { return fmt.Errorf("marshal selectors: %w", err) } status := a.Status if status == "" { status = "pending" } _, err = s.db.ExecContext(ctx, `INSERT INTO attestations (did, domain, rkey, dkim_selectors, relay_member, verified, last_verified, created_at, status, failure_kind, attempt_count, next_check_at, last_error) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(did, domain) DO UPDATE SET rkey = excluded.rkey, dkim_selectors = excluded.dkim_selectors, relay_member = excluded.relay_member, verified = excluded.verified, last_verified = excluded.last_verified, status = excluded.status, failure_kind = excluded.failure_kind, attempt_count = excluded.attempt_count, next_check_at = excluded.next_check_at, last_error = excluded.last_error`, a.DID, a.Domain, a.RKey, string(selectors), boolToInt(a.RelayMember), boolToInt(a.Verified), formatTime(a.LastVerified), formatTime(a.CreatedAt), status, a.FailureKind, a.AttemptCount, formatTime(a.NextCheckAt), a.LastError, ) if err != nil { return fmt.Errorf("upsert attestation: %w", err) } return nil}
// attestationColumns is the canonical SELECT column list for attestation rows.// Every read path uses it so the order matches scanAttestationFrom exactly.const attestationColumns = `did, domain, rkey, dkim_selectors, relay_member, verified, last_verified, created_at, status, failure_kind, attempt_count, next_check_at, last_error`
func (s *Store) GetAttestation(ctx context.Context, did, domain string) (*Attestation, error) { row := s.db.QueryRowContext(ctx, `SELECT `+attestationColumns+` FROM attestations WHERE did = ? AND domain = ?`, did, domain, ) return scanAttestationFrom(row)}
func (s *Store) DeleteAttestation(ctx context.Context, did, domain string) error { _, err := s.db.ExecContext(ctx, `DELETE FROM attestations WHERE did = ? AND domain = ?`, did, domain, ) return err}
// DeleteAttestationByRKey deletes an attestation by its atproto record key.// Used for Jetstream delete events where only the rkey is available.func (s *Store) DeleteAttestationByRKey(ctx context.Context, did, rkey string) error { _, err := s.db.ExecContext(ctx, `DELETE FROM attestations WHERE did = ? AND rkey = ?`, did, rkey, ) return err}
// DeleteAllAttestationsForDID removes every attestation row owned by a// DID. Used by the /admin/revoke endpoint when the relay tells the// labeler that a member account has been deleted (internal ref) —// without this, the reverify scheduler would re-issue labels on its// next pass against the still-valid DNS records.func (s *Store) DeleteAllAttestationsForDID(ctx context.Context, did string) (int, error) { res, err := s.db.ExecContext(ctx, `DELETE FROM attestations WHERE did = ?`, did, ) if err != nil { return 0, fmt.Errorf("delete attestations for DID: %w", err) } n, err := res.RowsAffected() if err != nil { return 0, fmt.Errorf("rows affected: %w", err) } return int(n), nil}
// GetAttestationsForDID returns all attestations for a given DID.// Used by ReconcileLabels to compute the desired label set.func (s *Store) GetAttestationsForDID(ctx context.Context, did string) ([]Attestation, error) { rows, err := s.db.QueryContext(ctx, `SELECT `+attestationColumns+` FROM attestations WHERE did = ?`, did, ) if err != nil { return nil, fmt.Errorf("get attestations for DID: %w", err) } defer rows.Close()
var atts []Attestation for rows.Next() { a, err := scanAttestationFrom(rows) if err != nil { return nil, err } atts = append(atts, *a) } return atts, rows.Err()}
func (s *Store) ListAttestations(ctx context.Context) ([]Attestation, error) { rows, err := s.db.QueryContext(ctx, `SELECT `+attestationColumns+` FROM attestations`, ) if err != nil { return nil, fmt.Errorf("list attestations: %w", err) } defer rows.Close()
var atts []Attestation for rows.Next() { a, err := scanAttestationFrom(rows) if err != nil { return nil, err } atts = append(atts, *a) } return atts, rows.Err()}
// CountAttestationsByStatus returns the number of attestation rows grouped by// their lifecycle status ("pending" | "verified" | "failed"). The reconciler// samples this each tick to drive the comail_attestations_by_status gauge// (internal ref). The returned map only contains statuses that have at least// one row; callers wanting a zeroed series for absent statuses must seed them.func (s *Store) CountAttestationsByStatus(ctx context.Context) (map[string]int, error) { rows, err := s.db.QueryContext(ctx, `SELECT status, COUNT(*) FROM attestations GROUP BY status`, ) if err != nil { return nil, fmt.Errorf("count attestations by status: %w", err) } defer rows.Close()
counts := make(map[string]int) for rows.Next() { var status string var n int if err := rows.Scan(&status, &n); err != nil { return nil, fmt.Errorf("scan status count: %w", err) } counts[status] = n } return counts, rows.Err()}
// scanner is satisfied by both *sql.Row and *sql.Rows.type scanner interface { Scan(dest ...any) error}
func scanAttestationFrom(sc scanner) (*Attestation, error) { var a Attestation var selJSON string var relay, verified int var lastVerified, createdAt, nextCheckAt string
err := sc.Scan( &a.DID, &a.Domain, &a.RKey, &selJSON, &relay, &verified, &lastVerified, &createdAt, &a.Status, &a.FailureKind, &a.AttemptCount, &nextCheckAt, &a.LastError, ) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, fmt.Errorf("scan attestation: %w", err) }
if err := json.Unmarshal([]byte(selJSON), &a.DKIMSelectors); err != nil { return nil, fmt.Errorf("unmarshal selectors: %w", err) } a.RelayMember = relay != 0 a.Verified = verified != 0 a.LastVerified = parseTime(lastVerified) a.CreatedAt = parseTime(createdAt) a.NextCheckAt = parseTime(nextCheckAt) return &a, nil}
// DueAttestations returns every attestation the reconciler should evaluate at// `now`: rows whose next_check_at is empty (due immediately) or whose stored// RFC3339 timestamp is at or before now. Backed by idx_attestations_next_check_at.//// The comparison is lexicographic on the RFC3339 strings, which is sound// because all timestamps are written in UTC ("Z") with formatTime — fixed// width, fixed offset, so string order equals chronological order. The empty// string sorts before any real timestamp, so the OR clause is belt-and-suspenders.func (s *Store) DueAttestations(ctx context.Context, now time.Time) ([]Attestation, error) { nowStr := formatTime(now.UTC()) rows, err := s.db.QueryContext(ctx, `SELECT `+attestationColumns+` FROM attestations WHERE next_check_at = '' OR next_check_at <= ?`, nowStr, ) if err != nil { return nil, fmt.Errorf("due attestations: %w", err) } defer rows.Close()
var atts []Attestation for rows.Next() { a, err := scanAttestationFrom(rows) if err != nil { return nil, err } atts = append(atts, *a) } return atts, rows.Err()}
// UpdateVerificationState applies one atomic state transition to a single// attestation, written by the reconciler and the manual reverify path. It sets// every lifecycle column and keeps the legacy verified/last_verified projection// in lock-step with Status://// - verified = 1 and last_verified = now ⟺ st.Status == "verified"// - otherwise verified = 0 and last_verified is left untouched (we never// clear the last-known-good timestamp on a transient blip).//// failure_kind and last_error are stored verbatim; the caller is responsible// for clearing them on success (st.FailureKind == "" / st.LastError == "").func (s *Store) UpdateVerificationState(ctx context.Context, did, domain string, st VerificationState) error { verified := st.Status == "verified" if verified { _, err := s.db.ExecContext(ctx, `UPDATE attestations SET status = ?, failure_kind = ?, last_error = ?, attempt_count = ?, next_check_at = ?, verified = 1, last_verified = ? WHERE did = ? AND domain = ?`, st.Status, st.FailureKind, st.LastError, st.AttemptCount, formatTime(st.NextCheckAt), formatTime(time.Now().UTC()), did, domain, ) if err != nil { return fmt.Errorf("update verification state (verified): %w", err) } return nil } _, err := s.db.ExecContext(ctx, `UPDATE attestations SET status = ?, failure_kind = ?, last_error = ?, attempt_count = ?, next_check_at = ?, verified = 0 WHERE did = ? AND domain = ?`, st.Status, st.FailureKind, st.LastError, st.AttemptCount, formatTime(st.NextCheckAt), did, domain, ) if err != nil { return fmt.Errorf("update verification state: %w", err) } return nil}
// --- Cursor ---
func (s *Store) GetCursor(ctx context.Context) (int64, error) { var cursor int64 err := s.db.QueryRowContext(ctx, `SELECT cursor FROM cursor WHERE id = 1`).Scan(&cursor) if err != nil { return 0, fmt.Errorf("get cursor: %w", err) } return cursor, nil}
func (s *Store) SetCursor(ctx context.Context, cursor int64) error { _, err := s.db.ExecContext(ctx, `UPDATE cursor SET cursor = ? WHERE id = 1`, cursor, ) return err}
// --- Stats ---
// Stats holds counts for metrics.type Stats struct { Labels int64 Attestations int64}
// Stats returns aggregate counts for metrics.func (s *Store) Stats(ctx context.Context) (Stats, error) { var st Stats err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM labels`).Scan(&st.Labels) if err != nil { return st, fmt.Errorf("count labels: %w", err) } err = s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM attestations`).Scan(&st.Attestations) if err != nil { return st, fmt.Errorf("count attestations: %w", err) } return st, nil}
// Ping verifies the database is reachable.func (s *Store) Ping(ctx context.Context) error { return s.db.PingContext(ctx)}
// --- helpers ---
func boolToInt(b bool) int { if b { return 1 } return 0}
func formatTime(t time.Time) string { if t.IsZero() { return "" } return t.Format(time.RFC3339)}
func parseTime(s string) time.Time { if s == "" { return time.Time{} } t, err := time.Parse(time.RFC3339, s) if err != nil { log.Printf("parseTime: invalid RFC3339 value %q: %v", s, err) return time.Time{} } return t}