How We Built Data Retention for SendRec

March 5, 2026

Video recordings accumulate. A team of ten recording three videos a day produces over 800 recordings a year. Some of those are important. Most are not. Without automatic cleanup, storage grows indefinitely and you end up violating your own data retention policies.

SendRec now has built-in data retention. Configure a retention period at the user or workspace level, pin the videos you want to keep forever, and everything else gets automatically deleted after a 7-day warning email.

Here’s how we built it.

Why data retention matters

Three reasons drove this feature:

GDPR data minimization. Article 5(1)(e) requires that personal data is kept only as long as necessary. Video recordings often contain faces, voices, and screen content with personal data. A configurable retention period lets organizations enforce this automatically.

Storage costs. For self-hosted deployments, S3 storage is a real line item. Auto-deleting old recordings keeps costs predictable.

Enterprise compliance. ISO 27001 and SOC 2 audits ask for documented data retention policies with enforcement mechanisms. A dropdown in settings and an automated worker is a concrete answer.

The data model

Three columns across two existing tables handle the configuration:

-- Migration 000051
ALTER TABLE organizations ADD COLUMN retention_days INT NOT NULL DEFAULT 0;
ALTER TABLE users ADD COLUMN retention_days INT NOT NULL DEFAULT 0;
ALTER TABLE videos ADD COLUMN pinned BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE videos ADD COLUMN retention_warned_at TIMESTAMPTZ;

retention_days = 0 means disabled. Valid values are 0, 30, 60, 90, 180, and 365. The pinned flag lets users exempt individual videos from auto-deletion. retention_warned_at tracks whether the 7-day warning email has been sent.

The interesting design decision is the resolution order. Workspace videos use the organization’s retention_days. Personal videos use the user’s retention_days. This means a workspace admin sets the policy once and it applies to every video in the workspace, while personal accounts control their own.

The two-pass worker

The retention worker runs on a daily ticker and does two things: warn, then delete. Separating these into two passes with a 7-day gap means users always get a chance to pin important videos before they disappear.

func StartRetentionWorker(ctx context.Context, db database.DBTX, sender RetentionSender, baseURL string) {
    if sender == nil {
        return
    }
    go func() {
        slog.Info("retention-worker: started")

        processRetentionWarnings(ctx, db, sender, baseURL)
        processRetentionDeletions(ctx, db)

        ticker := time.NewTicker(24 * time.Hour)
        defer ticker.Stop()
        for {
            select {
            case <-ctx.Done():
                slog.Info("retention-worker: shutting down")
                return
            case <-ticker.C:
                processRetentionWarnings(ctx, db, sender, baseURL)
                processRetentionDeletions(ctx, db)
            }
        }
    }()
}

Both passes run on startup and then every 24 hours. The nil sender check means if email isn’t configured, the worker silently skips.

Pass 1: Warn

The warning query is the most interesting part. It needs to resolve the correct retention period from either the organization or the user, depending on which one the video belongs to:

rows, err := db.Query(ctx,
    `SELECT v.id, v.title, v.share_token, u.email,
            COALESCE(o.retention_days, u.retention_days) AS retention_days
     FROM videos v
     JOIN users u ON u.id = v.user_id
     LEFT JOIN organizations o ON o.id = v.organization_id
     WHERE v.status = 'ready'
       AND v.pinned = false
       AND v.retention_warned_at IS NULL
       AND COALESCE(o.retention_days, u.retention_days) > 0
       AND v.created_at < now() - make_interval(days => COALESCE(o.retention_days, u.retention_days) - 7)
     LIMIT 100`)

COALESCE(o.retention_days, u.retention_days) does the resolution: if the video belongs to a workspace (organization_id is not null), the LEFT JOIN returns the org’s setting. For personal videos, o.retention_days is null and we fall through to the user’s setting.

The - 7 in the age check is the grace period. A 90-day retention policy triggers the warning at day 83, giving users 7 days to pin anything they want to keep.

Results are grouped by email address so each user gets a single email listing all their expiring videos, not one email per video:

grouped := make(map[string][]videoEntry)

for rows.Next() {
    var id, title, shareToken, userEmail string
    var days int
    rows.Scan(&id, &title, &shareToken, &userEmail, &days)
    grouped[userEmail] = append(grouped[userEmail], videoEntry{
        id: id, title: title, shareToken: shareToken, days: days,
    })
}

After sending the email, we mark each video with retention_warned_at = now() so the same video doesn’t trigger another warning on the next tick.

Pass 2: Delete

The deletion pass is simpler. It looks for videos that were warned at least 7 days ago and are still not pinned:

rows, err := db.Query(ctx,
    `SELECT id FROM videos
     WHERE retention_warned_at IS NOT NULL
       AND retention_warned_at < now() - interval '7 days'
       AND status = 'ready'
       AND pinned = false
     LIMIT 100`)

If a user pinned a video after receiving the warning, the pinned = false filter excludes it. The deletion itself is a soft delete — we set status = 'deleted' and remove the video from any playlists. The existing cleanup worker handles purging the actual S3 files later.

db.Exec(ctx, "DELETE FROM playlist_videos WHERE video_id = ANY($1)", videoIDs)
db.Exec(ctx, "UPDATE videos SET status = 'deleted' WHERE id = ANY($1)", videoIDs)

Both passes process 100 videos at a time. With a daily tick this handles up to 100 warnings and 100 deletions per day, which is more than sufficient for typical usage. Larger deployments will process the backlog over multiple days without spiking database load.

The pin toggle

Pinning is a one-click toggle on the VideoDetail page. The handler flips the boolean and returns the new state:

func (h *Handler) TogglePin(w http.ResponseWriter, r *http.Request) {
    videoID := chi.URLParam(r, "id")
    where, args := orgVideoFilter(r.Context(), videoID, nil, "AND status != 'deleted'")

    var pinned bool
    err := h.db.QueryRow(r.Context(),
        "UPDATE videos SET pinned = NOT pinned, updated_at = now() WHERE "+where+" RETURNING pinned", args...,
    ).Scan(&pinned)
    if err != nil {
        httputil.WriteError(w, http.StatusNotFound, "video not found")
        return
    }

    httputil.WriteJSON(w, http.StatusOK, map[string]bool{"pinned": pinned})
}

UPDATE ... SET pinned = NOT pinned ... RETURNING pinned is atomic — no race condition between reading the current value and writing the new one. The frontend shows a pin icon that toggles between filled and outlined, plus a “Pinned” badge when active. Pinned videos also show a pin indicator on library cards.

The warning email

Warning emails go through Listmonk via our existing transactional email pipeline. The email lists each expiring video with its title and watch link, plus the deletion date:

func (c *Client) SendRetentionWarning(ctx context.Context, toEmail string, videos []RetentionVideoSummary, expiryDate string) error {
    if c.config.RetentionWarningTemplateID == 0 {
        slog.Warn("retention warning template ID not set, skipping", "recipient", toEmail)
        return nil
    }

    c.ensureSubscriber(ctx, toEmail, "")

    body := txRequest{
        SubscriberEmail: toEmail,
        TemplateID:      c.config.RetentionWarningTemplateID,
        Data: map[string]any{
            "videos":     videos,
            "expiryDate": expiryDate,
        },
        ContentType: "html",
    }

    return c.sendTx(ctx, body)
}

Two details worth noting. First, retention warnings bypass the email allowlist. These are critical notifications — users need to know their videos are about to be deleted, even in development environments with restricted email sending. Second, if the template ID is zero (not configured), the function returns nil without error. This lets self-hosted instances run without Listmonk configured; videos will still be deleted on schedule, just without the warning.

What we learned

Two passes are simpler than one. We initially considered a single query that calculates the deletion date and groups videos by urgency. The two-pass approach is much cleaner: pass 1 sends warnings, pass 2 deletes warned videos. Each query is simple and testable.

COALESCE handles the org/user resolution in SQL. We considered resolving the retention policy in Go code by querying the org and user separately. Doing it in the query with COALESCE and a LEFT JOIN means one database round trip instead of two, and the filter logic stays in the same place as the data.

Soft delete cascades naturally. Setting status = 'deleted' means the video immediately disappears from the API (all list queries filter on status). The existing S3 cleanup worker picks up deleted videos later. No new cleanup code needed.

Try it

Data retention is live at app.sendrec.eu. Go to Settings, set a retention period, and pin any videos you want to keep forever.

Self-hosting? Pull the latest image and the migration runs automatically. Set DEFAULT_RETENTION_DAYS to configure the default for new accounts, and LISTMONK_RETENTION_WARNING_TEMPLATE_ID if you want warning emails.

Source code: github.com/sendrec/sendrec