Pagination

SELECT id, title, body, published_at
FROM notices
WHERE org_id = $1
  AND state = 'published'
  AND (published_at, id) < ($2, $3)
ORDER BY published_at DESC, id DESC
LIMIT $4;

package notice

import (
    "crypto/hmac"
    "crypto/sha256"
    "encoding/base64"
    "encoding/hex"
    "encoding/json"
    "errors"
    "fmt"
    "time"

    "github.com/google/uuid"
)

type CursorMode string

const (
    CursorModePublished CursorMode = "published"
    CursorModeSearch    CursorMode = "search"
)

var ErrInvalidCursor = errors.New("notice pagination: invalid cursor")

type FeedCursor struct {
    Mode        CursorMode
    PublishedAt *time.Time
    Score       *float64
    EvaluatedAt *time.Time
    ID          uuid.UUID
}

type cursorEnvelope struct {
    Payload FeedCursor
    Sig     string
}

type CursorCodec struct {
    secret []byte
}

func NewCursorCodec(secret string) (*CursorCodec, error) {
    if secret == "" {
        return nil, fmt.Errorf("%w: empty secret", ErrInvalidCursor)
    }
    return &CursorCodec{secret: []byte(secret)}, nil
}

func (c *CursorCodec) Encode(cursor FeedCursor) (string, error) {
    payloadBytes, err := json.Marshal(cursor)
    if err != nil {
        return "", err
    }

    mac := hmac.New(sha256.New, c.secret)
    mac.Write(payloadBytes)
    envelope := cursorEnvelope{
        Payload: cursor,
        Sig:     hex.EncodeToString(mac.Sum(nil)),
    }

    raw, err := json.Marshal(envelope)
    if err != nil {
        return "", err
    }
    return base64.RawURLEncoding.EncodeToString(raw), nil
}

func (c *CursorCodec) Decode(token string) (FeedCursor, error) {
    raw, err := base64.RawURLEncoding.DecodeString(token)
    if err != nil {
        return FeedCursor{}, fmt.Errorf("%w: base64 decode failed", ErrInvalidCursor)
    }

    var env cursorEnvelope
    if err := json.Unmarshal(raw, &env); err != nil {
        return FeedCursor{}, fmt.Errorf("%w: json decode failed", ErrInvalidCursor)
    }

    payloadBytes, err := json.Marshal(env.Payload)
    if err != nil {
        return FeedCursor{}, err
    }

    mac := hmac.New(sha256.New, c.secret)
    mac.Write(payloadBytes)
    expected := mac.Sum(nil)
    actual, err := hex.DecodeString(env.Sig)
    if err != nil || !hmac.Equal(expected, actual) {
        return FeedCursor{}, fmt.Errorf("%w: signature mismatch", ErrInvalidCursor)
    }

    if env.Payload.ID == uuid.Nil {
        return FeedCursor{}, fmt.Errorf("%w: missing id", ErrInvalidCursor)
    }

    switch env.Payload.Mode {
    case CursorModePublished:
        if env.Payload.PublishedAt == nil {
            return FeedCursor{}, fmt.Errorf("%w: published cursor missing published_at", ErrInvalidCursor)
        }
    case CursorModeSearch:
        if env.Payload.Score == nil || env.Payload.EvaluatedAt == nil {
            return FeedCursor{}, fmt.Errorf("%w: search cursor missing score or evaluated_at", ErrInvalidCursor)
        }
    default:
        return FeedCursor{}, fmt.Errorf("%w: unsupported mode", ErrInvalidCursor)
    }

    return env.Payload, nil
}

func PublishedSeekPredicate(cursor FeedCursor, firstArg int) (string, []any, error) {
    if cursor.Mode != CursorModePublished || cursor.PublishedAt == nil {
        return "", nil, fmt.Errorf("%w: published cursor required", ErrInvalidCursor)
    }
    return fmt.Sprintf(" AND (published_at, id) < ($%d, $%d)", firstArg, firstArg+1), []any{cursor.PublishedAt.UTC(), cursor.ID}, nil
}

func SearchSeekPredicate(cursor FeedCursor, firstArg int) (string, []any, error) {
    if cursor.Mode != CursorModeSearch || cursor.Score == nil || cursor.EvaluatedAt == nil {
        return "", nil, fmt.Errorf("%w: search cursor required", ErrInvalidCursor)
    }
    return fmt.Sprintf(" AND (score, id) < ($%d, $%d)", firstArg, firstArg+1), []any{*cursor.Score, cursor.ID}, nil
}

func NewPublishedCursor(item Notice) FeedCursor {
    return FeedCursor{
        Mode:        CursorModePublished,
        PublishedAt: item.PublishedAt,
        ID:          item.ID,
    }
}

func NewSearchCursor(item SearchResult, evaluatedAt time.Time) FeedCursor {
    score := item.Score
    stamp := evaluatedAt.UTC()
    return FeedCursor{
        Mode:        CursorModeSearch,
        Score:       &score,
        EvaluatedAt: &stamp,
        ID:          item.ID,
    }
}