Dec 1, 2025 · Dec 1, 2025 · Dec 1, 2025 · Dec 1, 2025 · Dec 1, 2025 · Dec 1, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return q.db.DeleteOldAuditLogConnectionEvents(ctx, threshold)
 }

 func (q *querier) DeleteOldAuditLogs(ctx context.Context, arg database.DeleteOldAuditLogsParams) (int64, error) {
 if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
 return 0, err
 }
 return q.db.DeleteOldAuditLogs(ctx, arg)
 }

 func (q *querier) DeleteOldConnectionLogs(ctx context.Context, arg database.DeleteOldConnectionLogsParams) (int64, error) {
 if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
 return 0, err
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 dbm.EXPECT().DeleteOldAuditLogConnectionEvents(gomock.Any(), database.DeleteOldAuditLogConnectionEventsParams{}).Return(nil).AnyTimes()
 check.Args(database.DeleteOldAuditLogConnectionEventsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
 }))
 s.Run("DeleteOldAuditLogs", s.Mocked(func(dbm *dbmock.MockStore, _ *gofakeit.Faker, check *expects) {
 dbm.EXPECT().DeleteOldAuditLogs(gomock.Any(), database.DeleteOldAuditLogsParams{}).Return(int64(0), nil).AnyTimes()
 check.Args(database.DeleteOldAuditLogsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
 }))
 }

 func (s *MethodTestSuite) TestConnectionLogs() {
diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dbpurge/dbpurge.go b/coderd/database/dbpurge/dbpurge.go
 // Batch size for connection log deletion. Smaller batches prevent long-held
 // locks that could impact concurrent database operations.
 connectionLogsBatchSize = 1000
 // Batch size for audit log deletion.
 auditLogsBatchSize = 1000
 // Telemetry heartbeats are used to deduplicate events across replicas. We
 // don't need to persist heartbeat rows for longer than 24 hours, as they
 // are only used for deduplication across replicas. The time needs to be
 if err := tx.ExpirePrebuildsAPIKeys(ctx, dbtime.Time(start)); err != nil {
 return xerrors.Errorf("failed to expire prebuilds user api keys: %w", err)
 }
 expiredAPIKeys, err := tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
 // Leave expired keys for a week to allow the backend to know the difference
 // between a 404 and an expired key. This purge code is just to bound the size of
 // the table to something more reasonable.
 Before: dbtime.Time(start.Add(time.Hour * 24 * 7 * -1)),
 // There could be a lot of expired keys here, so set a limit to prevent this
 // taking too long.
 // This runs every 10 minutes, so it deletes ~1.5m keys per day at most.
 LimitCount: 10000,
 })
 if err != nil {
 return xerrors.Errorf("failed to delete expired api keys: %w", err)

 var expiredAPIKeys int64
 apiKeysRetention := vals.Retention.APIKeys.Value()
 if apiKeysRetention == 0 {
 apiKeysRetention = vals.Retention.Global.Value()
 }
 if apiKeysRetention > 0 {
 // Delete keys that have been expired for at least the retention period.
 // A higher retention period allows the backend to return a more helpful
 // error message when a user tries to use an expired key.
 deleteExpiredKeysBefore := start.Add(-apiKeysRetention)
 expiredAPIKeys, err = tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
 Before: dbtime.Time(deleteExpiredKeysBefore),
 // There could be a lot of expired keys here, so set a limit to prevent
 // this taking too long. This runs every 10 minutes, so it deletes
 // ~1.5m keys per day at most.
 LimitCount: 10000,
 })
 if err != nil {
 return xerrors.Errorf("failed to delete expired api keys: %w", err)
 }
 }
 deleteOldTelemetryLocksBefore := start.Add(-maxTelemetryHeartbeatAge)
 if err := tx.DeleteOldTelemetryLocks(ctx, deleteOldTelemetryLocksBefore); err != nil {
 }
 }

 var purgedAuditLogs int64
 auditLogsRetention := vals.Retention.AuditLogs.Value()
 if auditLogsRetention == 0 {
 auditLogsRetention = vals.Retention.Global.Value()
 }
 if auditLogsRetention > 0 {
 deleteAuditLogsBefore := start.Add(-auditLogsRetention)
 purgedAuditLogs, err = tx.DeleteOldAuditLogs(ctx, database.DeleteOldAuditLogsParams{
 BeforeTime: deleteAuditLogsBefore,
 LimitCount: auditLogsBatchSize,
 })
 if err != nil {
 return xerrors.Errorf("failed to delete old audit logs: %w", err)
 }
 }

 logger.Debug(ctx, "purged old database entries",
 slog.F("expired_api_keys", expiredAPIKeys),
 slog.F("aibridge_records", purgedAIBridgeRecords),
 slog.F("connection_logs", purgedConnectionLogs),
 slog.F("audit_logs", purgedAuditLogs),
 slog.F("duration", clk.Since(start)),
 )
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1749,6 +1749,13 @@ func (q *querier) DeleteOldAuditLogConnectionEvents(ctx context.Context, thresho
		return q.db.DeleteOldAuditLogConnectionEvents(ctx, threshold)
		}

		func (q *querier) DeleteOldAuditLogs(ctx context.Context, arg database.DeleteOldAuditLogsParams) (int64, error) {
		if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
		return 0, err
		}
		return q.db.DeleteOldAuditLogs(ctx, arg)
		}

		func (q *querier) DeleteOldConnectionLogs(ctx context.Context, arg database.DeleteOldConnectionLogsParams) (int64, error) {
		if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
		return 0, err
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -324,6 +324,10 @@ func (s *MethodTestSuite) TestAuditLogs() {
		dbm.EXPECT().DeleteOldAuditLogConnectionEvents(gomock.Any(), database.DeleteOldAuditLogConnectionEventsParams{}).Return(nil).AnyTimes()
		check.Args(database.DeleteOldAuditLogConnectionEventsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
		}))
		s.Run("DeleteOldAuditLogs", s.Mocked(func(dbm dbmock.MockStore, _ gofakeit.Faker, check *expects) {
		dbm.EXPECT().DeleteOldAuditLogs(gomock.Any(), database.DeleteOldAuditLogsParams{}).Return(int64(0), nil).AnyTimes()
		check.Args(database.DeleteOldAuditLogsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
		}))
		}

		func (s *MethodTestSuite) TestConnectionLogs() {
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -28,6 +28,8 @@ const (
		// Batch size for connection log deletion. Smaller batches prevent long-held
		// locks that could impact concurrent database operations.
		connectionLogsBatchSize = 1000
		// Batch size for audit log deletion.
		auditLogsBatchSize = 1000
		// Telemetry heartbeats are used to deduplicate events across replicas. We
		// don't need to persist heartbeat rows for longer than 24 hours, as they
		// are only used for deduplication across replicas. The time needs to be
Expand DownExpand Up		@@ -81,18 +83,27 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, vals *coder
		if err := tx.ExpirePrebuildsAPIKeys(ctx, dbtime.Time(start)); err != nil {
		return xerrors.Errorf("failed to expire prebuilds user api keys: %w", err)
		}
		expiredAPIKeys, err := tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
		// Leave expired keys for a week to allow the backend to know the difference
		// between a 404 and an expired key. This purge code is just to bound the size of
		// the table to something more reasonable.
		Before: dbtime.Time(start.Add(time.Hour * 24 * 7 * -1)),
		// There could be a lot of expired keys here, so set a limit to prevent this
		// taking too long.
		// This runs every 10 minutes, so it deletes ~1.5m keys per day at most.
		LimitCount: 10000,
		})
		if err != nil {
		return xerrors.Errorf("failed to delete expired api keys: %w", err)

		var expiredAPIKeys int64
		apiKeysRetention := vals.Retention.APIKeys.Value()
		if apiKeysRetention == 0 {
		apiKeysRetention = vals.Retention.Global.Value()
		}
		if apiKeysRetention > 0 {
		// Delete keys that have been expired for at least the retention period.
		// A higher retention period allows the backend to return a more helpful
		// error message when a user tries to use an expired key.
		deleteExpiredKeysBefore := start.Add(-apiKeysRetention)
		expiredAPIKeys, err = tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
		Before: dbtime.Time(deleteExpiredKeysBefore),
		// There could be a lot of expired keys here, so set a limit to prevent
		// this taking too long. This runs every 10 minutes, so it deletes
		// ~1.5m keys per day at most.
		LimitCount: 10000,
		})
		if err != nil {
		return xerrors.Errorf("failed to delete expired api keys: %w", err)
		}
		}
		deleteOldTelemetryLocksBefore := start.Add(-maxTelemetryHeartbeatAge)
		if err := tx.DeleteOldTelemetryLocks(ctx, deleteOldTelemetryLocksBefore); err != nil {
Expand DownExpand Up		@@ -130,10 +141,27 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, vals *coder
		}
		}

		var purgedAuditLogs int64
		auditLogsRetention := vals.Retention.AuditLogs.Value()
		if auditLogsRetention == 0 {
		auditLogsRetention = vals.Retention.Global.Value()
		}
		if auditLogsRetention > 0 {
		deleteAuditLogsBefore := start.Add(-auditLogsRetention)
		purgedAuditLogs, err = tx.DeleteOldAuditLogs(ctx, database.DeleteOldAuditLogsParams{
		BeforeTime: deleteAuditLogsBefore,
		LimitCount: auditLogsBatchSize,
		})
		if err != nil {
		return xerrors.Errorf("failed to delete old audit logs: %w", err)
		}
		}

		logger.Debug(ctx, "purged old database entries",
		slog.F("expired_api_keys", expiredAPIKeys),
		slog.F("aibridge_records", purgedAIBridgeRecords),
		slog.F("connection_logs", purgedConnectionLogs),
		slog.F("audit_logs", purgedAuditLogs),
		slog.F("duration", clk.Since(start)),
		)

Expand Down