1 files changed, 57 insertions, 0 deletions
diff --git a/internal/controlplane/retention.go b/internal/controlplane/retention.go
new file mode 100644
index 0000000..4b20611
--- /dev/null
+++ b/internal/controlplane/retention.go
@@ -0,0 +1,57 @@
+package controlplane
+
+import (
+	"context"
+	"log/slog"
+	"time"
+)
+
+func (s *Store) RunRetentionWorker(ctx context.Context, auditDays, securityDays int, logger *slog.Logger) {
+	run := func() {
+		cleanupCtx, cancel := context.WithTimeout(ctx, 30*time.Second)
+		defer cancel()
+		if err := s.pruneExpiredSecurityData(cleanupCtx, auditDays, securityDays); err != nil {
+			logger.Error("retention_cleanup_failed", "error", err)
+		}
+	}
+	run()
+	ticker := time.NewTicker(24 * time.Hour)
+	defer ticker.Stop()
+	for {
+		select {
+		case <-ctx.Done():
+			return
+		case <-ticker.C:
+			run()
+		}
+	}
+}
+
+func (s *Store) pruneExpiredSecurityData(ctx context.Context, auditDays, securityDays int) error {
+	tx, err := s.db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback(ctx)
+	auditCutoff := time.Now().UTC().AddDate(0, 0, -auditDays)
+	securityCutoff := time.Now().UTC().AddDate(0, 0, -securityDays)
+	queries := []struct {
+		query  string
+		cutoff time.Time
+	}{
+		{`DELETE FROM audit_logs WHERE created_at < $1`, auditCutoff},
+		{`DELETE FROM console_sessions WHERE expires_at < $1 OR (revoked_at IS NOT NULL AND revoked_at < $1)`, securityCutoff},
+		{`DELETE FROM console_action_tokens WHERE expires_at < $1 OR (consumed_at IS NOT NULL AND consumed_at < $1)`, securityCutoff},
+		{`DELETE FROM console_auth_challenges WHERE expires_at < $1 OR (consumed_at IS NOT NULL AND consumed_at < $1)`, securityCutoff},
+		{`DELETE FROM console_webauthn_challenges WHERE expires_at < $1 OR (consumed_at IS NOT NULL AND consumed_at < $1)`, securityCutoff},
+		{`DELETE FROM console_login_throttles WHERE updated_at < $1`, securityCutoff},
+		{`DELETE FROM console_rate_limits WHERE updated_at < $1`, securityCutoff},
+		{`DELETE FROM console_mail_outbox WHERE status='sent' AND sent_at < $1`, securityCutoff},
+	}
+	for _, item := range queries {
+		if _, err := tx.Exec(ctx, item.query, item.cutoff); err != nil {
+			return err
+		}
+	}
+	return tx.Commit(ctx)
+}